26
Auditing Database Use

This chapter describes how to use the Oracle database server's auditing facilities, and contains these topics:

• Guidelines for Auditing
• What Information is Contained in the Audit Trail?
• Actions Audited by Default
• Auditing Administrative Users
• Managing the Audit Trail
• Fine-Grained Auditing
• Viewing Database Audit Trail Information

Guidelines for Auditing

This section describes guidelines for auditing and contains the following topics:

• Decide Whether to Use the Database or Operating System Audit Trail
• Keep Audited Information Manageable
• Guidelines for Auditing Suspicious Database Activity
• Guidelines for Auditing Normal Database Activity

Decide Whether to Use the Database or Operating System Audit Trail

The data dictionary of every database has a table named SYS.AUD$, commonly referred to as the database audit trail, that is designed to store entries auditing database statements, privileges, or schema objects.

You can optionally choose to store the database audit information to an operating system file. If your operating system has an audit trail that stores audit records generated by the operating system auditing facility, and Oracle is allowed to write to it, you can choose to direct the database audit entries to this file. For example, the Windows operating system allows Oracle to write audit records as events to the application event log.

Consider the advantages and disadvantages of using either the database or operating system audit trail to store database audit records.

Using the database audit trail offers the following advantages:

• You can view selected portions of the audit trail with the predefined audit trail views of the data dictionary.
• You can use Oracle tools (such as Oracle Reports) to generate audit reports.

Alternatively, your operating system audit trail may allow you to consolidate audit records from multiple sources including Oracle and other applications. Therefore, examining system activity might be more efficient because all audit records are in one place.

Keep Audited Information Manageable

Although auditing is relatively inexpensive, limit the number of audited events as much as possible. This minimizes the performance impact on the execution of statements that are audited, and minimizes the size of the audit trail.

Use the following general guidelines when devising an auditing strategy:

• Evaluate your purpose for auditing.

  After you have a clear understanding of the reasons for auditing, you can devise an appropriate auditing strategy and avoid unnecessary auditing.

  For example, suppose you are auditing to investigate suspicious database activity. This information by itself is not specific enough. What types of suspicious database activity do you suspect or have you noticed? A more focused auditing purpose might be to audit unauthorized deletions from arbitrary tables in the database. This purpose narrows the type of action being audited and the type of object being affected by the suspicious activity.

• Audit knowledgeably.

  Audit the minimum number of statements, users, or objects required to get the targeted information. This prevents unnecessary audit information from cluttering the meaningful information and consuming valuable space in the SYSTEM tablespace. Balance your need to gather sufficient security information with your ability to store and process it.

  For example, if you are auditing to gather information about database activity, determine exactly what types of activities you are tracking, audit only the activities of interest, and audit only for the amount of time necessary to gather the information you desire. Do not audit objects if you are only interested in each session's logical I/O information.

Guidelines for Auditing Suspicious Database Activity

When you audit to monitor suspicious database activity, use the following guidelines:

• Audit generally, then specifically.

  When starting to audit for suspicious database activity, it is common that not much information is available to target specific users or schema objects. Therefore, audit options must be set more generally at first. Once preliminary audit information is recorded and analyzed, the general audit options should be turned off and more specific audit options enabled. This process should continue until enough evidence is gathered to make concrete conclusions about the origin of the suspicious database activity.

• Protect the audit trail.

  When auditing for suspicious database activity, protect the audit trail so that audit information cannot be added, changed, or deleted without being audited.

  See Also: "Protecting the Audit Trail"

Guidelines for Auditing Normal Database Activity

When your purpose for auditing is to gather historical information about particular database activities, use the following guidelines:

• Audit only pertinent actions.

  To avoid cluttering meaningful information with useless audit records and reduce the amount of audit trail administration, only audit the targeted database activities.

• Archive audit records and purge the audit trail.

  After you have collected the required information, archive the audit records of interest and purge the audit trail of this information.

What Information is Contained in the Audit Trail?

Oracle can write records to either the database audit trail, an operating system file, or both. This section describes the makeup of this audit trail information.

Information Stored in the Database Audit Trail

The database audit trail, stored in the SYS.AUD$ table, contains different types of information, depending on the events audited and the auditing options set. The following information is always included in each audit trail record:

• Operating system login user name
• User name
• Session identifier
• Terminal identifier
• Name of the schema object accessed
• Operation performed or attempted
• Completion code of the operation
• Date and time stamp

The audit trail does not store information about any data values that might be involved in the audited statement. For example, old and new data values of updated rows are not stored when an UPDATE statement is audited. However, this specialized type of auditing can be performed using fine-grained auditing methods.

Information Stored in an Operating System File

The operating system file that contains the audit trail can contain any of the following:

• Audit records generated by the operating system
• Database audit trail records
• Database actions that are always audited
• Audit records for administrative users (SYS)

Audit trail records written to an operating system audit trail may contain encoded information, but this information can be decoded using data dictionary tables and error messages as follows:

Encoded Information	How to Decode
Action code	This describes the operation performed or attempted. The AUDIT_ACTIONS data dictionary table contains a list of these codes and their descriptions.
Privileges used	This describes any system privileges used to perform the operation. The SYSTEM_PRIVILEGE_MAP table lists all of these codes and their descriptions.
Completion code	This describes the result of the attempted operation. Successful operations return a value of zero; unsuccessful operations return the Oracle error code describing why the operation was unsuccessful. These codes are listed in Oracle9i Database Error Messages.

Actions Audited by Default

Regardless of whether database auditing is enabled, Oracle always audits certain database-related operations and writes them to the operating system audit file. These operations include the following:

• Connections to the instance with administrator privileges

  An audit record is generated that lists the operating system user connecting to Oracle as SYSOPER or SYSDBA. This provides for accountability of users with administrative privileges. Full auditing for these users can be enabled as explained in "Auditing Administrative Users".

• Database startup

  An audit record is generated that lists the operating system user starting the instance, the user's terminal identifier, the date and time stamp, and whether database auditing was enabled or disabled. This is stored in the operating system audit trail because the database audit trail is not available until after startup has successfully completed. Recording the state of database auditing at startup helps detect when an administrator has restarted a database with database auditing disabled (thus enabling the administrator to perform unaudited actions).

• Database shutdown

  An audit record is generated that lists the operating system user shutting down the instance, the user's terminal identifier, and the date and time stamp.

Auditing Administrative Users

Sessions for users who connect as SYS, this includes all users connecting as SYSDBA or SYSOPER, can be fully audited. Use the AUDIT_SYS_OPERATIONS initialization parameter to specify if user SYS is audited. For example, the following setting specifies that SYS is to be audited:

AUDIT_SYS_OPERATIONS = TRUE

A value of FALSE, which is the default, disables SYS auditing.

All audit records for SYS are written to the operating system file that contains the audit trail, and not to SYS.AUD$. All SYS issued SQL statements are audited indiscriminately and regardless of the setting of the AUDIT_TRAIL initialization parameter.

Consider the following SYS session:

CONNECT / AS SYSDBA;
ALTER SYSTEM FLUSH SHARED_POOL;
UPDATE salary SET base=1000 WHERE name='myname';

When SYS auditing is enabled, both the ALTER SYSTEM and UPDATE statements are displayed in the operating system audit file as follows:

Thu Jan 24 12:58:00 2002
ACTION: 'CONNECT'
DATABASE USER: '/'
OSPRIV: SYSDBA
CLIENT USER: scott
CLIENT TERMINAL: pts/2
STATUS: 0

Thu Jan 24 12:58:00 2002
ACTION: 'alter system flush shared_pool'
DATABASE USER: ''
OSPRIV: SYSDBA
CLIENT USER: scott
CLIENT TERMINAL: pts/2
STATUS: 0

Thu Jan 24 12:58:00 2002
ACTION: 'update salary set base=1000 where name='myname''
DATABASE USER: ''
OSPRIV: SYSDBA
CLIENT USER: scott
CLIENT TERMINAL: pts/2
STATUS: 0

Because of the superuser privileges available to users who connect as SYSDBA, Oracle recommends that DBAs rarely use this connection and only when necessary. Normal day to day maintenance activity can usually be done by DBAs assigned the DBA role.

Managing the Audit Trail

This section describes various aspects of managing audit trail information, and contains the following topics:

• Enabling and Disabling Auditing
• Setting Auditing Options
• Auditing in a Multi-Tier Environment
• Turning Off Audit Options
• Controlling the Growth and Size of the Audit Trail
• Protecting the Audit Trail

Enabling and Disabling Auditing

Any authorized database user can set statement, privilege, and object auditing options at any time, but Oracle does not generate audit information for the database audit trail unless database auditing is enabled. The security administrator is normally responsible for controlling auditing.

This section discusses the initialization parameters that enable and disable auditing.

Setting the AUDIT_TRAIL Initialization Parameter

Database auditing is enabled and disabled by the AUDIT_TRAIL initialization parameter in the database's initialization parameter file. The parameter can be set to the following values:

Parameter Value	Meaning
DB	Enables database auditing and directs all audit records to the database audit trail, except for records that are always written to the operating system audit trail
OS	Enables database auditing and directs all audit records to an operating system file
NONE	Disables auditing (This value is the default.)

Setting the AUDIT_FILE_DEST Initialization Parameter

The AUDIT_FILE_DEST initialization parameter specifies an operating system directory into which the audit trail is written when AUDIT_TRAIL=OS is specified. It is also the location to which mandatory auditing information is written and, if so specified by the AUDIT_SYS_OPERATIONS initialization parameter, audit records for user SYS.

If the AUDIT_FILE_DEST parameter is not specified, the default location is $ORACLE_HOME/rdbms/audit.

Setting Auditing Options

You specify auditing options using the AUDIT statement. The AUDIT statement allows you to set audit options at three levels:

Level	Effect
Statement	Causes auditing of specific SQL statements or groups of statements that affect a particular type of database object. For example, AUDIT TABLE audits the CREATE TABLE, TRUNCATE TABLE, COMMENT ON TABLE, and DELETE [FROM] TABLE statements.
Privilege	Audits SQL statements that are authorized by the specified system privilege. For Example, AUDIT CREATE ANY TRIGGER audits statements issued using the CREATE ANY TRIGGER system privilege.
Object	Audits specific statements on specific objects, such as ALTER TABLE on the emp table

To use the AUDIT statement to set statement and privilege options, you must have the AUDIT SYSTEM privilege. To use it to set object audit options, you must own the object to be audited or have the AUDIT ANY privilege.

Audit statements that set statement and privilege audit options can include a BY clause to specify a list of users or application proxies to limit the scope of the statement and privilege audit options.

When setting auditing options, you can also specify the following conditions for auditing:

• BY SESSION/BY ACCESS

  BY SESSION causes Oracle to write a single record for all SQL statements of the same type issued in the same session. BY ACCESS causes Oracle to write one record for each access.

  Note: If you are using an operating system file for the audit trail (AUDIT_FILE_DEST=OS), multiple records may still be written to the audit trail when BY SESSION is specified. This is because while Oracle can write to the operating system file, it is unable to read it to detect that it has already written an audit entry for the action.

• WHENEVER SUCCESSFUL/WHENEVER NOT SUCCESSFUL

  WHENEVER SUCCESSFUL chooses auditing only for statements that succeed. WHENEVER NOT SUCCESSFUL chooses auditing only for statements that fail or result in errors.

The implications of your choice of auditing option and specification of AUDIT statement clauses is discussed in subsequent sections.

A new database session picks up auditing options from the data dictionary when the session is created. These auditing options remain in force for the duration of the database connection. Setting new system or object auditing options causes all subsequent database sessions to use these options; existing sessions continue using the audit options in place at session creation.

Specifying Statement Auditing

Valid statement audit options that can be included in AUDIT and NOAUDIT statements are listed in the Oracle9i SQL Reference.

Two special cases of statement auditing are discussed in the following sections.

Auditing Connections and Disconnections

The SESSION statement option is unique because it does not generate an audit record when a particular type of statement is issued; this option generates a single audit record for each session created by connections to an instance. An audit record is inserted into the audit trail at connect time and updated at disconnect time. Cumulative information about a session such as connection time, disconnection time, logical and physical I/Os processed, and more is stored in a single audit record that corresponds to the session.

To audit all successful and unsuccessful connections to and disconnections from the database, regardless of user, BY SESSION (the default and only value for this option), enter the following statement:

