🪜

Snowflakeにおけるdata masking運用をdbtとtroccoでやる

2023/07/20に公開

SQL

今回のモチベーション

このBlogをみてもう少し再現してみたくなった
Snowflakeのroleについてそろそろちゃんと考えてみないとダメな時期が来ていた
有用なdbtの使い方として挙げられるかもという興味関心もあった

0. 準備

Snowflake

30日間の試用環境を利用させてもらいましたmm
- 今回はRoleとMaskingの機能を深堀

dbt

dbt cloudでの構築
- dbtは主にマクロで動的に権限を付与する役割がメイン

trocco

データ分析基盤の統合支援ツール
- データ転送とdbtで作成したジョブをワークフロー上で実行する

1. ダミーデータの作成と取り込み

下記のサイトを使用した、便利！

個人情報テストデータジェネレーター
実際に作成されたデータはこんな感じ

このデータをtroccoを使い Local or storage => Snowflake（user_infoというテーブル）で取り込みジョブを作成し、事前に実行（この作業は割愛）
テーブルのカラム名（物理名）は英名にした、おそらく問題ないがdbt側でエラーなどが出ても面倒なので

2. ポリシーの検討

データの中身の整理と、マスキング方法の検討を検討する
各職種やロールによってPIIの取り扱いは異なるはずで結構細分化が必要そう
参考にした記事でもブレインストーミングで2-3週間という結果、会社の規模やデータ量によってはもっと長くなる可能性大
1つの列に対して、ロールによってポリシーを変えたいことなどあるか（基本はロールによってmaskingするレベルが分かれるなんて大層なシステムじゃない限り無さそうな印象）
とりあえずもしポリシーに階層があったとしてもこの程度を想定
- システム管理者：全部見れる
- データ集計者・マネジャー：弱めのマスキング
- BI上で確認するレポーター：強めのマスキング（基本個人情報はmasking対象だが一部は見れる
- 不特定多数：そもそも項目みれない・全マスク
今回のデータで考えてみたもの（例えば...です）
このあとdbtのマクロ等でアタッチすることを考えると、ポリシーの名称とカラムの物理名の整合性はしっかりとる必要がある

3. ポリシーを作る

クエリの作成例（roleによって全マスクであるカラム：nameの例）

-- nameカラムのマスキングPOLICY
-- ちなみにPOLICYがすでに特定の列に設定された状態では、REPLACEができない、一度UNSETする必要あり
CREATE OR REPLACE MASKING POLICY name_mask AS (val string) returns string ->
  CASE
    -- 書き方としては CURRENT_ROLE() IN ('PII')　もあるが、ロールの継承の上で判定がおそらく効かない
    WHEN  IS_ROLE_IN_SESSION('PII') or val = '' or val is null THEN val
    ELSE '***MASKED***'
  END;

-- マスキングの一覧表示
SHOW MASKING POLICIES;

-- マスキングのSETとUNSET
ALTER TABLE "user_info" MODIFY COLUMN "name" SET MASKING POLICY name_mask;
ALTER TABLE "user_info" MODIFY COLUMN "name" UNSET MASKING POLICY;

補足：ロールとポリシーの進め方

実際にSnowflakeを導入する時点でRoleの設計は進めつつも、maskingのレベルまで検討できるユーザーや企業はどの程度いるのであろうか...
Roleが既存に存在することで、ポリシーの設計に困ることは大きくはない（逆にどのRoleにどんなポリシーをつければいいか考えるだけなので整理はしやすい？）
それよりもRoleやポリシーにおけるドメインの粒度設計（各集合の設計）が勘所になりそう
こういうものは「絶対に見えてはいけないカラム（秘匿性が高いカラム）」や「絶対付けてはいけない権限(SuperUser)」以外は機械的に運用してみるのも興。ただ結局は付与にあたって再現性が求められる部分もあり、機械的に成長するというより拡張するという印象のほうが強い😂

4. ロールの作成と継承

上記Blogととほぼ同じ構成で組んだ
PIIのロールを包含したロールはマスクなし見れるというもの（権限の継承）
- そもそもカラムを表示する、しないという観点は、devやbiのロールで制御し、あくまでのmaskingのポリシーはpiiのロールでで管理する
- 管理者 > dev > biという構成の上で、devやbiのメンバーが一時的に情報を確認したいとなった場合はpii_devやpii_biにロールを上げるという構成
- ただ、カラム表示とmaskingをロール別でコントロールするのは実際メンテが面倒かもと思ったり
初回の作成は意外とGUI上やったほうが楽な印象もあり
- 上記のように画面上でグラフィカルに関連が描画される。場合によってはGRANTを打ってると整理が追いつかなさそうなので、これはGUI上でもありかも
作成したロールにテーブルの権限などをつけるのも結構面倒
- 例えばdevロールにuser_infoテーブルのread only権限をまずつけようと思ったら下記をやる必要がある（末端のテーブルに着ければ一括で反映してくれる方法もあるかも）

	１．対象データベースにusageの権限を付与する
	２．対象スキーマにusageの権限を付与する
	３．対象テーブル（user_info）にselectの権限を付与する

