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;


Post a Comment

0 Comments

3