This section describes the steps involved in setting up auditing within an Oracle database. If you are going to monitor an Oracle database with a trace component rule set, you will need to perform these steps before events will be captured.Before configuring auditing it is suggested you review the Auditing Database Use section of the Oracle Database Administrator's Guide. This document provides an overview of Oracle's auditing functionality, as well as basic concepts and guidelines for auditing configurations. Note that 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.
When you create a component to monitor an Oracle database, you will configure that component with a database user that can log into the database to read the audit trail. This user account should only have read only access to the audit tables only. This user is different than the user that the Configuration Change Console Server uses for its repository.
On the machine on where the Oracle database that will be monitored 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. Note that this should not be a user used by an actual person within your infrastructure. Also, this user only needs read access to audit related tables.
Select the Security tab. Add the AUDIT SYSTEM privilege to the user by selecting it from 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.
To turn on user privileges, 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.
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:
| 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.
The following statement audits user sessions of users Bill and Lori.
AUDIT SESSION BY scott, lori;
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;
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;
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 component rule set