📣
TiDB Cloud Premium is now in public preview. Unlimited growth, instant elasticity, advanced security for enterprise workloads. Try it out →
​
Sign InStart for Free
AI
Developer
Best Practices
API
​
Sign InStart for Free

CREATE MASKING POLICY

Creates a new masking policy in TiDB Cloud Lake.

Syntax

CREATE MASKING POLICY [ IF NOT EXISTS ] <policy_name> AS
    ( <arg_name_to_mask> <arg_type_to_mask> [ , <arg_1> <arg_type_1> ... ] )
    RETURNS <arg_type_to_mask> -> <expression_on_arg_name>
    [ COMMENT = '<comment>' ]
ParameterDescription
policy_nameName of the masking policy to be created.
arg_name_to_maskParameter that represents the column being masked. This argument must appear first and automatically binds to the column referenced in SET MASKING POLICY.
arg_type_to_maskData type of the masked column. It must match the data type of the column where the policy is applied.
arg_1 ... arg_nOptional extra parameters for additional columns that the policy logic depends on. Provide these columns through the USING clause when you attach the policy.
arg_type_1 ... arg_type_nData types for each optional parameter. They must match the columns listed in the USING clause.
expression_on_arg_nameExpression that determines how the input columns should be treated to generate the masked data.
commentOptional comment that stores notes about the masking policy.

Access Control Requirements

PrivilegeDescription
CREATE MASKING POLICYRequired to create a masking policy. Typically granted on *.*.

TiDB Cloud Lake automatically grants OWNERSHIP on the new masking policy to the current role so that it can manage the policy with others.

Examples

This example illustrates the process of setting up a masking policy to selectively reveal or mask sensitive data based on user roles.

-- Create a table and insert sample data
CREATE TABLE user_info (
    user_id INT,
    phone  VARCHAR,
    email VARCHAR
);

INSERT INTO user_info (user_id, phone, email) VALUES (1, '91234567', 'sue@example.com');
INSERT INTO user_info (user_id, phone, email) VALUES (2, '81234567', 'eric@example.com');

-- Create a role
CREATE ROLE 'MANAGERS';
GRANT ALL ON *.* TO ROLE 'MANAGERS';

-- Create a user and grant the role to the user
CREATE USER manager_user IDENTIFIED BY 'datalake';
GRANT ROLE 'MANAGERS' TO 'manager_user';

-- Create a masking policy that expects an extra column
CREATE MASKING POLICY contact_mask
AS
  (contact_val nullable(string), phone_ref nullable(string))
  RETURNS nullable(string) ->
  CASE
  WHEN current_role() IN ('MANAGERS') THEN
    contact_val
  WHEN phone_ref LIKE '91%'
  THEN
    contact_val
  ELSE
    '*********'
  END
  COMMENT = 'mask contact data with phone check';

-- Associate the masking policy with the 'email' column
ALTER TABLE user_info
MODIFY COLUMN email SET MASKING POLICY contact_mask USING (email, phone);

-- Associate the masking policy with the 'phone' column
ALTER TABLE user_info
MODIFY COLUMN phone SET MASKING POLICY contact_mask USING (phone, phone);

-- Query with the Root user
SELECT user_id, phone, email FROM user_info ORDER BY user_id;

     user_id     │        phone     │       email      │
 Nullable(Int32) │ Nullable(String) │ Nullable(String) │
─────────────────┼──────────────────┼──────────────────┤
               1 │ 91234567         │ sue@example.com  │
               2 │ *********        │ *********        │

Was this page helpful?