Help Center> GaussDB> Distributed_8.x> SQL Reference> SQL Syntax> C> CREATE MASKING POLICY

Updated on 2024-06-03 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

    
         CREATE MASKING POLICY policy_name masking_clause[, ...] [ policy_filter_clause ] [ENABLE | DISABLE];

masking_clause

      
           masking_function ON LABEL(label_name[, ...])

masking_function
maskall is not a preset function 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:

     
          FILTER ON { FILTER_TYPE ( filter_value [, ... ] ) } [, ...]

FILTER_TYPE:
1

{ APP | ROLES | IP }

Parameters

policy_name
Specifies the masking 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

Create a masking policy using maskall. (All values of the character string type are masked as x, and other types are displayed as the default values of this type.)

-- 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);

-- Insert data into the tb_for_masking table.
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'); 

-- View data.
gaussdb=# SELECT * FROM tb_for_masking;
 idx |    col1    |   col2    |      col3      |      col4      |        col5         |                col6           
      |        col7         |        col8        
-----+------------+-----------+----------------+----------------+---------------------+-------------------------------
------+---------------------+--------------------
   1 | 9876543210 | usr321usr | abc@huawei.com | abc@huawei.com | 1234-4567-7890-0123 | abcdef 123456 ui 323 jsfd321 j
3k2l3 | 4880-9898-4545-2525 | this is a llt case
(1 row)

-- Create a resource label for sensitive column col1.
gaussdb=# CREATE RESOURCE LABEL mask_lb1 ADD COLUMN(tb_for_masking.col1);

-- Create a data masking policy named maskpol1.
gaussdb=# CREATE MASKING POLICY maskpol1 maskall ON LABEL(mask_lb1);

-- Access the tb_for_masking table. The masking policy is triggered for the col1 column.
gaussdb=# SELECT col1 FROM tb_for_masking;
    col1    
------------
 xxxxxxxxxx
(1 row)

Create a data masking policy that uses randommasking to mask values of the character string type to random numbers. The masked values are different each time.

-- Create a resource label for sensitive column col2.
gaussdb=# CREATE RESOURCE LABEL mask_lb2 ADD COLUMN(tb_for_masking.col2);

-- Create a data masking policy named maskpol2.
gaussdb=# CREATE MASKING POLICY maskpol2 randommasking ON LABEL(mask_lb2);

-- Access the tb_for_masking table. The masking policy is triggered for the col2 column.
gaussdb=# SELECT col2 FROM tb_for_masking;
   col2    
-----------
 0e8612d9a
(1 row)

Create a data masking policy that uses basicemailmasking to set all data content before the at sign (@) in the mailbox format value of the character string type to x.

-- Create a resource label for sensitive column col3.
gaussdb=# CREATE RESOURCE LABEL mask_lb3 ADD COLUMN(tb_for_masking.col3);

-- Create a data masking policy named maskpol3.
gaussdb=# CREATE MASKING POLICY maskpol3 basicemailmasking ON LABEL(mask_lb3);

-- Access the tb_for_masking table. The masking policy is triggered for the col3 column.
gaussdb=# SELECT col3 FROM tb_for_masking;
     col3      
----------------
 xxx@huawei.com
(1 row)

Create a data masking policy that uses fullemailmasking to retain only the at sign (@) and .com in the email address format of the character string type, and set other parameters to x.

-- Create a resource label for sensitive column col4.
gaussdb=# CREATE RESOURCE LABEL mask_lb4 ADD COLUMN(tb_for_masking.col4);

-- Create a data masking policy named maskpol4.
gaussdb=# CREATE MASKING POLICY maskpol4 fullemailmasking ON LABEL(mask_lb4);

-- Access the tb_for_masking table. The masking policy is triggered for the col4 column.
gaussdb=# SELECT col4 FROM tb_for_masking; 
    col4      
----------------
 xxx@xxxxxx.com
(1 row)

Create a masking policy that uses creditcardmasking to retain the hyphen (-) and the last four digits for values of the string type, and set the rest to x.

-- Create a resource label for sensitive column col5.
gaussdb=# CREATE RESOURCE LABEL mask_lb5 ADD COLUMN(tb_for_masking.col5);

-- Create a data masking policy named maskpol5.
gaussdb=# CREATE MASKING POLICY maskpol5 creditcardmasking ON LABEL(mask_lb5);

-- Access the tb_for_masking table. The masking policy is triggered for the col5 column.
gaussdb=# SELECT col5 FROM tb_for_masking;
       col5         
---------------------
 xxxx-xxxx-xxxx-0123
(1 row)

Create a data masking policy that uses shufflemasking to shuffle the positions of characters in the value of the character string type.

-- Create a resource label for sensitive column col6.
gaussdb=# CREATE RESOURCE LABEL mask_lb6 ADD COLUMN(tb_for_masking.col6);

-- Create a data masking policy named maskpol6.
gaussdb=#  CREATE MASKING POLICY maskpol6 shufflemasking ON LABEL(mask_lb6);

-- Access the tb_for_masking table. The masking policy is triggered for the col6 column.
gaussdb=# SELECT col6 FROM tb_for_masking;
               col6                 
-------------------------------------
 2 b6jusfd54c3312 13d23lk3jf3  2eai 
(1 row)

Create a masking policy that uses regexpmasking to mask values of the string type using regular expressions.

-- Create a resource label for sensitive column col7.
gaussdb=# CREATE RESOURCE LABEL mask_lb7 ADD COLUMN(tb_for_masking.col7);

-- Create a data masking policy named maskpol7.
gaussdb=# CREATE MASKING POLICY maskpol7 regexpmasking('[\d+]','*',2, 9) ON LABEL(mask_lb7);

-- Access the tb_for_masking table. The masking policy is triggered for the col7 column.
gaussdb=# SELECT col7 FROM tb_for_masking;    
        col7         
---------------------
 48**-****-*545-2525
(1 row)

Create a masking policy that takes effect only for scenarios where users are dev_mask and bob_mask, the client tool gsql is used, and IP addresses are 172.31.17.160 and 127.0.0.0/24.

-- Create users dev_mask and bob_mask.
gaussdb=# CREATE USER dev_mask PASSWORD '********';
gaussdb=# CREATE USER bob_mask PASSWORD '*********';

-- Create a resource label for sensitive column col8.
gaussdb=# CREATE RESOURCE LABEL mask_lb8 ADD COLUMN(tb_for_masking.col8); 

-- Create a data masking policy named maskpol8.
gaussdb=# CREATE MASKING POLICY maskpol8 randommasking ON LABEL(mask_lb8) FILTER ON ROLES(dev_mask, bob_mask), APP(gsql), IP('172.31.17.160', '127.0.0.0/24');

-- Access the tb_for_masking table. The masking policy is triggered for the col8 column.
gaussdb=# SELECT * FROM tb_for_masking;

-- Use the gsql tool, set the IP address to 172.31.17.160, and view tb_for_masking as user dev_mask.
gaussdb=# GRANT ALL PRIVILEGES TO dev_mask;

-- Use maskpol8 for data masking. The result is random and different each time.
gaussdb=# SELECT col8 FROM tb_for_masking;
        col8        
--------------------
 cf32a9aa427f219ab0
(1 row)

gaussdb=# SELECT col8 FROM tb_for_masking;
        col8        
--------------------
 13efa056dda1e1a474
(1 row)

Delete data.

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