数据加密 #

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

对于数据加密的需求，在现实的业务场景中一般分为两种情况：

新业务上线，安全部门规定需将涉及用户敏感信息，例如银行、手机号码等进行加密后存储到数据库，在使用的时候再进行解密处理。因为是全新系统，因而没有存量数据清洗问题，所以实现相对简单。
已上线业务，之前一直将明文存储在数据库中。相关部门突然需要对已上线业务进行加密整改。这种场景一般需要处理 3 个问题：

历史数据需要如何进行加密处理，即洗数。
如何能在不改动业务 SQL 和逻辑情况下，将新增数据进行加密处理，并存储到数据库；在使用时，再进行解密取出。
如何较为安全、无缝、透明化地实现业务系统在明文与密文数据间的迁移。

概述 #

SphereEx-DBPlusEngine 根据业界对加密的需求及业务改造痛点，提供了一套完整、安全、透明化、低改造成本的数据加密整合解决方案，目前 SphereEx-DBPlusEngine 内置了 MD5、AES、RC4、SM4 和 SM3 算法。

在真实业务场景中，相关业务开发团队则往往需要针对公司安全部门需求，自行实行并维护一套加解密系统。而当加密场景发生改变时，自行维护的加密系统往往又面临着重构或修改风险。此外，对于已经上线的业务，在不修改业务逻辑和 SQL 的情况下，透明化、安全低风险地实现无缝进行加密改造也相对复杂。对于一些安全级别更高的非幂等加密算法，提供不可逆的幂等列用于查询，即查询辅助列。

为了提升功能的易用性，SphereEx-DBPlusEngine 数据加密插件提供了云端密钥管理、洗数以及反洗数能力。

基本概念 #

逻辑列

用于计算加解密列的逻辑名称，是 SQL 中列的逻辑标识。逻辑列包含密文列（必须）、查询辅助列（可选）和明文列（可选）。

逻辑列类型（dataType）

用于定义逻辑列的类型，例如 INT NOT NULL，VARCHAR(200) DEFAULT NULL 等，具体可以参见官方文档中各种方言字段的定义。

密文列（cipherColumn）

加密后的数据列。

密文列类型（cipherDataType）

用于定义密文列的类型，同逻辑列类型。

查询辅助列（assistedQueryColumn）

用于查询的辅助列。对于一些安全级别更高的非幂等加密算法，提供不可逆的幂等列用于查询。

查询辅助列类型（assistedQueryDataType）

用于定义查询辅助列类型，可结合实际需要进行人工定义。

模糊查询列

对于需要模糊查询的场景，该字段用于实现模糊查询。

模糊查询列类型

用于定义模糊查询列类型，可结合实际需要进行人工定义。

明文列（plainColumn）

存储明文的列，用于在加密数据迁移过程中仍旧提供服务。在清洗数据结束后可以删除。

明文列类型（plainDataType）

用于定义明文列类型，同逻辑列类型。

加密算法

指对数据加密过程中，所使用的具体加密方式，如 AES 及 MD5 等。

加密洗数（encrypting）

对数据库中未加密数据进行批量加密。

解密洗数（decrypting）

对数据库中已加密数据进行批量解密。

密钥管理

密钥的存放及管理方式。

特色能力 #

云端密钥管理

由于加密 key 以明文形式配置在 props 文件中存在安全隐患，SphereEx-DBPlusEngine 可通过增加 AWS 云端密钥管理功能来管理加密的 key。例如利用 AWS 的 secretKey 功能保存密钥，从而提升整个加密的安全性和便捷性。程序在初始化加密算法时，会同 AWS 建立连接，从而获取存储在 AWS 中的相关密钥，然后将密钥存储在算法中。在整个数据加密的过程中不涉及与云端的网络交互。

云端密钥管理

加密洗数

目前 SphereEx-DBPlusEngine 已经提供了加密的解决方案，对于新表新业务而言，直接使用加密规则配置即可，但是对于已有数据表，就需要将这些表中的明文字段进行洗数，转化为加密内容，SphereEx-DBPlusEngine 提供了完整的洗数方案。加密洗数通过 DistSQL 来触发洗数任务，程序收到洗数任务的请求之后，会根据当前的洗数规则以及加密规则创建洗数任务。洗数任务主要由两部分构成，一部分是查询任务，一部分是更新任务，查询任务负责查询用户的表数据并获取需要加密的明文字段，然后推送到通道中；更新任务则从通道中获取数据，并加密更新。整个任务的创建以及执行过程都会与治理中心进行交互，因此用户可以通过相关 DistSQL 来查询任务进度以及清理任务。

加密洗数

反洗数

对于已经是密文存储的系统，此时如果需要还原到明文存储的状态，可通过 SphereEx-DBPlusEngine 反向洗数的能力，构建出对应的明文列。在操作上与洗数类似，反洗数前提是需要提前创建好对应的明文列。

重洗数

对于更换加密算法或更换密钥等情况，可通过 SphereEx-DBPlusEngine 来重新洗数。重洗数方案会调用反洗数和洗数流程，需要用户手动操作，并且过程中会有明文数据存储。

在以上洗数过程中，SphereEx-DBPlusEngine 支持断点续传的能力，为用户提供更好地洗数体验。

适用场景 #

新上线业务的的数据加密场景

对于想要快速上线新业务，同时又需要完成安全部门的加密规定的场景，通过 SphereEx-DBPlusEngine 加密插件，用户可以快速完成数据的合规化加密，无需自行开发复杂的加密系统。同时 SphereEx-DBPlusEngine 加密锁具备的灵活性，也能够帮助客户避免加密场景变更带来的复杂重构和修改风险。

已上线业务的数据加密改造场景

对于已经上线的成熟业务，用户不仅需要考虑历史数据的清洗，还需要考虑新旧功能的切换。通过 SphereEx-DBPlusEngine 加密插件，用户可以方便地完成系统的加密改造，它还能够帮助用户安全快速地切换新旧功能，对存量数据进行加密清洗。用户无需改动任何业务逻辑和 SQL 就能够透明化地使用加解密功能。

使用前提 #

SphereEx-DBPlusEngine 尽可能让业务无感完成加密改造，现阶段有部分功能存在受限情况，因此在加密设计阶段，需调研业务系统是否使用了如大于、小于等操作，SphereEx-DBPlusEngine 具体使用限制见下文。

使用限制 #

需自行处理数据库中原始的存量数据；
模糊查询支持 %、_、contact 函数，暂不支持 escape；
加密字段无法支持查询不区分大小写功能；
加密字段无法支持比较操作，如：大于、小于、ORDER BY、BETWEEN 等；
加密字段无法支持计算操作，如：AVG、SUM 以及计算表达式；

注意事项 #

删除明文列是高危且不可逆操作，因此当完成数据加密改造后，建议先稳定运行数月后再执行该操作。

优势 #

自动化 & 透明化数据加密过程，用户无需关注加密中间实现细节。
内置多种加密算法，用户仅需简单配置即可使用。
提供加密算法 API 接口，用户可实现接口，从而使用自定义加密算法进行数据加密。
针对已上线业务，可实现明文数据与密文数据同步存储，并通过配置决定使用明文列还是密文列进行查询。可实现在不改变业务查询 SQL 前提下，已上线系统对加密前后数据进行安全、透明化迁移。

原理介绍 #

SphereEx-DBPlusEngine 通过对用户输入的 SQL 进行解析，并依据用户提供的加密规则对 SQL 进行改写，从而实现对原文数据进行加密，并将原文数据（可选）及密文数据同时存储到底层数据库。在用户查询数据时，它仅从数据库中取出密文数据，并对其解密，最终将解密后的原始数据返回给用户。

SphereEx-DBPlusEngine 自动化 & 透明化了数据加密过程，让用户无需关注数据加密的实现细节，像使用普通数据那样使用加密数据。此外，无论是已在线业务进行加密改造，还是新上线业务使用加密功能，SphereEx-DBPlusEngine 都可以提供一套相对完善的解决方案。

原理图

加密模块将用户发起的 SQL 进行拦截，并通过 SQL 语法解析器进行解析、理解 SQL 行为，再依据用户传入的加密规则，找出需要加密的字段和所使用的加解密算法对目标字段进行加解密处理后，再与底层数据库进行交互。SphereEx-DBPlusEngine 会将用户请求的明文进行加密后存储到底层数据库；并在用户查询时，将密文从数据库中取出进行解密后返回给终端用户。通过屏蔽对数据的加密处理，使用户无需感知解析 SQL、数据加密、数据解密的处理过程，就像在使用普通数据一样使用加密数据。

