こんにちは、喜田です。

複雑なSnowflakeのデータクリーンルーム（Data Clean Room：DCR）をしっかり理解することを目指して、極力親切に、DCRに期待される機能や構築に必要なパーツを紐解いて解説していきます。

この記事では、SnowflakeでDCRを構築するために非常に重要な行アクセスポリシー（Row Access Policy：RAP）の動作を誰でも簡単に試せるサンプルを用いながら詳しく紹介します。

行アクセスポリシーとは

前回の記事でも記載しましたが、行アクセスポリシーは実行者の権限やコンテキストを読み取って、権限にマッチする行だけを見せるWHERE条件を強制的に付与する機能で、テーブルにアタッチして使います。
RAPがアタッチされたテーブルは一見して普通にSELECTできているように見えていても、実は実行者の権限にあった適切な行だけが絞られて返されるというものです。

さらっと登場した聞きなれない言葉「コンテキスト」について解説します。

SnowflakeではSQL文1実行につき、その実行者や接続情報、時刻情報、実行しようとしているSQL文そのもの、その他もろもろをコンテキストと呼ばれる内部的な管理情報として認識します。

私のSnowflake環境で現在のコンテキストを確認すると、以下のような感じです。

SELECT current_timestamp()
      ,current_ip_address()   -- IP、アカウント識別子、ユーザ名などの接続情報
      ,current_account()
      ,current_user()
      ,current_role()         -- ロール、DB、スキーマ、ウェアハウスはSnowflakeで
      ,current_database()     -- クエリ実行時に必ず意識するおなじみのコンテキスト
      ,current_schema()
      ,current_warehouse();

また現在実行中のSQLについても以下のように取得できます。通常のSQLの結果（今回で言うと行数をカウントした結果「3」）に並んで、実行したこのクエリ自身が「current_statement」によって取得されています。

SELECT count(*),current_statement() FROM mytable;

行アクセスポリシーに話を戻すと、クエリ実行時に「誰が」「どんなSQLで」アクセスしてきているかコンテキストを判断して、テーブル側で事前に定義したルールに従ってWHERE句を強制する機能と言い換えることができます。

ルールの作り方次第で、未定義の場合は結果をゼロ件にしたり、特定ロールで実行しているなど特別なケースではWHERE条件をつけないでデータを全件表示する（ルール制御をバイパスする）ような書き方が可能です。

行アクセスポリシーの実装

あるテーブルの所有ロール「data_owner」とSQL開発者ロール「developer」がいて、各開発者は自身の名前のユーザーでSnowflakeにログインします。

ログインユーザーとロールを判別して、以下のように振る舞うテーブルを作成します。

• data_ownerロールでアクセスしている場合は全行を見ることができる（ルール制御をバイパス）
• ルールで未定義のユーザは結果ゼロ件
• ルールで定義された「許可ユーザリスト」に含まれていれば自身の社員番号に関する行だけを見ることができる（強制WHERE句付与）

サンプル環境の準備

上記のロールを作成し、ユーザーにGRANTしておきます。

USE ROLE useradmin;
CREATE ROLE data_owner;
GRANT ROLE data_owner TO USER Kosuke_Kida;
CREATE ROLE developer;
GRANT ROLE developer TO USER Kosuke_Kida;

次に検証用データベース、テーブル等を作成し、上記ロールにusageを与えます。例として実施しているだけなので、このあたりは参照できれば何でも良いです。

USE ROLE sysadmin;
GRANT usage ON WAREHOUSE my_wh TO ROLE data_owner;  -- WHのusageを付与
GRANT usage ON WAREHOUSE my_wh TO ROLE developer;

CREATE DATABASE my_db;
GRANT create schema,usage ON DATABASE my_db TO ROLE data_owner;
 -- スキーマやテーブルを作成
GRANT usage ON DATABASE my_db TO ROLE developer;
 -- usageのみ

