Dynamic Data Masking

Restrictions

• The dynamic data masking policy must be created by a user with the POLADMIN or SYSADMIN attribute, or by the initial user. Common users do not have the permission to access the security policy system catalog and system view.
• Dynamic data masking takes effect only on data tables for which masking policies are configured. Audit logs are not within the effective scope of the masking policies.
• In a masking policy, only one masking mode can be specified for a resource label.
• Multiple masking policies cannot be used to mask the same resource label, except when FILTER is used to specify user scenarios where the policies take effect and there is no intersection between user scenarios of different masking policies that contain the same resource label. In this case, you can identify the policy that a resource label is masked by based on the user scenario.
• It is recommended that APP in FILTER be set to applications in the same trusted domain. Since a client may be forged, a security mechanism must be formed on the client when APP is used, to reduce misuse risks. Generally, you are advised not to use it. If it is used, pay attention to the risk of client spoofing.
• For INSERT or MERGE INTO operations with the query clause, if the source table contains masked columns, the inserted or updated result in the preceding two operations is the masked value and cannot be restored.
• When the built-in security policy is enabled, the ALTER TABLE EXCHANGE PARTITION statement fails to be executed if the source table is in the masked column.
• If a dynamic data masking policy is configured for a table, grant the trigger permission of the table to other users with caution to prevent other users from using the trigger to bypass the masking policy.
• A maximum of 98 dynamic data masking policies can be created.
• Only data with the resource labels containing the COLUMN attribute can be masked.
• Only columns in permanent ordinary tables can be masked.
• Only the data directly queried by SELECT can be masked. If you perform secondary processing on the masked result, the masking policy becomes invalid or does not meet the expectation.
• User-defined functions used for dynamic data masking can only be a standard database SQL or PL/SQL function.
• If the user-defined function used for dynamic data masking contains a statement (such as SELECT and INSERT) for accessing database resources, the dynamic data masking result using the user-defined function may not meet the expectation or may cause security risks.
• After a data masking policy is successfully created for a user-defined function used for dynamic data masking, if ALTER or DROP is performed on the data masking column, the data masking policy becomes invalid or does not meet the expectation.
• The user-defined function for dynamic data masking does not support the SECURITY INVOKER function. After a data masking policy is successfully created for a user-defined function that applies to dynamic data masking, no CREATE, ALTER, or DROP operation can be performed on the function.
• User-defined functions used for dynamic data masking can be created only by users with the POLADMIN permission. A user with the POLADMIN permission grants the usage permission for accessing the schema to the PUBLIC user. If the user cannot access the user-defined functions due to the GRANT and REVOKE operations, maskall is used for masking.
• The user-defined function used for dynamic data masking must be idempotent. That is, the execution results are the same for multiple times. If the user-defined function is set to non-idempotent, the dynamic data masking result using the user-defined function in distributed scenarios may not meet the expectation.
• The data masking policy created for a user-defined function used for dynamic data masking cannot be applied to system catalogs.
• Taking an IPv4 address as an example, the following formats are supported:

  IP Address	Example
  Single IP address	127.0.0.1
  IP address with mask	127.0.0.1|255.255.255.0
  CIDR IP address	127.0.0.1/24
  IP address segment	127.0.0.1-127.0.0.5

• Dynamic data masking policies cannot be exported using gs_dump. The system administrator or security policy administrator can access the GS_MASKING_POLICY, GS_MASKING_POLICY_ACTIONS, and GS_MASKING_POLICY_FILTERS system catalogs to query created dynamic data masking policies.

Viewing the Basic Configurations of Dynamic Data Masking

1. Set and check if the dynamic data masking function is enabled.

   gs_guc reload -Z datanode -N all -I all -c "enable_security_policy=on"

   If enable_security_policy is set to on, the function is enabled. If it is set to off, the function is disabled.

   gaussdb=# SHOW enable_security_policy;
    enable_security_policy
   -----------------------
    on
   (1 row)

Creating a Masking Policy

1. Create a table.

   -- 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@example.com', 'abc@example.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@example.com | abc@example.com | 1234-4567-7890-0123 | abcdef 123456 ui 323 jsfd321 j
   3k2l3 | 4880-9898-4545-2525 | this is a llt case
   (1 row)

2. Create a resource label.

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

3. Create a masking policy.
   For details about the syntax format, see "SQL Reference > SQL Syntax > CREATE MASKING POLICY" in Developer Guide.

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

   During the configuration of dynamic data masking policies, user-defined functions created by users can be adapted.

   gaussdb=# create or replace function msk_creditcard(col text) returns TEXT as $$
   declare
       result TEXT;
   begin
       result := overlay(col placing 'xxxx-xxxx' from 6);
       return result;
   end;
   $$ language plpgsql security DEFINER;
   CREATE FUNCTION
   -- Create a data masking policy named maskpol5.
   gaussdb=# CREATE MASKING POLICY maskpol5 msk_creditcard ON LABEL(mask_lb5);
   CREATE MASKING POLICY

4. Query the masked column data in the tb_for_masking table.

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

5. Clear data.

   -- Drop masking policies.
   gaussdb=# DROP MASKING POLICY maskpol1, maskpol5;
   DROP MASKING POLICY
   -- Drop resource labels.
   gaussdb=# DROP RESOURCE LABEL mask_lb1, mask_lb5;
   DROP RESOURCE LABEL
   -- Drop the tb_for_masking table.
   gaussdb=# DROP TABLE tb_for_masking;
   DROP TABLE