举例说明，假如数据库里有一张表叫做 t_user，这张表里实际有两个字段 pwd_plain，用于存放明文数据、pwd_cipher，用于存放密文数据、pwd_assisted_query，用于存放辅助查询数据，同时定义 logicColumn 为 pwd。那么，用户在编写 SQL 时应该面向 logicColumn 进行编写，即 INSERT INTO t_user SET pwd = '123'。SphereEx-DBPlusEngine 接收到该 SQL，通过用户提供的加密配置，发现 pwd 是 logicColumn，于是便对逻辑列及其对应的明文数据进行加密处理。 SphereEx-DBPlusEngine 将面向用户的逻辑列与面向底层数据库的明文列和密文列进行了列名以及数据的加密映射转换。如下图所示：

模型图

即依据用户提供的加密规则，将用户 SQL 与底层数据表结构割裂开来，使得用户的 SQL 编写不再依赖于真实的数据库表结构。而用户与底层数据库之间的衔接、映射、转换交由 SphereEx-DBPlusEngine 进行处理。

下方图片展示了使用加密模块进行增删改查时，其中的处理流程和转换逻辑，如下图所示。

加密流程图

加密算法解析 #

SphereEx-DBPlusEngine 提供了两种加解密的接口，即 EncryptAlgorithm 和 QueryAssistedEncryptAlgorithm。一方面，SphereEx-DBPlusEngine 为用户提供了内置的加解密实现类，用户只需进行配置即可使用；另一方面，为了满足用户不同场景的需求，我们还开放了相关加解密接口，用户可依据这两种类型的接口提供具体实现类。再进行简单配置，即可让 SphereEx-DBPlusEngine 调用用户自定义的加解密方案进行数据加密。

EncryptAlgorithm 该解决方案通过提供 encrypt(), decrypt() 两种方法对需要加密的数据进行加解密。在用户进行 INSERT, DELETE, UPDATE时，SphereEx-DBPlusEngine 会按照用户配置，对SQL进行解析、改写、路由，并调用 encrypt() 将数据加密后存储到数据库, 而在 SELECT 时，则调用 decrypt() 方法将从数据库中取出的加密数据进行逆向解密，最终将原始数据返回给用户。当前，SphereEx-DBPlusEngine 针对这种类型的加密解决方案提供了五种具体实现类，分别是 MD5(不可逆)，AES(可逆)，RC4(可逆)，SM3（不可逆）和 SM4（可逆），用户只需配置即可使用这五种内置的方案。
QueryAssistedEncryptAlgorithm

相比较于第一种加密方案，该方案更为安全和复杂。它的理念是：即使是相同的数据，如两个用户的密码相同，它们在数据库里存储的加密数据也应当是不一样的。这种理念更有利于保护用户信息，防止撞库成功。

它提供三种函数进行实现，分别是 encrypt(), decrypt(), queryAssistedEncrypt()。在 encrypt() 阶段，用户通过设置某个变动种子，例如时间戳。针对原始数据 + 变动种子组合的内容进行加密，就能保证即使原始数据相同，也因为有变动种子的存在，致使加密后的加密数据是不一样的。在 decrypt() 可依据之前规定的加密算法，利用种子数据进行解密。虽然这种方式确实可以增加数据的保密性，但是另一个问题却随之出现：相同的数据在数据库里存储的内容是不一样的，那么当用户按照这个加密列进行等值查询 (SELECT FROM table WHERE encryptedColumnn = ?) 时会发现无法将所有相同的原始数据查询出来。

为此，我们提出了辅助查询列的概念。该辅助查询列通过 queryAssistedEncrypt() 生成，与 decrypt() 不同的是，该方法通过对原始数据进行另一种方式的加密，但是针对原始数据相同的数据，这种加密方式产生的加密数据是一致的。将 queryAssistedEncrypt() 后的数据存储到数据中用于辅助查询真实数据。因此，数据库表中多出这一个辅助查询列。

由于 queryAssistedEncrypt() 和 encrypt() 产生不同加密数据进行存储，而 decrypt() 可逆，queryAssistedEncrypt() 不可逆。在查询原始数据的时候，我们会自动对 SQL 进行解析、改写、路由，利用辅助查询列进行WHERE条件的查询，却利用 decrypt() 对 encrypt() 加密后的数据进行解密，并将原始数据返回给用户。这一切都是对用户透明化的。当前，SphereEx-DBPlusEngine 针对这种类型的加密解决方案并没有提供具体实现类，却将该理念抽象成接口，提供给用户自行实现。SphereEx-DBPlusEngine 将调用用户提供的该方案的具体实现类进行数据加密。

加密配置 #

加密配置元素分为四部分：数据源配置，加密算法配置，加密表配置以及查询属性配置，其详情如下图所示：

规则配置图

数据源配置：指数据源配置。

加密算法配置：指使用什么加密算法进行加解密。目前 SphereEx-DBPlusEngine 内置了五种加解密算法：AES，MD5，RC4，SM3 和 SM4。

加密表配置：用于告诉 SphereEx-DBPlusEngine 数据表里哪个列用于存储密文数据（cipherColumn）、使用什么算法加解密（encryptorName）、哪个列用于存储辅助查询数据（assistedQueryColumn）、使用什么算法加解密（assistedQueryEncryptorName）、哪个列用于存储明文数据（plainColumn）以及用户想使用哪个列进行 SQL 编写（logicColumn）。

我们可以从加密模块存在的意义来理解。加密模块最终目的是希望屏蔽底层对数据的加密处理，也就是说我们不希望用户知道数据是如何被加解密的、如何将明文数据存储到 plainColumn，将密文数据存储到 cipherColumn。换句话说，我们不希望用户知道 plainColumn 和 cipherColumn 的存在和使用。所以，我们需要给用户提供一个概念意义上的列，这个列可以脱离底层数据库的真实列，它可以是数据库表里的一个真实列，也可以不是，从而使得用户可以随意改变底层数据库的 plainColumn 和 cipherColumn 的列名。或者删除 plainColumn，选择永远不再存储明文，只存储密文。只要用户的 SQL 面向这个逻辑列进行编写，并在加密规则里给出 logicColumn 和 plainColumn、cipherColumn 之间正确的映射关系即可。

查询属性的配置：当底层数据库表里同时存储了明文数据、密文数据后，该属性开关用于决定是直接查询数据库表里的明文数据进行返回，还是查询密文数据通过 SphereEx-DBPlusEngine 解密后返回。该属性开关支持字段级、表级别和全局规则级别配置，由 QUERY_WITH_CIPHER_COLUMN 进行配置，字段级别优先级最高。

使用指南 #

加密-新上线业务 #

新上线业务由于一切从零开始，不存在历史数据清洗问题，所以相对简单。选择合适的加密算法，如 AES 后，只需配置逻辑列（面向用户编写 SQL ）和密文列（数据表存密文数据）即可，逻辑列和密文列可以相同也可以不同。建议配置如下（YAML 格式展示）：

-!ENCRYPT
  encryptors:
    aes_encryptor:
      type: AES
      props:
        aes-key-value: 123456abc
  tables:
    t_user:
      columns:
        pwd:
          cipherColumn: pwd
          encryptorName: aes_encryptor

使用这套配置， SphereEx-DBPlusEngine 只需将 logicColumn 和 cipherColumn 进行转换，底层数据表不存储明文，只存储了密文，这也是安全审计部分的要求所在。如果用户希望将明文、密文一同存储到数据库，只需添加 plainColumn 配置即可。操作流程如下：

设计加密表，确定加密字段，可结合表结构、数据特点及应用情况综合考虑；
在 SphereEx-DBPlusEngine 创建逻辑库，注册存储节点（空）；
创建加密规则，可使用 SphereEx-Console 或 DistSQL 完成；
创建加密表，完成加密表构建；
根据业务实际情况，再选择保留或删除明文字段。

洗数-已上线业务改造 #

对于已上线的业务，需评估改造后 SQL 的支持度，另外存量数据洗数是必不可少的一个环节。

确认加密范围根据合规需求，明确具体的加密数据范围。
确认加密算法选择并确认适合的加密算法。
梳理业务 SQL，确认支持情况确认当前业务中是否存在非等值的密态计算情况，如 like、>、< 等，若存在，则需考虑业务改造。
洗数完成以上内容后，对于已上线的项目，需要对存量数据进行洗数。该过程无需引用外部组件，SphereEx-DBPlusEngine 可完成洗数过程，该过程可在线进行。
启用密文列启用密文列之后，应用的请求将通过 SphereEx-DBPlusEngine 与密文字段产生交互，此时明文字段依然在写入数据。
下线明文列当系统经过足够长时间的使用和观察，可将明文字段进行下线。敏感字段只以密文形式存在系统中。

