Hello All,
Does anyone have any thoughts on the subject of logging and auditing database actions ?
That is, we I'm looking to create an log of all create, update and delete calls to a database. This is for audit and security purposes. Critically, we must log the username of the user making each action. The basic problem is that the database does not know the name of the user in the application server (WebLogic). I have CMP beans, and stored procedures.

In some older client/server apps (like visual basic to Oracle), the client is logged into Oracle as an Oracle user. To get audit records, a trigger can be added to the table in question. This trigger can add the username in the audit record, because the username is known. This is the behavior I must achieve.
 
Possibilities
1- Custom Audit provider. This looks like the J2EE solution. It does not look complicated to do. It will give audit info for protected resources, like ejb's. So far
As far as I know right now, it will not give me any detail on stored procedure calls. But I'm looking at this as the correct direction.

2- Add parameter to all jdbc ativity. This goes:
Add 2 fields to the database tables- 'created by' and 'last modified by'.
Then, add a constraint to ensure these are non-null.
Add a trigger to check that 'modified by' parameter is included with any update, and log the audit info.
Add these parameters to all database code- CMP ejb's and stored procedures. There is not too much of it, so this is pretty realistic.

3- Modify the database driver class. Like, make a subclass of com.oracle.jdbc.OracleDriver, which does my logging before making a call. This seems a bit devious or abusive.

4- Some app servers and jdbc drivers have a jdbc debug option, which can write a log file showing all jdbc calls. This is close to the required behavior. Remaining issues are
 - the info goes into a file, instead of a database table.
 - and it's an ugly solution! maybe someone will just switch off the debugging.
This could work if I can modify the debug behavior to reflect the comments above. But I still worry it is not really a solid approach.

Well thanks for reading all that. In any case, it has been helpful for me get my random thoughts written down.

-JM

Saw this today: http://www.p6spy.com, might come in handy :)

Some suggestions:

You could use the mechanism used by the p6spy database logger tool (http://www.p6spy.com/), a proxy JDBC driver, which will sit between your application and the real JDBC Driver.

This will achieve 3, without modifying the JDBC Driver. You can obviously obtain all the source code for p6spy, and implement any sort of logging you want, e.g. to a database table, thereby achieving 4.

Yes indeed, and thanks for the replies.

'p6spy.com' does look very handy for lots of purposes. I've just downloaded a monitoring GUI which uses it from www.irongrid.com. Also, I noticed that BEA has something which looks like it... http://edocs.bea.com/wls/docs81/jdbc_drivers/spy.html
It's is mentioned in Oracle magazine also, in the context of testing
http://otn.oracle.com/oramag/oracle/03-may/o33j2ee_2.html

My concern is that it could be more of a debugging/optimizing thing than auditing. But, I guess it would be no more fragile than anything else done outside the database. It does give the option to modify the jdbc request somehow. This may allow me to add the username to the db can do the audit work (possibility #2 above), or implement #3 or #4 as described by mr T Brosnan above. Come to think of it, I could do #3 or #4, and modify the jdbc request to add some flag to say 'AuditTrail=true' or something, so the db knows the audit thing has happened. Configure (somehow) db to reject request if AuditTrail not set. Now that seems like an auditing solution.

Incidentally, I've been working with a custom audit provider. It can log the taking of a protected resource, by not the use of it. For example, it tells me a jdbc connection was taken, but not the sql command.

thanks all,
JM

We were using p6spy and IronGrid but we could not find the IronGrid code to fix some bugs. Then, we started implementing a client for p6spy and we finished implementing the full solution. This application is Elvyx and can be downloaded from: http://www.elvyx.com I think this tool could be useful to the community. Armando ;-) http://www.elvyx.com