南大通用GBase 8a脱敏功能使用样例

发表于 作者 laozizhu

GBase 8a数据库集群，提供了脱敏功能，支持多种对数据查询结果加密算法来实现敏感数据的屏蔽。本文介绍GBase 8a脱敏功能使用样例。

参考

对于底层磁盘数据文件级的加密，请参考 GBase 8a透明存储加密encrypt功能使用

创建脱敏属性语法

MASKED WITH(FUNCTION = ‘TYPE(参数)’)
TYPE有四种脱敏类型DEFAULT、RANDOM、PARTIAL、SHA、keymask

类型支持的类型语法样例
default所有MASKED WITH(FUNCTION = ‘DEFAULT()’)
random数字类型MASKED WITH(FUNCTION = ‘RANDOM(1,100)’)
partial字符类型masked with(function='Partial(1,"$",1)')
sha字符类型masked with(function='sha()')
keymask字符类型masked with(function='keymask("con","#",0)')

DEFAULT 类型

没有参数，是针对基本类型的数据列进行脱敏的，为默认脱敏函数。
设置方式：MASKED WITH(FUNCTION = ‘DEFAULT()’)
在该种模式下：

  1. 若数据类型包含 date、datetime 和 time：
     date 会以“1900-01-01”显示；
     datetime 会以“1900-01-01 00:00:00”显示；
     time 会以“00:00:00”显示。
  2. 若数据类型是整型、浮点型和 decimal：
     整型和浮点型会显示 0；
     decimal 会显示为 0.000…, 带有结果小数位（定义的类型或者评估
    的类型）个数 0。
  3. 若数据类型是字符串类型的：
    将会替换为固定4个X字符“XXXX”。
  4. NULL 值：
    不做脱敏处理，显示为NULL。
  5. Sql 函数：
    如果任一参数含有脱敏属性，则按照结果类型，执行默认脱敏。如果只参与条件部分，结果部分不包含脱敏列，则不脱敏，比如case when 的条件部分。

default数据类型范围

所有类型。

default样例

如下是默认脱敏的输出例子。其中函数部分，是先按照原始数据处理函数，只是在最后返回结果时，按照函数返回类型，根据脱敏规则进行。关于脱敏用户权限设置，请看后面章节。

create table testMask(
    id int                  masked with(function='default()'), 
    name varchar(100)       masked with(function='default()'), 
    birth date              masked with(function='default()'),
    salary decimal(16,3)    masked with(function='default()'),
    luckynumber bigint      masked with(function='default()')
);

insert into testmask values
    (1,'First','2001-01-01',1111.111,111111111111),
    (2,'Second','2002-02-02',2222.222,222222222222),
    (3,'张三','2003-03-03',3333.333,333333333333),
    (4,'李四','2004-04-04',4444.444,444444444444),
    (5,'大刀王五','2005-05-05',5555.555,555555555555);



gbase> create table testMask(
    ->     id int                  masked with(function='default()'),
    ->     name varchar(100)       masked with(function='default()'),
    ->     birth date              masked with(function='default()'),
    ->     salary decimal(16,3)    masked with(function='default()'),
    ->     luckynumber bigint      masked with(function='default()')
    -> );
Query OK, 0 rows affected (Elapsed: 00:00:00.63)

gbase> insert into testmask values
    ->     (1,'First','2001-01-01',1111.111,111111111111),
    ->     (2,'Second','2002-02-02',2222.222,222222222222),
    ->     (3,'张三','2003-03-03',3333.333,333333333333),
    ->     (4,'李四','2004-04-04',4444.444,444444444444),
    ->     (5,'大刀王五','2005-05-05',5555.555,555555555555);
Query OK, 5 rows affected (Elapsed: 00:00:00.49)
Records: 5  Duplicates: 0  Warnings: 0
gbase> select * from testmask;
+------+--------------+------------+----------+--------------+
| id   | name         | birth      | salary   | luckynumber  |
+------+--------------+------------+----------+--------------+
|    1 | First        | 2001-01-01 | 1111.111 | 111111111111 |
|    2 | Second       | 2002-02-02 | 2222.222 | 222222222222 |
|    3 | 张三         | 2003-03-03 | 3333.333 | 333333333333 |
|    4 | 李四         | 2004-04-04 | 4444.444 | 444444444444 |
|    5 | 大刀王五     | 2005-05-05 | 5555.555 | 555555555555 |
+------+--------------+------------+----------+--------------+
5 rows in set (Elapsed: 00:00:00.01)

