Auditing is the monitoring and recording of selected user database actions. It can be based on individual actions, such as the type of SQL statement executed, or on combinations of factors that can include user name, application, time, and so on. Security policies can trigger auditing when specified elements in an Oracle database are accessed or altered, including the contents within a specified object.

The present chapter describes the different types and focuses of auditing and the resulting audit trails and records.

Auditing is normally used to:

Enable future accountability for current actions taken in a particular schema, table, or row, or affecting specific content

Deter users (or others) from inappropriate actions, based on that accountability

Investigate suspicious activity. For example, if some 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.

Notify an auditor that an unauthorized user is manipulating or deleting data and that the user has more privileges than expected, which can lead to reassessing user authorizations.

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.

Detect problems with an authorization or access control implementation. For example, you can create audit policies that you expect will never generate an audit record because the data is protected in other ways. However, if these policies do generate audit records, you will know the other security controls are not properly implemented.

This chapter describes the types of auditing available in Oracle systems, in the following sections:

Table 8-1 Auditing Types and Descriptions

Audits SQL statements by type of statement, not by the specific schema objects on which they operate. Typically broad, statement auditing audits the use of several types of related actions for each option. For example, AUDITTABLE tracks several DDL statements regardless of the table on which they are issued. You can also set statement auditing to audit selected users or every user in the database.

Audits the use of powerful system privileges that enable corresponding actions, such as AUDITCREATETABLE. Privilege auditing is more focused than statement auditing, which audits only a particular type of action. You can set privilege auditing to audit a selected user or every user in the database.

Audits specific statements on a particular schema object, such as AUDITSELECTONemployees. Schema object auditing is very focused, auditing only a single specified type of statement (such as SELECT) on a specified schema object. Schema object auditing always applies to all users of the database.

Audit Records and the Audit Trails

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

The two general types of auditing are standard auditing, which is based on privileges, schemas, objects, and statements, and fine-grained auditing. Standard audit records can be written either to DBA_AUDIT_TRAIL (the sys.aud$ table) or to the operating system. Fine-grained audit records are written to DBA_FGA_AUDIT_TRAIL (the sys.fga_log$ table) and the DBA_COMMON_AUDIT_TRAIL view, which combines standard and fine-grained audit log records.

Database Audit Trail (DBA_AUDIT_TRAIL)

The database audit trail is a single table named SYS.AUD$ in the SYS schema of each Oracle database's data dictionary. Several predefined views are provided to help you use the information in this table, such as DBA_AUDIT_TRAIL.

Audit trail records can contain different types of information, depending on the events audited and the auditing options set. The partial list in the following section shows columns that always appear in the audit trail; if the data they represent is available, that data populates the corresponding column. (For certain columns, this list shows the column name displayed in the audit record, here inside parentheses.)

Table 8-2 Columns Shown in the Database Audit Trail (DBA_AUDIT_TRAIL)

Column Description/Name

Also Appears in the Operating System Audit Trail?

Operating system login user name (CLIENT USER)

Yes.

Database user name (DATABASE USER)

No.

Session identifier

Yes.

Terminal identifier

Yes.

Name of the schema object accessed

Yes.

Operation performed or attempted (ACTION)

Yes.

Completion code of the operation

Yes.

Date and time stamp in UTC (Coordinated Universal Time) format

No.

System privileges used (PRIVILEGE)

Yes.

Proxy Session's auditid

No.

Global User unique id

No.

Distinguished name

Yes.

Instance number

No.

Process number

No.

TransactionId

No.

SCN (system change number) for the SQL statement

No.

SQL text that triggered the auditing (SQLTEXT)

No.

Bind values used for the SQL statement, if any (SQLBIND)

No.

Notes:

The "Process number" column is always NULL in Oracle Database 10g.

SQLBIND and SQLTEXT are not populated unless you specified AUDIT_TRAIL=DB_EXTENDED in the database initialization file, init.ora, since CLOBs are comparatively expensive to populate.

If the database destination for audit records becomes full or unavailable and therefore unable to accept new records, an audited action cannot complete. Instead, it causes an error message and is not done. In some cases, an operating system log allows such an action to complete.

