Configuring auditing

How to check if auditing is enabled:

1. connect as sysdba
2. run

   SHOW PARAMETER AUDIT_TRAIL;
   

if auditing_trail parameter is set to None, enable auditing by performing the following steps:

1. Update parameter file:

   ALTER SYSTEM SET AUDIT_TRAIL='DB' SCOPE=SPFILE; 
   

2. Shutdown the database

   SHUTDOWN IMMEDIATE;
   

3. Re-start and open database

   STARTUP OPEN;
   

4. Check the parameter value:

   SHOW PARAMETER AUDIT_TRAIL;
   

When AUDIT_TRAIL parameter is set to DB, audit data can be sound in SYS.AUD$ table, or one of the views based on this tabel: DBA_AUDIT_TRAIL, DBA_COMMON_AUDIT_TRAIL or DBA_COMMON_AUDIT_TRAIL. In order to capture the actual SQL command issued by the user, change AUDIT_TRAIL to DB_EXTENDED. In SYS.AUD$ the sql command will show up in SQL_TEXT field. For example:

SELECT ACTION, SQL_TEXT FROM DBA_AUDIT_TRAIL;

To view what is audited for each user, run:

SELECT USER_NAME, AUDIT_OPTION FROM DBA_STMT_AUDIT_OPTS WHERE NOT USER_NAME IS NULL;

To retrieve all the sql statements ran by a current user, run (AUDIT_TRAIL should be set to DB_EXTENDED in order to auddit actual sql statements):

SELECT SQL_TEXT FROM USER_AUDIT_OBJECT;