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!