試しにdevとpii_devのロールから、クエリを叩いてみる

masking policyが上記SQLによってしっかり反映されている状態（nameにname_maskが設定されている）

devの場合

SQLを叩いた結果、しっかりとmaskされている

pii_devの場合

SQLを叩いた結果、maskがはずれている

5. dbt側でのマクロ化とrun/表示

dbtマクロでのポリシーの作成

dbt_snow_maskを使う
ただ作成したいmasking policyの数だけ、マクロ配下にファイルを切る必要も出てくるのでちょっと...という感じもあり
作成された masking policy のカラム反映(set)のみ利用しようかなという感じ

gitlabのdbtマクロを参考にする

gitlab_snowflake.create_masking_policyをほぼそのまま

domainという概念を追加し、domain粒度で次のget_maskを呼び出し、masking内容の振り分けをする

create_masking_policy_update.sql

{%- macro create_masking_policy(database, schema, domain, data_type, policy) -%}

    {%- set mask = get_mask(domain) -%}
    {% set body %}
  CASE
    WHEN IS_ROLE_IN_SESSION('{{ policy }}') THEN val -- Set for the user to inherit access bases on there roles
    ELSE {{ mask }} 
  END 
    {% endset %}

    {% set policy_name %}
"{{ database }}".{{ schema }}.{{ policy }}_{{ domain }}
    {% endset %}

CREATE MASKING POLICY IF NOT EXISTS {{ policy_name }} AS (val {{ data_type }}) 
  RETURNS {{ data_type }} ->
  {{ body }};

/*
-- policyの設定までは実施しない
ALTER MASKING POLICY IF EXISTS {{ policy_name }} SET BODY -> 
  {{ body }};
*/

{%- endmacro -%}

gitlab_snowflake.get_mask

domain単位で masking policy をまとめて条件分岐として作成しておく

get_mask_update.sql

