Building an Audit Trail for Your Data

Auditing Options

There are several different ways to audit database activity. In Oracle, you may audit things such as database connections, user logins, and so on, storing the results in a data dictionary table. This type of auditing is valuable but what about creating an audit trail for the data itself? Auditing application data is the focus of this article. 

A common, yet simplistic, approach to auditing data requires adding columns such as created_by/created_on, and updated_by/updated_on to every targeted table.  At commit time, these fields are set to the current user and system date. The problem with this approach is that it is recurrent. For example, it conveys the time of last update but provides no snapshot of the actual data as it existed before its current state.

Oracle also allows you to audit data using the ‘AUDIT’ command.  For example, ‘AUDIT DELETE ON my_table;’ will audit deletes on the table my_table.  Several options can be supplied with this command. However, the data is written to the central Oracle auditing table and it’s not possible to specify any criteria for auditing based on business rules. 

Oracle 9i introduces “fine-grained auditing”, enabling you to update an audit table based upon certain criteria provided by the user. For example, audit financial transactions only when the posted amount exceeds $1,000. Finally, many off-the-shelf applications provide their own auditing tables and procedures.

If you’re fortunate enough to have some existing capabilities with a commercial application or you are using Oracle 9i or higher, then by all means use them. Otherwise, the scripts below can be modified as needed to provide a rich audit trail.

Creating Audit Tables

The script to create the audit tables (as well as the script to create audit triggers) builds an executable script by spooling to an operating system file.


--audit_tables.sql creates an audit table for user’s tables.
SET SERVEROUTPUT ON SIZE 500000
SET FEEDBACK OFF
SPOOL build_audit_tables.sql



In the declaration of audit_tables.sql, two cursor definitions are required to obtain table information for any table in the user’s schema that does not have an audit table.  The audit table will be created as the table name (or, for tables with long names, the 1st 26 letters) followed by ‘$AUD’. 


DECLARE
   lv_precision_and_scale    VARCHAR2(20);  
   --Select tables w/o an audit table
   CURSOR cur_tbl2audit IS
     SELECT table_name 
       FROM user_tables 
       WHERE SUBSTR(table_name,1,26)||'$AUD' NOT IN 
        (SELECT table_name 
    FROM user_tables) 
       AND table_name NOT LIKE '%$AUD'
       --Add ineligible tables here: 
       AND table_name NOT IN ('PLAN_TABLE');
   --Select table def of unaudited table.
   CURSOR cur_col2audit(p_tbl2audit USER_TABLES.TABLE_NAME%TYPE) IS 
     SELECT column_name,data_type,data_length,data_precision,data_scale
       FROM user_tab_columns 
       WHERE table_name  = p_tbl2audit 
       --Add ineligible datatypes here : 
       AND data_type NOT IN ('BLOB', 'CLOB','RAW')
       ORDER BY column_id; 



    
Any tables to be ignored can be specified. (In the example, ‘PLAN_TABLE’ is used for generating ORACLE Explain Plans.) When selecting columns with the cur_col2audit cursor, you can exclude any column types you wish from the auditing process.  Due to their size, it might be a good idea to exclude columns like BLOB and CLOB if at all possible.

In the body of the program, the above cursors are opened and processed:
 


