Browse DevX
Sign up for e-mail newsletters from DevX


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

Mandated compliance with various regulations has placed an emphasis on data protection, retention, and security lately. This guide gets past the legalese and tackles electronic records management in Oracle using audit trails.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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:

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:


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.

Thanks for your registration, follow us on our social networks to keep up-to-date