如何对数据库操作进行审计？

​在对关系型数据库的处理上，我们经常希望能对表的创建和更新操作进行审计，即在表上增加四个标准的字段：创建人、创建时间、修改人、修改时间。

当新建一条记录时，将触发记录新增的用户和操作时间记录在创建人和创建时间两个字段中；当修改一条记录时，将触发记录修改的用户和操作时间记录在修改人和修改时间两个字段中。

以前做项目的时候，会强制开发人员写的SQL中要手动设置创建人、创建时间、修改人、修改时间值并记录到数据库中。

但是，这种靠Code Review和制度规范约定的方式，比较古老且不可靠。

那有没有什么办法可以将审计功能封装在框架中，不再依赖于开发人员手动设置相关属性呢？

理一理逻辑线

首先，我们尝试给数据库中的数据进行分类：

• 系统基础类数据：比如各类码值的定义表，系统的状态定义表等。这些数据和用户无关，大部分是在系统初始化的时候，由管理员配置写入的。对于这类数据的操作审计，通常需要通过数据库管理控制台处理，而不是应用系统。

• 系统配置类数据：和业务相关的配置信息。比如审批系统中的流程定义数据、环节定义数据等；规则引擎中的规则定义数据、算子定义数据等。这些数据，通常是在系统初始化时，由系统自行创建写入，对这些数据的操作进行审计，意义不大。

• 用户私有数据：用户生产的数据，每个客户的数据都不一样，且需要做数据安全隔离。这部分数据的审计一般需要通过系统执行，由系统识别当前操作的发起人，并记录审计信息。

我们重点看用户私有数据的审计该如何实现。

如何识别当前操作的发起人是完成整个审计框架的核心。这就要求：业务系统在发起对用户私有数据进行操作之前，必须完成用户的认证和授权操作。

在B/S应用中，如何完成用户的认证和授权呢？

答案很明显：Spring Security框架！Spring Security框架的目标便是实现对应用的安全防护、用户认证和用户授权的管理。

因此，看起来我们可以结合Spring Security和Spring Data，完成关系型数据库中数据操作的审计功能。

下面，我们来搭建DEMO，看下具体该如何实现该功能：

创建应用

创建一个名为SpringDataAuditor的maven工程，如下图所示：

引入依赖

修改pom.xml文件，增加相关依赖包

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>SpringDataAuditor</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.3.RELEASE</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>
    </dependencies>
</project>

spring-boot-starter-web表示我们要使用Spring WebMvc；spring-boot-starter-security是在Spring Boot应用中启用Spring Security特性；spring-boot-starter-data-jpa是引入JPA框架；mysql-connector-java是数据连接用的JDBC，我们使用MySQL数据库；lombok是为了少写一点get/set函数。

运行mvn dependency:tree命令，查看包依赖关系如下：

