Oracle7 Server Concepts

Focusing Statement, Privilege, and Object Auditing

Oracle allows statement, privilege, and object auditing to be focused in two areas:

successful and unsuccessful executions of the audited SQL statement

BY SESSION and BY ACCESS auditing

In addition, you can enable statement and privilege auditing for specific users or for all users in the database.

Auditing Successful and Unsuccessful Statement Executions

For statement, privilege, and object auditing, Oracle allows the selective auditing of successful executions of statements, unsuccessful attempts to execute statements, or both. Therefore, you can monitor actions even if the audited statements do not complete successfully.

You can audit an unsuccessful statement execution only if a valid SQL statement is issued but fails because of lack of proper authorization or because it references a non-existent object. Statements that failed to execute because they simply were not valid cannot be audited. For example, an enabled privilege auditing option set to audit unsuccessful statement executions audits statements that use the target system privilege but have failed for other reasons (for example, CREATE TABLE is set, but a CREATE TABLE statement fails due to lack of quota for the specified tablespace).

Using either form of the AUDIT command, you can include

the WHENEVER SUCCESSFUL option, to audit only successful executions of the audited statement

the WHENEVER NOT SUCCESSFUL option, to audit only unsuccessful executions of the audited statement

neither of the previous options, to audit both successful and unsuccessful executions of the audited statement

Auditing BY SESSION versus BY ACCESS

Most auditing options can be set to indicate how audit records should be generated if the audited statement is issued multiple times in a single user session. These sections describe the distinction between the BY SESSION and BY ACCESS options of the AUDIT command.

BY SESSION

BY SESSION inserts only one audit record in the audit trail, per user and object, per session that includes an audited action. This applies regardless of whether the audit is of an object, a statement, or a privilege.

To demonstrate how the BY SESSION option allows the generation of audit records, consider the following two examples.

Example 1 Assume the following:

The SELECT TABLE statement auditing option is set BY SESSION.

JWARD connects to the database and issues five SELECT statements against the table named DEPT and then disconnects from the database.

SWILLIAMS connects to the database and issues three SELECT statements against the table EMP and then disconnects from the database.

In this case, the audit trail will contain two audit records for the eight SELECT statements (one for each session that issued a SELECT statement).

Example 2 Alternatively, assume the following:

The SELECT TABLE statement auditing option is set BY SESSION.

JWARD connects to the database and issues five SELECT statements against the table named DEPT, three SELECT statements against the table EMP, and then disconnects from the database.

In this case, the audit trail will contain two records (one for each object against which the user issued a SELECT statement in a session).

Although you can use the BY SESSION option when directing audit records to the operating system audit trail, this generates and stores an audit record each time an access is made. Therefore, in this auditing configuration, BY SESSION is equivalent to BY ACCESS.

Note: A session is the time between when a user connects to and disconnects from an Oracle database.

BY ACCESS

Setting audit BY ACCESS inserts one audit record into the audit trail for each execution of an auditable within a cursor. Events that cause cursors to be reused include the following:

an application, such as Oracle Forms, holding a cursor open for reuse

subsequent execution of a cursor using new bind variables

statements executed within PL/SQL loops where the PL/SQL engine optimizes the statements to reuse a single cursor

Note that auditing is NOT affected by whether a cursor is shared; each user creates her or his own audit trail records on first execution of the cursor.

Example Assume the following:

The SELECT TABLE statement auditing option is set BY ACCESS.

JWARD connects to the database and issues five SELECT statements against the table named DEPT and then disconnects from the database.

SWILLIAMS connects to the database and issues three SELECT statements against the table DEPT and then disconnects from the database.

The audit trail contains eight records for the eight SELECT statements.

Defaults and Excluded Operations

The AUDIT command allows you to specify either BY SESSION or BY ACCESS. However, several audit options can only be set BY ACCESS, including

all statement audit options that audit DDL statements

all privilege audit options that audit DDL statements

For all other audit options, BY SESSION is used by default.

Auditing By User

Statement and privilege audit options can either be broad, auditing statements issued by any user, or focused, auditing statements issued by a specific list of users. By focusing on specific users, you can minimize the number of audit records generated.