Oracle7 Server Concepts

Introduction to Auditing

Auditing is the monitoring and recording of selected user database actions. Auditing is normally used to

investigate suspicious activity. For example, if an unauthorized user is deleting data from tables, the security administrator might decide to audit all connections to the database and all successful and unsuccessful deletions of rows from all tables in the database.

monitor and gather data about specific database activities. For example, the database administrator can gather statistics about which tables are being updated, how many logical I/Os are performed, or how many concurrent users connect at peak times.

Auditing Features

These sections outline the features of the Oracle auditing mechanism.

Types of Auditing

Oracle supports three general types of auditing:

statement auditing The selective auditing of SQL statements with respect to only the type of statement, not the specific objects on which it operates. Statement auditing options are typically broad, auditing the use of several types of related actions per option; for example, AUDIT TABLE, which tracks several DDL statements regardless of the table on which they are issued. You can set statement auditing to audit selected users or every user in the database.
privilege auditing The selective auditing of the use of powerful system privileges to perform corresponding actions, such as AUDIT CREATE TABLE. Privilege auditing is more focused than statement auditing, auditing only the use of the target privilege. You can set privilege auditing to audit a selected user or every user in the database.
object auditing The selective auditing of specific statements on a particular schema object, such as AUDIT SELECT ON EMP. Object auditing is very focused, auditing only a specific statement on a specific object. Object auditing always applies to all users of the database.

You can set audit options to determine the type of audit information that is collected.

Focus of Auditing

Oracle allows audit options to be focused or broad in the following areas:

audit successful statement executions, unsuccessful statement executions, or both

audit statement executions once per user session or once every time the statement is executed

audit activities of all users or of a specific user

Audit Records and the Audit Trail

Audit records include such information as the operation that was audited, the user performing the operation, and the date/time of the operation. Audit records can be stored in either a data dictionary table, called the audit trail, or an operating system audit trail.

The database audit trail is a single table named AUD$ in the SYS schema of each Oracle database's data dictionary. Several predefined views are provided to help you use this information. Instructions for creating and using these views are included in the Oracle7 Server Administrator's Guide.

Depending on the events audited and the auditing options set, the audit trail records can contain different types of information. The following information is always included in each audit trail record, provided that the information is meaningful to the particular audit action:

the user name

the session identifier

the terminal identifier

the name of the object accessed

the operation performed or attempted

the completion code of the operation

the date and time stamp

the system privileges used (including MAC privileges for Trusted Oracle)

the label of the user session (for Trusted Oracle only)

the label of the object accessed (for Trusted Oracle only)

Audit trail records written to the OS audit trail contain some encodings that are not human readable. These can be decoded as follows:

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, while unsuccessful operations return the Oracle error code describing why the operation was unsuccessful. These codes are listed in Oracle7 Server Messages.

Auditing Mechanisms

These sections explain the mechanisms used by the Oracle auditing features.

When Are Audit Records Generated?

Oracle allows the recording of audit information to be enabled or disabled. This functionality allows audit options to be set by any authorized database user at any time, but reserves control of recording audit information for the security administrator. Instructions on enabling and disabling auditing are included in the Oracle7 Server Administrator's Guide.

Assuming auditing is enabled in the database, an audit record is generated during the execute phase of statement execution.

Note: If you are not familiar with the different phases of SQL statement processing and shared SQL, see Chapter 11, "SQL and PL/SQL", for background information concerning the following discussion.

SQL statements inside PL/SQL program units are individually audited, as necessary, when the program unit is executed.

The generation and insertion of an audit trail record is independent of a user's transaction; therefore, if a user's transaction is rolled back, the audit trail record remains committed.

Note: Audit records are never generated by sessions established by the user SYS or connections as INTERNAL. Connections by these users bypass certain internal features of Oracle to allow specific administrative operations to occur (for example, database startup, shutdown, recovery, and so on).

Events Always Audited to the Operating System Audit Trail

Regardless of whether database auditing is enabled, the Oracle Server will always audit certain database-related actions into the operating system audit trail. These events include the following:

