Complete Data Audit

In some situations it's necessary for your application to audit before and after snapshots of data for every transaction. This level of detail is not available from Oracle's own auditing features so this article presents a simple generic solution.

Relational Schema
Package
Trigger Script
Test It

Relational Schema

First we must create a relational schema to work on.

CREATE TABLE audit_logs (
  id              NUMBER(10)    NOT NULL,
  log_timestamp   TIMESTAMP     NOT NULL,
  username        VARCHAR2(30)  NOT NULL,
  object_name     VARCHAR2(30)  NOT NULL,
  action          VARCHAR2(10)  NOT NULL,
  data            CLOB
)
/

ALTER TABLE audit_logs ADD (
  CONSTRAINT audit_logs_pk PRIMARY KEY (id)
)
/

CREATE SEQUENCE audit_logs_seq;

Package

Next we create the package that actually performs the inserts into the audit_logs table. This has been separated out into a package so that addition control features, like on/off audit switches, can be added centrally.

CREATE OR REPLACE PACKAGE tsh_audit AS

PROCEDURE insert_log (p_username  IN  VARCHAR2,
                      p_object    IN  VARCHAR2,
                      p_action    IN  VARCHAR2,
                      p_data      IN  CLOB);
               
END;
/

SHOW ERRORS


CREATE OR REPLACE PACKAGE BODY tsh_audit AS

PROCEDURE insert_log (p_username  IN  VARCHAR2,
                      p_object    IN  VARCHAR2,
                      p_action    IN  VARCHAR2,
                      p_data      IN  CLOB) IS
BEGIN
  INSERT INTO audit_logs (
    id,
    log_timestamp,
    username,
    object_name,
    action,
    data)
  VALUES (
    audit_logs_seq.NEXTVAL,
    SYSTIMESTAMP,
    p_username,
    p_object,
    p_action,
    p_data
  );
END;
               
END;
/
SHOW ERRORS

Trigger Script

A trigger must be defined for each audited table. This can be done using the following script.

SET SERVEROUTPUT ON SIZE 1000000
SET VERIFY OFF
SET TRIMSPOOL ON
DECLARE
  v_owner  VARCHAR2(30) := UPPER('&1');
  v_table  VARCHAR2(30) := UPPER('&2');

  CURSOR c_columns IS
    SELECT column_name
    FROM   all_tab_columns
    WHERE  owner      = v_owner
    AND    table_name = v_table;
BEGIN
  DBMS_OUTPUT.PUT_LINE('CREATE OR REPLACE TRIGGER ' || v_table || '_aiudr_trg');
  DBMS_OUTPUT.PUT_LINE('AFTER INSERT OR UPDATE OR DELETE ON ' || v_table);
  DBMS_OUTPUT.PUT_LINE('FOR EACH ROW');
  DBMS_OUTPUT.PUT_LINE('DECLARE');
  DBMS_OUTPUT.PUT_LINE('	PRAGMA AUTONOMOUS_TRANSACTION;');
  DBMS_OUTPUT.PUT_LINE('	v_action VARCHAR2(30) := ''NONE'';');
  DBMS_OUTPUT.PUT_LINE('BEGIN');
  DBMS_OUTPUT.PUT_LINE('	IF INSERTING THEN');
  DBMS_OUTPUT.PUT_LINE('		v_action := ''INSERT'';');
  DBMS_OUTPUT.PUT_LINE('	ELSIF UPDATING THEN');
  DBMS_OUTPUT.PUT_LINE('		v_action := ''UPDATE'';');
  DBMS_OUTPUT.PUT_LINE('	ELSIF DELETING THEN');
  DBMS_OUTPUT.PUT_LINE('		v_action := ''DELETE'';');
  DBMS_OUTPUT.PUT_LINE('	END IF;');
    
  DBMS_OUTPUT.PUT_LINE('	tsh_audit.insert_log (');
  DBMS_OUTPUT.PUT_LINE('		SYS_CONTEXT(''USERENV'',''CLIENT_IDENTIFIER''),');
  DBMS_OUTPUT.PUT_LINE('		''' || v_table || ''',');
  DBMS_OUTPUT.PUT_LINE('		v_action,');
  FOR cur_rec IN c_columns LOOP
    IF c_columns%ROWCOUNT > 1 THEN
      DBMS_OUTPUT.PUT_LINE('||');
    END IF;
    DBMS_OUTPUT.PUT('		''NEW.' || cur_rec.column_name || '=['' || :NEW.' || cur_rec.column_name || ' || ''] ' ||
                         'OLD.' || cur_rec.column_name || '=['' || :OLD.' || cur_rec.column_name || ' || '']'' ');
  END LOOP;
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT_LINE('	);');
  
  DBMS_OUTPUT.PUT_LINE('	COMMIT;');
  DBMS_OUTPUT.PUT_LINE('END;');
  DBMS_OUTPUT.PUT_LINE('/');
  DBMS_OUTPUT.PUT_LINE('SHOW ERRORS');
END;
/

The script should be saved on the filesystem as audit_trigger.sql and called as follows.

SQL> @audit_trigger my_schema my_table

The resulting trigger can be compiled into the schema.

Note the use of the SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') function. The value of the current user must be set in this context using the following procedure call.

DBMS_SESSION.SET_IDENTIFIER('USER_NAME');

This method is used rather than the USER function so that the system will also work where middle tier application users share a common DB user. In the case of separate DB users this can be altered to use the USER.

Test It

With the schema, package and triggers in place you can test the auditing process by inserting, updating and deleting from the audited tables then querying the audit_logs table.

Hope this helps. Regards Tim...

Back to the Top.