AUDIT SESSION;

You can set this option selectively for individual users also, as in the next example:

AUDIT SESSION
BY scott, lori;

Auditing Statements That Fail Because an Object Does Not Exist

The NOT EXISTS statement option specifies auditing of all SQL statements that fail because the target object does not exist.

Specifying Privilege Auditing

Privilege audit options exactly match the corresponding system privileges. For example, the option to audit use of the DELETE ANY TABLE privilege is DELETE ANY TABLE. To turn this option on, you use a statement similar to the following example:

AUDIT DELETE ANY TABLE
    BY ACCESS
    WHENEVER NOT SUCCESSFUL;

Oracle's system privileges are listed in the Oracle9i SQL Reference.

To audit all successful and unsuccessful uses of the DELETE ANY TABLE system privilege, enter the following statement:

AUDIT DELETE ANY TABLE;

To audit all unsuccessful SELECT, INSERT, and DELETE statements on all tables and unsuccessful uses of the EXECUTE PROCEDURE system privilege, by all database users, and by individual audited statement, issue the following statement:

AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE
      BY ACCESS
      WHENEVER NOT SUCCESSFUL;

The AUDIT SYSTEM system privilege is required to set any statement or privilege audit option. Normally, the security administrator is the only user granted this system privilege.

Specifying Object Auditing

The Oracle9i SQL Reference lists valid object audit options and the schema object types for which each option is available.

A user can set any object audit option for the objects contained in the user's schema. The AUDIT ANY system privilege is required to set an object audit option for an object contained in another user's schema or to set the default object auditing option. Normally, the security administrator is the only user granted the AUDIT ANY privilege.

To audit all successful and unsuccessful DELETE statements on the scott.emp table, BY SESSION (the default value), enter the following statement:

AUDIT DELETE ON scott.emp;

To audit all successful SELECT, INSERT, and DELETE statements on the dept table owned by user jward, BY ACCESS, enter the following statement:

AUDIT SELECT, INSERT, DELETE
     ON jward.dept
     BY ACCESS
     WHENEVER SUCCESSFUL;

To set the default object auditing options to audit all unsuccessful SELECT statements, BY SESSION (the default), enter the following statement:

AUDIT SELECT
     ON DEFAULT
     WHENEVER NOT SUCCESSFUL;

Auditing in a Multi-Tier Environment

In a multi-tier environment, Oracle preserves the identity of the client through all tiers. This enables auditing of actions taken on behalf of the client. To do so, you use the BY proxy clause in your AUDIT statement.

This clause allows you a few options. You can:

• Audit SQL statements issued by the specified proxy on its own behalf
• Audit statements executed on behalf of a specified user or users
• Audit all statements executed on behalf of any user

The following example audits SELECT TABLE statements issued on behalf of client jackson by the proxy application server appserve.

AUDIT SELECT TABLE
    BY appserve ON BEHALF OF jackson; 

Turning Off Audit Options

The NOAUDIT statement turns off the various audit options of Oracle. Use it to reset statement and privilege audit options, and object audit options. A NOAUDIT statement that sets statement and privilege audit options can include the BY user or BY proxy option to specify a list of users to limit the scope of the statement and privilege audit options.

You can use a NOAUDIT statement to disable an audit option selectively using the WHENEVER clause. If the clause is not specified, the auditing option is disabled entirely, for both successful and unsuccessful cases.

The BY SESSION/BY ACCESS option pair is not supported by the NOAUDIT statement; audit options, no matter how they were turned on, are turned off by an appropriate NOAUDIT statement.

Turning Off Statement and Privilege Auditing

The following statements turn off the corresponding audit options:

NOAUDIT session;
NOAUDIT session BY scott, lori;
NOAUDIT DELETE ANY TABLE;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,
    EXECUTE PROCEDURE;

The following statement turns off all statement audit options:

NOAUDIT ALL;

The following statement turns off all privilege audit options:

NOAUDIT ALL PRIVILEGES;

To disable statement or privilege auditing options, you must have the AUDIT SYSTEM system privilege.

Turning Off Object Auditing

The following statements turn off the corresponding auditing options:

NOAUDIT DELETE
   ON emp;
NOAUDIT SELECT, INSERT, DELETE
   ON jward.dept;

