6.7. Auditing Database Activity

Auditing involves monitoring and recording specific database activity. An Oracle 10g database supports four levels of auditing and affords you two locations for recording these activities.

ALTER PROFILE student LIMIT password_verify_function my_password_verify;

CREATE OR REPLACE FUNCTION my_password_verify
    (username VARCHAR2
    ,password VARCHAR2
   ,old_password VARCHAR2
   ) RETURN BOOLEAN
IS

BEGIN
    -- Check for the minimum length of the password
   IF LENGTH(password) < 6 THEN
       raise_application_error(-20001
         ,'Password must be at least 6 characters long'),
   END IF;

    -- Check that the password does not contain any
   -- upper/lowercase version of either the user name
   -- or the keyword PASSWORD
   IF (   regexp_like(password,username,'i')
       OR regexp_like(password,'password','i')) THEN
       raise_application_error(-20002
         ,'Password cannot contain username or PASSWORD'),

END IF;

   -- Check that the password contains at least one letter,
   -- one digit and one punctuation character
   IF NOT(     regexp_like(password,'[[:digit:]]')
           AND regexp_like(password,'[[:alpha:]]')
           AND regexp_like(password,'[[:punct:]]')
         ) THEN
      raise_application_error(-20003
         ,'Password must contain at least one digit '||
          'and one letter and one punctuation character'),
    END IF;

   -- password is okey dokey
   RETURN(TRUE);
END;
/

Audit records can be stored in the database or in operating system files for greater security. You tell the database where to record audit trail records by setting the initialization parameter audit_trail. The default is NONE. AUDIT_TRAIL=DB tells the database to record audit records in the database. AUDIT_TRAIL=DB_EXTENDED tells the database to record audit records in the database together with bind variables (SQLBIND) and the SQL statement triggering the audit entry (SQLTEXT). AUDIT_TRAIL=OS tells the database to record audit records in operating system files. You cannot change this parameter in memory, only in your PFILE or SPFILE. For example, the following statement will change the location of audit records in the SPFILE:

ALTER SYSTEM SET audit_trail=DB SCOPE=SPFILE;

After changing the audit_trail parameter, you will need to bounce (shut down and start up) your database instance for the change to take effect.

When recorded in the database, most audit entries are recorded in the SYS.AUD$ table. On Unix systems, operating system audit records are written into files in the directory specified by the initialization parameter audit_file_dest (which defaults to $ORACLE_HOME/rdbms/audit). On Microsoft Windows systems, these audit records are written to the Event Viewer log file.

The four levels of auditing—statement, privilege, object, and fine-grained access—are described in detail in the following sections.

6.7.1. Managing Statement Auditing

Statement auditing involves monitoring and recording the execution of specific types of SQL statements. In the following sections, you will learn how to enable and disable statement auditing as well as identify what statement auditing options are enabled.

6.7.1.1. Enabling Statement Auditing

You enable auditing of specific SQL statements with an AUDIT statement. For example, to audit the SQL statements CREATE TABLE, DROP TABLE, or TRUNCATE TABLE, use the TABLE audit option like this:

AUDIT table;

To record audit entries for specific users only, include a BY USER clause in the AUDIT statement. For example, to audit CREATE, DROP, or TRUNCATE TABLE statements for user juanita only, execute the following:

AUDIT table BY juanita;

Frequently, you want to record only attempts that fail—perhaps to look for users who are probing the system to see what they can get away with. To further limit auditing to only these unsuccessful executions, use a WHENEVER clause like this:

AUDIT table BY juanita WHENEVER NOT SUCCESSFUL;

You can alternately specify WHENEVER SUCCESSFUL to record only successful statements. If you do not include a WHENEVER clause, both successful and unsuccessful statements trigger audit records.

You can further configure non-DDL statements to record one audit entry for the triggering session or one entry for each auditable action during the session. Specify BY ACCESS or BY SESSION in the AUDIT statement, like this:

AUDIT INSERT TABLE BY juanita BY ACCESS;

There are many auditing options other than TABLE or INSERT TABLE. Table 6.1 shows all of the statement auditing options.

6.7.1.2. Identifying Enabled Statement Auditing Options

You can identify the statement auditing options that have been enabled in your database by querying the DBA_STMT_AUDIT_OPTS data dictionary view. For example, the following example shows that SESSION auditing is enabled for all users, NOT EXISTS auditing is enabled for all users, and TABLE auditing WHENEVER NOT SUCCESSFUL is enabled for user juanita:

SELECT audit_option, failure, success, user_name
FROM dba_stmt_audit_opts
ORDER BY audit_option, user_name;

