Help Center> GaussDB> Centralized_3.x> SQL Reference> SQL Syntax> CREATE MASKING POLICY
Updated on 2024-05-07 GMT+08:00
View PDF

CREATE MASKING POLICY

Description

Creates a masking policy.

Precautions

Only users with the poladmin or sysadmin permission, or the initial user can perform this operation.

The masking policy takes effect only after the security policy is enabled, that is, enable_security_policy is set to on.

When you use database links to perform operations on remote objects, the client initiates a database link request. The actual sender is the server, and the attributes such as the IP address of the sender are the values of the server. For details, see DATABASE LINK.

Syntax

1
 
CREATE MASKING POLICY policy_name masking_clause[, ...] [ policy_filter_clause ] [ENABLE | DISABLE];
  • masking_clause 
    1
    		 
    masking_function ON LABEL(label_name[, ...])
  • masking_function

    maskall is not a preset function. It is hard-coded and cannot be displayed by running \df.

    The masking methods during presetting are as follows: 
    { maskall | randommasking | creditcardmasking | basicemailmasking | fullemailmasking | shufflemasking | alldigitsmasking | regexpmasking }
  • policy_filter_clause: 
    1
    		 
    FILTER ON { FILTER_TYPE ( filter_value [, ...] ) } [, ...]
  • FILTER_TYPE: 
    1
    		 
    IP | APP | ROLES

Parameters

  • policy_name

    Specifies the audit policy name, which must be unique.

    Value range: a string. It must comply with the naming convention.

  • label_name

    Specifies the resource label name.

  • masking_clause

    Specifies the masking function to be used to anonymize database resources labeled by label_name. schema.function can be used to specify the masking function.

  • policy_filter

    Specifies the users for which the masking policy takes effect. If this parameter is left empty, the masking policy takes effect for all users.

  • FILTER_TYPE

    Specifies the types of information to be filtered by the policy, including IP, APP, and ROLES.

  • filter_value

    Indicates the detailed information to be filtered, such as the IP address, app name, and username.

  • ENABLE|DISABLE

    Enables or disables the masking policy. If ENABLE|DISABLE is not specified, ENABLE is used by default.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
 
-- Create users dev_mask and bob_mask.
gaussdb=# CREATE USER dev_mask PASSWORD '********';
gaussdb=# CREATE USER bob_mask PASSWORD '********';

-- Create table tb_for_masking.
gaussdb=# CREATE TABLE tb_for_masking(idx int, col1 text, col2 text, col3 text, col4 text, col5 text, col6 text, col7 text,col8 text);
gaussdb=# INSERT INTO tb_for_masking VALUES(1, '9876543210', 'usr321usr', 'abc@huawei.com', 'abc@huawei.com', '1234-4567-7890-0123', 'abcdef 123456 ui 323 jsfd321 j3k2l3', '4880-9898-4545-2525', 'this is a llt case');
gaussdb=# INSERT INTO tb_for_masking VALUES(2, '0123456789', 'lltc123llt', 'abc@gmail.com', 'abc@gmail.com', '9876-5432-1012-3456', '1234 abcd ef 56 gh78ijk90lm', '4856-7654-1234-9865','this,is.a!LLT?case');
-- Create a resource label for sensitive columns.
gaussdb=# CREATE RESOURCE LABEL mask_lb1 ADD COLUMN(tb_for_masking.col1);
gaussdb=# CREATE RESOURCE LABEL mask_lb2 ADD COLUMN(tb_for_masking.col2);
gaussdb=# CREATE RESOURCE LABEL mask_lb3 ADD COLUMN(tb_for_masking.col3);
gaussdb=# CREATE RESOURCE LABEL mask_lb4 ADD COLUMN(tb_for_masking.col4);
gaussdb=# CREATE RESOURCE LABEL mask_lb5 ADD COLUMN(tb_for_masking.col5);
gaussdb=# CREATE RESOURCE LABEL mask_lb6 ADD COLUMN(tb_for_masking.col6);
gaussdb=# CREATE RESOURCE LABEL mask_lb7 ADD COLUMN(tb_for_masking.col7);
gaussdb=# CREATE RESOURCE LABEL mask_lb8 ADD COLUMN(tb_for_masking.col8);

