Oracle Database Auditing

1. List out all related audit parameter in Oracle database.

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /oracle/audit
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      NONE

2. Set the location where the audit log files written. You can specify the location either in DB or OS level. You can also specify the format into XML type.

SQL> alter system set audit_trail=db scope=spfile; 

3, Enable SYS user (Oracle will audit any connection using the SYSDBA, SYSOPER privilleges) auditing.

SQL> alter system set audit_sys_operations=true scope=spfile;
SQL> Shutdown immediate;
SQL> startup

4. Start the Oracle auditing, as an example, you want audit table creation (there are multiple options can be audits!)

SQL> audit create any table;

5. Disable Oracle auditing.

SQL> alter system set audit_trail=none scope=spfile;