Mastering Database Auditing with ORM: Hibernate Audit Table Generation

Keywords:

Database Auditing Spring JPA Auditing Hibernate Envers Entity Versioning JPA EntityManagerFactory Audit Table Configuration Spring Boot ORM Database Audit Logs SQL Triggers in Hibernate Audited Annotation Spring Boot Hibernate Integration JPA Repository Configuration Hibernate Persistence Provider Entity Audit Trail Database Change Monitoring Audit Table Suffix DataSource Configuration EntityManagerFactory Bean Spring Boot Entity Auditing JPA Hibernate Integration Database Table Structure Updates Spring ORM Configuration Database Change Logs Hibernate Audit Trail Spring Boot Data Auditing Database Change Tracking Hibernate JPA Properties Spring Boot Configuration Auditing Database Changes

Introduction

Database auditing is a critical practice for ensuring data integrity, compliance, and security which refers monitoring and logging of persistent entity-related events, or just entity versioning. By leveraging Object-Relational Mapping (ORM) frameworks, you can implement robust auditing systems to track and log entity-related events such as insert, update, and delete operations. This guide will demonstrate how to integrate database auditing into your Spring Boot application using Spring JPA Auditing and Hibernate Envers.

Why Database Auditing Matters

Database auditing provides:

• Compliance Assurance: Meets regulatory requirements like GDPR, HIPAA, and PCI-DSS.

• Data Integrity: Tracks changes to critical data in real-time.

• Enhanced Security: Detects unauthorized modifications.

• Historical Analysis: Enables version control and rollback capabilities.

Getting Started

Implementing auditing requires configuring an EntityManagerFactory and customizing Hibernate properties to manage entity versioning and audit trails effectively.

Here, we are going to leverage Spring JPA and Hibernate features to implement a robust auditing mechanism. The core idea is to set up a JPA EntityManagerFactory, which handles all low-level tasks related to database auditing seamlessly.

Step 1: Configure the EntityManagerFactory

1.  Create a Configuration Class

   Start by creating a configuration class named EntityManagerDatasourceConfig.java. Annotate it with @Configuration to indicate that this class provides Spring-managed beans and configuration.

2. Use the @EnableJpaRepositories annotation at the class level to specify the base packages containing the JPA repository interfaces. This annotation instructs Spring to scan and manage the repositories under the specified package. 

3. Define a Bean for EntityManagerFactory

   Inside the configuration class, define a method annotated with @Bean(name = "entityManagerFactory"). This method will return a LocalContainerEntityManagerFactoryBean instance, which is responsible for creating the EntityManagerFactory

4. Configure the LocalContainerEntityManagerFactoryBean

   • Persistence Provider: Set the persistence provider to Hibernate by calling the setPersistenceProvider() method and passing an instance of HibernatePersistenceProvider

   • Entity Package Scanning: Define the package containing JPA entity classes using the setPackagesToScan() method. This allows the factory to locate and manage the entity classes.

   • Data Source: Inject the javax.sql.DataSource instance into the factory using the setDataSource() method. Ensure the DataSource bean is autowired in the configuration class.

5. Define JPA Properties

   Create a Properties object to hold additional JPA configurations. Configure the properties to meet specific requirements:

• Audit Table Suffix: Use the org.hibernate.envers.audit_table_suffix property to set a custom suffix for audit tables (e.g., _hst).

• Auto Schema Update: Optionally, enable hibernate.hbm2ddl.auto with the value update for development environments. This updates the table structure automatically when changes are made to the entity classes. Avoid using this in production environments.
  
  Assign the configured Properties object to the factory using the setJpaProperties() method:

6. Finalize the Configuration

   Invoke the afterPropertiesSet() method on the LocalContainerEntityManagerFactoryBean to initialize the factory.

7. Return the EntityManagerFactory

   The method should return the EntityManagerFactory instance created by the LocalContainerEntityManagerFactoryBean.
   
   