反洗数 #

新增对应明文字段
修改加密配置，在配置中增加明文字段，此时 queryWithCipher 为 true
开始反洗数
修改 queryWithCipher 为 false
删除加密规则
最后再情况加密字段

重洗数 #

新增对应明文字段
开始反洗数
修改 queryWithCipher 为 false
新增新的加密字段，清空原加密字段
修改加密规则及算法
洗数
修改 queryWithCipher 为 true
修改加密规则，删除明文字段
删除原有加密字段和明文字段

操作指南 #

数据加密-新业务 #

设计加密表，确定加密字段，可结合表结构、数据特点及应用情况综合考虑；
在 SphereEx-DBPlusEngine 创建逻辑库，注册存储节点（空）；
创建加密规则，可使用 SphereEx-Console 或 DistSQL 完成；
创建加密表，完成加密表构建；
根据业务实际情况，再选择保留或删除明文字段。

加密洗数-成熟业务 #

设计加密表，确定加密字段，可结合表结构、数据特点及应用情况综合考虑；
在 SphereEx-DBPlusEngine 创建逻辑库，注册数据源（明文数据）；
使用 DistSQL 创建加密规则；
通过 ENCRYPT TABLE 命令开始洗数；
通过 ALTER ENCRYPT RULE 命令启用加密字段；
根据业务实际情况，再选择保留或删除明文字段。

配置示例 #

新上线业务加密配置-DistSQL #

业务场景分析：新上线业务由于一切从零开始，不存在历史数据清洗问题，所以相对简单。

解决方案说明：选择合适的加密算法，如 AES 后，只需配置逻辑列（面向用户编写 SQL ）和密文列（数据表存密文数据）即可，逻辑列和密文列可以相同也可以不同。

SphereEx-DBPlusEngine 只需将 logicColumn 和 cipherColumn，assistedQueryColumn 进行转换，底层数据表不存储明文，只存储了密文，这也是安全审计部分的要求所在。如果用户希望将明文、密文一同存储到数据库，只需添加 plainColumn配置即可。整体处理流程如下图所示：

整体处理流程图

环境说明 #

	实例	IP 地址	服务端口	主机名	备注
1	DBPlusEngine 1.2.0	192.168.xx.102	3307	dbplusengine
2	MySQL 5.7.26	192.168.xx.106	3306	ds_encryption

拓扑图 #

拓扑图

配置过程 #

准备数据源

在 MySQL 中创建名为 ds_encryption 的数据库。

# mysql -utest -p -h192.168.xx.106

mysql> CREATE DATABASE ds_encryption;
Query OK, 1 row affected (0.05 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| ds_encryption      |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

在 SphereEx-DBPlusEngine 中创建名为 testdb 的逻辑库。

# mysql -uroot -p -P3307 -h127.0.0.1

mysql> CREATE DATABASE testdb;
Query OK, 0 rows affected (0.85 sec)

mysql> SHOW DATABASES;
+--------------------+
| schema_name        |
+--------------------+
| mysql              |
| information_schema |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)

将数据源注册到 SphereEx-DBPlusEngine 中

mysql> USE testdb;
Database changed

mysql> REGISTER STORAGE UNIT ds_0 (
    URL="jdbc:mysql://192.168.xx.103:3306/mysql_0?serverTimezone=UTC&useSSL=false",
    USER="test",
    PASSWORD="Test@123"
);
Query OK, 0 rows affected (0.81 sec)

在 SphereEx-DBPlusEngine 中创建分片规则

mysql> CREATE ENCRYPT RULE t_user (
  COLUMNS(
    (
      NAME = id_card_no,
      PLAIN = id_card_no_plain,
      CIPHER = id_card_no_cipher,
      ENCRYPT_ALGORITHM(
        TYPE(
          NAME = 'AES',
          PROPERTIES('aes-key-value' = '123456abc')
        )
      )
    ),
    (
      NAME = mobile,
      PLAIN = mobile_plain,
      CIPHER = mobile_cipher,
      ENCRYPT_ALGORITHM(
        TYPE(
          NAME = 'AES',
          PROPERTIES('aes-key-value' = '123456abc')
        )
      )
    )
  ),
  QUERY_WITH_CIPHER_COLUMN = true
);
Query OK, 0 rows affected (6.56 sec)

mysql> SHOW ENCRYPT TABLE RULE t_user\G
*************************** 1. row ***************************
                   table: t_user
            logic_column: id_card_no
         logic_data_type:
           cipher_column: id_card_no_cipher
        cipher_data_type:
            plain_column: id_card_no_plain
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
query_with_cipher_column: true
*************************** 2. row ***************************
                   table: t_user
            logic_column: mobile
         logic_data_type:
           cipher_column: mobile_cipher
        cipher_data_type:
            plain_column: mobile_plain
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
query_with_cipher_column: true
2 rows in set (0.00 sec)

在以上配置中，对 t_user 表中 mobile 和 id_card_no 字段通过 AES 算法进行加密。

创建表并插入若干条测试记录

mysql> CREATE TABLE t_user (
    id INT(8), 
    mobile VARCHAR(50), 
    id_card_no VARCHAR(50)
);
Query OK, 0 rows affected (1.90 sec)

mysql> INSERT INTO t_user (id, mobile, id_card_no)
VALUES (1, 18236483857, 220605194709308170),
       (2, 15686689114, 360222198806088804),
       (3, 14523360225, 411601198601098107),
       (4, 18143924353, 540228199804231247),
       (5, 15523349333, 360924195311103360),
       (6, 13261527931, 513229195302236086),
       (7, 13921892133, 500108194806107214),
       (8, 15993370854, 451322194405305441),
       (9, 18044280924, 411329199808285772),
       (10, 13983621809, 430204195612042092);
Query OK, 10 rows affected (0.61 sec)

确认测试数据插入结果，同时执行 join 的操作进行验证。

mysql> SELECT * FROM t_user;
+------+-------------+--------------------+
| id   | mobile      | id_card_no         |
+------+-------------+--------------------+
|    1 | 18236483857 | 220605194709308170 |
|    2 | 15686689114 | 360222198806088804 |
|    3 | 14523360225 | 411601198601098107 |
|    4 | 18143924353 | 540228199804231247 |
|    5 | 15523349333 | 360924195311103360 |
|    6 | 13261527931 | 513229195302236086 |
|    7 | 13921892133 | 500108194806107214 |
|    8 | 15993370854 | 451322194405305441 |
|    9 | 18044280924 | 411329199808285772 |
|   10 | 13983621809 | 430204195612042092 |
+------+-------------+--------------------+
10 rows in set (0.25 sec)

mysql> SELECT * FROM t_user WHERE mobile = '18236483857';
+------+-------------+--------------------+
| id   | mobile      | id_card_no         |
+------+-------------+--------------------+
|    1 | 18236483857 | 220605194709308170 |
+------+-------------+--------------------+
1 row in set (0.50 sec)

mysql>
mysql> SELECT * FROM t_user WHERE mobile = '18236483857'
    -> AND id_card_no = '220605194709308170';
+------+-------------+--------------------+
| id   | mobile      | id_card_no         |
+------+-------------+--------------------+
|    1 | 18236483857 | 220605194709308170 |
+------+-------------+--------------------+
1 row in set (0.26 sec)

在 MySQL 中进行查看，可见数据库中 mobile 和 id_card_no 字段均存储的是密文数据。

mysql -utest -p -h192.168.xx.103

mysql> USE ds_encryption;
Database changed

mysql> SHOW TABLES;
+-------------------------+
| Tables_in_ds_encryption |
+-------------------------+
| t_user                  |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t_user;
+------+--------------------------+----------------------------------------------+
| id   | mobile_cipher            | id_card_no_cipher                            |
+------+--------------------------+----------------------------------------------+
|    1 | p31Pkl9nIunYdH+AngyNUA== | pQv0JEkM94QzktJdM8UMg/uLrU71G6n6DALdPp9w6L0= |
|    2 | CV8+uYRaWOzcTQnQX3RcwA== | dCF7k4haK0aIV/d7dtwgzIb4lIFlJ913hrPim1+J278= |
|    3 | jnfu7o44KgN/PV1zhiu7jw== | 8iulp3+XTSv2XHGUUHKV0UsLuFx7yEpQVT+47EFfg94= |
|    4 | ZJDrTv/XIjdqdG1yp0t95w== | iqU6myMGfgI/XnxCtjhbMrwIauriWu8crxPS6BH2pMk= |
|    5 | FnQMYGnFJaiWmTHeNYzbFA== | KAPrCXoo1svMt5NWe0UaKYZIl1rSEVddHbBJO1jPIqw= |
|    6 | lv2ECfTCgQQksvdPp6k3Ug== | BBBPAuwU+iJluI9d9TA+H81BPnVXBaly1BE3EplN4e8= |
|    7 | z46vpnHCFTkIF2EtntxpHQ== | Bc39nPtyz1ji9Rc8k4f7G9CKfPew23mKFwp8guK7ybg= |
|    8 | p/IJdGcCikhpCu5gVZj4jg== | nnv/kS1i7uHXKncUOuLzE8OWM0nGlcGkLokT2dltSaQ= |
|    9 | NvPcQv4w3EqD77+VAX0KCA== | +yeo5LWKNWcekFqYawCKjsctAZqe104DrI7AeZdR/Uk= |
|   10 | xOyg9E0X9lhy9mUx0QyL0A== | U7P1CMcxn6VPHYHPgTAtjHEbb6N6vhGOpdJtVjAdHlA= |
+------+--------------------------+----------------------------------------------+
10 rows in set (0.00 sec)

删除明文字段

（注：删除明文列是高危且不可逆操作，建议先稳定运行数月后再执行该操作。）

--首先在数据库中删除铭文字段
--然后在 Proxy 中刷新元数据信息即可
mysql> REFRESH table METADATA;

以上为使用 DistSQL 的方式完成了加密表的配置，下面将演示通过 Console 来完成加密的配置。

新上线业务加密配置-Console #

环境说明 #

	实例	IP 地址	服务端口	主机名
1	DBPlusEngine 1.2.0	192.168.xx.102	3307	dbplusengine
2	Console 1.1.0	192.168.xx.101	8089	console
3	MySQL 5.7.26	192.168.xx.106	3306	ds_encryption

拓扑图 #

拓扑图

配置过程 #

登陆 SphereEx-Console，在“集群”-“对象”页中，点击“新建表”按钮。

根据参考示例，输入如下建表语句，点击“确定”。

CREATE TABLE t_encrypt(
    id INT(8), 
    mobile VARCHAR(50), 
    id_card_no VARCHAR(50)
);

添加加密插件

选择创建的数据表：t_encrypt，点击“添加加密”–>选择“常规”加密–>点击“添加加密插件”，选择“加密算法”，选择 “需要加密的列”，勾选“存储明文”，点击“添加”–>点击“确定”。

插入测试数据

# mysql -uroot -proot -P3307 -h192.168.xx.102

mysql>use logical001;
mysql> INSERT INTO t_encrypt (id, mobile_plain, id_card_no)
    -> VALUES (1, 18236483857, 220605194709308170),
    ->        (2, 15686689114, 360222198806088804),
    ->        (3, 13983621809, 430204195612042092);
Query OK, 3 rows affected (0.02 sec)

确认数据加密结果

（说明：如果需要 SphereEx-DBPlusEngine 中显示明文，不勾选 “是否使用加密列查询”。）

mysql -uroot -proot -P3307 -h192.168.xx.102

mysql>use logical001;
mysql> SELECT * FROM t_encrypt;
+------+----------------------------------+--------------------+
| id   | mobile                           | id_card_no         |
+------+----------------------------------+--------------------+
|    1 | 6f655ea3b73e32d54ae2d34d672c0c55 | 220605194709308170 |
|    2 | a2193d0e8de4c2c4f166448e22ba97ae | 360222198806088804 |
|    3 | 3cf1fd442630071ca664bbd5e683f7f6 | 430204195612042092 |
+------+----------------------------------+--------------------+
3 rows in set (0.00 sec)

mysql -uroot -proot -P3306 -h192.168.xx.106

mysql> use t_encrypt_db;
mysql> select * from t_encrypt;
+------+----------------------------------+--------------+--------------------+
| id   | mobile_cipher                    | mobile_plain | id_card_no         |
+------+----------------------------------+--------------+--------------------+
|    1 | 6f655ea3b73e32d54ae2d34d672c0c55 | 18236483857  | 220605194709308170 |
|    2 | a2193d0e8de4c2c4f166448e22ba97ae | 15686689114  | 360222198806088804 |
|    3 | 3cf1fd442630071ca664bbd5e683f7f6 | 13983621809  | 430204195612042092 |
+------+----------------------------------+--------------+--------------------+

以上是 Console 配置加密表的过程，操作相对更简化。

已上线业务加密配置-DistSQL #

对于已经上线运行一段时间的业务系统，数据库里会存在大量明文历史数据，本示例将演示如何对历史项目中的数据进行洗数。

准备 MySQL 环境（明文），模拟已上线数据库

mysql> CREATE DATABASE ds_mysql;
Query OK, 1 row affected (0.00 sec)

mysql> USE ds_mysql;
Database changed

mysql> CREATE TABLE t_user (
    id INT(8) primary key, 
    mobile VARCHAR(50), 
    id_card_no VARCHAR(50)
);
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> INSERT INTO t_user (id, mobile, id_card_no)
VALUES (1, 18236483857, 220605194709308170),
       (2, 15686689114, 360222198806088804),
       (3, 14523360225, 411601198601098107),
       (4, 18143924353, 540228199804231247),
       (5, 15523349333, 360924195311103360),
       (6, 13261527931, 513229195302236086),
       (7, 13921892133, 500108194806107214),
       (8, 15993370854, 451322194405305441),
       (9, 18044280924, 411329199808285772),
       (10, 13983621809, 430204195612042092);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t_user;
+----+-------------+--------------------+
| id | mobile      | id_card_no         |
+----+-------------+--------------------+
|  1 | 18236483857 | 220605194709308170 |
|  2 | 15686689114 | 360222198806088804 |
|  3 | 14523360225 | 411601198601098107 |
|  4 | 18143924353 | 540228199804231247 |
|  5 | 15523349333 | 360924195311103360 |
|  6 | 13261527931 | 513229195302236086 |
|  7 | 13921892133 | 500108194806107214 |
|  8 | 15993370854 | 451322194405305441 |
|  9 | 18044280924 | 411329199808285772 |
| 10 | 13983621809 | 430204195612042092 |
+----+-------------+--------------------+
10 rows in set (0.00 sec)

此时，已经完成了 MySQL 环境的准备，在 ds_mysql 数据库中存在一张名为 t_user 的明文表。

在 SphereEx-DBPlusEngine 中创建明为 testdb 的逻辑库，并将 MySQL 数据源注册到其中

mysql> CREATE DATABASE testdb;
Query OK, 0 rows affected (0.00 sec)

mysql> USE testdb;
Database changed

mysql> REGISTER STORAGE UNIT ds_0 (
    URL="jdbc:mysql://192.168.xx.103:3306/ds_mysql?serverTimezone=UTC&useSSL=false",
    USER="test",
    PASSWORD="Test@123"
);
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW STORAGE UNITS\G
*************************** 1. row ***************************
                           name: ds_0
                           type: MySQL
                           host: 192.168.xx.103
                           port: 3306
                             db: ds_mysql
connection_timeout_milliseconds: 30000
      idle_timeout_milliseconds: 60000
      max_lifetime_milliseconds: 2100000
                  max_pool_size: 50
                  min_pool_size: 1
                      read_only: false
               other_attributes: {"dataSourceProperties":{"cacheServerConfiguration":"true","elideSetAutoCommits":"true","useServerPrepStmts":"true","cachePrepStmts":"true","rewriteBatchedStatements":"true","cacheResultSetMetadata":"false","useLocalSessionState":"true","maintainTimeStats":"false","prepStmtCacheSize":"8192","tinyInt1isBit":"false","prepStmtCacheSqlLimit":"2048","netTimeoutForStreamingResults":"0","zeroDateTimeBehavior":"round"},"healthCheckProperties":{},"initializationFailTimeout":1,"validationTimeout":5000,"keepaliveTime":0,"leakDetectionThreshold":0,"registerMbeans":false,"allowPoolSuspension":false,"autoCommit":true,"isolateInternalQueries":false}
1 row in set (0.00 sec)

mysql> SHOW TABLES;
+------------------+------------+
| Tables_in_testdb | Table_type |
+------------------+------------+
| t_user           | BASE TABLE |
+------------------+------------+
1 row in set (0.01 sec)

通过如上输出可确认，逻辑库中已经识别到明文表 t_user。

洗数工作前的准备

在开始洗数前，需要在 MySQL 中增加对应的密文字段。这里将增加 2 个密文字段，分别对应 mobile 和 id_card_no。

mysql> ALTER TABLE t_user ADD COLUMN mobile_cipher varchar(50) AFTER mobile;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t_user ADD COLUMN id_card_no_cipher varchar(50) AFTER id_card_no;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t_user;
+-------------------+-------------+------+-----+---------+-------+
| Field             | Type        | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| id                | int         | NO   | PRI | NULL    |       |
| mobile            | varchar(50) | YES  |     | NULL    |       |
| mobile_cipher     | varchar(50) | YES  |     | NULL    |       |
| id_card_no        | varchar(50) | YES  |     | NULL    |       |
| id_card_no_cipher | varchar(50) | YES  |     | NULL    |       |
+-------------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM t_user;
+----+-------------+---------------+--------------------+-------------------+
| id | mobile      | mobile_cipher | id_card_no         | id_card_no_cipher |
+----+-------------+---------------+--------------------+-------------------+
|  1 | 18236483857 | NULL          | 220605194709308170 | NULL              |
|  2 | 15686689114 | NULL          | 360222198806088804 | NULL              |
|  3 | 14523360225 | NULL          | 411601198601098107 | NULL              |
|  4 | 18143924353 | NULL          | 540228199804231247 | NULL              |
|  5 | 15523349333 | NULL          | 360924195311103360 | NULL              |
|  6 | 13261527931 | NULL          | 513229195302236086 | NULL              |
|  7 | 13921892133 | NULL          | 500108194806107214 | NULL              |
|  8 | 15993370854 | NULL          | 451322194405305441 | NULL              |
|  9 | 18044280924 | NULL          | 411329199808285772 | NULL              |
| 10 | 13983621809 | NULL          | 430204195612042092 | NULL              |
+----+-------------+---------------+--------------------+-------------------+
10 rows in set (0.00 sec)

当前密文字段内容为空，稍后将会通过洗数操作在线转换密文数据。

在 SphereEx-DBPlusEngine 中配置加密规则

CREATE ENCRYPT RULE t_user (
  COLUMNS(
    (
      NAME = id_card_no,
      PLAIN = id_card_no,
      CIPHER = id_card_no_cipher,
      ENCRYPT_ALGORITHM(
        TYPE(
          NAME = 'AES',
          PROPERTIES('aes-key-value' = '123456abc')
        )
      )
    ),
    (
      NAME = mobile,
      PLAIN = mobile,
      CIPHER = mobile_cipher,
      ENCRYPT_ALGORITHM(
        TYPE(
          NAME = 'AES',
          PROPERTIES('aes-key-value' = '123456abc')
        )
      )
    )
  ),
  QUERY_WITH_CIPHER_COLUMN = false
);
Query OK, 0 rows affected (0.05 sec)


-- NAME：为业务中字段名称，基于应用免改造的出发点，可设置与明文字段名称一致
-- PLAIN：明文字段名称
-- CIPHER：密文字段名称
-- ENCRYPT_ALGORITHM：加密算法相关配置
-- QUERY_WITH_CIPHER_COLUMN：是否启用密文字段，当前密文字段为空，因此需要设置为 false

mysql> SHOW ENCRYPT TABLE RULE t_user\G
*************************** 1. row ***************************
                   table: t_user
            logic_column: id_card_no
         logic_data_type:
           cipher_column: id_card_no_cipher
        cipher_data_type:
            plain_column: id_card_no
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
query_with_cipher_column: false
*************************** 2. row ***************************
                   table: t_user
            logic_column: mobile
         logic_data_type:
           cipher_column: mobile_cipher
        cipher_data_type:
            plain_column: mobile
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
query_with_cipher_column: false
2 rows in set (0.00 sec)

mysql> PREVIEW SELECT * FROM t_user;
+------------------+--------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                                             |
+------------------+--------------------------------------------------------------------------------------------------------+
| ds_0             | SELECT `t_user`.`id`, `t_user`.`mobile` AS `mobile`, `t_user`.`id_card_no` AS `id_card_no` FROM t_user |
+------------------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

通过 PREVIEW 结果来看，当前是通过明文字段来响应请求，与预期一致。

开始洗数在配置好加密字段及加密规则后，可开始洗数。

mysql> ENCRYPT TABLE t_user;
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW ENCRYPTING LIST;
+---------------------------------------+--------+----------------+--------+---------------------+-----------+
| id                                    | tables | job_item_count | active | create_time         | stop_time |
+---------------------------------------+--------+----------------+--------+---------------------+-----------+
| j5201e2af8bea809d29f6ab4b3ac4c99b5789 | t_user | 1              | true   | 2023-02-05 13:56:32 | NULL      |
+---------------------------------------+--------+----------------+--------+---------------------+-----------+
1 row in set (0.00 sec)

mysql> SHOW ENCRYPTING STATUS j5201e2af8bea809d29f6ab4b3ac4c99b5789;
+------+-------------+------------------------+--------+--------------------------------+---------------+
| item | data_source | status                 | active | encrypting_finished_percentage | error_message |
+------+-------------+------------------------+--------+--------------------------------+---------------+
| 0    | testdb      | EXECUTE_INVENTORY_TASK | true   | 100                            |               |
+------+-------------+------------------------+--------+--------------------------------+---------------+
1 row in set (0.03 sec)

如上所示，加密完成进度为 100%，可登陆 MySQL 查看真实表的数据。此时 MySQL 中的数据如下：

mysql> SELECT * FROM t_user;
+----+-------------+--------------------------+--------------------+----------------------------------------------+
| id | mobile      | mobile_cipher            | id_card_no         | id_card_no_cipher                            |
+----+-------------+--------------------------+--------------------+----------------------------------------------+
|  1 | 18236483857 | p31Pkl9nIunYdH+AngyNUA== | 220605194709308170 | pQv0JEkM94QzktJdM8UMg/uLrU71G6n6DALdPp9w6L0= |
|  2 | 15686689114 | CV8+uYRaWOzcTQnQX3RcwA== | 360222198806088804 | dCF7k4haK0aIV/d7dtwgzIb4lIFlJ913hrPim1+J278= |
|  3 | 14523360225 | jnfu7o44KgN/PV1zhiu7jw== | 411601198601098107 | 8iulp3+XTSv2XHGUUHKV0UsLuFx7yEpQVT+47EFfg94= |
|  4 | 18143924353 | ZJDrTv/XIjdqdG1yp0t95w== | 540228199804231247 | iqU6myMGfgI/XnxCtjhbMrwIauriWu8crxPS6BH2pMk= |
|  5 | 15523349333 | FnQMYGnFJaiWmTHeNYzbFA== | 360924195311103360 | KAPrCXoo1svMt5NWe0UaKYZIl1rSEVddHbBJO1jPIqw= |
|  6 | 13261527931 | lv2ECfTCgQQksvdPp6k3Ug== | 513229195302236086 | BBBPAuwU+iJluI9d9TA+H81BPnVXBaly1BE3EplN4e8= |
|  7 | 13921892133 | z46vpnHCFTkIF2EtntxpHQ== | 500108194806107214 | Bc39nPtyz1ji9Rc8k4f7G9CKfPew23mKFwp8guK7ybg= |
|  8 | 15993370854 | p/IJdGcCikhpCu5gVZj4jg== | 451322194405305441 | nnv/kS1i7uHXKncUOuLzE8OWM0nGlcGkLokT2dltSaQ= |
|  9 | 18044280924 | NvPcQv4w3EqD77+VAX0KCA== | 411329199808285772 | +yeo5LWKNWcekFqYawCKjsctAZqe104DrI7AeZdR/Uk= |
| 10 | 13983621809 | xOyg9E0X9lhy9mUx0QyL0A== | 430204195612042092 | U7P1CMcxn6VPHYHPgTAtjHEbb6N6vhGOpdJtVjAdHlA= |
+----+-------------+--------------------------+--------------------+----------------------------------------------+
10 rows in set (0.00 sec)

表中明文密文同时存在，此时为业务提供响应的是明文字段。

启用秘文字段

当明文密文并行运行一段时间后，可结合实际情况启用密文字段。

ALTER ENCRYPT RULE t_user (
  COLUMNS(
    (
      NAME = id_card_no,
      PLAIN = id_card_no,
      CIPHER = id_card_no_cipher,
      ENCRYPT_ALGORITHM(
        TYPE(
          NAME = 'AES',
          PROPERTIES('aes-key-value' = '123456abc')
        )
      )
    ),
    (
      NAME = mobile,
      PLAIN = mobile,
      CIPHER = mobile_cipher,
      ENCRYPT_ALGORITHM(
        TYPE(
          NAME = 'AES',
          PROPERTIES('aes-key-value' = '123456abc')
        )
      )
    )
  ),
  QUERY_WITH_CIPHER_COLUMN = true
);
Query OK, 0 rows affected (0.02 sec)


mysql> SHOW ENCRYPT TABLE RULE t_user\G
*************************** 1. row ***************************
                   table: t_user
            logic_column: id_card_no
         logic_data_type:
           cipher_column: id_card_no_cipher
        cipher_data_type:
            plain_column: id_card_no
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
query_with_cipher_column: true
*************************** 2. row ***************************
                   table: t_user
            logic_column: mobile
         logic_data_type:
           cipher_column: mobile_cipher
        cipher_data_type:
            plain_column: mobile
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
query_with_cipher_column: true
2 rows in set (0.01 sec)

mysql> PREVIEW SELECT * FROM t_user;
+------------------+----------------------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                                                           |
+------------------+----------------------------------------------------------------------------------------------------------------------+
| ds_0             | SELECT `t_user`.`id`, `t_user`.`mobile_cipher` AS `mobile`, `t_user`.`id_card_no_cipher` AS `id_card_no` FROM t_user |
+------------------+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

此时，通过 PREVIEW 输出所示，业务已开始使用了密文字段。洗数操作完成，明文字段可根据实际的使用需求进行变更调整。

模糊查询 #

SphereEx-DBPlusEngine 支持加密列 like 查询，以下为验证步骤。

配置 MySQL 配置文件

为了避免 MySQL 存储中文乱码导致 LIKE 功能错乱，需要提前修改 my.cnf，确保进行了如下配置：

[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
port=3306
character-set-server=utf8mb4
character-set-filesystem=utf8mb4
collation-server=utf8mb4_general_ci
init-connect='SET NAMES utf8mb4'

创建逻辑库，并添加资源

CREATE DATABASE encrypt_db;

USE encrypt_db;

REGISTER STORAGE UNIT ds_0 (
    HOST="127.0.0.1",
    PORT=3306,
    DB="demo_ds_0",
    USER="root",
    PASSWORD="123456"
), ds_1 (
    HOST="127.0.0.1",
    PORT=3306,
    DB="demo_ds_1",
    USER="root",
    PASSWORD="123456"
);

创建加密规则

CREATE ENCRYPT KEY MANAGER aws_kms_key_manager (TYPE(NAME='SphereEx:AWS_KMS', PROPERTIES('access-key'='AKIAXRZPPZNGXS6VJUGY','secret-key'='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','aws-region'='ap-northeast-1','secret-name'='dbplus-engine/encrypt-key')));

CREATE ENCRYPT RULE t_user (
        COLUMNS(
                (
                        NAME=user_name,PLAIN=user_name_plain,CIPHER=user_name_cipher,LIKE_QUERY_COLUMN=user_name_like,
                        ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))),
                        LIKE_QUERY_ALGORITHM(TYPE(NAME='SphereEx:CHAR_TRANSFORM_LIKE',PROPERTIES('key-manager'= "aws_kms_key_manager")))
                ),
                (
                        NAME=password, CIPHER =password_cipher,
                        ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc')))
                ),
                (
                        NAME=email, CIPHER =email_cipher,
                        ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc')))
                ),
                (
                        NAME=telephone,PLAIN=telephone_plain,CIPHER=telephone_cipher,LIKE_QUERY_COLUMN=telephone_like,
                        ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))),
                        LIKE_QUERY_ALGORITHM(TYPE(NAME='SphereEx:CHAR_TRANSFORM_LIKE',PROPERTIES('key-manager'= "aws_kms_key_manager")))
                )
        ), QUERY_WITH_CIPHER_COLUMN = TRUE
), t_merchant (
        COLUMNS(
                (
                        NAME=business_code,PLAIN=business_code_plain,CIPHER=business_code_cipher,LIKE_QUERY_COLUMN=business_code_like,
                        ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))),
                        LIKE_QUERY_ALGORITHM(TYPE(NAME='SphereEx:CHAR_TRANSFORM_LIKE',PROPERTIES('key-manager'= "aws_kms_key_manager")))
                ),
                (
                        NAME=telephone,PLAIN=telephone_plain,CIPHER=telephone_cipher,LIKE_QUERY_COLUMN=telephone_like,
                        ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))),
                        LIKE_QUERY_ALGORITHM(TYPE(NAME='SphereEx:CHAR_TRANSFORM_LIKE',PROPERTIES('key-manager'= "aws_kms_key_manager")))
                )
        ), QUERY_WITH_CIPHER_COLUMN = TRUE
);

创建逻辑库，并创建测试表，插入若干条测试记录

USE encrypt_db;

DROP TABLE IF EXISTS t_merchant;


CREATE TABLE t_merchant (
  merchant_id INT PRIMARY KEY, 
  country_id SMALLINT NOT NULL, 
  merchant_name VARCHAR(50) NOT NULL, 
  business_code VARCHAR(50) NOT NULL, 
  telephone CHAR(50) NOT NULL, 
  creation_date DATE NOT NULL);

INSERT INTO t_merchant ......

CREATE TABLE t_user (
  user_id INT PRIMARY KEY, 
  user_name VARCHAR(50) NOT NULL, 
  password VARCHAR(50) NOT NULL, 
  email VARCHAR(50) NOT NULL, 
  telephone CHAR(50) NOT NULL, 
  creation_date DATE NOT NULL);

INSERT INTO t_user ......

测试数字 LIKE 查询

-- 测试 % 前缀匹配
SELECT * FROM t_merchant WHERE business_code LIKE '%1';
+-------------+------------+---------------+---------------+-------------+---------------+
| merchant_id | country_id | merchant_name | business_code | telephone   | creation_date |
+-------------+------------+---------------+---------------+-------------+---------------+
|           1 |         86 | test101       | 86000001      | 86100000001 | 2017-08-08    |
|          11 |          1 | test111       | 01000011      | 01100000011 | 2017-08-08    |
+-------------+------------+---------------+---------------+-------------+---------------+
2 rows in set (0.01 sec)

-- 测试 % 后缀匹配
SELECT * FROM t_merchant WHERE business_code LIKE '86%';
+-------------+------------+---------------+---------------+-------------+---------------+
| merchant_id | country_id | merchant_name | business_code | telephone   | creation_date |
+-------------+------------+---------------+---------------+-------------+---------------+
|           1 |         86 | test101       | 86000001      | 86100000001 | 2017-08-08    |
|           2 |         86 | test102       | 86000002      | 86100000002 | 2017-08-08    |
|           3 |         86 | test103       | 86000003      | 86100000003 | 2017-08-08    |
|           4 |         86 | test104       | 86000004      | 86100000004 | 2017-08-08    |
|           5 |         86 | test105       | 86000005      | 86100000005 | 2017-08-08    |
|           6 |         86 | test106       | 86000006      | 86100000006 | 2017-08-08    |
|           7 |         86 | test107       | 86000007      | 86100000007 | 2017-08-08    |
|           8 |         86 | test108       | 86000008      | 86100000008 | 2017-08-08    |
|           9 |         86 | test109       | 86000009      | 86100000009 | 2017-08-08    |
|          10 |         86 | test110       | 86000010      | 86100000010 | 2017-08-08    |
+-------------+------------+---------------+---------------+-------------+---------------+
10 rows in set (0.01 sec)

测试字母 LIKE 查询

-- 测试 % 前缀匹配
SELECT * FROM t_user WHERE user_name LIKE '%san';
+---------+-----------+----------+--------------------+-------------+---------------+
| user_id | user_name | password | email              | telephone   | creation_date |
+---------+-----------+----------+--------------------+-------------+---------------+
|      10 | zhangsan  | 111111   | zhangsan@gmail.com | 12345678900 | 2017-08-08    |
|      19 | zhousan   | 123456   | zhousan@gmail.com  | 12345678909 | 2017-08-08    |
+---------+-----------+----------+--------------------+-------------+---------------+
2 rows in set (0.01 sec)

-- 测试 % 后缀匹配
SELECT * FROM t_user WHERE user_name LIKE 'j%';
+---------+-----------+----------+------------------+-------------+---------------+
| user_id | user_name | password | email            | telephone   | creation_date |
+---------+-----------+----------+------------------+-------------+---------------+
|      22 | jerry     | 456789   | jerry@gmail.com  | 12345678912 | 2017-08-08    |
|      23 | james     | 567890   | james@gmail.com  | 12345678913 | 2017-08-08    |
|      27 | jack      | 222333   | jack@gmail.com   | 12345678917 | 2017-08-08    |
|      28 | jordan    | 333444   | jordan@gmail.com | 12345678918 | 2017-08-08    |
|      29 | julie     | 444555   | julie@gmail.com  | 12345678919 | 2017-08-08    |
+---------+-----------+----------+------------------+-------------+---------------+
5 rows in set (0.01 sec)

测试汉字 LIKE 查询

- 测试 % 前缀匹配
SELECT * FROM t_user WHERE user_name LIKE '%靖';
+---------+-----------+----------+-------------------+-------------+---------------+
| user_id | user_name | password | email             | telephone   | creation_date |
+---------+-----------+----------+-------------------+-------------+---------------+
|      34 | 郭靖      | 123123   | guojing@gmail.com | 18012512345 | 2017-08-08    |
+---------+-----------+----------+-------------------+-------------+---------------+
1 row in set (0.01 sec)

-- 测试 % 后缀匹配
SELECT * FROM t_user WHERE user_name LIKE '王%';
+---------+-----------+----------+--------------------------+-------------+---------------+
| user_id | user_name | password | email                    | telephone   | creation_date |
+---------+-----------+----------+--------------------------+-------------+---------------+
|      35 | 王之涣    | 234234   | wangzhihuan@gmail.com | 18012512345 | 2017-08-08    |
|      36 | 王安石    | 345345   | jeff@gmail.com           | 18012512345 | 2017-08-08    |
|      37 | 王羲之    | 345345   | wangxizhi@gmail.com      | 18012512345 | 2017-08-08    |
|      38 | 王莽      | 456456   | wangmang@gmail.com       | 18012512345 | 2017-08-08    |
|      39 | 王勃      | 567567   | wangbo@gmail.com         | 18012512345 | 2017-08-08    |
+---------+-----------+----------+--------------------------+-------------+---------------+
5 rows in set (0.01 sec)

反洗数 #

环境准备

快速构建环境，创建物理库和逻辑库。

-- MySQL
mysql> create database demo_ds_0;

-- DBPlusEngine
mysql> create database encrypt_db;

mysql> ADD RESOURCE ds_0 (
    URL="jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false",
    USER="root",
    PASSWORD="123456",
    PROPERTIES("maximumPoolSize"="50","idleTimeout"="60000")
);

mysql> CREATE ENCRYPT RULE t_encrypt (
COLUMNS(
(NAME=user_id,PLAIN=user_plain,CIPHER=user_cipher,TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))),
(NAME=order_id,PLAIN=order_id, CIPHER =order_cipher,TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc')))
),QUERY_WITH_CIPHER_COLUMN=true);

mysql> CREATE TABLE `t_encrypt` (
  `id` int NOT NULL,
  `user_id` varchar(200) DEFAULT NULL,
  `order_id` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

mysql> insert into t_encrypt values (1,123,123),(2,234,234),(3,345,345);

将明文字段更新数据为空，为反洗数做环境准备。

mysql> update t_encrypt set user_plain = null , order_id = null;

mysql> select * from t_encrypt;
+----+--------------------------+------------+--------------------------+----------+
| id | user_cipher              | user_plain | order_cipher             | order_id |
+----+--------------------------+------------+--------------------------+----------+
|  1 | DZEHT99l6UjthceKuCCKIw== | NULL       | DZEHT99l6UjthceKuCCKIw== | NULL     |
|  2 | Q3YzQboxcI5L5DSx9bSp9Q== | NULL       | Q3YzQboxcI5L5DSx9bSp9Q== | NULL     |
|  3 | pJOAtTsHiqrpPRv/cv8MnA== | NULL       | pJOAtTsHiqrpPRv/cv8MnA== | NULL     |
+----+--------------------------+------------+--------------------------+----------+
3 rows in set (0.01 sec)

创建反洗数配置

mysql> create DECRYPTING PROCESS CONFIGURATION(
READ(
  WORKER_THREAD=40,
  BATCH_SIZE=1000,
  SHARDING_SIZE=1000000
),
WRITE(
  WORKER_THREAD=40,
  BATCH_SIZE=1000
),
STREAM_CHANNEL(TYPE(NAME="MEMORY", PROPERTIES("block-queue-size"=10000)))
);

mysql> SHOW DECRYPTING PROCESS CONFIGURATION;

开始反洗数

mysql> DECRYPT table t_encrypt;
Query OK, 0 rows affected (0.19 sec)

mysql> SHOW DECRYPTING LIST;
+---------------------------------------+-----------+-----------------+--------+---------------------+-----------+
| id                                    | tables    | job_split_count | active | create_time         | stop_time |
+---------------------------------------+-----------+-----------------+--------+---------------------+-----------+
| j53014fb26631db0f7149741422d86cc5dfe6 | t_encrypt | 1               | true   | 2022-10-20 19:56:20 | NULL      |
+---------------------------------------+-----------+-----------------+--------+---------------------+-----------+
1 row in set (0.06 sec)

mysql> SHOW DECRYPTING STATUS j53014fb26631db0f7149741422d86cc5dfe6;
+------+-------------+------------------------+--------+--------------------------------+---------------+
| item | data_source | status                 | active | decrypting_finished_percentage | error_message |
+------+-------------+------------------------+--------+--------------------------------+---------------+
| 0    | encrypt_db  | EXECUTE_INVENTORY_TASK | true   | 100                            |               |
+------+-------------+------------------------+--------+--------------------------------+---------------+
1 row in set (0.02 sec)

-- 提交反洗数任务

mysql> COMMIT DECRYPTING j53014fb26631db0f7149741422d86cc5dfe6;
Query OK, 0 rows affected (0.21 sec)

在 MySQL 数据库中验证数据

mysql> select * from t_encrypt;
+----+--------------------------+------------+--------------------------+----------+
| id | user_cipher              | user_plain | order_cipher             | order_id |
+----+--------------------------+------------+--------------------------+----------+
|  1 | DZEHT99l6UjthceKuCCKIw== | 123        | DZEHT99l6UjthceKuCCKIw== | 123      |
|  2 | Q3YzQboxcI5L5DSx9bSp9Q== | 234        | Q3YzQboxcI5L5DSx9bSp9Q== | 234      |
|  3 | pJOAtTsHiqrpPRv/cv8MnA== | 345        | pJOAtTsHiqrpPRv/cv8MnA== | 345      |
+----+--------------------------+------------+--------------------------+----------+
3 rows in set (0.00 sec)

操作至此，反洗数操作已完成。

重洗数 #

准备工作，在 MySQL 中创建名为 demo_ds_0的数据库

mysql> create datbase demo_ds_0;

在 SphereEx-DBPlusEngine 中创建逻辑库，注册数据源

mysql> create database encrypt_db;

mysql> use encrypt_db

mysql> REGISTER STORAGE UNIT ds_0 (
    URL="jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=True",
    USER="root",
    PASSWORD="123456",
    PROPERTIES("maximumPoolSize"="100","idleTimeout"="30000")
)

创建加密规则，建标并插入数据

CREATE ENCRYPT RULE t_encrypt (
COLUMNS(
(NAME=user_id,PLAIN=user_plain,CIPHER=user_cipher,ASSISTED_QUERY_COLUMN=user_assisted,LIKE_QUERY_COLUMN=user_like,ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='123456abc'))),ASSISTED_QUERY_ALGORITHM(TYPE(NAME='MD5')), LIKE_QUERY_ALGORITHM(TYPE(NAME='CHAR_DIGEST_LIKE'))),
(NAME=order_id, PLAIN=order_plain,CIPHER =order_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='654321abc'))))
), QUERY_WITH_CIPHER_COLUMN=true);

mysql> create table t_encrypt (
  id int primary key, 
  user_id varchar(200),
  rder_id varchar(200), 
  name varchar(200));

mysql> insert into t_encrypt values 
  (1,'a','aa','aaa'),
  (2,'b','bb','bbb'),
  (3,'c','cc','ccc');

在 MySQL 中清除明文数据


mysql> update t_encrypt set user_plain = null,order_plain =null;

