G Oracle Database Auditing

This appendix describes the steps involved in setting up auditing within an Oracle database.

Before configuring auditing it is suggested you review the Appendix - Auditing Database Use section of the Oracle 10g Database Administrator's Guide (http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90117/audit.htm). This document provides an overview of Oracle's auditing functionality, as well as basic concepts and guidelines for auditing configurations. Note that this document requires an Oracle login. If you do not have a login, simply create one through the Oracle site. (https://profile.oracle.com/jsp/reg/createUser.jsp?src=1180585&act=5&language=en)

This document does not cover all details of configuring and fine tuning the Oracle audit system. Instead, this document serves as an example of the basic steps involved to configure the Oracle audit system, and how to use the Oracle Audit Monitor in conjunction with the Configuration Change Console Compliance Solution.

Setting Auditing User Privileges

The Oracle Database user used with the Configuration Change Console compliance solution requires specific user permissions in order to run audit statements within the Oracle database, and thus configure auditing of database events.

On the machine on where Oracle is installed or remotely:

Start the Oracle Enterprise Manager Console.
From the main navigation tree select the database instance you wish to audit. (Network -> Databases -> Database Name)
Log into the database as the system user.
From the navigation pane navigate to Network -> Databases -> Database Name -> Security -> Users. Select the user you will use for the Configuration Change Console compliance solution. Note that this should not be a user used by an actual person within your infrastructure.
Select the Security tab. Add the AUDIT SYSTEM privilege to the user by selecting it form the Available window and clicking the adjacent down-arrow icon. Optionally do the same for the AUDIT ALL permission. See the following section, Specifying Auditing Options for more information regarding the two permissions. Click Apply.

Turning on Auditing

To turn on auditing, follow these steps:

Start the Oracle Enterprise Manager Console.
From the main navigation tree select the database instance you wish to audit. (Network --> Databases --> Database Name).
Log in to the database as a sys user, connecting as SYSDBA.
From the navigation pane select Network --> Databases --> Database Name --> Instance --> Configuration
On the General tab, to the right of the navigation pane, click the All Initialization Parameters… button.
Locate the audit_trail parameter listing. Change the value from None to DB. Click Apply.
This change will require a restart of the database. Select the appropriate restart option and click OK.

Specifying Audit Options

Through SQL plus, an Oracle DBA can use audit and noaudit statements to configure audit options for the database.

The audit statement allows you to set audit options at three levels:

Table G-1 Audit Option Levels

Level	Effect
Statement	Audits 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 executed under the umbrella of a 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 employee table

In order to use the audit statement to set statement and privilege auditing options a DBA must be assigned AUDIT SYSTEM privileges. In order to use the audit statement to set object audit options, the DBA must own the object to be audited or be assigned the AUDIT ANY privilege within Oracle. Privilege assignments are covered in the following section.

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

Some examples of audit statements follow below. Feel free to use these as a basis for the audit settings you specify within your database. Once all audit settings are in place you can create application policies, using the Oracle (SQL Trace) agent module with which to monitor the Oracle database instance.

Statement Audit Options (User Sessions)

The following statement audits user sessions of users Bill and Lori:

AUDIT SESSION
BY scott, lori;

Privilege Audit Options

The following statement audits all successful and unsuccessful uses of the DELETE ANY TABLE system privilege:

AUDIT DELETE ANY TABLE
    BY ACCESS
    WHENEVER NOT SUCCESSFUL;

Object Audit Options

The following statement audits all successful SELECT, INSERT, and DELETE statements on the dept table owned by user jward:

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

Example Oracle Audit Monitor Configurations

The following command audits all basic statements. Extra statements are not audited.

Audit all by access;

The following statement audits all extra statements:

audit ALTER SEQUENCE, 
        ALTER TABLE, 
        DELETE TABLE, 
        EXECUTE PROCEDURE, 
        GRANT DIRECTORY, 
        GRANT PROCEDURE, 
        GRANT SEQUENCE, 
        GRANT TABLE,
        GRANT TYPE,
        INSERT TABLE,
        LOCK TABLE,
        UPDATE TABLE
        by access;

The following command displays audit settings for statements:

SELECT * FROM DBA_STMT_AUDIT_OPTS;

Once you have specified your audit configuration you can then set up a SQL Trace application policy.