编码技巧——数据加密（一）ShardingSphere

迈不过友情╰ 2023-09-29 11:22 23阅读 0赞

## 1. 背景 ##

接到公司合规部门的指示，应工信部整改文件，限制各大互联网公司对用户隐私数据的收集；对服务端的影响就是，以前存储的用户隐私信息如用户安卓设备ID、用户姓名、手机号、联系方式、邮箱等信息，需要加密存储和传输；

> **因此——我们需要对线上和历史的DB中的涉及用户敏感信息的数据进行存储加密，读取时进行解密；**

个人是执行此次任务的服务端Leader，本篇介绍几种方案选型及各自优劣；

## 2. 挑战 ##

所负责的数据所在项目是部门的核心项目，存储大量的用户数据，由于历史原因（早期的负责人仅满足快速上线需求，未考虑分表方案和敏感数据脱敏、未考虑代码的可维护性、未考虑未来的业务扩展性），存在很多困难：

> 1. 整体工作量大：20张逻辑表（120+物理表），8个应用工程；  
> 2. 对同一类数据的读写分散各个应用，未做成微服务化，历史包袱非常重；  
> 3. 未使用统一的ORM框架，存在hibernate+jdbc+mybatis；  
> 4. 存在多张大表（上亿级别），数据量级很大  
> 5. 存在极其复杂的SQL写法，甚至将业务逻辑写在了SQL语句中；  
> 6. 有不规范的SQL语句，如字段名未加分号、和mysql的保留字/关键字冲突；  
> 7. 测试范围非常大 测试配合成本高

接到任务的时候思考了几种方案，但是真正的了解到工作量以及现状（严峻的历史包袱）时，觉得并不是一件简单的事；思考方案很简单，但是由于本次任务所涉及的数据和业务是部门的核心业务数据，加上要批量操作数据库，哪怕有点小事故，可能影响范围是很大的，自己可能就得跑路了...冷静一下先来理一下可能的方案；

## 3. 整体方案选型 ##

在方案选择之前，先明确下我们需要做的事和顺序：

> **（1）新数据的入库加密；**
> 
> **（2）历史老数据的清洗；**
> 
> **（3）线上业务查询逻辑的变更，从查询原明文到查询密文解密；**
> 
> **（4）历史明文数据的清理；**
> 
> **（5）此外，需要考虑异常时的功能回退；**

结合以上需要做的事情，来思考下我们对技术方案的预期，或者说需要达到的目的，以此来判断方案的可行性：

> **（1）功能上：对新增数据实现数据脱敏，同时对线上的历史数据进行数据脱敏，执行任务时不会影响线上数据的读写，最重要的是，保证出现问题可回滚；**
> 
> **（2）方案上：对系统的侵入性降到最小，理论上改的代码越少，出问题的几率越小；因此，方案最好尽量不动业务SQL和业务逻辑代码；**
> 
> **（3）影响上：平滑过渡，不需要停机迁移数据，不能对用户有影响；按照业务或表维度再或者机器数量，逐步迁移功能，不能梭哈ALL IN（一次全部生效），要控制风险；**

结合上面的对技术方案的预期，看看一开始设想过的几个方案：

### 方案1）停机洗数升版 ###

描述：流量低峰期挂停机公告，禁止新数据写入，先启动洗数程序处理历史数据，然后通过改造代码支持数据库新插入数据加密和读数据解密，发版并验证；

优点：简单（粗暴）；

缺点：需要停机部署；处理过程不可控；

简单是简单，但是看到标题时第一反应就是不可行，因为需要停机；我们是互联网业务，面向大量的C端用户，日活千万量级，绝对不能采用这个方案；

###  ###

### 方案2）部署明文密文两套环境 ###

描述：保持现有prd明文环境正常运行，新增一套密文环境（服务和数据库），密文环境仅支持读写密文；先监听明文库的binlog，复制线上全量数据的同时，在监听器内实现将明文加密成密文入库，这样密文库就只保留密文数据；然后根据IP开始灰度，将部分用户请求路由到新的密文环境；逐渐放量，直到服务全量切换，然后下掉旧的服务；

优点：灰度发布，风险可控；

缺点：需要两套环境，成本较大；

整体思路是对的，就是需要一套新的环境和库表，成本略高，可以作为备选方案；

###  ###

### 方案3）执行SQL前后修改列的值 ###

思路：对于插入语句，执行SQL前对敏感数据字段进行加密，然后做parameter的值替换；对于查询语句，在获得Resut时，判断当前敏感数据字段所在列的值是明文还是密文，是明文则直接返回，是密文就解密后返回；

