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

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

Amazon Redshiftで動的データマスキングを使えば、SUPER型のJSONに対してもロールベースでデータマスキングが可能です。
#AWS
#Amazon Redshift
渡部晃季

渡部晃季

facebook logohatena logotwitter logo
Clock Icon2024.06.24

はじめに

こんにちは、データアナリティクス事業本部の渡部です。

今回はRedshiftの動的データマスキング（DDM）を使用して、SUPER型に対するデータマスキングを試してみました。
DDMを使うことで、Redshiftのユーザー/ロール単位で項目レベルでデータマスキング可能です。
各業務部門ごとに見れるデータを制限したいという話が最近よくあがるため、確認してみます。

以下の記事をなぞりながら試してみました。

やってみる

1.データ準備

テーブルorder_transactionを作成します。
テーブルにはSUPER型の項目data_jsonを用意します。

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"
          }
        }
        ')
    );

2.ユーザーとロールを作成

ここで作成するのはRedshiftのユーザーとロールです。
RedsiftなのかIAMなのか、言葉が一緒なので迷いがちですが、ここはRedshiftです。
4ユーザーがそれぞれ別部門に所属していて、それぞれのロールが付与されている状態を準備します。

-- ユーザーを作成
-- 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;

3.マスキングポリシーの作成

マスキングポリシーは、「こういうデータはこういう風にマスクするよ」を決めるものです。
WITH句では、マスクする対象のデータ型とパラメータ名を指定して、
USNIG句では、マスクの編集要領を指定します。
WITH句のパラメータは実際の項目名と合わせる必要はありません。

CREATE_MASKING_POLICY

-- 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) );

4.マスキングポリシーの割り当て

マスキングポリシーを特定のテーブルの項目に対して適用し、指定のロールにそのマスキングを有効にします。
ATTACH_MASKING_POLICY


-- 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;

これで設定は完了です。

5.試してみる

ここからは設定がどう反映されているか確認します。

まずはマスキングポリシーです。
RedshiftにはSVVビューがあり、マスキングポリシーは簡単に一覧化可能です。

SELECT * FROM svv_masking_policy;

次に4で設定した、マスキングポリシーが適用されているテーブルのカラムと、そのポリシーがアタッチされているユーザー/ロールの一覧確認をします。
SVVビュー1つで用意されているので、いろんなテーブルやビューを確認して権限を確認するといった管理作業が不要です。

SELECT * FROM svv_attached_masking_policy;

以降は、各ロールがアタッチされたユーザーでSELECTをしてマスキングがされているかの確認です。
5.2のSQLで確認したロールごとに、項目がマスキングされていればOKなので、5.2の結果と見比べます。
以下は5.2のSQLの抜粋で、input_columnsがマスキングされていることを確認します。

grantee policy_name input_columns output_columns
cust_srvc_role mask_credit_card ["data_json.\"c_creditcard\""] ["data_json.\"c_creditcard\""]
cust_srvc_role mask_date ["data_json.\"orders\".\"o_orderdate\""] ["data_json.\"orders\".\"o_orderdate\""]
cust_srvc_role mask_full ["data_json.\"c_custkey\""] ["data_json.\"c_custkey\""]
cust_srvc_role mask_phone ["data_json.\"c_phone\""] ["data_json.\"c_phone\""]
cust_srvc_role mask_price ["data_json.\"orders\".\"o_totalprice\""] ["data_json.\"orders\".\"o_totalprice\""]
sales_srvc_role mask_phone ["data_json.\"c_phone\""] ["data_json.\"c_phone\""]
staff_role mask_date ["data_json.\"orders\".\"o_orderdate\""] ["data_json.\"orders\".\"o_orderdate\""]
staff_role mask_full ["data_json.\"orders\".\"o_orderkey\""] ["data_json.\"orders\".\"o_orderkey\""]
staff_role mask_full ["data_json.\"orders\".\"o_orderstatus\""] ["data_json.\"orders\".\"o_orderstatus\""]
staff_role mask_price ["data_json.\"orders\".\"o_totalprice\""] ["data_json.\"orders\".\"o_totalprice\""] 
set session authorization Kate_cust;
select * from order_transaction;

