Auditing DML changes in Oracle

auditing sql in oracle

We are often faced with a situation when every DML change (Inserts/Updates/Deletes) made in Oracle/SQL tables must be audited. Banking Softwares and other similar applications have a strict requirement to maintain the audit trail of every single change made to the database.

The DML changes must be audited irrespective of whether it was made from the Front End, during a release, or directly by a production support person while serving a production ticket. Ever wondered how an audit trail of such large numbers tables in your database can be created. Especially when your application is ever-changing with new columns getting added, dropped or modified often.

Triggers in oracle often come handy when fulfilling audit requirements for your database. An audit trigger can be created on the table which will compare the old and new values of all the columns and in case of a difference will log the old record into an audit table. The audit table will have a similar structure to the main table with 3 additional columns AUDIT_BY, AUDIT_AT and AUDIT_ACTION.

Triggers will ensure that the audit trail is maintained irrespective of from where the database change was initiated. However creating such large number of audit tables and triggers manually can be a huge effort. In this article I will demonstrate how easily we can create audit tables and triggers in oracle for database of any size very easily and with very less effort.

Step 1 – Create some tables

Create some sample tables for which you would like to maintain the audit trail.

CREATE TABLE EMPLOYEE ( EID NUMBER, ENAME VARCHAR2 (40) ); CREATE TABLE DEPARTMENT ( DID NUMBER, DNAME VARCHAR2 (40) ); CREATE TABLE SALARY ( EID NUMBER, SALARY NUMBER );
Code language: SQL (Structured Query Language) (sql)

Step 2 – Create an exclude table

There will be always some tables which we would like to exclude from the audit. For example if the table is very huge, contains blob or images, or if the table is rarely modified we might not want to audit it. The exclude table will contain a list of such table which we would like to exclude from the audit.

CREATE TABLE EXAUDIT ( TNAME VARCHAR2 (30) NOT NULL );
Code language: SQL (Structured Query Language) (sql)

In our example let us assume that we want to exclude the department table from the audit. We simply make an entry of this table in our exclude table.

INSERT INTO EXAUDIT (TNAME) VALUES ('DEPARTMENT');
Code language: SQL (Structured Query Language) (sql)

Step 3 – Create audit tables

Now comes the interesting part. We want to create audit tables that will hold the audit trail of all the tables in our database. This can be achieved with a simple procedure like below.

CREATE OR REPLACE PROCEDURE create_audit_tables (table_owner VARCHAR2) IS CURSOR c_tables ( table_owner VARCHAR2) IS SELECT ot.owner AS owner, ot.table_name AS table_name FROM all_tables ot WHERE ot.owner = table_owner AND ot.table_name NOT LIKE 'AUDIT_%' AND ot.table_name <> 'EXAUDIT' AND NOT EXISTS (SELECT 1 FROM EXAUDIT efa WHERE ot.table_name = efa.tname) AND NOT EXISTS (SELECT 1 FROM all_tables at WHERE at.table_name = 'AUDIT_'||ot.table_name); v_sql VARCHAR2 (8000); v_count NUMBER := 0; v_aud VARCHAR2 (30); BEGIN FOR r_table IN c_tables (table_owner) LOOP BEGIN v_aud := 'AUDIT_'||r_table.table_name; v_sql := 'create table ' || v_aud || ' as select * from ' || r_table.owner || '.' || r_table.table_name || ' where 0 = 1'; DBMS_OUTPUT.put_line ('Info: ' || v_sql); EXECUTE IMMEDIATE v_sql; v_sql := 'alter table ' || v_aud || ' add ( AUDIT_ACTION char(1), AUDIT_BY varchar2(50), AUDIT_AT TIMESTAMP)'; EXECUTE IMMEDIATE v_sql; v_count := c_tables%ROWCOUNT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ( 'Failed to create table ' || v_aud || ' due to ' || SQLERRM); END; END LOOP; IF v_count = 0 THEN DBMS_OUTPUT.put_line ('No audit tables created'); ELSE DBMS_OUTPUT.put_line (v_count || ' audit tables created.'); END IF; END; /
Code language: SQL (Structured Query Language) (sql)

After the above procedure is created execute it by passing the schema name (owner) of the schema where your main tables were created.

execute create_audit_tables('SCHEMANAME');
Code language: SQL (Structured Query Language) (sql)

This will create audit tables corresponding to all main tables and with the additional columns like audit_on,audit_by and audit_action. The tables in the exclude table will be excluded.

Step 4 – Create audit triggers

I will first create a small helper function that will give me a comma separated list of columns of a given table (with a prefix if required)

