Friday, March 18, 2011

Auditing entities with Hibernate JPA

In a real world project you will need to audit certain database changes keeping track of the author of the change. Hibernate includes nowadays the Envers API and here I am showing you how to achieve auditing when using Hibernate as JPA persistence provider.

  1. Include dependencies. I can confirm this works with the below version. 3.5.1 for example fails to update auditing tables. About other versions you will need to try yourself.
    <!-- Hibernate Audit -->
            <dependency>
                <groupId>org.hibernate</groupId>
                <artifactId>hibernate-envers</artifactId>
                <version>3.5.6-Final</version>
            </dependency>
    
  2. In persistence.xml use hibernate.hbm2ddl.auto="create" (I do not like update as it is dangerous to update your schema, I prefer creating it from scratch or failing if it already exists) to ensure tables are created from Java.
    ...
    <properties>
                <property name="hibernate.hbm2ddl.auto" value="create" />
    ...
    
  3. And then add also the properties needed for audit
    <!-- Audit -->
                <property name="hibernate.ejb.event.post-insert"
                value="org.hibernate.ejb.event.EJB3PostInsertEventListener,org.hibernate.envers.event.AuditEventListener" />
                <property name="hibernate.ejb.event.post-update"
                value="org.hibernate.ejb.event.EJB3PostUpdateEventListener,org.hibernate.envers.event.AuditEventListener" />
                <property name="hibernate.ejb.event.post-delete"
                value="org.hibernate.ejb.event.EJB3PostDeleteEventListener,org.hibernate.envers.event.AuditEventListener" />
                <property name="hibernate.ejb.event.pre-collection-update"
                value="org.hibernate.envers.event.AuditEventListener" />
                <property name="hibernate.ejb.event.pre-collection-remove"
                value="org.hibernate.envers.event.AuditEventListener" />
                <property name="hibernate.ejb.event.post-collection-recreate"
                value="org.hibernate.envers.event.AuditEventListener" />
  4. Annotate your entities or fields with @Audited. Let us start with a simple example where you annotate specific fields
    @Audited
        private String home_email;
    
  5. Now you will end up with two tables.
    CREATE TABLE `revinfo` (
      `rev` int(11) NOT NULL AUTO_INCREMENT,
      `revtstmp` bigint(20) DEFAULT NULL,
      PRIMARY KEY (`rev`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3;
    
    CREATE TABLE `employee_aud` (
      `id` int(11) NOT NULL,
      `rev` int(11) NOT NULL,
      `revtype` tinyint(4) DEFAULT NULL,
      `home_email` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`id`,`rev`),
      KEY `FK9900899F8B9EFD24` (`rev`),
      CONSTRAINT `FK9900899F8B9EFD24` FOREIGN KEY (`rev`) REFERENCES `custom_revision_entity` (`id`)
    ) ENGINE=InnoDB;
    
  6. Update the entity and you will get records in both tables. For example:
    select * from employee_aud;
    +----+-----+---------+----------------+
    | id | rev | revtype | home_email     |
    +----+-----+---------+----------------+
    | 79 |   1 |       1 | abc@sample.com |
    +----+-----+---------+----------------+
    1 row in set (0.00 sec)
    
    select * from revinfo;
    +-----+---------------+
    | rev | revtstmp      |
    +-----+---------------+
    |   1 | 1300395363157 |
    +-----+---------------+
    1 row in set (0.02 sec)
    
  7. You might want to use the envers API to retrieve historical data but if you have to provide such data from reports you probably just want to use plain old SQL. For example see the below a query results after two updates for employee Paul:
    SELECT e.first_name, ea.home_email, FROM_UNIXTIME(r.revtstmp / 1000) FROM employee e INNER JOIN employee_aud ea ON e.id = ea.id INNER JOIN revinfo r ON r.rev = ea.rev;
    +------------+-----------------+----------------------------------+
    | first_name | home_email      | FROM_UNIXTIME(r.revtstmp / 1000) |
    +------------+-----------------+----------------------------------+
    | Paula      | abc@sample.com  | 2011-03-17 16:56:03              |
    | Paula      | abcd@sample.com | 2011-03-17 17:04:49              |
    +------------+-----------------+----------------------------------+
    2 rows in set (0.00 sec)
    
  8. We are now in the middle of the task resolution as we need to provide the user that actually made the change. The first thing to be done is to create a JPA entity that will create a third table
    import javax.persistence.Entity;
    
    import org.hibernate.envers.DefaultRevisionEntity;
    import org.hibernate.envers.RevisionEntity;
    
    @Entity
    @RevisionEntity(CustomRevisionListener.class)
    public class CustomRevisionEntity extends DefaultRevisionEntity {
       
        private static final long serialVersionUID = 3775550420286576001L;
        
        private String username;
    
        public String getUsername() {
            return username;
        }
    
        public void setUsername(String username) {
            this.username = username;
        }
    
    }
    
  9. Here is the CustomRevisionListener
    import org.hibernate.envers.RevisionListener;
    import org.springframework.security.core.context.SecurityContextHolder;
    import org.springframework.security.core.userdetails.UserDetails;
    
    public class CustomRevisionListener implements RevisionListener {
         
        public void newRevision(Object revisionEntity) {
            CustomRevisionEntity revision = (CustomRevisionEntity) revisionEntity;
            UserDetails userDetails = (UserDetails) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
            revision.setUsername(userDetails.getUsername());
        }
     
    }
    
  10. Here is the new table (In production I include this as part of the deployment plan). Note that table revinfo will not longer exist. This is the new table containing revision details.
    CREATE TABLE `custom_revision_entity` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `timestamp` bigint(20) NOT NULL,
      `username` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB;
    
  11. Finally after some editions you can pull some report for your manager directly from SQL
    SELECT e.first_name, ea.home_email, FROM_UNIXTIME(cre.timestamp / 1000), cre.username FROM employee e INNER JOIN employee_aud ea ON e.id = ea.id INNER JOIN custom_revision_entity cre ON cre.id = ea.rev;
    +------------+-----------------+-------------------------------------+-------------------+
    | first_name | home_email      | FROM_UNIXTIME(cre.timestamp / 1000) | username          |
    +------------+-----------------+-------------------------------------+-------------------+
    | Paula      | abc@sample.com  | 2011-03-18 11:44:47                 | nurquiza@nu.com |
    | Paula      | abcd@sample.com | 2011-03-18 11:47:19                 | nurquiza@nu.com |
    +------------+-----------------+-------------------------------------+-------------------+
    2 rows in set (0.00 sec)
    
  12. You can annotate whole entities with the exceptions of certain fields. For example let's say we want to audit the whole employee class with the exception of some ManyToOne and OneToMany fields but including at least a ManyToOne relationship
    @Entity
    @Audited
    public class Employee{
    ...
        @NotAudited
        @ManyToOne(fetch = FetchType.LAZY)
        private Office office;
    
        @NotAudited
        @OneToMany
        private List<EmployeePromotionHistory> promotionHistory;
    
        @ManyToOne(fetch = FetchType.LAZY)
        private Department department;
    ...
    }
    
  13. For the above to work you will need to audit the department entity or at least one of its fields:
    @Entity
    public class Department {
    ...
        @Audited
        private String name;
    ...
    }
    
  14. Note that when you assign a different department to the employee a new record will appear in employee_aud table, however no records will show up in department_aud. When a department name is changed then the change is registered in department_aud. Here is where the AuditQuery comes handy in terms of queries.

From now on the audited entity will keep not only the history but also who changed it. Note that you can add other columns creating the possibility to achieve literally anything you want from an entity auditing perspective.

If you want to audit the user iterations you can take a look at my recent post about login requests. Instead of just login to a file you can and use a table where you keep by sessionId all request details. This has proven to be extremely useful for a company I worked for in the past. Thanks to this "flow logging" they basically can do BI on their clients, dispute client billing and so much more.

14 comments:

Jeffrey said...

Very clear explanation! Thanks!

Unknown said...

Hi, thank you for the post. It's very helpful.
I have a question, is it possible to get the rev id right after updating the data (run-time)? For example, after invoking the method that updates the home_email, how to get the rev id 1?
Thanks in advance.

Nestor Urquiza said...

@SCKing Did you look at the Envers API? I have no experience with that part as so far being able to audit through plain SQL is enough for my user stories.

To be able to use annotations to audit and at the same time do other stuff at runtime you will need to hook into the API, I suspect.

Good luck!
-Nestor

Unknown said...

Hi, thank you for the post. It's very helpful.
I have a question,can we add extra column in audit table.
like i want to track the record of when this task done or who done that.
so i want to add extra column in audit table because Envers only add that column which is in mapped class how i achieve this.please help me out

Nestor Urquiza said...

@Amit, you have the username in table custom_revision_entity.

Unknown said...

@Nestor Urquiza,thanks for your quick response
i have a table 'meeting' in which i didn't take username as a field but when audit table generated from this table i want to add extra column "username" in audit table e.g 'meeting_AUD'. i want to track who performed this action.
Means who did what.
so is it possible with Envers that we can add extra column because it only track time stamp when this operation performed.
please help me out.
thanks

Nestor Urquiza said...

@Amit, if you read the post you will notice that envers will not add the user name by itself. You need to provide a hook like explained. The hook creates a new table that you can join with the audited entity table because every time a change is done it registers the username and the timestamp.

Unknown said...

@Nestor Urquiza,thanks a ton,you saved my time

Nestor Urquiza said...

@Amit, my pleasure.

Anonymous said...

I'm trying to achieve a feature to show the old and new value on the UI. Is it possible?

Anonymous said...

Is there way I show the old and new value of a field that has changed?

Nestor Urquiza said...

@Descorpicus you should be able to find a solution with embers queries. See http://docs.jboss.org/envers/docs/index.html#queries

madsammy4u said...

I'm still confused how custom_revision_entity table will be created, how will hibernate get to know that it has to create this table.

Nestor Urquiza said...

@madsammy4u that would be a question for the hibernate team but certainly the source code could be inspected to find out the "how".

Followers