6 Configuring Auditing

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

DELETE FROM SYS.AUD$;

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

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

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
  

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.

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

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.

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.

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.

NOAUDIT ALL;

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

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.

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

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-7 lists auditing options that are now enabled in Oracle Database 11g Release 1 (11.1).

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

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

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

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

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

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.

AUDIT SELECT TABLE, UPDATE TABLE 
     BY scott, blake;

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

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.

6.5.11.3 Disabling Network Auditing

Example 6-11 shows how to turn off 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.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.

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.

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, local0–local7, syslog, daemon, kern, mail, auth, lpr, news, uucp, and cron.

     The local0–local7 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.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.

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.

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:

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.

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