Instance startup An audit record is generated that details the OS user starting the instance, his terminal identifier, the date and time stamp, and whether database auditing was enabled or disabled. This is audited into the OS audit trail because the database audit trail is not available until after startup has successfully completed. Recording the state of database auditing at startup further prevents an administrator from restarting a database with database auditing disabled so that they are able to perform unaudited actions.
Instance shutdown An audit record is generated that details the OS user shutting down the instance, her terminal identifier, the date and time stamp.
Connections to the database as INTERNAL An audit record is generated that details the OS user connecting to Oracle as INTERNAL. This provides accountability of users connected as INTERNAL.

On operating systems that do not make an audit trail accessible to Oracle, these audit trail records are placed in an Oracle audit trail file in the same directory as background process trace files.

Additional Information: See your operating system-specific Oracle documentation for more information about the operating system audit trail.

When Do Audit Options Take Effect?

Statement and privilege audit options in effect at the time a database user connects to the database remain in effect for the duration of the session. A session does not see the effects of statement audit options being set or changed. A database user only adheres to modified statement or privilege audit options when the current session is ended and a new session is created. On the other hand, changes in object audit options become effective for current sessions immediately.

Auditing in a Distributed Database

Auditing is site autonomous; an instance audits only the statements issued by directly connected users. A local Oracle node cannot audit actions that take place in a remote database. Because remote connections are established via the user account of a database link, the remote Oracle node audits the statements issued via the database link's connection. See Chapter 21, "Distributed Databases", for more information about distributed databases and database links.

Auditing to the OS Audit Trail

Both Oracle7 and Trusted Oracle7 allow audit trail records to be directed to an operating system audit trail on platforms where the OS makes such an audit trail available to Oracle. On some other operating systems, these audit records are written to a file outside the database, with a format similar to other Oracle trace files.

Additional Information: See your platform-specific Oracle documentation to see if this feature has been implemented on your operating system.

Trusted Oracle and Oracle allow certain actions that are always audited to continue even when the operating system audit trail, or the operating system file containing audit records, is unable to record the audit record. The normal cause of this is that the operating system audit trail, or the file system, is full and unable to accept new records.

When configured with OS auditing, system administrators should ensure that the audit trail or the file system does not fill completely. Most operating systems provide extensive measures to provide administrators with sufficient information and warning to ensure this does not occur. Furthermore, configuring auditing to use the database audit trail removes this vulnerability, as the Oracle Server prevents audited events from occurring if the audit trail is unable to accept the audit record for the statement.

statement auditing	The selective auditing of SQL statements with respect to only the type of statement, not the specific objects on which it operates. Statement auditing options are typically broad, auditing the use of several types of related actions per option; for example, AUDIT TABLE, which tracks several DDL statements regardless of the table on which they are issued. You can set statement auditing to audit selected users or every user in the database.
privilege auditing	The selective auditing of the use of powerful system privileges to perform corresponding actions, such as AUDIT CREATE TABLE. Privilege auditing is more focused than statement auditing, auditing only the use of the target privilege. You can set privilege auditing to audit a selected user or every user in the database.
object auditing	The selective auditing of specific statements on a particular schema object, such as AUDIT SELECT ON EMP. Object auditing is very focused, auditing only a specific statement on a specific object. Object auditing always applies to all users of the database.

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, while unsuccessful operations return the Oracle error code describing why the operation was unsuccessful. These codes are listed in Oracle7 Server Messages.

Instance startup	An audit record is generated that details the OS user starting the instance, his terminal identifier, the date and time stamp, and whether database auditing was enabled or disabled. This is audited into the OS audit trail because the database audit trail is not available until after startup has successfully completed. Recording the state of database auditing at startup further prevents an administrator from restarting a database with database auditing disabled so that they are able to perform unaudited actions.
Instance shutdown	An audit record is generated that details the OS user shutting down the instance, her terminal identifier, the date and time stamp.
Connections to the database as INTERNAL	An audit record is generated that details the OS user connecting to Oracle as INTERNAL. This provides accountability of users connected as INTERNAL.