place for auditing interception

[jlawmi]

Hi,

I intend to do auditing of database tables in a particular application using triggers within the database. The decision to use triggers (and not java) is the database has other entry points.

Due to the nature of the typical 3 tier j2ee app (where pooled connections are used), it is not possible to use the session user id when writing the user id (who made the changes). Oracle provides some work around for this, in that it is possible to set things in a session context, that can then be retrieved by a stored procedure.

So, my question is this. If I need to always make sure this is set via jdbc call before any data access code, where would be the best place to put it? Since the application already uses spring transaction management (tx:annotation stuff...) would it make sense to try to do it as a interceptor on the service classes that already have transaction interceptors?

Thanks!

James

[Niels Ull Harremoës]

How will you get the user identity? Unless this is to be hardcoded in configuration, or you have some other smart way of determining it, you will need to set this in your code. And there's no simple way to pass a parameter to an interceptor, unless you get into madness like "annotate the string argument that is the username with @ThisIsTheUsername"

My suggestion would be this:
You will need several co-operating classes.
A DoAsUserTemplate that puts the user into a ThreadLocal scoped bean.

The DoAsUserTemplate would be modelled after e.g. TransactionTemplate and be used like this:

DoAsUserTemplate temp = new DoAsUserTemplate();
temp.execute("SomeUserName", 
   new DoAsUserCallback() {
      doAsUser() { ...};
   }
)

It would first test whether a ThreadLocal bean already existed and throw an exception if it did and representeda different user. Then put the user into the threadLocal bean before calling doAsUser and, in a finally clause, remove it afterwards if it had been put in.

Then wrap your datasource inside a a UserSetupDataSource which extracts the user from the ThreadLocal class and does the Alter Session before passing on the DataSource. It should probably throw an exception, if no user has been set up.

Let us know, if this wuld work for you?

[jlawmi]

Good point...
HI, thanks for the reply. I think I am in luck, as I am using Acegi. So acegi sets a thread local, such that I can access the user via

SecurityContext ctx = SecurityContextHolder.getContext();User myUser = (MYUser)ctx.getAuthentication().getPrincipal();

I should have access to this in my interceptor.

James

[karldmoore]

This should be fine. My last project did exactly the same thing.

[manifoldronin]

Or use Spring AOP to weave the checking code around the DAOs instead of services, for two reasons - logically this validation is a concern of the DAO layer not the service layer; practically a service call doesn't always end up in a jdbc call, so there is no point to check prematurely.