mysql> select * from t_encrypt;
+----+--------------------------+----------------------------------+-----------+------------+--------------------------+-------------+------+
| id | user_cipher              | user_assisted                    | user_like | user_plain | order_cipher             | order_plain | name |
+----+--------------------------+----------------------------------+-----------+------------+--------------------------+-------------+------+
|  1 | FrpvlLvZyXewBRRnbeNnbA== | 0cc175b9c0f1b6a831c399e269772661 | `         | NULL       | 7xINTg7CqT86N1xDAHXgxg== | NULL        | aaa  |
|  2 | XWj53fUjwGnpP/LDiJWvow== | 92eb5ffee6ae2fec3ad71c777531578f | a         | NULL       | oTVrU2sLf+HWvYn9FJ04Zw== | NULL        | bbb  |
|  3 | MglbJfLRYx339IXZ3VHDVw== | 4a8a08f09d37b73795649038408b5f33 | d         | NULL       | zsz41oPgr06KG3WWrYixIg== | NULL        | ccc  |
+----+--------------------------+----------------------------------+-----------+------------+--------------------------+-------------+------+
3 rows in set (0.00 sec)

5. 更换密钥

```sql
mysql> REENCRYPT TABLE WITH RULE t_encrypt (
COLUMNS(
(NAME=user_id,PLAIN=user_plain,CIPHER=user_cipher,ASSISTED_QUERY_COLUMN=user_assisted,LIKE_QUERY_COLUMN=user_like,ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='665544aaa'))),ASSISTED_QUERY_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='523234abc'))), LIKE_QUERY_ALGORITHM(TYPE(NAME='CHAR_DIGEST_LIKE'))),
(NAME=order_id, PLAIN=order_plain,CIPHER =order_cipher, ENCRYPT_ALGORITHM(TYPE(NAME='AES',PROPERTIES('aes-key-value'='445533ccc'))))
), QUERY_WITH_CIPHER_COLUMN=true);

查看当前重洗数任务

mysql> SHOW REENCRYPTING LIST;

mysql> SHOW REENCRYPTING LIST;
+---------------------------------------+-----------+----------------+--------+---------------------+-----------+
| id                                    | tables    | job_item_count | active | create_time         | stop_time |
+---------------------------------------+-----------+----------------+--------+---------------------+-----------+
| j54014fb26631db0f7149741422d86cc5dfe6 | t_encrypt | 1              | true   | 2022-12-23 18:08:19 | NULL      |
+---------------------------------------+-----------+----------------+--------+---------------------+-----------+
1 row in set (0.21 sec)

查看重洗数任务状态

-- 当发现任务状态到达 SUSPEND 证明第一阶段任务结束（完成了反洗数以及切换到明文查询）
mysql> SHOW REENCRYPTING STATUS j54014fb26631db0f7149741422d86cc5dfe6;
+------+-------------+---------+--------+--------------------------------+---------------+
| item | data_source | status  | active | encrypting_finished_percentage | error_message |
+------+-------------+---------+--------+--------------------------------+---------------+
| 0    | encrypt_db  | SUSPEND | true   | 50                             |               |
+------+-------------+---------+--------+--------------------------------+---------------+
1 row in set (0.01 sec)

查看规则状态

SHOW ENCRYPT RULES;
-- 可以看到规则采用了明文查询
mysql> SHOW ENCRYPT RULES\G
*************************** 1. row ***************************
                   table: t_encrypt
            logic_column: user_id
         logic_data_type:
           cipher_column: user_cipher
        cipher_data_type:
            plain_column: user_plain
         plain_data_type:
   assisted_query_column: user_assisted
assisted_query_data_type:
       like_query_column: user_like
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=123456abc
     assisted_query_type: MD5
    assisted_query_props:
         like_query_type: CHAR_DIGEST_LIKE
        like_query_props:
query_with_cipher_column: false
*************************** 2. row ***************************
                   table: t_encrypt
            logic_column: order_id
         logic_data_type:
           cipher_column: order_cipher
        cipher_data_type:
            plain_column: order_plain
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=654321abc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
query_with_cipher_column: false
2 rows in set (0.00 sec)

确认明文数据

mysql> select * from t_encrypt;
+----+--------------------------+----------------------------------+-----------+------------+--------------------------+-------------+------+
| id | user_cipher              | user_assisted                    | user_like | user_plain | order_cipher             | order_plain | name |
+----+--------------------------+----------------------------------+-----------+------------+--------------------------+-------------+------+
|  1 | FrpvlLvZyXewBRRnbeNnbA== | 0cc175b9c0f1b6a831c399e269772661 | `         | a          | 7xINTg7CqT86N1xDAHXgxg== | aa          | aaa  |
|  2 | XWj53fUjwGnpP/LDiJWvow== | 92eb5ffee6ae2fec3ad71c777531578f | a         | b          | oTVrU2sLf+HWvYn9FJ04Zw== | bb          | bbb  |
|  3 | MglbJfLRYx339IXZ3VHDVw== | 4a8a08f09d37b73795649038408b5f33 | d         | c          | zsz41oPgr06KG3WWrYixIg== | cc          | ccc  |
+----+--------------------------+----------------------------------+-----------+------------+--------------------------+-------------+------+
3 rows in set (0.01 sec)

重洗数第二阶段

START REENCRYPTING j54014fb26631db0f7149741422d86cc5dfe6;

查看重洗数任务状态

-- 当发现任务状态到达 FINISHED 证明第二阶段任务结束（完成了换密钥，清理加密数据，洗数，切换至密文查询）

mysql> SHOW REENCRYPTING STATUS j54014fb26631db0f7149741422d86cc5dfe6;
+------+-------------+----------+--------+--------------------------------+---------------+
| item | data_source | status   | active | encrypting_finished_percentage | error_message |
+------+-------------+----------+--------+--------------------------------+---------------+
| 0    | encrypt_db  | FINISHED | true   | 100                            |               |
+------+-------------+----------+--------+--------------------------------+---------------+
1 row in set (0.00 sec)

查看当前规则

-- 发现密钥已经替换，并且使用加密列查询
mysql> show encrypt rules\G
*************************** 1. row ***************************
                   table: t_encrypt
            logic_column: user_id
         logic_data_type:
           cipher_column: user_cipher
        cipher_data_type:
            plain_column: user_plain
         plain_data_type:
   assisted_query_column: user_assisted
assisted_query_data_type:
       like_query_column: user_like
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=665544aaa
     assisted_query_type: AES
    assisted_query_props: aes-key-value=523234abc
         like_query_type: CHAR_DIGEST_LIKE
        like_query_props:
query_with_cipher_column: true
*************************** 2. row ***************************
                   table: t_encrypt
            logic_column: order_id
         logic_data_type:
           cipher_column: order_cipher
        cipher_data_type:
            plain_column: order_plain
         plain_data_type:
   assisted_query_column:
assisted_query_data_type:
       like_query_column:
    like_query_data_type:
          encryptor_type: AES
         encryptor_props: aes-key-value=445533ccc
     assisted_query_type:
    assisted_query_props:
         like_query_type:
        like_query_props:
query_with_cipher_column: true
2 rows in set (0.01 sec)

在 MySQL 中确认数据替换情况

mysql> select * from t_encrypt;
+----+--------------------------+--------------------------+-----------+------------+--------------------------+-------------+------+
| id | user_cipher              | user_assisted            | user_like | user_plain | order_cipher             | order_plain | name |
+----+--------------------------+--------------------------+-----------+------------+--------------------------+-------------+------+
|  1 | Ktmk9NUTHYXS+OApcRCg+A== | FMAjB5mOAmqotmfX7lQwhQ== | `         | a          | 5owposzhT/Zpk70GAQCBKw== | aa          | aaa  |
|  2 | 9MbqQaWIea72vZomWLoUMQ== | qiZJCKlQF7+/LzjoU9KYTQ== | a         | b          | aHp/Hywm/TkEMgnvjbOLOw== | bb          | bbb  |
|  3 | TLXTYnAbSCstVOz0Cv3iNg== | 3fGlHRInkQn+FcIzMUWS8Q== | d         | c          | 4PhsQk1WvNg1gbD5KfwPpw== | cc          | ccc  |
+----+--------------------------+--------------------------+-----------+------------+--------------------------+-------------+------+
3 rows in set (0.00 sec)

提交重洗数任务

mysql> COMMIT REENCRYPTING j54014fb26631db0f7149741422d86cc5dfe6;

FAQ #

洗数操作是否可以在线进行？对性能是否有影响？

可在线进行，SphereEx-DBPlusEngine 提供了线程配置方法，可尽量减少对业务的性能影响。同时，建议洗数据操作尽可能的在停机状态下操作，性能会更好。

如果洗数操作意外中断，是否可以继续洗数？

可以。可重新执行洗数命令继续操作。