gbase>

[gbase@rh6-1 ~]$ gccli -umasked -pmasked -e"select * from testdb.testmask"
+------+------+------------+--------+-------------+
| id   | name | birth      | salary | luckynumber |
+------+------+------------+--------+-------------+
|    0 | xxxx | 1900-01-01 |  0.000 |           0 |
|    0 | xxxx | 1900-01-01 |  0.000 |           0 |
|    0 | xxxx | 1900-01-01 |  0.000 |           0 |
|    0 | xxxx | 1900-01-01 |  0.000 |           0 |
|    0 | xxxx | 1900-01-01 |  0.000 |           0 |
+------+------+------------+--------+-------------+
[gbase@rh6-1 ~]$

[gbase@rh6-1 ~]$ gccli -umasked -pmasked -e"select substr(name,0,2) from testdb.testmask"
+------------------+
| substr(name,0,2) |
+------------------+
| xx               |
| xx               |
| xx               |
| xx               |
| xx               |
+------------------+
[gbase@rh6-1 ~]$ gccli -umasked -pmasked -e"select substr(name,0,2) from testdb.testmask where substr(name,0,2)='Fi'"
+------------------+
| substr(name,0,2) |
+------------------+
| xx               |
+------------------+

RANDOM 类型

RANDOM(min,max)有两个参数标定随机范围，最小值和最大值随机脱敏函数只对数字类型起作用，它将会随机显示某一个范围内的值，并多次执行，但同一行的随机值会不同。

 对 NULL 值不做处理，依然显示为 NULL。
 min 数值必须小于 max

设置方式：MASKED WITH(FUNCTION = ‘RANDOM(XXX,YYYYYY)’)

random数据类型范围

