Oracle7 Server Administrator's Guide

Viewing Database Audit Trail Information

This section offers examples that demonstrate how to examine and interpret the information in the audit trail, and includes the following topics:

Listing Active Statement Audit Options

Listing Active Privilege Audit Options

Listing Active Object Audit Options for Specific Objects

Listing Default Object Audit Options

Listing Audit Records

Listing Audit Records for the AUDIT SESSION Option

You may have to audit a database for the following suspicious activities:

Passwords, tablespace settings, and quotas for some database users are being altered without authorization.

A high number of deadlocks are occurring, most likely because of users acquiring exclusive table locks.

Rows are arbitrarily being deleted from the EMP table in SCOTT's schema.

As an example, say that you suspect the users JWARD and SWILLIAMS of several of these detrimental actions. The database administrator may then issue the following statements (in order):

AUDIT ALTER, INDEX, RENAME ON DEFAULT

   BY SESSION;

CREATE TABLE scott.emp . . . ;

CREATE VIEW scott.employee AS SELECT * FROM scott.emp;

AUDIT SESSION BY jward, swilliams;

AUDIT ALTER USER;

AUDIT LOCK TABLE

   BY ACCESS

   WHENEVER SUCCESSFUL;

AUDIT DELETE ON scott.emp

   BY ACCESS

   WHENEVER SUCCESSFUL;

The following statements are subsequently issued by the user JWARD:

ALTER USER tsmith QUOTA 0 ON users;

DROP USER djones;

The following statements are subsequently issued by the user SWILLIAMS:

LOCK TABLE scott.emp IN EXCLUSIVE MODE;

DELETE FROM scott.emp WHERE mgr = 7698;

ALTER TABLE scott.emp ALLOCATE EXTENT (SIZE 100K);

CREATE INDEX scott.ename_index ON scott.emp (ename);

CREATE PROCEDURE scott.fire_employee (empid NUMBER) AS

BEGIN

   DELETE FROM scott.emp WHERE empno = empid;

END;

EXECUTE scott.fire_employee(7902);

The following sections show the information that can be listed using the audit trail views in the data dictionary.

Listing Active Statement Audit Options

The following query returns all the statement audit options that are set:

SELECT * FROM sys.dba_stmt_audit_opts;

USER_NAME            AUDIT_OPTION         SUCCESS    FAILURE

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

JWARD                SESSION              BY SESSION BY SESSION

SWILLIAMS            SESSION              BY SESSION BY SESSION

                     LOCK TABLE           BY ACCESS  NOT SET

Notice that the view reveals the statement audit options set, whether they are set for success or failure (or both), and whether they are set for BY SESSION or BY ACCESS.

Listing Active Privilege Audit Options

The following query returns all the privilege audit options that are set:

SELECT * FROM sys.dba_priv_audit_opts;

USER_NAME            AUDIT_OPTION         SUCCESS    FAILURE

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

ALTER USER           BY SESSION BY SESSION

Listing Active Object Audit Options for Specific Objects

The following query returns all audit options set for any objects contained in SCOTT's schema:

SELECT * FROM sys.dba_obj_audit_opts

   WHERE owner = 'SCOTT' AND object_name LIKE 'EMP%';

OWNER  OBJECT_NAME OBJECT_TY ALT AUD COM DEL GRA IND INS LOC ...

------ ----------- --------- --- --- --- --- --- --- --- --- ...

SCOTT  EMP         TABLE     S/S -/- -/- A/- -/- S/S -/- -/- ...

SCOTT  EMPLOYEE    VIEW      -/- -/- -/- -/- -/- -/- -/- -/- ...

Notice that the view returns information about all the audit options for the specified object. The information in the view is interpreted as follows:

The character "-" indicates that the audit option is not set.

The character "S" indicates that the audit option is set, BY SESSION.

The character "A" indicates that the audit option is set, BY ACCESS.

Each audit option has two possible settings, WHENEVER SUCCESSFUL and WHENEVER NOT SUCCESSFUL, separated by "/". For example, the DELETE audit option for SCOTT.EMP is set BY ACCESS for successful delete statements and not set at all for unsuccessful delete statements.

Listing Default Object Audit Options

The following query returns all default object audit options:

SELECT * FROM all_def_audit_opts;

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE

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

S/S -/- -/- -/- -/- S/S -/- -/- S/S -/- -/- -/- -/-

Notice that the view returns information similar to the USER_OBJ_AUDIT_OPTS and DBA_OBJ_AUDIT_OPTS views (see previous example).

Listing Audit Records

The following query lists audit records generated by statement and object audit options:

SELECT username, obj_name, action_name, ses_actions

   FROM sys.dba_audit_object;

Listing Audit Records for the AUDIT SESSION Option

The following query lists audit information corresponding to the AUDIT SESSION statement audit option:

SELECT username, logoff_time, logoff_lread, logoff_pread,

   logoff_lwrite, logoff_dlock

   FROM sys.dba_audit_session;

USERNAME   LOGOFF_TI LOGOFF_LRE LOGOFF_PRE LOGOFF_LWR LOGOFF_DLO

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

JWARD      02-AUG-91         53          2         24 0

SWILLIAMS  02-AUG-91       3337        256        630 0