适用范围
MogDB 所有版本
问题概述
某客户反馈，有这样的场景需求，生产环境会定期数据覆盖到测试环境，但一般按照监管要求，生产数据是不能直接用户开发跟测试的，需要对敏感字进行脱敏。
于是，对于这场景，针对mogdb做动态脱敏解决方案测试。
解决方案
一、准备环境
一套mogdb环境，版本MogDB 2.1.1，一主一备，假设是生产环境。
1.建库和模式
MogDB=# create database gs_school;
CREATE DATABASE
MogDB=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
gs_school | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
| | | | | omm=CTc/omm
(4 rows)
MogDB=# \c gs_school;
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "gs_school" as user "omm".
gs_school=# create schema gs_school;
CREATE SCHEMA
gs_school=# \dn
List of schemas
Name | Owner
----------------+-------
blockchain | omm
cstore | omm
db4ai | omm
dbe_perf | omm
dbe_pldebugger | omm
gs_school | omm
pkg_service | omm
public | omm
snapshot | omm
sqladvisor | omm
(10 rows)
gs_school=# SET search_path to gs_school;
SET
2.建表和数据
gs_school=# create table student(id int,name varchar(100),idcard varchar(18),tel varchar(11),email varchar(100));
CREATE TABLE
gs_school=# \d+ student
Table "gs_school.student"
Column | Type | Modifiers | Storage | Stats target | Description
--------+------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
name | character varying(100) | | extended | |
idcard | character varying(18) | | extended | |
tel | character varying(11) | | extended | |
email | character varying(100) | | extended | |
Has OIDs: no
Options: orientation=row, compression=no
字段idcard：身份证号
字段tel：手机号
字段email:邮箱
gs_school=# insert into student values(1,'李一','111111111111111111','11111111111','liyi@enmotech.com');
INSERT 0 1
gs_school=# insert into student values(2,'王二','222222222222222222','22222222222','wanger@enmotech.com');
INSERT 0 1
gs_school=# insert into student values(3,'张三','333333333333333333','33333333333','zhangsan@enmotech.com');
INSERT 0 1
gs_school=# insert into student values(4,'陈四','444444444444444444','44444444444','chensi@enmotech.com');
INSERT 0 1
gs_school=# insert into student values(5,'吴五','555555555555555555','55555555555','wuwu@enmotech.com');
INSERT 0 1
gs_school=# insert into student values(6,'吴五','555555555555555555','55555555555','wuwu@enmotech.com');
INSERT 0 1
gs_school=# select * from student;
id | name | idcard | tel | email
----+------+--------------------+-------------+-----------------------
1 | 李一 | 111111111111111111 | 11111111111 | liyi@enmotech.com
2 | 王二 | 222222222222222222 | 22222222222 | wanger@enmotech.com
3 | 张三 | 333333333333333333 | 33333333333 | zhangsan@enmotech.com
4 | 陈四 | 444444444444444444 | 44444444444 | chensi@enmotech.com
5 | 吴五 | 555555555555555555 | 55555555555 | wuwu@enmotech.com
(5 rows)
3.建用户和授权
gs_school=# create user hc identified by '1234@abc';
CREATE ROLE
gs_school=# GRANT ALL PRIVILEGES TO hc;
ALTER ROLE
gs_school=# alter schema gs_school owner to hc;
ALTER SCHEMA
二、脱敏处理
1.定义脱敏资源标签
语法格式
gs_school=# create resource label label_student_idcard add column(gs_school.student.idcard);
CREATE RESOURCE LABEL
gs_school=# create resource label label_student_tel add column(gs_school.student.tel);
CREATE RESOURCE LABEL
gs_school=# create resource label label_student_email add column(gs_school.student.email);
CREATE RESOURCE LABEL
2.定义脱敏策略
语法格式
gs_school=# create masking policy msk_idcard creditcardmasking on label(label_student_idcard) filter on roles(hc);
CREATE MASKING POLICY
gs_school=# create masking policy msk_tel maskall on label(label_student_tel) filter on roles(hc);
CREATE MASKING POLICY
gs_school=# create masking policy msk_email fullemailmasking on label(label_student_email) filter on roles(hc);
CREATE MASKING POLICY
gs_school=# select * from GS_MASKING;
polname | polenabled | maskaction | labelname | masking_object | filter_name
------------+------------+-------------------+----------------------+---------------------------------+--------------
msk_email | t | fullemailmasking | label_student_email | column:gs_school.student.email | roles[24581]
msk_idcard | t | creditcardmasking | label_student_idcard | column:gs_school.student.idcard | roles[24581]
msk_tel | t | maskall | label_student_tel | column:gs_school.student.tel | roles[24581]
(3 rows)
3.开启参数生效
需要打开enable_security_policy开关，脱敏策略才可以生效。
$ gs_guc reload -I all -c "enable_security_policy=on"
gs_school=# show enable_security_policy;
enable_security_policy
------------------------
on
(1 row)
4.特定用户访问数据
指定用户hc访问资源动态脱敏
$ gsql -U hc -W"1234@abc" -d gs_school -p 26000
gs_school=> select * from gs_school.student;
id | name | idcard | tel | email
----+------+--------------------+-------------+-----------------------
1 | 李一 | xxxxxxxxxxxxxx1111 | xxxxxxxxxxx | xxxx@xxxxxxxx.com
2 | 王二 | xxxxxxxxxxxxxx2222 | xxxxxxxxxxx | xxxxxx@xxxxxxxx.com
3 | 张三 | xxxxxxxxxxxxxx3333 | xxxxxxxxxxx | xxxxxxxx@xxxxxxxx.com
4 | 陈四 | xxxxxxxxxxxxxx4444 | xxxxxxxxxxx | xxxxxx@xxxxxxxx.com
5 | 吴五 | xxxxxxxxxxxxxx5555 | xxxxxxxxxxx | xxxx@xxxxxxxx.com
(5 rows)
三、导出数据
用hc用户导出数据
$ gs_dump -U hc -W"1234@abc" -f gs_school.dump -p 26000 gs_school -Fc
四、导入数据
在测试环境导入生产数据
1.创建库
MogDB=# create database gs_school;
CREATE DATABASE
2.导入数据
$ gs_restore -d gs_school gs_school.dump -p 26000
3.访问数据
gs_school=# select * from gs_school.student;
id | name | idcard | tel | email
----+------+--------------------+-------------+-----------------------
1 | 李一 | xxxxxxxxxxxxxx1111 | xxxxxxxxxxx | xxxx@xxxxxxxx.com
2 | 王二 | xxxxxxxxxxxxxx2222 | xxxxxxxxxxx | xxxxxx@xxxxxxxx.com
3 | 张三 | xxxxxxxxxxxxxx3333 | xxxxxxxxxxx | xxxxxxxx@xxxxxxxx.com
4 | 陈四 | xxxxxxxxxxxxxx4444 | xxxxxxxxxxx | xxxxxx@xxxxxxxx.com
5 | 吴五 | xxxxxxxxxxxxxx5555 | xxxxxxxxxxx | xxxx@xxxxxxxx.com
(5 rows)
测试环境导入生产环境的数据，查询已经是经过脱敏后的数据。
参考文档
https://docs.mogdb.io/zh/mogdb/v3.0/overview
适用范围
MogDB 所有版本
问题概述
某客户反馈，有这样的场景需求，生产环境会定期数据覆盖到测试环境，但一般按照监管要求，生产数据是不能直接用户开发跟测试的，需要对敏感字进行脱敏。
于是，对于这场景，针对mogdb做动态脱敏解决方案测试。
解决方案
一、准备环境
一套mogdb环境，版本MogDB 2.1.1，一主一备，假设是生产环境。
1.建库和模式
MogDB=# create database gs_school;
CREATE DATABASE
MogDB=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
gs_school | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
| | | | | omm=CTc/omm
template1 | omm | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/omm +
| | | | | omm=CTc/omm
(4 rows)
MogDB=# \c gs_school;
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "gs_school" as user "omm".
gs_school=# create schema gs_school;
CREATE SCHEMA
gs_school=# \dn
List of schemas
Name | Owner
----------------+-------
blockchain | omm
cstore | omm
db4ai | omm
dbe_perf | omm
dbe_pldebugger | omm
gs_school | omm
pkg_service | omm
public | omm
snapshot | omm
sqladvisor | omm
(10 rows)
gs_school=# SET search_path to gs_school;
SET
2.建表和数据
gs_school=# create table student(id int,name varchar(100),idcard varchar(18),tel varchar(11),email varchar(100));
CREATE TABLE
gs_school=# \d+ student
Table "gs_school.student"
Column | Type | Modifiers | Storage | Stats target | Description
--------+------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
name | character varying(100) | | extended | |
idcard | character varying(18) | | extended | |
tel | character varying(11) | | extended | |
email | character varying(100) | | extended | |
Has OIDs: no
Options: orientation=row, compression=no
字段idcard：身份证号
字段tel：手机号
字段email:邮箱
gs_school=# insert into student values(1,'李一','111111111111111111','11111111111','liyi@enmotech.com');
INSERT 0 1
gs_school=# insert into student values(2,'王二','222222222222222222','22222222222','wanger@enmotech.com');
INSERT 0 1
gs_school=# insert into student values(3,'张三','333333333333333333','33333333333','zhangsan@enmotech.com');
INSERT 0 1
gs_school=# insert into student values(4,'陈四','444444444444444444','44444444444','chensi@enmotech.com');
INSERT 0 1
gs_school=# insert into student values(5,'吴五','555555555555555555','55555555555','wuwu@enmotech.com');
INSERT 0 1
gs_school=# insert into student values(6,'吴五','555555555555555555','55555555555','wuwu@enmotech.com');
INSERT 0 1
gs_school=# select * from student;
id | name | idcard | tel | email
----+------+--------------------+-------------+-----------------------
1 | 李一 | 111111111111111111 | 11111111111 | liyi@enmotech.com
2 | 王二 | 222222222222222222 | 22222222222 | wanger@enmotech.com
3 | 张三 | 333333333333333333 | 33333333333 | zhangsan@enmotech.com
4 | 陈四 | 444444444444444444 | 44444444444 | chensi@enmotech.com
5 | 吴五 | 555555555555555555 | 55555555555 | wuwu@enmotech.com
(5 rows)
3.建用户和授权
gs_school=# create user hc identified by '1234@abc';
CREATE ROLE
gs_school=# GRANT ALL PRIVILEGES TO hc;
ALTER ROLE
gs_school=# alter schema gs_school owner to hc;
ALTER SCHEMA
二、脱敏处理
1.定义脱敏资源标签
语法格式
gs_school=# create resource label label_student_idcard add column(gs_school.student.idcard);
CREATE RESOURCE LABEL
gs_school=# create resource label label_student_tel add column(gs_school.student.tel);
CREATE RESOURCE LABEL
gs_school=# create resource label label_student_email add column(gs_school.student.email);
CREATE RESOURCE LABEL
2.定义脱敏策略
语法格式
gs_school=# create masking policy msk_idcard creditcardmasking on label(label_student_idcard) filter on roles(hc);
CREATE MASKING POLICY
gs_school=# create masking policy msk_tel maskall on label(label_student_tel) filter on roles(hc);
CREATE MASKING POLICY
gs_school=# create masking policy msk_email fullemailmasking on label(label_student_email) filter on roles(hc);
CREATE MASKING POLICY
gs_school=# select * from GS_MASKING;
polname | polenabled | maskaction | labelname | masking_object | filter_name
------------+------------+-------------------+----------------------+---------------------------------+--------------
msk_email | t | fullemailmasking | label_student_email | column:gs_school.student.email | roles[24581]
msk_idcard | t | creditcardmasking | label_student_idcard | column:gs_school.student.idcard | roles[24581]
msk_tel | t | maskall | label_student_tel | column:gs_school.student.tel | roles[24581]
(3 rows)
3.开启参数生效
需要打开enable_security_policy开关，脱敏策略才可以生效。
$ gs_guc reload -I all -c "enable_security_policy=on"
gs_school=# show enable_security_policy;
enable_security_policy
------------------------
on
(1 row)
4.特定用户访问数据
指定用户hc访问资源动态脱敏
$ gsql -U hc -W"1234@abc" -d gs_school -p 26000
gs_school=> select * from gs_school.student;
id | name | idcard | tel | email
----+------+--------------------+-------------+-----------------------
1 | 李一 | xxxxxxxxxxxxxx1111 | xxxxxxxxxxx | xxxx@xxxxxxxx.com
2 | 王二 | xxxxxxxxxxxxxx2222 | xxxxxxxxxxx | xxxxxx@xxxxxxxx.com
3 | 张三 | xxxxxxxxxxxxxx3333 | xxxxxxxxxxx | xxxxxxxx@xxxxxxxx.com
4 | 陈四 | xxxxxxxxxxxxxx4444 | xxxxxxxxxxx | xxxxxx@xxxxxxxx.com
5 | 吴五 | xxxxxxxxxxxxxx5555 | xxxxxxxxxxx | xxxx@xxxxxxxx.com
(5 rows)
三、导出数据
用hc用户导出数据
$ gs_dump -U hc -W"1234@abc" -f gs_school.dump -p 26000 gs_school -Fc
四、导入数据
在测试环境导入生产数据
1.创建库
MogDB=# create database gs_school;
CREATE DATABASE
2.导入数据
$ gs_restore -d gs_school gs_school.dump -p 26000
3.访问数据
gs_school=# select * from gs_school.student;
id | name | idcard | tel | email
----+------+--------------------+-------------+-----------------------
1 | 李一 | xxxxxxxxxxxxxx1111 | xxxxxxxxxxx | xxxx@xxxxxxxx.com
2 | 王二 | xxxxxxxxxxxxxx2222 | xxxxxxxxxxx | xxxxxx@xxxxxxxx.com
3 | 张三 | xxxxxxxxxxxxxx3333 | xxxxxxxxxxx | xxxxxxxx@xxxxxxxx.com
4 | 陈四 | xxxxxxxxxxxxxx4444 | xxxxxxxxxxx | xxxxxx@xxxxxxxx.com
5 | 吴五 | xxxxxxxxxxxxxx5555 | xxxxxxxxxxx | xxxx@xxxxxxxx.com
(5 rows)
测试环境导入生产环境的数据，查询已经是经过脱敏后的数据。
参考文档
https://docs.mogdb.io/zh/mogdb/v3.0/overview
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
