Data Auditing in NHibernate and SqlServer

Question

I'm using NHibernate on a project and I need to do data auditing. I found this article on codeproject which discusses the IInterceptor interface.

What is your preferred way of auditing data? Do you use database triggers? Do you use something similar to what's dicussed in the article?

Answer 1

I prefer the CodeProject approach you mentioned.

One problem with database triggers is that it leaves you no choice but to use Integrated Security coupled with ActiveDirectory as access to your SQL Server. The reason for that is that your connection should inherit the identity of the user who triggered the connection; if your application uses a named "sa" account or other user accounts, the "user" field will only reflect "sa".

This can be overriden by creating a named SQL Server account for each and every user of the application, but this will be impractical for non-intranet, public facing web applications, for example.

Answer 2

[EDIT]

Post NH2.0 release, please look at the Event Listeners as suggested below. My answer is outdated.

---

The IInterceptor is the recommended way to modify any data in nhibernate in a non-invasive fashion. It's also useful for decryption / encryption of data without your application code needing to know.

Triggers on the database are moving the responsibility of logging (an application concern) in to the DBMS layer which effectively ties your logging solution to your database platform. By encapsulating the auditing mechanics in the persistance layer you retain platform independance and code transportability.

I use Interceptors in production code to provide auditing in a few large systems.

Answer 3

I do like the Interceptor approach mentioned, and use this on the project I'm currently working on.

However, one obvious disadvantage that deserves highlighting is that this approach will only audit data changes made via your application. Any direct data modifications such as ad-hoc SQL scripts that you may need to execute from time to time (it always happens!) won't be audited, unless you remember to perform the audit table insertions at the same time.

Answer 4

I have a similar need to do auditing regardless of whether it came from application or someone issuing some sql via other means. So this must be done at the database level. The database is Oracle. we looked at doing it via Triggers and also via something called Fine Grained Auditing that Oracle provides. In both cases, we turned on auditing on specific tables and specific columns. However, we found that Performance really sucks when we use either of these methods.

Since auditing is an absolute must due to regulations placed around data privacy, I am wondering what is best way to do this without significant performance degradations. If someone has Oracle specific experience with this, it will be helpful as well.

Answer 5

For NHibernate 2.0, you should also look at Event Listeners. These are the evolution of the IInterceptor interface and we use them successfully for auditing.

Answer 6

As an entirely different approach, you could use the decorator pattern with your repositories.

Say I have

public interface IRepository<EntityType> where EntityType:IAuditably
{ 
    public void Save(EntityType entity);
}

Then, we'd have our NHibernateRepository:

public class NHibernateRepository<EntityType>:IRepository<EntityType>
{
   /*...*/
   public void Save ( EntityType entity )
   {
       session.SaveOrUpdate(entity);
   }
}

Then we could have an Auditing Repository:

public class AuditingRepository<EntityType>:IRepository<EntityType>
{
   /*...*/
   public void Save ( EntityType entity )
   {
       entity.LastUser = security.CurrentUser;
       entity.LastUpdate = DateTime.UtcNow;
       innerRepository.Save(entity)
   }
}

Then, using an IoC Framework (StructureMap, Castle Windsor, NInject) you could build it all up without the rest of your code every knowing you had auditing going on.

Of course, how you audit the elements of cascaded collections is another issue entirely...

Answer 7

I understand this is an old question. But I would like to answer this in the light of the new Event System in NH 2.0. Event Listeners are better for auditing-like-functions than Interceptors. Ayende wrote a great example on his blog last month. Here's the URL to his blog post -

ayende.com/Blog/archive/2009/04/29/nhibernate-ipreupdateeventlistener-amp-ipreinserteventlistener.aspx