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.
ENAME VARCHAR2 (40)
DNAME VARCHAR2 (40)
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.
Finally execute the procedure. This will create all the audit triggers.
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.
insertinto employee values(1,'John');
insertinto employee values(2,'Smith');
insertinto department values(1,'Sales');
insertinto department values(2,'Purchase');
insertinto salary values(1,5000);
insertinto salary values(2,10000);
deletefrom employee where eid = 1;
update employee set ename = 'Raj'where eid = 2;
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.