mybatis天然的支持上述的操作，包括：实现Mybatis 拦截器Interceptor和实现mybatis类型处理器BaseTypeHandler；与mybatis Interceptor不同，**TypeHandler**不是在执行SQL的那一层拦截，而是在实现DO实体和DB字段类型转换时做数据的setter，看起来更加合适；

首先实现**BaseTypeHandler**接口，在mybatis的的配置文件中加入该类型处理器；（类型处理器可以全局生效，也可以在DAO的resultMap中指定）；然后实现**BaseTypeHandler**接口的方法（写入数据时实现setParameter方法，读取数据时实现getResult方法），拦截到的实体的属性打了加密注解，**通过当前字段的值判断是否为密文/明文**，对其分别处理；

优点：1.对原数据库表没有任何影响，无需修改/新增表字段，无需改动索引；2.对代码的侵入非常小，轻量级：不需要任何对数据库表的操作，不需要任何业务代码的修改，只需要在mybatis的mapper对应的xml中，对ResultMap中的敏感数据列显示的加上handlerType标签指定类型处理器即可；3.相比较拦截SQL语句，该方法更加关注数据本身，而不用去解析SQL是写入还是读出；

存在的问题：当前方法不保留明文，异常时数据无法恢复；需要根据当前列的值来判断是明文还是密文，而判断明文/密文的方法难以实现；如果能给出一种能区分密文和明文的方法，该方案将是较为合适的方案，例如，通过增加是否密文的标识列（需要在DO和Mapper中新加1个标识属性）

思考：其实，如果业务是新项目，或数据量小，而且非核心业务，这种方法是最快的！甚至可以通过字符长度、特殊尾缀来标记密文；但是回归我们的要求"**保证出现问题可回滚**"，因此含泪放弃这个方法~~

###  ###

### 方案4）原数据表新增是否密文的标识字段 ###

描述：在原数据表加一列，标识当前行数据是否存储密文如0-否/1-是，默认值为0-否（存储明文）；修改代码，对于写操作，将明文加密且将密文标识置为"1-是"，然后执行插入/更新操作；对于读操作，查询出来后根据标识位，为"0-否"则直接返回，为"1-是"则执行解密，然后返回数据；可根据表的维度，逐渐灰度放量观察，直到全部表配置生效；然后启动定时任务进行数据清洗，保证先读后写在一个事务内即可；

思考：这个方案看起来非常便捷，缺点是表多且涉及加密的字段多、SQL语句多的情况下，还是有一定工作量的，当然可以使用mybatis的TypeHandler之类的做统一处理；

但是总感觉还是有一些细节没考虑到：

**怎么发布服务呢？**

直接发布，这肯定是不可以的，因为上线过程中，上百台机器是滚动部署，线上一定存在新老代码共存的情况，如：新的代码兼容了读写密文，但是对于线上老的代码来说，它的明文被改掉了，这是不允许的！因此我们需要先发布读密文相关的代码，直至全量，然后再发布写密文的代码，这样即使线上有明文密文都存在的情况，对数据的读操作也不会有误；

**加标识不存明文真的可以吗？**

理论上是可以的，但是回忆下我们对技术方案的预期，有一条是"**保证出现问题可回滚**"，这一点我们真的做到了吗？——没有，因为明文数据可能永久的被替换掉了，一旦我们的加解密出现不一致情况，数据就永久的丢失了；所以此方案有个致命的缺陷就是没有回头路，不保留明文，风险高！

那么——借着这个思路，我们加密的同事为何不保留一份明文呢？

###  ###

### 方案5）整体方案确定——原数据表新增密文字段（基于方案4 改良） ###

描述：新增密文列，同时存储一份明文和密文，写明文的同时也写密文（双写）；然后执行定时任务读出原数据，将明文加密，写到密文列，保证事务；期间可校验明文列与密文列是否一一对应；然后根据表配置，对部分表进行读密文，验证稳定性，异常时由于明文数据还在，立即切换为读明文即可；平稳全量后，观察一段时间无误，再通过DDL清理历史明文数据列，完成目标；

至此，我们的整体方案明确了，但还是存在一个问题，即我们需要操作的数据表、SQL非常多，如何高效的完成对数据的加解密，**尽量不要改动到SQL那一层，并且尽量少修改业务代码**呢？看看下面的实施方案；

##  ##

## 4. 具体方案 ##

讨论的方案：

**【1】实现mybatis插件/拦截器interceptor**

（1）数据表加一列密文列，如emmcid，密文emmcid\_cipher；密文列可空；

（2）修改Mapper.xml，查询语句和插入/更新SQL语句中加上密文列，使用<if>标签非空判断；

