Announcement Announcement Module
No announcement yet.
Auditing Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Auditing

    I am in process of architecting a system. There is a push to use several of the core J2EE patterns in the business layer. I've been working in from either side to the business layer. There is a strong auditing requirement on the database side. What I want to do is capture who authenticated and push this through to the database side in a transparent manner.

    Preference is to create an auditing aspect, that all Hibernate objects get. The authentication is pushed through in a transaction set, most likely to a temp table, then when database triggers fire, they can pick up the information from the temp table.

    The naive suggestion at this point is to just put the auditing in the application layer. However, auditing will capture other information on a direct connection to the database. This has to be automatic, so triggers are the best option. The only thing the trigger can't get is the authenticated user who is using a connection from a connection pool.

    So this information has to be communicated as part of a transaction set. How would spring handle such a thing?


  • #2
    What database are you using?

    Oracle has support for doing this sort of thing.


    • #3
      Database agnostic

      We're using Oracle and SQL Server as the test beds. It must be database agnostic.

      As far as database support, the problem is that a user is authenticated through a Realm in Tomcat. Then they use a connection from a pool. How can their identity be communicated to a transaction in Oracle. Most databases support auditing versus who connected. A connection pool obliterates such details. The fact that it must work with SQL Server as well, means that a database independent solution has to be found.

      Spring looks like it hides a lot of details and allows for aspects. I was thinking that some object in Spring might be able to extract the needed details and do it during transaction setup.


      • #4
        How can their identity be communicated to a transaction in Oracle
        It's sort of like setting a ThreadLocal on the connection. You need to set it and then remember to clear it when you're done.

        Apart from that, maybe you could use a base class for your hibernate objects that contains the auditing fields, and add an interceptor to TransactionFactoryProxyBean that sets those fields ?


        • #5

          I think that's exactly what I was looking for. Off to the trenchs...


          • #6
            That's pretty much what I do. I have a Java threadlocal that is set to the web user name when the web controller starts. Then I have an "AuditingDataSource" that wraps the regular datasource. When getConnection is called it gets the connection from the wrappped DataSource and calls a stored procedure passing in the user name taken from the Java thread local. When this is done it returns the "prepared" connection. The stored procedure handles the database side of things and would probably be different for Oracle vs. SQLServer.