Oracle7 Server Administrator's Guide

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.

Note: In some fields, the Oracle AUDIT command is considered a security audit facility, while triggers can provide a financial audit facility.

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. In contrast, triggers can audit only DML statements issued against tables.

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