How to Audit Oracle Database

 

Step 1: Log in as SYS with SYSDBA privileges

# sqlplus / as sysdba

Step 2: Check the current settings

SQL> show parameter audit

Note: default audit_sys_operations value is FALSE and audit_trial value is none

Step 3: Set the type of auditing you want by setting the audit_trail parameter

SQL> alter system set audit_trail=DB, EXTENDED scope=spfile;

Step 4: Restart the Oracle instance

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP

Note: Check parameter of audit table again

SQL> show parameter audit

Step 5: Audit enable on specific table

SQL> audit select,delete, insert, update on apps.fnd_user by access;

Step 6: Audit disable on specific table

SQL> noaudit select,delete, insert, update on apps.fnd_user;

SQL> AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;            // enable insert, update, delete audit

SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY username BY ACCESS;

SQL> NOAUDIT CREATE TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY username;     // audit diable

Step 7: Show audit data from this query

SQL> select EXTENDED_TIMESTAMP, DB_USER, OS_USER, STATEMENT_TYPE, SQL_TEXT from DBA_COMMON_AUDIT_TRAIL

 

 Step 8: Audit disable on Database

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