Friday, June 5, 2020

Auditing in MySQL

One of the important requirements in many RDBMS based workloads is to have audit log where any row in any table is stamped with who changed it and  when was it changed? To do this in every place where the table is modified is extremely cumbersome. In this blog post we look at how we can enable the springframework based auditing framework to perform this activity.

Before we do that, we need to upgrade the versions for our dependencies since I have been using this project for quite sometime. Also I decided to use lombok logging annotation and removed all the dependencies on log4j.

Here are the modifications to the pom.xml for setting dependencies.

Now we look at our User entity. We need to add auditing fields to this table. Since in a realistic project one would have multiple entities, we create a abstract entity base class with all the audit fields. We also move the primary key to the abstract base class. When we do that, hibernate provides multiple ways of mapping the entities to the database schema. We want all the parent attributes to be stored in a single table with the child class attribute. To accomplish that, we need to add @MappedSuperClass to the base class.

As we can see, we have added five attributes in the base class. One is the id for all our entities and rest four will be used for auditing purposes.
At this time we also add another layer to our code. Currently all the Endpoints directly call the Repository layer, this causes a problem if we want to write functions that can be reused across different endpoints. An example of this need is retrieveUser method that takes an argument that could be a username or a email. Currently this method lies in the Endpoint layer as a private method. This is a useful method in many different contexts, so we create a new UserService layer and move this method there.

Now, let's get to the original task of enabling auditing. First we define a Auditing Config as below.

We had earlier defined a ThreadLocal that is used by the auditAware method defined above to extract currently logged in user and return its userId. As we can see the audit fields in the AbstractBaseEntity expects a Long for @createdBy and  @LastModifiedBy fields. The EntityAuditConfig also has annotation @EnableJpaAuditing which is required.
At this point we also add a new endpoint called ProfileEndpoint which can be used to manage the entity that represents a user profile. This entity currently only contains a url.
Now if we perform any operation on any of the endpoint, we will see the auditing fields automatically populated. Give it a spin. It is a life saver in many productions applications. I have had situations where users changed their passwords, forgot them and then complained saying that they have been hacked. 
The complete code for this and previous posts can be found at my github repository. This tutorial changes are under v1.5.

No comments:

Post a Comment