{%- macro get_mask(domain, policy=none) -%}

  {# Default maksing for each domain type #}

  {% if domain == 'NAME' %}
    {% set mask = '\'***MASKED***\''%}

  {% elif domain == 'DATE_JA_TEXT' %}
    {% set mask = "substr(val,0,8)" %}

  {% elif domain == 'MAIL' %}
    {% set mask = "regexp_replace(val,'.+\@','*****@')" %}

  {% elif domain == 'NUMBER' %}
    {% set mask = "substr(val,0,3) || '********'" %}

  {% elif domain == 'POSTAL' %}
    {% set mask = "substr(val,0,3) || '-****'" %}

  {% elif domain == 'ADDRESS' %}
    {% set mask = "substr(val,0,3) || '-***'" %}

  {% elif domain == 'HIGH_PERSONAL' %}
    {% set mask = '\'***MASKED***\'' %}

  {% else %}
    {% set mask = 'NULL'%}
  
  {% endif %}

  {{ return(mask) }}

{% endmacro %}

上記マクロを通して事前にmasking policyは作成しておく
- policyのみを別途loopでdrop/createするような機構は作っても良さそう

models/user_models/schema.yml

上記のマクロでは、先に作成したpiiのRoleでmasking判定のロジックを切ったため、policyの名前は　pii_xxx　(xxxの部分はdomain名が入る)となっているはず
そのpolicy名をschema側のmeta情報に設定していく（この内容をもってdbt_snow_mask側でカラムにpolicyのsetを行ってくれる）
2. ポリシーの検討 で検討したものがおおむね反映されている


version: 2

models:
    - name: user_info
      description: "use as datamart"
      columns:
          - name: name
            meta:
              masking_policy: PII_NAME
          - name: name_hira
            meta:
              masking_policy: PII_NAME
          - name: name_kana
            meta:
              masking_policy: PII_NAME
          - name: age
          - name: mail
            meta:
              masking_policy: PII_MAIL
          - name: birth_day
            meta:
              masking_policy: PII_DATE_JA_TEXT
          - name: address
            meta:
              masking_policy: PII_ADDRESS
          - name: boodtype
          - name: cell_phone_number
            meta:
              masking_policy: PII_NUMBER
          - name: company_name
            meta:
              masking_policy: PII_NAME
          - name: expire_date
            meta:
              masking_policy: PII_HIGH_PERSONAL
          - name: gender
          - name: my_number
            meta:
              masking_policy: PII_HIGH_PERSONAL
          - name: postal_code
            meta:
              masking_policy: PII_POSTAL
          - name: tel_number
            meta:
              masking_policy: PII_NUMBER

models/user_models/user_info.sql

最期にモデル側でテーブルを作成
事前に取りこんだデータはデータレイクに格納した扱いとして、新しくデータマートに作成する形とした
post_hook部でpolicyのcolumnへのsetを命令している、作成したテーブルへdev_roleでのSELECTの権限も追加（このroleにはPIIの権限は踏襲されていないので、maskされた状態での表示となるはず）
snowflakeのデータベースをdev/prd単位で分けたので、変数(var:dev_or_prd)を実行時に渡すようにする

user_info.sql


{{ config(
    post_hook=[
      "{{ dbt_snow_mask.apply_masking_policy('models') }}"
      "grant select on {{ this }} to role dev_role"
    ] 
) }}

{{ config(materialized='table') }}

select
  "name" as name,
  "name_hira" as name_hira,
  "name_kana" as name_kana,
  "age" as age,
  "mail" as mail,
  "birth_day" as birth_day ,
  "gender" as gender ,
  "blood_type" as blood_type ,
  "tel_number" as tel_number ,
  "cell_phone_number" as cell_phone_number ,
  "postal_code" as postal_code ,
  "address" as address ,
  "company_name" as company_name ,
  "expire_date" as expire_date ,
  "my_number" as my_number
from
{{ var("dev_or_prd") }}_app.datalake."user_info"

dbt run と確認

とりあえず変数を指定して、データマートを使ってみる
Snowflake側のコンソールで確認

dbt deps
dbt run --vars '{"dev_or_prd": "dev"}'

accountadminで見た場合

dev_roleで見た場合(pii権限なし)

想定通りの動きはしていそう！！

6. troccoでdbtも含めたワークフローを組む

これまで作ったものをtrocco上でワークフロー化します

trocco転送ジョブ

1. ダミーデータの作成と取り込み　のジョブをそのまま利用
変数にdev/prdが指定できるようにした上で、snowflakeのデータベースレベルで切り替えられるように設定

trocco上におけるdbtジョブ

5. dbt側でのマクロ化とrun/表示 の内容をそのままジョブ化
dev/prd指定でtargetを使わなかったのは、trocco側で動的な変数として設定ができなかったのでdbt run側のオプションであるvarを一旦無理やり渡す形で設定

troccoでのワークフロー

上記で作成したテーブルをワークフロー化
事前に作成したテーブルなどは丸ごと削除し、環境変数を指定の上で実行！
（ただし、masking policyについては事前に環境に設定済みの状態からスタート）
正常終了の上、生データの配置 ~ 権限のついたmasking済みデータマートの作成までが完了しました

さいごに

気が付けばスコープが広がる一方でしたが、なんだかんだでやっぱり便利だなと感じる検証でした。
今回は少し古めかしいDomain観点でのpolicy作成を検討してみましたが、maskingを適用するカラムが実際にはあまり多くないことなども踏まえると、gitlab側のget_maskにあるようなデータ型でのmasking設定などが一番効率的なのかなという印象です。

ただ、カラムレベルでしっかりとpolicy情報が付いていると整っている感があって個人的には好きですw

おわり🙇

今回のモチベーション

0. 準備

Snowflake

dbt

trocco

1. ダミーデータの作成と取り込み

下記のサイトを使用した、便利！

2. ポリシーの検討

3. ポリシーを作る

4. ロールの作成と継承

試しにdevとpii_devのロールから、クエリを叩いてみる

devの場合

pii_devの場合

5. dbt側でのマクロ化とrun/表示

dbtマクロでのポリシーの作成

gitlabのdbtマクロを参考にする

gitlab_snowflake.create_masking_policyをほぼそのまま

gitlab_snowflake.get_mask

models/user_models/schema.yml

models/user_models/user_info.sql

dbt run と 確認

accountadminで見た場合

dev_roleで見た場合(pii権限なし)

想定通りの動きはしていそう！！

6. troccoでdbtも含めたワークフローを組む

trocco転送ジョブ

trocco上におけるdbtジョブ

troccoでのワークフロー

さいごに

Discussion

dbt run と確認