Skip to content

Auditing the database

July 27, 2010

A long time ago, we just put some stuff in a database and it was fine. Now, you have to audit the stuff. The audit is now requested for every update in a database: WHO and WHEN someone has changed WHAT. And usually, this kind of information must be available in the application itself.

Every time, you will have to provide these information:

  • who has changed the stuff?
  • when he/she has changed the stuff?
  • what stuff he/she has changed?

And, in this article, I forget the very high level of security where you have to audit in addition of the changes, the access itself: what the information has been retrieved, by who and when? But for some spy agencies, it can be a requirement.

But we consider the information not so secret. Then you have several choices to audit depending of your database and of your application. The basic idea is to create triggers directly in the database on each UPDATE/INSERT/DELETE operation. This ensure to record any change including changes made by someone having a direct access to the database i.e. the helpdesk team. I know a direct access to the database should never be given to a team doing the support of the application, but it happens quite often.

This is a basic, simple idea: using triggers. With triggers, you can store: the timestamp (when?), the table(s) and record(s) changed (what?)  and the user who did it (who?). Unfortunately, you suppose the user connects with his own login. That’s not the case anymore when you use the connection pools. In any JAVA-based (or even PHP-based) application, the connection is done anonymously through a connection pool: every user uses a generic connection. Except if you save the current user in the database itself, you never know you has modified the stuff.

Another issue is: normally, you use one audit table per table to audit. The reason is because it is generally complex to store the changed information on a unique table and using only one table can create bottleneck in the transactions of the database. A second issue is you will do a low-level technical audit. Except if your triggers are smart, a global update (created by a daily program) could create many audit records: and this is not a very good solution.

If you use a 3-tier application, you can create a complete functional audit (at the Service level), but you will never store the information updated outside your application and, if the programmer forgets to add an audit (don’t think a programmer is perfect, usually he generates bugs), you could loose information.

Until now, I have not a perfect solution. Because auditing a database is very complex and need a deep analysis. What do we have to audit (all the tables or only some of them, all the columns or only some of them)? So do we need to audit the changes made outside the application? How many time we have to keep the audit? Do we need a technical or a functional audit? Many questions must be asked before choosing the right solution.

If you use Hibernate, you can use interceptors, it is the same thing than the triggers but at the Hibernate level. This is a not-so-bad solution: you do not have to create triggers at the database level but only at the Hibernate level. Interceptors are transparent for the developers (once the Interceptor is created), you can usually retrieve the user login (who?) and the data modified (what?) because your interceptor is called each time a modification (when?) is made in the database. This is a not too bad solution, and give the opportunity to the programmer to create audit records having a good quality (can be read by people, not only by the hexadecimal guru of the team). In addition, the Hibernate Interceptor has no special requirement to write in the same database and, if needed, the information can be written on flat files. And, if you have also requirements for performance, asynchronously.

If you choose to audit your database through Hibernate, you can read the following article (and also copy/paste the code you will find):

http://community.jboss.org/wiki/AuditLogging

Advertisements

From → Other

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: