Skip Headers
Oracle® Database Security Guide
11g Release 1 (11.1)

Part Number B28531-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

6 Configuring Auditing

Auditing is about accountability, and is frequently performed to protect and preserve privacy for the information stored in databases.

This chapter discusses the following topics:

See "Guidelines for Auditing" for general guidelines to follow when deciding how to audit your system.

6.1 About Auditing

Auditing is the monitoring and recording of selected user database actions. You can base auditing on individual actions, such as the type of SQL statement executed, or on combinations of factors that can include the user name, application, time, and so on. You can create security policies to trigger auditing when someone accesses or alters specified objects in an Oracle database, including the contents within a specified object.

This section includes the following topics:

See also Oracle Audit Vault Administrator's Guide for information about Oracle Audit Vault, which provides advanced auditing features.

6.1.1 Why Is Auditing Used?

You typically use auditing to perform the following activities:

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

  • Deter users (or others, such as intruders) from inappropriate actions based on their accountability.

  • Investigate suspicious activity. For example, if a user is deleting data from tables, then a 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 of the actions of an unauthorized user. For example, an unauthorized user could be changing or deleting data, or a 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 generate audit records, then you will know the other security controls are not properly implemented.

  • Address auditing requirements for compliance. Regulations such as the following have common auditing-related requirements:

    • Sarbanes-Oxley Act

    • Health Insurance Portability and Accountability Act (HIPAA)

    • International Convergence of Capital Measurement and Capital Standards: a Revised Framework (Basel II)

    • Japan Privacy Law

    • European Union Directive on Privacy and Electronic Communications

6.1.2 What Is Audited?

You can design auditing to be focused or broad, enabling you to audit the following:

  • Successful statement executions, unsuccessful statement executions, or both

  • Statement executions once in each user session or once every time the statement is executed

  • Activities of all users or of a specific user

Table 6-1 describes the different Oracle Database auditing mechanisms. Each entry in the first column is a link to a more extensive discussion of that particular method.

Table 6-1 Auditing Types and Descriptions

Type of Auditing (Link to Discussion) Description

Auditing SQL Statements


Audits SQL statements by type of statement. Typically broad, statement auditing audits the use of several types of related actions for each option. For example, AUDIT TABLE 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.

You can audit a set of default SQL statements that Oracle recommends be audited. See "Using Default Auditing for Security-Relevant SQL Statements and Privileges" for more information.

Auditing Privileges


Audits the use of powerful system privileges that enable corresponding actions, such as AUDIT CREATE TABLE. 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.

You can audit a set of default privileges that Oracle recommends be audited. See "Using Default Auditing for Security-Relevant SQL Statements and Privileges" for more information.

Auditing Schema Objects


Audits specific statements on a particular schema object, such as AUDIT SELECT ON employees. 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.

Auditing SQL Statements and Privileges in a Multitier Environment


Audits actions taken on behalf of the client by a middle-tier application.

Auditing Network Activity


Audits unexpected errors in network protocol or internal errors in the network layer.

Using Fine-Grained Auditing to Monitor Specific Activities


Audits at the most granular level, data access, and actions based on content, using Boolean measures, such as value > 1,000,000. Enables auditing based on access to or changes in a column.


6.2 Creating a Record of Audited Activity

This section explains the different types of audit records that you can create. It explores the following topics:

6.2.1 Where Are Audited Activities Recorded?

Audit records include information about 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.

There are three general types of auditing:

  • Standard auditing. Use standard auditing for SQL statements, privileges, schemas, objects, and network and multitier activity. Standard audit records are written to either of the following locations:

    • SYS.AUD$ system table. You can view the contents of this table by querying the DBA_AUDIT_TRAIL data dictionary view, or the DBA_COMMON_AUDIT_TRAIL view, which combines standard and fine-grained audit log records.

    • Operating system files. In addition to writing the audit trail in operating system file format, you can write it in XML format as well. See "Managing the Operating System Audit Trail".

    To control how standard audit trail records are written, you set the AUDIT_TRAIL initialization parameter. Table 6-3 describes the AUDIT_TRAIL parameter settings.

    See "Using Standard Auditing to Monitor General Activities" for more information about standard auditing.

  • Fine-grained auditing. Use fine-grained auditing to monitor specific activities, such as actions on a database table or times that activities occur. Fine-grained audit records are written to the SYS.FGA_LOG$ system table. To view the contents of this table, query the DBA_FGA_AUDIT_TRAIL data dictionary view or the DBA_COMMON_AUDIT_TRAIL view.

    To control how fine-grained audit trail records are written, set the audit_trail parameter in the DBMS_FGA.ADD_POLICY procedure. The audit_trail parameter writes the records to either the FGA_LOG$ system table or to an XML operating system file.

    See "Using Fine-Grained Auditing to Monitor Specific Activities" for more information.

  • Administrator auditing. On UNIX systems, you can monitor the activities of system administrators (user SYS, and users connecting with the SYSDBA or SYSOPER privilege) by using the syslog audit trail. Syslog is another destination audit trail, similar to operating system files, XML format files, and database tables. On Windows, these activities are recorded in the Windows Event Log, along with other types of activities.

    For both UNIX and Windows, to control how administrator audit files are written, you set the following initialization parameters:

    • AUDIT_SYS_OPERATIONS parameter. Enables or disables administrator auditing. Setting it to TRUE records system administrator activities in the operating system file that contains the audit trail.

    • AUDIT_SYSLOG_LEVEL parameter. When the AUDIT_TRAIL parameter is set to OS, writes SYS and standard operating system audit records to the system audit log using the syslog utility.

    See "Auditing Administrative Users" for more information.

See Also:

"Finding Information About Audited Activities" for how you can use data dictionary views that capture audited information to find suspicious behavior

6.2.2 Activities That Are Always Audited

Regardless of whether database auditing is enabled, Oracle Database always audits certain database-related operations and writes them to the operating system audit file. The operating system audit file captures the complete archived messages for these types of activities. You can set the location of this file by using the AUDIT_FILE_DEST initialization parameter, which is described in "Specifying a Directory for the Operating System Audit Trail". This is called mandatory auditing, and it includes the following operations:

  • Administrative privilege connections to the database instance. An audit record is generated that lists the operating system user connecting to Oracle Database as SYSOPER or SYSDBA. This provides for accountability of users with administrative privileges. You can fully audit these users, as explained in "Auditing Administrative Users".

  • Database startup. An audit record is generated that lists the operating system user starting the instance, the user terminal identifier, and the date and time stamp. This data is stored in the operating system audit trail because the database audit trail is not available until after the startup has successfully completed.

  • Database shutdown. An audit record is generated that lists the operating system user shutting down the instance, the user terminal identifier, and the date and time stamp.

6.2.3 Activities That Are Always Recorded in the Operating System and Syslog Audit Trails

Some database-related actions are always recorded into the operating system audit trail and for UNIX systems, the syslog audit trail, regardless of whether database auditing is enabled. (The syslog audit trail is described in "Using the Syslog Audit Trail to Audit System Administrators on UNIX Systems".) The fact that these records are always created is sometimes referred to as mandatory auditing. (See "Activities That Are Always Recorded in the Standard Audit Trail" for more information.)

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

See Also:

Operating system-specific Oracle Database documentation for more information about the operating system and syslog audit trail

6.3 Managing the Database Audit Trail

This section contains the following topics:

6.3.1 Database Audit Trail Contents

The database audit trail is a pair of tables, SYS.AUD$ and SYS.FGA_LOGS$, in the SYS schema of each Oracle Database data dictionary. It records both standard and fine-grained audit activities. Several predefined views are provided to help you use the information in this table, such as DBA_AUDIT_TRAIL.

The database audit trail record contains different types of information, depending on the events audited and the auditing options set. Table 6-2 contains a partial list in the that shows columns that always appear in the audit trail. If the data they represent is available, then that data populates the corresponding column. For certain columns, this list has the column name as it displays in the audit record, shown inside parentheses. The operating system audit trail has only those columns that have Yes in the corresponding column.

Table 6-2 Audit Trail Record Data

Data Populated in Database Audit Trail In Operating System Audit Trail?

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

Footnote 1

(*) SQL text (the SQL text that triggered the auditing)

Footnote 1

Completion code of the operation

Yes

Database user name (DATABASE USER)

Yes

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

No

Distinguished name

Yes

Global User unique ID

No

Instance number

No

Name of the schema object accessed

Yes

Operating system login user name (CLIENT USER)

Yes

Operation performed or attempted (ACTION)

Yes

Process number

Footnote 2

Proxy Session audit ID

No

SCN (system change number) for the SQL statement

No

Session identifier

Yes

System privileges used (PRIVILEGE)

Yes

Terminal identifier

Yes

Transaction ID

No


Footnote 1: Columns with an asterisk (*) in Table 6-2 appear in the audit records only if you have set the AUDIT_TRAIL initialization parameter to DB, EXTENDED or XML, EXTENDED. Also, for an array, the values recorded are only the last set of bind values.

Footnote 2: Process number is populated as ProcessId on UNIX systems. On Windows systems, the label is ProcessId:ThreadId (or ProcessId if it is not running as a thread).

Note:

If the AUDIT_TRAIL initialization parameter is set to XML or XML, EXTENDED, then Oracle Database sends standard audit records to operating system files in XML format. Because XML is a standard document format, many utilities are available to parse and analyze XML data.

If the database destination for audit records becomes full or unavailable, and, therefore, unable to accept new records, then an audited action cannot complete. Instead, Oracle Database generates an error message and does not audit the action. In most cases, using an operating system log as the audit trail destination allows the action to complete.

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.

The DBA_COMMON_AUDIT_TRAIL view combines standard and fine-grained audit log records.

You can use the Flashback Query feature to show the old and new values of the updated rows, subject to any auditing policy presently in force. The current policies are enforced even if the flashback is to an old query that was originally subject to a different policy. Current business access rules always apply.

See Also:

Note:

To read from FLASHBACK_TRANSACTION_TABLE or V$LOGMNR_CONTENTS, you need to have the SELECT ANY TRANSACTION system privilege.

6.3.2 Example of Auditing Changes to the SYS.AUD$ Table

This example demonstrates the auditing of changes made to the SYS.AUD$ table.

Follow these steps:

6.3.2.1 Step 1: Create a User for This Example

  1. Log in to SQL*Plus as user SYS and connect with the AS SYSDBA privilege.

    sqlplus "SYS/AS SYSDBA"
    Enter password: password
    Connected.
    
  2. Create the following user:

    GRANT CREATE SESSION TO smith IDENTIFIED BY test2day;
    
  3. Grant user smith the following privileges:

    GRANT SELECT, INSERT, UPDATE, DELETE ON AUD$ TO smith;
    GRANT SELECT ON DBA_AUDIT_TRAIL TO smith; 
    
  4. Enter the following commands to format the output in later steps of this procedure:

    col username format a10
    col action_name format a13
    col owner format a7
    col obj_name format a10
    

    See SQL*Plus User's Guide and Reference for more information about formatting commands in SQL*Plus.

