How to configure oracle auditing

This article show real time example how to enable oracle auditing and how to configure different types of auditing. Firstly we need to check oracle auditing are enable if oracle auditing are not enable follow the below steps to configure oracle auditing.

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;