BEGIN
  --Retrieve table names: 
  FOR cur_tbl2audit_rec IN cur_tbl2audit LOOP 
    DBMS_OUTPUT.PUT_LINE('CREATE TABLE '|| 
     SUBSTR(cur_tbl2audit_rec.table_name,1,26)||'$AUD (');
     --Retrieve table columns: 
     FOR cur_col2audit_rec 
 IN cur_col2audit(cur_tbl2audit_rec.table_name) LOOP 
   IF cur_col2audit_rec.data_type = 'NUMBER'  THEN
      --Add precision for NUMBER or provide a default.
      IF cur_col2audit_rec.data_precision IS NULL THEN
         lv_precision_and_scale := '38,0)';
           ELSE
         lv_precision_and_scale :=
          cur_col2audit_rec.data_precision||','||
               cur_col2audit_rec.data_scale||')';
           END IF;
           --RPAD adds spaces for easier reading.
           DBMS_OUTPUT.PUT_LINE
            (RPAD(cur_col2audit_rec.column_name,35)||
         cur_col2audit_rec.data_type||'('||
            lv_precision_and_scale||',');
       ELSIF cur_col2audit_rec.data_type IN 
        ('CHAR','VARCHAR','VARCHAR2')  THEN 
         DBMS_OUTPUT.PUT_LINE
     (RPAD(cur_col2audit_rec.column_name,35)||
          cur_col2audit_rec.data_type||'('||
          cur_col2audit_rec.data_length||'),');
       ELSE –no length required.
         DBMS_OUTPUT.PUT_LINE
     (RPAD(cur_col2audit_rec.column_name,35)||
     cur_col2audit_rec.data_type||',');
       END IF;  
     END LOOP; 
        
     --Add audit fields to table: 
     DBMS_OUTPUT.PUT_LINE
 ('aud_action CHAR(3),aud_timestamp DATE,aud_user VARCHAR2(30) )');  
     DBMS_OUTPUT.PUT_LINE('/');
      
  END LOOP; 
EXCEPTION 
  WHEN OTHERS THEN 
    DBMS_OUTPUT.PUT_LINE('Failure creating audit tables : '||
     SUBSTR(SQLERRM,1,200));
    RAISE;
END;
/



Passing Table Names

A table name is retrieved from the first cursor. This table name is then passed as a parameter to the second cursor which obtains column information from the data dictionary. Some data type checking is required so the proper length can be specified, or, in the case of NUMBER, a precision and scale.  

To audit the data, three columns are added to the audit table: aud_action captures the type of DML performed, aud_timestamp receives the system date, and aud_user records the current user performing the action.

The resulting build_script can be immediately executed from the current script.

SPOOL OFF
@build_audit_tables.sql

If the schema contained the single unaudited table described below:

desc audit_test
Name	Null?	Type
TEST_VARCHAR2		VARCHAR2(30)
TEST_CHAR		CHAR(4)
TEST_LONG		LONG
TEST_NUMBER		NUMBER(2
TEST_NUMBER2		NUMBER
TEST_DATE		DATE

The script build_audit_tables.sql will look something like this:

CREATE TABLE audit_test$AUD(
test_varchar2	VARCHAR2(30),
test_char	CHAR(4),
test_long	LONG,
test_number	NUMBER(2,0),
test_number2	NUMBER(38,0),
test_date	DATE,
aud_action CHAR(3),aud_timestamp DATE,	aud_user VARCHAR2(30))/

                                                                   
                                                        
                                                      
                                                                 

Creating the Audit Triggers

Once audit tables exist, a second script is used to create triggers that fire whenever inserts, updates, or deletes occur on the main table.

The code for audit_triggers.sql is below.  As you will notice, it uses many of the same constructs used in the audit_tables.sql. Two cursors are defined for each table name and definition, then processed.


--audit_triggers.sql creates (or replaces) triggers on audit tables.
SET SERVEROUTPUT ON SIZE 1000000
SET FEEDBACK OFF
SPOOL build_audit_triggers.sql
   
DECLARE
  v_prefix      VARCHAR2(5)   := NULL;
  v_condition   VARCHAR2(30)  := NULL;
  --Select all user tables with a corresponding audit table.
  CURSOR cur_tbl2audit IS
    SELECT table_name 
      FROM user_tables a
      WHERE table_name NOT LIKE '%$AUD'
      AND EXISTS 
       (SELECT 'x'
          FROM user_tables b
          WHERE b.table_name  
          = SUBSTR(a.table_name,1,26) || '$AUD');
  --Select table def of audit table, sans audit columns.      
  CURSOR cur_col2audit(p_audittbl USER_TABLES.TABLE_NAME%TYPE) IS 
    SELECT column_name 
      FROM user_tab_columns 
      WHERE table_name  = p_audittbl 
      AND column_name NOT IN 
      ('AUD_ACTION','AUD_TIMESTAMP','AUD_USER') 
 ORDER BY column_id;  
BEGIN
  FOR cur_tbl2audit_rec IN cur_tbl2audit LOOP 
    DBMS_OUTPUT.PUT_LINE('CREATE OR REPLACE TRIGGER '||
     SUBSTR(cur_tbl2audit_rec.table_name,1,23)||'$AUDTRG '||CHR(10)||
     ' AFTER INSERT OR DELETE OR UPDATE '||
     'ON '||cur_tbl2audit_rec.table_name||
     ' FOR EACH ROW ');
    v_prefix       := ':new';
    v_condition    := 'IF INSERTING OR UPDATING THEN';
    DBMS_OUTPUT.PUT_LINE('DECLARE '||CHR(10)||
     'v_operation VARCHAR2(10) := NULL;');
    DBMS_OUTPUT.PUT_LINE('BEGIN ');
    IF v_prefix = ':new' THEN 
       DBMS_OUTPUT.PUT_LINE( 
        '    IF INSERTING THEN '||CHR(10)||
        '       v_operation := ''INS''; '||CHR(10)||
        '    ELSIF UPDATING THEN '||CHR(10)||
        '       v_operation := ''UPD''; '||CHR(10)||
        '    ELSE '||CHR(10)||
        '       v_operation := ''DEL''; '||CHR(10)||
        '    END IF; '||CHR(13));
    END IF;
    LOOP 
      DBMS_OUTPUT.PUT_LINE(v_condition||CHR(10));
      DBMS_OUTPUT.PUT_LINE('   INSERT INTO '||
       SUBSTR(cur_tbl2audit_rec.table_name,1,26) || '$AUD (');
      
      --Loop through 1st to get column names:
      FOR cur_col2audit_rec IN cur_col2audit
       (cur_tbl2audit_rec.table_name) LOOP 
  DBMS_OUTPUT.PUT_LINE(cur_col2audit_rec.column_name|| ',');
      END LOOP;
      
      DBMS_OUTPUT.PUT_LINE('aud_action,aud_timestamp,aud_user) '||
    'VALUES (');
 
      --Loop a 2nd time for the values: 
      FOR cur_col2audit_rec IN cur_col2audit(
       cur_tbl2audit_rec.table_name) LOOP          
       DBMS_OUTPUT.PUT_LINE(v_prefix||'.'||
        cur_col2audit_rec.column_name|| ',');
      END LOOP;
      
      DBMS_OUTPUT.PUT_LINE('v_operation,SYSDATE,USER);'||CHR(10));
   
       EXIT WHEN v_prefix = ':old';
         v_prefix := ':old';
         v_condition := 'ELSE ';
     END LOOP;
     
     DBMS_OUTPUT.PUT_LINE('   END IF;'||CHR(10)||
      'END;'||CHR(10)||'/'||CHR(10));
   END LOOP; 
  
EXCEPTION 
   --Any additional error checking would go here.
   WHEN OTHERS THEN 
     DBMS_OUTPUT.PUT_LINE('Failure building audit triggers : '||
      SUBSTR(SQLERRM,1,200));
     RAISE;
END;
/
SPOOL OFF
--Build the audit triggers:
@build_audit_triggers.sql



Making the Trigger Code Readable

To make the resulting trigger code somewhat readable, I’ve liberally used carriage returns CHR(10) in the print statements(a handy tool whenever dynamically generating code that might need to be debugged later). 

In Oracle, the keywords ‘:new’ and ‘:old’ allow you to reference the new value and old value, respectively, of a table column.  Some logic is required in the trigger itself to differentiate between insert, updates, and deletes.  Two variables, v_prefix and v_condition, and a simple Oracle LOOP, help us build an IF/ELSE statement.

Going back to our previous example, the build_audit_triggers script would look like the following when processing the audit_test table:


CREATE OR REPLACE TRIGGER AUDIT_TEST$AUDTRG 
 AFTER INSERT OR DELETE OR UPDATE ON AUDIT_TEST FOR EACH ROW 
DECLARE 
v_operation VARCHAR2(10) := NULL;   
BEGIN    
IF INSERTING THEN 
   v_operation := 'INS'; 
ELSIF UPDATING THEN 
   v_operation := 'UPD'; 
ELSE 
   v_operation := 'DEL'; 
END IF;     
IF INSERTING OR UPDATING THEN
  INSERT INTO AUDIT_TEST$AUD (  
  TEST_VARCHAR2,     
  TEST_CHAR,  
  TEST_LONG, 
  TEST_NUMBER, 
  TEST_NUMBER2,  
  TEST_DATE,   
  aud_action,aud_timestamp,aud_user)  VALUES 
( 
  :new.TEST_VARCHAR2,             
  :new.TEST_CHAR,       
  :new.TEST_LONG,     
  :new.TEST_NUMBER, 
  :new.TEST_NUMBER2,    
  :new.TEST_DATE,
  v_operation,SYSDATE,USER);
ELSE 
  INSERT INTO AUDIT_TEST$AUD 
( 
  TEST_VARCHAR2,   
  TEST_CHAR, 
  TEST_LONG,
  TEST_NUMBER,    
  TEST_NUMBER2,   
  TEST_DATE,    
  aud_action,aud_timestamp,aud_user)  VALUES 
(  
  :old.TEST_VARCHAR2,            
  :old.TEST_CHAR,        
  :old.TEST_LONG,    
  :old.TEST_NUMBER,             
  :old.TEST_NUMBER2,        
  :old.TEST_DATE,   
  v_operation,SYSDATE,USER);

END IF; 
END;
/



Any inserts, updates, or deletes will be captured in audit_test$aud (actual DML statements omitted):


SELECT test_varchar2,aud_action,TO_CHAR(aud_timestamp,’DD-MON-YY HH24:MI:SS’) aud_timestamp,aud_user FROM audit_test$aud;



TEST_VARCHAR2	AUD_ACTION	AUD_TIMESTAMP	AUD_USER
varchar2_data	INS	15-JAN-04 09:15:00	SCOTT
updated_data	UPD	15-JAN-04 09:16:12	SCOTT
updated_data	DEL	15-JAN-04 09:16:18	SCOTT

When displaying Oracle’s SYSDATE, you’ll need to use TO_CHAR to get the full date and time.

Auditing Tips/Techniques

Customizing

Building on these scripts, other possible enhancements include:

• Add Grant statements that execute immediately following the table DDL.
• Add criteria for when triggers should fire, possibly data-driven from a configuration table. In Oracle, this is done with the ‘WHEN’ clause.  
• Add support for auditing views with ‘INSTEAD OF’ triggers in Oracle 8 and higher.

When customizing, keep in mind that trigger logic must execute quickly. A slow performing trigger can wreak havoc on database performance (see below). Also, if the definition of an audit table is altered (due to modifications to the audited table), the associated trigger should be rebuilt to reflect those changes.

Check Performance

Auditing high-volume tables in production can have serious performance ramifications.  For OLTP systems with a low to moderate amount of volume, a database like Oracle is more than up to the task.  It would be prudent to test the performance with triggers enabled and disabled on any tables in question.  Tables containing configuration data and metadata are ideal candidates for auditing.

Purge old data

Have a system in place to routinely purge audit data.  Active tables can quickly consume disk space with audit information.  Set expiration dates on audit trail data and, if necessary, move it off-line.

Put it on display

An audit trail might be useful to application users.  One example is in an issue tracking application where a user can view previous activity that might provide clues to their problem.  A ‘History’ button can be added to the user interface that will open a window displaying previously audited information for the master record. 

blog comments powered by Disqus