The following code snippet demonstrates the finalized configuration and implementation of the EntityManagerFactory.

 
 import java.util.Properties;

 import javax.persistence.EntityManagerFactory;
 import javax.sql.DataSource;
 import org.hibernate.jpa.HibernatePersistenceProvider;
 import org.springframework.context.annotation.Bean;
 import org.springframework.context.annotation.Configuration;
 import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
 import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean; import lombok.RequiredArgsConstructor;



 @RequiredArgsConstructor
 @Configuration
 @EnableJpaRepositories(basePackages = "com.devsdebugger.audit.repository")
 public class EntityManagerDatasourceConfig {

     private final DataSource dataSource;

     /**
     * Defines project specific entity manager configurations for Hibernate including table auto-generation
     * and overriding & replace audit table suffix etc.
     * @return javax.persistence.EntityManagerFactory
     */
     @Bean(name = "entityManagerFactory")
     public EntityManagerFactory entityManagerFactory() {

        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setPersistenceProvider(new HibernatePersistenceProvider());
        em.setPackagesToScan("com.cloudofgoods.notification.entity");
        em.setDataSource(dataSource);

        Properties properties = new Properties();
        properties.setProperty("org.hibernate.envers.audit_table_suffix", "_hst"); // Overrides Audit table suffix of "_AUD" to "_hst"
        //properties.setProperty("hibernate.hbm2ddl.auto", "update"); // Note:- Uncomment only when needed to manually update table structure for development purpose          
        em.setJpaProperties(properties);
        em.afterPropertiesSet();

        return em.getObject();
      }
 }

Key Configurations:

• Audit Table Suffix: The org.hibernate.envers.audit_table_suffix property changes the audit table suffix to _hst.

• Auto-Update Mode: hibernate.hbm2ddl.auto ensures table structures are updated during development. Avoid using it in production.

• DataSource: Injects the DataSource bean for database connectivity.

With the above-mentioned changes, the EntityManagerFactory configuration is complete. However, these modifications alone are not sufficient for enabling auditing. To implement auditing, each entity that requires auditing must be annotated with \ (from org.hibernate.envers.Audited), as demonstrated in the following code snippet.

Step 2: Add the @Audited Annotation

Enable auditing by annotating entities with @Audited. Example:

import java.io.Serializable;
import java.sql.Timestamp;

import javax.persistence.*;
import org.hibernate.envers.Audited;

import lombok.Getter;
import lombok.Setter;


@Audited
@Getter
@Setter
@Entity
@Table(name = "notification")
public class Notification implements Serializable {

   private static final long serialVersionUID = 1L;

   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   @Column(name = "notification_id")
   private Long id;

   @Column(name = "user_id")
   private Long userId;

   @Column(name = "firebase_client_token")
   private String token;

   @Enumerated(EnumType.STRING)
   @Column(name = "platform")
   private DevicePlatform platform;

   @Enumerated(EnumType.STRING)
   @Column(name = "status")
   private ActiveStatus activeStatus;

   @Column(name = "last_updated_date_time")
   private Timestamp lastUpdatedDateTime;

   @Column(name = "last_updated_user")
   private String lastUpdatedUser;
}

Step 3: Verify Audit Table Generation

Upon starting your application, Hibernate Envers creates audit tables with the suffix _hst. For example, the entity table notification will have an audit table named notification_hst.

 

Step 4: Tracking Changes

Audit tables capture entity state changes:

• Insert: Logs the initial state.

• Update: Records old and new values.

• Delete: Logs the state before deletion.

Moreover, any changes made to the database table via JPA entities will automatically be reflected in the corresponding audit table, ensuring a comprehensive record of all modifications.

Advanced Configurations

1. Custom Naming Conventions: Adjust audit table prefixes or suffixes.

2. Entity Listeners: Implement custom entity listeners for granular control.

3. Query APIs: Use Hibernate Envers APIs to retrieve historical data for analysis.

Benefits of ORM-Based Database Auditing

• Seamless Integration: Combines Spring Boot, JPA, and Hibernate for a cohesive solution.

• Comprehensive Logging: Tracks every database operation efficiently.

• Regulatory Compliance: Supports industry standards for data auditing.

• Scalability: Handles large-scale applications effortlessly.

Conclusion

Database auditing with ORM frameworks like Hibernate Envers streamlines the process of monitoring and logging entity changes. By integrating these capabilities into your Spring Boot application, you ensure data reliability, security, and compliance with auditing standards. Start implementing auditing today to maintain a robust and secure database infrastructure.

Keywords:

Database Auditing Spring JPA Auditing Hibernate Envers Entity Versioning JPA EntityManagerFactory Audit Table Configuration Spring Boot ORM Database Audit Logs SQL Triggers in Hibernate Audited Annotation Spring Boot Hibernate Integration JPA Repository Configuration Hibernate Persistence Provider Entity Audit Trail Database Change Monitoring Audit Table Suffix DataSource Configuration EntityManagerFactory Bean Spring Boot Entity Auditing JPA Hibernate Integration Database Table Structure Updates Spring ORM Configuration Database Change Logs Hibernate Audit Trail Spring Boot Data Auditing Database Change Tracking Hibernate JPA Properties Spring Boot Configuration Auditing Database Changes