数据库中间件sharding-jdbc实现数据脱敏

前言

安全控制一直是治理的重要环节，数据脱敏属于安全控制的范畴。对互联网公司、传统行业来说，数据安全一直是极为重视和敏感的话题。数据脱敏是指对某些敏感信息通过脱敏规则进行数据的变形，实现敏感隐私数据的可靠保护。涉及客户安全数据或者一些商业性敏感数据，如身份证号、手机号、卡号、客户号等个人信息按照相关部门规定，都需要进行数据脱敏。

Apache ShardingSphere根据业界对脱敏的需求及业务改造痛点，提供了一套完整、安全、透明化、低改造成本的数据脱敏整合解决方案。

数据脱敏模块属于ShardingSphere分布式治理这一核心功能下的子功能模块。它通过对用户输入的SQL进行解析，并依据用户提供的脱敏配置对SQL进行改写，从而实现对原文数据进行加密，并将原文数据(可选)及密文数据同时存储到底层数据库。在用户查询数据时，它又从数据库中取出密文数据，并对其解密，最终将解密后的原始数据返回给用户。Apache ShardingSphere分布式数据库中间件自动化&透明化了数据脱敏过程，让用户无需关注数据脱敏的实现细节，像使用普通数据那样使用脱敏数据。

springboot整合

为了实现案例，我们需要创建一个用户表

create table t_user(id int primary key auto_increment,username varchar(10),password varchar(30));

pom文件

<?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> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.1.3.RELEASE</version> <relativePath/> </parent> <groupId>com.sunyuqi</groupId> <artifactId>sharding-jdbc-study</artifactId> <version>0.0.1-SNAPSHOT</version> <name>sharding-jdbc-study</name> <description>Demo project for Spring Boot</description> <properties> <java.version>9</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.3</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>4.0.0-RC1</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.14</version> </dependency> <dependency> <groupId>javax.xml.bind</groupId> <artifactId>jaxb-api</artifactId> <version>2.3.1</version> </dependency> <dependency> <groupId>com.sun.xml.bind</groupId> <artifactId>jaxb-impl</artifactId> <version>2.3.1</version> </dependency> <dependency> <groupId>org.glassfish.jaxb</groupId> <artifactId>jaxb-runtime</artifactId> <version>2.3.1</version> </dependency> <dependency> <groupId>javax.activation</groupId> <artifactId>activation</artifactId> <version>1.1</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>

配置文件

spring: shardingsphere: datasource: names: ds0 ds0: type: com.alibaba.druid.pool.DruidDataSource driver-class: com.mysql.cj.jdbc.Driver url: jdbc:mysql://127.0.0.1:3306/testdb?useSSL=false&serverTimezone=UTC&characterEncoding=utf8 username: root password: qwe123 maxPoolSize: 50 minPoolSize: 1 encrypt: encryptors: encryptor_aes: type: aes #加密类型，可选aes,md5（若选择aes需要提供aes.key.value字段） props: aes.key.value: 123456abc qualifiedColumns: t_user.password #加密字段 props: sql.show: true query: with: cipher: column: true #是否使用密文列查询 sharding: tables: t_order: columns: password: cipherColumn: password encryptor: encryptor_aes #加解密器 mybatis: configuration: map-underscore-to-camel-case: true

User实体类

package com.sunyuqi.model; public class User { private Long id; private String username; private String password; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", password='" + password + '\'' + '}'; } }

UserDao

package com.sunyuqi.dao; import com.sunyuqi.model.User; import org.apache.ibatis.annotations.Insert; import org.apache.ibatis.annotations.Mapper; import org.apache.ibatis.annotations.Select; import java.util.List; @Mapper public interface UserDao { @Insert("insert into t_user(username,password) values(#{username},#{password})") void UserAdd(User user); @Select("select id,username,password from t_user where id=#{id}") User UserGet(Long id); @Select("select id,username,password from t_user") List<User> UserFindAll(); }

UserService

package com.sunyuqi.service; import com.sunyuqi.dao.UserDao; import com.sunyuqi.model.User; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; @Service public class UserService { @Autowired private UserDao userDao; public void UserAdd(User user) { this.userDao.UserAdd(user); } public User UserGet(Long id) { return this.userDao.UserGet(id); } public List<User> UserFindAll() { return this.userDao.UserFindAll(); } }

UserController

package com.sunyuqi.controller; import com.sunyuqi.model.User; import com.sunyuqi.service.UserService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; @RestController @RequestMapping("user") public class UserController { @Autowired private UserService userService; @RequestMapping("add") public String add(User user) { this.userService.UserAdd(user); return "添加成功"; } @RequestMapping("get") public User get(Long id) { return this.userService.UserGet(id); } @RequestMapping("all") public List<User> findall() { return this.userService.UserFindAll(); } }

引导类

package com.sunyuqi; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; @SpringBootApplication public class ShardingJdbcStudyApplication { public static void main(String[] args) { SpringApplication.run(ShardingJdbcStudyApplication.class, args); } }

运行引导类进行添加查询测试 添加数据 查询测试 似乎密码都是明文的？这是由于我们在查询的过程中sharding-jdbc已经自动为我们解密了。 查看数据库： 已经加密存储了。