（3）定义注解，注解属性包括实体中的明文参数名、密文参数名；在Mapper接口中，对方法加上注解；（注意这里是参数名而非数据表的列名，我们只准备做参数填充而非修改拼接SQL语句）

（4）实现mybatis拦截器并注册该拦截器；分别拦截查询和写入语句：

写入时，对于插入/更新操作进行拦截，将敏感字段拿出来，对明文进行加密，填充到密文列对应的parameter上，再执行插入；查询时，对于查询结果赋值拦截，将查出的密文列进行解密，然后替换掉明文列对应的返回值；

以上方式专门写一篇来讲述，并且分析其优缺点，不再赘述；本篇主要讲一下**ShardingSphere**数据加密方案的实现**；**

**【2】使用ShardingSphere数据加密功能**

产品介绍：[概览 :: ShardingSphere][_ ShardingSphere]

脱敏功能用户手册：[数据脱敏 :: ShardingSphere][_ ShardingSphere 1]

原理简介：

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5LiD5rW35YGl5Lq6_size_20_color_FFFFFF_t_70_g_se_x_16][]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5LiD5rW35YGl5Lq6_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

**实施方案：**

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5LiD5rW35YGl5Lq6_size_20_color_FFFFFF_t_70_g_se_x_16 2][]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5LiD5rW35YGl5Lq6_size_20_color_FFFFFF_t_70_g_se_x_16 3][]

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5LiD5rW35YGl5Lq6_size_20_color_FFFFFF_t_70_g_se_x_16 4][]

**优点**：

（1）公司MySQL中间件底层基于sharding-jdbc，故可直接使用sharding-jdbc提供的读写分离、分库分表以及数据加密功能；

（2）除ShardingSphere限制的SQL语法外**，**基本上不需要再修改DAO层代码；

**存在的问题**：

（1）需要手动对表加密文cipher字段；每个明文字段都需要加一个对应的密文字段；

（2）若明文字段有索引，则需要对密文字段加索引，且DB平台索字段大小有限制767字节，对应varchar长度190；

（3）**ShardingSphere由较多限制，部分SQL不支持需要修改DAO层SQL语句，甚至必须修改业务代码；**

**（4）公司MySQL中间件存在缺陷，不支持动态修改数据源的配置，如加密的表、字段、读密文开关；修改配置需要重启服务才能生效；如果异常，回滚功能时机器较多的情况加，响应较慢；**

**目前已知的ShardingSphere 的限制和踩坑**：

（1）脱敏字段无法支持比较操作，如：大于小于、ORDER BY、BETWEEN、LIKE等；无法支持计算操作，如：AVG、SUM以及计算表达式；

（2）不支持高版本c3p0（0.9.5.x）连接池，查询报错Marking a ResultSet inactive that we did not know was opened；

（3）shardingsphere5.0.0之前的版本不支持ON DUPLICATE KEY UPDATE 新增或更新语法，会报No value specified for parameter错误https://github.com/apache/shardingsphere/issues/8375，需要修改业务代码；

（4）有join联合查询时，从表的加密列配置不生效，暂时处理方案是在主表上增加相关加密列配置（后续增加相关DB规范，尽量不要使用联表查询，如果要用放入到单独mapper文件，并使用join关键字）；

（5）关联查询时，如A表name是**加密**字段，B表也有一个name字段刚好是**不加密**字段，sharding会自动对B表的name也做解密，解密会失败或报错；

（6）单独使用数据加密，SQL基本都兼容；配合分库分表使用时，分库分表不支持的sql，数据加密也不支持；分库分表[不支持sql参考][sql]；

（7）sql as 驼峰名与resultmap下划线分割的名称，不匹配时，原有mybatis可能适配比较好，运行正常，使用ShardingSphere后，不能正常匹配。注意一下，按照正常规范使用即可。

（8）加密字段，设置大小写敏感的binary选项如下；如果忽略大小写，不同明文加密后的结果可能一致，查询结果会有误或者新建唯一索引报错；

`imei_cipher` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '' COMMENT 'imei脱敏字段',

**来自兄弟团队业务迁移案例的建议——**

“总结一下，我们平常在书写SQL时能不用子查询尽量就不用，如果实在要用可以对子查询中的查询条件起一些别名提供给外部条件使用来规避shardingsphere无法解析的问题；

另外一些复杂的sql包含order by ,group by的语句一定要经过实际验证，特别是在多数据源的场景下要验证强制路由是否会失效；

最后就是可以查阅shardingsphere的官方手册，里面清楚的标注了哪些SQL写法是禁止使用的，如果严格按照其给的标准去书写SQL，相信就可以自动避开许多坑了。”

## **5. 方案执行** ##

以下基于**ShardingSphere**

**【执行方案】**

先按照应用的维度执行；

