CREATE MASKING POLICY

在 TiDB Cloud Lake 中创建新的 masking policy。

语法

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>' ]

访问控制要求

TiDB Cloud Lake 会自动将新 masking policy 的 OWNERSHIP 授予当前角色，以便其管理该策略并与其他人协作。

示例

本示例演示了如何设置 masking policy，以便根据用户角色有选择地显示或隐藏敏感数据。

-- 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 │ *********        │ *********        │