SYS.AUD$: The Audit Trail Table

The name of the audit trail table in the database is SYS.AUD$. A large number of columns compose this table, which contains all recorded database audit information. The following example shows a simple select from this table:

SQL> select sessionid, userid,

 2 to_char(timestamp#,’mm/dd/yy hh;mi;ss’)

 3 from sys.aud$

 4 /

SESSIONID USERID            TO_CHAR(TIMESTAMP

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

    84 RYAN                 04/04/98 05:24:05

When auditing is activated, Oracle generates an audit record for each audited event, with the following default information:

•  The username associated with the audited event.

•  The action code of the audited event.

•  The applicable object(s) audited.

•  The date and timestamp of the audited event.

Creating and Deleting the Audit Trail Views

The audit trail views of SYS.AUD$ are created by simply running a script provided by Oracle Corporation. The following SQL script is used:

CATAUDIT.SQL

Regardless of the operating system platform on the host server, this script is stored under ORACLE_HOME or the directory in which the Oracle software was installed. On a UNIX system, the location of this script is $ORACLE_HOME/rdbms/admin. On a Windows NT platform, the location is C:\ORANT\RDBSM80\ADMIN.

To remove the audit trail data dictionary views, run the following script, which resides locally with the previous script:

CATNOAUD.SQL

---

NOTE:  
These scripts for creating and dropping the data dictionary views for auditing must be run while you’re connected to the database as the SYS user account.

---

The Audit Trail Data Dictionary Views

The following views are accessible only by the DBA or privileged user:

    SYS.DBA_STMT_AUDIT_OPTS

    SYS.DBA_OBJ_AUDIT_OPTS

    SYS.DBA_AUDIT_TRAIL

    SYS.DBA_AUDIT_SESSION

    SYS.DBA_AUDIT_STATEMENT

    SYS.DBA_AUDIT_OBJECT

    SYS.DBA_AUDIT_EXISTS

The following views are accessible by every Oracle user:

    SYS.STMT_AUDIT_OPTION_MAP

    SYS.AUDIT_ACTIONS

    SYS.ALL_DEF_AUDIT_OPTS

    SYS.USER_OBJ_AUDIT_OPTS

    SYS.USER_AUDIT_TRAIL

    SYS.USER_AUDIT_SESSION

    SYS.USER_AUDIT_STATEMENT

    SYS.USER_AUDIT_OBJECT

    SYS.USER_TAB_AUDIT_OPS

If a non-DBA attempts to access the DBA_ views, the following results are received:

SQL> desc dba_audit_session

ERROR:

ORA-04043: object SYS.DBA_AUDIT_SESSION does not exist





SQL> desc user_audit_session

 Name              Null?    Type

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

 OS_USERNAME                VARCHAR2(255)

 USERNAME                   VARCHAR2(30)

 USERHOST                   VARCHAR2(128)

 TERMINAL                   VARCHAR2(255)

 TIMESTAMP         NOT NULL DATE

 ACTION_NAME                VARCHAR2(27)

 LOGOFF_TIME                DATE

 LOGOFF_LREAD               NUMBER

 LOGOFF_PREAD               NUMBER

 LOGOFF_LWRITE              NUMBER

 LOGOFF_DLOCK               VARCHAR2(40)

 SESSIONID         NOT NULL NUMBER

 RETURNCODE        NOT NULL NUMBER

 SESSION_LABEL              RAW MLSLABEL

The AUDIT Command

Once auditing has been enabled, the SQL command AUDIT is used to activate certain audit options, which will be discussed in the following sections. To turn off audit options that have been previously specified, use the NOAUDIT command. These commands are typically submitted at the command prompt, such as in SQL*Plus or Server Manager. The syntax for the AUDIT and NOAUDIT commands is shown in the following appropriate sections, which also discuss the different audit options.

Types of Auditing

There are various types of auditing that may integrated into an Oracle database. Audit information can be taken from general login information or from specific changes that occur within specific database objects. All actions can be audited, and the task of auditing can be (and should be) tailored specifically to the needs of the database system to ensure proper use of the system and discourage unauthorized database activity through auditing, and do so in a cost-effective manner.

The types of auditing discussed are

•  Session auditing

•  Statement auditing

•  Privilege auditing

•  Object auditing

---

NOTE:  
As mentioned earlier, Oracle automatically audits database instance startup, shutdown, and any connections made to the database with administrative privileges.

---

Audit Options

The following sections outline the various auditing options available. The first three (session, statement, and privilege auditing) are auditing options that reside on the database system level. The fourth, object auditing, resides on the schema or object level. As will be explained, certain privileges are required for the different types of auditing.

Session Audits

Session auditing is the process of auditing user sessions. This auditing option is technically part of SQL statement auditing, and mainly involves recording the success/failure of each individual who connects or attempts to connect to the database. By default, both successful and unsuccessful login attempts will generate an audit record. If you prefer, you can specify that only successful login attempts will generate audit records.

Here’s the syntax to audit user logins:

AUDIT SESSION

[BY username [,username] ]

[BY SESSION | ACCESS ]

[WHENEVER [NOT] SUCCESSFUL];

To activate login auditing:

SQL> audit session;



Audit succeeded.

To audit unsuccessful SYSTEM login attempts:

SQL> audit session

  2  by system

  3  whenever not successful;



Audit succeeded.

To disable the previous option:

SQL> noaudit session

  2  by system

  3  whenever not successful;



Noaudit succeeded.

To audit all database events:

SQL> audit all;



Audit succeeded.

To disable the previous option:

SQL> noaudit all;



Noaudit succeeded.