6.3.2.2 Step 2: Enable Auditing and Truncate the SYS.AUD$ Table

  1. Enable auditing on the SYS.AUD$ table.

    AUDIT SELECT ON AUD$ BY ACCESS;
    

    The BY ACCESS clause enables the audit operation to write one record each time the SYS.AUD$ table is accessed.

  2. Truncate the SYS.AUD$ table.

    TRUNCATE TABLE AUD$;
    

    The TRUNCATE TABLE statement purges all records from the SYS.AUD$ table, and removes the extents allocated for the table. If a table is very large, using TRUNCATE TABLE is faster than using DELETE to remove rows from a table.

6.3.2.3 Step 3: Perform and Audit Actions by the User

  1. Connect as user smith.

    CONNECT smith
    Enter password: test2day
    Connected.
    
  2. Enter the following statement:

    SELECT COUNT(*) FROM SYS.AUD$;
    
     COUNT(*)
    ---------
            1
    
  3. Enter the following SELECT statement:

    SELECT USERNAME, ACTION_NAME, OWNER, OBJ_NAME FROM DBA_AUDIT_TRAIL
    WHERE ACTION NOT IN (100, 101);
    
    USERNAME   ACTION_NAME   OWNER   OBJ_NAME
    ---------- ------------- ------- ----------
    SMITH      SELECT        SYS     AUD$
    

    This SELECT statement shows the SELECT statement user smith performed on the DBA_AUDIT_TRAIL view, which lists the contents of the SYS.AUD$ table.

  4. Perform the following UPDATE statement on the SYS.AUD$ table:

    UPDATE SYS.AUD$ SET USERID = 0;
    
    3 rows updated.
    
  5. Repeat the SELECT statement from Step 3 and note the changed output:

    SELECT USERNAME, ACTION_NAME, OWNER, OBJ_NAME FROM DBA_AUDIT_TRAIL
    WHERE ACTION NOT IN (100, 101);
    
    USERNAME   ACTION_NAME   OWNER   OBJ_NAME
    ---------- ------------- ------- ----------
    0          SELECT        SYS     AUD$
    0          SELECT        SYS     AUD$
    SMITH      UPDATE        SYS     AUD$
     
    

    As you can see, the SYS.AUD$ table is recording each action performed by user smith.

  6. Delete the rows from the SYS.AUD$ table.

    DELETE FROM SYS.AUD$;
    
    4 rows deleted.
    
  7. Repeat the SELECT statement from Step 3 and note the changed output:

    SELECT USERNAME, ACTION_NAME, OWNER, OBJ_NAME FROM DBA_AUDIT_TRAIL
    WHERE ACTION NOT IN (100, 101);
    
    USERNAME   ACTION_NAME   OWNER   OBJ_NAME
    ---------- ------------- ------- ----------
    SMITH      UPDATE        SYS     AUD$
    SMITH      DELETE        SYS     AUD$
    
    

6.3.2.4 Step 4: Remove the Components for This Example

  1. Connect as user SYS with the AS SYSDBA privilege.

    CONNECT SYS/AS SYSDBA
    Enter password: password
    
  2. Remove auditing from the SYS.AUD$ table.

    NOAUDIT SELECT, INSERT, UPDATE, DELETE ON AUD$;
    
  3. Drop user smith.

    DROP USER smith; 
    

6.4 Using Default Auditing for Security-Relevant SQL Statements and Privileges

When you create a new database or modify an existing database, you can use the Security Settings window in Database Configuration Assistant (DBCA) to enable or disable the default security settings. Oracle recommends that you enable these settings. When you enable the default security settings, Oracle Database audits some of the security-relevant SQL statements and privileges. It also sets the AUDIT_TRAIL initialization parameter to DB.

Oracle Database audits the AUDIT ROLE SQL statement by default. The privileges that are audited by default are as follows:

ALTER ANY PROCEDURE CREATE ANY JOB DROP ANY TABLE
ALTER ANY TABLE CREATE ANY LIBRARY DROP PROFILE
ALTER DATABASE CREATE ANY PROCEDURE DROP USER
ALTER PROFILE CREATE ANY TABLE EXEMPT ACCESS POLICY
AUDIT ROLE BY ACCESS CREATE EXTERNAL JOB GRANT ANY OBJECT PRIVILEGE
ALTER SYSTEM CREATE PUBLIC DATABASE LINK GRANT ANY PRIVILEGE
ALTER USER CREATE SESSION GRANT ANY ROLE
AUDIT SYSTEM CREATE USER
AUDIT SYSTEM BY ACCESS DROP ANY PROCEDURE

Oracle Database also audits all privileges and statements BY ACCESS in one statement.

If you are concerned that the auditing of these statements and privileges will adversely affect your applications, you can disable this auditing in the Security Settings window of Database Configuration Assistant. You also should be aware that auditing may adversely affect performance. If you choose the Oracle 10g Release 10.2 default for auditing, auditing will be disabled.

Oracle recommends that you enable auditing by default. Auditing is an effective method of enforcing strong internal controls so that your site can meet its regulatory compliance requirements, as defined in the Sarbanes-Oxley Act. This enables you to monitor business operations, and find any activities that may deviate from company policy. Doing so translates into tightly controlled access to your database and the application software, ensuring that patches are applied on schedule and preventing ad hoc changes. By enabling auditing by default, you can generate an audit record for audit and compliance personnel. However, be aware that auditing may affect database performance. Change the audit settings based on your enterprise security and compliance needs.

To individually control the auditing of SQL statements and privileges, use the AUDIT and NOAUDIT statements. For more information, see "Auditing SQL Statements" and "Auditing Privileges".

See Also:

6.5 Using Standard Auditing to Monitor General Activities

This section describes how to monitor general activities, such as SQL statements or privileges, by using standard auditing. It contains the following topics:

See Also:

6.5.1 About Standard Auditing

In standard auditing, you audit SQL statements, privileges, schema objects, and network activity. You accomplish this by using the AUDIT SQL statement to enable the auditing, and NOAUDIT to disable it. Alternatively, you can use Enterprise Manager Database Control to enable or disable standard auditing.

6.5.2 Who Can Perform Standard Auditing?

Any user can audit the objects in his or her own schema, by using the AUDIT statement. To disable auditing of an object, the user can use the NOAUDIT statement. No additional privileges are needed to perform this task. Users can run AUDIT statements to set auditing options regardless of the AUDIT_TRAIL parameter setting. If auditing has been disabled, the next time it is enabled, Oracle Database will record the auditing activities set by the AUDIT statements. "Enabling or Disabling the Standard Audit Trail" explains how to enable standard auditing.

Note the following:

  • To audit objects in another schema, the user needs to have the AUDIT ANY system privilege.

  • To audit system privileges, the user must have the AUDIT SYSTEM privilege.

  • If the O7_DICTIONARY_ACCESSIBILITY initialization parameter has been set to FALSE (the default), then only users who have the SYSDBA privilege can audit objects in the SYS schema. For greater security, set the O7_DICTIONARY_ACCESSIBILITY parameter to FALSE.

See Also:

  • GRANT in Oracle Database SQL Language Reference for a listing of available system and object privileges

  • AUDIT in Oracle Database SQL Language Reference for a full listing of audit options

6.5.3 Managing the Standard Audit Trail

Oracle Database writes the standard audit records to either the SYS.AUD$ table (accessible by querying the DBA_AUDIT_TRAIL view) or to an operating system file.

The following sections explain how to manage the standard audit trail:

See also Oracle Database 2 Day + Security Guide an example of how to use standard auditing.

6.5.3.1 When Are Standard Audit Records Created?

You, as the security administrator, enable or disable standard auditing for the entire database. If it is disabled, then 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 you enable database auditing, then individual audit options become effective. Any authorized database user can set these audit options for the database objects he or she owns. It is important that users exercise caution when selecting objects to audit because auditing too many objects can fill up the SYSTEM tablespace, which impacts performance.

When auditing is enabled in the database and an action set to be audited occurs, Oracle Database generates an audit record during or after the execution phase of the SQL statement. Oracle Database individually audits SQL statements inside PL/SQL program units, as necessary, when the program unit is run.

The generation and insertion of an audit trail record is independent of a user transaction being committed. That is, even if a user 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 take effect in that session. The modified statement or privilege audit options take effect only when the current session ends and a new session is created.

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

Note:

AUDIT_SYS_OPERATIONS does not depend on the standard auditing parameter, AUDIT_TRAIL. Storing the auditing records in a location separate from the usual database audit trail in the SYS schema provides greater auditing security. To specify a location for the AUDIT_SYS_OPERATIONS audit records, set the AUDIT_FILE_DEST initialization parameter. By default, Oracle Database stores these audit records in the $ORACLE_HOME/rdbms/audit directory for UNIX systems and in the Event Viewer log file for Microsoft Windows systems.

See Also:

Oracle Database Concepts for information about the different phases of SQL statement processing and shared SQL

6.5.3.2 Activities That Are Always Recorded in the Standard Audit Trail

Oracle Database records all data manipulation language (DML) statements, such as INSERT, UPDATE, MERGE, and DELETE on SYS.AUD$ and SYS.FGA_LOGS$ in the standard audit trail table SYS.AUD$. It performs the audit even if auditing is not enabled for the table in which these activities occur. You can check these activities by running the DBA_AUDIT_TRAIL and DBA_COMMON_AUDIT_TRAIL views.

6.5.3.3 Enabling or Disabling the Standard Audit Trail

Before you can use standard auditing, you need to enable the standard audit trail by setting the AUDIT_TRAIL initialization parameter. This setting determines whether to create the audit trail in the database audit trail, write the audit activities to an operating system file, or to disable auditing.

To enable or disable the standard audit trail, log in to SQL*Plus (or SQL Developer) with administrative privileges, and use the ALTER SYSTEM statement. Afterwards, you need to restart the database instance.

If you want to check the current value of the AUDIT_TRAIL parameter, use the SHOW PARAMETERS statement in SQL*Plus.

Example 6-1 shows how to run the SHOW PARAMETERS statement.

Example 6-1 Checking the Current Value of the AUDIT_TRAIL Initialization Parameter

SHOW PARAMETERS AUDIT_TRAIL

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
audit_trail                          string      DB

Example 6-2 shows how to log onto SQL*Plus, enable the standard audit trail, and then restart the database instance.

Example 6-2 Enabling the Standard Audit Trail

sqlplus "SYS/AS SYSDBA"
Enter password: password
Connected.
SQL> ALTER SYSTEM SET AUDIT_TRAIL=DB, EXTENDED SCOPE=SPFILE;
System altered.
SQL> CONNECT SYS/AS SYSOPER
Enter password: password
Connected.
SQL> SHUTDOWN;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.

This examples uses the SCOPE clause because the database instance had been started using a server parameter file (SPFILE). Starting the database with a server parameter file is the preferred way of starting a database instance. See Oracle Database Administrator's Guide for information about creating configuring server parameter files.

Table 6-3 lists the settings you can use for the AUDIT_TRAIL initialization parameter.

Table 6-3 AUDIT_TRAIL Parameter Settings

AUDIT_TRAIL Value Description

DB

Enables database auditing and directs audit records to the database audit trail (the SYS.AUD$ table), except for records that are always written to the operating system audit trail. Use this setting for a general database for manageability. (This value is the default.)

If the database was started in read-only mode with AUDIT_TRAIL set to DB, then Oracle Database internally sets AUDIT_TRAIL to OS. Check the alert log for details.

