Redshiftの動的データマスキング(DDM)の機能を試してみた(Preview) #reinvent

-- スキーマとテーブル作成
CREATE SCHEMA ddmtest;
SET search_path to ddmtest;

CREATE TABLE phone_number (
id INT,
phone TEXT,
flag BOOLEAN
);

-- テーブルにデータロード
COPY phone_number
FROM 's3://<detail-bucket-name>/<detail-object-key>'
IAM_ROLE '<redshift_role>'
CSV
IGNOREHEADER 1
ENCODING UTF8
;

--スキーマ、テーブルにSELECT出来る権限を付与（検証なので手っ取り早くTO PUBLICを使います）
GRANT USAGE ON SCHEMA ddmtest TO PUBLIC;
GRANT SELECT ON phone_number TO PUBLIC;

--ユーザー作成
CREATE USER test1 WITH PASSWORD 'Password123!';
CREATE USER test2 WITH PASSWORD 'Password123!';
CREATE USER test3 WITH PASSWORD 'Password123!';
CREATE USER test4 WITH PASSWORD 'Password123!';

-- ロールを作成
CREATE ROLE cust_srvc_role;
CREATE ROLE frdprvnt_role;
CREATE ROLE auditor_role;
-- note that public role exist by default.

-- ユーザーにロールをアタッチ
GRANT ROLE cust_srvc_role to test2;
GRANT ROLE frdprvnt_role  to test3;
GRANT ROLE auditor_role   to test4;

-- 1 電話番号を全てマスキングするポリシー
CREATE MASKING POLICY Mask_CC_Full
WITH (phone VARCHAR(256))
USING ('XXX-XXX-XXXX');

-- 2 テキストより左から３桁のみを表示させる処理を行う定義関数を作成
CREATE FUNCTION REDACT_PHONE (text)
  returns text
immutable
as $$
  select left($1,4)||'XXX-XXXX'
$$ language sql;

-- 3 ２の定義関数を実行し、市外局番のみ表示させる部分マスキングを実現するポリシー
CREATE MASKING POLICY Mask_CC_Partial
WITH (phone VARCHAR(256))
USING (REDACT_PHONE(phone));

-- 4 flag列のtrue/falseで判断し、表示／NULL加工をするマスキングポリシー
CREATE MASKING POLICY Mask_CC_Conditional
WITH (flag BOOLEAN, phone VARCHAR(256))
USING (CASE WHEN flag 
                 THEN phone 
                 ELSE Null 
       END);

-- 5 マスキングを行わないポリシー
CREATE MASKING POLICY Mask_CC_Raw
WITH (phone varchar(256))
USING (phone);

-- デフォルトのポリシーはフルマスキングポリシーに設定
-- より高い優先順位のマスキング・ポリシーがユーザーまたはそのロールに付加されていない限り、すべてのユーザーにこのマスキング・ポリシーが表示されます
ATTACH MASKING POLICY Mask_CC_Full
ON phone_number(phone)
TO PUBLIC;

-- cust_srvc_roleロールを持つユーザーは、一部の番号（市外局番のみ）を見ることができる
ATTACH MASKING POLICY Mask_CC_Partial
ON phone_number(phone)
TO ROLE cust_srvc_role
PRIORITY 10;

-- frdprvnt_roleロールを持つユーザーは、flag列がtrueの行のみ番号を見ることができる
ATTACH MASKING POLICY Mask_CC_Conditional
ON phone_number(phone)
USING (flag, phone)
TO ROLE frdprvnt_role
PRIORITY 20;

-- auditor_roleロールを持つユーザーは、すべての番号を見ることができる
--    users with auditor_role role can see raw credit card numbers
ATTACH MASKING POLICY Mask_CC_Raw
ON phone_number(phone)
TO ROLE auditor_role
PRIORITY 30;

SELECT * FROM svv_masking_policy;

SELECT * FROM svv_attached_masking_policy;

SET SESSION AUTHORIZATION test2;
SELECT * FROM phone_number;

SET SESSION AUTHORIZATION test3;
SELECT * FROM phone_number;

SET SESSION AUTHORIZATION test4;
SELECT * FROM phone_number;

SET SESSION AUTHORIZATION test1;
SELECT * FROM phone_number;

--ポリシーをロールからデタッチするクエリの例
DETACH MASKING POLICY Mask_CC_Partial
ON                    credit_cards(credit_card)
FROM ROLE             cust_srvc_role;

-- ポリシーを削除するクエリの例
DROP MASKING POLICY Mask_CC_Partial;

-- あとは改めてポリシー作成のクエリを実行する