create or replace FUNCTION get_columns_for_table ( table_owner VARCHAR2, t_name VARCHAR2, prefix VARCHAR2 ) RETURN CLOB IS v_text CLOB; BEGIN FOR getrec IN (SELECT column_name FROM all_tab_columns WHERE table_name = t_name AND owner = table_owner AND data_type<>'BLOB') LOOP v_text := v_text || ',' || prefix || getrec.column_name || CHR (10) || ' '; END LOOP; RETURN ltrim(v_text,','); END;
Code language: SQL (Structured Query Language) (sql)

Next create a helper function that will give us a comparison between the columns in case of table updates

create or replace function get_column_comparasion ( table_owner VARCHAR2, t_name VARCHAR2 ) RETURN CLOB IS v_text CLOB; BEGIN FOR getrec IN (SELECT column_name FROM all_tab_columns WHERE table_name = t_name AND owner = table_owner AND data_type<>'BLOB') LOOP v_text := v_text || ' or( (:old.' || getrec.column_name || ' <> :new.' || getrec.column_name || ') or (:old.' || getrec.column_name || ' IS NULL and :new.' || getrec.column_name || ' IS NOT NULL) or (:old.' || getrec.column_name || ' IS NOT NULL and :new.' || getrec.column_name || ' IS NULL))' || CHR (10) || ' '; END LOOP; v_text := LTRIM (v_text, ' or'); RETURN v_text; END;
Code language: SQL (Structured Query Language) (sql)

Next create the procedure that will create our audit triggers

CREATE OR REPLACE PROCEDURE create_audit_triggers (table_owner VARCHAR2) IS CURSOR c_tab_inc ( table_owner VARCHAR2) IS SELECT ot.owner AS owner, ot.table_name AS table_name FROM all_tables ot WHERE ot.owner = table_owner AND ot.table_name NOT LIKE 'AUDIT_%' AND ot.table_name <> 'EXAUDIT' AND ot.table_name NOT IN (SELECT tname FROM EXAUDIT); v_query VARCHAR2 (32767); v_count NUMBER := 0; BEGIN FOR r_tab_inc IN c_tab_inc (table_owner) LOOP BEGIN v_query := 'CREATE OR REPLACE TRIGGER TRIGGER_' || r_tab_inc.table_name || ' AFTER INSERT OR UPDATE OR DELETE ON ' || r_tab_inc.owner || '.' || r_tab_inc.table_name || ' FOR EACH ROW' || CHR (10) || 'DECLARE ' || CHR (10) || ' v_user varchar2(30):=null;' || CHR (10) || ' v_action varchar2(15);' || CHR (10) || 'BEGIN' || CHR (10) || ' SELECT SYS_CONTEXT (''USERENV'', ''session_user'') session_user' || CHR (10) || ' INTO v_user' || CHR (10) || ' FROM DUAL;' || CHR (10) || ' if inserting then ' || CHR (10) || ' v_action:=''INSERT'';' || CHR (10) || ' insert into AUDIT_' || r_tab_inc.table_name || '(' || get_columns_for_table (r_tab_inc.owner, r_tab_inc.table_name, NULL) || ' ,AUDIT_ACTION,AUDIT_BY,AUDIT_AT)' || CHR (10) || ' values (' || get_columns_for_table (r_tab_inc.owner, r_tab_inc.table_name, ':new.') || ' ,''I'',v_user,SYSDATE);' || CHR (10) || ' elsif updating then ' || CHR (10) || ' v_action:=''UPDATE'';' || CHR (10) || ' if ' || get_column_comparasion (r_tab_inc.owner, r_tab_inc.table_name) || ' then ' || CHR (10) || ' insert into AUDIT_' || r_tab_inc.table_name || '(' || get_columns_for_table (r_tab_inc.owner, r_tab_inc.table_name, NULL) || ' ,AUDIT_ACTION,AUDIT_BY,AUDIT_AT)' || CHR (10) || ' values (' || get_columns_for_table (r_tab_inc.owner, r_tab_inc.table_name, ':new.') || ' ,''U'',v_user,SYSDATE);' || CHR (10) || ' end if;' || ' elsif deleting then' || CHR (10) || ' v_action:=''DELETING'';' || CHR (10) || ' insert into AUDIT_' || r_tab_inc.table_name || '(' || get_columns_for_table (r_tab_inc.owner, r_tab_inc.table_name, NULL) || ' ,AUDIT_ACTION,AUDIT_BY,AUDIT_AT)' || CHR (10) || ' values (' || get_columns_for_table (r_tab_inc.owner, r_tab_inc.table_name, ':old.') || ' ,''D'',v_user,SYSDATE);' || CHR (10) || ' end if;' || CHR (10) || 'END;'; DBMS_OUTPUT.put_line ( 'CREATE TRIGGER ' || REPLACE (r_tab_inc.table_name, 'TABLE_', 'TRIGGER_')); EXECUTE IMMEDIATE v_query; DBMS_OUTPUT.put_line ( 'Audit trigger ' || REPLACE (r_tab_inc.table_name, 'TABLE_', 'TRIGGER_') || ' created.'); v_count := c_tab_inc%ROWCOUNT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line ( 'Failed to create audit trigger for ' || r_tab_inc.owner || '.' || r_tab_inc.table_name || ' due to ' || SQLERRM); END; END LOOP; IF v_count = 0 THEN DBMS_OUTPUT.put_line ('No audit triggers created'); END IF; END;
Code language: SQL (Structured Query Language) (sql)