See also "Managing the Database Audit Trail".

DB, EXTENDED

Performs all actions of AUDIT_TRAIL=DB, and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available. These two columns are populated only when this parameter is specified.

DB, EXTENDED captures the SQL triggered by an audit. You can capture both the SQL statement that caused the audit, and any associated bind variables. However, be aware that you can only capture data from scalar column types, such as integers. You cannot capture data from object columns, LOBS, CLOBS, BLOBS, or user-defined column types.

If the database was started in read-only mode with AUDIT_TRAIL set to DB, EXTENDED, then Oracle Database internally sets AUDIT_TRAIL to OS. Check the alert log for details.

OS

Enables database auditing, and directs all audit records to an operating system file. If you are using an ultra-secure database configuration, Oracle recommends that you use this setting because it reduces the likelihood of a Denial of Service (DoS) attack. This setting also makes it easier to secure the audit trail. If the auditor is distinct from the database administrator, you must use the OS setting. Any auditing information stored in the database can be viewed and modified by the DBA.

To specify the location of the operating system audit record file, set the AUDIT_FILE_DEST initialization parameter. The default directory is $ORACLE_BASE/admin/$DB_UNIQUE_NAME/adump. See also "Managing the Operating System Audit Trail".

XML

Writes to the operating system audit record file in XML format. Records all elements of the AuditRecord node except Sql_Text and Sql_Bind to the operating system XML audit file.

XML, EXTENDED

Performs all actions of AUDIT_TRAIL=XML, and populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, wherever possible. These columns are populated only when this parameter is specified.

NONE

Disables standard auditing.


Note the following:

  • You do not need to restart the database if you change the object auditing. You only need to restart the database if you made a universal change, such as turning off all auditing.

  • You do not need to set AUDIT_TRAIL to enable either fine-grained auditing or SYS auditing. For fine-grained auditing, you add and remove fine-grained audit policies as necessary, applying them to the specific operations or objects you want to monitor. You can use the AUDIT_SYS_OPERATIONS parameter to enable and disable SYS auditing.

6.5.3.4 Enabling Standard Auditing Options

To use standard auditing, use the AUDIT SQL statement. Table 6-4 lists the categories in which you can use the AUDIT statement.

Table 6-4 Standard Auditing Levels and Their Effects

Level Effect

Statement

Audits specific SQL statements or groups of statements that affect a particular type of database object. For example, AUDIT TABLE audits the CREATE TABLE, TRUNCATE TABLE, COMMENT ON TABLE, and DELETE [FROM] TABLE statements.

Privilege

Audits SQL statements that are authorized by the specified system privilege. For example, AUDIT CREATE ANY TRIGGER audits statements issued using the CREATE ANY TRIGGER system privilege.

Object

Audits specific statements on specific objects, such as ALTER TABLE on the HR.EMPLOYEES table.

Network

Audits unexpected errors in network protocol or internal errors in the network layer.


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.

Example 6-3 shows how to use the BY clause to audit statements by users jward and jane.

Example 6-3 AUDIT Statement Using BY Clause

AUDIT SELECT TABLE, UPDATE TABLE, DELETE TABLE
 BY jward, jane;

When setting auditing options, you can also specify the following conditions for auditing:

  • BY SESSION/BY ACCESS

    BY SESSION writes a single record for all SQL statements of the same type issued in the same session. BY ACCESS writes one record for each access.

    For example:

    AUDIT SELECT TABLE, UPDATE TABLE, DELETE TABLE
     BY SESSION;
    

    Note:

    If AUDIT_TRAIL is set to OS or AUDIT_TRAIL is set to XML, then you can still write multiple records to the audit trail when BY SESSION is specified. Multiple records occur because, while Oracle Database can write to the operating system file, the database cannot read it to detect that an audit entry already exists for the action.
  • WHENEVER SUCCESSFUL/WHENEVER NOT SUCCESSFUL

    WHENEVER SUCCESSFUL audits only statements that succeed. WHENEVER NOT SUCCESSFUL audits only statements that fail or result in errors.

    For example:

    AUDIT SELECT UPDATE TABLE, DELETE TABLE
     WHENEVER NOT SUCCESSFUL;
    

Subsequent sections discuss the implications of your choice of auditing options and the specification of AUDIT statement clauses.

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.

Caution:

The AUDIT statement only specifies auditing options. It does not enable auditing. To turn auditing on and control whether Oracle Database generates audit records based on the audit options currently set, set the initialization parameter AUDIT_TRAIL as described in "Enabling or Disabling the Standard Audit Trail".

See Also:

Oracle Database SQL Language Reference for a description of the AUDIT statement syntax

6.5.3.5 Disabling Standard Audit Options

The NOAUDIT statement turns off the audit options. 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 BY user or BY 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, then the auditing option is disabled entirely, for both successful and unsuccessful cases.

The NOAUDIT statement does not support the BY SESSION/BY ACCESS option pair. You can turn off audit options, no matter how they were turned on, by using an appropriate NOAUDIT statement.

Caution:

The NOAUDIT statement only specifies auditing options. It does not disable auditing. To turn auditing off and stop Oracle Database from generating audit records, set the AUDIT_TRAIL initialization parameter as described in "Enabling or Disabling the Standard Audit Trail".

See Also:

Oracle Database SQL Language Reference for a description of the NOAUDIT statement syntax

6.5.3.6 Controlling the Growth and Size of the Standard Audit Trail

If the audit trail is full and no more audit records can be inserted, then audited statements cannot be successfully run until you purge the audit trail. Warnings are returned to all users who issue audited statements. Therefore, you must control the growth and size of the audit trail.

When auditing is enabled and audit records are being generated, the audit trail file increases according to two factors:

  • The number of audit options turned on

  • The frequency of execution of audited statements

To control the growth of the audit trail, you can use the following methods:

  • Enable and disable database auditing. If it is enabled, then audit records are generated and stored in the audit trail. If it is disabled, then audit records are not generated.

  • Be selective about the audit options that are turned on. If more selective auditing is performed, then useless or unnecessary audit information is not generated and stored in the audit trail.

  • Tightly control the ability to perform object auditing. This can be accomplished in two ways:

    • A security administrator owns all objects and never grants the AUDIT ANY system privilege to any other user. Alternatively, all schema objects can belong to a schema for which the corresponding user does not have CREATE SESSION privilege.

    • All objects are contained in schemas that do not correspond to real database users (that is, the CREATE SESSION privilege is not granted to the corresponding user). The security administrator is the only user granted the AUDIT ANY system privilege.

    In both scenarios, a security administrator controls entirely object auditing.

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.

See Also:

Operating system-specific Oracle Database documentation for more information about managing the operating system audit trail when directing audit records to that location
6.5.3.6.1 Archiving Standard Audit Trail Information

If you need to archive audit trail information for historical purposes, then you can copy the relevant records to a typical database table (for example, using INSERT INTO table SELECT ... FROM SYS.AUD$ ...), or export the audit trail table to an operating system file. "Archiving the Standard and Fine-Grained Audit Trails" explains how to use Oracle Data Pump Export to export the SYS.AUD$ table to an operating system file.

6.5.3.6.2 Purging the Standard Audit Trail

