**GBase 8a Mpp之数据脱敏
脱敏功能
Gbase8a MPP支持默认脱敏、随机脱敏、自定义脱敏、哈希脱敏和指定位置脱敏五种数据脱敏函数。动态数据脱敏不会真正改动表中存储的实际数据，只是在查询的时候应用该特性控制查询返回的数据，动态数据脱敏支持默认脱敏 default、随机脱敏 random、自定义脱敏partial 、哈希脱敏 sha 和指定位置脱敏 keymask 五种数据脱敏函数。
脱敏语法
MASKED WITH(FUNCTION = ‘TYPE (参数)’)
动态数据脱敏支持四种类型数据脱敏函数，如下：
1)默认脱敏default类型。这种类型没有参数。
MASKED WITH(FUNCTION = ‘DEFAULT。’)
2)随机脱敏random类型。
random(min,max)有两个参数标定随机范围，参数min的最小值为1,参数 max的最大值为1。。。
MASKED WITH(FUNCTION = ‘RANDOMd, 100)’)
3)自定义脱敏partial类型。
这种类型包含三个参数，partial (prefix, padding, suffix),参数详细
说明如下：
• prefix表示前缀保留显示字符数量；
• padding表示脱敏显示字符；
• suffix表示结尾保留显示字符数量。
MASKED WITH(FUNCTION = ‘PARTIAL(1,‘XXXX’,1)’)
4)哈希脱敏sha类型。
这个类型没有参数。
MASKED WITH(FUNCTION = ‘SHA()’)
5)自定义脱敏keymask类型。
keymask(substr,padding, pos)
masked with(function=’ keymask(〃@gbase〃，〃****〃，0)’)
参考示例：
创建脱敏用户并赋予数据库权限
gbase> create user user1;
Query OK, 0 rows affected (Elapsed: 00:00:00.02)
gbase> grant all on *.*.* to user1;
Query OK, 0 rows affected (Elapsed: 00:00:00.02)
gbase> revoke super,unmask on *.*.* from user1;
Query OK, 0 rows affected (Elapsed: 00:00:00.02)
默认脱敏
gbase> CREATE TABLE t_m_default(name VARCHAR(10) MASKED
-> WITH(FUNCTION = ‘DEFAULT()’),b_date DATETIME MASKED
-> WITH(FUNCTION = ‘DEFAULT()’),age INT MASKED WITH(FUNCTION =‘DEFAULT()’));
Query OK, 0 rows affected (Elapsed: 00:00:00.90)
gbase> INSERT INTO t_m_default VALUES(‘Jieke’,‘1949-11-16 12:31:24.123000’,99);
Query OK, 1 row affected (Elapsed: 00:00:00.46)
gbase> SELECT * FROM t_m_default;
±------±---------------------------±-----+
| name | b_date | age |
±------±---------------------------±-----+
| Jieke | 1949-11-16 12:31:24.123000 | 99 |
±------±---------------------------±-----+
1 row in set (Elapsed: 00:00:00.08)
[gbase@node15 ~]$ gccli -uuser1;
GBase client 9.5.3.28.12d6ab4d. Copyright © 2004-2022, GBase. All Rights Reserved.
gbase> use vc vc1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> use test;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
切换脱敏用户查看脱敏后效果
gbase> SELECT * FROM t_m_default;
±-----±--------------------±-----+
| name | b_date | age |
±-----±--------------------±-----+
| xxxx | 1900-01-01 00:00:00 | 0 |
±-----±--------------------±-----+
1 row in set (Elapsed: 00:00:00.02)
随机脱敏
gbase> CREATE TABLE t_m_random(age INT MASKED
-> WITH(FUNCTION = ‘RANDOM(1,4)’));
Query OK, 0 rows affected (Elapsed: 00:00:01.42)
gbase> INSERT INTO t_m_random VALUES(29),(19);
Query OK, 2 rows affected (Elapsed: 00:00:00.56)
Records: 2 Duplicates: 0 Warnings: 0
gbase> SELECT * FROM t_m_random;
±-----+
| age |
±-----+
| 29 |
| 19 |
±-----+
2 rows in set (Elapsed: 00:00:00.32)
[gbase@node15 ~]$ gccli -uuser1;
GBase client 9.5.3.28.12d6ab4d. Copyright © 2004-2022, GBase. All Rights Reserved.
gbase> use vc vc1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> use test;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
切换脱敏用户查看脱敏后效果
gbase> SELECT * FROM t_m_random;
±-----+
| age |
±-----+
| 2 |
| 4 |
±-----+
2 rows in set (Elapsed: 00:00:00.10)
自定义脱敏
gbase> CREATE TABLE t_m_partial(context VARCHAR(255) MASKED
-> WITH(FUNCTION = ‘PARTIAL(3,“XXXX”,6)’));
Query OK, 0 rows affected (Elapsed: 00:00:01.12)
gbase> INSERT INTO t_m_partial VALUES(‘Construction in progress’),(‘happy’);
Query OK, 2 rows affected (Elapsed: 00:00:00.61)
Records: 2 Duplicates: 0 Warnings: 0
gbase> SELECT * FROM t_m_partial;
±-------------------------+
| context |
±-------------------------+
| Construction in progress |
| happy |
±-------------------------+
2 rows in set (Elapsed: 00:00:00.01)
[gbase@node15 ~]$ gccli -uuser1;
GBase client 9.5.3.28.12d6ab4d. Copyright © 2004-2022, GBase. All Rights Reserved.
gbase> use vc vc1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> use test;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
切换脱敏用户查看脱敏后效果
gbase> SELECT * FROM t_m_partial;
±--------------+
| context |
±--------------+
| ConXXXXogress |
| XXXX |
±--------------+
2 row in set (Elapsed: 00:00:00.02)
SHA脱敏
gbase> CREATE TABLE t_m_sha(context VARCHAR(255) MASKED WITH(FUNCTION =‘SHA()’));
Query OK, 0 rows affected (Elapsed: 00:00:00.91)
gbase> INSERT INTO t_m_sha VALUES(‘abc’);
Query OK, 1 row affected (Elapsed: 00:00:00.44)
gbase> SELECT * FROM t_m_sha;
±--------+
| context |
±--------+
| abc |
±--------+
1 row in set (Elapsed: 00:00:00.02)
切换脱敏用户查看脱敏后效果
gbase> SELECT * FROM t_m_sha;
±-----------------------------------------+
| context |
±-----------------------------------------+
| a9993e364706816aba3e25717850c26c9cd0d89d |
±-----------------------------------------+
1 row in set (Elapsed: 00:00:00.02)
