ShardingSphere4.1.1：Sharding-JDBC数据加密及SPI加密策略实现

0、脱敏规则

1、环境构建

1.1、创建一个 Spring Boot 项目，引入依赖如下：

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">

    <parent>
        <groupId>cn.zwqh</groupId>
        <artifactId>sharding-sphere-4.1.1</artifactId>
        <version>1.0-SNAPSHOT</version>
    </parent>

    <modelVersion>4.0.0</modelVersion>
    <groupId>cn.zwqh</groupId>
    <artifactId>sharding-sphere-demo-8</artifactId>
    <version>${parent.version}</version>

    <packaging>jar</packaging>
    <name>sharding-sphere-demo-8</name>
    <description>Demo project for Spring Boot</description>

    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <mainClass>cn.zwqh.shardingspheredemo8.ShardingSphereDemo8Application</mainClass>
                </configuration>
                <executions>
                    <execution>
                        <id>repackage</id>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project> 

1.2、测试数据库 ds1

CREATE TABLE `t_user` (
  `user_id` int NOT NULL COMMENT '用户id',
  `user_name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '用户名称',
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '密码明文',
  `password_encrypt` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '密码密文',
  `password_assisted` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '辅助查询列',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; 

1.3、实体类

@Data
public class UserEntity {

    private Integer userId;

    private String userName;

    private String password;

    private String passwordEncrypt;

    private String passwordAssisted;
} 

1.4、Mapper

@Mapper
public interface UserMapper {

    @Insert("insert into t_user(user_id,user_name,password) values(#{userId},#{userName},#{password})")
    void insertUser(UserEntity userEntity);

    @Select("select * from t_user where user_name=#{userName} and password=#{password}")
    @Results({
            @Result(column = "user_id", property = "userId"),
            @Result(column = "user_name", property = "userName"),
            @Result(column = "password", property = "password"),
            @Result(column = "password_assisted", property = "passwordAssisted")
    })
    List<UserEntity> getUserInfo(@Param("userName") String userName, @Param("password") String password);
} 

1.5、测试类

@Slf4j
@SpringBootTest
class ShardingSphereDemo8ApplicationTests {

    @Resource
    private UserMapper userMapper;

    @Test
    void insertUser() {
        UserEntity userEntity = new UserEntity();
        userEntity.setUserId(19);
        userEntity.setUserName("user19");
        userEntity.setPassword("123456");
        userMapper.insertUser(userEntity);
    }

    @Test
    void getUserInfo() {
        List<UserEntity> userEntityList = userMapper.getUserInfo("user19", "123456");
        log.info(userEntityList.toString());
    }

} 

2、默认加密算法

2.1、AES

配置文件

# 应用名称
spring.application.name=sharding-sphere-demo-8
spring.shardingsphere.datasource.name=ds
spring.shardingsphere.datasource.ds.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds.url=jdbc:mysql://127.0.0.1:3306/ds1?serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.ds.username=root
spring.shardingsphere.datasource.ds.password=123456
spring.shardingsphere.datasource.ds.max-total=100
# 采用AES对称加密策略
spring.shardingsphere.encrypt.encryptors.encryptor_aes.type=aes
spring.shardingsphere.encrypt.encryptors.encryptor_aes.props.aes.key.value=123456
# password为逻辑列，password为数据表明文列，password_encrypt为数据表密文列
spring.shardingsphere.encrypt.tables.t_user.columns.password.plainColumn=password
spring.shardingsphere.encrypt.tables.t_user.columns.password.cipherColumn=password_encrypt
spring.shardingsphere.encrypt.tables.t_user.columns.password.encryptor=encryptor_aes
spring.shardingsphere.props.sql.show=true
# 查询是否使用密文列
spring.shardingsphere.props.query.with.cipher.column=true 

测试结果

1、设置了明文列和密文列，运行成功，新增时逻辑列会改写成明文列和密文列

2、仅设置明文列，运行直接报错，所以必须设置加密列

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'encryptDataSource' defined in class path resource [org/apache/shardingsphere/shardingjdbc/spring/boot/SpringBootConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'encryptDataSource' threw exception; nested exception is java.lang.IllegalArgumentException: Invalid encrypt column configurations in EncryptTableRuleConfigurations. 

3、仅设置密文列，运行成功，明文会进行加密，数据库实际插入到密文列

4、设置了明文列和密文列， spring.shardingsphere.props.query.with.cipher.column 为false时，查询通过明文列执行，返回数据为明文列

5、设置了明文列和密文列， spring.shardingsphere.props.query.with.cipher.column 为true时，查询通过密文列查询，返回数据为明文

6、仅设置密文列， spring.shardingsphere.props.query.with.cipher.column 为false时，查询默认通过密文列查询，但入参未进行自动加密，所以不能查到数据

7、仅设置密文列， spring.shardingsphere.props.query.with.cipher.column 为true时，查询通过密文列查询，返回数据为明文

2.2、MD5

配置文件

# 应用名称
spring.application.name=sharding-sphere-demo-8
spring.shardingsphere.datasource.name=ds
spring.shardingsphere.datasource.ds.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds.url=jdbc:mysql://127.0.0.1:3306/ds1?serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.ds.username=root
spring.shardingsphere.datasource.ds.password=123456
spring.shardingsphere.datasource.ds.max-total=100
# 采用MD5加密策略
spring.shardingsphere.encrypt.encryptors.encryptor_md5.type=MD5
# password为逻辑列，password为数据表明文列，password_encrypt为数据表密文列
spring.shardingsphere.encrypt.tables.t_user.columns.password.plainColumn=password
spring.shardingsphere.encrypt.tables.t_user.columns.password.cipherColumn=password_encrypt
spring.shardingsphere.encrypt.tables.t_user.columns.password.encryptor=encryptor_md5
spring.shardingsphere.props.sql.show=true
# 查询是否使用密文列
spring.shardingsphere.props.query.with.cipher.column=true 

测试结果

1、新增时，可以看到加密后的数据和AES的有所区别

2、查询时，spring.shardingsphere.props.query.with.cipher.column为true时，通过密文列查询，由于MD5加密是非对称的，所以返回的是密文数据

3、查询时，spring.shardingsphere.props.query.with.cipher.column为false时，通过明文列查询，返回明文数据

3、SPI自定义加密算法

3.1、SPI 配置

在resources/META-INF/services目录下新增配置

配置文件名字为:org.apache.shardingsphere.encrypt.strategy.spi.Encryptor

配置文件里的内容，放入自定义的加密策略的类的全路径，和要使用官方内置的加密策略的类的全路径

org.apache.shardingsphere.encrypt.strategy.impl.AESEncryptor
org.apache.shardingsphere.encrypt.strategy.impl.MD5Encryptor
cn.zwqh.shardingspheredemo8.encryptor.Sha256Encryptor
cn.zwqh.shardingspheredemo8.encryptor.Sha256RandomEncryptor 

3.2、实现Encryptor接口

创建 Sha256Encryptor 类

@Getter
@Setter
public class Sha256Encryptor implements Encryptor {

    private Properties properties = new Properties();

    @Override
    public void init() {

    }

    @Override
    public String encrypt(Object plaintext) {
        if (null == plaintext) {
            return null;
        }
        return DigestUtils.sha256Hex(String.valueOf(plaintext));
    }

    @Override
    public Object decrypt(String ciphertext) {
        return ciphertext;
    }

    @Override
    public String getType() {
        return "SHA256";
    }
} 

修改配置文件

# 应用名称
spring.application.name=sharding-sphere-demo-8
spring.shardingsphere.datasource.name=ds
spring.shardingsphere.datasource.ds.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds.url=jdbc:mysql://127.0.0.1:3306/ds1?serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.ds.username=root
spring.shardingsphere.datasource.ds.password=123456
spring.shardingsphere.datasource.ds.max-total=100
# 采用自定义策略 SHA256
spring.shardingsphere.encrypt.encryptors.encryptor_sha256.type=SHA256
# password为逻辑列，password为数据表明文列，password_encrypt为数据表密文列
spring.shardingsphere.encrypt.tables.t_user.columns.password.plainColumn=password
spring.shardingsphere.encrypt.tables.t_user.columns.password.cipherColumn=password_encrypt
spring.shardingsphere.encrypt.tables.t_user.columns.password.encryptor=encryptor_sha256
spring.shardingsphere.props.sql.show=true
# 查询是否使用密文列
spring.shardingsphere.props.query.with.cipher.column=true 

测试结果

1、新增时，可以看到加密后的数据和AES的有所区别，说明自定义加密策略使用成功

2、查询时，spring.shardingsphere.props.query.with.cipher.column为true时，通过密文列查询，由于SHA256类似MD5加密，所以返回的是密文数据

3、查询时，spring.shardingsphere.props.query.with.cipher.column为false时，通过明文列查询，返回明文数据

3.3、实现QueryAssistedEncryptor接口

创建 Sha256RandomEncryptor 类

@Getter
@Setter
public class Sha256RandomEncryptor implements QueryAssistedEncryptor {

    private Properties properties = new Properties();

    @Override
    public String queryAssistedEncrypt(String plaintext) {
        if (null == plaintext) {
            return null;
        }
        // 原始字符串
        return DigestUtils.sha256Hex(String.valueOf(plaintext));
    }

    @Override
    public void init() {

    }

    @Override
    public String encrypt(Object plaintext) {
        if (null == plaintext) {
            return null;
        }
        // 原始字符串+变动因子（如时间戳）
        plaintext = plaintext.toString() + LocalDateTime.now().toString();
        return DigestUtils.sha256Hex(String.valueOf(plaintext));
    }

    @Override
    public Object decrypt(String ciphertext) {
        return ciphertext;
    }

    @Override
    public String getType() {
        return "SHA256_RANDOM";
    }
} 

修改配置文件

# 应用名称
spring.application.name=sharding-sphere-demo-8
spring.shardingsphere.datasource.name=ds
spring.shardingsphere.datasource.ds.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds.url=jdbc:mysql://127.0.0.1:3306/ds1?serverTimezone=UTC&useSSL=false
spring.shardingsphere.datasource.ds.username=root
spring.shardingsphere.datasource.ds.password=123456
spring.shardingsphere.datasource.ds.max-total=100
# 采用自定义策略 SHA256_RANDOM
spring.shardingsphere.encrypt.encryptors.encryptor_sha256_random.type=SHA256_RANDOM
# password为逻辑列，password为数据表明文列，password_encrypt为数据表密文列，password_assisted辅助查询列
spring.shardingsphere.encrypt.tables.t_user.columns.password.plainColumn=password
spring.shardingsphere.encrypt.tables.t_user.columns.password.cipherColumn=password_encrypt
spring.shardingsphere.encrypt.tables.t_user.columns.password.assistedQueryColumn=password_assisted
spring.shardingsphere.encrypt.tables.t_user.columns.password.encryptor=encryptor_sha256_random
spring.shardingsphere.props.sql.show=true
# 查询是否使用密文列
spring.shardingsphere.props.query.with.cipher.column=true 

测试结果

1、新增两条用户数据，密码一致，可以看到密文列的数据是不一致的，辅助列的数据一致，这样可以更好的保护数据安全

2、查询时，spring.shardingsphere.props.query.with.cipher.column为true时，通过辅助查询列查询，而返回的数据为密文列

3、查询时，spring.shardingsphere.props.query.with.cipher.column为false时，通过明文列查询，而返回的数据为明文

小结

1、数据加密默认算法支持 AES 和 MD5 两种，AES 为对称加密，MD5 为非对称加密；

2、可以通过 SPI 自定义实现加密策略；

3、根据业务安全需求选择合适的加密算法以及配置。