ALTER MASKING POLICY

Description

Modifies 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.

Syntax

• Modify the policy description.

  1	ALTER MASKING POLICY policy_name COMMENTS policy_comments;

  

• Modify the masking method.

  1	ALTER MASKING POLICY policy_name {ADD | REMOVE | MODIFY} masking_actions[, ...];

  

  • The syntax of masking_actions

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

    

  • The syntax of masking_function

    1	{ maskall | randommasking | creditcardmasking | basicemailmasking | fullemailmasking | shufflemasking | alldigitsmasking | regexpmasking }

    

• Modify the scenarios where the masking policies take effect.

  1	ALTER MASKING POLICY policy_name MODIFY (filter_group_clause);

  

  • The syntax of filter_group_clause

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

    

• Remove the filters of the masking policies.

  1	ALTER MASKING POLICY policy_name DROP FILTER;

  

• Enable or disable the masking policies.

  1	ALTER MASKING POLICY policy_name {ENABLE | DISABLE};

  

Parameters

• policy_name

  Specifies the masking policy name, which must be unique.

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

• policy_comments

  Adds or modifies description of masking policies.

• masking_function

  Specifies eight preset masking methods or user-defined functions. Schema is supported.

  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 }

• label_name

  Specifies the resource label name.

• FILTER_TYPE

  Specifies the types of information to be filtered by the policies: IP, ROLES, and APP.

• 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

• Modify the policy description.

  -- 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);
  
  -- Add description for masking policy maskpol1.
  gaussdb=# ALTER MASKING POLICY maskpol1 COMMENTS 'masking policy for tb_for_masking.col1';
  
  -- View the description of data masking policy maskpol1.
  gaussdb=# SELECT * FROM GS_MASKING_POLICY;                
   polname  |              polcomments               |         modifydate         | polenabled 
  ----------+----------------------------------------+----------------------------+------------
   maskpol1 | masking policy for tb_for_masking.col1 | 2023-11-07 16:38:31.607374 | t
  (1 row)

• Modify a masking policy:

  -- Create a resource label for sensitive column col2.
  gaussdb=# CREATE RESOURCE LABEL mask_lb2 ADD COLUMN(tb_for_masking.col2);
  
  -- Add the randommasking masking mode to the masking policy maskpol1.
  gaussdb=# ALTER MASKING POLICY maskpol1 ADD 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   
  ------------
   27e8da66cc
  (1 row)
  
  -- Remove the randommasking masking mode from the masking policy maskpol1.
  gaussdb=# ALTER MASKING POLICY maskpol1 REMOVE randommasking ON LABEL(mask_lb2);
  
  -- Access the tb_for_masking table. The data in the col2 column is not masked, indicating that the masking mode randommasking is invalid.
  gaussdb=# SELECT col2 FROM tb_for_masking;
     col2    
  -----------
   usr321usr
  (1 row)
  
  -- Change the masking mode of the masking policy maskpol1 to randommasking.
  gaussdb=# ALTER MASKING POLICY maskpol1 MODIFY randommasking 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    
  ------------
   5a03debac1
  (1 row)
  

• Modify the scenarios where the masking policies take effect.

  -- 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');
  
  -- Modify the filtering information ROLES of data masking policy maskpol8.
  gaussdb=# ALTER MASKING POLICY maskpol1 MODIFY (FILTER ON ROLES(dev_mask));
  
  -- Check tb_for_masking as the dev_mask user.
  gaussdb=# GRANT ALL PRIVILEGES TO dev_mask;
  
  -- Access the tb_for_masking table. The masking policy is triggered for the col8 column.
  gaussdb=# SELECT col8 FROM tb_for_masking;
          col8        
  --------------------
   f134e06ef528013b46
  (1 row)
  

• Remove the filters of the masking policies for the policies to take effect.

  gaussdb=# ALTER MASKING POLICY maskpol1 DROP FILTER;

• Disable a masking policy:

  -- Disable masking policy maskpol1.
  gaussdb=# ALTER MASKING POLICY maskpol1 DISABLE;
  
  -- Check the status of data masking policy maskpol1. If the value of polenabled is f, the data masking policy is disabled successfully.
  gaussdb=# SELECT * FROM GS_MASKING_POLICY;
   polname  | polcomments |         modifydate         | polenabled 
  ----------+-------------+----------------------------+------------
   maskpol1 |             | 2023-11-07 17:22:54.594111 | f

• Delete data:

  -- Delete a masking policy.
  gaussdb=# DROP MASKING POLICY maskpol1, maskpol8;
  
  -- Delete a resource label.
  gaussdb=# DROP RESOURCE LABEL mask_lb1, mask_lb2, 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

CREATE MASKING POLICY and DROP MASKING POLICY