数据仓库服务 GAUSSDB(DWS)-GaussDB(DWS)数据脱敏:示例

时间:2024-11-28 17:37:20

示例

以员工表emp,表的属主alice及角色matu、july为例,简要介绍数据脱敏过程。其中,表emp包含员工的姓名、手机号、邮箱、银行卡号、薪资等隐私数据。

  1. 使用管理员用户连接数据库后,创建角色alice、matu和july。

    1
    2
    3
    CREATE ROLE alice PASSWORD '{Password}';
    CREATE ROLE matu PASSWORD '{Password}';
    CREATE ROLE july PASSWORD '{Password}';
    

  2. 赋予alice、matu和july当前数据库的模式权限。

    1
    GRANT ALL PRIVILEGES on schema public to alice,matu,july;
    

  3. 切换至角色alice,创建表emp并插入三条员工信息。

    1
    2
    3
    4
    5
    6
    7
    SET ROLE alice PASSWORD '{Password}';
    
    CREATE TABLE emp(id int, name varchar(20), phone_no varchar(11), card_no number, card_string varchar(19), email text, salary numeric(100, 4), birthday date);
    
    INSERT INTO emp VALUES(1, 'anny', '13420002340', 1234123412341234, '1234-1234-1234-1234', 'smithWu@163.com', 10000.00, '1999-10-02');
    INSERT INTO emp VALUES(2, 'bob', '18299023211', 3456345634563456, '3456-3456-3456-3456', '66allen_mm@qq.com', 9999.99, '1989-12-12');
    INSERT INTO emp VALUES(3, 'cici', '15512231233', NULL, NULL, 'jonesishere@sina.com', NULL, '1992-11-06');
    

  4. alice将表emp的读取权限授予matu、july。

    1
    GRANT SELECT ON emp TO matu, july;
    

  5. 创建脱敏策略mask_emp,仅alice可查看员工所有信息,matu和july对员工银行卡号和薪资数据不可见。字段card_no是数值类型,采用MASK_FULL全脱敏成固定值0;字段card_string是字符类型,采用MASK_PARTIAL按指定的输入输出格式对原始数据进行部分脱敏;字段salary是数值类型,采用MASK_PARTIAL指定数字9部分脱敏倒数第二位前的所有数位值。

    1
    2
    3
    4
    CREATE REDACTION POLICY mask_emp ON emp WHEN (current_user IN ('matu', 'july'))
     ADD COLUMN card_no WITH mask_full(card_no),
     ADD COLUMN card_string WITH mask_partial(card_string, 'VVVVFVVVVFVVVVFVVVV','VVVV-VVVV-VVVV-VVVV','#',1,12),
     ADD COLUMN salary WITH mask_partial(salary, '9', 1, length(salary) - 2);
    

  6. 切换到matu和july,查看员工表emp。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    SET ROLE matu PASSWORD '{Password}';
    SELECT * FROM emp;
     id | name |  phone_no   | card_no |     card_string     |        email         |   salary   |      birthday       
    ----+------+-------------+---------+---------------------+----------------------+------------+---------------------
      1 | anny | 13420002340 |       0 | ####-####-####-1234 | smithWu@163.com      | 99999.9990 | 1999-10-02 00:00:00
      2 | bob  | 18299023211 |       0 | ####-####-####-3456 | 66allen_mm@qq.com    |  9999.9990 | 1989-12-12 00:00:00
      3 | cici | 15512231233 |         |                     | jonesishere@sina.com |            | 1992-11-06 00:00:00
    (3 rows)
    
    SET ROLE july PASSWORD '{Password}';
    SELECT * FROM emp;
     id | name |  phone_no   | card_no |     card_string     |        email         |   salary   |      birthday       
    ----+------+-------------+---------+---------------------+----------------------+------------+---------------------
      1 | anny | 13420002340 |       0 | ####-####-####-1234 | smithWu@163.com      | 99999.9990 | 1999-10-02 00:00:00
      2 | bob  | 18299023211 |       0 | ####-####-####-3456 | 66allen_mm@qq.com    |  9999.9990 | 1989-12-12 00:00:00
      3 | cici | 15512231233 |         |                     | jonesishere@sina.com |            | 1992-11-06 00:00:00
    (3 rows)
    

  7. 若需要matu也有员工所有信息的查看权限,只有july不可见,修改策略生效范围即可。

    1
    2
    SET ROLE alice PASSWORD '{Password}';
    ALTER REDACTION POLICY mask_emp ON emp WHEN(current_user = 'july');
    

  8. 切换到matu和july,重新查看员工表emp。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    SET ROLE matu PASSWORD '{Password}';
    SELECT * FROM emp;
     id | name |  phone_no   |     card_no      |     card_string     |        email         |   salary   |      birthday       
    ----+------+-------------+------------------+---------------------+----------------------+------------+---------------------
      1 | anny | 13420002340 | 1234123412341234 | 1234-1234-1234-1234 | smithWu@163.com      | 10000.0000 | 1999-10-02 00:00:00
      2 | bob  | 18299023211 | 3456345634563456 | 3456-3456-3456-3456 | 66allen_mm@qq.com    |  9999.9900 | 1989-12-12 00:00:00
      3 | cici | 15512231233 |                  |                     | jonesishere@sina.com |            | 1992-11-06 00:00:00
    (3 rows)
    
    SET ROLE july PASSWORD '{Password}';
    SELECT * FROM emp;
     id | name |  phone_no   | card_no |     card_string     |        email         |   salary   |      birthday       
    ----+------+-------------+---------+---------------------+----------------------+------------+---------------------
      1 | anny | 13420002340 |       0 | ####-####-####-1234 | smithWu@163.com      | 99999.9990 | 1999-10-02 00:00:00
      2 | bob  | 18299023211 |       0 | ####-####-####-3456 | 66allen_mm@qq.com    |  9999.9990 | 1989-12-12 00:00:00
      3 | cici | 15512231233 |         |                     | jonesishere@sina.com |            | 1992-11-06 00:00:00
    (3 rows)
    

  9. 员工信息phone_no、email和birthday也是隐私数据,更新脱敏策略mask_emp,新增三个脱敏列。

    1
    2
    3
    4
    SET ROLE alice PASSWORD '{Password}';
    ALTER REDACTION POLICY mask_emp ON emp ADD COLUMN phone_no WITH mask_partial(phone_no, '*', 4);
    ALTER REDACTION POLICY mask_emp ON emp ADD COLUMN email WITH mask_partial(email, '*', 1, position('@' in email));
    ALTER REDACTION POLICY mask_emp ON emp ADD COLUMN birthday WITH mask_full(birthday);
    

  10. 切换到july,查看表emp数据。

    1
    2
    3
    4
    5
    6
    7
    8
    SET ROLE july PASSWORD '{Password}';
    SELECT * FROM emp;
     id | name |  phone_no   | card_no |     card_string     |        email         |   salary   |      birthday       
    ----+------+-------------+---------+---------------------+----------------------+------------+---------------------
      1 | anny | 134******** |       0 | ####-####-####-1234 | ********163.com      | 99999.9990 | 1970-01-01 00:00:00
      2 | bob  | 182******** |       0 | ####-####-####-3456 | ***********qq.com    |  9999.9990 | 1970-01-01 00:00:00
      3 | cici | 155******** |         |                     | ************sina.com |            | 1970-01-01 00:00:00
    (3 rows)
    

  11. 通过视图redaction_policies和redaction_columns查看当前脱敏策略mask_emp的详细信息。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    SELECT * FROM redaction_policies;
     object_schema | object_owner | object_name | policy_name |            expression             | enable | policy_description | inherited 
    ---------------+--------------+-------------+-------------+-----------------------------------+--------+--------------------+-----------
     public        | alice        | emp         | mask_emp    | ("current_user"() = 'july'::name) | t      |                    | f       
    (1 row)
    
    SELECT object_name, column_name, function_info FROM redaction_columns;
     object_name | column_name |                                             function_info                                             
    -------------+-------------+-------------------------------------------------------------------------------------------------------
     emp         | card_no     | mask_full(card_no)
     emp         | card_string | mask_partial(card_string, 'VVVVFVVVVFVVVVFVVVV'::text, 'VVVV-VVVV-VVVV-VVVV'::text, '#'::text, 1, 12)
     emp         | email       | mask_partial(email, '*'::text, 1, "position"(email, '@'::text))
     emp         | salary      | mask_partial(salary, '9'::text, 1, (length((salary)::text) - 2))
     emp         | birthday    | mask_full(birthday)
     emp         | phone_no    | mask_partial(phone_no, '*'::text, 4)
    (6 rows)
    

  12. 新增一列salary_info,若需要将文本类型的薪资信息统一脱敏成“*.*”,可以创建自定义脱敏函数实现。此处采用PL/PGSQL语言定义脱敏函数mask_regexp_salary,创建脱敏列时,只需自定义脱敏的函数名和参数列表,详细内容可参考 GaussDB (DWS)用户自定义函数。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    SET ROLE alice PASSWORD '{Password}';
    
    ALTER TABLE emp ADD COLUMN salary_info TEXT;
    UPDATE emp SET salary_info = salary::text;
    
    CREATE FUNCTION mask_regexp_salary(salary_info text) RETURNS text AS
    $$
     SELECT regexp_replace($1, '[0-9]+','*','g');
    $$
     LANGUAGE SQL 
    STRICT SHIPPABLE;
    
    ALTER REDACTION POLICY mask_emp ON emp ADD COLUMN salary_info WITH mask_regexp_salary(salary_info);
    
    SET ROLE july PASSWORD '{Password}';
    SELECT id, name, salary_info FROM emp;
     id | name | salary_info 
    ----+------+-------------
      1 | anny | *.*
      2 | bob  | *.*
      3 | cici | 
    (3 rows)
    

  13. 无需为表emp设置敏感策略,删除脱敏策略mask_emp。

    1
    2
    SET ROLE alice PASSWORD '{Password}';
    DROP REDACTION POLICY mask_emp ON emp;
    

support.huaweicloud.com/devg-dws/dws_04_0062.html