Furthermore, to turn off all object audit options on the emp table, enter the following statement:

NOAUDIT ALL
   ON emp;

To turn off all default object audit options, enter the following statement:

NOAUDIT ALL
   ON DEFAULT;

All schema objects created before this NOAUDIT statement is issued continue to use the default object audit options in effect at the time of their creation, unless overridden by an explicit NOAUDIT statement after their creation.

To disable object audit options for a specific object, you must be the owner of the schema object. To disable the object audit options of an object in another user's schema or to disable default object audit options, you must have the AUDIT ANY system privilege. A user with privileges to disable object audit options of an object can override the options set by any user.

Controlling the Growth and Size of the Audit Trail

If the audit trail becomes completely full and no more audit records can be inserted, audited statements cannot be successfully executed until the audit trail is purged. Warnings are returned to all users that issue audited statements. Therefore, the security administrator must control the growth and size of the audit trail.

When auditing is enabled and audit records are being generated, the audit trail grows according to two factors:

• The number of audit options turned on
• The frequency of execution of audited statements

To control the growth of the audit trail, you can use the following methods:

• Enable and disable database auditing. If it is enabled, audit records are generated and stored in the audit trail; if it is disabled, audit records are not generated.
• Be very selective about the audit options that are turned on. If more selective auditing is performed, useless or unnecessary audit information is not generated and stored in the audit trail.
• Tightly control the ability to perform object auditing. This can be done two different ways:
  • A security administrator owns all objects and the AUDIT ANY system privilege is never granted to any other user. Alternatively, all schema objects can belong to a schema for which the corresponding user does not have CREATE SESSION privilege.
  • All objects are contained in schemas that do not correspond to real database users (that is, the CREATE SESSION privilege is not granted to the corresponding user) and the security administrator is the only user granted the AUDIT ANY system privilege.

  In both scenarios, object auditing is controlled entirely by the security administrator.

The maximum size of the database audit trail (SYS.AUD$ table) is determined by the default storage parameters of the SYSTEM tablespace, in which it is stored. You should not move SYS.AUD$ to another tablespace as a means of controlling the growth and size of the audit trail. However, you can modify the storage parameters for SYS.AUD$.

Purging Audit Records from the Audit Trail

After auditing is enabled for some time, the security administrator may want to delete records from the database audit trail both to free audit trail space and to facilitate audit trail management.

For example, to delete all audit records from the audit trail, enter the following statement:

DELETE FROM SYS.AUD$;

Alternatively, to delete all audit records from the audit trail generated as a result of auditing the table emp, enter the following statement:

DELETE FROM SYS.AUD$
     WHERE obj$name='EMP';

If audit trail information must be archived for historical purposes, the security administrator can copy the relevant records to a normal database table (for example, using INSERT INTO table SELECT ... FROM SYS.AUD$ ...) or export the audit trail table to an operating system file.

Only the user SYS, a user who has the DELETE ANY TABLE privilege, or a user to whom SYS has granted DELETE privilege on SYS.AUD$ can delete records from the database audit trail.

Reducing the Size of the Audit Trail

As with any database table, after records are deleted from the database audit trail, the extents allocated for this table still exist.

If the database audit trail has many extents allocated for it, but many of them are not being used, the space allocated to the database audit trail can be reduced by following these steps:

1. If you want to save information currently in the audit trail, copy it to another database table or export it using the EXPORT utility.
2. Connect as a user with administrator privileges.
3. Truncate SYS.AUD$ using the TRUNCATE statement.
4. Reload archived audit trail records generated from Step 1.

The new version of SYS.AUD$ is allocated only as many extents as are necessary to contain current audit trail records.

Protecting the Audit Trail

When auditing for suspicious database activity, protect the integrity of the audit trail's records to guarantee the accuracy and completeness of the auditing information.

To protect the database audit trail from unauthorized deletions, grant the DELETE ANY TABLE system privilege to security administrators only.

To audit changes made to the database audit trail, use the following statement:

AUDIT INSERT, UPDATE, DELETE
    ON sys.aud$
    BY ACCESS;

Audit records generated as a result of object audit options set for the SYS.AUD$ table can only be deleted from the audit trail by someone connected with administrator privileges, which itself has protection against unauthorized use.

