25
Auditing Database Use

This chapter describes how to use the Oracle auditing facilities, and includes the following topics:

• Guidelines for Auditing

• Creating and Deleting the Database Audit Trail Views

• Managing Audit Trail Information

• Viewing Database Audit Trail Information

• Auditing Through Database Triggers

Guidelines for Auditing

This section describes guidelines for auditing and includes the following topics:

• Audit via the Database or Operating System

• Keep Audited Information Manageable

Audit via the Database or Operating System

The data dictionary of every database has a table named SYS.AUD$, commonly referred to as the database audit trail.

Either the database or operating system audit trail can store all audit records generated as the result of statement, privilege, or object auditing.

Your operating system may or may not support database auditing to the operating system audit trail. If this option is available, consider the advantages and disadvantages of using either the database or operating system auditing 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.

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

Alternatively, your operating system audit trail may allow you to consolidate audit records from multiple sources including Oracle and other applications. Therefore, examining system activity might be more efficient because all audit records are in one place.

See Also: Your operating system may also contain an audit trail that stores audit records generated by the operating system auditing facility. However, this facility is operating system-dependent. See your operating system-specific Oracle documentation.

Keep Audited Information Manageable

Although auditing is relatively inexpensive, limit the number of audited events as much as possible. This will minimize the performance impact on the execution of statements that are audited, and minimize the size of the audit trail.

Use the following general guidelines when devising an auditing strategy:

• Evaluate your purpose for auditing.

  After you have a clear understanding of the reasons for auditing, you can devise an appropriate auditing strategy and avoid unnecessary auditing.

  For example, suppose you are auditing to investigate suspicious database activity. This information by itself is not specific enough. What types of suspicious database activity do you suspect or have you noticed? A more focused auditing purpose might be to audit unauthorized deletions from arbitrary tables in the database. This purpose narrows the type of action being audited and the type of object being affected by the suspicious activity.

• Audit knowledgeably.

  Audit the minimum number of statements, users, or objects required to get the targeted information. This prevents unnecessary audit information from cluttering the meaningful information and consuming valuable space in the SYSTEM tablespace. Balance your need to gather sufficient security information with your ability to store and process it.

  For example, if you are auditing to gather information about database activity, determine exactly what types of activities you are tracking, audit only the activities of interest, and audit only for the amount of time necessary to gather the information you desire. Do not audit objects if you are only interested in each session's logical I/O information.

Auditing Suspicious Database Activity

When you audit to monitor suspicious database activity, use the following guidelines:

• Audit generally, then specifically.

  When starting to audit for suspicious database activity, it is common that not much information is available to target specific users or schema objects. Therefore, audit options must be set more generally at first. Once preliminary audit information is recorded and analyzed, the general audit options should be turned off and more specific audit options enabled. This process should continue until enough evidence is gathered to make concrete conclusions about the origin of the suspicious database activity.

• Protect the audit trail.

  When auditing for suspicious database activity, protect the audit trail so that audit information cannot be added, changed, or deleted without being audited.

See Also: For more information about the audit trail, see "Protecting the Audit Trail".

Auditing Normal Database Activity

When your purpose for auditing is to gather historical information about particular database activities, use the following guidelines:

• Audit only pertinent actions.

  To avoid cluttering meaningful information with useless audit records and reduce the amount of audit trail administration, only audit the targeted database activities.

• Archive audit records and purge the audit trail.

  After you have collected the required information, archive the audit records of interest and purge the audit trail of this information.

Creating and Deleting the Database Audit Trail Views

This section describes how to create and delete database audit trail views, and includes the following topics:

• Creating the Audit Trail Views

• Deleting the Audit Trail Views

The database audit trail (SYS.AUD$) is a single table in each Oracle database's data dictionary. To help you view meaningful auditing information in this table, several predefined views are provided. They must be created for you to use auditing; you can later delete them if you decide not to use auditing.

Audit trail views are created automatically when you run the script CATALOG.SQL.

Creating the Audit Trail Views

If you decide to use auditing, create the auditing views by connecting as SYS and running the script CATAUDIT.SQL. This script creates the following views:

• STMT_AUDIT_OPTION_MAP

• AUDIT_ACTIONS

• ALL_DEF_AUDIT_OPTS

• DBA_STMT_AUDIT_OPTS

• USER_OBJ_AUDIT_OPTS, DBA_OBJ_AUDIT_OPTS

• USER_AUDIT_TRAIL, DBA_AUDIT_TRAIL

