Problem solve Get help with specific problems with your technologies, process and projects.

Are you ready for a compliance audit?

SOX, HIPAA, SB1386, EUDPA, GLBA. Are you ready?

Today, everyone knows the acronyms: SOX, HIPAA, SB1386, EUDPA, GLBA. Yet companies are still trying to meet requirements to audit access to corporate information and produce reports detailing who has changed, or seen, that information.

Auditing database activity has always been an excellent practice to improve business operations and safeguard data integrity. However, proving that there is a recorded audit trail of who has accessed, or even tried to access, specific information is more than just good business today; it is essential.

What is the best way to audit data? Let's look at the best practices for auditing data access and database changes and the available techniques.

Best Practices: Setting Your Database Auditing Goals

Enterprises with effective data auditing solutions are driven by dual goals of accountability and visibility: while they developed suitable, company-wide privacy and security policies and mechanisms, they also recognized that these policies still leave the organization vulnerable from within and implemented auditing to understand what actually happened.

Capturing records of data activity is an essential step, but this must be done in a trusted and comprehensive way. Several common approaches miss key activities, introduce a false sense of security, and interfere with runtime database performance. By understanding the required capabilities of an effective solution, enterprises can overcome these shortcomings.

Required Capabilities for a Data Auditing Solution

Certain capabilities should be part of any data auditing solution:

  • Capture Data Access: Automatically track whenever data is modified or viewed by any means on target databases, preferably with control over the granularity of data tracked
  • Capture Structural Changes: Automatically track changes both to the permissions that control data access and to database schema (to ensure ongoing integrity of the structures storing data)
  • Manage Captured Information: Automatically consolidate the tracked information from multiple databases into an easily managed, long-term common repository
  • Centralize Configuration & Management of All Servers: Provide a straightforward way to configure auditing of all of the target servers, specify the activities of interest, the repository for managing the information, and the schedule for transferring data
  • Flexible Information Access: Provide flexible and efficient means for processing the stored information to identify activities of interest
  • Produce Reports: Ad hoc and periodic exporting of analysis results in a variety of formats, for display, printing, and transmission
  • Detect Conditions of Interest for Notification: Automatically monitoring for conditions of interest and generating selected alerts.

Data Auditing Options

Some approaches to data auditing are subject to pitfalls that may create compliance risk or increase the costs of implementing compliance solutions.

Application modification entails changing the source code of every application that might be used to access the data of interest. Each application is changed so that it captures data modification and viewing activity and stores it for further processing.

This approach requires each application to be modified (or, if that is not possible such as with legacy applications, it must be replaced). Planning, implementing, and testing these changes is costly and time-consuming, and it is difficult to guarantee complete coverage.

Furthermore, access outside the modified applications (e.g., via a database administrative console) is not captured, implying incomplete coverage, and changes to permissions and schema cannot be captured by this means. These problems exist with any approach not tied directly to the target database, rather than the applications or the network.

Trigger-based collection at the data source. Most users dread triggers (special-purpose application logic) on the database, because of the drawbacks:

  • they are hard to write correctly;
  • they add substantial runtime performance overhead (because they execute in line with transactions, reducing throughput);
  • fear of overhead leads DBAs to minimize the number of modifications recorded or the period over which they are recorded;
  • they cannot capture data views or changes to schema and permissions.

A Preferred Audit Approach: Non-Trigger Tracking at the Data Source

Non-trigger audit agents are associated with each database server containing important data. These audit agents are responsible for harvesting information about data-related activity, and because they operate at the database server, they capture all relevant data activity, regardless of the application used, including direct backdoor access. Applications need not be modified to accommodate this approach.

The audit agent harvests information through two primary means. It can read the database transaction log, which each database maintains in the normal course of its operation, to gather data modifications and other activity. Using the transaction log does not interfere with the timely execution of transactions, because the analysis can be time-shifted or carried out on machines other than the one hosting the target database. In addition, the agent can use the database's built-in event notification mechanism to obtain additional information, such as permission changes and data viewing activities.

The non-trigger audit agent approach offers maximum coverage without disruption to applications and systems. Unlike application modifications, mid-tier portals, and triggers, this approach is easy and cost-effective to install and maintain.


By adopting a comprehensive approach to data auditing, enterprises can meet government regulations and institute good business practices to protect their critical data assets. In parallel with the development of policies, procedures, and technical requirements for data integrity, it is critical to identify a solution that effectively captures and audits data-related activity.

The best technical approach to data auditing minimizes performance overhead while consolidating a complete audit of data access across multiple servers and providing active monitoring and alerting. Many approaches have shortcomings that negatively affect risk and system performance and require additional technical resources.

The non-trigger audit agent approach offers maximum coverage without disruption to applications and systems. Prudent organizations are implementing these solutions to meet today's data auditing requirements.

About the author

Dr. Mazer is co-founder of Lumigent Technologies, Inc. He can be reached at

Dig Deeper on SQL Server Database Compliance

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.