Announcement Announcement Module
Collapse
No announcement yet.
Auto write audit log information to database Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Auto write audit log information to database

    Hi,

    I am a newbie on the Springframework and Spring DAO, but I still can't prevent myself from asking a question even before I jump up:

    We're on a project with the aspect of intensive data operations, and need logging the data editing history in database, for management purpose. Say, we need trace back on what time, who, made what modification. I believe it is a general enterprise application requirement.

    I still remeber that several years ago I was involved in another C++ project, which we manually wrote the SQL code to insert the log information on every database updating... That's pain and you bet by no mean I want to go the same way. So could somebody give me idea, with Springframework and DAO armed, how to implement it nicely, such as in Hibernate or JDO?

    Thanks a lot in advance.

  • #2
    You can use a nice feature of Hibernate called Interceptor (entityInterceptor in the case of spring as to distinguish it from AOP interceptor). You can add an entityInterceptor with setEntityInterceptor on the HibernateTemplate and HibernateInterceptor (whatever the way you choose to add Hibernate support in your code).
    So you create a bean implementing Interceptor and on the onSave method you can add code which will be called for each object. In the signature you'll see that you can access its id etc ...
    However, I woudln't recommend to directly save an audit entry in the onSave method but rather to save the object in a list and save the whole audit trail in the postFlush method, in which all objects that you'll have saved sooner will have their id set (in the case of new objects with id set by the underlying database).

    HTH

    Olivier

    Comment


    • #3
      Don't forget you can probably achieve the same result by using an AOP Alliance MethodInterceptor against your Dao.create and Dao.update methods.

      Personally I find Hibernate Interceptors less attractive as they're fragile (you can't modify the object and you can't execute queries against the Session), and you are bound to Hibernate. I routinely use both Spring data access mapping as well as Hibernate in the same application, so MethodInterceptor lets you switch ORM approaches easily.

      Comment


      • #4
        In Oracle they have built in auditing functionality that can record such data. The problem is, when accessing from a middle tier through a datasource pool, is identifing the actual user as opposed to the name used by the pool to connect.

        Oracle solves this by a built in function, the dbms_session.set_identifier pl/sql command, that changes the identifier (user) for a particular transaction. I use this via a simple Spring AOP interceptor that calls this function, passing the currently logged in user, before any methods I need to change the identifier for.

        Downside is that it's Oracle specific.

        Comment


        • #5
          Originally posted by jwray
          In Oracle they have built in auditing functionality that can record such data. The problem is, when accessing from a middle tier through a datasource pool, is identifing the actual user as opposed to the name used by the pool to connect.

          Oracle solves this by a built in function, the dbms_session.set_identifier pl/sql command, that changes the identifier (user) for a particular transaction. I use this via a simple Spring AOP interceptor that calls this function, passing the currently logged in user, before any methods I need to change the identifier for.

          Downside is that it's Oracle specific.
          I am interested to know how you attach this Spring AOP interceptor?
          I am working on supporting setting what database schema to use for the given user as I have to support pr. user based schema. I can do this by calling a SQL like what you do to set the user in Oracle. How do you handle this if a user case involvels multie SQL calls in one business call? Do you execute the pl/sql command several times?

          You can check the thread about the multi schema support at
          http://forum.springframework.org/showthread.php?t=10728
          Last edited by robyn; May 14th, 2006, 04:56 PM.

          Comment


          • #6
            I'm not sure this will help in your situation. The Oracle pl/sql call I mentioned does not change the user that is logged in (and hence the schema) but rather what they call the "Client Identifier". It is this client identifier that is recorded in audit logs. Thus, you can still connect as one user with connections from your connection pool but record something (the client identifier) that identifies who is performing the operation.

            They also have the concept of application contexts which allow the definition of additional attributes for a database session. I've not really explored these though, I've not had the need, but they might be useful in your case.

            The code needs to be called within a transaction, but before anything else is called. This means that all calls within the transaction will be given the same client identifier. Here's an example configuration for a service class who's methods will have the client identifier interceptor called before they operate, but after the transaction is started.

            Code:
            <bean id="assayServices" class="org.springframework.aop.framework.ProxyFactoryBean">
                <property name="proxyInterfaces">
                  <value>net.fiveprime.services.AssayServices</value>
                </property>
            	<property name="target"><ref local="assayServicesTarget"/></property>
                <property name="interceptorNames">
                  <list>
                    <value>securityInterceptor</value>
                    <value>transactionInterceptor</value>
                    <value>clientIdentifierInterceptor</value>
                  </list>
                </property>
              </bean>

            Comment


            • #7
              jwray, thanks for the reply.

              Yep it does not help in my situation as you said that you could grap any connection from the pool and set it's "Client Identifier" to whatever you want.

              You said that you must call this pl/sql in a transction and before any other SQL. In my case I must do the same and I could probably do it like you do with the interceptors. I will still get the same connection for 1..n SQL calls since I will use Spring thread-bound Connection transaction support.

              But my soltutions is as Alef outlined above and the DataSourceProxy and it works quite well. Now I am fightig to make it work in Websphere 5.1.

              However the AOP concept is more and more appealing to me. And using Spring it's fairly easy to configure it after you have tried it for a couple of weeks.

              Comment


              • #8
                Setting up client identifier for Oracle

                Originally posted by jwray View Post
                I'm not sure this will help in your situation. The Oracle pl/sql call I mentioned does not change the user that is logged in (and hence the schema) but rather what they call the "Client Identifier". It is this client identifier that is recorded in audit logs. Thus, you can still connect as one user with connections from your connection pool but record something (the client identifier) that identifies who is performing the operation.

                They also have the concept of application contexts which allow the definition of additional attributes for a database session. I've not really explored these though, I've not had the need, but they might be useful in your case.

                The code needs to be called within a transaction, but before anything else is called. This means that all calls within the transaction will be given the same client identifier. Here's an example configuration for a service class who's methods will have the client identifier interceptor called before they operate, but after the transaction is started.

                Code:
                <bean id="assayServices" class="org.springframework.aop.framework.ProxyFactoryBean">
                    <property name="proxyInterfaces">
                      <value>net.fiveprime.services.AssayServices</value>
                    </property>
                	<property name="target"><ref local="assayServicesTarget"/></property>
                    <property name="interceptorNames">
                      <list>
                        <value>securityInterceptor</value>
                        <value>transactionInterceptor</value>
                        <value>clientIdentifierInterceptor</value>
                      </list>
                    </property>
                  </bean>
                Hi,
                Can you please send me code to set the Client Identifier, I have similar requirement like you.
                I have the logged on user id from my web application and I am using spring + hibernate.
                And I want to set the logged user id, so that when user update and saves, the web application logged on user id will be set to database dbms_session.set_identifier.

                Please let me know, how to do this.

                Thank You
                srigold

                Comment


                • #9
                  I would have thought you could just write a filter to bind the userId to threadLocal. In your interceptor do whatever you want with it.

                  Comment


                  • #10
                    Spring + Hibernate + Oracle (Client Identifier)

                    Hi,
                    I had this question before and I am able to resolve this with the help from this forum and I went a ahead and added more information to my blog.

                    Please visit at - http://www.learnspringframework.blogspot.com/

                    srigold

                    Comment

                    Working...
                    X