Auditing
You can observe a lot by watching.
Yogi Berra
This chapter discusses the auditing feature of Oracle. It includes:
If you are using Trusted Oracle, see the Trusted Oracle7 Server Administrator's Guide for additional information.
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 name of the object accessed
- the operation performed or attempted
- the completion code of the operation
- 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
Statement auditing is the selective auditing of related groups of statements that fall into two categories:
- DDL statements, regarding a particular type of database structure or object, but not a specifically named structure or object (for example, AUDIT TABLE audits all CREATE and DROP TABLE statements)
- DML statements, regarding a particular type of database structure or object, but not a specifically named structure or object (for example, AUDIT SELECT TABLE audits all SELECT
. . . FROM TABLE/VIEW/SNAPSHOT statements, regardless of the table, view, or snapshot)
Statement auditing can be broad and audit the activities of all database users, or focused and audit only the activities of a select list of database users.
Privilege Auditing
Privilege auditing is the selective auditing of the statements allowed using a system privilege. For example, auditing of the SELECT ANY TABLE system privilege audits users' statements that are executed using the SELECT ANY TABLE system privilege.
You can audit the use of any system privilege. In all cases of privilege auditing, owner privileges and object privileges are checked before the use of system privileges. If these other privileges suffice to permit the action, the action is not audited. If similar statement and privilege audit options are both set, only a single audit record is generated. For example, if the statement option TABLE and the system privilege CREATE TABLE are both audited, only a single audit record is generated each time a table is created.
Privilege auditing is more focused than statement auditing because each option audits only specific types of statements, not a related list of statements. For example, the statement auditing option TABLE audits CREATE TABLE, ALTER TABLE, and DROP TABLE statements, while the privilege auditing option CREATE TABLE audits only CREATE TABLE statements, since only the CREATE TABLE statement requires the CREATE TABLE privilege.
Privilege auditing can be broad, and audit the activities of all database users, or focused, and audit only the activities of a select list of database users.
Object Auditing
Object auditing is the selective auditing of specific DML statements (including queries), and GRANT and REVOKE statements for specific schema objects. Object auditing audits the operations permitted by object privileges, such as SELECT or DELETE statements on a given table, as well as the GRANT and REVOKE statements that control those privileges.
You can audit statements that reference tables, views, sequences, standalone stored procedures and functions, and packages (procedures in packages cannot be audited individually). Notice that statements that reference clusters, database links, indexes, or synonyms are not audited directly.
You can, however, audit access to these objects indirectly by auditing the operations that affect the base table. Object audit options are always set for all users of the database; these options cannot be set for a specific list of users. Oracle provides a mechanism for setting default object audit options for all auditable schema objects.
Object Audit Options for Views and Procedures
Because views and procedures (including stored functions, packages, and triggers) reference underlying objects in their definition, auditing with respect to views and procedures has several unique characteristics. Several audit records can potentially be generated as the result of using a view or a procedure. Not only is the use of the view or procedure subject to enabled audit options, but the SQL statements issued as a result of using the view or procedure are subject to the enabled audit options of the base objects (including default audit options).
As an illustration of this situation, consider the following series of SQL statements:
AUDIT SELECT ON emp;
CREATE VIEW emp_dept AS
SELECT empno, ename, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno;
AUDIT SELECT ON emp_dept;
SELECT * FROM emp_dept;
As a result of the query on EMP_DEPT, two audit records are generated: one for the query on the EMP_DEPT view and one for the query on the base table EMP (indirectly via the EMP_DEPT view). The query on the base table DEPT 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 EMP_DEPT 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 an object invalidates that object in the cache and causes it to be reloaded. Any changes to the audit options of base objects are not observed by views and procedures in the shared pool. Continuing with the above example, if auditing of SELECT statements is turned off for the EMP table, use of the EMP_DEPT view would no longer generate an audit record for the EMP table.
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.
