How to Disable Oracle Database Auditing

What is Oracle Database Auditing?

Auditing is the monitoring and recording of selected user database actions. It can be based on individual actions, such as the type of SQL statement executed, or on combinations of factors that can include user name, application, time, and so on. Security policies can trigger auditing when specified elements in an Oracle database are accessed or altered, including the contents within a specified object.

Below is a list of different types of auditing mechanisms implemented in the oracle database and how to disable them.

1. Standard database auditing

To immediately disable auditing enabled with AUDIT commands (also known as standard auditing), set AUDIT_TRAIL = NONE. However, this requires a restart of the database. The AUDIT_TRAIL parameter has no bearing on Fine-Grained Auditing (FGA), so FGA cannot be disabled in this way, and FGA policies will continue to produce audit trail records.

If a database restart is not desired, the NOAUDIT command can be used to turn off AUDIT commands that were previously issued. NOAUDIT will only affect new sessions, and will never affect SYSDBA.

2. SYSDBA auditing

Some auditing of SYSDBA is mandatory and cannot be disabled (STARTUP, SHUTDOWN, and CONNECT). However, if AUDIT_SYS_OPERATIONS is currently set to TRUE, set this to FALSE if it is necessary to reduce the amount/size of files created in AUDIT_FILE_DEST that are being generated by SYSDBA activity.

AUDIT_TRAIL also has no bearing on SYSDBA, as AUDIT commands will not capture SYSDBA actions.

3. Fine-Grained Auditing (FGA)

To disable Fine-Grained Auditing, execute DBMS_FGA.DISABLE_POLICY or DROP_POLICY, whichever is preferred.

4. Unified Auditing (Oracle12c)

To disable Unified Auditing on Unix/Linux platforms, follow these steps:

1. Login to the database as sys with sysoper privileges:

$ sqlplus sys as sysoper

2. In a multitenant environment, connect to the appropriate PDB. For example:

CONNECT SYS@appdb AS SYSOPER
Enter password: password

Note: To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the ‘show con_name’ command.

3. Shutdown the database. For example:

SQL> SHUTDOWN IMMEDIATE

4. Exit from SQL*Plus and at the command prompt and run the following commands:

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk uniaud_off ioracle

5. In SQL*Plus, connect as sysdba and restart the database.

SQL> STARTUP

6. Then run the following query to check whether unified auditing is disabled or not:

SQL> select * from audit_unified_enabled_policies;

7. If the above query returns any rows, disable the policies using the following syntax:

 SQL> noaudit policy [policy_name]