Design Considerations for Audit Trail Implementation

Auditing the operations in any application is a very common requirement for security and auditing purpose. Auditing can be done at various levels with varying level of details. Overall prime requirements for Audit Trail are:
  • Collect enough information to determine, 
    • Who has done the changes, 
    • What are the changes, 
    • When these changes have been done, Locale etc
    • What user scenario was in action, i.e. the operation context
  • To capture all user actions, even if some of these are not reaching to DB or would be interacting with external services directly (rare, but happens in some cases)
  • Successful and unsuccessful logons or other security related operations
  • Management of collected audit data, which could be very large. 
  • Search operations on collected data
  • Display the collected data to Users in presentable format as and when demanded 

Considering above requirements, there are various design approaches to implement the audit trail in system. Few of these are given below (considering a JEE application, however, many of these are generic)

  1. Implement DB triggers to capture any change in data states and log in audit tables
  2. Log audit data in business services, which means collecting the required data in each business service operation and log it using some abstract Audit service to some data store
  3. Using generic logging framework like Log4J to log the auditing information to desired format and data store and later parse these logs to extract the required data
  4. Interceptors based approach to intercept all operations, collect the data and log it through Audit service. Here interceptors can be of DAO layer interceptors like Hibernate Interceptors or Service level interceptor using AOP.

All of these approaches have their own pros and cons. As obvious, one approach can not be fit for all. Application state, project requirements and scope can define the right set of implementation. So lets discuss the pros and cons of each of these approaches to understand it more.

Database triggers

Database triggers can do really well and nothing can escape from the triggers. Once these are set carefully on all levels, all changes are bound to pass through these and hence will be captured in audit tables.

Pros:

  • Whether it is simple user operation, or any change is being done by DB admin; all kind of changes will be captured by triggers.
  • A comparatively simple approach, in aspect, that no code change is required and it can be implemented directly at database level without disturbing the code.