データ保護ポリシー
TiDB Cloud Lake は、保存された値を変更することなく、クエリ実行時に機密データを保護します。
どちらもアプリケーションに対して透過的です。コード変更、追加のビュー、データコピーは不要です。
適切なポリシーを選ぶ
例: phone、amount、region を持つ orders テーブル。
どのように連携するか
Query
→ Row Access Policy filters rows
→ Masking Policy transforms surviving columns
→ Result returned
最初に行フィルタリングが実行されます。マスキングは、残った行に対してのみ適用されます。
同じテーブルで両方を使用できます。ただし、同じカラムを両方にバインドできません。
-- 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(...)で監査できます