future@FuturedeMacBook-Pro SpringDataAuditor % mvn dependency:tree
[INFO] Scanning for projects...
[INFO] 
[INFO] -------------------< org.example:SpringDataAuditor >--------------------
[INFO] Building SpringDataAuditor 1.0-SNAPSHOT
[INFO] --------------------------------[ jar ]---------------------------------
[INFO] 
[INFO] --- maven-dependency-plugin:3.1.2:tree (default-cli) @ SpringDataAuditor ---
[INFO] org.example:SpringDataAuditor:jar:1.0-SNAPSHOT
[INFO] +- org.springframework.boot:spring-boot-starter-web:jar:2.3.3.RELEASE:compile
[INFO] |  +- org.springframework.boot:spring-boot-starter:jar:2.3.3.RELEASE:compile
[INFO] |  |  +- org.springframework.boot:spring-boot:jar:2.3.3.RELEASE:compile
[INFO] |  |  +- org.springframework.boot:spring-boot-autoconfigure:jar:2.3.3.RELEASE:compile
[INFO] |  |  +- org.springframework.boot:spring-boot-starter-logging:jar:2.3.3.RELEASE:compile
[INFO] |  |  |  +- ch.qos.logback:logback-classic:jar:1.2.3:compile
[INFO] |  |  |  |  \- ch.qos.logback:logback-core:jar:1.2.3:compile
[INFO] |  |  |  +- org.apache.logging.log4j:log4j-to-slf4j:jar:2.13.3:compile
[INFO] |  |  |  |  \- org.apache.logging.log4j:log4j-api:jar:2.13.3:compile
[INFO] |  |  |  \- org.slf4j:jul-to-slf4j:jar:1.7.30:compile
[INFO] |  |  +- jakarta.annotation:jakarta.annotation-api:jar:1.3.5:compile
[INFO] |  |  +- org.springframework:spring-core:jar:5.2.8.RELEASE:compile
[INFO] |  |  |  \- org.springframework:spring-jcl:jar:5.2.8.RELEASE:compile
[INFO] |  |  \- org.yaml:snakeyaml:jar:1.26:compile
[INFO] |  +- org.springframework.boot:spring-boot-starter-json:jar:2.3.3.RELEASE:compile
[INFO] |  |  +- com.fasterxml.jackson.core:jackson-databind:jar:2.11.2:compile
[INFO] |  |  |  +- com.fasterxml.jackson.core:jackson-annotations:jar:2.11.2:compile
[INFO] |  |  |  \- com.fasterxml.jackson.core:jackson-core:jar:2.11.2:compile
[INFO] |  |  +- com.fasterxml.jackson.datatype:jackson-datatype-jdk8:jar:2.11.2:compile
[INFO] |  |  +- com.fasterxml.jackson.datatype:jackson-datatype-jsr310:jar:2.11.2:compile
[INFO] |  |  \- com.fasterxml.jackson.module:jackson-module-parameter-names:jar:2.11.2:compile
[INFO] |  +- org.springframework.boot:spring-boot-starter-tomcat:jar:2.3.3.RELEASE:compile
[INFO] |  |  +- org.apache.tomcat.embed:tomcat-embed-core:jar:9.0.37:compile
[INFO] |  |  +- org.glassfish:jakarta.el:jar:3.0.3:compile
[INFO] |  |  \- org.apache.tomcat.embed:tomcat-embed-websocket:jar:9.0.37:compile
[INFO] |  +- org.springframework:spring-web:jar:5.2.8.RELEASE:compile
[INFO] |  |  \- org.springframework:spring-beans:jar:5.2.8.RELEASE:compile
[INFO] |  \- org.springframework:spring-webmvc:jar:5.2.8.RELEASE:compile
[INFO] |     +- org.springframework:spring-context:jar:5.2.8.RELEASE:compile
[INFO] |     \- org.springframework:spring-expression:jar:5.2.8.RELEASE:compile
[INFO] +- org.springframework.boot:spring-boot-starter-security:jar:2.3.3.RELEASE:compile
[INFO] |  +- org.springframework:spring-aop:jar:5.2.8.RELEASE:compile
[INFO] |  +- org.springframework.security:spring-security-config:jar:5.3.4.RELEASE:compile
[INFO] |  |  \- org.springframework.security:spring-security-core:jar:5.3.4.RELEASE:compile
[INFO] |  \- org.springframework.security:spring-security-web:jar:5.3.4.RELEASE:compile
[INFO] +- org.springframework.boot:spring-boot-starter-data-jpa:jar:2.3.3.RELEASE:compile
[INFO] |  +- org.springframework.boot:spring-boot-starter-aop:jar:2.3.3.RELEASE:compile
[INFO] |  |  \- org.aspectj:aspectjweaver:jar:1.9.6:compile
[INFO] |  +- org.springframework.boot:spring-boot-starter-jdbc:jar:2.3.3.RELEASE:compile
[INFO] |  |  +- com.zaxxer:HikariCP:jar:3.4.5:compile
[INFO] |  |  \- org.springframework:spring-jdbc:jar:5.2.8.RELEASE:compile
[INFO] |  +- jakarta.transaction:jakarta.transaction-api:jar:1.3.3:compile
[INFO] |  +- jakarta.persistence:jakarta.persistence-api:jar:2.2.3:compile
[INFO] |  +- org.hibernate:hibernate-core:jar:5.4.20.Final:compile
[INFO] |  |  +- org.jboss.logging:jboss-logging:jar:3.4.1.Final:compile
[INFO] |  |  +- org.javassist:javassist:jar:3.24.0-GA:compile
[INFO] |  |  +- net.bytebuddy:byte-buddy:jar:1.10.14:compile
[INFO] |  |  +- antlr:antlr:jar:2.7.7:compile
[INFO] |  |  +- org.jboss:jandex:jar:2.1.3.Final:compile
[INFO] |  |  +- com.fasterxml:classmate:jar:1.5.1:compile
[INFO] |  |  +- org.dom4j:dom4j:jar:2.1.3:compile
[INFO] |  |  +- org.hibernate.common:hibernate-commons-annotations:jar:5.1.0.Final:compile
[INFO] |  |  \- org.glassfish.jaxb:jaxb-runtime:jar:2.3.3:compile
[INFO] |  |     +- jakarta.xml.bind:jakarta.xml.bind-api:jar:2.3.3:compile
[INFO] |  |     +- org.glassfish.jaxb:txw2:jar:2.3.3:compile
[INFO] |  |     +- com.sun.istack:istack-commons-runtime:jar:3.0.11:compile
[INFO] |  |     \- com.sun.activation:jakarta.activation:jar:1.2.2:runtime
[INFO] |  +- org.springframework.data:spring-data-jpa:jar:2.3.3.RELEASE:compile
[INFO] |  |  +- org.springframework.data:spring-data-commons:jar:2.3.3.RELEASE:compile
[INFO] |  |  +- org.springframework:spring-orm:jar:5.2.8.RELEASE:compile
[INFO] |  |  +- org.springframework:spring-tx:jar:5.2.8.RELEASE:compile
[INFO] |  |  \- org.slf4j:slf4j-api:jar:1.7.30:compile
[INFO] |  \- org.springframework:spring-aspects:jar:5.2.8.RELEASE:compile
[INFO] +- mysql:mysql-connector-java:jar:8.0.21:compile
[INFO] \- org.projectlombok:lombok:jar:1.18.12:provided
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  5.080 s
[INFO] Finished at: 2020-09-27T12:03:23+08:00
[INFO] ------------------------------------------------------------------------