• USER_AUDIT_SESSION, DBA_AUDIT_SESSION

• USER_AUDIT_STATEMENT, DBA_AUDIT_STATEMENT

• USER_AUDIT_OBJECT, DBA_AUDIT_OBJECT

• DBA_AUDIT_EXISTS

• USER_AUDIT_SESSION, DBA_AUDIT_SESSION

• USER_TAB_AUDIT_OPTS

See Also: For information about these views, see the Oracle8i Reference.

For examples of audit information interpretations, see "Viewing Database Audit Trail Information".

Deleting the Audit Trail Views

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

Managing Audit Trail Information

This section describes various aspects of managing audit trail information, and includes the following topics:

• Events Audited by Default

• Setting Auditing Options

• Enabling and Disabling Database Auditing

• Controlling the Growth and Size of the Audit Trail

• Protecting the Audit Trail

Depending on the events audited and the auditing options set, the audit trail records can contain different types of information. The following information is always included in each audit trail record, provided that the information is meaningful to the particular audit action:

• user name

• session identifier

• terminal identifier

• name of the object accessed

• operation performed or attempted

• completion code of the operation

• date and time stamp

Audit trail records written to the operating system audit trail contain some encodings that are not readable. These can be decoded as follows:

Action Code

This describes the operation performed or attempted. The AUDIT_ACTIONS data dictionary table contains a list of these codes and their descriptions.

Privileges Used

This describes any system privileges used to perform the operation. The SYSTEM_PRIVILEGE_MAP table lists all of these codes, and their descriptions.

Completion Code

This describes the result of the attempted operation. Successful operations return a value of zero, while unsuccessful operations return the Oracle error code describing why the operation was unsuccessful.

Events Audited by Default

Regardless of whether database auditing is enabled,Oracle will always audit certain database-related actions into the operating system audit trail. These events include the following:

instance shutdown	An audit record is generated that lists the OS user shutting down the instance, the user's terminal identifier, the date and time stamp.
connections to the database with administrator privileges	An audit record is generated that lists the OS user connecting to Oracle as SYSOPER or SYSDBA, to provide accountability of users with administrator privileges.

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

Setting Auditing Options

Depending on the auditing options set, audit records can contain different types of information. However, all auditing options generate the following information:

• the user that executed the audited statement

• the action code (a number) that indicates the audited statement executed by the user

• the object or objects referenced in the audited statement

• the date and time that the audited statement was executed

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 on DML statements involving tables by using database triggers.

Oracle allows you to set audit options at three levels:

privilege	audits use of a particular system privilege, such as CREATE TABLE
object	audits specific statements on specific objects, such as ALTER TABLE on the EMP table

See Also: For examples of trigger usage for this specialized type of auditing, see "Auditing Through Database Triggers".

Statement Audit Options

Valid statement audit options that can be included in AUDIT and NOAUDIT statements are listed in the Oracle8i SQL Reference.

Shortcuts for Statement Audit Options

Shortcuts are provided so that you can specify several related statement options with one word.

Shortcuts are not statement options themselves; rather, they are ways of specifying sets of related statement options with one word in AUDIT and NOAUDIT statements. Shortcuts for system privileges and statement options are detailed in the Oracle8i SQL Reference.

Auditing Connections and Disconnections

The SESSION statement option (and CONNECT shortcut) is unique because it does not generate an audit record when a particular type of statement is issued; this option generates a single audit record for each session created by connections to an instance. An audit record is inserted into the audit trail at connect time and updated at disconnect time. Cumulative information about a session such as connection time, disconnection time, logical and physical I/Os processed, and more is stored in a single audit record that corresponds to the session.

See Also: The Oracle8i SQL Reference also lists additional audit options not covered by the shortcuts.

Privilege Audit Options

Privilege audit options exactly match the corresponding system privileges. For example, the option to audit use of the DELETE ANY TABLE privilege is DELETE ANY TABLE. To turn this option on, you would use a statement similar to the following example:

AUDIT DELETE ANY TABLE
    BY ACCESS
    WHENEVER NOT SUCCESSFUL;

Oracle's system privileges are listed in "System Privileges".

Object Audit Options

The Oracle8i SQL Reference lists valid object audit options and the schema object types for which each option is available.

Shortcut for Object Audit Options

The ALL shortcut can be used to specify all available object audit options for a schema object. This shortcut is not an option itself; rather, it is a way of specifying all object audit options with one word in AUDIT and NOAUDIT statements.

Enabling Audit Options

The SQL statement AUDIT turns on statement and privilege audit options, and object audit options. To use it 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.

You can set any auditing option, and specify the following conditions for auditing:

• WHENEVER SUCCESSFUL/WHENEVER NOT SUCCESSFUL

• BY SESSION/BY ACCESS

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 will continue using the audit options in place at session creation.

See Also: For a complete description of the AUDIT command, see the Oracle8i SQL Reference.

For more information about enabling and disabling auditing, see "Enabling and Disabling Database Auditing".

Enabling Statement Privilege Auditing

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 next example:

AUDIT SESSION
BY scott, lori;

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. Normally, the security administrator is the only user granted this system privilege.

Enabling Object Auditing

To audit all successful and unsuccessful DELETE statements on the SCOTT.EMP table, BY SESSION (the default value), enter the following statement:

AUDIT DELETE ON scott.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;

A user can set any object audit option for the objects contained in the user's schema. The AUDIT ANY system privilege is required to set an object audit option for an object contained in another user's schema or to set the default object auditing options; normally, the security administrator is the only user granted this system privilege.

Disabling Audit Options

The NOAUDIT command turns off the various audit options of Oracle. Use it to reset statement and privilege audit options, and object audit options. A NOAUDIT statement that sets statement and privilege audit options can include the BY USER option to specify a list of users to limit the scope of the statement and privilege audit options.

You can use a NOAUDIT statement to disable an audit option selectively using the WHENEVER clause. If the clause is not specified, the auditing option is disabled entirely, for both successful and unsuccessful cases.

The BY SESSION/BY ACCESS option pair is not supported by the NOAUDIT command; audit options, no matter how they were turned on, are turned off by an appropriate NOAUDIT statement.

See Also: For a complete syntax listing of the NOAUDIT command, see the Oracle8i SQL Reference.

Also see "Enabling and Disabling Database Auditing".

Disabling Statement and Privilege Auditing

The following statements turn off the corresponding audit options:

NOAUDIT session;
NOAUDIT session BY scott, lori;
NOAUDIT DELETE ANY TABLE;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,
    EXECUTE PROCEDURE;

The following statements turn off all statement (system) and privilege audit options:

NOAUDIT ALL;
NOAUDIT ALL PRIVILEGES;

To disable statement or privilege auditing options, you must have the AUDIT SYSTEM system privilege.

Disabling Object Auditing

The following statements turn off the corresponding auditing options:

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

Furthermore, to turn off all object audit options on the EMP table, enter the following statement:

NOAUDIT ALL
   ON emp;

Disabling Default Object Audit Options

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

NOAUDIT ALL
   ON DEFAULT;

Note that all schema objects created before this NOAUDIT statement is issued continue to use the default object audit options in effect at the time of their creation, unless overridden by an explicit NOAUDIT statement after their creation.

To disable object audit options for a specific object, you must be the owner of the schema object. To disable the object audit options of an object in another user's schema or to disable default object audit options, you must have the AUDIT ANY system privilege. A user with privileges to disable object audit options of an object can override the options set by any user.

Enabling and Disabling Database Auditing

Any authorized database user can set statement, privilege, and object auditing options at any time, but Oracle does not generate and store audit records in the audit trail unless database auditing is enabled. The security administrator is normally responsible for this operation.

Database auditing is enabled and disabled by the AUDIT_TRAIL initialization parameter in the database's parameter file. The parameter can be set to the following values:

OS	enables database auditing and directs all audit records to the operating system audit trail
NONE	disables auditing (This value is the default.)

After you have edited the parameter file, restart the database instance to enable or disable database auditing as intended.

See Also: For more information about editing parameter files, see the Oracle8i Reference.

Controlling the Growth and Size of the Audit Trail

If the audit trail becomes completely full and no more audit records can be inserted, audited statements cannot be successfully executed until the audit trail is purged. Warnings are returned to all users that issue audited statements. Therefore, the security administrator must control the growth and size of the audit trail.

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

• 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, audit records are generated and stored in the audit trail; if it is disabled, audit records are not generated.

• Be very selective about the audit options that are turned on. If more selective auditing is performed, 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 done two different ways:
  • A security administrator owns all objects and the AUDIT ANY system privilege is never granted to any other user. Alternatively, all schema objects can belong to a schema for which the corresponding user does not have CREATE 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) and the security administrator is the only user granted the AUDIT ANY system privilege.

  In both scenarios, object auditing is controlled entirely by the security administrator.

