Snowflakeの集約ポリシーと投影ポリシーを試してみた

分析屋の中田(ナカタ)です。
Snowflakeの新機能、集約ポリシー・投影ポリシーを試してみました。

集約ポリシー・投影ポリシーとは

集約ポリシー・投影ポリシー | Snowflake Documentation

February 15, 2024 — Aggregation and Projection Policies Release Notes | Snowflake Documentation

テーブルへのアクセスを制御する機能です（プレビュー版）。
集約ポリシーはGROUP BYを強制し、生データへのアクセスを制御します。
投影ポリシーは列単位でアクセスを制御します。

今回やること

テーブルに集約ポリシーと投影ポリシーを設定します。
ロールによって挙動を変えるようにします。
ACCOUNTADMINなら制約を受けず、他のロールなら各ポリシーの制約を受けるようにします。

環境

Snowflakeのエディション：エンタープライズ版
クラウド：AWS（東京リージョン）
以降のクエリではデータベース名とスキーマ名を省略します。

事前準備

練習用のテーブルを作成します。
集約ポリシーの練習用と、投影ポリシーの練習用で合計2テーブル作成します。

CREATE OR REPLACE TABLE SAMPLE_TABLE (
    id VARCHAR,
    amount INTEGER
);

INSERT INTO SAMPLE_TABLE
VALUES('A1001',1000)
,('A1001',2000)
,('A1001',3000)
,('A1002',1000)
,('A1002',2000)
,('A1003',1000)
,('A1003',2000)
,('A1003',2000)
,('A1004',1000)
,('A1004',2000)
,('A1005',1000)
,('A1005',2000)
;

CREATE OR REPLACE TABLE SAMPLE_TABLE2 (
    id VARCHAR,
    amount INTEGER
);

INSERT INTO SAMPLE_TABLE2
VALUES('A1001',1000)
,('A1001',2000)
,('A1001',3000)
,('A1002',1000)
,('A1002',2000)
,('A1003',1000)
,('A1003',2000)
,('A1003',2000)
,('A1004',1000)
,('A1004',2000)
,('A1005',1000)
,('A1005',2000)
;

クエリ自体はテーブル名を変えただけで、内容は同じテーブルを作っています。
SAMPLE_TABLEに集約ポリシーを、
SAMPLE_TABLE2に投影ポリシーを設定しようとしています。

また、ロールACCOUNTADMINとSYSADMINのそれぞれでSELECT文を発行したときの挙動の違いを検証するため
予めスキーマのUSAGE権限と、テーブルへのSELECT権限を付与しておきます。

スキーマは以下の通り

各テーブルは以下の通り

①集約ポリシー

何はともあれ、いったんクエリの具体例を見てみましょう。
※スキーマでのSYSADMINの権限がUSAGEだけだと実行できません。
　ロールをACCOUNTADMINに切り替えて実行します。
　画面上だと、エディタ右上でロールの切り替えが可能です。

CREATE OR REPLACE AGGREGATION POLICY my_agg_policy
  AS () RETURNS AGGREGATION_CONSTRAINT ->
    CASE
      WHEN CURRENT_ROLE() = 'ACCOUNTADMIN'
        THEN NO_AGGREGATION_CONSTRAINT()
      ELSE AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 3)
    END;

このクエリでは、my_agg_policyという名前の集約ポリシーを作成しています。
そのポリシーの内容はCASE式で書かれており、

• 現在のロールがACCOUNTADMINなら制限なし

• 現在のロールがACCOUNTADMIN以外ならGROUP BYを強制。
  かつ、集約単位ごとに最低3行無いものはGROUP BYでも閲覧不可。

この説明ではピンときにくいので、とりあえずSAMPLE_TABLEテーブルにmy_agg_policyを設定してみます。
以下のクエリで、テーブルに対して集約ポリシーを設定できます。

ALTER TABLE SAMPLE_TABLE SET AGGREGATION POLICY my_agg_policy;

ちなみに集約ポリシーを作り直したいなどで、いったん集約ポリシーを解除したい場合は以下のクエリで可能です。

ALTER TABLE SAMPLE_TABLE UNSET AGGREGATION POLICY;

いよいよ、実際にロールごとにSELECT文を発行してみます。

まずはロールACCOUNTADMINでSAMPLE_TABLEのデータを参照してみます。

SELECT * FROM SAMPLE_TABLE;

ACCOUNTADMINは制約を受けないように集約ポリシーで定義していますので、通常通り結果が表示されます。

ロールをSYSADMINに切り替えて、同じSELECT文を発行してみます。

SELECT * FROM SAMPLE_TABLE;

結果は以下の通り、コンパイルエラーになります。

ロールACCOUNTADMIN以外はGROUP BYを強制されていますので
直接生データにアクセスできないようになっています。

では、GROUP BYで参照するとどうなるか試してみます。

SELECT 
    id,
    SUM(amount) 
FROM 
    SAMPLE_TABLE