Fine-Grained Auditing

In the auditing methods discussed so far, a fixed set of facts is recorded in the audit trail. Additionally, audit options can only be set to monitor access of objects or privileges. No support has been discussed for obtaining more specific information about the environment or query results, nor any mechanism to specify audit conditions in order to minimize false audits. For these purposes, Oracle offers fine-grained auditing.

Fine-grained auditing allows the monitoring of data access based on content. For example, a central tax authority needs to track access to tax returns to guard against employee snooping. Enough detail is wanted to be able to determine what data was accessed, not just that SELECT privilege was used by a specific user on a particular table. Fine-grained auditing provides this functionality.

In general, fine-grained auditing policy is based on simple user-defined SQL predicates on table objects as conditions for selective auditing. During fetching, whenever policy conditions are met for a returning row, the query is audited. Later, Oracle executes user-defined audit event handlers using autonomous transactions to process the event.

Fine-grained auditing can be implemented in user applications using the DBMS_FGA package or by using database triggers.

Viewing Database Audit Trail Information

The database audit trail (SYS.AUD$) is a single table in each Oracle database's data dictionary. To help you meaningfully view auditing information in this table, several predefined views are available. They must be created by you. You can later delete them if you decide not to use auditing.

Creating the Audit Trail Views

The following views (except STMT_AUDIT_OPTION_MAP) are created by the CATALOG.SQL and CATAUDIT.SQL scripts:

View	Description
STMT_AUDIT_OPTION_MAP	Contains information about auditing option type codes. Created by the SQL.BSQ script at CREATE DATABASE time.
AUDIT_ACTIONS	Contains descriptions for audit trail action type codes
ALL_DEF_AUDIT_OPTS	Contains default object-auditing options that will be applied when objects are created
DBA_STMT_AUDIT_OPTS	Describes current system auditing options across the system and by user
DBA_PRIV_AUDIT_OPTS	Describes current system privileges being audited across the system and by user
DBA_OBJ_AUDIT_OPTS USER_OBJ_AUDIT_OPTS	Describes auditing options on all objects. USER view describes auditing options on all objects owned by the current user.
DBA_AUDIT_TRAIL USER_AUDIT_TRAIL	Lists all audit trail entries. USER view shows audit trail entries relating to current user.
DBA_AUDIT_OBJECT USER_AUDIT_OBJECT	Contains audit trail records for all objects in the system. USER view lists audit trail records for statements concerning objects that are accessible to the current user.
DBA_AUDIT_SESSION USER_AUDIT_SESSION	Lists all audit trail records concerning CONNECT and DISCONNECT. USER view lists all audit trail records concerning connections and disconnections for the current user.
DBA_AUDIT_STATEMENT USER_AUDIT_STATEMENT	Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database, or for the USER view, issued by the user
DBA_AUDIT_EXISTS	Lists audit trail entries produced BY AUDIT NOT EXISTS
The following views are used for fine-grained auditing:
DBA_AUDIT_POLICIES	Shows all the auditing policies on the system.
DBA_FGA_AUDIT_TRAIL	Lists audit trail records for value-based auditing.

Deleting the Audit Trail Views

If you disable auditing and no longer need the audit trail views, delete them by connecting to the database as SYS and running the script file CATNOAUD.SQL. The name and location of the CATNOAUD.SQL script are operating system dependent.

Using Audit Trail Views to Investigate Suspicious Activities

This section offers examples that demonstrate how to examine and interpret the information in the audit trail. Consider the following situation.

You would like to audit the 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.

You suspect the users jward and swilliams of several of these detrimental actions.

To enable your investigation, you issue the following statements (in order):

AUDIT ALTER, INDEX, RENAME ON DEFAULT
    BY SESSION;
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 display the information relevant to your investigation that can be viewed using the audit trail views in the data dictionary:

• 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

Listing Active Statement Audit Options

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

SELECT * FROM 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 DBA_PRIV_AUDIT_OPTS;

USER_NAME           PRIVILEGE            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 whose name starts with the characters emp and which are contained in scott's schema:

SELECT * FROM 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      -/- -/- -/- A/- -/- S/S -/- -/- ...

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 * FROM 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 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