We're looking to audit UPDATE/INSERT/DELETE activity on a few of our tables.

The way I've usually done it is to create a trigger that uses the CURRENT USER register for the userid, and inserts the row into the audit table with a timestamp, etc.

Now, though, we use Websphere with a ConnectionPool, so all the users come in as the same userid (ie. a project userid, doesn't map to real people).

I found a fairly neat solution, which is to insert a row mapping the "real user" to the "application id":
http://www-900.ibm.com/developerWork...html#section2b

But unfortunately it doesn't really work, since every HTTP request the user gets a new connection - might be the same one, but usually not.

Does anyone have a clever solution to this, short of passing the "real user" to every INSERT/UPDATE/DELETE in some way? I'm trying to avoid that, but doesn't look possible.

I found that Oracle has some extentions to JDBC that let you set a PROXY_USER_NAME, which can then be retrieved on the backend... nice feature.

Db2?

Hi Jonathan

This might be stupid, but can't you use the Audit facility
with the context option - and import the results, then query
the table on the tables you want to audit?

HTH

..this will not give you the true user, but it will
give you a unique id pr. application session connecting.

Because the connection itself is shared accross multiple users within the application, I'm still stuck.

I think I'm going to have to either pass the userid to db2 or let the application itself handle the audit.

I figured this one out!

Java:
((com.ibm.db2.jcc.DB2Connection)con).setDB2ClientU ser("testusr");

Then on server:
VALUES (CLIENT USERID)

Voila, no need to pass the actual userid through SQL.

Caveats:
(1) There is no security on the call, you can pass whatever string you want as the user, even users that don't exist. Could build a security layer on the server side if one really wanted to.

(2) I haven't yet investigated whether something similar can be done from non-Java environments. I assume it can, but need to investigate.

FYI - there is a sample:
samples\c\cli_info.c

That shows how to do this from the C side.