The ” _trace_files_public” parameter is a hidden parameter whose default value is false and once it’s set to true that will allow all users accessing the server where the oracle database is hosted will be able to read the trace files. 

Trace files in an oracle database are generated when there is an issue or we have deliberately enabled it to debug some scenario and the oracle process based on version also creates multiple trace files. These files may have some sensitive information and if you want only the owner of your oracle software and users belonging to the same group can only read them we must keep the “_trace_files_public” parameter to its default value false. This is also a CIS recommendation for oracle database security.

Since this is a hidden parameter and your query its current setting following X$ tables need to be queried:

     select A.ksppinm, B.ksppstvl

     from sys.x$ksppi a,sys.x$ksppcv b

     where A.indx=B.indx

     and A.ksppinm like '\_%trace_files_public' escape '\';

File will look like as below when this parameter is set to false:

  -rw-r----- 1 oracle oinstall   925 May 17 06:21 TESTDB_ora_934343.trc

In case when it’s true it will look like:

-rw-r--r-- 1 oracle oinstall   925 May 17 06:21 TESTDB_ora_934343.trc

     

To change the value of this parameter, it will need a database bounce after running:

alter system set "_trace_files_public" = TRUE scope=spfile;

     

It is not recommended to make this parameter TRUE and only provided in the blog for informational purposes.

Those who want to scan their database for CIS compliance through non-sys users will not be able to run the above-mentioned query on X$ tables. If you want to use a non-sys user to query this, this would need to create views in sys schema and then grant select on these views to other non-sys users. Running scans with sys user also not recommended and creating custom views in sys schema also not accepted as best practices. Let us still discuss about this for informational purpose:

     create view sys.x_$ksppi as select * from sys.x$ksppi;

     create view sys.x_$ksppcv as select * from sys.x$ksppcv;

     grant select on sys.x$ksppi to <role_name>;

     grant select on sys.x$ksppcv to <role_name>;

     grant <role_name> to <non sys scan user>;

Now query to be referencing views in place of tables:

     select A.ksppinm, B.ksppstvl

     from sys.x_$ksppi a,sys.x_$ksppcv b

     where A.indx=B.indx

     and A.ksppinm like '\_%trace_files_public' escape '\';

Share this post: on Twitter on Facebook