Operating System Audit Trail

Oracle allows audit trail records to be directed to an operating system audit trail if the operating system makes such an audit trail available to Oracle. If not, audit records are written to a file outside the database. The target directory varies by platform: on the Solaris platform, it is $ORACLE_HOME/rdbms/audit, but for other platforms you must check the platform documentation to learn the correct target directory. In Windows, the information is accessed through Event Viewer.

See Also:

Your operating system specific Oracle documentation, to see if this feature has been implemented on your operating system

An operating system audit trail or file system can become full and therefore unable to accept new records, including audit records directed to the operating system. In this circumstance, Oracle still allows certain actions that are always audited to continue, even though the audit record cannot be stored because the operating system destination is full. Using a database audit trail prevents audited actions from completing if their audit records cannot be stored.

System administrators configuring operating system auditing should ensure that the operating system audit trail or the file system does not fill completely. Most operating systems provide administrators with sufficient information and warning to enable them to ensure this does not occur.

Note, however, that configuring auditing to use the database audit trail removes this potential loss of audit information. The Oracle server prevents audited events from occurring if the audit trail is unable to accept the database audit record for the statement.

Operating System Audit Records

The operating system audit trail is encoded, but it is decoded in data dictionary files and error messages.

Action code describes the operation performed or attempted. The AUDIT_ACTIONS data dictionary table contains a list of these codes and their descriptions.

Privileges used describes any system privileges used to perform the operation. The SYSTEM_PRIVILEGE_MAP table lists all of these codes and their descriptions.

Completion code describes the result of the attempted operation. Successful operations return a value of zero, and unsuccessful operations return the Oracle error code describing why the operation was unsuccessful.

Records Always in the Operating System Audit Trail

Some database-related actions are always recorded into the operating system audit trail regardless of whether database auditing is enabled. The fact that these records are always created is sometimes referred to as mandatory auditing:

At instance startup, an audit record is generated that details the operating system user starting the instance, the user's terminal identifier, the date and time stamp. This information is recorded into the operating system audit trail because the database audit trail is not available until after startup has successfully completed.

At instance shutdown, an audit record is generated that details the operating system user shutting down the instance, the user's terminal identifier, the date and time stamp.

During connections made with administrator privileges, an audit record is generated that details the operating system user connecting to Oracle with administrator privileges. This record provides accountability regarding users connected with administrator privileges.

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, and in a similar format.

See Also:

Your operating system specific Oracle documentation for more information about the operating system audit trail

When Are Audit Records Created?

Standard auditing for the entire database is either enabled or disabled by the security administrator. If it is disabled, no audit records are created.

Note:

Fine-grained auditing uses audit policies applied to individual objects. Therefore, standard audit settings that are on or off for the entire database do not affect fine-grained auditing.

If database auditing is enabled by the security administrator, then individual audit options become effective. These audit options can be set by any authorized database user for database objects he owns.

When auditing is enabled in the database and an action set to be audited occurs, an audit record is generated during the execute phase of statement execution.

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 being committed. That is, even if a user's transaction is rolled back, the audit trail record remains committed.

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. Setting or changing statement or privilege audit options in a session does not cause effects in that session. The modified statement or privilege audit options take effect only when the current session is ended and a new session is created.

In contrast, changes to schema object audit options become effective for current sessions immediately.

Note:

Operations by the SYS user and by users connected through SYSDBA or SYSOPER can be fully audited with the AUDIT_SYS_OPERATIONS initialization parameter. Every successful SQL statement from SYS is audited. This specialized form of auditing audits all actions performed by every user with the SYSDBA privilege and writes only to an operating system location. It is not dependent on the standard auditing parameter AUDIT_TRAIL=. Sending these records to a location separate from the usual database audit trail in the SYS schema provides for greater auditing security.

Statement auditing is the selective auditing of related groups of statements regarding a particular type of database structure or schema object, but not a specifically named structure or schema object. These statements fall into two categories:

DDL statements. As an example, AUDITTABLE audits all CREATE and DROPTABLE statements.

