はじめまして、こんにちは。データエンジニア部の中森です。

今回は表題の通り、SnowflakeのEnterprisエディション以上で使える機能の、行毎にユーザへの閲覧権限を設定できるrow access policy(https://docs.snowflake.com/ja/user-guide/security-row-intro.html)について設定をしてみます。
複数の部署や会社に跨った数値があり、それぞれに属する人にはその属している組織データを見せたいがその他の組織データは見せたくない、しかし全体を統括する人には見せたいというシーンでの利用を想定しています。

行レベルセキュリティの概要

公式サイト(https://docs.snowflake.com/ja/user-guide/security-row-intro.html)に

Snowflakeは、行アクセスポリシーを使用してクエリ結果で返す行を決定することにより、行レベルのセキュリティをサポートします。行アクセスポリシーは、1つの特定のロールが行を表示できるようにするために比較的単純な場合もあれば、クエリ結果の行へのアクセスを決定するためにポリシー定義に [マッピングテーブル](https://en.wikipedia.org/wiki/Associative_entity)を含めるときのように、より複雑な場合もあります。
行アクセスポリシーは、次の型のステートメントから、テーブルまたはビューの特定の行を表示できるかどうかを決定する、スキーマレベルのオブジェクトです。

とあるように、特定のロールやユーザに対して行を参照できるようにする単純なパターンや、マッピングテーブルを用いた複雑なアクセス制御を表現することができます。
また、スキーマレベルのオブジェクトということで、テーブルやビューに対して個別にアクセスポリシーを設定できます。

利用想定条件

今回は、下記のような3つのテーブルを想定しています。

・PROJECT_SALES
プロジェクト毎の売り上げを管理するテーブル。このプロジェクトに関わっている人にのみ参照権限を渡したい、数字の実体となるテーブル。

・PROJECT_STAKEHOLDER
　プロジェクトに関わっていた人の名前が登録されているテーブル。ここに名前のあるユーザに参照権限を渡したいのでマッピングテーブルとなる。

・STAKEHOLDER_RANK
　プロジェクトに関わっていた人でなかったとしても、プロジェクトオーナー(PO)であればそのプロジェクトに関する参照権限を付与する特例のマッピングテーブルとなる。

また、システム管理者としてACCOUNTADMINロールの場合も特例として全ての行の参照権限を持たせるようにします。
プロジェクト単位でのアクセスポリシーを想定していますが、利用シーンに合わせて適宜部署や会社に読み替えれば問題なく使用できると思います。

データ準備

今回ログインしているユーザ名を `test-user@datumstudio.jp` とします。またこのユーザにはACCOUNTADMINとSYSADMIN権限が付与されているものとします。
次に上段で記載したテーブルと、そのデータを準備していきます。

PROJECT_SALES

プロジェクトID1から5まで採番し、それぞれ適当な売り上げ数値をいれておきます。

USE ROLE SYSADMIN;

CREATE OR REPLACE TABLE sandbox.project_sales (
  id INTEGER AUTOINCREMENT
  , sales INTEGER DEFAULT 0);

INSERT INTO sandbox.project_sales (id, sales)
  VALUES (1, 100), (2, 200), (3, 300), (4, 400), (5, 500);

PROJECT_STAKEHOLDER

マッピングテーブルです。プロジェクトID1から4までにそれぞれユーザ名を割り振ります。
今回テスト用のユーザはプロジェクト1と3に関わっていて、project_ownerはプロジェクト4に関わっている想定です。

CREATE OR REPLACE TABLE sandbox.project_stakeholder (
  id INTEGER
  , name STRING);
  
INSERT INTO sandbox.project_stakeholder (id, name)
  VALUES (1, 'test-user@datumstudio.jp'), (1, 'hoge'), (2, 'hoge')
  			, (3, 'test-user@datumstudio.jp'), (4, 'piyo'), (4, 'project_owner');

STAKEHOLDER_RANK

もう一つのマッピングテーブルです。project_ownerのみにPOフラグをつけています。
このユーザは関わっているプロジェクト以外にもデータ参照権限が必要という要件です。

CREATE OR REPLACE TABLE sandbox.stakeholder_rank (
  name STRING
  , is_po BOOLEAN);
  
INSERT INTO sandbox.stakeholder_rank (name, is_po)
  VALUES ('test-user@datumstudio.jp', 0), ('hoge', 0), ('hoge', 0), ('piyo', 0), ('project_owner', 1);

ROW ACCESS POLICY 構文について

row access policyの実際の使い方についてはこちらにあります。

行アクセスポリシーの使用

最もシンプルなパターンとしては下記のような、必ずtrueを返し全ての行が参照できるようなポリシーになります。

CREATE ROW ACCESS POLICY test AS (id INTEGER) RETURNS BOOLEAN ->
  TRUE

ポリシー名の後ろのasがどのパラメータを用いてアクセス制御を行うかの引数になり、複数の値を受け取れます。
アロー(->)の先が条件文となり、具体的な権限制御はここにBOOLEAN値のSQL式（UDF含む）を記述します。

要件を満たすポリシーとその適用

今回の要件は、「POはどのプロジェクトも参照でき、その他は関わっていたプロジェクトのみ参照できる。またACCOUNTADMINロールであれば無条件に参照できる。」となっています。これをSQLに起こすと下記のようになります。

CREATE OR REPLACE ROW ACCESS POLICY sandbox.rap_test as (project_id INTEGER) RETURNS BOOLEAN ->
  current_role() = 'ACCOUNTADMIN'
    OR CURRENT_USER() IN (SELECT name FROM sandbox.stakeholder_rank WHERE is_po = 1)
    OR EXISTS (
      SELECT 1 FROM sandbox.project_stakeholder
        WHERE id = project_id
          AND name = CURRENT_USER()
    )

OR句で条件をつなげて、どこかに引っかかった場合TRUEを返す(=データ参照できる)ようなポリシーになります。
CURRENT_USERがログインしているアカウントのユーザ名になり、それとマッピングマスタにある名前と一致しているか判定しています。
DESCRIBEコマンドで登録がされているか確認できるので念のため確認しておきます。

DESCRIBE ROW ACCESS POLICY sandbox.rap_test

name	signature	return_type	body
RAP_TEST	(PROJECT_ID NUMBER)	BOOLEAN	current_role() = 'ACCOUNTADMIN'      or current_user i...

`RAP_TEST` として登録されていますね！signatureとして引数が定義さてていて、アローの先の条件文がbodyとして登録さているようです。
次に、このポリシーをPROJECT_SALESテーブルに適用していきます。

ALTER TABLE project_sales
  ADD ROW ACCESS POLICY sandbox.rap_test ON (id)

今回参照権限を絞りたい対象となるPROJECT_SALESにADDします。その際に、idカラムでバインドするよう設定します。
ポリシーが適用されているかは下記クエリで確認ができます。REF_ENTITY_NAMEカラムにPROJECT_SALESの名前があればOKです。

SELECT
  *
FROM TABLE (
  information_schema.policy_references(
    policy_name=>'rap_test'
  )
);

パラメータを書き換え挙動確認

長くなりましたが、事前準備が完了したので実際にSQLを叩いて挙動を確認していきます。

ACCOUNTADMINで全てのレコードが参照できる

USE ROLE ACCOUNTADMIN;

SELECT * FROM PROJECT_SALES;

ID	SALES
1	100
2	200
3	300
4	400
5	500

とりあえず全件見れています。これだけだとポリシーが効いているのかわかりませんが要件達成です。

SYSADMINの非POで、関連対象となるレコードのみ参照できる

これが本命です。

USE ROLE SYSADMIN;

SELECT * FROM PROJECT_SALES;

ID	SALES
1	100
3	300

お！！ちゃんと想定通り、プロジェクトID1と3のみが閲覧できていますね！！

SYSADMINのPOで、関連対象外のレコードも参照できる

最後にプロジェクトオーナー条件を確認していきます。

UPDATE stakeholder_rank SET is_po = 1 WHERE name = CURRENT_USER();

SELECT * FROM PROJECT_SALES;

ID	SALES
1	100
2	200
3	300
4	400
5	500

POフラグを立てることで、全件参照できるようになりました。
これで、要件3つを満たしたポリシーが作成できたと思います。

終わり

マッピングテーブルを使ったrow access policyであれば、複雑な権限制御ができることを紹介しました。
今回紹介したrow access policyとSSOを組み合わせることで、業務ルールに沿った権限制御が簡単に実現できます。
どれくらいパフォーマンスに影響があるのか、クエリ順によって影響はあるのか等気になる点があると思いますので、もし機会があればご紹介します。
その他Snowflakeのセキュリティについてご相談がございましたら、お問い合わせフォームからご連絡ください。
ではまた！