Results 1 to 7 of 7

Thread: Change auditing

  1. #1
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513

    Unanswered: Change auditing

    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by J Petruk
    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?
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Question

    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
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  4. #4
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    I forgot

    ..this will not give you the true user, but it will
    give you a unique id pr. application session connecting.
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  5. #5
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by Tank
    ..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.
    --
    Jonathan Petruk
    DB2 Database Consultant

  6. #6
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

  7. #7
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by J Petruk
    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •