Audit Trail example in Oracle 10gR2

This is a quick tutorial which demonstrates how to enable database auditing in oracle 10g, how to set audit options and at the end how to see the audit trail generated. There are very low level of details about database auditing in this tutorial as it is a quicky. I will give a shot on writing in detail about database auditing soon.

$ sqlplus / as sysdba

SQL> show parameter audit

NAME                  TYPE     VALUE
--------------------- -------- -------------------------------
audit_file_dest       string   /d01/app/admin/ora10g/adump
audit_sys_operations  boolean  FALSE
audit_syslog_level    string
audit_trail           string   NONE
SQL>

/*
  audit_sys_operations should be True if changes need to be tracked 
  by any SYSDBA user. audit_trail should be set to db_extended if 
  the sql text used by users to make changes needs to be audited and
  the audit is supposed to be kept inside the database itself.
*/

SQL> alter system set audit_sys_operations=TRUE scope=SPFILE;

System altered.

SQL> alter system set audit_trail=db_extended scope=SPFILE;

System altered.

/* 
  We need a Database restart to make the above changes effective. 
*/

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2076464 bytes
Variable Size             805306576 bytes
Database Buffers           25165824 bytes
Redo Buffers                6311936 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter audit

NAME                  TYPE     VALUE
--------------------- -------- -------------------------------
audit_file_dest       string   /d01/app/admin/ora10g/adump
audit_sys_operations  boolean  TRUE
audit_syslog_level    string
audit_trail           string   DB_EXTENDED
SQL>

SQL> AUDIT CREATE ANY TABLE, DROP ANY TABLE 
  2  BY scott 
  3  BY ACCESS;

Audit succeeded.

SQL> AUDIT INSERT , UPDATE , DELETE
  2  ON scott.emp
  3  BY ACCESS;

Audit succeeded.

/* 
  The above two statements set two auditing options.
  1. Whenever user SCOTT exercises the CREATE ANY TABLE and 
     DROP ANY TABLE system privileges. 
  2. Whenever the UPDATE, DELETE and INSERT object privileges 
     are exercised on table SCOTT.EMP.
*/

Lets connect as user scott and trigger a few statements relevant to the auditing options we just enabled.

$ sqlplus scott/tiger

SQL> create table audit_test (id number);

Table created.

SQL> insert into emp (empno, deptno) values (1212,10);

1 row created.

SQL> update emp set sal = 9900 where empno = 1212;

1 row updated.

SQL> delete from emp where empno=1212;

1 row deleted.

SQL> commit;

Commit complete.

SQL> exit

The user scott has successfully performed a CREATE and DROP table and INSERT, UPDATE and DELETE on table EMP in his schema.

$ sqlplus / as sysdba

SQL> COLUMN username FORMAT A8
SQL> COLUMN obj_name FORMAT A10
SQL> COLUMN action_name FORMAT A12
SQL> COLUMN sql_text FORMAT A37   
SQL> SELECT username,obj_name,action_name, sql_text
  2  FROM   dba_audit_trail
  3  WHERE  username = 'SCOTT'
  4  ORDER BY timestamp;

USERNAME OBJ_NAME   ACTION_NAME  SQL_TEXT
-------- ---------- ------------ -------------------------------------
SCOTT    AUDIT_TEST CREATE TABLE create table audit_test (id number)
SCOTT    EMP        INSERT       insert into emp (empno, deptno) value
                                 s (1212,10)
SCOTT    EMP        UPDATE       update emp set sal = 9900 where empno
                                  = 1212
SCOTT    EMP        DELETE       delete from emp where empno=1212

There are lots of other auditing options are available. I will discuss those in another tutorial later.