The maximum size of the database audit trail (SYS.AUD$ table) is predetermined during database creation. By default, up to 99 extents, each 10K in size, can be allocated for this table.

You cannot move SYS.AUD$ to another tablespace as a means of controlling the growth and size of the audit trail. However, you can modify the default storage parameters (except INITIAL) in SYS.AUD$.

See Also: If you are directing audit records to the operating system audit trail, see your operating system-specific Oracle documentation for more information about managing the operating system audit trail.

For more details on the SYS.AUD$ storage parameters, see the Oracle8i Reference.

Purging Audit Records from the Audit Trail

After auditing is enabled for some time, the security administrator may want to delete records from the database audit trail both to free audit trail space and to facilitate audit trail management.

For example, to delete all audit records from the audit trail, enter the following statement:

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';

If audit trail information must be archived for historical purposes, the security administrator can copy the relevant records to a normal database table (for example, using "INSERT INTO table SELECT ... FROM sys.aud$ ...") or export the audit trail table to an operating system file.

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

Note: If the audit trail is completely full and connections are being audited (that is, if the SESSION option is set), 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.

See Also: For information about exporting tables, see Oracle8i Utilities.

Reducing the Size of 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, the space allocated to the database audit trail can be reduced using the following steps:

1. If you want to save information currently in the audit trail, copy it to another database table or export it using the EXPORT utility.

2. Connect as with administrator privileges.

3. Truncate SYS.AUD$ using the TRUNCATE command.

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

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

Protecting the Audit Trail

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

To protect the database audit trail from unauthorized deletions, grant the DELETE ANY TABLE system privilege to security administrators only.

To audit changes made to the database audit trail, use the following statement:

AUDIT INSERT, UPDATE, DELETE
    ON sys.aud$
    BY ACCESS;

Audit records generated as a result of object audit options set for the SYS.AUD$ table can only be deleted from the audit trail by someone connected with administrator privileges, which itself has protection against unauthorized use. As a final measure of protecting the audit trail, any operation performed while connected with administrator privileges is audited in the operating system audit trail, if available.

See Also: For more information about the availability of an operating system audit trail and possible uses, see your operating system-specific Oracle documentation.

Viewing Database Audit Trail Information

This section offers examples that demonstrate how to examine and interpret the information in the audit trail, and includes the following topics:

• Listing Active Statement Audit Options

• Listing Active Privilege Audit Options

• Listing Active Object Audit Options for Specific Objects

• Listing Default Object Audit Options

• Listing Audit Records

• Listing Audit Records for the AUDIT SESSION Option

You may have to audit a database for the following suspicious activities:

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

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

• Rows are arbitrarily being deleted from the EMP table in SCOTT's schema.

As an example, say that you suspect the users JWARD and SWILLIAMS of several of these detrimental actions. The database administrator may then issue the following statements (in order):

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

The following statements are subsequently issued by the user JWARD:

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

The following statements are subsequently issued by the user SWILLIAMS:

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

EXECUTE scott.fire_employee(7902);

The following sections show the information that can be listed using the audit trail views in the data dictionary.

Listing Active Statement Audit Options

The following query returns all the statement audit options that are set:

SELECT * FROM sys.dba_stmt_audit_opts;

USER_NAME               AUDIT_OPTION         SUCCESS         FAILURE
--------------------    -------------------  ----------      ---------
JWARD                   SESSION              BY SESSION      BY SESSION
SWILLIAMS               SESSION              BY SESSION      BY SESSION
                        LOCK TABLE           BY ACCESS       NOT SET

Notice that the view reveals the statement audit options set, whether they are set for success or failure (or both), and whether they are set for BY SESSION or BY ACCESS.

Listing Active Privilege Audit Options

The following query returns all the privilege audit options that are set:

SELECT * FROM sys.dba_priv_audit_opts;

USER_NAME           PRIVILEGE            SUCCESS      FAILURE
------------------- -------------------- ---------   ----------
ALTER USER          BY SESSION           BY SESSION

Listing Active Object Audit Options for Specific Objects

The following query returns all audit options set for any objects contained in SCOTT's schema:

SELECT * FROM sys.dba_obj_audit_opts
    WHERE owner = 'SCOTT' AND object_name LIKE 'EMP%';

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

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

• The character "-" indicates that the audit option is not set.

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

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

