数据保护策略
TiDB Cloud Lake 在查询时保护敏感数据，而不会更改存储的值：
两者对应用程序都是透明的：无需修改代码，无需额外视图，无需复制数据。
选择合适的策略
示例：一个包含 phone、amount 和 region 的 orders 表。
它们如何协同工作
Query
→ Row Access Policy filters rows
→ Masking Policy transforms surviving columns
→ Result returned
行过滤先执行。掩码仅应用于剩余的行。
同一张表可以同时使用两者。同一个 column 不能同时绑定到两者。
-- Rows: sales only see their region
CREATE ROW ACCESS POLICY rap_region
AS (r STRING) RETURNS BOOLEAN ->
CASE
WHEN is_role_in_session('admin') THEN true
ELSE is_role_in_session(r)
END;
ALTER TABLE customers ADD ROW ACCESS POLICY rap_region ON (region);
-- Columns: non-HR see redacted SSN
CREATE MASKING POLICY mask_ssn
AS (val STRING) RETURNS STRING ->
CASE
WHEN is_role_in_session('hr') THEN val
ELSE '***-**-****'
END;
ALTER TABLE customers MODIFY COLUMN ssn SET MASKING POLICY mask_ssn;
端到端：职责分离
将 RBAC 与这两种策略结合使用，使创建者、应用者和读取者彼此分离。
-- account_admin: roles, users, CREATE privileges
CREATE ROLE security_admin;
CREATE ROLE data_engineer;
CREATE ROLE analyst_apac;
CREATE ROLE support_global;
CREATE USER sec_user IDENTIFIED BY 'password123';
CREATE USER eng_user IDENTIFIED BY 'password123';
CREATE USER analyst_user IDENTIFIED BY 'password123';
CREATE USER support_user IDENTIFIED BY 'password123';
GRANT ROLE security_admin TO USER sec_user;
GRANT ROLE data_engineer TO USER eng_user;
GRANT ROLE analyst_apac TO USER analyst_user;
GRANT ROLE support_global TO USER support_user;
GRANT CREATE DATABASE ON *.* TO ROLE data_engineer;
GRANT CREATE MASKING POLICY ON *.* TO ROLE security_admin;
GRANT CREATE ROW ACCESS POLICY ON *.* TO ROLE security_admin;
GRANT GRANT ON *.* TO ROLE security_admin;
-- data_engineer: table ownership
SET ROLE data_engineer;
CREATE DATABASE ecommerce;
CREATE TABLE ecommerce.orders (
order_id INT,
customer_name STRING,
phone STRING,
region STRING,
amount DECIMAL(10,2),
created_at TIMESTAMP
);
INSERT INTO ecommerce.orders VALUES
(1, 'Alice', '13812345678', 'APAC', 299.00, '2025-01-15 10:00:00'),
(2, 'Bob', '14987654321', 'EMEA', 150.00, '2025-01-16 11:00:00'),
(3, 'Charlie', '13698765432', 'APAC', 520.00, '2025-01-17 09:30:00'),
(4, 'Diana', '15012349876', 'AMER', 89.00, '2025-01-18 14:00:00');
-- security_admin: create policies (auto OWNERSHIP)
SET ROLE security_admin;
SET enable_experimental_row_access_policy = 1;
CREATE MASKING POLICY mask_phone
AS (val STRING) RETURNS STRING ->
CASE
WHEN is_role_in_session('data_engineer') OR is_role_in_session('support_global') THEN val
ELSE CONCAT(SUBSTRING(val, 1, 3), '****', SUBSTRING(val, 8))
END;
CREATE ROW ACCESS POLICY rap_region
AS (r STRING) RETURNS BOOLEAN ->
CASE
WHEN is_role_in_session('data_engineer') OR is_role_in_session('support_global') THEN true
WHEN is_role_in_session('analyst_apac') AND r = 'APAC' THEN true
ELSE false
END;
GRANT APPLY ON MASKING POLICY mask_phone TO ROLE data_engineer;
GRANT APPLY ON ROW ACCESS POLICY rap_region TO ROLE data_engineer;
-- data_engineer: attach (needs table ALTER + policy APPLY)
SET ROLE data_engineer;
SET enable_experimental_row_access_policy = 1;
ALTER TABLE ecommerce.orders MODIFY COLUMN phone SET MASKING POLICY mask_phone;
ALTER TABLE ecommerce.orders ADD ROW ACCESS POLICY rap_region ON (region);
-- account_admin: grant table access through roles
GRANT USAGE ON ecommerce.* TO ROLE analyst_apac;
GRANT USAGE ON ecommerce.* TO ROLE support_global;
GRANT SELECT ON ecommerce.orders TO ROLE analyst_apac;
GRANT SELECT ON ecommerce.orders TO ROLE support_global;
结果：
SET ROLE analyst_apac;
SELECT * FROM ecommerce.orders;
-- Alice / Charlie only, phones masked
SET ROLE support_global;
SELECT * FROM ecommerce.orders;
-- all 4 rows, phones visible
SET ROLE security_admin;
SELECT * FROM ecommerce.orders;
-- ERROR: Permission denied
回收该角色后，无需更改表授权即可移除访问权限：
REVOKE ROLE analyst_apac FROM USER analyst_user;
经验法则：
- 创建策略 ≠ 查询数据；附加策略需要同时具备策略
APPLY和表ALTER - 优先将权限授予角色，而不是用户
- 创建者角色会自动获得 OWNERSHIP
CREATE MASKING/ROW ACCESS POLICY是授予给角色，而不是用户- 使用
SHOW GRANTS ON MASKING POLICY ...、SHOW GRANTS ON ROW ACCESS POLICY ...和POLICY_REFERENCES(...)进行审计