Finally execute the procedure. This will create all the audit triggers.

EXECUTE CREATE_AUDIT_TRIGGERS('SCHEMANAME');
Code language: SQL (Structured Query Language) (sql)

Step 5 – Test the auditing

Now execute a few DML scripts and notice that all changes made to our main tables get audited with appropriate action in the audit tables. Changes to department table will not be audited as we have excluded it.

insert into employee values(1,'John'); insert into employee values(2,'Smith'); insert into department values(1,'Sales'); insert into department values(2,'Purchase'); insert into salary values(1,5000); insert into salary values(2,10000); delete from employee where eid = 1; update employee set ename = 'Raj' where eid = 2;
Code language: SQL (Structured Query Language) (sql)

All tables will have a primary key which never changes. Using the primary key we can query our audit tables and get the entire audit trail when required. Instead of session user we can also set the user from the middle tier in the SYS_CONTEXT.

Here I demonstrated how with few simple procedures you can fulfil the audit requirement of your application. The concepts and scripts here are very small but quite powerful and can be used to create audit trail for any number of tables in your database.

Get our Articles via Email. Enter your email address.

You may also like...

19 Comments

  1. APEX Dev says:

    Great code. Works like a charm.

  2. Dinesh Mishra says:

    when i run execute create_audit_tables(‘SCHEMANAME’); command in oracle 11g SQL command prompt with schemaname. generated error is ORA-00900: invalid SQL statement.
    plz help me. where and how run it in ORACLE 11 g.

    • Pintu Dattani says:

      Dinesh, You are suppose to replace SCHEMANAME with the oracle schema in which you have kept your main tables. looks like you have not done that.

  3. Mini Mini says:

    Hi Viral,

    I am using this procedures as base line for auditing. Now when I am updating a row I just want to put updated values only and dont want other values in the table. Which function and value needs to be modified?

    Regards,
    Mini

  4. Brian Zou says:

    great simple codes.thanks
    I have the same question as ‘Mini’. any more tips?

  5. Mayur says:

    What is the table name to view the auditing details?

  6. GreenDBA says:

    Fantastic.
    Thanks for this post it is extremely useful… Beats hand coding custom triggers for every table in a schema. If you created all of this yourself… good for you… if this was inspired by someone else’s code please source it. :)

  7. ramu says:

    Good artcile

  8. augusto says:

    where I see the audited tables

  9. Rohit says:

    Hi Viral,

    I have used these codes in different schema than schema where my tables are whom I want to be audited. I found whenever I do DML operation on the targeted table, I got each created triggers are executing twice and my audit table is updated twice for each DML. Kindly help me to avoid this situation.

    This article is very useful.. Thanks.. :)

  10. Luis says:

    where I see the audited tables

  11. vicky says:

    Hi Viral

    When i run
    EXECUTE CREATE_AUDIT_TRIGGERS(‘SCHEMANAME’); Getting the below Error:
    Failed to create audit trigger for SCOTT.EMP due to ORA-01031: insufficient privileges

  12. Akshaya Agarwal says:

    I have Executed all the steps Above mentioned however Not able to find the location where I can actually check the DML Changes. Please Help

  13. anonymous says:

    Good article

  14. NoBody says:

    Oracle has in build AUDIT feature, why use these silly thing which serialize the whole thing and slows down the application, Triggers are very error prone.

  15. Neha says:

    Nice article

  16. asif says:

    This is a nice article and could be very helpful if someone told me how we can query audited table?
    Please told

  17. deepan says:

    HOW CAN I DO THIS IN SQL SERVER ?????

  18. helmut says:

    Very good !

Leave a Reply

Your email address will not be published. Required fields are marked *