• Each audit option has two possible settings, WHENEVER SUCCESSFUL and WHENEVER NOT SUCCESSFUL, separated by "/". For example, the DELETE audit option for SCOTT.EMP is set BY ACCESS for successful delete statements and not set at all for unsuccessful delete statements.

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
--- --- --- --- --- --- --- --- --- --- --- --- ---
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 (see previous example).

Listing Audit Records

The following query lists audit records generated by statement and object audit options:

    SELECT * FROM sys.dba_audit_object;

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

Auditing Through Database Triggers

You can use triggers to supplement the built-in auditing features of Oracle. Although you can write triggers to record information similar to that recorded by the AUDIT command, do so only when you need more detailed audit information. For example, you can use triggers to provide value-based auditing on a per-row basis for tables.

When deciding whether to create a trigger to audit database activity, consider the advantages that the standard Oracle database auditing features provide compared to auditing by triggers:

• Standard auditing options cover DML and DDL statements regarding all types of schema objects and structures.

• All database audit information is recorded centrally and automatically using the auditing features of Oracle.

• Auditing features enabled using the standard Oracle features are easier to declare and maintain and less prone to errors than are auditing functions defined through triggers.

• Any changes to existing auditing options can also be audited to guard against malicious database activity.

• Using the database auditing features, you can generate records once every time an audited statement is issued (BY ACCESS) or once for every session that issues an audited statement (BY SESSION). Triggers cannot audit by session; an audit record is generated each time a trigger-audited table is referenced.

• Database auditing can audit unsuccessful data access. In comparison, any audit information generated by a trigger is rolled back if the triggering statement is rolled back.

• Connections and disconnections, as well as session activity (such as physical I/Os, logical I/Os, and deadlocks), can be recorded by standard database auditing.

When using triggers to provide sophisticated auditing, normally use AFTER triggers. By using AFTER triggers, you record auditing information after the triggering statement is subjected to any applicable integrity constraints, preventing cases where audit processing is carried out unnecessarily for statements that generate exceptions to integrity constraints.

When you should use AFTER row as opposed to AFTER statement triggers depends on the information being audited. For example, row triggers provide value-based auditing on a per-row basis for tables. Triggers can also allow the user to supply a "reason code" for issuing the audited SQL statement, which can be useful in both row and statement-level auditing situations.

The following trigger audits modifications to the EMP table on a per-row basis. It requires that a "reason code" be stored in a global package variable before the update. The trigger demonstrates the following:

• how triggers can provide value-based auditing

• how to use public package variables

Comments within the code explain the functionality of the trigger.

CREATE TRIGGER audit_employee
AFTER INSERT OR DELETE OR UPDATE ON emp
FOR EACH ROW
BEGIN
/* AUDITPACKAGE is a package with a public package
   variable REASON. REASON could be set by the
   application by a command such as EXECUTE
   AUDITPACKAGE.SET_REASON(reason_string). Note that a
   package variable has state for the duration of a
   session and that each session has a separate copy of
   all package variables. */
IF auditpackage.reason IS NULL THEN
   raise_application_error(-20201,'Must specify reason with ',
   'AUDITPACKAGE.SET_REASON(reason_string)');
END IF;

/* If the above conditional evaluates to TRUE, the
    user-specified error number and message is raised,
    the trigger stops execution, and the effects of the
    triggering statement are rolled back. Otherwise, a
    new row is inserted into the pre-defined auditing
    table named AUDIT_EMPLOYEE containing the existing
    and new values of the EMP table and the reason code
    defined by the REASON variable of AUDITPACKAGE. Note
    that the "old" values are NULL if triggering
    statement is an INSERT and the "new" values are NULL
    if the triggering statement is a DELETE. */
INSERT INTO audit_employee VALUES
    (:old.ssn, :old.name, :old.job_classification, :old.sal,
    :new.ssn, :new.name, :new.job_classification, :new.sal,
    auditpackage.reason, user, sysdate );
END;

Optionally, you can also set the reason code back to NULL if you want to force the reason code to be set for every update. The following AFTER statement trigger sets the reason code back to NULL after the triggering statement is executed:

CREATE TRIGGER audit_employee_reset
   AFTER INSERT OR DELETE OR UPDATE ON emp
BEGIN
    auditpackage.set_reason(NULL);
END;

The previous two triggers are both fired by the same type of SQL statement. However, the AFTER row trigger is fired once for each row of the table affected by the triggering statement, while the AFTER statement trigger is fired only once after the triggering statement execution is completed.

---