DML statements. As an example, AUDITSELECTTABLE audits all SELECT ... FROMTABLE/VIEW statements, regardless of the table or view.

Statement auditing can be broad or focused, auditing the activities of all database users or of only a select list.

Privilege auditing audits statements that use a system privilege, such as SELECTANYTABLE. For example, when AUDIT SELECT ANY TABLE is in force, all statements issued by users with the SELECT ANY TABLE privilege are audited.

You can audit the use of any system privilege. Like statement auditing, privilege auditing can audit the activities of all database users or of only a specified list.

If similar statement and privilege audit options are both set, only a single audit record is generated. For example, if the statement clause TABLE and the system privilege CREATETABLE are both audited, only a single audit record is generated each time a table is created.

Thus privilege auditing does not occur if the action is already permitted by the existing owner and schema object privileges. Privilege auditing triggered only if they are insufficient, that is, only if what makes the action possible is a system privilege.

Privilege auditing is more focused than statement auditing because each privilege auditing option audits only specific types of statements, not a related list of statements. For example, the statement auditing clause TABLE audits CREATETABLE, ALTERTABLE, and DROPTABLE statements. However, the privilege auditing option CREATE TABLE audits only CREATE TABLE statements, because only the CREATE TABLE statement requires the CREATETABLE privilege.

Schema object auditing can audit all SELECT and DML statements permitted by schema object privileges, such as SELECT or DELETE statements on a given table. The GRANT and REVOKE statements that control those privileges are also audited.

Statements that reference clusters, database links, indexes, or synonyms are not audited directly. However, you can audit access to these schema objects indirectly, by auditing the operations that affect the base table.

Schema object audit options are always set for all users of the database. These options cannot be set for a specific list of users. You can set default schema object audit options for all auditable schema objects.

Schema Object Audit Options for Views, Procedures, and Other Elements

The definitions for views and procedures (including stored functions, packages, and triggers) reference underlying schema objects. Because of this dependency, some unique characteristics apply to auditing views and procedures, such as the likelihood of generating multiple audit records.

Views and procedures are subject to the enabled audit options on the base schema objects, including the default audit options. These options apply to the resulting SQL statements as well.

As a result of the query on employees_departments, two audit records are generated: one for the query on the employees_departments view and one for the query on the base table employees (indirectly through the employees_departments view). The query on the base table departments does not generate an audit record because the SELECT audit option for this table is not enabled. All audit records pertain to the user that queried the employees_departments view.

The audit options for a view or procedure are determined when the view or procedure is first used and placed in the shared pool. These audit options remain set until the view or procedure is flushed from, and subsequently replaced in, the shared pool. Auditing a schema object invalidates that schema object in the cache and causes it to be reloaded. Any changes to the audit options of base schema objects are not observed by views and procedures in the shared pool.

In the given example, if the "AUDIT SELECT ON employees;" statement is omitted, then using the employees_departments view will not generate an audit record for the employees table.

Audit By User, for specific users or for all users in the database (statement and privilege auditing only)

Auditing Statement Executions: Successful, Unsuccessful, or Both

For statement, privilege, and schema 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. Monitoring unsuccessful SQL can expose users who are snooping or acting maliciously, though of course most unsuccessful SQL is neither.

Auditing an unsuccessful statement execution provides a report only if a valid SQL statement is issued but fails because it lacks proper authorization or references a nonexistent schema 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. One example is when a CREATE TABLE auditing condition is set, but some CREATETABLE statements fail due to lack of quota for the specified tablespace.

When your audit statement includes the WHENEVERSUCCESSFUL clause, you will be auditing only successful executions of the audited statement.

When your audit statement includes the WHENEVER NOT SUCCESSFUL clause, you will be auditing only unsuccessful executions of the audited statement.

When your audit statement includes neither of the preceding two clauses, you will be auditing both successful and unsuccessful executions of the audited statement.

Number of Audit Records from Multiple Executions of a Statement