After auditing is enabled for some time, you should periodically purge (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:

DELETE FROM SYS.AUD$;

Alternatively, to delete all audit records from the audit trail generated as a result of auditing the table emp, enter the following statement:

DELETE FROM SYS.AUD$
     WHERE obj$name='EMP';

Note:

Oracle Database audits all deletions from the audit trail, without exception. See "Auditing the Standard Audit Trail" and "Auditing Administrative Users".

Only the user SYS, a user who has the DELETE ANY TABLE privilege, or a user to whom SYS granted the DELETE privilege on SYS.AUD$ can delete records from the database audit trail.

Note:

If the audit trail is full and connections are being audited (that is, if the SESSION option is set), then typical users cannot connect to the database because the associated audit record for the connection cannot be inserted into the audit trail. In this case, the security administrator must connect as SYS (operations by SYS are not audited), and make space available in the audit trail.

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, then you can reduce the space allocated to the database audit trail by following these steps:

  1. If you want to save information currently in the audit trail, then copy it to another database table, or export it by using the Oracle Data Pump Export.

    See "Archiving the Standard and Fine-Grained Audit Trails" for an example of using Oracle Data Pump Export.

  2. Connect as a user with administrator privileges.

  3. Truncate SYS.AUD$ using the TRUNCATE TABLE statement.

  4. Reload archived audit trail records generated in Step 1.

The new version of SYS.AUD$ is allocated only as many extents as are necessary to maintain current audit trail records.

Note:

SYS.AUD$ is the only SYS object that should ever be directly modified.

6.5.3.7 Protecting the Standard Audit Trail

When auditing for suspicious database activity, you should protect the integrity of the audit trail records to guarantee the accuracy and completeness of the auditing information.

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 who has connected with administrator privileges. Remember that administrators are also audited for unauthorized use. See "Auditing Administrative Users" for more information.

6.5.3.8 Auditing the Standard Audit Trail

If an application needs to give SYS.AUD$ access to regular users (non-SYSDBA users), remember that DML statements such as INSERT, UPDATE, MERGE, and DELETE are always audited and recorded in the SYS.AUD$ table. You can check these activities by running the DBA_AUDIT_TRAIL and DBA_COMMON_AUDIT_TRAIL views.

If a typical user has SELECT, UPDATE, INSERT, and DELETE privileges on SYS.AUD$ and executes a SELECT operation, then the audit trail will have a record of that operation. That is, SYS.AUD$ will have a row identifying the SELECT action on itself, as for example row 1.

If a user later tries to DELETE this row from SYS.AUD$, then the DELETE succeeds, because the user has the privilege to perform this action. However, this DELETE action on SYS.AUD$ is also recorded in the audit trail. Setting up this type of auditing acts as a safety feature, potentially revealing unusual or unauthorized actions. A log file for an illustrative test case appears in "Example of Auditing Changes to the SYS.AUD$ Table".

Note:

DELETE, INSERT, UPDATE, and MERGE operations on SYS.AUD$ table are always audited, and such audit records are not allowed to be deleted.

6.5.4 Managing the Operating System Audit Trail

As an alternative to creating standard audit records in the DBA_AUDIT_TRAIL (SYS.AUD$ table), you can create standard audit records in operating system files. This section describes the following topics:

6.5.4.1 Contents of the Operating System Trail

The operating system file that contains the audit trail can include any of the following data:

  • Audit records generated by the operating system

  • Database audit trail records

  • Database actions that are always audited

  • Audit records for administrative users (SYS)

6.5.4.2 How the Operating System Audit Trail Works

You can direct audit trail records to an operating system audit trail if the operating system makes an audit trail available to Oracle Database. If not, then Oracle Database writes the audit records to a file outside the database. The target directory varies by platform. On most UNIX platforms, it is $ORACLE_BASE/admin/$DB_UNIQUE_NAME/adump, but for other platforms, check the platform documentation to learn the correct target directory. In Microsoft Windows, you can access this information through Event Viewer.

If you set the AUDIT_TRAIL initialization parameter to XML, then Oracle Database writes audit records to the operating system as XML files. The V$XML_AUDIT_TRAIL view makes XML audit records available to database administrators through a SQL query, providing enhanced usability. Querying this view parses all XML files (all files with a .xml extension) in the AUDIT_FILE_DEST directory to, and then presents them in relational table format. Because XML is a standard document format, many utilities are available to parse and analyze XML data. Consult the operating system-specific Oracle Database documentation to find if this feature has been implemented on your operating system.

Be aware that 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 case, Oracle Database still allows 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 ensure this does not occur.

If you configure auditing to use the database audit trail, you can prevent this potential loss of audit information. Oracle Database prevents audited events from occurring if the audit trail is unable to accept the database audit record for the statement.

6.5.4.3 Specifying a Directory for the Operating System Audit Trail

Use the AUDIT_FILE_DEST initialization parameter to specify an operating system directory into which the audit trail is written, when the AUDIT_TRAIL initialization parameter is set to OS or to XML. You must set AUDIT_FILE_DEST to a valid directory with permissions restricted to the owner of the Oracle software and the DBA group. Mandatory auditing information also goes into that directory, as do audit records for user SYS if the AUDIT_SYS_OPERATIONS initialization parameter is specified. You must change AUDIT_FILE_DEST using the following ALTER SYSTEM statement, which enables the new destination to be effective for all subsequent sessions.

ALTER SYSTEM SET AUDIT_FILE_DEST = directory_path DEFERRED;

If you do not set the AUDIT_FILE_DEST parameter, then Oracle Database places the file in the following default locations:

  • Linux and Solaris: $ORACLE_BASE/admin/$DB_UNIQUE_NAME/adump

    For example:

    /opt/oracle/app/oracle/admin/orcl/adump
    
  • Windows: %ORACLE_BASE%\admin\%DB_UNIQUE_NAME\adump

    For example:

    C:\ORACLE\ADMIN\ORCL\ADUMP
    

Notes:

  • If your operating system supports an audit trail, then its location is operating system-specific. For example, when the AUDIT_TRAIL initialization parameter is set to OS, then Windows operating systems write audit records as events to the application event log. On most UNIX platforms, it is $ORACLE_BASE/admin/$DB_UNIQUE_NAME/adump, but for other platforms, check the platform documentation to learn the correct target directory.

  • When the AUDIT_TRAIL initialization parameter is set to XML (or XML,EXTENDED), then Oracle Database writes audit records to XML-formatted operating system files. The XML-format audit records are written to the directory specified by the AUDIT_FILE_DEST parameter on all platforms, including Windows.

6.5.4.4 Decoding Operating System Audit Trial Records

Oracle Database encodes the operating system audit trail records. You can decode this information by referring to the appropriate data dictionary tables and error messages.

Table 6-5 describes the information that is encoded and where you can find its decoded version.

Table 6-5 Encoding Information in Audit Trail Records

Encoded Information How to Decode

Action code

Describes the operation performed or attempted, using codes listed in the AUDIT_ACTIONS data dictionary table, with their descriptions.

Privileges used

Describes any system privileges used to perform the operation, using codes listed in the SYSTEM_PRIVILEGE_MAP table, with their descriptions.

Completion code

Describes the result of the attempted operation, using codes listed in Oracle Database Error Messages, with their descriptions. Successful operations return a value of zero, and unsuccessful operations return an Oracle Database error code corresponding to the reason the operation was unsuccessful.


See also "Activities That Are Always Audited" for how the operating system file captures audit information for activities that always audited.

6.5.5 Deciding Whether to Use the Database or Operating System Audit Trail

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:

  • You can view selected portions of the audit trail with the predefined audit trail views of the data dictionary, such as DBA_AUDIT_TRAIL.

  • You can use Oracle tools (such as Oracle Reports) or third-party tools to generate audit reports.

Using the operating system audit trail offers these advantages:

  • Audit records stored in operating system files can be more secure than database-stored audit records because access can require file permissions that database administrators do not have. Greater availability is another advantage to operating system storage for audit records, because they remain available even if the database is temporarily inaccessible.

  • If the AUDIT_TRAIL initialization parameter is set to XML (or XML, EXTENDED), then Oracle Database writes audit records to the operating system as XML files. You can use the V$XML_AUDIT_TRAIL view to make such XML audit records available to DBAs through a SQL query, providing enhanced usability. Querying this view causes all XML files (all files with an .xml extension) in the AUDIT_FILE_DEST directory to be parsed and presented in relational table format.

  • The DBA_COMMON_AUDIT_TRAIL view includes the standard and fine grained audit trails written to database tables, XML-format audit trail records, and the contents of the V$XML_AUDIT_TRAIL dynamic view (standard, fine grained, SYS and mandatory).

  • Using your operating system audit trail can enable you to consolidate audit records from multiple sources, including Oracle Database and other applications. Examining system activity can be more efficient with all audit records in one place. If you use XML audit records, then you can use of any standard XML editing tool to review or extract information from those records.

See Also:

  • Your operating system-specific documentation for information about its auditing capabilities.

  • Table 6-10 for a list of database audit trails that are created when you first install Oracle Database

6.5.6 Auditing SQL Statements

SQL statement auditing is the selective auditing of related groups of SQL statements regarding a particular type of database structure or schema object, but not a specifically named structure or schema object.

This section includes the following topics:

See also "Focusing Statement, Privilege, and Schema Auditing" for additional information about auditing SQL statements.

6.5.6.1 Types of SQL Statements That Are Audited

The statements that you can audit are in the following categories:

  • DDL statements. As an example, AUDIT TABLE audits all CREATE and DROP TABLE statements

  • DML statements. As an example, AUDIT SELECT TABLE audits all SELECT ... FROM TABLE/VIEW statements, regardless of the table or view

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

6.5.6.2 Enabling SQL Statement Auditing

Use the AUDIT statement to enable SQL statement auditing. You must have the AUDIT SYSTEM system privilege before you can enable auditing. Typically, only the security administrator is granted this system privilege.

Example 6-4 shows how to audit the DROP TABLE SQL statement.

Example 6-4 Using AUDIT to Enable SQL Statement Auditing

AUDIT DROP TABLE;

If you plan to audit a statement using the SESSION or NOT EXISTS option of the AUDIT statement, follow these guidelines:

  • Auditing Connections and Disconnections. The SESSION option of AUDIT 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. It inserts an audit record into the audit trail at connection time, and then updates the audit record at disconnect time. Cumulative information about a session is stored in a single audit record that corresponds to the session. This record can include the connection time, disconnection time, and logical and physical I/O processed, among other information.

    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:

    AUDIT SESSION;
    

    You can set this option selectively for individual users also, as in the following example:

    AUDIT SESSION
    BY jward, swilliams;
    
  • Auditing Statements That Fail Because an Object Does Not Exist. The NOT EXISTS option of the AUDIT statement specifies auditing of all SQL statements that fail because the target object does not exist.

    For example:

    AUDIT NOT EXISTS;
    

See Oracle Database SQL Language Reference for detailed information about the AUDIT statement.

6.5.6.3 Disabling SQL Statement Auditing

To disable SQL statement auditing, use the use the NOAUDIT SQL statement. You must have the AUDIT SYSTEM system privilege before you can disable auditing.

Example 6-5 shows examples of using the NOAUDIT statement to disable auditing.

Example 6-5 Using NOAUDIT to Disable Session and SQL Statement Auditing

NOAUDIT session;
NOAUDIT session BY preston, sebastian;
NOAUDIT DELETE ANY TABLE;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,
    EXECUTE PROCEDURE;

Example 6-6 shows how to disable all auditing by using the NOAUDIT statement.

Example 6-6 Using NOAUDIT to Disable All Auditing

NOAUDIT ALL;

See Oracle Database SQL Language Reference for detailed information about the NOAUDIT statement.

6.5.7 Auditing Privileges

Privilege auditing audits statements that use a system privilege, such as SELECT ANY TABLE.

This section includes the following topics:

See "Focusing Statement, Privilege, and Schema Auditing" for additional information about auditing privileges.

6.5.7.1 Types of Privileges That Can Be Audited

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

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

Privilege auditing does not occur if the action is already permitted by the existing owner and schema object privileges. Privilege auditing is 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 CREATE TABLE, ALTER TABLE, and DROP TABLE statements. However, the privilege auditing option, CREATE TABLE, audits only CREATE TABLE statements, because only the CREATE TABLE statement requires the CREATE TABLE privilege.

See the listing of system privileges in the GRANT SQL statement section of Oracle Database SQL Language Reference.

6.5.7.2 Enabling Privilege Auditing

Privilege audit options are the same as their corresponding system privileges. For example, the option to audit use of the DELETE ANY TABLE privilege is DELETE ANY TABLE.

Example 6-7 shows how to audit the DELETE ANY TABLE privilege.

Example 6-7 Using AUDIT to Enable Privilege Auditing

AUDIT DELETE ANY TABLE
    BY ACCESS
    WHENEVER NOT SUCCESSFUL;

To audit all successful and unsuccessful uses of the DELETE ANY TABLE system privilege, enter the following statement:

AUDIT DELETE ANY TABLE;

To audit all unsuccessful SELECT, INSERT, and 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;

The AUDIT SYSTEM system privilege is required to set any statement or privilege audit option. Usually, only the security administrator is granted this system privilege.

6.5.7.3 Disabling Privilege Auditing

The following statement turns off all privilege audit options:

NOAUDIT ALL PRIVILEGES;

To disable privilege auditing options, you must have the AUDIT SYSTEM system privilege. Usually, only the security administrator is granted this system privilege.

6.5.8 Auditing SQL Statements and Privileges in a Multitier Environment

You can use the AUDIT statement to audit the activities of a client in a multitier environment. In a multitier environment, Oracle Database preserves the identity of a client through all tiers. Thus, you can audit actions taken on behalf of the client by a middle-tier application. To do so, use the BY proxy clause in your AUDIT statement.

This clause allows you the following options:

  • 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 client identity in a database session, enabling the auditing of end-user actions through the middle-tier application. The end-user client identity then shows up in the audit trail.

Example 6-8 shows how to audit SELECT TABLE statements issued on behalf of client jackson by the proxy application server appserve.

Example 6-8 Using AUDIT to Audit a SQL Statement on Behalf of a Proxy User

AUDIT SELECT TABLE
 BY appserve ON BEHALF OF jackson;

See Also:

Oracle Database Concepts for more information about proxies and multitier applications

6.5.9 Auditing Schema Objects

Schema object auditing audits 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.

This section includes the following topics:

See "Focusing Statement, Privilege, and Schema Auditing" for additional information about auditing schema objects.

6.5.9.1 Types of Schema Objects That Can Be Audited

You can audit statements that refer to tables, views, sequences, standalone stored procedures or functions, and packages, but not individual procedures within packages.

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

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

See Also:

Oracle Database SQL Language Reference for information about auditable schema objects

6.5.9.2 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 also apply to the resulting SQL statements.

Consider the following series of SQL statements:

AUDIT SELECT ON employees; 
 
CREATE VIEW employees_departments AS 
  SELECT employee_id, last_name, department_id
    FROM employees, departments
    WHERE employees.department_id = departments.department_id;
 
AUDIT SELECT ON employees_departments; 
 
SELECT * FROM employees_departments; 

As a result of the query on the employees_departments view, 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 removed from, and subsequently replaced in, the shared pool. Auditing a schema object invalidates that schema object in the cache and then reloads it in the cache. 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 does not generate an audit record for the employees table.

Table 6-6 lists auditing actions that are now available in Oracle Database 11g Release 1 (11.1).

Table 6-6 Auditing Actions Newly Enabled by Oracle Database 11g Release 1 (11.1)

Object or Element Auditable Action

Mining Model

ALTER, AUDIT, COMMENT, GRANT, RENAME, SELECT

OLAP Primary Dimension

ALTER, AUDIT, DELETE, INSERT, SELECT, CREATE

OLAP Cube

ALTER, AUDIT, DELETE, SELECT, UPDATE, CREATE

OLAP Measure Folder

AUDIT, DELETE, INDEX, SELECT, CREATE

OLAP InterAction

AUDIT, UPDATE, CREATE

Edition

ALTER, AUDIT, COMMENT, GRANT


Table 6-7 lists auditing options that are now enabled in Oracle Database 11g Release 1 (11.1).

Table 6-7 System Auditing Options Enabled in Oracle Database 11g Release 1 (11.1)

System Auditable Action

Edition

CREATE ANY EDITION, DROP ANY EDITION, ALTER ANY EDITION, COMMENT EDITION, GRANT EDITION, USE EDITION

Primary Dimension

CREATE PRIMARY DIMENSION, ALTER ANY PRIMARY DIMENSION, CREATE ANY PRIMARY DIMENSION, DELETE ANY PRIMARY DIMENSION, DROP ANY PRIMARY DIMENSION, INSERT ANY PRIMARY DIMENSION, SELECT ANY PRIMARY DIMENSION, UPDATE ANY PRIMARY DIMENSION

Cube

CREATE CUBE, ALTER ANY CUBE, CREATE ANY CUBE, DROP ANY CUBE, SELECT ANY CUBE, UPDATE ANY CUBE

Measure Folder

CREATE MEASURE FOLDER, CREATE ANY MEASURE FOLDER, DELETE ANY MEASURE FOLDER, DROP ANY MEASURE FOLDER, INSERT ANY MEASURE FOLDER

Interaction

CREATE INTERACTION, CREATE ANY INTERACTION, DROP ANY INTERACTION, UPDATE ANY INTERACTION


6.5.9.3 Enabling Schema Object Auditing

You can use the AUDIT statement to enable object auditing. Oracle Database SQL Language Reference lists valid object audit options for AUDIT 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 schema of the user. The AUDIT ANY system privilege is required to set an object audit option for an object contained in another user schema or to set the default object auditing option. Usually, only the security administrator is granted the AUDIT ANY privilege.

Example 6-9 shows how to audit all successful and unsuccessful DELETE statements on the laurel.emp table, BY SESSION (the default value).

Example 6-9 Using AUDIT to Enable Auditing for Schema Objects

AUDIT DELETE ON laurel.emp;

To audit all successful SELECT, INSERT, and DELETE statements on the dept table owned by user jward, BY ACCESS, enter the following statement:

AUDIT SELECT, INSERT, DELETE
     ON jward.dept
     BY ACCESS
     WHENEVER SUCCESSFUL;

To set the default object auditing options to audit all unsuccessful SELECT statements, BY SESSION (the default), enter the following statement:

AUDIT SELECT
     ON DEFAULT
     WHENEVER NOT SUCCESSFUL;

6.5.9.4 Disabling Object Auditing

Use the NOAUDIT statement to disable object auditing. The following statements turn off the corresponding auditing options:

NOAUDIT DELETE
   ON emp;
NOAUDIT SELECT, INSERT, DELETE
   ON jward.dept;

To turn off all object audit options on the emp table, enter the following statement:

NOAUDIT ALL
   ON emp;

To turn off all default object audit options, enter the following statement:

NOAUDIT ALL
   ON DEFAULT;

All schema objects that are 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 the schema of another user 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.

6.5.10 Focusing Statement, Privilege, and Schema Auditing

Oracle Database lets you focus statement, privilege, and schema object auditing in three areas.

6.5.10.1 Auditing Statement Executions: Successful, Unsuccessful, or Both

For statement, privilege, and schema object auditing, Oracle Database permits 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 statement can expose users who are snooping or acting maliciously; though most unsuccessful SQL statements are 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 fail to execute because they 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 failed for other reasons. One example is when a CREATE TABLE auditing condition is set, but some CREATE TABLE statements fail due to insufficient quota for the specified tablespace.

When your audit statement includes the WHENEVER SUCCESSFUL clause, you will be able to audit 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 able to audit both successful and unsuccessful executions of the audited statement.

6.5.10.2 Number of Audit Records from Multiple Executions of a Statement

If an audited statement is issued multiple times in a single user session, then the audit trail can have one or more related records. The controlling clause BY ACCESS in the AUDIT statement generates a separate audit record for each execution of an auditable operation within a cursor. If you use the BY SESSION clause instead, then the audit trail contains 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, some audit options can be set only BY ACCESS:

  • 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.

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

See Oracle Database SQL Language Reference for additional information about the BY ACCESS clause in AUDIT.

6.5.10.2.1 Creating One Audit Record for Each Operation with the BY ACCESS Clause

Setting audit BY ACCESS 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 or not 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 SELECT TABLE statement auditing option is set to BY ACCESS.

  • The user jward connects to the database and issues five SELECT statements against the table named departments and then disconnects from the database.

  • The user swilliams connects to the database and issues three SELECT statements against the departments table and then disconnects from the database.

The single audit trail contains eight records, one recored for each SELECT statement.

6.5.10.2.2 Creating a Single Audit Record for Each Session with the BY SESSION Clause

For any type of audit (schema object, statement, or privilege), BY SESSION 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 then disconnects from Oracle Database.

BY SESSION: Example 1

Assume the following:

  • The SELECT TABLE statement auditing option is set to BY SESSION.

  • The user jward connects to the database and issues five SELECT statements against the table named departments and then disconnects from the database.

  • The user 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 SELECT TABLE statement auditing option is set to BY SESSION.

  • The user 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 BY SESSION clause when directing audit records to the operating system audit trail, then Oracle Database generates and stores an audit record each time an access is made. Therefore, in this auditing configuration, BY SESSION is equivalent to BY ACCESS.

6.5.10.3 Auditing Actions Performed by Specific Users

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.

Example 6-10 shows how to audit statements by users scott and blake when they query or update a table or view.

Example 6-10 Using AUDIT to Audit User Actions

AUDIT SELECT TABLE, UPDATE TABLE 
     BY scott, blake;

See Oracle Database SQL Language Reference for additional information about auditing by user.

6.5.11 Auditing Network Activity

You can use the AUDIT statement to audit unexpected errors in network protocol or internal errors in the network layer. This section includes the following topics:

6.5.11.1 Enabling Network Auditing

To enable network auditing, use the AUDIT statement. For example:

AUDIT NETWORK;

See Oracle Database SQL Language Reference for additional information about the AUDIT statement.

6.5.11.2 Types of Errors Recorded in Network Auditing

The errors that network auditing uncovers (such as ACTION 122 Network Error in AUDIT_ACTIONS) are not connection failures. There can be several possible causes of network errors. One possible cause could be an internal event set by a database engineer for testing purposes. Other causes include conflicting configuration settings for encryption, such as the network not finding the information required to create or process expected encryption. Table 6-8 shows four network error conditions.

Table 6-8 Auditable Network Error Conditions

Error Cause Action

TNS-02507

Encryption algorithm not installed

After picking an algorithm, the server was unable to find an index for it in its table of algorithms. This should be impossible because the algorithm was chosen (indirectly) from that list.

Turn on tracing for further details, and then rerun the operation. (Note that this error is not normally visible to the user.) If the error persists, then contact Oracle Support Services.

TNS-12648

Encryption or data integrity algorithm list empty

An Oracle Advanced Security list-of-algorithms parameter was empty.

Change the list to contain the name of at least one installed algorithm, or remove the list entirely if every installed algorithm is not acceptable.

TNS-12649

Unknown encryption or data integrity algorithm

An Oracle Advanced Security list-of-algorithms parameter included an algorithm name that was not recognized.

Remove that algorithm name, correct it if it was misspelled, or install the driver for the missing algorithm.

TNS-12650

No common encryption or data integrity algorithm

The client and server have no algorithm in common for either encryption or data integrity or both.

Choose sets of algorithms that overlap. In other words, add one of the client algorithm choices to the server list, or add one of the server list choices to the client algorithm.


6.5.11.3 Disabling Network Auditing

Example 6-11 shows how to turn off network auditing.

Example 6-11 Using NOAUDIT to Disable Network Auditing

NOAUDIT NETWORK;

Oracle Database disables network auditing, including auditing for DB link usage and login types.

See Oracle Database SQL Language Reference for more information about the NOAUDIT statement.

6.6 Auditing Administrative Users

You can audit administrative users by using the following methods:

6.6.1 Auditing Users Who Connect as SYS

You can fully audit sessions for users who connect as SYS, including all users connecting using the SYSDBA or SYSOPER privileges. Use the AUDIT_SYS_OPERATIONS initialization parameter to specify whether these users are to be audited.

Example 6-12 shows how to set the AUDIT_SYS_OPERATIONS initialization parameter to TRUE, which specifies that SYS is to be audited.

Example 6-12 Enabling Auditing for Users Who Connect as SYS

ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE SCOPE=SPFILE;

By default, AUDIT_SYS_OPERATIONS is set to TRUE.

All audit records for SYS are written to the operating system file that contains the audit trail, and not to SYS.AUD$ (also viewable as DBA_AUDIT_TRAIL).

In Windows, for example, when the AUDIT_TRAIL initialization parameter is set to OS, Oracle Database writes audit records as events to the Event Viewer log file. If either XML or XML,EXTENDED is specified, then audit records are written as XML files in the directory specified by the AUDIT_FILE_DEST parameter.

Notes:

The adump directory is the first default location used if the AUDIT_FILE_DEST initialization parameter is not set or does not point to a valid directory. If writing to that first default location fails, then Oracle Database uses the $ORACLE_HOME/rdbms/audit directory as the backup default location. If that attempt fails, then the audited operation fails and a message is written to the alert log.

When AUDIT_TRAIL is set to OS (for operating system), audit file names continue to be in the following form:

short_form_of_process_name_processid.aud

For example, the short process name ora is used for dedicated server processes, and the names s001, s002, and so on are used for shared server processes.

When AUDIT_TRAIL is set to XML or XML, EXTENDED, the same audit file names have the extension xml instead of aud.

If you do not specify the AUDIT_FILE_DEST initialization parameter, then the default location is $ORACLE_BASE/admin/$DB_UNIQUE_NAME/adump in Linux and Solaris, and $ORACLE_BASE\admin\$DB_UNIQUE_NAME\adump in Windows.

For other operating systems, refer to their audit trail documentation.

All SYS-issued SQL statements are audited indiscriminately and regardless of the setting of the AUDIT_TRAIL initialization parameter.

Consider the following SYS session:

CONNECT/AS SYSDBA;
ALTER SYSTEM FLUSH SHARED_POOL;
UPDATE salary SET base=1000 WHERE name='laurel';

When SYS auditing is enabled, both the ALTER SYSTEM and UPDATE statements are displayed in the operating system audit file, similar to the following:

Thu Jun 24 12:58:00 2007
ACTION: 'CONNECT'
DATABASE USER: '/'
OSPRIV: SYSDBA
CLIENT USER: laurel
CLIENT TERMINAL: pts/2
STATUS: 0

Thu Jan 24 12:58:00 2007
ACTION: 'alter system flush shared_pool'
DATABASE USER: ''
OSPRIV: SYSDBA
CLIENT USER: laurel
CLIENT TERMINAL: pts/2
STATUS: 0

Thu Jan 24 12:58:00 2007
ACTION: 'update salary set base=1000 where name='myname''
DATABASE USER: ''
OSPRIV: SYSDBA
CLIENT USER: laurel
CLIENT TERMINAL: pts/2
STATUS: 0

Because of the superuser privileges available to users who connect as SYSDBA, Oracle recommends that database administrators rarely use this connection and only when necessary. Database administrators can usually perform normal day-to-day maintenance activity. These database administrators are typical database users with the DBA role, or have a DBA role (for example, mydba or jr_dba) that your organization customizes.

6.6.2 Using the Syslog Audit Trail to Audit System Administrators on UNIX Systems

On UNIX systems, you can audit the activities of system administrators by creating a syslog audit trail. This section includes the following topics:

See "Activities That Are Always Recorded in the Operating System and Syslog Audit Trails".

Note:

The security vulnerability that is exposed with an operating system audit trail is not an issue on Windows operating systems. This is because audit records cannot by modified directly. Instead, audit records on Windows operating systems are stored and monitored through Event Viewer.

6.6.2.1 About the Syslog Audit Trail

A potential security vulnerability for an operating system audit trail is that a privileged user, such as a database administrator, can modify or delete database audit records. To minimize this risk, you can use a syslog audit trail. Syslog is a standard protocol on UNIX-based systems for logging information from different components of a network. Applications call the syslog() function to log information to the syslog daemon, which then determines where to log the information. You can configure syslog to log information to a file name syslog.conf, to the console, or to a remote, dedicated log host. (The syslog.conf file is only used for configuration.) You can also configure syslog to alert a specified set of users when information is logged.

Because applications, such as an Oracle process, use the syslog() function to log information to the syslog daemon, a privileged user would not have permissions to the file system where syslog messages are logged. For this reason, audit records stored using a syslog audit trail can be more secure than audit records stored using an operating system audit trail. In addition to restricting permissions to a file system for a privileged user, for a syslog audit trail to be secure, neither privileged users nor the Oracle process should have root access to the system where the audit records are written.

Caution:

You should have a strong understanding of how to work with syslog before enabling syslog auditing. See the following references for more information about syslog:
  • Oracle Database Reference for information about the AUDIT_SYSLOG_LEVEL initialization parameter

  • The UNIX man page for the syslogd utility for more information about the facility.priority settings and their directory paths

6.6.2.2 Format of the Information Stored in the Syslog Audit Trail

Similar to the operating system audit trail records, Oracle Database encodes the syslog records to ensure greater security. To find the contents of the syslog records, query the appropriate data dictionary tables and error messages. See "Finding Information About Audited Activities" for ways to query the data dictionary tables for security-related information.

Table 6-5 describes the information that is encoded and where you can find its decoded version.

Table 6-9 Encoded Information in Audit Trail Records

Encoded Information How to Decode

Action code

Describes the operation performed or attempted, using codes listed in the AUDIT_ACTIONS data dictionary table, with their descriptions.

Privileges used

Describes any system privileges used to perform the operation, using codes listed in the SYSTEM_PRIVILEGE_MAP table, with their descriptions.

Completion code

Describes the result of the attempted operation, using codes listed in Oracle Database Error Messages, with their descriptions. Successful operations return a value of zero, and unsuccessful operations return an Oracle error code corresponding to the reason the operation was unsuccessful.


6.6.2.3 Configuring Syslog Auditing

To enable syslog auditing, follow these steps:

  1. Assign a value of OS to the AUDIT_TRAIL initialization parameter, as described in "Enabling or Disabling the Standard Audit Trail".

    For example:

    ALTER SYSTEM SET AUDIT_TRAIL=OS SCOPE=SPFILE;
    
  2. Manually add and set the AUDIT_SYSLOG_LEVEL parameter to the initialization parameter file, initsid.ora.

    Set the AUDIT_SYSLOG_LEVEL parameter to specify a facility and priority in the format AUDIT_SYSLOG_LEVEL=facility.priority.

    • facility: Describes the part of the operating system that is logging the message. Accepted values are user, local0local7, syslog, daemon, kern, mail, auth, lpr, news, uucp, and cron.

      The local0local7 values are predefined tags that enable you to sort the syslog message into categories. These categories can be log files or other destinations that the syslog utility can access. To find more information about these types of tags, refer to the syslog utility MAN page.

    • priority: Defines the severity of the message. Accepted values are notice, info, debug, warning, err, crit, alert, and emerg.

    The syslog daemon compares the value assigned to the facility argument of the AUDIT_SYSLOG_LEVEL parameter with the syslog.conf file to determine where to log information.

    For example, the following statement identifies the facility as local1 with a priority level of warning:

    AUDIT_SYSLOG_LEVEL=local1.warning
    

    See Oracle Database Reference for more information about AUDIT_SYSLOG_LEVEL.

  3. Add the audit file destination to the syslog configuration file /etc/syslog.conf.

    For example, assuming you had set the AUDIT_SYSLOG_LEVEL to local1.warning, enter the following:

    local1.warning /var/log/audit.log
    

    This setting logs all warning messages to the /var/log/audit.log file.

  4. Restart the syslog logger:

    $/etc/rc.d/init.d/syslog restart
    

    Now, all audit records will be captured in the file /var/log/audit.log through the syslog daemon.

  5. Restart the database instance:

    CONNECT SYS / AS SYSOPER
    Enter password: password
    Connected.
    SQL> SHUTDOWN;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> STARTUP;
    ORACLE instance started.
    

6.7 Using Triggers to Record Customized Standard Auditing Information

You can often use triggers to record additional customized information that is not automatically included in audit records, thereby customizing your own audit conditions and record contents. For example, you could define a trigger on the employee_salaries table to generate an audit record whenever the salary of an employee is increased by more than 10 percent. You can include selected information, such as the values of salary before and after it was changed.

Example 6-13 shows a trigger used to record customized audit information.

Example 6-13 Using a Trigger to Record Customized Audit Information

CREATE TRIGGER audit_emp_salaries
AFTER INSERT OR DELETE OR UPDATE ON employee_salaries
for each row
begin
if (:new.salary> :old.salary * 1.10)
      then
      insert into emp_salary_audit values (
      :employee_no,
      :old.salary,
      :new.salary,
      user,
      sysdate);
      endif;
end;

Furthermore, you can use event triggers to enable auditing options for specific users on login, and disable them upon logoff.

However, though Oracle Database triggers can readily monitor DML actions such as INSERT, UPDATE, and DELETE, monitoring on SELECT can be costly and, in some cases, uncertain. Triggers do not enable businesses to capture the statement executed and the result set from a query. They also do not enable users to define their own alert action in addition to simply inserting an audit record into the audit trail.

For these capabilities, use fine-grained auditing, which provides an extensible auditing mechanism supporting definition of key conditions for granular audit and as an event handler to actively alert administrators to misuse of data access rights. See "Using Fine-Grained Auditing to Monitor Specific Activities".

6.8 Using Fine-Grained Auditing to Monitor Specific Activities

Fine-grained auditing enables you to create policies that define specific conditions that must take place for the audit to occur. This section explores the following topics:

6.8.1 About Fine-Grained Auditing

In general, fine-grained audit policies are based on simple, user-defined SQL predicates on table objects as conditions for selective auditing. During fetching, whenever policy conditions are met for a row, the query is audited.

For example, you can use fine-grained auditing to audit the following types of actions:

  • A table being accessed between 9:00 p.m. and 6:00 a.m. or on Saturday and Sunday

  • An IP address from outside the corporate network being used

  • A table column being selected or updated

  • A value in a table column being used

Fine-grained auditing creates a more meaningful audit trail, one that includes only very specific actions that you want to audit. It excludes unnecessary information that occurs if each table access was recorded. Fine-grained auditing has the following advantages over standard auditing:

  • It performs a Boolean condition check. If the Boolean condition you specify is met, for example, a table being accessed on a Saturday, then the audit takes place.

  • It captures the SQL that triggered the audit. You can capture both the SQL statement that caused the audit, and any associated bind variables. Be aware that you can only capture data from scalar column types. You cannot capture data from object columns, LOBs, or user-defined column types. For example, suppose you have the following query:

    SELECT NAME FROM EMPLOYEE WHERE SSN = :1
    

    If :1 is of integer type and the value for SSN is 123566789, then the audit trail can capture this information. However, the audit trail cannot capture this information if :1 is a BLOB, CLOB, object, or user-defined type.

    This feature is available to standard auditing if you set the AUDIT_TRAIL parameter to DB, EXTENDED.

  • It adds extra protection to sensitive columns. You can audit specific relevant columns that may hold sensitive information, such as salaries or social security numbers.

  • It provides an event handler feature. For example, you can write a function that calls an alert when an audited column that should not be changed at midnight is updated.

  • You do not need to set initialization parameters to enable fine-grained auditing. Instead of setting initialization parameters such as AUDIT_TRAIL, you use the DBMS_FGA PL/SQL package to add and remove fine-grained auditing policies as necessary applying them to the specific operations or objects you want to monitor. A built-in audit mechanism in the database prevents users from bypassing the audit.

Fine-grained auditing records are stored in the SYS.FGA_LOG$ table. To find information about fine-grained audit policies, you can use the DBA_FGA_AUDIT_TRAIL view. The DBA_COMMON_AUDIT_TRAIL view combines both standard and fine-grained audit log records. In addition, you can use the V$XML_AUDIT_TRAIL view to find fine-grained audit records that were written in XML formatted files. For detailed information about these views, see Oracle Database Reference.

Note:

  • Fine-grained auditing is supported only with cost-based optimization. For queries using rule-based optimization, fine-grained auditing checks before applying row filtering, which could result in an unnecessary audit event trigger.

  • Policies currently in force on an object involved in a flashback query are applied to the data returned from the specified flashback snapshot (based on time or system change number (SCN).

6.8.2 Who Can Perform Fine-Grained Auditing?

To perform fine-grained auditing, you must have EXECUTE privileges on the DBMS_FGA PL/SQL package. The package is owned by the SYS user.

6.8.3 Activities That Are Always Recorded in Fine-Grained Auditing

Oracle Database records all data manipulation language (DML) statements, such as INSERT, UPDATE, MERGE, and DELETE on the SYS.FGA_LOG$ in the table SYS.AUD$. It performs the audit even if auditing has not been enabled for the table in which these activities occur. You can check these activities by running the DBA_FGA_AUDIT_TRAIL and DBA_COMMON_AUDIT_TRAIL views.

6.8.4 Archiving and Purging the Fine-Grained Audit Trail

To archive fine-grained audit records, you can copy the relevant records to a normal database table, for example, using INSERT INTO table SELECT ... FROM SYS.FGA_LOG$ ... . Alternatively, you can export the SYS.FGA_LOG$ table to an operating system file. "Archiving the Standard and Fine-Grained Audit Trails" explains how to use Oracle Data Pump Export to export the SYS.FGA_LOG$ table to an operating system file.

To purge fine-grained audit records, you can delete them records from the SYS.FGA_LOG$ table. For example, to delete all fine-grained audit records, enter the following statement:

DELETE FROM SYS.FGA_LOG$;

Alternatively, to delete all audit records from the fine-grained audit trail generated as a result of auditing the table emp, enter the following statement:

DELETE FROM SYS.FGA_LOG$
     WHERE obj$name='EMP';

6.8.5 Using the DBMS_FGA Package to Manage Fine-Grained Audit Policies

This section explores the following topics:

6.8.5.1 About the DBMS_FGA PL/SQL Package

To manage a fine-grained audit policy, you use the DBMS_FGA PL/SQL package. This package enables you to add all combinations of SELECT, INSERT, UPDATE, and DELETE statements to one policy. You can also audit MERGE statements, by auditing the underlying actions of INSERT and UPDATE. To audit MERGE statements, configure fine-grained access on the INSERT and UPDATE statements. Only one record is generated for each policy for successful MERGE operations. To administer fine-grained audit policies, you need to have EXECUTE privileges on the DBMS_FGA package.

The audit policy is bound to the table for which you created it. This simplifies the management of audit policies because the policy only needs to be changed once in the database, not in each application. In addition, no matter how a user connects to the database—from an application, a Web interface, or through SQL*Plus or Oracle SQL Developer—Oracle Database records any actions that affect the policy.

If any rows returned from a query match the audit condition that you define, then Oracle Database inserts an audit entry into the fine-grained audit trail. This entry excludes all the information that is reported in the regular audit trail. In other words, only one row of audit information is inserted into the audit trail for every fine-grained audit policy that evaluates to true. You can optionally define an event handler to process this event, for example, by sending an alert to the pager of an administrator.

For detailed information about the syntax of the DBMS_FGA package, see Oracle Database PL/SQL Packages and Types Reference. See also Oracle Database Advanced Application Developer's Guide.

6.8.5.2 Creating a Fine-Grained Audit Policy

To create a fine-grained audit policy, use the DBMS_FGA.ADD_POLICY procedure. This procedure creates an audit policy using the supplied predicate as the audit condition. The maximum number of fine-grained policies on any table or view object is 256. Oracle Database stores the policy in the data dictionary table, but you can create the policy on any table or view that is not in the SYS schema.

The syntax for the ADD_POLICY procedure is:

DBMS_FGA.ADD_POLICY(
   object_schema      VARCHAR2, 
   object_name        VARCHAR2, 
   policy_name        VARCHAR2, 
   audit_condition    VARCHAR2, 
   audit_column       VARCHAR2, 
   handler_schema     VARCHAR2, 
   handler_module     VARCHAR2, 
   enable             BOOLEAN, 
   statement_types    VARCHAR2,
   audit_trail        BINARY_INTEGER IN DEFAULT,
   audit_column_opts  BINARY_INTEGER IN DEFAULT);

In this specification:

  • object_schema: Specifies the schema of the object to be audited.

  • object_name: Specifies the name of the object to be audited.

  • policy_name: Specifies the name of the policy to be created.

  • audit_condition: Specifies a Boolean condition in a row. Null is allowed. See "Auditing Specific Columns and Rows" for more information.

  • audit_column: Specifies one or more columns to audit, including hidden columns. If null or omitted, all columns are audited.

  • handler_schema: If an alert is used to trigger a response when the policy is violated, specifies the name of the schema that contains the event handler. See "Adding Alerts to a Fine-Grained Audit Policy" for more information.

  • handler_module: Specifies the name of the event handler.

  • enable: Enables or disables the policy using true or false. If omitted, the policy is enabled.

  • statement_types: Specifies the SQL statements to be audited.

  • audit_trail: Specifies the destination (DB or XML) of fine-grained audit records. Also specifies whether to populate LSQLTEXT and LSQLBIND in FGA_LOG$.

  • audit_column_opts: If more than one column is specified in the audit_column parameter, determines whether to audit all or specific columns. See "Auditing Specific Columns and Rows" for more information.

See Oracle Database PL/SQL Packages and Types Reference for additional details about the ADD_POLICY syntax.

Example 6-14 shows how to audit statements INSERT, UPDATE, DELETE, and SELECT on table HR.EMPLOYEES. Note that this example omits the audit_column_opts parameter, because it is not a mandatory parameter.

Example 6-14 Using DBMS_FGA.ADD_POLICY to Create a Fine-Grained Audit Policy

BEGIN
  DBMS_FGA.ADD_POLICY(
   object_schema      => 'hr',
   object_name        => 'employees',
   policy_name        => 'chk_hr_employees',
   audit_condition    =>  NULL,
   audit_column       =>  NULL,
   handler_schema     =>  NULL,
   handler_module     =>  NULL,
   enable             =>  TRUE,
   statement_types    => 'INSERT, UPDATE, SELECT, DELETE',
   audit_trail        =>  DBMS_FGA.DB + DBMS_FGA.EXTENDED);
END;

At this point, if you run the DBA_AUDIT_POLICIES view, you will find the new policy listed:

SELECT policy_name FROM DBA_AUDIT_POLICIES;
POLICY_NAME
-------------------------------
CHK_HR_EMPLOYEES

Afterwards, any of the following SQL statements log an audit event record.

SELECT count(*) FROM hr.employees WHERE commission_pct = 20 and salary > 4500;

SELECT salary FROM hr.employees WHERE department_id = 50;

DELETE from hr.employees WHERE salary > 1000000;

Auditing Specific Columns and Rows

You can fine-tune the audit behavior by targeting a specific column, referred to as a relevant column, to be audited if a condition is met. To accomplish this, you use the audit_column parameter to specify one or more sensitive columns. In addition, you can audit data in specific rows by using the audit_condition parameter to define a Boolean condition.

Example 6-14 performs an audit if anyone in Department 50 tries to access the salary and commission_pct columns.

audit_condition    => 'department_id = 50', 
audit_column       => 'salary,commission_pct,'

As you can see, this feature is enormously beneficial. It not only enables you to pinpoint particularly important types of data to audit, but it provides increased protection for columns that contain sensitive data, such as social security numbers, salaries, patient diagnoses, and so on.

If the audit_column lists more than one column, you can use the audit_column_opts parameter to specify whether a statement is audited when the query references any column specified in the audit_column parameter or only when all columns are referenced. For example:

audit_column_opts   => DBMS_FGA.ANY_COLUMNS,
audit_column_opts   => DBMS_FGA.ALL_COLUMNS,

If you do not specify a relevant column, then auditing applies to all columns. That is, without a relevant column specified, auditing occurs whenever any specified statement type affects any column.

Using NULL for Audit Conditions

If you want to guarantee auditing of the specified actions (statement_types) affecting the specified columns (audit_column), specify the audit_condition parameter as NULL (or omit it), which is interpreted as TRUE. Only specifying NULL audits the specified actions (statement_types) affecting the specified columns (audit_column).

Follow these guidelines:

  • Do not enter 1=1 as an audit condition because this feature is no longer used, and hence will not achieve the desired result. NULL performs the audit even if no rows were processed, so that all actions on an audit_column with the policy are audited.

  • Do not use an empty string to specify NULL. Using an empty string is not equivalent to NULL and will not reliably cause auditing of all actions on a table with this policy.

If NULL or no audit condition is specified, then any action on a table with that policy causes an audit record to be created, whether or not rows are returned.

6.8.5.3 Adding Alerts to a Fine-Grained Audit Policy

You can add an alert to a fine-grained audit policy that goes into effect when a user (or an intruder) violates the policy. You first need to create a procedure that generates the alert, and then use the following ADD_POLICY parameters to call this function when someone violates this policy:

  • handler_schema: The schema in which the handler event is stored

  • handler_module: The name of the event handler

The alert can come in any form that best suits your environment: an e-mail or pager notification, updates to a particular file or table, and so on. Creating alerts also helps to meet certain compliance regulations, such as California Senate Bill 1386.

Use the following syntax to create the alert procedure:

PROCEDURE fname ( 
  object_schema VARCHAR2, 
  object_name VARCHAR2, 
  policy_name VARCHAR2 ) 
AS ...

In this specification:

  • fname is the name of the procedure.

  • object_schema is the name of the schema of the table audited.

  • object_name is the name of the table to be audited.

  • policy_name is the name of the policy being enforced.

For example, suppose a clerk wanted to find the salaries of highly paid coworkers. With the audit policy created in Example 6-14 in place, his actions would be immediately logged. To notify an administrator of the overly curious behavior of the clerk, you would create a procedure to record this information, and then modify the chk_hr_employee audit policy to call this procedure.

To create this type of alert, log on to SQL*Plus with administrative privileges (user SYSTEM), and follow these steps:

  1. Create a table to record the violations to the chk_hr_employees policy:

    CREATE TABLE emp_violations (
     username VARCHAR(20),
     userhost VARCHAR(20),
     time TIMESTAMP);
    
  2. Create the procedure that will generate the alert:

    CREATE OR REPLACE PROCEDURE emp_violations_alert (
     hr_schema VARCHAR2,
     employees_table VARCHAR2,
     hr_policy VARCHAR2)
    AS
    BEGIN
     INSERT INTO sec_mgr.emp_violations (
     username, userhost, time)
     SELECT user, sys_context('userenv','terminal'), sysdate FROM DUAL;
    END emp_violations_alert;
    
  3. If you already created the example chk_hr_employees policy in Example 6-14, then drop that policy:

    BEGIN
     DBMS_FGA.DROP_POLICY(
      object_schema  => 'hr',
      object_name    => 'employees',
      policy_name    => 'chk_hr_employees');
    END;
    
  4. Re-create the chk_hr_employees policy to include a call to the emp_violations_alert alert.

    Because you now are concerned with financial data access, also modify this policy to protect only the salary and commission_pct columns.

    BEGIN
     DBMS_FGA.ADD_POLICY (
      object_schema      =>  'hr', 
      object_name        =>  'employees', 
      policy_name        =>  'chk_hr_employees', 
      audit_condition    =>  'department_id = 50', 
      audit_column       =>  'salary,commission_pct', 
      handler_schema     =>  'system', 
      handler_module     =>  'emp_violations_alert', 
      enable             =>   TRUE, 
      statement_types    =>  'INSERT, UPDATE, SELECT, DELETE', 
      audit_trail        =>   DBMS_FGA.XML + DBMS_FGA.EXTENDED, 
      audit_column_opts  =>   DBMS_FGA.ANY_COLUMNS); 
    END;
    

Now you are ready to test the alert:

  1. Connect to SQL*Plus as user HR and perform a SELECT statement on the employees table.

    CONNECT HR
    Enter password: password
    Connected.
    
    SQL> SELECT COUNT(*) FROM employees WHERE SALARY > 4500;
    
      COUNT(*)
    ----------
           60
    
  2. Connect as SYSTEM and then perform the same SELECT statement that HR performed.

    CONNECT SYSTEM
    Enter password: password
    Connected.
    
    SQL> SELECT COUNT(*) FROM hr.employees WHERE SALARY > 4500;
    
      COUNT(*)
    ----------
           60
    
  3. As user SYSTEM, check the emp_violations table, which contains the two violations.

    SQL> SELECT * FROM emp_violations;
    
    USERNAME              USERHOST              TIME
    --------------------- --------------------- ------------------------------
    HR                    SHOBEEN-PC            17-APR-07 03.30.47.000000 PM
    SYSTEM                SHOBEEN-PC            17-APR-07 03.53.18.000000 PM
    

As you can see, anyone who violates the chk_hr_employees policy is recorded in the emp_violations table, including users who have administrative privileges.

Oracle Database executes the audit function as an autonomous transaction, committing only the actions of the handler_module setting and not any user transaction. The function has no effect on any user SQL transaction.

After the first row of interest is fetched, the event is recorded, and the emp_violations_alert audit function runs. The audit event record generated is stored in the DBA_FGA_AUDIT_TRAIL view, which is fga_log$ in the SYS schema in the SYSTEM tablespace. This table has reserved columns (such as SQL_TEXT and SQL_BIND) for recording SQL text, policy name, and other information. The SQLBIND and SQLTEXT values are recorded in the LSQLTEXT and LSQLBIND columns of FGA_LOG$ only if the policy specifies audit_trail = DBMS_FGA.DB + DBMS_FGA.EXTENDED. If the policy specifies AUDIT_TRAIL=DBMS_FGA.XML, then the audit records would be written to XML-formatted operating system files.

6.8.5.4 Disabling and Enabling a Fine-Grained Audit Policy

You can disable a fine-grained audit policy by using the DBMS_FGA.DISABLE_POLICY procedure. The syntax for DISABLE_POLICY is:

DBMS_FGA.DISABLE_POLICY(
   object_schema  VARCHAR2, 
   object_name    VARCHAR2, 
   policy_name    VARCHAR2 ); 

Example 6-15 shows how to disable the fine-grained audit policy created in Example 6-14.

Example 6-15 Disabling a Fine-Grained Audit Policy

DBMS_FGA.DISABLE_POLICY(
  object_schema        => 'hr',
  object_name          => 'employees'
  policy_name          => 'chk_hr_employees');

For detailed information about the DISABLE_POLICY syntax, see Oracle Database PL/SQL Packages and Types Reference.

Example 6-16 show how to reenable the chk_hr_emp policy by using the DBMS_FGA.ENABLE_POLICY procedure:

Example 6-16 Enabling a Fine-Grained Audit Policy

DBMS_FGA.ENABLE_POLICY(
  object_schema        => 'hr',
  object_name          => 'employees',
  policy_name          => 'chk_hr_employees'
  enable               => 'true');

For detailed information about the ENABLE_POLICY syntax, see Oracle Database PL/SQL Packages and Types Reference.

6.8.5.5 Dropping a Fine-Grained Audit Policy

Example 6-17 shows how to drop a fine-grained audit policy by using the DBMS_FGA.DROP_POLICY procedure.

Example 6-17 Dropping a Fine-Grained Audit Policy

DBMS_FGA.DROP_POLICY(
  object_schema      => 'hr',
  object_name        => 'employees',
  policy_name        => 'chk_hr_employees');

See Oracle Database PL/SQL Packages and Types Reference for detailed information about the DROP_POLICY syntax.

6.8.6 Creating Operating System XML Fine-Grained Audit Records

The values for the AUDIT_TRAIL parameter (XML and XML, EXTENDED) write fine-grained auditing records to operating system files in XML format.

Audit records stored in operating system files can be more secure than database-stored audit records because file permissions that database administrators do not have may be required to access the records. Operating system storage for audit records also offers higher availability, because such records remain available even if the database is temporarily inaccessible.

You can use the V$XML_AUDIT_TRAIL view to make audit records from XML files available to DBAs through a SQL query, providing enhanced usability. Querying this view causes all XML files (all files with an .xml extension) in the AUDIT_FILE_DEST directory to be parsed and presented in relational table format.

The DBA_COMMON_AUDIT_TRAIL view includes the contents of the V$XML_AUDIT_TRAIL dynamic view for standard and fine-grained audit records.

Because the audit XML files are stored in files with the .xml extension on all platforms, the dynamic view presents audit information similarly on all platforms. See Oracle Database Reference for detailed information about the V$XML_AUDIT_TRAIL view contents.

6.9 Archiving the Standard and Fine-Grained Audit Trails

You can create an archive of the standard audit and fine-grained audit trails by exporting their system tables (SYS.AUD$ and SYS.FGA_LOG$) to operating system dump files. You should periodically archive the audit trail to prevent it from growing too large.

To archive the standard audit trail or fine-grained audit trail:

that are less than the scn column standard

6.10 Finding Information About Audited Activities

You can use data dictionary views to work with the audit trail. This section explores the following topics:

6.10.1 Using Data Dictionary Views to Find Information About the Audit Trial

Oracle Database stores audit records for standard auditing in the SYS.AUD$ table and audit records for fine-grained auditing the SYS.FGA_LOG$ table. Each of these tables is a single table in each Oracle database data dictionary. Several predefined views are available to present auditing information from this table in a meaningful way. If you decide not to use auditing, then you can later delete these views. For detailed information about these views, see Oracle Database Reference.

Table 6-10 lists views that are provide auditing information.

Table 6-10 Views That Display Information about the Database Audit Trail

View Description

ALL_AUDIT_POLICIES

Describes the fine-grained auditing policies on the tables and views accessible to the current user

ALL_AUDIT_POLICY_COLUMNS

Describes the fine-grained auditing policy columns on the tables and views accessible to the current user.

ALL_DEF_AUDIT_OPTS

Lists default object-auditing options that will be applied when objects are created

AUDIT_ACTIONS

Describes audit trail action type codes

DBA_AUDIT_EXISTS

Lists audit trail entries produced BY AUDIT NOT EXISTS

DBA_AUDIT_OBJECT

Lists audit trail records for all objects in the system

DBA_AUDIT_POLICIES

Lists all the fine-grained auditing policies on the system

DBA_AUDIT_SESSION

Lists all audit trail records concerning CONNECT and DISCONNECT

DBA_AUDIT_STATEMENT

Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database

DBA_AUDIT_TRAIL

Lists all audit trail entries

DBA_COMMON_AUDIT_TRAIL

Combines standard and fine-grained audit log records, and includes SYS and mandatory audit records written in XML format

DBA_FGA_AUDIT_TRAIL

Lists audit trail records for fine-grained auditing.

DBA_OBJ_AUDIT_OPTS

Describes auditing options on all objects

DBA_PRIV_AUDIT_OPTS

Describes current system privileges being audited across the system and by user

DBA_STMT_AUDIT_OPTS

Describes current statement auditing options across the system and by user

USER_AUDIT_OBJECT

Lists audit trail records for statements concerning objects that are accessible to the current user

USER_AUDIT_SESSION

Lists all audit trail records concerning connections and disconnections for the current user

USER_AUDIT_STATEMENT

Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements issued by the user

USER_AUDIT_TRAIL

Lists audit trail entries relating to current user

USER_OBJ_AUDIT_OPTS

Describes auditing options on all objects owned by the current user

STMT_AUDIT_OPTION_MAP

Describes information about auditing option type codes


6.10.2 Using Audit Trail Views to Investigate Suspicious Activities

This section provides 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:

  • Passwords, tablespace settings, and quotas for some database users are altered without authorization.

  • A high number of deadlocks occur, most likely because of users acquiring exclusive table locks.

  • Rows are arbitrarily deleted from the emp table in laurel's schema.

You suspect the users jward and swilliams of several of these detrimental actions.

To investigate, you issue the following statements (in the order specified):

AUDIT ALTER, INDEX, RENAME ON DEFAULT
    BY SESSION;
CREATE VIEW laurel.employee AS SELECT * FROM laurel.emp;
AUDIT SESSION BY jward, swilliams;
AUDIT ALTER USER;
AUDIT LOCK TABLE
    BY ACCESS
    WHENEVER SUCCESSFUL;
AUDIT DELETE ON laurel.emp
    BY ACCESS
    WHENEVER SUCCESSFUL;

The following statements are subsequently issued by the user jward:

ALTER USER tsmith QUOTA 0 ON users;
DROP USER djones;

The following statements are subsequently issued by the user swilliams:

LOCK TABLE laurel.emp IN EXCLUSIVE MODE;
DELETE FROM laurel.emp WHERE mgr = 7698;
ALTER TABLE laurel.emp ALLOCATE EXTENT (SIZE 100K);
CREATE INDEX laurel.ename_index ON laurel.emp (ename);
CREATE PROCEDURE laurel.fire_employee (empid NUMBER) AS
  BEGIN
    DELETE FROM laurel.emp WHERE empno = empid;
  END;
/

EXECUTE laurel.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:

6.10.2.1 Listing Active Statement Audit Options

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

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 BY ACCESS.

6.10.2.2 Listing Active Privilege Audit Options

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

6.10.2.3 Listing Active Object Audit Options for Specific Objects

The following query returns all audit options set for any objects with names that start with the characters emp and that are contained in the schema of laurel:

SELECT * FROM DBA_OBJ_AUDIT_OPTS
    WHERE OWNER = 'LAUREL' AND OBJECT_NAME LIKE 'EMP%';

OWNER   OBJECT_NAME OBJECT_TY ALT AUD COM DEL GRA IND INS LOC ...
-----   ----------- --------- --- --- --- --- --- --- --- --- ...
LAUREL EMP         TABLE     S/S -/- -/- A/- -/- S/S -/- -/- ...
LAUREL EMPLOYEE    VIEW      -/- -/- -/- A/- -/- S/S -/- -/- ...

The view returns information about all the audit options for the specified object. The information in the view is interpreted as follows:

  • A dash (-) indicates that the audit option is not set.

  • The S character indicates that the audit option is set BY SESSION.

  • The A character indicates that the audit option is set BY ACCESS.

  • Each audit option has two possible settings, WHENEVER SUCCESSFUL and WHENEVER NOT SUCCESSFUL, separated by a slash (/). For example, the DELETE audit option for laurel.emp is set BY ACCESS for successful DELETE statements and not set at all for unsuccessful DELETE statements.

6.10.2.4 Listing Default Object Audit Options

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 FBK REA
--- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
S/S -/- -/- -/- -/- S/S -/- -/- S/S -/- -/- -/- -/- /-  -/-

Notice that the view returns information similar to the USER_OBJ_AUDIT_OPTS and DBA_OBJ_AUDIT_OPTS views (refer to previous example).

6.10.2.5 Listing Audit Records

The following query lists audit records generated for all objects in the database:

SELECT * FROM DBA_AUDIT_OBJECT;

6.10.2.6 Listing Audit Records for the AUDIT SESSION Option

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 

6.10.3 Deleting the Audit Trail Views

If you disable auditing and no longer need the audit trail views, then delete them by connecting to the database as SYS and run the script file CATNOAUD.SQL. The location of the CATNOAUD.SQL script is operating system-dependent.