GROUP BY id
;

結果は以下の通りです。

ここで、(MIN_GROUP_SIZE => 3) が効いています。
集約単位ごとに最低3行無いものはGROUP BYでも閲覧不可となっています。

今回の例だと
SAMPLE_TABLE内のid列でGROUP BYにて集約しています。
この場合、集約前の生データの段階でidごとに3行以上ない場合は
IDはNULLとして集約されています。

IDがA1001とA1003は元データで3行あるので、集約結果も表示されていますが、それ以外のIDは3行未満なので、NULLとしてまとめられています。

何だこの仕様は・・・と思う方もいるかもしれません。
元データの時点で集約単位ごとにある程度の行数がないと、GROUP BYしても結局生データを見ているのと同じ状況になるからかと思われます。

何らかの試験結果で
20代の受験者100人
30代の受験者1人
40代の受験者1人
のような極端なデータの場合
年代でGROUP BYして平均スコアを集計した結果は、20代以外は1人分の平均スコアが表示されてしまい、個人のスコアを直接見ているのと変わらなくなるためです。

②投影ポリシー

続いて投影ポリシーを試してみます。
こちらは集約ポリシーと比べて理解がしやすいです。

とりあえず投影ポリシーを定義します。
※スキーマでのSYSADMINの権限がUSAGEだけだと実行できません。
　この場合はロールをACCOUNTADMINに切り替えて実行します。

CREATE OR REPLACE PROJECTION POLICY mypropolicy
AS () RETURNS PROJECTION_CONSTRAINT ->
CASE
  WHEN CURRENT_ROLE() = 'ACCOUNTADMIN'
    THEN PROJECTION_CONSTRAINT(ALLOW => true)
  ELSE PROJECTION_CONSTRAINT(ALLOW => false)
END;

mypropolicyという名前の投影ポリシーを定義しています。
内容は

• 現在のロールがACCOUNTADMINなら、制限なし

• 現在のロールがACCOUNTADMIN以外なら、制限あり

というものです。

この投影ポリシーをSAMPLE_TABLE2のid列に設定します。

ALTER TABLE SAMPLE_TABLE2
MODIFY COLUMN id
SET PROJECTION POLICY mypropolicy;

ちなみに投影ポリシーの解除は以下のクエリです。

ALTER TABLE SAMPLE_TABLE2
 MODIFY COLUMN id
 UNSET PROJECTION POLICY;

さて、ロールごとにSELECT文を発行してみます。
まずはACCOUNTADMINで全行表示します。

SELECT * FROM SAMPLE_TABLE2;

ACCOUNTADMINには制約がないため
ばっちり表示されています。

SYSADMINに切り替えて実行します。

SELECT * FROM SAMPLE_TABLE2;

以下の通り、コンパイルエラーになります。

投影ポリシーを設定したid列を含むクエリは制約により表示されなくなります。
制約を指定していない、amount列であれば表示できます。

SELECT amount FROM SAMPLE_TABLE2;

最後に

いろんな関係者が個人情報もりもりのテーブルを参照する場合、情報漏えい対策としての用途が考えられるかと思いました。
マスキング機能とあわせて強固にデータ管理ができそうです。

ちなみに今回の検証で大変だったことは、ロールを何度も切り替える操作でした。

---

ここまでお読みいただき、ありがとうございました！
この記事が少しでも参考になりましたら「スキ」を押していただけると幸いです！

これまでの記事はこちら！

中田のSnowflake研究所｜分析屋｜note

株式会社分析屋について

弊社が作成を行いました分析レポートを、鎌倉市観光協会様HPに掲載いただきました。

鎌倉市の観光動向（鎌倉市観光協会調査） - 鎌倉市観光協会 | 時を楽しむ、旅がある。～鎌倉観光公式ガイド～

ホームページはこちら。

Top - 分析屋

noteでの会社紹介記事はこちら。

【データ分析で日本を豊かに】
分析屋はシステム分野・ライフサイエンス分野・マーケティング分野の知見を生かし、多種多様な分野の企業様のデータ分析のご支援をさせていただいております。 「あなたの問題解決をする」をモットーに、お客様の抱える課題にあわせた解析・分析手法を用いて、問題解決へのお手伝いをいたします！

【マーケティング】
マーケティング戦略上の目的に向けて、各種のデータ統合及び加工ならびにPDCAサイクル運用全般を支援や高度なデータ分析技術により複雑な課題解決に向けての分析サービスを提供いたします。

【システム】
アプリケーション開発やデータベース構築、WEBサイト構築、運用保守業務などお客様の問題やご要望に沿ってご支援いたします。

【ライフサイエンス】
機械学習や各種アルゴリズムなどの解析アルゴリズム開発サービスを提供いたします。過去には医療系のバイタルデータを扱った解析が主でしたが、今後はそれらで培った経験・技術を工業など他の分野の企業様の問題解決にも役立てていく方針です。

【SES】
SESサービスも行っております。