Apache ShardingSphere数据脱敏接入与使用

偏执的太偏执、 2024-05-08 07:33 101阅读 0赞

Spring boot+druid+mysql+ShardingSphere

#### 接入依赖： ####

<dependency>
    	<groupId>org.apache.shardingsphere</groupId>
    	<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    	<version>4.0.0-RC2</version>
    </dependency>
    
    <dependency>
    	<groupId>com.alibaba</groupId>
    	<artifactId>druid</artifactId>
    </dependency>
    <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
    </dependency>

#### 接入配置： ####

spring:
      shardingsphere:
        datasource:
          name: master
          master: 
            type: com.alibaba.druid.pool.DruidDataSource
            driverClassName: com.mysql.jdbc.Driver
            url: jdbc:mysql://
            username: root
            password: 
            poolPingConnectionsNotUsedFor: 60000
            removeAbandoned: true
            connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
            minIdle: 1
            validationQuery: SELECT 1 FROM DUAL
            initialSize: 5
            maxWait: 60000
            poolPreparedStatements: false
            filters: stat,wall
            testOnBorrow: false
            testWhileIdle: true
            minEvictableIdleTimeMillis: 300000
            timeBetweenEvictionRunsMillis: 60000
            testOnReturn: false
            maxActive: 50
            druid:
              user: admin
              password: admin
        encrypt:
          encryptors:
            encryptor_aes:
              type: aes
              props: 
                aes.key.value: 2019
          tables:
            t_user: 
              columns:
                phone:
                  cipherColumn: phone   
                  encryptor: encryptor_aes  
                email:
                  cipherColumn: email   
                  encryptor: encryptor_aes
            t_org_person: 
              columns:
                phone:
                  cipherColumn: phone   
                  encryptor: encryptor_aes  
                id_card:
                  cipherColumn: id_card   
                  encryptor: encryptor_aes
            t_organization:
              columns:
                email:
                  cipherColumn: email   
                  encryptor: encryptor_aes  
                phone:
                  cipherColumn: phone   
                  encryptor: encryptor_aes 
                bank_account: 
                  cipherColumn: bank_account   
                  encryptor: encryptor_aes       
        props:
          sql:
            show: true
          query:
            with:
              cipher:
                column: true

#### 接入与使用注意事项： ####

1.  逻辑列与密文列相同就表示直接将数据库表的对应字段插入加密查询解密
2.  采用aes算法加密字段数据后长度会根据传入的内容不同最终的结果长度也会不一致，所以注意字段长度设置。
3.  数据脱敏密文长度和明文长度的关系：\{明文长度/16\} 向上取整\*16\*2
4.  对接spring boot后不用再自定义数据源配置。如DruidConfiguration
5.  如果加密字段在插入时未设置值或设置为null，存进数据库的值仍然会被加密成null的字符串。此时在SQL查询或JPA查询都需注意不可再判断IS NOT NULL
6.  脱敏字段无法支持比较操作，如：大于小于、ORDER BY、BETWEEN、LIKE等
7.  脱敏字段无法支持计算操作，如：AVG、SUM以及计算表达式
8.  org.apache.shardingsphere.core.strategy.encrypt.impl.AESShardingEncryptor