AUDIT_OPTION         FAILURE    SUCCESS    USER_NAME
-------------------- ---------- ---------- -------------
CREATE SESSION        BY ACCESS  BY ACCESS
NOT EXISTS            BY ACCESS  BY ACCESS
TABLE                BY ACCESS  NOT SET    JUANITA

6.7.1.3. Disabling Statement Auditing

To disable auditing of a specific SQL statement, use a NOAUDIT statement, which allows the same BY and WHENEVER options as the AUDIT statement. If you enable auditing for a specific user, specify that user in the NOAUDIT statement as well. However, it is not necessary to include the WHENEVER NOT SUCCESSFUL clause in the NOAUDIT statement.

For example, to disable the three audit options in the previous section, execute the following three statements:

NOAUDIT session;
NOAUDIT not exists;
NOAUDIT table BY juanita;

6.7.1.4. Examining the Audit Trail

Statement, privilege, and object audit records are written to the SYS.AUD$ table and made available via the data dictionary views DBA_AUDIT_TRAIL and USER_AUDIT_TRAIL. These data dictionary views may not contain values for every record because this view is used for three different types of audit records. For example, you can view the user, time, and type of statement audited for user juanita by executing the following:

SELECT username, timestamp, action_name
FROM dba_audit_trail
WHERE username = 'JUANITA';

USERNAME         TIMESTAMP             ACTION_NAME
---------------- --------------------- -------------
JUANITA           15-Jun-2004 18:43:52  LOGON
JUANITA           15-Jun-2004 18:44:19  LOGOFF
JUANITA           15-Jun-2004 18:46:01  LOGON
JUANITA           15-Jun-2004 18:46:40  CREATE TABLE

If you enable AUDIT SESSION, the database creates one audit record when a user logs on and updates that record when the user logs off successfully. These session audit records contain some valuable information that can help you narrow the focus of your tuning efforts. Among the information recorded in the audit records are the username, logon time, logoff time, and the number of physical reads and logical reads performed during the session. By looking for sessions with high counts of logical or physical reads, you can identify high-resource-consuming jobs and narrow the focus of your tuning efforts.

6.7.2. Managing Privilege Auditing

Privilege auditing involves monitoring and recording the execution of SQL statements that require a specific system privilege, such as SELECT ANY TABLE or GRANT ANY PRIVILEGE. You can audit any system privilege. In the following section, you will learn how to enable and disable privilege auditing as well as identify which privilege auditing options are enabled in your database.

6.7.2.1. Enabling Privilege Auditing

You enable privilege auditing with an AUDIT statement, specifying the system privilege that you want to monitor. For example, to audit statements that require the system privilege CREATE ANY TABLE, execute the following:

AUDIT create any table;

To record audit entries for specific users only, include a BY USER clause in the AUDIT statement. For example, to audit SQL statements made by user juanita that require the CREATE ANY TABLE privilege, execute the following:

AUDIT create any table BY juanita;

Just as you do with statement auditing, you can further configure non-DDL privileges to record one audit entry for the triggering session or one for each auditable action during the session by specifying BY ACCESS or BY SESSION in the AUDIT statement, like this:

AUDIT DELETE ANY TABLE BY juanita BY ACCESS;

6.7.2.2. Identifying Enabled Privilege Auditing Options

You can report on the privilege auditing that has been enabled in your database by querying the DBA_PRIV_AUDIT_OPTS data dictionary view. For example, the following report shows that ALTER PROFILE auditing is enabled for all users and that ALTER USER and DELETE ANY TABLE auditing is enabled for user juanita:

SELECT privilege, user_name
FROM dba_priv_audit_opts
ORDER BY privilege, user_name

PRIVILEGE            USER_NAME
-------------------- ----------------
ALTER PROFILE
DELETE ANY TABLE      JUANITA
ALTER USER            JUANITA

6.7.2.3. Disabling Privilege Auditing

To disable auditing of a system privilege, use a NOAUDIT statement. The NOAUDIT statement allows the same BY options as the AUDIT statement. If you enable auditing for a specific user, you need to specify that user in the NOAUDIT statement. For example, to disable the three audit options in the previous section, execute the following three statements:

NOAUDIT alter profile;
NOAUDIT delete any table BY juanita;
NOAUDIT alter user BY juanita;

6.7.3. Managing Object Auditing

Object auditing involves monitoring and recording the execution of SQL statements that require a specific object privilege, such as SELECT, INSERT, UPDATE, DELETE, or EXECUTE. Unlike either statement or system privilege auditing, schema object auditing cannot be restricted to specific users—it is enabled for all users or no users. In the following sections, you will learn how to enable and disable object auditing options as well as identify which object auditing options are enabled.

6.7.3.1. Enabling Object Auditing

You enable object auditing with an AUDIT statement, specifying both the object and object privilege that you want to monitor. For example, to audit SELECT statements on the HR.EMPLOYEE_ SALARY table, execute the following:

AUDIT select ON hr.employee_salary;

You can further configure these audit records to record one audit entry for the triggering session or one for each auditable action during the session by specifying BY ACCESS or BY SESSION in the AUDIT statement. This access/session configuration can be defined differently for successful or unsuccessful executions. For example, to make one audit entry per auditable action for successful SELECT statements on the HR.EMPLOYEE_SALARY table, execute the following:

-- one audit entry for each trigging statement
AUDIT select ON hr.employee_salary
   BY ACCESS WHENEVER SUCCESSFUL;

-- one audit entry for the session experiencing one or more
-- triggering statements
AUDIT select ON hr.employee_salary
   BY SESSION WHENEVER NOT SUCCESSFUL;

6.7.3.2. Identifying Enabled Object Auditing Options

The object auditing options that are enabled in the database are recorded in the DBA_OBJ_ AUDIT_OPTS data dictionary view. Unlike the statement and privilege _AUDIT_OPTS views, the DBA_OBJ_AUDIT_OPTS always has one row for each auditable object in the database. There are columns for each object privilege that auditing can be enabled on, and in each of these columns, a code is reported that shows the auditing options. For example, the following report on the HR.EMPLOYEES table shows that no auditing is enabled for the INSERT object privilege and that the SELECT object privilege has auditing enabled with one audit entry for each access when the access is successful and one audit entry for each session when the access is not successful:

SELECT owner, object_name, object_type, ins, sel
FROM dba_obj_audit_opts
WHERE owner='HR'
AND   object_name='EMPLOYEE_SALARY';

OWNER        OBJECT_NAME                OBJECT_TY INS SEL
------------ ------------------------- --------- --- ---
HR           EMPLOYEE_SALARY           TABLE      -/- A/S

The coding for the object privilege columns contains one of three possible values: a dash (-) to indicate no auditing is enabled), an A to indicate BY ACCESS, or an S to indicate BY SESSION. The first code (preceding the slash) denotes the action for successful statements, and the second code (after the slash) denotes the action for unsuccessful statements.

6.7.3.3. Disabling Object Auditing

To disable object auditing, use a NOAUDIT statement, which allows the same WHENEVER options as the AUDIT statement. For example, to disable auditing of unsuccessful SELECT statements against the HR.EMPLOYEES table, execute the following:

NOAUDIT select ON hr.employee_salary WHENEVER NOT SUCCESSFUL;

6.7.4. Purging the Audit Trail

Database audit records for statement, privilege, and object auditing are stored in the table SYS.AUD$. Depending on how extensive your auditing and retention policies are, you will need to periodically delete old audit records from this table. The database does not provide an interface to assist in deleting rows from the audit table, so you will need to do so yourself. To purge audit records older than 90 days, execute the following as user SYS:

DELETE FROM sys.aud$ WHERE timestamp# < SYSDATE −90;

You might want to copy the audit records into a different table for historical retention or export them to an operating system file before removing them. It is a good practice to audit changes to the AUD$ table so that you can identify when changes were made.

6.7.5. Managing Fine-Grained Auditing

Fine-grained auditing (FGA) lets you monitor and record data access based on the content of the data. With FGA, you define an audit policy on a table and optionally a column. When the specified condition evaluates to TRUE, an audit record is created, and an optional event-handler program is called. You use the PL/SQL package DBMS_FGA to configure and manage FGA.

In the following sections, you will learn how to create, drop, enable, and disable fine-grained auditing policies.

6.7.5.1. Creating an FGA Policy

To create a new FGA policy, use the packaged procedure DBMS_FGA.ADD_POLICY. This procedure has the following parameters:

object_schema This is the owner of the object to be audited. The default is NULL, which tells the database to use the current user.

object_name This is the name of the object to be monitored.

policy_name This is a unique name for the new policy.

audit_condition This is a SQL expression that evaluates to a Boolean. When this condition evaluates to either TRUE or NULL (the default), an audit record can be created. This condition cannot directly use the SYSDATE, UID, USER, or USERENV functions, it cannot use subqueries or sequences, nor can it reference the pseudocolumns LEVEL, PRIOR, or ROWNUM.

audit_column This is a comma-delimited list of columns that the database will look to access. If a column in audit_column is referenced in the SQL statement and the audit_condition is not FALSE, an audit record is created. Columns appearing in audit_column do not have to also appear in the audit_condition expression. The default value is NULL, which tells the database that any column being referenced should trigger the audit record.

handler_schema This is the owner of the event-handler procedure. The default is NULL, which tells the database to use the current schema.

handler_module This is the name of the event-handler procedure. The default NULL tells the database to not use an event handler. If the event handler is a packaged procedure, the handler_ module must reference both the package name and program, using dot notation, like this:

UTL_MAIL.SEND_ATTACH_RAW

enable This is a Boolean that tells the database if this policy should be in effect. The default is TRUE.

statement_types This tells the database which types of statements to monitor. Valid values are a comma-delimited list of SELECT, INSERT, UPDATE, and DELETE. The default is SELECT.

audit_trail This parameter tells the database whether to record the SQL statement and bind variables for the triggering SQL in the audit trail. The default value DBMS_FGA.DB_EXTENDED indicates that the SQL statement and bind variables should be recorded in the audit trail. Set this parameter to DBMS_FGA.DB to save space by not recording the SQL statement or bind variables in the audit trail.

audit_column_ops This parameter has only two valid values: DBMS_FGA.ALL_COLUMNS and DBMS_FGA.ANY_COLUMNS. When set to DBMS_FGA.ALL_COLUMNS, this parameter tells the database that all columns appearing in the audit_column parameter must be referenced in order to trigger an audit record. The default is DBMS_FGA.ANY_COLUMNS, which tells the database that if any column appearing in the audit_column also appears in the SQL statement, an audit record should be created.

To create a new disabled audit policy named COMPENSATION_AUD that looks for SELECT statements that access the HR.EMPLOYEES table and references either SALARY or COMMISSION_ PCT, execute the following:

DBMS_FGA.ADD_POLICY(object_schema=>'HR'
   ,object_name=>'EMPLOYEES'
   ,policy_name=>'COMPENSATION_AUD'
   ,audit_column=>'SALARY, COMMISSION_PCT'
   ,enable=>FALSE
   ,statement_types=>'SELECT'),

6.7.5.2. Enabling an FGA Policy

Use the procedure DBMS_FGA.ENABLE_POLICY to enable an FGA policy. This procedure will not raise an exception if the policy is already enabled. For example, you can enable the COMPENSATION_AUD policy added in the previous section like this:

DBMS_FGA.ENABLE_POLICY(object_schema=>'HR'
   ,object_name=>'EMPLOYEES'
   ,policy_name=>'COMPENSATION_AUD'),

If you use direct path inserts, be careful with FGA auditing. If an FGA policy is enabled on a table participating in a direct path insert, the auditing overrides the hint, disabling the direct path access and causing conventional inserts. As with all hints, the database does not directly tell you that your hint is being ignored.

6.7.5.3. Disabling an FGA Policy

To turn off a fine-grained access policy, use the DBMS_FGA.DISABLE_POLICY procedure. Here is an example:

DBMS_FGA.DISABLE_POLICY(object_schema=>'HR'
   ,object_name=>'EMPLOYEES'
   ,policy_name=>'COMPENSATION_AUD'),

6.7.5.4. Dropping an FGA Policy

To remove an FGA audit policy, use the DBMS_FGA.DROP_POLICY procedure. For example, to drop the COMPENSATION_AUD policy used in this section, run this:

DBMS_FGA.DROP_POLICY(object_schema=>'HR'
   ,object_name=>'EMPLOYEES'
   ,policy_name=>'COMPENSATION_AUD'),

6.7.5.5. Identifying FGA Policies in the Database

Query the DBA_AUDIT_POLICIES data dictionary view to report on the FGA policies defined in your database. For example, the following report shows that the policy named COMPENSATION_ AUD on the column SALARY in the table HR.EMPLOYEES is defined, but not enabled:

SELECT policy_name ,object_schema||'.'||
      object_name object_name
      ,policy_column
      ,enabled ,audit_trail
FROM dba_audit_policies;

POLICY_NAME       OBJECT_NAME  POLICY ENABLED AUDIT_TRAIL
---------------- ------------ ------ ------- -----------
COMPENSATION_AUD HR.EMPLOYEES SALARY NO       DB_EXTENDED

Audit records from this policy, when enabled, capture the standard auditing information as well as the text of the SQL statement that triggered the auditing (DB_EXTENDED).

6.7.5.6. Reporting on the FGA Audit Trail Entries

The DBA_FGA_AUDIT_TRAIL data dictionary view is used in reporting on the FGA audit entries that have been recorded in the database. The following example shows audit trail entries for the COMPENSATION_AUD policy, listing the database username and the timestamp of the audit record and computer from which the database connection was made.

SELECT db_user, timestamp, userhost
FROM dba_fga_audit_trail

WHERE policy_name='COMPENSATION_AUD'

DB_USER      TIMESTAMP            USERHOST
------------ -------------------- --------------------
CHIPD        10-Jun-2004 09:48:14 XYZcorpCHIPNOTEBOOK
JUANITA      19-Jun-2004 14:50:47 XYZcorpHR_PC2