Happy (Audit) Trails to Oracle E-records Management : Page 2

Implementing Audit Trails in the Database
Database applications usually implement audit trails using triggers on the tables. This is the primary audit trail mechanism. In the trigger-based approach, each table in the database has a corresponding audit log table (also called a history table). This audit log table stores the audit trail data. The database table to be audited has 'BEFORE INSERT', 'BEFORE UPDATE', and 'BEFORE DELETE' triggers. The audit log table contains all the columns that the main table has, as well as the following additional columns:

• INS_USER_ID
• INS_TIMESTAMP
• UPD_USER_ID
• UPD_TIMESTAMP
• DEL_USER_ID
• DEL_TIMESTAMP

The INSERT triggers populate the INS_ columns, UPDATE populates the UPD_ columns, and the DELETE triggers populate the DEL_ columns. The MERGE statement also fires the appropriate triggers (INSERT or UPDATE).

Optionally, instead of columns for each operation, you may find just the following columns:

• MOD_USER_ID
• MOD_TIMESTAMP
• MOD_OPERATION

A PL/Gen utility written by PL/SQL author and instructor Steven Feuerstein may best explain triggers. The following is an example of a template that may be used to generate code in the PL/Gen utility:

DROP TABLE [objname]_aud;

      CREATE TABLE [objname]_aud  (
      [FOREACH]col
         [colname]_o [data_declaration], 
      [ENDFOREACH]   
      [FOREACH]col
         [colname]_n [data_declaration], 
      [ENDFOREACH]   
      [FOREACH]col
         [colname]_f CHAR(1), 
      [ENDFOREACH]
         created_on DATE,
         created_by VARCHAR2(30)   
         );

   CREATE OR REPLACE TRIGGER [objname]_upd_audit
      AFTER UPDATE ON [objname]
      FOR EACH ROW
      DECLARE
         audit_rec [objname]_aud%ROWTYPE;
      BEGIN

      [FOREACH]col
      IF :OLD.[colname] != :NEW.[colname] OR
         (:OLD.[colname] IS NULL AND :NEW.[colname] IS NOT NULL) OR
         (:OLD.[colname] IS NOT NULL AND :NEW.[colname] IS NULL)
      THEN
         audit_rec.[colname]_f := 'Y';
         audit_rec.[colname]_n := :NEW.[colname];
         audit_rec.[colname]_o := :OLD.[colname];
      ELSE
         audit_rec.[colname]_f := 'N';
      END IF;
      [ENDFOREACH]


      INSERT INTO [objname]_aud VALUES (
      [FOREACH]col
         audit_rec.[colname]_O, 
      [ENDFOREACH]   
      [FOREACH]col
         audit_rec.[colname]_N, 
      [ENDFOREACH]   
      [FOREACH]col
         audit_rec.[colname]_f,
      [ENDFOREACH]   
         SYSDATE,
         USER
         );

Another approach to auditing is using the AUDIT statement (provided by Oracle since version 7.x). Using the parameters AUDIT_TRAIL and AUDIT_SYS_OPERATIONS in the initialization file will enable this option. The AUDIT-based approach is useful for all-pervasive, sure-fire (but blind) auditing. You can also audit administrator operations using the AUDIT statement. Such auditing is transaction independent. You can query the system views DBA_STMT_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, and DBA_OBJ_AUDIT_OPTS for reporting on the auditing options in use. You can find out the default auditing options by querying ALL_DEF_AUDIT_OPTS.

The trigger-based approach is applicable for specific, conditional, and intelligent auditing. The rules of transactions govern auditing with custom triggers. System-level triggers may also be used to audit usage—especially the administrator's or DBA's actions—or actions that a user may potentially utilize to tamper with application security and data integrity. A full comparison between custom auditing (using triggers) and system auditing (provided by Oracle) is available in the Oracle documentation in the Application Developer's Guide – Fundamentals.

Auditing large tables may result in a drop in performance. Before rolling out an auditing scheme, you should test the implications on response time.

Previous Page

Next Page