USE ROLE data_owner;
USE DATABASE my_db;
CREATE SCHEMA my_schema;
  -- data_ownerでスキーマ作成
GRANT usage ON SCHEMA my_schema TO ROLE developer;
  -- developerのスキーマ利用を許可

USE SCHEMA my_schema;
CREATE TABLE mytable (id integer,value varchar); -- テーブルとデータを準備
INSERT INTO mytable VALUES
     (1111,'この行はkidaのみ見れます')
    ,(2222,'この行はsample2のみ見れます')
    ,(4444,'この行は誰も見れません');

GRANT select ON TABLE mytable TO ROLE developer;
　-- developerであればこのテーブルをselect可能

ここまでで検証用データが整いました。

図左上のmytableにあたるテーブルを用意しましたので、あとは実行者のコンテキストに応じて結果を出し分けるところを行アクセスポリシーで実装していきます。

まずはマッピングテーブルを作成する

図左下にあるRAP制御テーブルと書いてきたものを作成します。

この時点ではあくまでも普通のCREATE TABLEです。テーブル名も何でも良いですし、作成者は元テーブルの作成者であるdata_ownerとしました。

USE ROLE data_owner;
USE SCHEMA my_schema;
CREATE TABLE rap_mapping_table (empno integer,login_name varchar);
INSERT INTO rap_mapping_table VALUES
      (1111,'KOSUKE_KIDA')
     ,(2222,'SAMPLE2')
     ,(3333,'SAMPLE3');

少し図とデータは違いますが、同じ構造です。

SELECT * FROM rap_mapping_table;

mytableには下図左側のデータが入っていました。マッピングテーブルは右側の3行を登録。
横並びにしてみるとイメージしやすいと思いますが、1111はマッピングテーブル上に該当ユーザーが登録されている、4444という人はマッピングテーブル上で未定義ですので、そのWHERE条件が自動的に付与されることなく、WHERE条件なしのバイパス実行した場合のみ見れる行です。

いよいよ行アクセスポリシーを作成

実際に上記データで動作する行アクセスポリシーを作成します。全文は以下の通りです。
このあと1行ずつ説明します。

CREATE OR REPLACE ROW ACCESS POLICY rap_by_empno
AS (filter_value integer) RETURNS BOOLEAN -> 
'DATA_OWNER' = current_role()
  OR
EXISTS ( SELECT 1 FROM rap_mapping_table
             WHERE login_name = current_user()
             AND empno = filter_value);

まず全体像について

CREATE ROW ACCESS POLICY文で、一つのオブジェクトとして作成されます。
中身はboolean値を返す関数で、各行について、filter_valueと名付けた列値をインプットに「->」以下の条件の真偽を返すような動作です。

filter_valueについて
Snowflakeのマニュアルではサンプルデータの列名に似た名前がついていて少々読み解きづらいのですが、このfilter_valueこそが名前の通り「強制WHERE条件」に与えるインプットになりますので超重要です。
本記事では、一貫して「強制WHERE条件を付ける」イメージで説明していきますので、この値がWHERE句に入るフィルター条件 WEHRE id ＝xxx の部分と思ってもらうと理解しやすいと思います。
実際には実行されるSQLが内部でWHERE句付きにリライトされるわけではなく、このようにテーブル側で1行1行評価していくということがわかります。

あとは「->」以下の条件に付いて解説します。テーブルの振る舞いを以下のように決めました。

• data_ownerロールでアクセスしている場合は全行を見ることができる（ルール制御をバイパス）
• ルールで未定義のユーザは結果ゼロ件
• ルールで定義された「許可ユーザリスト」に含まれていれば自身の社員番号に関する行だけを見ることができる（強制WHERE句付与）

第1の条件：data_ownerロールではルール制御をバイパス

