Friday, March 23, 2012

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!

No comments: