Introduction to Auditing

A database’s audit trail is stored in the SYS.AUD$ table:

SQL> desc sys.aud$

Name                       Null?    Type

-------------------------- -------- ------------------

SESSIONID                  NOT NULL NUMBER

ENTRYID                    NOT NULL NUMBER

STATEMENT                  NOT NULL NUMBER

TIMESTAMP#                 NOT NULL DATE

USERID                              VARCHAR2(30)

USERHOST                            VARCHAR2(128)

TERMINAL                            VARCHAR2(255)

ACTION#                    NOT NULL NUMBER

RETURNCODE                 NOT NULL NUMBER

OBJ$CREATOR                         VARCHAR2(30)

OBJ$NAME                            VARCHAR2(128)

AUTH$PRIVILEGES                     VARCHAR2(16)

AUTH$GRANTEE                        VARCHAR2(30)

NEW$OWNER                           VARCHAR2(30)

NEW$NAME                            VARCHAR2(128)

SES$ACTIONS                         VARCHAR2(19)

SES$TID                             NUMBER

LOGOFF$LREAD                        NUMBER

LOGOFF$PREAD                        NUMBER

LOGOFF$LWRITE                       NUMBER

LOGOFF$DEAD                         NUMBER

LOGOFF$TIME                         DATE

COMMENT$TEXT                        VARCHAR2(4000)

CLIENTID                            VARCHAR2(64)

SPARE1                              VARCHAR2(255)

SPARE2                              NUMBER

OBJ$LABEL                           RAW(255)

SES$LABEL                           RAW(255)

PRIV$USED                           NUMBER

SESSIONCPU                          NUMBER

 

SQL>

 

You can tell Oracle what to monitor using the AUDIT command. The example below will record when the SYSTEM user connects to the database:

 

SQL> audit create session by system;

 

Audit succeeded.

 

SQL>

 

This database currently has auditing turned off i.e the audit_trail initialisation parameter is set to NONE:

 

SQL> l

  1  select value from v$parameter

  2* where name = 'audit_trail'

SQL> /

 

VALUE

------------------------------

NONE

 

SQL>

 

So subsequent logins by SYSTEM will not be recorded in SYS.AUD$:

 

SQL> conn system/manager

Connected.

SQL> select count(*) from sys.aud$

  2  /

 

  COUNT(*)

----------

         0

 

SQL>

 

In versions up to and including Oracle 11, you cannot turn auditing on while a database is open:

 

SQL> alter system set audit_trail = true;

alter system set audit_trail = true

                 *

ERROR at line 1:

ORA-02095: specified initialization parameter cannot

be modified

 

SQL>

 

You have to alter the pfile or spfile as appropriate and bounce the database. Once audit_trail is set to true, auditing will start working and a row will appear in SYS.AUD$ after SYSTEM has connected to the database:

 

SQL> select value from v$parameter

  2  where name = 'audit_trail'

  3  /

 

VALUE

------------------------------

TRUE

 

SQL> conn system/manager

Connected.

SQL> select count(*) from sys.aud$

  2  /

 

  COUNT(*)

----------

         1

 

SQL>

 

You can query records in SYS.AUD$ in the normal way. The TIMESTAMP# column has the logon time and a PRIV$USED of 5 stands for CREATE SESSION:

 

SQL> l

  1* select userid, timestamp#, priv$used from sys.aud$

SQL> /

 

USERID     TIMESTAMP#  PRIV$USED

---------- ---------- ----------

SYSTEM     21-MAR-12           5

 

SQL>

 

Various views record the auditing which you have asked Oracle to do in your database. The audit request we made above is stored in DBA_PRIV_AUDIT_OPTS:

 

SQL> select user_name, privilege

  2  from dba_priv_audit_opts

  3  /

 

USER_NAME            PRIVILEGE

-------------------- --------------------

SYSTEM               CREATE SESSION

 

SQL>

 

You can stop an audit request with the NOAUDIT command:

 

SQL> noaudit create session by system

  2  /

 

Noaudit succeeded.

 

SQL>

 

Once you have done this, the relevant entry disappears from DBA_PRIV_AUDIT_OPTS:

 

SQL> select user_name, privilege

  2  from dba_priv_audit_opts

  3  /

 

no rows selected

 

SQL>

 

And connections by SYSTEM will no longer be recorded in SYS.AUD$:

 

SQL> select count(*) from sys.aud$

  2  /

 

  COUNT(*)

----------

         1

 

SQL> conn system/manager

Connected.

SQL> select count(*) from sys.aud$

  2  /

 

  COUNT(*)

----------

         1

 

SQL>

 

Unlike other objects owned by SYS, you are allowed to delete rows from SYS.AUD$.

 

SQL> delete sys.aud$

  2  /

 

1 row deleted.

 

SQL>

 

However, you should check first that it does not contain rows produced by your colleagues with separate audit requests!