Audit Trail Logging: Transparently Tracking Data Changes in Persistence Layer

Audit is a process that can apply many fields of an enterprise including information systems. Whether it is internal (Audit Department) or external (Outsourced), audit is important to control people, infrastructure and third party relations. Many business processes are carried out by transaction processing systems with a database backend. To track and spot the processes, we need a change history for every transaction. In enterprise applications, this is possible with data change logging. Especially, ERP applications have a built-in audit trail logging feature.

Trail logging has many benefits:
1- It helps audit personnel to detect fraud. It enables us to detect unauthorized or unapproved data manipulation. It only helps because fraud can be possible by legal and normal records if nobody sufficiently controls the business transactions.

2- It provides data change non-repudiation. That means when a user changes a record and claim he/she didn’t change the record, then its validity is precisely determined via inspecting audit trailer logs. Sometimes users may blame the applications for transaction leaks, this is a very nice tool for application developers to justify the right side.

3- Users also use this facility. When a user notices that his/her inserted records are changed, by looking audit trails he can understand who changed the records.

4- Developers also use it to determine abnormal application behavior. An application may inadvertently alter some records and this can be easily debugged if an audit trail is present.

There are 2 types of audit trail logging technique:

Audit Trailing on Table
You add some meta-columns to your tables for data change information like UserInserted, UserInsertDate, UserUpdated, UserUpdateDate.

Pros:
Easy to record trail data
Audit data size is small
Easy to fetch trail data with SQL statements

Cons:
Only one update trail data can be stored
Can’t store changed data
Data and row meta-data(trail data) is merged

Audit Trailing on External Table
An external table may be used to keep change logs. Every successful transaction should be logged to this table. This table may be taken to another database or split to multiple tables to ease management.

Pros:
Insert and every update can be logged
Changed data can be logged
Separated form the transactional data
By adding a transaction id to logs (Log table has a transaction id column), it is possible to see every change in a transaction (This is a shining feature when debugging transaction boundaries).

Cons:
Hard to fetch trail data with SQL
Trail data is bigger since PK columns and table names are also stored within every row.

We used second technique within our ORM Framework (This feature is still missing or hard to use in many other ORM frameworks). We implemented it as a transparent service plugged to persistence layer so that developers don’t have to write any log code or SQL. By the nature of a service, it can be closed and opened when necessary without system restart. It works asynchronously so that application doesn’t wait logging process. It is independent from any DBMS vendor. We named it as “Database Logging Service”.

Architecture of database logging service is as following:



Books:
Integrated Auditing of ERP Systems

Auditing Information Systems: A Comprehensive Reference Guide

Auditing Information Systems