侧边栏壁纸
博主头像
码森林博主等级

一起走进码森林,享受编程的乐趣,发现科技的魅力,创造智能的未来!

  • 累计撰写 145 篇文章
  • 累计创建 73 个标签
  • 累计收到 4 条评论

目 录CONTENT

文章目录

ShardingSphere 4.1.1:Sharding-JDBC 数据加密及 SPI 加密策略实现

码森林
2022-03-03 / 0 评论 / 0 点赞 / 788 阅读 / 14,191 字 / 正在检测是否收录...
温馨提示:
本文最后更新于 2022-03-03,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

通过本文可以了解 Sharding-JDBC 数据加密的策略。

脱敏规则

环境构建

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>

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;

3、实体类

@Data
public class UserEntity {

    private Integer userId;

    private String userName;

    private String password;

    private String passwordEncrypt;

    private String passwordAssisted;
}

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);
}

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());
    }

}

默认加密算法

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、设置了明文列和密文列,运行成功,新增时逻辑列会改写成明文列和密文列

![image-20210729101206767](/Users/charlie/Library/Application Support/typora-user-images/image-20210729101206767.png)

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、仅设置密文列,运行成功,明文会进行加密,数据库实际插入到密文列

![image-20210729101338140](/Users/charlie/Library/Application Support/typora-user-images/image-20210729101338140.png)

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

![image-20210729102002911](/Users/charlie/Library/Application Support/typora-user-images/image-20210729102002911.png)

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

![image-20210729101849728](/Users/charlie/Library/Application Support/typora-user-images/image-20210729101849728.png)

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

![image-20210729102213815](/Users/charlie/Library/Application Support/typora-user-images/image-20210729102213815.png)

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

![image-20210729102417315](/Users/charlie/Library/Application Support/typora-user-images/image-20210729102417315.png)

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的有所区别

![image-20210729104524141](/Users/charlie/Library/Application Support/typora-user-images/image-20210729104524141.png)

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

![image-20210729104834560](/Users/charlie/Library/Application Support/typora-user-images/image-20210729104834560.png)

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

![image-20210729104953689](/Users/charlie/Library/Application Support/typora-user-images/image-20210729104953689.png)

SPI自定义加密算法

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

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的有所区别,说明自定义加密策略使用成功

![image-20210729102813219](/Users/charlie/Library/Application Support/typora-user-images/image-20210729102813219.png)

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

![image-20210729104010808](/Users/charlie/Library/Application Support/typora-user-images/image-20210729104010808.png)

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

![image-20210729103903132](/Users/charlie/Library/Application Support/typora-user-images/image-20210729103903132.png)

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、新增两条用户数据,密码一致,可以看到密文列的数据是不一致的,辅助列的数据一致,这样可以更好的保护数据安全

![image-20210729105328985](/Users/charlie/Library/Application Support/typora-user-images/image-20210729105328985.png)

![image-20210729105448531](/Users/charlie/Library/Application Support/typora-user-images/image-20210729105448531.png)

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

![image-20210729105743488](/Users/charlie/Library/Application Support/typora-user-images/image-20210729105743488.png)

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

![image-20210729110003912](/Users/charlie/Library/Application Support/typora-user-images/image-20210729110003912.png)

源码

github

码云

小结

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

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

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

0

评论区