dbtを用いた行・列ポリシーの付与(snowflake)
概要
snowflakeの機能である行アクセスポリシー,マスキングポリシーの簡単な説明とdbtを用いた付与の方法について紹介します。
※本記事ではdbtやsnowflakeの基礎的事項については既知として進めます。
機能・ユースケース
| 機能 | 用途 | 使用例 |
|---|---|---|
| 行アクセスポリシー | 閲覧可能な行を制御する | 職位や部署により見える行を制御する |
| マスキングポリシー | 列の値を制御する | 名前や住所などの個人情報をマスキング,ハッシュ化する |
公式ドキュメント
行アクセスポリシーについて
列レベルのセキュリティについて
環境構築(snowflake)
検証のため、以下の手順(概略)で簡単な環境を構築しました。
- ロール構成
accountadmin,sysadminに加え以下の2つのロールを作成し、それぞれsysadminにぶら下げる。
・ developer : 開発者用,dbtを操作する
・ viewer: 利用者用,martスキーマ内のテーブルをselectできる
- データベース/スキーマ構成
attach_policy_demo (データベース)
├─ mart (joinやgroup byをして加工したデータを格納するスキーマ)
└─ raw (生データを格納するスキーマ)
データの準備
以下の2テーブルをcsvファイルで用意し, dbt seed コマンドでrawスキーマにアップロードします。
※本来RawデータはS3等からsnowpipeやcopy into等を用いてロードするケースが多いですが,ここでは検証のためseed(csv)で用意します。具体的なデータについてはこの記事の末尾に記載します。
- 売上テーブル(amount): 売上を商品別に持つ
- 商品テーブル(item): 商品を販売している部署を持つ
- 顧客テーブル(customer): 顧客の名前を保持する
- 社員テーブル(employee): 社員とその所属部署を保持する
上のテーブルの関係は以下のようになっています。
売上テーブルと商品テーブルから部門別の売上(合計)を持つテーブルを作り, dbt runコマンドでモデルを作成します。
select
t2.dept_id
, sum(t1.amount) as amount
from
{{ ref('amount') }} as t1
inner join
{{ ref('item') }} as t2
on t1.item_id = t2.id
group by
t2.dept_id
developerロールでデータを確認します。
DEPT_ID,AMOUNT
d1,600
d2,1500
d3,2400
行アクセスポリシーの作成・付与
今回は次のような行アクセスポリシーを設定します。
| role | 閲覧可能な行 |
|---|---|
| accountadmin sysadmin developer |
すべて |
| viewer | 自身の所属する部署のデータのみ |
viewerロールは
- 部署1(d1)に所属するユーザーA
- 部署2(d2)に所属するユーザーB
- 部署3(d3)に所属するユーザーC
の3人に付与されており、各ユーザーが自身の所属する部門の行だけが閲覧可能ということを想定しています。
すでに作成している社員テーブルも用いて、以下の行アクセスポリシーをsnowflakeのコンソールから作成し、mart_amount_by_deptテーブルにアタッチします。
create or replace row access policy department_policy as (col varchar) returns boolean ->
current_role() = 'ACCOUNTADMIN'
or current_role() = 'SYSADMIN'
or current_role() = 'DEVELOPER'
or exists (
select 1 from attach_policy_demo.raw.employee
where col = dept_id
and name = current_user()
)
;
alter table attach_policy_demo.mart.mart_amount_by_dept
add row access policy department_policy on (dept_id);
そしてユーザーAでselectした場合, 1行のみ表示されました！
しかしここで問題が生じます。
ローカルやAirflow等のワークフローツールからdbt runでモデルを実行するたびに、アタッチしたポリシーが外れてしまいます。
dbtではgrants機能を用いて、簡易な権限をモデルの実行するたびに付与する機能は最近のアップデートで実装されてきていますが、行アクセスポリシーは現時点(2022年12月)では付与できないようです。
ここではpost-hook機能を用いて、モデルの実行毎に行アクセスポリシーをアタッチするようにモデルを修正します。
{{ config(
post_hook=["use schema mart","alter view {{this}} add row access policy department_policy on (dept_id);"]
) }}
select
t2.dept_id
, sum(t1.amount) as amount
from
{{ ref('amount') }} as t1
inner join
{{ ref('item') }} as t2
on t1.item_id = t2.id
group by
t2.dept_id
こちらのモデルをdbt runする後に、post_hookの中のクエリが流れ、行アクセスポリシーが付与されます。
post-hook機能の他の使い所の例についてはこちらにも記載があります。
マスキングポリシーの作成・付与
今回は次のようなマスキングポリシーを作成し、モデル(mart_amount_by_customer)の顧客名に対して設定します。
| role | 見え方 |
|---|---|
| accountadmin | そのまま |
| sysadmin developer viewer |
塗りつぶし |
ここではパッケージdbt_snow_maskを利用し、ドキュメントの通りに進めていきます。 まずpackages.ymlに必要なパッケージを記載し、dbt depsコマンドでインストールします。
packages:
- package: dbt-labs/dbt_utils
version: 1.0.0
- package: entechlog/dbt_snow_mask
version: 0.2.3
dbt_snow_maskのドキュメントに従い, macros/snow-mask-ddl/の下に以下のファイルを作成します。
create_masking_policy_〇〇という名前にする必要があります。
{% macro create_masking_policy_pii_policy(node_database,node_schema) %}
CREATE MASKING POLICY IF NOT EXISTS {{node_database}}.{{node_schema}}.pii_policy AS (val string)
RETURNS string ->
CASE WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN') THEN val
ELSE '**********'
END
{% endmacro %}
次にdbt_project.ymlにon-run-startに上記のcreate文が動作するように記載を,modelの部分のpost-hookにモデル作成後ポリシーをアタッチするよう記載します。
on-run-start:
- "{{ dbt_snow_mask.create_masking_policy('models')}}"
models:
attach_policy_demo:
raw:
schema: raw
mart:
schema: mart
post-hook:
- "{{ dbt_snow_mask.apply_masking_policy('models') }}"
顧客ごとに売上を合計するモデルを作成し、ユーザー名にマスキングするようymlファイルに記載します。
select
t2.name as user_name
, sum(t1.amount) as amount
from
{{ ref('amount') }} as t1
inner join
{{ ref('customer') }} as t2
on t1.customer_id = t2.id
group by
t2.name
マスキングを行うカラムにはpii_policyを付与すると記載します。ymlファイルに記載ができるので、どのカラムがマスキングされているか容易に確認ができます。
version: 2
- name: mart_amount_by_customer
columns:
- name: user_name
meta:
masking_policy: pii_policy
- name: amount
こちらでdbt runコマンドを実行すると以下のようになり、
Running 2 on-run-start hooks
1 of 2 START hook: attach_policy_demo.on-run-start.0 ........................... [RUN]
1 of 2 OK hook: attach_policy_demo.on-run-start.0 .............................. [OK in 0.00s]
2 of 2 START hook: attach_policy_demo.on-run-start.1 ........................... [RUN]
2 of 2 OK hook: attach_policy_demo.on-run-start.1 .............................. [OK in 0.00s]
Concurrency: 2 threads (target='dev')
1 of 2 START sql table model mart.mart_amount_by_customer ...................... [RUN]
2 of 2 START sql table model mart.mart_amount_by_dept .......................... [RUN]
2 of 2 OK created sql table model mart.mart_amount_by_dept ..................... [SUCCESS 1 in 3.89s]
21:45:14 | applying masking policy to model : ATTACH_POLICY_DEMO.MART.PII_POLICY on attach_policy_demo.mart.mart_amount_by_customer.user_name [force = False]
1 of 2 OK created sql table model mart.mart_amount_by_customer ................. [SUCCESS 1 in 4.77s]
Finished running 2 table models, 2 hooks in 0 hours 0 minutes and 8.92 seconds (8.92s).
accountadmin以外のロールでデータを確認すると,データがマスキングされていることが確認できました！
欠点としては、マスキングポリシーの作成者、付与者が開発者ロール(developer)になり、開発者がポリシーを削除したり、外したりしてデータを確認できてしまうというリスクがあります。
こちらは社内の個人情報管理ルールにおいて、開発者が個人情報を閲覧できないというルールの場合、懸念となるため、この付与方法は用いずaccountadminや個人情報管理用のロールでdbtとは別で付与する方がよいでしょう。
使用データ
id,customer_id,item_id,amount
a,c1,i1,100
b,c2,i1,200
c,c3,i2,300
d,c1,i3,400
e,c2,i4,500
f,c3,i5,600
g,c1,i5,700
h,c4,i6,800
i,c5,i7,900
j,c1,i8,1000
k,c2,i9,1100
id,dept_id
i1,d1
i2,d1
i3,d1
i4,d2
i5,d2
i6,d2
i7,d3
i8,d3
i9,d3
id,name
c1,佐藤
c2,鈴木
c3,高橋
c4,田中
c5,伊藤
id,name,dept_id
123,USER_A,d1
456,USER_B,d2
789,USER_C,d3
Discussion