data_json
"{""c_custkey"":""000000XXXX0000"",""c_name"":""Customer#000328558"",""c_phone"":""XXX-XXX-7415"",""c_creditcard"":""XXXXXXXXXXXX0987"",""orders"":{""o_orderkey"":8014018,""o_orderstatus"":""F"",""o_totalprice"":120850,""o_orderdate"":""XXXX-XX-XX""}}"
"{""c_custkey"":""000000XXXX0000"",""c_name"":""Customer#000328559"",""c_phone"":""XXX-XXX-7421"",""c_creditcard"":""XXXXXXXXXXXX9924"",""orders"":{""o_orderkey"":8014019,""o_orderstatus"":""S"",""o_totalprice"":9010,""o_orderdate"":""XXXX-XX-XX""}}"
"{""c_custkey"":""000000XXXX0000"",""c_name"":""Customer#000328560"",""c_phone"":""XXX-XXX-9023"",""c_creditcard"":""XXXXXXXXXXXX0090"",""orders"":{""o_orderkey"":8014020,""o_orderstatus"":""C"",""o_totalprice"":18760,""o_orderdate"":""XXXX-XX-XX""}}" 
set session authorization Ken_sales;
select * from order_transaction;

data_json
"{""c_custkey"":328558,""c_name"":""Customer#000328558"",""c_phone"":""XXX-XXX-7415"",""c_creditcard"":""4596209611290987"",""orders"":{""o_orderkey"":8014018,""o_orderstatus"":""F"",""o_totalprice"":120857.71,""o_orderdate"":""2024-01-01""}}"
"{""c_custkey"":328559,""c_name"":""Customer#000328559"",""c_phone"":""XXX-XXX-7421"",""c_creditcard"":""8709000219329924"",""orders"":{""o_orderkey"":8014019,""o_orderstatus"":""S"",""o_totalprice"":9015.98,""o_orderdate"":""2024-01-01""}}"
"{""c_custkey"":328560,""c_name"":""Customer#000328560"",""c_phone"":""XXX-XXX-9023"",""c_creditcard"":""8765994378650090"",""orders"":{""o_orderkey"":8014020,""o_orderstatus"":""C"",""o_totalprice"":18765.56,""o_orderdate"":""2024-01-01""}}" 
set session authorization Jane_staff;
select * from order_transaction;

data_json
"{""c_custkey"":328558,""c_name"":""Customer#000328558"",""c_phone"":""586-436-7415"",""c_creditcard"":""4596209611290987"",""orders"":{""o_orderkey"":""000000XXXX0000"",""o_orderstatus"":""000000XXXX0000"",""o_totalprice"":120850,""o_orderdate"":""XXXX-XX-XX""}}"
"{""c_custkey"":328559,""c_name"":""Customer#000328559"",""c_phone"":""789-232-7421"",""c_creditcard"":""8709000219329924"",""orders"":{""o_orderkey"":""000000XXXX0000"",""o_orderstatus"":""000000XXXX0000"",""o_totalprice"":9010,""o_orderdate"":""XXXX-XX-XX""}}"
"{""c_custkey"":328560,""c_name"":""Customer#000328560"",""c_phone"":""276-564-9023"",""c_creditcard"":""8765994378650090"",""orders"":{""o_orderkey"":""000000XXXX0000"",""o_orderstatus"":""000000XXXX0000"",""o_totalprice"":18760,""o_orderdate"":""XXXX-XX-XX""}}" 
set session authorization Bob_exec;
select * from order_transaction;

data_json
"{""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""}}"
"{""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""}}"
"{""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""}}"

4ユーザー（ロール）ごとに、確かにデータマスキングされていることが確認できました。

最後はSUPER型のJSONを加工してSELECTをしてみました。
ピリオドで要素を繋げれば簡単にSELECT可能です。

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

おわりに

データマスキングという名前なので、どうしても私はデータを隠すものなのかなあと思ってしまいますが、
ロール単位でのデータ加工にも使えないかなあと考えています。
例えばデータから逆ジオコーディングなど。
なかなか使いどころは限られるとは思いますが、引き出しとして覚えておこうと思います。　　

以上、RedshiftのSUPER型に対してのデータマスキングでした。
どなたかのご参考になれば幸いです。

Share this article

facebook logohatena logotwitter logo

関連記事

パブリックアクセスブロック(BPA)でインターネットアクセスを遮断してみた

パブリックアクセスブロック(BPA)でインターネットアクセスを遮断してみた

User avatar
suzuki.ryo
2024.11.22
「AWSにおける生成AI＆データの全体像」というタイトルで 第8回 Classmethod AI Talks(CATs)に登壇しました #catalks

「AWSにおける生成AI＆データの全体像」というタイトルで 第8回 Classmethod AI Talks(CATs)に登壇しました #catalks

User avatar
石川覚
2024.11.21
CloudFront 新機能！VPCオリジンでプライベートなALBをセキュアに公開してみた

CloudFront 新機能！VPCオリジンでプライベートなALBをセキュアに公開してみた

User avatar
suzuki.ryo
2024.11.21
[アップデート] EBSスナップショットとAMIのゴミ箱保持ルールの適用対象をタグで制御できるようになりました

[アップデート] EBSスナップショットとAMIのゴミ箱保持ルールの適用対象をタグで制御できるようになりました

User avatar
武田隆志
2024.11.21
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.