Snowflake の Projection policies （射影ポリシー）を試してみた

2024/2/15 のリリースでSnowflakeのProjection Policies （射影ポリシー）がパブリックプレビューとなりました。 （ [Feb 15, 2024 - Aggregation & Projection Policies](https://docs.snowflake.com/en/release-notes/2024/other/2024-02-15-policies) ）
本記事では実際にどのような動きになるのか、類似機能であるマスキングポリシー比較しながら確認します。

# 射影ポリシー (Projection policies) とは
ドキュメント [Projection Policies](https://docs.snowflake.com/en/user-guide/projection-policies) にある通り、テーブルやビューのカラムに対して、クエリ結果への出力可否をクエリを実行したロールなどに応じて制御するものになります。
SELECT 文を実行した際、指定のカラムが出力項目に含まれている場合にはクエリ実行自体が失敗し、結果を出力することができなくなるよう制約をかけることができます。

類似の機能としてマスキングポリシーがありますが、マスキングポリシーが指定のカラムを指定の値で置き換えた状態とするのに対し、射影ポリシーではあくまでカラムの値は維持し出力のみを抑制します。そのため、マスキングポリシーでは検索条件などカラムの値が利用できなくなるのに対し、射影ポリシーでは WHERE 句の指定など、出力以外の箇所では利用することができます。

なお、制約事項として、この機能は Enterprise Edition 以上の契約が必要となっています。

# 射影ポリシーの動作確認


## サンプルシナリオ
ここでは、サンプルとして以下のようなテーブルを結合するケースを試します。
・顧客テーブル：顧客情報としてメールアドレスと都道府県を格納
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3478922/5072ed36-acd3-599f-ed6d-f058aee5d860.png)
・アンケート回答テーブル：アンケートの回答結果として、メールアドレスとアンケートの識別番号、アンケートへの回答を格納
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3478922/a7465421-3299-1bf4-76f9-3c88c9cd8efe.png)
この2テーブルでメールアドレスをキーにして、アンケート回答結果に回答者の属性を顧客テーブルから追加します。
メールアドレスは個人情報のため、分析者（analyst）ロールには見せないように Projection Policy を設定します。

## ポリシーの作成、適用

分析者（analyst）ロールには射影を許可しない Projection Policy を作成します。
```SQL
CREATE OR REPLACE PROJECTION POLICY prjc_test
AS () RETURNS PROJECTION_CONSTRAINT ->
CASE
  WHEN CURRENT_ROLE() = 'ANALYST'
    THEN PROJECTION_CONSTRAINT(ALLOW => false)
  ELSE PROJECTION_CONSTRAINT(ALLOW => true)
END;
```

作成したポリシーを顧客テーブル（customers）、アンケート回答テーブル（survey_responces）の両テーブルのメールアドレスのカラムに適応します。
```SQL
ALTER TABLE customers
 MODIFY COLUMN email
 SET PROJECTION POLICY prjc_test;
```
```SQL
ALTER TABLE survey_responses
 MODIFY COLUMN email
 SET PROJECTION POLICY prjc_test;
```

## 実行結果
まず、テーブルのメールアドレスを含む全項目をSELECT文で表示するケースです。
```SQL
SELECT * FROM survey_responses;
```
下記のようなエラーとなり、クエリが実行されません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3478922/d1b8c0a3-4f49-38d2-d454-4a704396c3be.png)

ポリシーの対象となっているカラムを出力対象から外すと、問題なくクエリが実行できます。
```SQL
SELECT prefecture FROM customers;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3478922/bef72d1d-88ca-993d-8011-62e86f0a073c.png)

ポリシーの対象となっているカラムを検索条件に利用します。
```SQL
SELECT prefecture FROM customers WHERE email='customer001@example.com';
```
こちらも問題なく実行できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3478922/ac6302d4-c50c-27c3-b72d-5fa5b5d6e33f.png)

ポリシーの対象となっているカラムをキーにテーブルを結合します。
```SQL
SELECT
    customers.prefecture AS prefecture,
    survey_responses.servey_number AS servey_number,
    survey_responses.responce AS responce
FROM customers, survey_responses
WHERE customers.email = survey_responses.email
;
```
こちらも問題なく実行できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3478922/ad348d95-c26f-8a83-8621-66b6789f610d.png)

なお、マスキングポリシーでは、ポリシーで指定したカラムの値が指定の値に置き換えられるため、検索や結合には利用できなくなります。
```SQL
SELECT * from customers_masked;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3478922/a82f6c05-16d1-741f-669f-b99ab43ad436.png)

```SQL
SELECT * from customers_masked WHERE email='customer001@example.com';
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3478922/514a60ad-0fed-4fff-4534-6bfcb463be16.png)

マスクされたカラムをキーにした結合を試みると、マスクで置き換え後の値で結合され、元の値の場合とは異なった結果となります。
※マスキングポリシーで固定文字列への置き換えとせずに、ハッシュ値へ置き換えるなど、対応方法はあります。
```SQL
SELECT
    customers_masked.email AS email,
    customers_masked.prefecture AS prefecture,
    survey_responses_masked.servey_number AS servey_number,
    survey_responses_masked.responce AS responce
FROM customers_masked, survey_responses_masked
WHERE customers_masked.email = survey_responses_masked.email
;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3478922/9ac4a935-d1fe-37c0-8966-270b9ce362fe.png)


# まとめ
射影ポリシーによって、個人情報など秘匿すべき項目の出力を抑止した上で、検索条件や結合の条件として利用できることが確認できました。
これまで提供されていた機能で同様のことを実現するには、以下のような手法が考えられますが、パフォーマンスや利用の柔軟性、提供時のコストなどの面で射影ポリシーの利用が有利と思われます。
- マスキングポリシーで出力する値を固定値ではなくハッシュ値を指定する
- ハッシュ化済みの値で格納した別テーブルを用意する
- 想定される結合条件で結合済みのビューを用意する

一方で、射影ポリシーによる制限は、検索条件に利用できることから、特定のメールアドレスが存在するかなど特定の値を持ったレコードの存在は確認することができ、値の存在自体を秘匿したい場合には不十分となります。
どのレベルまで秘匿が必要なのかなど要件によって使い分けが必要と思われます。