When it comes to compliance, one of the most difficult things to implement in a database is an audit trail. For some kinds of compliance it is enough to track what data was modified, and who made the modification. Others, you simply need to prove what access a user may have when connected to a database server. The most aggressive compliance will require you track not only what data a user has manipulated, but also requires you to track what data a user has requested.
Proving access capabilities is usually the easiest compliance requirement to implement. A user is configured with the appropriate credentials, and testing begins, evaluating areas of the application they can manipulate, and other areas of the application they cannot manipulate.
This kind of compliance can be implemented in the application itself, using a shared database user connection. This kind of implementation reduces the complexity of the implementation. However, since there is often only one database account, a user may get higher access than intended though bypassing your application, and connecting to the database itself.
In SQL Server it is much safer and easier to grant permissions to a domain group instead. Users granted access to the appropriate groups acquire the necessary credentials for your application. This scenario doesn’t work well for external web based applications. It is better targeted for internal applications. It is the internal applications where you find the most risk of users bypassing your application in the first place.
What about tracking ad-hoc queries? I found I could create an SQL Server trace that would track every query against an SQL instance, executing the trace at SQL Server startup. The trace would capture any user disabling the trace as a worst scenario. I could filter the trace so it wouldn’t capture any of the activity executing using the application accounts.
Application activity is fairly easy to track as well. There are any number of techniques for capturing who modified data, and when it happens. With application history and a standardized trace, you have most of what you would need for the most complete compliance regulation. If you maintain a history of your select queries you end up with full coverage.
Have you had to implement detailed audit requirements? Share your experience in comments or by email to btaylor@sswug.org.
Cheers,
Ben