14

I found couple of discussion threads on this- but nothing which brought a comparison of all three mechanism under one thread.

So here is my question...

I need to audit DB changes- insert\updates\deletes to business objects.

I can think of three ways to do this

1) DB Triggers

2) Hibernate interceptors

3) Spring AOP

(This question is specific to a Spring\Hibernate\RDBMS- I guess this is neutral to java\c# or hibernate\nhibernate- but if your answer is dependent upon C++ or Java or specific implementation of hibernate- please specify)

What are the pros and cons of selecting one of these strategies ?

I am not asking for implementation details.-This is a design discussion.

I am hoping we can make this as a part of community wiki

RN.
  • 997
  • 4
  • 14
  • 31
  • There is another option: At least some databases have their same audit feature. Pro: Very reliant, probably high performance; Con: highly vendor specific – Jens Schauder Aug 12 '09 at 14:30

6 Answers6

5

I only can talk about Triggers and NHibernate, because I don't know enought abou tSpring AOP.

It depends on, as always, what is most important for you.

DB triggers

  • are fast
  • are always called, even from native SQL, Scripts, external apps.
  • write data in the DB of which NH doesn't know about. It will be missing in the current session. (Which could lead to unexpected results)
  • do usually not know anything about your session (say: login name).

NHibernate interceptors / events

  • are not DBMS specific.
  • allow you easy access to you business information, like the user session, client machine name, certain calculations or interpretations, localization, etc.
  • allow you declarative configuration, like attributes on the entity, which define if the entity needs to be logged and how.
  • allow you turning off logging, this could be important for upgrades, imports, special actions that are not triggered by the user.
  • allow you an entity view to the business model. You are probably closer to the users point of view.
Stefan Steinegger
  • 63,782
  • 15
  • 129
  • 193
3

I understand this is not 100% related to the question but it does add value with new options.

There are two more ways you can audit what’s going on.

Reading transaction log: If database is in full recovery mode then all details about INSERT, UPDATE, DELETE and DDL statements are logged into transaction log.

Problem is that it’s very complex to read because it’s not natively supported and that you’ll need a third party transaction log reader such as ApexSQL Log or SQL Log Rescue (the latter one is free but only supports sql 2000).

Advantage of this method is that you literally don’t have to make any changes except to put your database in full recovery mode.

SQL Server traces: Traces will capture everything in trace files including select statements which also may be needed for some compliance scenarios. The downside is that traces are text files that need to be parsed and organized.

Mspaja
  • 748
  • 7
  • 4
2

I can't think of any good reason for not using database triggers to audit changes to the database. Inserts, updates and deletes can potentially hit the database from various sources - triggers will catch all these; Hibernate etc. will not.

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • 1
    I'm with you, the only way to be sure that all activity on the database is audited is to do it at the database level. – HLGEM Apr 20 '09 at 21:33
  • What if you wanted to switch database ? Rewrite all triggers ? – Arun R Sep 20 '10 at 11:31
  • @Icarus: that would be one of MANY things you'd need to do if you switched databases, yes. In reality, businesses don't tend to switch databases that much. – Tony Andrews Sep 20 '10 at 11:45
  • 2
    The only things a database trigger know about the context are the date, and the database login name. When you have an application connecting with a single login, that leaves only the date (and the data change). The value of having only the date depends on the kind of audit you're doing. – Nicolas Mar 03 '11 at 21:55
0

an old question that i chanced upon now.There is one more option available and that is Envers which is available along with hibernate starting from ver 3.6 onwards..

Chetya
  • 1,267
  • 1
  • 17
  • 31
0

Using Hibernate interceptors to perform Audit logs is deeply flawed. I'm stunned by the number of blogs that recommend this method without pointing out its most obvious flaw - the interceptor HAS to use a new transaction to record the audit. Which means you could successfully save the main transaction and have a system crash that fails to record the audit transaction!

Καrτhικ
  • 3,833
  • 2
  • 29
  • 42
  • You would certainly not want a log transaction crash to fail the main transaction. – Arun R Sep 20 '10 at 11:30
  • 2
    I think you would. For if it didn't, then from an Auditor's perspective, your audit-log is no longer the reliable "truth" for what actually happened or did not happen in your system. FYI: We implemented a system where we wrap hibernate entities using Javassist to capture settter method calls and changes (a little more complex for collections) and store this in a "job" attached to the transaction (our layer on top of hibernate allows this) and capture very nicely the audit changes. – Καrτhικ Sep 21 '10 at 20:21
0

I tink when you consider auditing, you need to consider what it is for. First, it is to havea record of who changed what and what changed so you can back out bad changes, you can identify problems with the system (we can see which of several differnt applications casued the change which helps identify quickly which one is broken) and so you can identify who made the change. The last can be really critical when it comes to detecting fraud. If you do everything from the user interface, you will never see the user committing fraud who changes the data in the backend to write himself a check. If you do everything from the interface, likely you have to have permissions set at the tabel level, thus opening the door for fraud to begin with. If you do everything from the interface you won't know which disgruntled employee deleted the entire user table for the pure annoyance value. If you do everything from the front end you won't know which incompetent dba accidentally updated all customer orders to the same customer. I can't support using anything except triggers for auditing as you lose a good part of why you need auditing in the first place.

HLGEM
  • 94,695
  • 15
  • 113
  • 186