Random只对数字类型(int系列,decimal）有效，对字符串，日期等会报错。

gbase> create table testMaskRandom(
    ->     id int                  masked with(function='random(1,10)'),
    ->     name varchar(100)       masked with(function='random(20,30)'),
    ->     birth date              masked with(function='random(40,50)'),
    ->     salary decimal(16,3)    masked with(function='random(60,70)'),
    ->     luckynumber bigint      masked with(function='random(80,90)')
    -> );
ERROR 1210 (HY000): Incorrect arguments to The data type of column 'name' does not support data masking function 'random'.
gbase>

random样例

random每次执行结果都不一样。

不影响查询条件和函数计算等，脱敏只对最终返回的结果有效。


gbase> create table testMaskRandom(
    ->     id int                  masked with(function='random(1,10)'),
    ->     name varchar(100)       ,
    ->     birth date              ,
    ->     salary decimal(16,3)    masked with(function='random(60,70)'),
    ->     luckynumber bigint      masked with(function='random(80,90)')
    -> );
Query OK, 0 rows affected (Elapsed: 00:00:00.60)

gbase> insert into testMaskRandom values
    ->     (1,'First','2001-01-01',1111.111,111111111111),
    ->     (2,'Second','2002-02-02',2222.222,222222222222),
    ->     (3,'张三','2003-03-03',3333.333,333333333333),
    ->     (4,'李四','2004-04-04',4444.444,444444444444),
    ->     (5,'大刀王五','2005-05-05',5555.555,555555555555);
Query OK, 5 rows affected (Elapsed: 00:00:00.58)
Records: 5  Duplicates: 0  Warnings: 0
[gbase@rh6-1 ~]$ gccli -umasked -pmasked -e"select * from testdb.testmaskrandom"
+------+--------------+------------+--------+-------------+
| id   | name         | birth      | salary | luckynumber |
+------+--------------+------------+--------+-------------+
|    6 | First        | 2001-01-01 | 66.254 |          84 |
|    4 | Second       | 2002-02-02 | 63.583 |          88 |
|    8 | 张三         | 2003-03-03 | 67.809 |          84 |
|    8 | 李四         | 2004-04-04 | 67.991 |          85 |
|    4 | 大刀王五     | 2005-05-05 | 68.429 |          83 |
+------+--------------+------------+--------+-------------+
[gbase@rh6-1 ~]$ gccli -umasked -pmasked -e"select * from testdb.testmaskrandom"
+------+--------------+------------+--------+-------------+
| id   | name         | birth      | salary | luckynumber |
+------+--------------+------------+--------+-------------+
|    5 | First        | 2001-01-01 | 68.080 |          88 |
|    6 | Second       | 2002-02-02 | 61.481 |          82 |
|    8 | 张三         | 2003-03-03 | 60.335 |          89 |
|    5 | 李四         | 2004-04-04 | 66.832 |          90 |
|    8 | 大刀王五     | 2005-05-05 | 67.511 |          86 |
+------+--------------+------------+--------+-------------+
[gbase@rh6-1 ~]$ gccli -umasked -pmasked -e"select * from testdb.testmaskrandom where salary=1111.111"
+------+-------+------------+--------+-------------+
| id   | name  | birth      | salary | luckynumber |
+------+-------+------------+--------+-------------+
|    1 | First | 2001-01-01 | 60.464 |          81 |
+------+-------+------------+--------+-------------+

PARTIAL 自定义类型

PARTIAL(prefix,padding,suffix)，有三个参数：

prefix 表示前缀保留显示字符数量；
padding 表示脱敏显示字符；
suffix 表示结尾保留显示字符数量。

如果实际内容长度小于等于prefix+suffix+length(padding)长度，则直接显示 padding 的字符内容。

 对 NULL 不做脱敏处理，显示为 NULL；
 prefix 和 suffix 为大于等于 0 的整数

设置方式：MASKED WITH(FUNCTION = ‘PARTIAL(1,”XXXX”,1)’)

partial数据类型范围

支持字符串，对数字、日期类型，不支持。

gbase> create table testMaskPartial(
    ->     id int                  masked with(function='Partial(1,"#",1)'),
    ->     name varchar(100)       masked with(function='Partial(1,"$",1)'),
    ->     birth date              masked with(function='Partial(1,"%",1)'),
    ->     salary decimal(16,3)    masked with(function='Partial(1,"&",1)'),
    ->     luckynumber bigint      masked with(function='Partial(1,"*",1)'),
    -> );
ERROR 1210 (HY000): Incorrect arguments to The data type of column 'id' does not support data masking function 'partial'.
gbase> create table testMaskPartial(
    ->     id int                  ,
    ->     name varchar(100)       masked with(function='Partial(1,"$",1)'),
    ->     birth date              masked with(function='Partial(1,"%",1)'),
    ->     salary decimal(16,3)    ,
    ->     luckynumber bigint
    -> );
ERROR 1210 (HY000): Incorrect arguments to The data type of column 'birth' does not support data masking function 'partial'.
gbase>

partial样例

gbase> create table testMaskPartial(
    ->     id int                  ,
    ->     name varchar(100)       masked with(function='Partial(1,"$",1)'),
    ->     birth date              ,
    ->     salary decimal(16,3)    ,
    ->     luckynumber bigint
    -> );
Query OK, 0 rows affected (Elapsed: 00:00:00.80)

gbase> insert into testMaskPartial values
    ->     (1,'First','2001-01-01',1111.111,111111111111),
    ->     (2,'Second','2002-02-02',2222.222,222222222222),
    ->     (3,'张三','2003-03-03',3333.333,333333333333),
    ->     (4,'李四','2004-04-04',4444.444,444444444444),
    ->     (5,'大刀王五','2005-05-05',5555.555,555555555555);
Query OK, 5 rows affected (Elapsed: 00:00:00.40)
Records: 5  Duplicates: 0  Warnings: 0

gbase> select * from testMaskPartial;
+------+--------------+------------+----------+--------------+
| id   | name         | birth      | salary   | luckynumber  |
+------+--------------+------------+----------+--------------+
|    1 | First        | 2001-01-01 | 1111.111 | 111111111111 |
|    2 | Second       | 2002-02-02 | 2222.222 | 222222222222 |
|    3 | 张三         | 2003-03-03 | 3333.333 | 333333333333 |
|    4 | 李四         | 2004-04-04 | 4444.444 | 444444444444 |
|    5 | 大刀王五     | 2005-05-05 | 5555.555 | 555555555555 |
+------+--------------+------------+----------+--------------+
5 rows in set (Elapsed: 00:00:00.01)

gbase>
[gbase@rh6-1 ~]$ gccli -umasked -pmasked -e"select * from testdb.testmaskpartial"
+------+---------+------------+----------+--------------+
| id   | name    | birth      | salary   | luckynumber  |
+------+---------+------------+----------+--------------+
|    1 | F$t     | 2001-01-01 | 1111.111 | 111111111111 |
|    2 | S$d     | 2002-02-02 | 2222.222 | 222222222222 |
|    3 | $       | 2003-03-03 | 3333.333 | 333333333333 |
|    4 | $       | 2004-04-04 | 4444.444 | 444444444444 |
|    5 | 大$五   | 2005-05-05 | 5555.555 | 555555555555 |
+------+---------+------------+----------+--------------+
[gbase@rh6-1 ~]$ gccli -umasked -pmasked -e"select * from testdb.testmaskpartial where name='First'"
+------+------+------------+----------+--------------+
| id   | name | birth      | salary   | luckynumber  |
+------+------+------------+----------+--------------+
|    1 | F$t  | 2001-01-01 | 1111.111 | 111111111111 |
+------+------+------------+----------+--------------+

SHA 类型

做SHA加密计算。

对 NULL 不做处理，显示为 NULL。

设置方式

MASKED WITH(FUNCTION = ‘SHA()’)

sha数据类型范围

字符串类型。其它类型报错。

gbase> create table testMaskSHA(
    ->     id int                  masked with(function='sha()'),
    ->     name varchar(100)       masked with(function='sha()'),
    ->     birth date              masked with(function='sha()'),
    ->     salary decimal(16,3)    masked with(function='sha()'),
    ->     luckynumber bigint      masked with(function='sha()')
    -> );
ERROR 1210 (HY000): Incorrect arguments to The data type of column 'id' does not support data masking function 'sha'.
gbase> create table testMaskSHA(
    ->     id int                  ,
    ->     name varchar(100)       masked with(function='sha()'),
    ->     birth date              masked with(function='sha()'),
    ->     salary decimal(16,3)    masked with(function='sha()'),
    ->     luckynumber bigint      masked with(function='sha()')
    -> );
ERROR 1210 (HY000): Incorrect arguments to The data type of column 'birth' does not support data masking function 'sha'.
gbase>

sha样例

gbase> create table testMaskSHA(
    ->     id int                  ,
    ->     name varchar(100)       masked with(function='sha()'),
    ->     birth date              ,
    ->     salary decimal(16,3)    ,
    ->     luckynumber bigint
    -> );
Query OK, 0 rows affected (Elapsed: 00:00:01.00)

gbase> insert into testMaskSHA values
    ->     (1,'First','2001-01-01',1111.111,111111111111),
    ->     (2,'Second','2002-02-02',2222.222,222222222222),
    ->     (3,'张三','2003-03-03',3333.333,333333333333),
    ->     (4,'李四','2004-04-04',4444.444,444444444444),
    ->     (5,'大刀王五','2005-05-05',5555.555,555555555555);
Query OK, 5 rows affected (Elapsed: 00:00:00.35)
Records: 5  Duplicates: 0  Warnings: 0

gbase> select * from testMaskSHA;
+------+--------------+------------+----------+--------------+
| id   | name         | birth      | salary   | luckynumber  |
+------+--------------+------------+----------+--------------+
|    1 | First        | 2001-01-01 | 1111.111 | 111111111111 |
|    2 | Second       | 2002-02-02 | 2222.222 | 222222222222 |
|    3 | 张三         | 2003-03-03 | 3333.333 | 333333333333 |
|    4 | 李四         | 2004-04-04 | 4444.444 | 444444444444 |
|    5 | 大刀王五     | 2005-05-05 | 5555.555 | 555555555555 |
+------+--------------+------------+----------+--------------+
5 rows in set (Elapsed: 00:00:00.02)

gbase> ^CAborted
[gbase@rh6-1 ~]$ gccli -umasked -pmasked -e"select * from testdb.testmasksha"
+------+------------------------------------------+------------+----------+--------------+
| id   | name                                     | birth      | salary   | luckynumber  |
+------+------------------------------------------+------------+----------+--------------+
|    1 | 916a78d701ded328cd66da58a97ef8cd28a99e84 | 2001-01-01 | 1111.111 | 111111111111 |
|    2 | 2a4bcae654f264daf22579fb462bf0bae302a057 | 2002-02-02 | 2222.222 | 222222222222 |
|    3 | ced07fb42b05a2ed9efa330250e2bb9175f962ce | 2003-03-03 | 3333.333 | 333333333333 |
|    4 | b7b286678338a2092572733e23236485d166c0df | 2004-04-04 | 4444.444 | 444444444444 |
|    5 | 393b73dba6e95c7487be4e175dc1d37ace4a5480 | 2005-05-05 | 5555.555 | 555555555555 |
+------+------------------------------------------+------------+----------+--------------+
[gbase@rh6-1 ~]$ gccli -umasked -pmasked -e"select * from testdb.testmasksha where name='First'"
+------+------------------------------------------+------------+----------+--------------+
| id   | name                                     | birth      | salary   | luckynumber  |
+------+------------------------------------------+------------+----------+--------------+
|    1 | 916a78d701ded328cd66da58a97ef8cd28a99e84 | 2001-01-01 | 1111.111 | 111111111111 |
+------+------------------------------------------+------------+----------+--------------+
[gbase@rh6-1 ~]$

自定义脱敏函数

keymask(substr,padding,pos)

功能：指定字符为初始计数位置，指定位数内脱敏功能。

keymask数据类型范围

keymask脱敏函数只用于varchar/char列进行脱敏，其他类型列使用该函数将报错返回。

substr：要找到的字符串
padding：替换成的字符
pos: 0/1 覆盖方向，0表示向前覆盖，1表示向后覆盖

kekeymask样例

如下例子中，Second字符里，先找到con的位置，然后按照pos=0向前替换所有字符为padding的字符#,结果就是#cond


gbase> create table testMaskKeymask(
    ->     id int                  ,
    ->     name varchar(100)       masked with(function='keymask("con","#",0)'),
    ->     birth date              ,
    ->     salary decimal(16,3)    ,
    ->     luckynumber bigint
    -> );
Query OK, 0 rows affected (Elapsed: 00:00:00.87)

gbase> insert into testMaskKeymask values
    ->     (1,'First','2001-01-01',1111.111,111111111111),
    ->     (2,'Second','2002-02-02',2222.222,222222222222),
    ->     (3,'张三','2003-03-03',3333.333,333333333333),
    ->     (4,'李四','2004-04-04',4444.444,444444444444),
    ->     (5,'大刀王五','2005-05-05',5555.555,555555555555);
Query OK, 5 rows affected (Elapsed: 00:00:00.37)
Records: 5  Duplicates: 0  Warnings: 0

gbase> select * from testMaskKeymask;
+------+--------------+------------+----------+--------------+
| id   | name         | birth      | salary   | luckynumber  |
+------+--------------+------------+----------+--------------+
|    1 | First        | 2001-01-01 | 1111.111 | 111111111111 |
|    2 | Second       | 2002-02-02 | 2222.222 | 222222222222 |
|    3 | 张三         | 2003-03-03 | 3333.333 | 333333333333 |
|    4 | 李四         | 2004-04-04 | 4444.444 | 444444444444 |
|    5 | 大刀王五     | 2005-05-05 | 5555.555 | 555555555555 |
+------+--------------+------------+----------+--------------+
5 rows in set (Elapsed: 00:00:00.00)

gbase> ^CAborted
[gbase@rh6-1 ~]$ gccli -umasked -pmasked -e"select * from testdb.testmaskkeymask where name='Second'"
+------+-------+------------+----------+--------------+
| id   | name  | birth      | salary   | luckynumber  |
+------+-------+------------+----------+--------------+
|    2 | #cond | 2002-02-02 | 2222.222 | 222222222222 |
+------+-------+------------+----------+--------------+
[gbase@rh6-1 ~]$ gccli -umasked -pmasked -e"select * from testdb.testmaskkeymask"
+------+--------------+------------+----------+--------------+
| id   | name         | birth      | salary   | luckynumber  |
+------+--------------+------------+----------+--------------+
|    1 | First        | 2001-01-01 | 1111.111 | 111111111111 |
|    2 | #cond        | 2002-02-02 | 2222.222 | 222222222222 |
|    3 | 张三         | 2003-03-03 | 3333.333 | 333333333333 |
|    4 | 李四         | 2004-04-04 | 4444.444 | 444444444444 |
|    5 | 大刀王五     | 2005-05-05 | 5555.555 | 555555555555 |
+------+--------------+------------+----------+--------------+
[gbase@rh6-1 ~]$

修改脱敏属性

支持创建表时直接指定脱敏列，也可以后期随时修改脱敏算法。如果以前没有，则时增加了脱敏属性。

alter table TABLENAME alter 列名 masked with 新的属性。

gbase> alter table testdb.testmasksha alter salary masked with(function='default()');
Query OK, 0 rows affected (Elapsed: 00:00:00.57)

gbase> select * from testMaskSHA;
+------+--------------+------------+----------+--------------+
| id   | name         | birth      | salary   | luckynumber  |
+------+--------------+------------+----------+--------------+
|    1 | First        | 2001-01-01 | 1111.111 | 111111111111 |
|    2 | Second       | 2002-02-02 | 2222.222 | 222222222222 |
|    3 | 张三         | 2003-03-03 | 3333.333 | 333333333333 |
|    4 | 李四         | 2004-04-04 | 4444.444 | 444444444444 |
|    5 | 大刀王五     | 2005-05-05 | 5555.555 | 555555555555 |
+------+--------------+------------+----------+--------------+
5 rows in set (Elapsed: 00:00:00.02)

gbase> ^CAborted
[gbase@rh6-1 ~]$ gccli -umasked -pmasked -e"select * from testdb.testmasksha where name='First'"
+------+------------------------------------------+------------+--------+--------------+
| id   | name                                     | birth      | salary | luckynumber  |
+------+------------------------------------------+------------+--------+--------------+
|    1 | 916a78d701ded328cd66da58a97ef8cd28a99e84 | 2001-01-01 |  0.000 | 111111111111 |
+------+------------------------------------------+------------+--------+--------------+
[gbase@rh6-1 ~]$

删除脱敏属性

语法如下：

alter table TABLENAME alter 列名 drop masked

[gbase@rh6-1 ~]$ gccli testdb

GBase client 8.6.2.43-R28.122445. Copyright (c) 2004-2020, GBase.  All Rights Reserved.

gbase> alter table testMaskSHA alter salary drop masked;
Query OK, 0 rows affected (Elapsed: 00:00:00.58)

gbase> ^CAborted
[gbase@rh6-1 ~]$ gccli -umasked -pmasked -e"select * from testdb.testmaskSHA"
+------+------------------------------------------+------------+----------+--------------+
| id   | name                                     | birth      | salary   | luckynumber  |
+------+------------------------------------------+------------+----------+--------------+
|    1 | 916a78d701ded328cd66da58a97ef8cd28a99e84 | 2001-01-01 | 1111.111 | 111111111111 |
|    2 | 2a4bcae654f264daf22579fb462bf0bae302a057 | 2002-02-02 | 2222.222 | 222222222222 |
|    3 | ced07fb42b05a2ed9efa330250e2bb9175f962ce | 2003-03-03 | 3333.333 | 333333333333 |
|    4 | b7b286678338a2092572733e23236485d166c0df | 2004-04-04 | 4444.444 | 444444444444 |
|    5 | 393b73dba6e95c7487be4e175dc1d37ace4a5480 | 2005-05-05 | 5555.555 | 555555555555 |
+------+------------------------------------------+------------+----------+--------------+
[gbase@rh6-1 ~]$ gccli testdb

GBase client 8.6.2.43-R28.122445. Copyright (c) 2004-2020, GBase.  All Rights Reserved.

gbase> alter table testMaskSHA alter name drop masked;
Query OK, 0 rows affected (Elapsed: 00:00:00.30)

gbase> ^CAborted
[gbase@rh6-1 ~]$ gccli -umasked -pmasked -e"select * from testdb.testmaskSHA"
+------+--------------+------------+----------+--------------+
| id   | name         | birth      | salary   | luckynumber  |
+------+--------------+------------+----------+--------------+
|    1 | First        | 2001-01-01 | 1111.111 | 111111111111 |
|    2 | Second       | 2002-02-02 | 2222.222 | 222222222222 |
|    3 | 张三         | 2003-03-03 | 3333.333 | 333333333333 |
|    4 | 李四         | 2004-04-04 | 4444.444 | 444444444444 |
|    5 | 大刀王五     | 2005-05-05 | 5555.555 | 555555555555 |
+------+--------------+------------+----------+--------------+
[gbase@rh6-1 ~]$

函数或计算表达式

只要函数或表达式中，有脱敏列，那么将按照default规则进行，数字变成1，字符串变成XXXX,日期变成1970-01-01

CREATE TABLE "testmaskrandom" (
  "id" int(11) DEFAULT NULL MASKED WITH(FUNCTION='RANDOM(1,10)'),
  "name" varchar(100) DEFAULT NULL,
  "birth" date DEFAULT NULL MASKED WITH(FUNCTION='DEFAULT()'),
  "salary" decimal(16,3) DEFAULT NULL MASKED WITH(FUNCTION='RANDOM(60,70)'),
  "luckynumber" bigint(20) DEFAULT NULL MASKED WITH(FUNCTION='RANDOM(80,90)')
) 

数据和前面的列子一样。

gbase> select id,id+1,sin(id),concat(id,'ABC'),birth,date_add(birth,interval 1 day) birth from testmaskrandom;
+------+------+---------+------------------+------------+------------+
| id   | id+1 | sin(id) | concat(id,'ABC') | birth      | birth      |
+------+------+---------+------------------+------------+------------+
|    4 |    0 |       0 | xxxx             | 1900-01-01 | 1900-01-01 |
|    2 |    0 |       0 | xxxx             | 1900-01-01 | 1900-01-01 |
|    5 |    0 |       0 | xxxx             | 1900-01-01 | 1900-01-01 |
|    8 |    0 |       0 | xxxx             | 1900-01-01 | 1900-01-01 |
|    5 |    0 |       0 | xxxx             | 1900-01-01 | 1900-01-01 |
+------+------+---------+------------------+------------+------------+
5 rows in set (Elapsed: 00:00:00.02)

脱敏列出现在表达式条件部分，不会导致脱敏。

gbase> select id,case when id>=3 then date_add(now(),interval 10 day) else now() end birth from testmaskrandom;
+------+---------------------+
| id   | birth               |
+------+---------------------+
|    9 | 2020-12-08 09:08:21 |
|   10 | 2020-12-08 09:08:21 |
|    3 | 2020-12-18 09:08:21 |
|    3 | 2020-12-18 09:08:21 |
|    7 | 2020-12-18 09:08:21 |
+------+---------------------+
5 rows in set (Elapsed: 00:00:00.00)

嵌套查询

如果嵌套内的表达式，只要结果里面包含了脱敏类，无论条件是否命中，那么返回结果的对应列，就会被脱敏，按照默认规则。 

gbase> select id,id+1,sin(id),concat(id,'ABC'),birth from (select id,case when id>=3 then birth else now() end birth from testmaskrandom)t;
+------+------+---------+------------------+---------------------+
| id   | id+1 | sin(id) | concat(id,'ABC') | birth               |
+------+------+---------+------------------+---------------------+
|    2 |    0 |       0 | xxxx             | 1900-01-01 00:00:00 |
|    3 |    0 |       0 | xxxx             | 1900-01-01 00:00:00 |
|    9 |    0 |       0 | xxxx             | 1900-01-01 00:00:00 |
|    9 |    0 |       0 | xxxx             | 1900-01-01 00:00:00 |
|    5 |    0 |       0 | xxxx             | 1900-01-01 00:00:00 |
+------+------+---------+------------------+---------------------+
5 rows in set (Elapsed: 00:00:00.00)

如下例子将id>3000,不可能命中，但依然被脱敏，因为case when的结果部分有birth。

gbase> select id,id+1,sin(id),concat(id,'ABC'),birth from (select id,case when id>=3000 then birth else now() end birth from testmaskrandom)t;
+------+------+---------+------------------+---------------------+
| id   | id+1 | sin(id) | concat(id,'ABC') | birth               |
+------+------+---------+------------------+---------------------+
|    9 |    0 |       0 | xxxx             | 1900-01-01 00:00:00 |
|    2 |    0 |       0 | xxxx             | 1900-01-01 00:00:00 |
|    7 |    0 |       0 | xxxx             | 1900-01-01 00:00:00 |
|    4 |    0 |       0 | xxxx             | 1900-01-01 00:00:00 |
|    4 |    0 |       0 | xxxx             | 1900-01-01 00:00:00 |
+------+------+---------+------------------+---------------------+
5 rows in set (Elapsed: 00:00:00.00)

如果只出现在嵌套查询的条件部分，则不影响。如下id只出现在case when的条件部分，而结果部分没有脱敏字段，结果则是【不脱敏】的。

gbase> select id,id+1,sin(id),concat(id,'ABC'),birth from (select id,case when id>=3 then date_add(now(),interval 10 day) else now() end birth from testmaskrandom)t;
+------+------+---------+------------------+---------------------+
| id   | id+1 | sin(id) | concat(id,'ABC') | birth               |
+------+------+---------+------------------+---------------------+
|    7 |    0 |       0 | xxxx             | 2020-12-08 09:06:58 |
|    7 |    0 |       0 | xxxx             | 2020-12-08 09:06:58 |
|    1 |    0 |       0 | xxxx             | 2020-12-18 09:06:58 |
|    3 |    0 |       0 | xxxx             | 2020-12-18 09:06:58 |
|    2 |    0 |       0 | xxxx             | 2020-12-18 09:06:58 |
+------+------+---------+------------------+---------------------+
5 rows in set (Elapsed: 00:00:00.02)

脱敏权限

默认没有unmask的用户，都只能看到脱敏后的数据。

管理员用户root和gbase不受此限制。默认有所有权限。

gbase> create user masked identified by 'masked';
Query OK, 0 rows affected (Elapsed: 00:00:00.02)
gbase> grant all on testdb.* to masked;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
[gbase@rh6-1 ~]$ gccli -umasked -pmasked

GBase client 8.6.2.43-R28.122445. Copyright (c) 2004-2020, GBase.  All Rights Reserved.

gbase> use testdb;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> select * from testmask;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | F#st | 2001-01-01 |
|    2 | S#nd | 2002-02-02 |
|    3 | #    | 2003-03-03 |
|    4 | #    | 2004-04-04 |
+------+------+------------+
4 rows in set (Elapsed: 00:00:00.04)

脱敏权限时unmask, 其默认不包含在*里面，比如testdb.*, 必须要单独授权，且当前版本只支持*.*, 不支持库级和表级的授权。

gbase> create user unmasked identified by 'unmasked';
Query OK, 0 rows affected (Elapsed: 00:00:00.03)

gbase> grant all on testdb.* to unmasked;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> ^CAborted
[gbase@rh6-1 ~]$ gccli -uunmasked -punmasked -e"select * from testdb.testmask"
+------+---------+------------+
| id   | name    | birth      |
+------+---------+------------+
|    1 | F#t     | 2001-01-01 |
|    2 | S#d     | 2002-02-02 |
|    3 | #       | 2003-03-03 |
|    4 | #       | 2004-04-04 |
|    5 | 大#五   | 2005-05-05 |
+------+---------+------------+
[gbase@rh6-1 ~]$ gccli testdb

GBase client 8.6.2.43-R28.122445. Copyright (c) 2004-2020, GBase.  All Rights Reserved.

gbase> grant unmask on testdb.* to unmasked;
ERROR 1758 (HY000): gcluster dal error: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES.
gbase> grant unmask on *.* to unmasked;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

gbase> ^CAborted
[gbase@rh6-1 ~]$ gccli -uunmasked -punmasked -e"select * from testdb.testmask"
+------+--------------+------------+
| id   | name         | birth      |
+------+--------------+------------+
|    1 | First        | 2001-01-01 |
|    2 | Second       | 2002-02-02 |
|    3 | 张三         | 2003-03-03 |
|    4 | 李四         | 2004-04-04 |
|    5 | 大刀王五     | 2005-05-05 |
+------+--------------+------------+

备注

脱敏一般和加密一起使用，请参考

GBase 8a透明存储加密encrypt功能使用
Post Views: 6,508

相关文章:

  1. 南大通用GBase 8a通过aes_encrypt和aes_decrypt进行AES算法加密和解密
  2. 南大通用GBase 8a透明存储加密encrypt功能使用
  3. 南大通用GBase 8a用户角色role、用户组的管理
  4. 南大通用GBase 8a对用户权限的控制方法，grant的使用
  5. 南大通用GBase 8a支持sha256的sftp加载性能对比sha1下降情况
  6. 南大通用GBase 8a 对明文密码的安全保护措施
  7. 南大通用GBase 8a窗口函数row_number的使用样例
  8. 南大通用GBase 8a运维管理用自定义存储过程汇总
  9. 南大通用GBase 8a版本(V8,V9)升级操作手顺
  10. 数据库SQL面试题：查询学生张三没有选修的课程号有哪些