Mastering Oracle Auditing: How to Enable, Configure, and Interpret Audit Trails

1. Purpose of auditing

Auditing records each operation on database objects, capturing timestamp, user, object, and operation type. It does not store the exact data values changed, unless fine‑grained auditing (FGA) is used to capture DML statements.

2. Auditing categories

Statement auditing : Audits specific SQL statements regardless of the objects they affect, e.g., AUDIT CREATE TABLE; Privilege auditing : Audits operations that involve particular privileges; the same command also audits the CREATE TABLE privilege.

Object auditing : Audits actions performed on designated objects.

3. Enabling auditing

Set the AUDIT_TRAIL initialization parameter in the spfile/pfile: DB – stores audit records in the database. OS – writes audit records to operating‑system files (location controlled by AUDIT_FILE_DEST). NONE – disables auditing (default).

4. Defining audit policies

Use the AUDIT statement. Example to audit all SELECT, INSERT, DELETE on table jward.dept:

AUDIT SELECT, INSERT, DELETE ON jward.dept;

5. Controlling audit record generation

Session vs. Access : BY SESSION creates a single entry per session for repeated actions; BY ACCESS creates an entry for every execution.

Success vs. Failure : WHENEVER SUCCESSFUL records only successful operations; WHENEVER NOT SUCCESSFUL records failures.

6. Sample audit statements

Statement auditing:

AUDIT SESSION;

Privilege auditing:

AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;

Object auditing:

AUDIT SELECT, INSERT, DELETE ON jward.dept BY ACCESS WHENEVER SUCCESSFUL;

7. Storage locations for audit records

If AUDIT_TRAIL=OS, records are written to files under $ORACLE_HOME/rdbms/audit/ on Unix or to the Windows Event Viewer.

If AUDIT_TRAIL=DB, records are stored in the following tables and views: SYS.AUD$ (base table) DBA_AUDIT_TRAIL,

USER_AUDIT_TRAIL

DBA_AUDIT_OBJECT

,

USER_AUDIT_OBJECT

DBA_AUDIT_SESSION

,

USER_AUDIT_SESSION

DBA_AUDIT_STATEMENT

,

USER_AUDIT_STATEMENT

DBA_AUDIT_EXISTS

Fine‑grained auditing views: DBA_AUDIT_POLICIES,

DBA_FGA_AUDIT_TRAIL

8. Overlap between statement and privilege auditing

A single AUDIT CREATE TABLE; command creates both a statement‑audit rule and a privilege‑audit rule, producing identical entries whenever a CREATE TABLE statement is executed.

SQL> audit CREATE TABLE; Audit succeeded SQL> SELECT * FROM DBA_STMT_AUDIT_OPTS; CREATE TABLE BY ACCESS BY ACCESS SQL> SELECT * FROM DBA_PRIV_AUDIT_OPTS; CREATE TABLE BY ACCESS BY ACCESS

9. References

http://blog.itpub.net/post/468/6806

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/audit.htm#1108

http://www.softhouse.com.cn/html/200412/2004121608315200002957.html

http://www.itpub.net/showthread.php?s=&threadid=239693&highlight=%C9%F3%BC%C6