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

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 |
+------+--------------+------------+

备注

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