（1）准备工作，配置加密数据源EncryptDataSource，敏感字段表、字段、明文及密文列，明文读开关；

（2）梳理出当前应用涉及脱敏的表列表；例如表A，

a. 先表A加上imei和emmcid的密文字段，以\_cipher作为尾缀（**如有索引则在数据清洗后再建立索引**）；  
b. 然后将当前表配置到数据源属性上，将"query.with.cipher.column"置位false，重启应用；  
c. 在DAO层，验证双写，包括插入和更新；验证查询，查询包括密文字段为空和非空的记录；  
d. 然后将"query.with.cipher.column"置位true，重启应用；  
e. 在DAO层，验证只读密文，包括明文列为空（手动置空）和非空的记录；

（3）如果涉及**ShardingSphere**不支持的SQL语法，需要修改SQL和业务代码；

因为涉及业务代码的修改，而执行数据脱敏的同事不熟悉相关业务，因此根据自己的理解直接动原来的业务代码或者SQL风险很大，是否功能上无感知不可控；

这里建议：负责脱敏的同事梳理和标记出需要修改的DAO层方法（SQL），由相关同事负责修改业务代码，负责脱敏的同事负责double check和验证SQL；

（4）在自测的同时，标记service所在的接口、任务，记录影响范围，便于测试；

（5）**涉及的表需要将脱敏方案、加密算法同步给大数据分析团队**；

接下来执行完剩余的表；

最终执行完所有涉及的应用；

**【示意图】**

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5LiD5rW35YGl5Lq6_size_20_color_FFFFFF_t_70_g_se_x_16 5][]

**【 上线方案】**

1. 选择某张表A，对该表的写入和查询最好在1个应用内；将这张表配置上去，重启应用，新增的数据会写入密文列，且先只读明文；

2. 一段时间后观察服务平稳，无异常日志，密文字段正常写入且通过加密算法可以解密；

3. 开启定时任务，对历史数据进行清洗，刷新密文字段；确保所有历史数据的密文字段全部刷新；

4. 在检测密文字段无空记录的前提下，将然后将"query.with.cipher.column"置位true，重启应用；

5. 一段时间后观察服务平稳，无异常日志，主要观察密文字段可以查出来（开发时可预留DUBBO接口方便服务治理内调用查询）；

通过以上步骤，表A基本上已完成；

接下来将表A在所有的应用配置中心配置上去，将所有应用发布/重启；再次观察一段时间后观察服务平稳，无异常日志，读写正常；

通过以上步骤，表A脱敏全部完成；

接下来根据业务范围，逐渐增加表的范围，注意：每选择一张表，这张表的写入的应用最好也要先发布，保证新产生的数据都带有密文数据；

##  ##

**【参考文档】**

ShardingSphere官网：[Sharding-JDBC :: ShardingSphere][Sharding-JDBC _ ShardingSphere]

[_ ShardingSphere]: https://shardingsphere.apache.org/document/legacy/4.x/document/cn/overview/
[_ ShardingSphere 1]: https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-jdbc/usage/encrypt/
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5LiD5rW35YGl5Lq6_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/db18cf6927dc4878983626e3e9e4b676.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5LiD5rW35YGl5Lq6,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5LiD5rW35YGl5Lq6_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/f79f84b17f2c465c9f9c183da2b401af.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5LiD5rW35YGl5Lq6,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5LiD5rW35YGl5Lq6_size_20_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/59538b7d35e34d248fddb45d7e14cc7a.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5LiD5rW35YGl5Lq6,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5LiD5rW35YGl5Lq6_size_20_color_FFFFFF_t_70_g_se_x_16 3]: https://img-blog.csdnimg.cn/1e80070a16f94f3685485bdc9e3e1032.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5LiD5rW35YGl5Lq6,size_20,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5LiD5rW35YGl5Lq6_size_20_color_FFFFFF_t_70_g_se_x_16 4]: https://img-blog.csdnimg.cn/b641e6212cf04078ad82a18c18d7bd44.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5LiD5rW35YGl5Lq6,size_20,color_FFFFFF,t_70,g_se,x_16
[sql]: https://shardingsphere.apache.org/document/legacy/4.x/document/cn/features/sharding/use-norms/sql/#%E4%B8%8D%E6%94%AF%E6%8C%81%E7%9A%84sql
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5LiD5rW35YGl5Lq6_size_20_color_FFFFFF_t_70_g_se_x_16 5]: https://img-blog.csdnimg.cn/d80e40b21b3b427ebbb5602382ede4c7.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5LiD5rW35YGl5Lq6,size_20,color_FFFFFF,t_70,g_se,x_16
[Sharding-JDBC _ ShardingSphere]: https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-jdbc/