调整工程

分别创建org.example、org.example.controller、org.example.entity、org.example.repository四个package。

在org.example下新建Application类，添加Spring Boot应用启动的标准代码：

package org.example;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class);
    }
}

在src/main/resources下新增application.properties文件，添加如下配置项：

spring.datasource.url=jdbc:mysql://localhost:3306/jpa_demo?characterEncoding=utf8&useSSL=false
spring.datasource.username=root
spring.datasource.password=root1234
spring.jpa.generate-ddl=true

数据库的连接串、用户名、密码需要根据实际数据库信息修改。

调整完的目录结构如下：

编写业务代码

我们假设有个Comment类，保存的是所有的评论信息。

在org.example.entity目录下，我们新建Comment实体类：

package org.example.entity;

import lombok.Data;
import org.springframework.data.annotation.CreatedBy;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedBy;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import javax.persistence.Entity;
import javax.persistence.EntityListeners;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import java.util.Date;

@Entity
@Data
@EntityListeners(AuditingEntityListener.class)
public class Comment {
    @Id @GeneratedValue(strategy= GenerationType.AUTO)
    private Long id;
    private String details;

    @CreatedBy private String creator;
    @CreatedDate private Date createdDate;
    @LastModifiedBy private String modifier;
    @LastModifiedDate private Date ModifiedDate;
}

重点是需要在实体类上增加@EntityListeners(AuditingEntityListener.class)启用审计特性，同时增加了四个审计属性，并分别使用@CreatedBy、@CreatedDate、@LastModifiedBy、@LastModifiedDate进行标注。

在org.example.repository下创建CommentRepository，如下：

package org.example.repository;

import org.example.entity.Comment;
import org.springframework.data.jpa.repository.JpaRepository;

public interface CommentRepository extends JpaRepository<Comment, Long> {
}

在org.example.repository中创建CommentController，增加create方法：

package org.example.controller;

import org.example.entity.Comment;
import org.example.repository.CommentRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class CommentController {
    @Autowired CommentRepository commentRepository = null;

    @RequestMapping("create")
    public Comment create(String details) {
        Comment comment = new Comment();
        comment.setDetails(details);
        return commentRepository.save(comment);
    }
}

