Redshiftの動的データマスキング(DDM)でSUPER型のデータをマスキングしてみた

drop table if exists public.order_transaction;
create table public.order_transaction (
 data_json super
);

INSERT INTO public.order_transaction
VALUES
    (
        json_parse('
        {
        "c_custkey": 328558,
        "c_name": "Customer#000328558",
        "c_phone": "586-436-7415",
        "c_creditcard": "4596209611290987",
        "orders":{
          "o_orderkey": 8014018,
          "o_orderstatus": "F",
          "o_totalprice": 120857.71,
          "o_orderdate": "2024-01-01"
          }
        }'
        )
    ),
    (
        json_parse('
        {
        "c_custkey": 328559,
        "c_name": "Customer#000328559",
        "c_phone": "789-232-7421",
        "c_creditcard": "8709000219329924",
        "orders":{
          "o_orderkey": 8014019,
          "o_orderstatus": "S",
          "o_totalprice": 9015.98,
          "o_orderdate": "2024-01-01"
          }
        }'
        )
    ),
    (
        json_parse('
        {
        "c_custkey": 328560,
        "c_name": "Customer#000328560",
        "c_phone": "276-564-9023",
        "c_creditcard": "8765994378650090",
        "orders":{
          "o_orderkey": 8014020,
          "o_orderstatus": "C",
          "o_totalprice": 18765.56,
          "o_orderdate": "2024-01-01"
          }
        }
        ')
    );

-- ユーザーを作成
-- admin権限を持つ場合は、set文は不要です
set session authorization admin;
CREATE USER Kate_cust WITH PASSWORD disable;
CREATE USER Ken_sales WITH PASSWORD disable;
CREATE USER Bob_exec WITH PASSWORD disable;
CREATE USER Jane_staff WITH PASSWORD disable;

-- ロールを作成
CREATE ROLE cust_srvc_role;
CREATE ROLE sales_srvc_role;
CREATE ROLE executives_role;
CREATE ROLE staff_role;

-- 各ユーザーにロールを付与
GRANT ROLE cust_srvc_role to Kate_cust;
GRANT ROLE sales_srvc_role to Ken_sales;
GRANT ROLE executives_role to Bob_exec;
GRANT ROLE staff_role to Jane_staff;

-- publicスキーマの全てのテーブルに対して全ての権限を各ロールに付与
GRANT ALL ON ALL TABLES IN SCHEMA "public" TO ROLE cust_srvc_role;
GRANT ALL ON ALL TABLES IN SCHEMA "public" TO ROLE sales_srvc_role;
GRANT ALL ON ALL TABLES IN SCHEMA "public" TO ROLE executives_role;
GRANT ALL ON ALL TABLES IN SCHEMA "public" TO ROLE staff_role;

-- pii dataをマスクするポリシー
CREATE MASKING POLICY mask_full
WITH(pii_data VARCHAR(256))
USING ('000000XXXX0000'::TEXT);

-- 指定価格を10の倍数に丸めるポリシー
CREATE MASKING POLICY mask_price
WITH(price INT)
USING ( (FLOOR(price::FLOAT / 10) * 10)::INT );

-- クレジットカードの末尾4桁以外をマスクするポリシー
CREATE MASKING POLICY mask_credit_card
WITH(credit_card TEXT)
USING ( 'XXXXXXXXXXXX'::TEXT || SUBSTRING(credit_card::TEXT FROM 13 FOR 4) );

-- 日付をマスクするポリシー
CREATE MASKING POLICY mask_date
WITH(order_date TEXT)
USING ( 'XXXX-XX-XX'::TEXT);

-- 電話番号の末尾4桁以外をマスクするポリシー
CREATE MASKING POLICY mask_phone
WITH(phone_number TEXT)
USING ( 'XXX-XXX-'::TEXT || SUBSTRING(phone_number::TEXT FROM 9 FOR 4) );

-- cust_srvc_roleへのマスク
set session authorization admin; -- 必要ならコメントアウトをオフにする

ATTACH MASKING POLICY mask_full
ON public.order_transaction(data_json.c_custkey)
TO ROLE cust_srvc_role;

ATTACH MASKING POLICY mask_phone
ON public.order_transaction(data_json.c_phone)
TO ROLE cust_srvc_role;

ATTACH MASKING POLICY mask_credit_card
ON public.order_transaction(data_json.c_creditcard)
TO ROLE cust_srvc_role;

ATTACH MASKING POLICY mask_price
ON public.order_transaction(data_json.orders.o_totalprice)
TO ROLE cust_srvc_role;

ATTACH MASKING POLICY mask_date
ON public.order_transaction(data_json.orders.o_orderdate)
TO ROLE cust_srvc_role;

-- sales_srvc_roleへのマスク
set session authorization admin; -- 必要ならコメントアウトをオフにする

ATTACH MASKING POLICY mask_phone
ON public.order_transaction(data_json.c_phone)
TO ROLE sales_srvc_role;


-- staff_roleへのマスク
set session authorization admin;　-- 必要ならコメントアウトをオフにする

ATTACH MASKING POLICY mask_full
ON public.order_transaction(data_json.orders.o_orderkey)
TO ROLE staff_role;

ATTACH MASKING POLICY mask_full
ON public.order_transaction(data_json.orders.o_orderstatus)
TO ROLE staff_role;

ATTACH MASKING POLICY mask_price
ON public.order_transaction(data_json.orders.o_totalprice)
TO ROLE staff_role;

ATTACH MASKING POLICY mask_date
ON public.order_transaction(data_json.orders.o_orderdate)
TO ROLE staff_role;

SELECT * FROM svv_masking_policy;

SELECT * FROM svv_attached_masking_policy;

set session authorization Kate_cust;
select * from order_transaction;

set session authorization Ken_sales;
select * from order_transaction;

set session authorization Jane_staff;
select * from order_transaction;

set session authorization Bob_exec;
select * from order_transaction;

set session authorization Jane_staff;
SELECT
    data_json.c_phone
    ,data_json.orders.o_orderdate
FROM
    order_transaction