Setup Oracle Database Auditing

Server Setup

SQL> SHOW PARAMETER AUDIT

You will find the value as follows
audit_trail string NONE

Auditing is disabled by default, but can enabled by setting the AUDIT_TRAIL static parameter, which has the following allowed values

AUDIT_TRAIL = { none os db db,extended xml xml,extended }

The following list provides a description of each setting:
none or false - Auditing is disabled.
db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
xml- Auditing is enabled, with all audit records stored as XML format OS files.
xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
os- Auditing is enabled, with all audit records directed to the operating system's audit trail.

To enable auditing and direct audit records to the database audit trail, we would do the following.

SQL> Conn SYS AS SYSDBA
SQL> ALTER SYSTEM SET audit_trail=db,extended SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

Next we audit all operations by the SCOTT user on DEPT table.

AUDIT DELETE,SELECT,UPDATE,INSERT on TABLE SCOTT.DEPT BY ACCESS;


View Audit Trail Data

SQL> Conn SYSTEM/systempassword

SQL> SELECT os_username, userhost, terminal user_pc,
TO_CHAR (extended_timestamp, 'dd/mm/rrrr hh:mi:ss') action_time,
obj_name, action_name, sql_text, username schema_made_action
FROM dba_audit_trail
WHERE owner = 'SCOTT'
ORDER BY TIMESTAMP DESC

Purging the Audit Trail

The DBA can purge the audit trail table by deleting all records in the table or by truncating the table:

SQL> delete from sys.aud$;


or

SQL> truncate table sys.aud$;


Archiving the Audit Trail

Before purging data from the audit trail table, it is probably a good idea to archive the data first. It is usually better to be safe than sorry when dealing with any type of data.

The easiest way to archive the audit trail before purging it is to create a table based on the contents of SYS.AUD$, as in the following example. After a successful copy has been made, the original table can be purged:

SQL> create table audit_copy as
select * from sys.aud$;


SQL> delete from sys.aud$;


After a copy of SYS.AUD$ has been made, you can append data to the table from future clean-up routines by inserting the contents of SYS.AUD$ into your copy table:

SQL> insert into audit_copy
select * from sys.aud$;


SQL> delete from sys.aud$;