-- Create a masking policy.
gaussdb=# CREATE MASKING POLICY maskpol1 maskall ON LABEL(mask_lb1);
gaussdb=# CREATE MASKING POLICY maskpol2 alldigitsmasking ON LABEL(mask_lb2);
gaussdb=# CREATE MASKING POLICY maskpol3 basicemailmasking ON LABEL(mask_lb3);
gaussdb=# CREATE MASKING POLICY maskpol4 fullemailmasking ON LABEL(mask_lb4);
gaussdb=# CREATE MASKING POLICY maskpol5 creditcardmasking ON LABEL(mask_lb5);
gaussdb=# CREATE MASKING POLICY maskpol6 shufflemasking ON LABEL(mask_lb6);
gaussdb=# CREATE MASKING POLICY maskpol7 regexpmasking('[\d+]','*',2, 9) ON LABEL(mask_lb7);

-- Create a masking policy that takes effect only for scenarios where user is dev_mask or bob_mask, the client tool is gsql, and IP address is 10.20.30.40 or 127.0.0.0/24.
gaussdb=# CREATE MASKING POLICY maskpol8 randommasking ON LABEL(mask_lb8) FILTER ON ROLES(dev_mask, bob_mask), APP(gsql), IP('10.20.30.40', '127.0.0.0/24');
-- Check whether the data masking policy takes effect.
gaussdb=# SELECT * FROM tb_for_masking;
 idx |    col1    |    col2    |      col3      |      col4      |        col5         |                col6                 |        col7         |        col8
-----+------------+------------+----------------+----------------+---------------------+-------------------------------------+---------------------+--------------------
   1 | xxxxxxxxxx | usr000usr  | xxx@huawei.com | xxx@xxxxxx.com | xxxx-xxxx-xxxx-0123 |   s 2iju1bcjk243df333d61l 22 53ef3a | 48**-****-*545-2525 | this is a llt case
   2 | xxxxxxxxxx | lltc000llt | xxx@gmail.com  | xxx@xxxxx.com  | xxxx-xxxx-xxxx-3456 | j 71fem0l286dbia543 g k9 ch         | 48**-****-*234-9865 | this,is.a!LLT?case
(2 rows)
-- Use the gsql tool, set the IP address to 10.20.30.40, and view tb_for_masking as user dev_mask.
gaussdb=# GRANT ALL PRIVILEGES TO dev_mask;
gaussdb=# GRANT ALL PRIVILEGES TO bob_mask;
gaussdb=# SET role dev_mask PASSWORD 'xxxxxxxxxx';
-- Use maskpol8 for data masking. The result is random and different each time.
gaussdb=# SELECT col8 FROM tb_for_masking;
        col8
--------------------
 9f1425b3835cc30d99
 9585b4ea8ea8ddcc5b
(2 rows)
gaussdb=# SET role bob_mask PASSWORD 'xxxxxxxxxx';
gaussdb=# SELECT col8 FROM tb_for_masking;
        col8
--------------------
 f29ef3a0769a1f417c
 806aa46409482d838f
(2 rows)

-- Delete a masking policy.
gaussdb=# DROP MASKING POLICY maskpol1, maskpol2, maskpol3, maskpol4, maskpol5, maskpol6, maskpol7, maskpol8;

-- Delete a resource label.
gaussdb=# DROP RESOURCE LABEL mask_lb1, mask_lb2, mask_lb3, mask_lb4, mask_lb5, mask_lb6, mask_lb7, mask_lb8;

-- Delete the tb_for_masking table.
gaussdb=# DROP TABLE tb_for_masking;

-- Delete the dev_mask and bob_mask users.
gaussdb=# DROP USER dev_mask, bob_mask;

Helpful Links

ALTER MASKING POLICY and DROP MASKING POLICY

Parent topic: SQL Syntax