If an audited statement is issued multiple times in a single user session, your audit trail can have one or more related records. The controlling clause BY ACCESS causes each execution of an auditable operation within a cursor to generate a separate audit record. If you use the BY SESSION clause instead, your audit trail will contain a single audit record for each session, for each user and schema object. Only one audit record results, no matter how often the statement occurs in that session.

However, several audit options can be set only BYACCESS:

All statement audit options that audit DDL statements

All privilege audit options that audit DDL statements

For all other audit options, BYSESSION is used by default.

This section provides detailed examples of using each clause, in the following subsections:

BY SESSION

For any type of audit (schema object, statement, or privilege), BYSESSION inserts only one audit record in the audit trail, for each user and schema object, during a session that includes an audited action.

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

BY SESSION Example 1

Assume the following:

The SELECTTABLE statement auditing option is set BYSESSION.

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

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

In this case, the audit trail contains two audit records for the eight SELECT statements-- one for each session that issued a SELECT statement.

BY SESSION Example 2

Alternatively, assume the following:

The SELECTTABLE statement auditing option is set BYSESSION.

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

In this case, the audit trail contains two records--one for each schema object against which the user issued a SELECT statement in a session.

Note:

If you use the BYSESSION clause when directing audit records to the operating system audit trail, Oracle generates and stores an audit record each time an access is made. Therefore, in this auditing configuration, BYSESSION is equivalent to BYACCESS.

BY ACCESS

Setting audit BYACCESS inserts one audit record into the audit trail for each execution of an auditable operation 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.

For example, assume that:

The SELECTTABLE statement auditing option is set BYACCESS.

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

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

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

Audit By User

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

Audit By User Example

To audit statements by the users SCOTT and BLAKE that query or update a table or view, issue the following statements:

In a multitier environment, Oracle can preserve the identity of a client through all tiers. Thus, you can audit actions taken on behalf of the client by a mid-tier application. To do so, use the BYproxy clause in your AUDIT statement.

This clause allows you a few options. You can:

Audit SQL statements issued by the specific 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 middle tier can also set the user's client identity in a database session, enabling audit of end-user actions through the mid-tier application. The end-user's client identity then shows up in the audit trail.

Fine-Grained Auditing (FGA) enables you to monitor data access based on content. A built-in audit mechanism in the database prevents users from bypassing the audit.

While Oracle triggers can potentially monitor DML actions such as INSERT, UPDATE, and DELETE, monitoring on SELECT can be costly. In some cases, a trigger may audit too much; in others, its effectiveness or completeness may be uncertain. Triggers also do not enable users to define their own alert action in response to a triggered audit, beyond simply inserting an audit record into the audit trail.

Fine-Grained Auditing provides an extensible interface for creating policies to audit SELECT and DML statements on tables and views. The DBMS_FGA package administers these value-based audit policies. Using DBMS_FGA, the security administrator creates an audit policy on the target object. If any rows returned from a query match the audit condition, then an audit event entry is inserted into the fine-grained audit trail. This entry includes all the information reported in the regular audit trail: see the Audit Records and the Audit Trails section. Only one row of audit information is inserted into the audit trail for every FGA policy that evaluates to TRUE. The extensibility framework in FGA also enables administrators optionally to define an appropriate audit event handler to process the event, for example by sending an alert page to the administrator.

The administrator uses the DBMS_FGA.ADD_POLICY interface to define each FGA policy for a table or view, identifying any combination of SELECT, UPDATE, DELETE, or INSERT statements.

FGA policies associated with a table or view may also specify relevant columns, so that any specified statement type affecting a relevant column is audited. If no relevant column is specified, auditing applies to all columns. That is, auditing occurs whenever any specified statement type affects any column, independent of whether any rows are returned or not.

The relevant-column capability enables you to hone in on particularly important types of data to audit. Examples include privacy-relevant columns, such as those containing social security numbers, salaries, patient diagnoses, and so on. You could combine the fine-grained audit records to surface queries that had addressed both name and social security number, a potential violation of privacy security laws.

One added benefit is that the audit records being created are more clearly relevant, because they relate to specific data of interest or concern. Another benefit is that fewer total audit records need be generated, because each is now more specific and useful than what could be tracked in earlier releases.