到这里，我们的业务代码就编写完了，整个工程大概是这样：

增加权限并开启审计

首先，调整Spring Security，增加对所有请求的拦截并强制要求认证。同时增加两个用户：admin和user。

    @Bean
    public UserDetailsService users() {
        User.UserBuilder users = User.withDefaultPasswordEncoder();
        UserDetails user = users
                .username("user")
                .password("password")
                .roles("USER")
                .build();
        UserDetails admin = users
                .username("admin")
                .password("password")
                .roles("USER", "ADMIN")
                .build();
        return new InMemoryUserDetailsManager(user, admin);
    }

    @Configuration(proxyBeanMethods = false)
    class DefaultConfigurerAdapter extends WebSecurityConfigurerAdapter {
        @Override
        protected void configure(HttpSecurity http) throws Exception {
            http.authorizeRequests(authorize -> authorize
                    .mvcMatchers("/**").access("hasRole('USER')")
                    .anyRequest().denyAll()
            );
            http.formLogin(withDefaults());
        }
    }

然后，在配置类上，增加注解@EnableJpaAuditing，开启审计功能：

@SpringBootApplication
@EnableJpaAuditing
public class Application

最后，需要增加逻辑，告诉框架需要从SecurityContext中获取审计用的用户身份信息：

@Component
    class SpringSecurityAuditorAware implements AuditorAware<String> {
        public Optional<String> getCurrentAuditor() {
            SecurityContext context = SecurityContextHolder.getContext();
            if (context == null) return Optional.of("");

            Authentication authentication = context.getAuthentication();
            if (authentication == null) return Optional.of("");

            if (!authentication.isAuthenticated()) return Optional.of("");

            return Optional.of(authentication.getName());
        }
    }

完整的Application代码如下：

package org.example;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.domain.AuditorAware;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.config.annotation.web.configuration.WebSecurityConfigurerAdapter;
import org.springframework.security.core.Authentication;
import org.springframework.security.core.context.SecurityContext;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.security.core.userdetails.User;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.provisioning.InMemoryUserDetailsManager;
import org.springframework.stereotype.Component;

import java.util.Optional;

import static org.springframework.security.config.Customizer.withDefaults;

@SpringBootApplication
@EnableJpaAuditing
public class Application {
    @Bean
    public UserDetailsService users() {
        User.UserBuilder users = User.withDefaultPasswordEncoder();
        UserDetails user = users
                .username("user")
                .password("password")
                .roles("USER")
                .build();
        UserDetails admin = users
                .username("admin")
                .password("password")
                .roles("USER", "ADMIN")
                .build();
        return new InMemoryUserDetailsManager(user, admin);
    }

    @Configuration(proxyBeanMethods = false)
    class DefaultConfigurerAdapter extends WebSecurityConfigurerAdapter {
        @Override
        protected void configure(HttpSecurity http) throws Exception {
            http.authorizeRequests(authorize -> authorize
                    .mvcMatchers("/**").access("hasRole('USER')")
                    .anyRequest().denyAll()
            );
            http.formLogin(withDefaults());
        }
    }

    @Component
    class SpringSecurityAuditorAware implements AuditorAware<String> {
        public Optional<String> getCurrentAuditor() {
            SecurityContext context = SecurityContextHolder.getContext();
            if (context == null) return Optional.of("");

            Authentication authentication = context.getAuthentication();
            if (authentication == null) return Optional.of("");

            if (!authentication.isAuthenticated()) return Optional.of("");

            return Optional.of(authentication.getName());
        }
    }

    public static void main(String[] args) {
        SpringApplication.run(Application.class);
    }
}

测试

启动应用，打开浏览器输入http://localhost:8080/create?details=CommentA，页面会跳转至登录页面：

输入user/password后，页面返回如下：

输入http://localhost:8080/logout，登出user用户。

浏览器输入http://localhost:8080/create?details=CommentB，在登录页面输入admin/password，页面返回如下：

查看数据库，其中保存了两条记录：

好了，上面便是关于数据库操作审计的所有内容。