単純ですが、'DATA_OWNER' = current_role() の行がそれにあたります。
SnowflakeはSQL実行時にコンテキストを理解していて、実行者のcurrent_roleに応じた判断ができます。
この後のEXISTS句をOR条件でつないでいるので、実行者が'DATA_OWNER'であればRAP全体が真で終わります。これがEXISTS以下のルールを無視するための書き方のポイントになります。

第2の条件：ルールで未定義のユーザは結果ゼロ件

EXISTS句は、その中の結果が1件以上あるか、0件かで真偽が変わりますので、

SELECT 1 FROM rap_mapping_table WHERE login_name = current_user()

によって得られる結果が0件（RAPマッピングテーブルに許可ユーザーとして記載がない）場合はEXISTSの判定で常に偽となります。

第3の条件：許可ユーザリストに含まれていれば、自身の社員番号に関する行だけ見れる

SQLを実行しているKosuke_Kidaユーザーはコンテキストとしてcurrent_user()に渡され、マッピングテーブル（許可ユーザリスト）のkidaの行を探します。ここからempnoを取得し、filter_valueに一致すればその行についての条件判定が「真」になり結果を返すことができます。

SELECT 1 FROM rap_mapping_table WHERE login_name = current_user()
AND empno = filter_value

結果として、WHERE ID = 1111 の条件が付与されたことと同等の結果が返ります。

これでRAPによる3つの振る舞いを定義できました。

テーブルにRAPを適用する

最後にテーブル mytableに RAPをアタッチします。理解しておくべきことは強制WHERE条件の対象にする列名を指定するということぐらいで、単純な構文です。

ALTER TABLE mytable
ADD ROW ACCESS POLICY rap_by_empno ON (id);

ちなみにポリシーの除去は以下の通りDROPで行います。

ALTER TABLE mytable
DROP ROW ACCESS POLICY rap_by_empno;

動作確認

第1の条件：data_ownerのときバイパスする

USE ROLE data_owner;
SELECT current_role(),current_user();
SELECT * FROM mytable;

第2の条件：未定義のユーザーでは結果が表示されない

USE ROLE useradmin;
GRANT ROLE developer TO USER exture;
USE ROLE developer;
USE DATABASE my_db;
USE SCHEMA my_schema;
USE WAREHOUSE my_wh;
SELECT current_role(),current_user();
SELECT * FROM mytable;

この検証以外のところで作ってあったextureさんを急遽召喚しましたｗ
developerロールを付与して検索したところ、結果は0件

第3の条件：許可ユーザーからの検索では強制WHERE条件が適用される

USE ROLE developer;
SELECT current_role(),current_user();
SELECT * FROM mytable;

期待する動作がちゃんとできているようです。

ちなみにこの時、ふと気になってクエリプロファイルを見ると以下のように、実テーブル＋Row Access Policyによる「DynamicSecureView」なるものに対して自動的にアクセスしていました。

行アクセスポリシーまとめ ＆ やっとデータクリーンルームの話へ

ここまで長文にお付き合いいただきありがとうございました！笑

さて、この記事の本来のテーマはデータクリーンルームです。覚えてましたか？！？！でも本質的な理解のためには絶対に必要な行アクセスポリシーについて、マニュアル以上に詳しく書いたつもりです。

ポリシーで振る舞いを制御するために重要な「コンテキスト」について紹介し、実際にポリシーを使う例を紹介しました。

次回予告を兼ねて、もう一つ重要なポイントに触れて今回は終わります。

コンテキストの例で紹介した、

SELECT current_timestamp()
,current_ip_address() — IP、アカウント識別子、ユーザ名などの接続情報
, current_account()

SELECT count(*),current_statement() FROM mytable;

行アクセスポリシーを理解してしまえば、上記のような「クエリ実行者のアカウントが特定のアカウントならOK」とか「今まさに実行しているクエリが事前に許可したものであればOK」という制御もできてしまいます。

アカウントをまたぐ＝データシェアリングによって公開したデータを誰かに見せるという状況ですね。次回、この二つを駆使してデータクリーンルームを実装していきます。