|Oracle9i Database Administrator's Guide
Release 2 (9.2)
Part Number A96521-01
This chapter describes how to use the Oracle database server's auditing facilities, and contains these topics:
This section describes guidelines for auditing and contains the following topics:
The data dictionary of every database has a table named
SYS.AUD$, commonly referred to as the database audit trail, that is designed to store entries auditing database statements, privileges, or schema objects.
You can optionally choose to store the database audit information to an operating system file. If your operating system has an audit trail that stores audit records generated by the operating system auditing facility, and Oracle is allowed to write to it, you can choose to direct the database audit entries to this file. For example, the Windows operating system allows Oracle to write audit records as events to the application event log.
Consider the advantages and disadvantages of using either the database or operating system audit trail to store database audit records.
Using the database audit trail offers the following advantages:
Alternatively, your operating system audit trail may allow you to consolidate audit records from multiple sources including Oracle and other applications. Therefore, examining system activity might be more efficient because all audit records are in one place.
Your operating system specific documentation for information about its auditing capabilities
Although auditing is relatively inexpensive, limit the number of audited events as much as possible. This minimizes the performance impact on the execution of statements that are audited, and minimizes the size of the audit trail.
Use the following general guidelines when devising an auditing strategy:
After you have a clear understanding of the reasons for auditing, you can devise an appropriate auditing strategy and avoid unnecessary auditing.
For example, suppose you are auditing to investigate suspicious database activity. This information by itself is not specific enough. What types of suspicious database activity do you suspect or have you noticed? A more focused auditing purpose might be to audit unauthorized deletions from arbitrary tables in the database. This purpose narrows the type of action being audited and the type of object being affected by the suspicious activity.
Audit the minimum number of statements, users, or objects required to get the targeted information. This prevents unnecessary audit information from cluttering the meaningful information and consuming valuable space in the
SYSTEM tablespace. Balance your need to gather sufficient security information with your ability to store and process it.
For example, if you are auditing to gather information about database activity, determine exactly what types of activities you are tracking, audit only the activities of interest, and audit only for the amount of time necessary to gather the information you desire. Do not audit objects if you are only interested in each session's logical I/O information.
When you audit to monitor suspicious database activity, use the following guidelines:
When starting to audit for suspicious database activity, it is common that not much information is available to target specific users or schema objects. Therefore, audit options must be set more generally at first. Once preliminary audit information is recorded and analyzed, the general audit options should be turned off and more specific audit options enabled. This process should continue until enough evidence is gathered to make concrete conclusions about the origin of the suspicious database activity.
When auditing for suspicious database activity, protect the audit trail so that audit information cannot be added, changed, or deleted without being audited.
When your purpose for auditing is to gather historical information about particular database activities, use the following guidelines:
To avoid cluttering meaningful information with useless audit records and reduce the amount of audit trail administration, only audit the targeted database activities.
After you have collected the required information, archive the audit records of interest and purge the audit trail of this information.
Oracle can write records to either the database audit trail, an operating system file, or both. This section describes the makeup of this audit trail information.
The database audit trail, stored in the
SYS.AUD$ table, contains different types of information, depending on the events audited and the auditing options set. The following information is always included in each audit trail record:
The audit trail does not store information about any data values that might be involved in the audited statement. For example, old and new data values of updated rows are not stored when an
UPDATE statement is audited. However, this specialized type of auditing can be performed using fine-grained auditing methods.
"Fine-Grained Auditing" for more information about methods of fine-grained auditing
The operating system file that contains the audit trail can contain any of the following:
Audit trail records written to an operating system audit trail may contain encoded information, but this information can be decoded using data dictionary tables and error messages as follows:
|Encoded Information||How to Decode|
This describes the operation performed or attempted. The
This describes any system privileges used to perform the operation. The
This describes the result of the attempted operation. Successful operations return a value of zero; unsuccessful operations return the Oracle error code describing why the operation was unsuccessful. These codes are listed in Oracle9i Database Error Messages.
Regardless of whether database auditing is enabled, Oracle always audits certain database-related operations and writes them to the operating system audit file. These operations include the following:
An audit record is generated that lists the operating system user connecting to Oracle as
SYSDBA. This provides for accountability of users with administrative privileges. Full auditing for these users can be enabled as explained in "Auditing Administrative Users".
An audit record is generated that lists the operating system user starting the instance, the user's terminal identifier, the date and time stamp, and whether database auditing was enabled or disabled. This is stored in the operating system audit trail because the database audit trail is not available until after startup has successfully completed. Recording the state of database auditing at startup helps detect when an administrator has restarted a database with database auditing disabled (thus enabling the administrator to perform unaudited actions).
An audit record is generated that lists the operating system user shutting down the instance, the user's terminal identifier, and the date and time stamp.
Sessions for users who connect as
SYS, this includes all users connecting as
SYSOPER, can be fully audited. Use the
AUDIT_SYS_OPERATIONS initialization parameter to specify if user
SYS is audited. For example, the following setting specifies that
SYS is to be audited:
A value of
FALSE, which is the default, disables
All audit records for
SYS are written to the operating system file that contains the audit trail, and not to
SYS issued SQL statements are audited indiscriminately and regardless of the setting of the
AUDIT_TRAIL initialization parameter.
Consider the following
CONNECT / AS SYSDBA; ALTER SYSTEM FLUSH SHARED_POOL; UPDATE salary SET base=1000 WHERE name='myname';
When SYS auditing is enabled, both the
ALTER SYSTEM and
UPDATE statements are displayed in the operating system audit file as follows:
Thu Jan 24 12:58:00 2002 ACTION: 'CONNECT' DATABASE USER: '/' OSPRIV: SYSDBA CLIENT USER: scott CLIENT TERMINAL: pts/2 STATUS: 0 Thu Jan 24 12:58:00 2002 ACTION: 'alter system flush shared_pool' DATABASE USER: '' OSPRIV: SYSDBA CLIENT USER: scott CLIENT TERMINAL: pts/2 STATUS: 0 Thu Jan 24 12:58:00 2002 ACTION: 'update salary set base=1000 where name='myname'' DATABASE USER: '' OSPRIV: SYSDBA CLIENT USER: scott CLIENT TERMINAL: pts/2 STATUS: 0
Because of the superuser privileges available to users who connect as
SYSDBA, Oracle recommends that DBAs rarely use this connection and only when necessary. Normal day to day maintenance activity can usually be done by DBAs assigned the DBA role.
This section describes various aspects of managing audit trail information, and contains the following topics:
Any authorized database user can set statement, privilege, and object auditing options at any time, but Oracle does not generate audit information for the database audit trail unless database auditing is enabled. The security administrator is normally responsible for controlling auditing.
This section discusses the initialization parameters that enable and disable auditing.
All of the initialization parameters affecting auditing are static. This means that is you change the values for the
Database auditing is enabled and disabled by the
AUDIT_TRAIL initialization parameter in the database's initialization parameter file. The parameter can be set to the following values:
Enables database auditing and directs all audit records to the database audit trail, except for records that are always written to the operating system audit trail
Enables database auditing and directs all audit records to an operating system file
Disables auditing (This value is the default.)
AUDIT_FILE_DEST initialization parameter specifies an operating system directory into which the audit trail is written when
AUDIT_TRAIL=OS is specified. It is also the location to which mandatory auditing information is written and, if so specified by the
AUDIT_SYS_OPERATIONS initialization parameter, audit records for user
AUDIT_FILE_DEST parameter is not specified, the default location is
You specify auditing options using the
AUDIT statement. The
AUDIT statement allows you to set audit options at three levels:
Causes auditing of specific SQL statements or groups of statements that affect a particular type of database object. For example,
Audits SQL statements that are authorized by the specified system privilege. For Example,
Audits specific statements on specific objects, such as
To use the
AUDIT statement to set statement and privilege options, you must have the
AUDIT SYSTEM privilege. To use it to set object audit options, you must own the object to be audited or have the
AUDIT ANY privilege.
Audit statements that set statement and privilege audit options can include a
BY clause to specify a list of users or application proxies to limit the scope of the statement and privilege audit options.
When setting auditing options, you can also specify the following conditions for auditing:
BY SESSION causes Oracle to write a single record for all SQL statements of the same type issued in the same session.
BY ACCESS causes Oracle to write one record for each access.
If you are using an operating system file for the audit trail (
WHENEVER NOT SUCCESSFUL
WHENEVER SUCCESSFUL chooses auditing only for statements that succeed.
WHENEVER NOT SUCCESSFUL chooses auditing only for statements that fail or result in errors.
The implications of your choice of auditing option and specification of
AUDIT statement clauses is discussed in subsequent sections.
A new database session picks up auditing options from the data dictionary when the session is created. These auditing options remain in force for the duration of the database connection. Setting new system or object auditing options causes all subsequent database sessions to use these options; existing sessions continue using the audit options in place at session creation.
Oracle9i SQL Reference for a complete description of the
Valid statement audit options that can be included in
NOAUDIT statements are listed in the Oracle9i SQL Reference.
Two special cases of statement auditing are discussed in the following sections.
SESSION statement option is unique because it does not generate an audit record when a particular type of statement is issued; this option generates a single audit record for each session created by connections to an instance. An audit record is inserted into the audit trail at connect time and updated at disconnect time. Cumulative information about a session such as connection time, disconnection time, logical and physical I/Os processed, and more is stored in a single audit record that corresponds to the session.
To audit all successful and unsuccessful connections to and disconnections from the database, regardless of user,
BY SESSION (the default and only value for this option), enter the following statement:
You can set this option selectively for individual users also, as in the next example:
NOT EXISTS statement option specifies auditing of all SQL statements that fail because the target object does not exist.
Privilege audit options exactly match the corresponding system privileges. For example, the option to audit use of the
DELETE ANY TABLE privilege is
DELETE ANY TABLE. To turn this option on, you use a statement similar to the following example:
Oracle's system privileges are listed in the Oracle9i SQL Reference.
To audit all successful and unsuccessful uses of the
DELETE ANY TABLE system privilege, enter the following statement:
To audit all unsuccessful
DELETE statements on all tables and unsuccessful uses of the
EXECUTE PROCEDURE system privilege, by all database users, and by individual audited statement, issue the following statement:
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL;
AUDIT SYSTEM system privilege is required to set any statement or privilege audit option. Normally, the security administrator is the only user granted this system privilege.
The Oracle9i SQL Reference lists valid object audit options and the schema object types for which each option is available.
A user can set any object audit option for the objects contained in the user's schema. The
AUDIT ANY system privilege is required to set an object audit option for an object contained in another user's schema or to set the default object auditing option. Normally, the security administrator is the only user granted the
AUDIT ANY privilege.
To audit all successful and unsuccessful
DELETE statements on the
BY SESSION (the default value), enter the following statement:
To audit all successful
DELETE statements on the
dept table owned by user
BY ACCESS, enter the following statement:
To set the default object auditing options to audit all unsuccessful
BY SESSION (the default), enter the following statement:
In a multi-tier environment, Oracle preserves the identity of the client through all tiers. This enables auditing of actions taken on behalf of the client. To do so, you use the
proxy clause in your
This clause allows you a few options. You can:
The following example audits
SELECT TABLE statements issued on behalf of client
jackson by the proxy application server
Oracle9i Database Concepts and Oracle9i Application Developer's Guide - Fundamentals for more information on proxies and multi-tier applications
NOAUDIT statement turns off the various audit options of Oracle. Use it to reset statement and privilege audit options, and object audit options. A
NOAUDIT statement that sets statement and privilege audit options can include the
proxy option to specify a list of users to limit the scope of the statement and privilege audit options.
You can use a
NOAUDIT statement to disable an audit option selectively using the
WHENEVER clause. If the clause is not specified, the auditing option is disabled entirely, for both successful and unsuccessful cases.
BY ACCESS option pair is not supported by the
NOAUDIT statement; audit options, no matter how they were turned on, are turned off by an appropriate
Oracle9i SQL Reference for a complete syntax listing of the
The following statements turn off the corresponding audit options:
NOAUDIT session; NOAUDIT session BY scott, lori; NOAUDIT DELETE ANY TABLE; NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
The following statement turns off all statement audit options:
The following statement turns off all privilege audit options:
To disable statement or privilege auditing options, you must have the
AUDIT SYSTEM system privilege.
The following statements turn off the corresponding auditing options:
Furthermore, to turn off all object audit options on the
emp table, enter the following statement:
To turn off all default object audit options, enter the following statement:
All schema objects created before this
NOAUDIT statement is issued continue to use the default object audit options in effect at the time of their creation, unless overridden by an explicit
NOAUDIT statement after their creation.
To disable object audit options for a specific object, you must be the owner of the schema object. To disable the object audit options of an object in another user's schema or to disable default object audit options, you must have the
AUDIT ANY system privilege. A user with privileges to disable object audit options of an object can override the options set by any user.
If the audit trail becomes completely full and no more audit records can be inserted, audited statements cannot be successfully executed until the audit trail is purged. Warnings are returned to all users that issue audited statements. Therefore, the security administrator must control the growth and size of the audit trail.
When auditing is enabled and audit records are being generated, the audit trail grows according to two factors:
To control the growth of the audit trail, you can use the following methods:
AUDIT ANYsystem privilege is never granted to any other user. Alternatively, all schema objects can belong to a schema for which the corresponding user does not have
CREATE SESSIONprivilege is not granted to the corresponding user) and the security administrator is the only user granted the
AUDIT ANYsystem privilege.
In both scenarios, object auditing is controlled entirely by the security administrator.
The maximum size of the database audit trail (
SYS.AUD$ table) is determined by the default storage parameters of the
SYSTEM tablespace, in which it is stored. You should not move
SYS.AUD$ to another tablespace as a means of controlling the growth and size of the audit trail. However, you can modify the storage parameters for
Your operating system specific Oracle documentation for more information about managing the operating system audit trail when you are directing audit records to that location
After auditing is enabled for some time, the security administrator may want to delete records from the database audit trail both to free audit trail space and to facilitate audit trail management.
For example, to delete all audit records from the audit trail, enter the following statement:
Alternatively, to delete all audit records from the audit trail generated as a result of auditing the table
emp, enter the following statement:
If audit trail information must be archived for historical purposes, the security administrator can copy the relevant records to a normal database table (for example, using
SELECT ... FROM SYS.AUD$ ...) or export the audit trail table to an operating system file.
Only the user
SYS, a user who has the
DELETE ANY TABLE privilege, or a user to whom
SYS has granted
DELETE privilege on
SYS.AUD$ can delete records from the database audit trail.
If the audit trail is completely full and connections are being audited (that is, if the
Oracle9i Database Utilities for information about exporting tables
As with any database table, after records are deleted from the database audit trail, the extents allocated for this table still exist.
If the database audit trail has many extents allocated for it, but many of them are not being used, the space allocated to the database audit trail can be reduced by following these steps:
The new version of
SYS.AUD$ is allocated only as many extents as are necessary to contain current audit trail records.
When auditing for suspicious database activity, protect the integrity of the audit trail's records to guarantee the accuracy and completeness of the auditing information.
To protect the database audit trail from unauthorized deletions, grant the
DELETE ANY TABLE system privilege to security administrators only.
To audit changes made to the database audit trail, use the following statement:
Audit records generated as a result of object audit options set for the
SYS.AUD$ table can only be deleted from the audit trail by someone connected with administrator privileges, which itself has protection against unauthorized use.
In the auditing methods discussed so far, a fixed set of facts is recorded in the audit trail. Additionally, audit options can only be set to monitor access of objects or privileges. No support has been discussed for obtaining more specific information about the environment or query results, nor any mechanism to specify audit conditions in order to minimize false audits. For these purposes, Oracle offers fine-grained auditing.
Fine-grained auditing allows the monitoring of data access based on content. For example, a central tax authority needs to track access to tax returns to guard against employee snooping. Enough detail is wanted to be able to determine what data was accessed, not just that
SELECT privilege was used by a specific user on a particular table. Fine-grained auditing provides this functionality.
In general, fine-grained auditing policy is based on simple user-defined SQL predicates on table objects as conditions for selective auditing. During fetching, whenever policy conditions are met for a returning row, the query is audited. Later, Oracle executes user-defined audit event handlers using autonomous transactions to process the event.
Fine-grained auditing can be implemented in user applications using the
DBMS_FGA package or by using database triggers.
Oracle9i Application Developer's Guide - Fundamentals for information about using fine-grained auditing
The database audit trail (
SYS.AUD$) is a single table in each Oracle database's data dictionary. To help you meaningfully view auditing information in this table, several predefined views are available. They must be created by you. You can later delete them if you decide not to use auditing.
The following views (except
STMT_AUDIT_OPTION_MAP) are created by the
Contains information about auditing option type codes. Created by the
Contains descriptions for audit trail action type codes
Contains default object-auditing options that will be applied when objects are created
Describes current system auditing options across the system and by user
Describes current system privileges being audited across the system and by user
Describes auditing options on all objects.
Lists all audit trail entries.
Contains audit trail records for all objects in the system.
Lists all audit trail records concerning
Lists audit trail records concerning
Lists audit trail entries produced
The following views are used for fine-grained auditing:
Shows all the auditing policies on the system.
Lists audit trail records for value-based auditing.
Oracle9i Database Reference for more detailed descriptions of the Oracle provided predefined views
If you disable auditing and no longer need the audit trail views, delete them by connecting to the database as
SYS and running the script file
CATNOAUD.SQL. The name and location of the
CATNOAUD.SQL script are operating system dependent.
This section offers examples that demonstrate how to examine and interpret the information in the audit trail. Consider the following situation.
You would like to audit the database for the following suspicious activities:
You suspect the users
swilliams of several of these detrimental actions.
To enable your investigation, you issue the following statements (in order):
AUDIT ALTER, INDEX, RENAME ON DEFAULT BY SESSION; CREATE VIEW scott.employee AS SELECT * FROM scott.emp; AUDIT SESSION BY jward, swilliams; AUDIT ALTER USER; AUDIT LOCK TABLE BY ACCESS WHENEVER SUCCESSFUL; AUDIT DELETE ON scott.emp BY ACCESS WHENEVER SUCCESSFUL;
The following statements are subsequently issued by the user
The following statements are subsequently issued by the user
LOCK TABLE scott.emp IN EXCLUSIVE MODE; DELETE FROM scott.emp WHERE mgr = 7698; ALTER TABLE scott.emp ALLOCATE EXTENT (SIZE 100K); CREATE INDEX scott.ename_index ON scott.emp (ename); CREATE PROCEDURE scott.fire_employee (empid NUMBER) AS BEGIN DELETE FROM scott.emp WHERE empno = empid; END; / EXECUTE scott.fire_employee(7902);
The following sections display the information relevant to your investigation that can be viewed using the audit trail views in the data dictionary:
The following query returns all the statement audit options that are set:
SELECT * FROM DBA_STMT_AUDIT_OPTS; USER_NAME AUDIT_OPTION SUCCESS FAILURE -------------------- ------------------- ---------- --------- JWARD SESSION BY SESSION BY SESSION SWILLIAMS SESSION BY SESSION BY SESSION LOCK TABLE BY ACCESS NOT SET
Notice that the view reveals the statement audit options set, whether they are set for success or failure (or both), and whether they are set for
BY SESSION or
The following query returns all the privilege audit options that are set:
SELECT * FROM DBA_PRIV_AUDIT_OPTS; USER_NAME PRIVILEGE SUCCESS FAILURE ------------------- -------------------- --------- ---------- ALTER USER BY SESSION BY SESSION
The following query returns all audit options set for any objects whose name starts with the characters
emp and which are contained in
SELECT * FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER = 'SCOTT' AND OBJECT_NAME LIKE 'EMP%'; OWNER OBJECT_NAME OBJECT_TY ALT AUD COM DEL GRA IND INS LOC ... ----- ----------- --------- --- --- --- --- --- --- --- --- ... SCOTT EMP TABLE S/S -/- -/- A/- -/- S/S -/- -/- ... SCOTT EMPLOYEE VIEW -/- -/- -/- A/- -/- S/S -/- -/- ...
Notice that the view returns information about all the audit options for the specified object. The information in the view is interpreted as follows:
WHENEVER NOT SUCCESSFUL, separated by "/". For example, the
DELETEaudit option for
BY ACCESSfor successful delete statements and not set at all for unsuccessful delete statements.
The following query returns all default object audit options:
SELECT * FROM ALL_DEF_AUDIT_OPTS; ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE --- --- --- --- --- --- --- --- --- --- --- --- --- S/S -/- -/- -/- -/- S/S -/- -/- S/S -/- -/- -/- -/-
Notice that the view returns information similar to the
DBA_OBJ_AUDIT_OPTS views (see previous example).
The following query lists audit records generated by statement and object audit options:
The following query lists audit information corresponding to the
AUDIT SESSION statement audit option:
SELECT USERNAME, LOGOFF_TIME, LOGOFF_LREAD, LOGOFF_PREAD, LOGOFF_LWRITE, LOGOFF_DLOCK FROM DBA_AUDIT_SESSION; USERNAME LOGOFF_TI LOGOFF_LRE LOGOFF_PRE LOGOFF_LWR LOGOFF_DLO ---------- --------- ---------- ---------- ---------- ---------- JWARD 02-AUG-91 53 2 24 0 SWILLIAMS 02-AUG-91 3337 256 630 0