MySQL企业版之数据脱敏功能

刺骨的言语ヽ痛彻心扉 2023-02-18 12:59 9阅读 0赞

> 利用企业级特性给敏感数据打码。

--------------------

1. 插件安装2. 数据打码插件应用2.1 隐藏重要数据2.2 生成随机数据并打码2.3 基于字典生成随机值2.4 其他要注意的地方3. 总结4. 延伸阅读  
MySQL企业版从8.0.13开始，新增一个插件叫做Data Masking and De-Identification，我将其简称为数据打码插件，其主要功能有：

*  将部分敏感数据转换成无害数据。这就可以在查询数据库中的敏感数据时以"\*\*\*"或"XXX"等方式打码显示，避免泄漏。
 *  生成随机数据，例如邮箱或银行卡号。例如在做功能测试时，我们可能需要生成一些随机数据，就用得上了。

# 1. 插件安装 #

数据打码插件名为 `data_masking`，安装成功后，会生成一系列UDF，部分UDF需要授予 `SUPER` 权限才行。  
执行下面的命令即可快速安装插件：

INSTALL PLUGIN data_masking SONAME 'data_masking.so';
    CREATE FUNCTION gen_blacklist RETURNS STRING SONAME 'data_masking.so';
    CREATE FUNCTION gen_dictionary RETURNS STRING SONAME 'data_masking.so';
    CREATE FUNCTION gen_dictionary_drop RETURNS STRING SONAME 'data_masking.so';
    CREATE FUNCTION gen_dictionary_load RETURNS STRING SONAME 'data_masking.so';
    CREATE FUNCTION gen_range RETURNS INTEGER SONAME 'data_masking.so';
    CREATE FUNCTION gen_rnd_email RETURNS STRING SONAME 'data_masking.so';
    CREATE FUNCTION gen_rnd_pan RETURNS STRING SONAME 'data_masking.so';
    CREATE FUNCTION gen_rnd_ssn RETURNS STRING SONAME 'data_masking.so';
    CREATE FUNCTION gen_rnd_us_phone RETURNS STRING SONAME 'data_masking.so';
    CREATE FUNCTION mask_inner RETURNS STRING SONAME 'data_masking.so';
    CREATE FUNCTION mask_outer RETURNS STRING SONAME 'data_masking.so';
    CREATE FUNCTION mask_pan RETURNS STRING SONAME 'data_masking.so';
    CREATE FUNCTION mask_pan_relaxed RETURNS STRING SONAME 'data_masking.so';
    CREATE FUNCTION mask_ssn RETURNS STRING SONAME 'data_masking.so';

执行下面的命令确认安装成功：

到这里，就表示安装成功了，可以愉快地给数据打码了。

# 2. 数据打码插件应用 #

## 2.1 隐藏重要数据 ##

这部分共有X个UDF，我们分别举例说明。

# mask_inner()函数，从第5个字符开始打码，直到最后3个字符，用"***"代替（默认用'X'打码）
    [root@yejr.run] [mysql]> SELECT mask_inner("MySQL is the world's most popular open source database", 5, 3, "*") as MySQL;
    +--------------------------------------------------------+
    | MySQL                                                  |
    +--------------------------------------------------------+
    | MySQL**********************************************ase |
    +--------------------------------------------------------+
    
    # mask_outer()函数，从头开始打码，直到第5个字符结束；再从尾部开始打码，直到倒数第三个字符结束
    [root@yejr.run] [mysql]> SELECT mask_outer("MySQL is the world's most popular open source database", 5, 3) AS MySQL;
    +--------------------------------------------------------+
    | MySQL                                                  |
    +--------------------------------------------------------+
    | XXXXX is the world's most popular open source databXXX |
    +--------------------------------------------------------+

## 2.2 生成随机数据并打码 ##

# 生成随机数字
    # 并用 gen_rnd_pan() 函数打码，只保留最后4位数，例如常用于只显示电话号码最后4位
    [root@yejr.run]> select gen_rnd_pan() as RAND_NUMBER, mask_pan(gen_rnd_pan()) as MASK_RAND_NUMBER;
    +------------------+------------------+
    | RAND_NUMBER      | MASK_RAND_NUMBER |
    +------------------+------------------+
    | 1936900392284608 | XXXXXXXXXXXX2155 |
    +------------------+------------------+
    
    # 也可以只打码中间部分数字
    [root@yejr.run]> select gen_rnd_pan() as RAND_NUMBER, mask_pan_relaxed(gen_rnd_pan()) as MASK_RAND_NUMBER;
    +------------------+------------------+
    | RAND_NUMBER      | MASK_RAND_NUMBER |
    +------------------+------------------+
    | 9868703631627362 | 426420XXXXXX3182 |
    +------------------+------------------+

其他几个生成随机数据的函数还有

*  gen\_rnd\_ssn()，生成美式社会安全码（身份证），例如 1234-5678-0123。相应地，可以用函数 mask\_ssn() 对其打码。
 *  gen\_range(1000, 2000)，在1000 - 2000之间产生一个随机数。
 *  gen\_rnd\_email()，生成一个随机邮件地址。
 *  gen\_rnd\_us\_phone()，生成美式电话号码，例如 1-555-016-7135。

## 2.3 基于字典生成随机值 ##

有时候，需要随机生成国家地区城市数据，就可以先造好这些字典，加载到数据库中，再从中随机抽取。  
编辑城市列表字典：

[root@yejr.run]# cat cn_cities.txt
    Beijing
    Shanghai
    Shenzhen
    Guangzhou
    Hangzhou
    Wuhan
    
    # 加载到MySQL中
    [root@yejr.run]> SELECT gen_dictionary_load('path/cn_cities.txt', 'CN_Cities');
    +--------------------------------------------------------+
    | gen_dictionary_load('path/cn_cities.txt', 'CN_Cities') |
    +--------------------------------------------------------+
    | Dictionary load success                                |
    +--------------------------------------------------------+
    
    # 从中随机抽取城市
    [root@yejr.run]> select gen_dictionary('CN_Cities') AS City;
    +----------+
    | City     |
    +----------+
    | Shenzhen |
    +----------+
    
    # 还可以配合 ELT() 函数从多个字典中随机抽取
    [root@yejr.run]> select gen_dictionary(ELT(gen_range(1,3), 'DE_Cities', 'US_Cities', 'CN_Cities')) AS City;
    +---------+
    | City    |
    +---------+
    | Beijing |
    +---------+

## 2.4 其他要注意的地方 ##

从MySQL 8.0.19开始，部分UDF函数默认使用latin1字符集，而在此之前默认使用binary字符集，使用过程中需要注意可能因此引发隐式类型转换导致索引不可用的风险，可以用 CONV() 函数进行转码。

# 3. 总结 #

数据打码插件是个非常不错的功能，除了上面描述的几个场景，还可以在用在 `SELECT ... INTO OUTFILE` 导出线上生产数据，然后再导入本地测试环境。或者直接在线上环境中，利用这些函数将数据脱敏处理后，写入专门的测试库，让测试程序直接读取，或者再利用主从复制同步到本地测试环境，可以玩出各种花样。

更多详情请参考手册内容。

最后亲切友情提醒：**MySQL企业版下载后只能试用一个月，试用完毕后记得删除卸载哟，土豪的话直接无脑付费即可哟**。

# 4. 延伸阅读 #

*  MySQL Enterprise Data Masking and De-Identification，https://dev.mysql.com/doc/refman/8.0/en/data-masking.html

enjoy MySQL :)

全文完。

--------------------

*  由叶老师主讲的知数堂「MySQL优化课」课程早已升级到MySQL 8.0版本了，现在上车刚刚好，扫码开启MySQL 8.0的修行之旅吧。
    
    ![format_png][]
    
    --------------------
    
    另外，叶老师在腾讯课堂《**MySQL性能优化**》精编版第一期已完结，本课程讲解读几个MySQL性能优化的核心要素：**合理利用索引，降低锁影响，提高事务并发度**。
    
    下面是自动拼团的二维码直接享受组团价
    
    ![format_png 1][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2pwZy9udHM1Mm5IaGVUeEx5ajlRdWJEY0hQeUlwemgwaWNXSVpnTm96aFo3TmxDbW53MkU1ekpqUzZTTjE0TENaR0JvUW04WGliVHkzZDFtaWFqYWlhQVhTcTFTVVEvNjQw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9udHM1Mm5IaGVUek5xdmliY3IwZmJDQkU3MXBoMzF5aWJ5TzNFOVJONnlLd0NYd1R6cVZmc1ZxM2RxUUFpYWRoUDlGSkVXOUluNlk0Mnl4ZTVUUWVuMm9BUS82NDA?x-oss-process=image/format,png