投影ポリシーと集計ポリシーを適用したテーブルを Tableau から参照してみる #SnowflakeDB

特集

安原朋紀

2024.06.26

はじめに

2024年5月のリリースで、Snowflake でデータ保護のために使用できるポリシーである投影ポリシーと集計ポリシーが一般提供となりました。
こちらの機能について、概要をまとめつつ BI ツール（Tableau）からどのように見えるかを確認したく記事としました。

投影ポリシー

投影ポリシーをカラムに対して適用すると、ポリシー定義に基づき、そのカラムを選択するクエリを実行可能とするか、つまり指定のカラムをセレクト句に含めることを許可するかを制御できます。
以下は公式ドキュメント記載のポリシーの例です。この例の場合は、クエリを実行したユーザーのロールに基づいて、対象のカラムを選択可能かコントロールします。

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

保護したい個人情報などを含むカラムに対してマスキングポリシーを適用したり、そもそも保護したい列を含まないビューを定義した場合との違いとして、この機能により保護されたカラムはセレクト句に含めることはできませんが、例えば結合のキーとして使用することが可能です。

これにより、個人情報などを保護しつつレコードレベルで複数のテーブルにまたがる分析が可能となります。

投影ポリシーに関する主な特徴として以下があげられます。

  • SQL クエリの出力に列を投影できるかどうかを定義するスキーマ レベルのオブジェクト
  • カラムには一度に1つの投影ポリシーのみを割り当て可能
  • 投影ポリシーで保護したカラムに対して、マスキングポリシーを適用したり、対象のカラムを含むテーブルに対して行アクセスポリシーを適用することも可能
    • この場合「行アクセスポリシー」による行フィルター、「投影ポリシー」によるクエリの許可、「マスキング ポリシー」による列マスクが順に適用される
  • タグベースの投影ポリシーは現時点では使用できない（タグに投影ポリシーをセットすることはできない）
  • 仮想列または外部テーブルの VALUE 列には適用できない

詳細は以下をご参照ください。

集計ポリシー

集計ポリシーは、テーブルやビューに対して適用されます。この際、ポリシー定義に基づき対象のテーブルに対するクエリとしてグループに集計することを強制します。これにより、個々のレコード単位の情報を返すことが防がれ、行レベルのプライバシーとして機能します。

集計ポリシーの主な特徴は以下です。

  • テーブルまたはビューのデータにアクセスできるクエリの種類を制御する、スキーマレベルのオブジェクト
  • ウィンドウ関数は、集計制約付きテーブルまたはビューに対するクエリでは使用できない
  • テーブルまたはビューに関連付け可能な集計ポリシーは1つのみ

詳細は以下をご参照ください。

また、集計ポリシーを定義する際はあわせて以下を考慮します。

  • 最小グループサイズ
  • エンティティ キー

最小グループサイズ

例えば以下のようなテーブルに対して集計ポリシーを適用したとします。

| Username        | Department  | Salary           |
|-----------------|-------------|------------------|
| John Smith      | Sales       | 400              |
| Emily Johnson   | Sales       | 350              |
| Michael Brown   | Development | 450              |
| James Wilson    | Development | 500              |
| Linda Martin    | HR          | 320              |

ここで以下のクエリで部署ごとの平均 Salary を算出してみます。

SELECT Department, AVG(Salary) AS Average_Salary, COUNT(*) AS Employee_Count
FROM employees
GROUP BY Department;

集計ポリシーは、テーブルに対する集計を強制するため、上記のようなクエリは実行でき、この場合出力は以下のようになります。

| Department  | Average Salary  | Employee Count |
|-------------|---------------- |----------------|
| Sales       | 375             | 2              |
| Development | 475             | 2              |
| HR          | 320             | 1              |

最終行の部署 HR を見てみると、このグループ部署に属するレコード数は１なので、実質個人を特定可能な結果を得ることができています。
そこで集計ポリシーを定義する際はMIN_GROUP_SIZEとして集計時のグループに含まれる最小のレコード数を指定します。これにより、最小グループサイズよりも少ないレコードを含むグループがある場合、それらのグループに属するレコードを remainder group としてまとめ、結果を返します。
この際、グループのキー項目値は NULL とされます（複数のグループからなる可能性があるため）。さらに remainder group に分類してもMIN_GROUP_SIZEに満たない場合は、結果のすべてのフィールド値が NULL として返されます。
この例でMIN_GROUP_SIZEを2として設定すると HR グループが remainder group に分類されます。ただし、この場合でもMIN_GROUP_SIZEを満たすレコード数には足りないため、すべてのフィールドが NULL として出力されることになります。

| Department  | Average Salary  | Employee Count |
|-------------|---------------- |----------------|
| Sales       | 375             | 2              |
| Development | 475             | 2              |
| null        | null            | null           |

エンティティ キー

集計ポリシーではMIN_GROUP_SIZEとあわせてENTITY KEYを指定できます。
これにより集計グループに含まれるレコード数だけでなく、指定のキーについてMIN_GROUP_SIZE以上の種類数含まれるように制限することが可能となります。
先のデータに年の情報を追加した以下のような、年ごと部ごとに集計可能なテーブルがあるとします。

| USERNAME        | DEPARTMENT   | SALARY | YEAR |
|-----------------|--------------|--------|------|
| John Smith      | Sales        | 400    | 2023 |
| Emily Johnson   | Sales        | 350    | 2023 |
| Michael Brown   | Development  | 450    | 2023 |
| James Wilson    | Development  | 500    | 2023 |
| Linda Martin    | HR           | 320    | 2023 |
| John Smith      | Sales        | 400    | 2024 |
| Emily Johnson   | Sales        | 350    | 2024 |
| Michael Brown   | Development  | 470    | 2024 |
| James Wilson    | Development  | 520    | 2024 |
| Linda Martin    | HR           | 330    | 2024 |

ここで同じようにMIN_GROUP_SIZEとして2を指定した集計ポリシーを適用したとし、先と同じ集計クエリを実行すると以下の出力となります。

| DEPARTMENT  | AVERAGE_SALARY | EMPLOYEE_COUNT |
|-------------|----------------|----------------|
| Sales       | 375            | 4              |
| Development | 485            | 4              |
| HR          | 325            | 2              |

HR 部門については、一名の情報しかありませんが、複数年にまたがるレコードがあるためMIN_GROUP_SIZEの制約を満たし出力が返ってきます。しかし、この例では実質個人の Salary ができてしまっている状況です。
そこで集計ポリシーをテーブルに適用する際にENTITY KEYとして [USERNAME] カラムを合わせて指定します。その上で同じクエリを実行すると以下の出力となります。

| DEPARTMENT  | AVERAGE_SALARY | EMPLOYEE_COUNT |
|-------------|----------------|----------------|
| Sales       | 375            | 4              |
| Development | 485            | 4              |
| null        | null           | null           |

集約のレコード数は指定の条件を満たしていますが、部門でテーブルをグループ化した際に、ENTITY KEY として指定した [USERNAME] がのユニーク数が2未満のため remainder group に分類され、さらに十分なレコード数がないためにすべてのフィールド値が NULL として返されます。

環境の用意

ここでは以下のコマンドで検証用のデータベース・スキーマ、ロールを定義しました。

--データベース作成
USE ROLE SYSADMIN;
----サンプルデータ格納用DB
CREATE OR REPLACE DATABASE testdb;
----ポリシー管理用DB
CREATE OR REPLACE DATABASE governance;

--ロール作成
USE ROLE USERADMIN;
----policy管理者ロール
CREATE OR REPLACE ROLE policy_admin;
----データベース管理者
CREATE OR REPLACE ROLE prj_admin;

--権限付与
USE ROLE SECURITYADMIN;
----policy管理者ロール：スキーマを作成させることでポリシー作成権限を与える
GRANT OWNERSHIP ON DATABASE governance TO ROLE policy_admin;
----prj管理者ロール
GRANT OWNERSHIP ON DATABASE testdb TO ROLE prj_admin;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE prj_admin;
--ロール階層
GRANT ROLE policy_admin TO ROLE SYSADMIN;
GRANT ROLE prj_admin TO ROLE SYSADMIN;

--サンプルデータの作成
USE ROLE prj_admin;
USE WAREHOUSE COMPUTE_WH;
CREATE SCHEMA testdb.policy_test;

CREATE OR REPLACE TABLE Employees (
    employee_id INT,
    department VARCHAR(50),
    email VARCHAR(100)
);
INSERT INTO Employees (department, email)
VALUES
    ('Sales', 'john.doe@example.com'),
    ('Development', 'jane.smith@example.com'),
    ('Sales', 'emily.johnson@example.com'),
    ('HR', 'michael.brown@example.com'),
    ('Marketing', 'lisa.white@example.com'),
    ('Development', 'mark.black@example.com'),
    ('HR', 'susan.grey@example.com'),
    ('Marketing', 'james.green@example.com'),
    ('Legal', 'natalie.moss@example.com'),
    ('Research', 'brian.oak@example.com');

CREATE OR REPLACE TABLE Salaries (
    email VARCHAR(100),
    salary INT,
    year INT
);
INSERT INTO Salaries (email, salary, year)
VALUES
    ('john.doe@example.com', 50000, 2023),
    ('jane.smith@example.com', 55000, 2023),
    ('emily.johnson@example.com', 60000, 2023),
    ('michael.brown@example.com', 65000, 2023),
    ('lisa.white@example.com', 47000, 2023),
    ('mark.black@example.com', 62000, 2023),
    ('susan.grey@example.com', 58000, 2023),
    ('james.green@example.com', 53000, 2023),
    ('natalie.moss@example.com', 70000, 2023),
    ('brian.oak@example.com', 68000, 2023);

投影ポリシーの作成

サンプルデータとして作成した２つのテーブルには、個人情報として [email] が含まれています。個人を特定可能な [email] を保護しつつ、２つのテーブルを結合し部門単位での Salary の集計を行いたい、というような設定です。

ここでは以下のコマンドで投影ポリシーを作成しました。ポリシーの作成にはスキーマ内の CREATE PROJECTION POLICY 権限が必要ですが、上記でポリシー作成用のロールがスキーマの所有者となる構造のため、この権限を明示的に付与していません。

--ポリシー定義用ロールに切り替え
USE ROLE policy_admin;
CREATE SCHEMA governance.projection_policies;

--ポリシー定義
USE SCHEMA governance.projection_policies;

CREATE OR REPLACE PROJECTION POLICY my_projection_policy
AS () RETURNS PROJECTION_CONSTRAINT ->
CASE
  WHEN IS_ROLE_IN_SESSION('SYSADMIN')
    THEN PROJECTION_CONSTRAINT(ALLOW => true)
  ELSE PROJECTION_CONSTRAINT(ALLOW => false)
END;

ポリシーは、現在のロールが SYSADMIN 以上であれば制約無し、それ以外であればポリシーが適用される設定です。
ポリシーをカラムに適用するには、以下のいずれかの権限が必要です。

  • アカウントに対する APPLY PROJECTION POLICY 権限
  • 投影ポリシーに対して APPLY 権限を持ち、テーブルまたはビューに対して OWNERSHIP 権限を持つ

ここでは、アカウントに対する APPLY PROJECTION POLICY 権限を付与することでポリシーを適用しました。

--アカウントに対する APPLY PROJECTION POLICY 権限の付与
USE ROLE ACCOUNTADMIN;
GRANT APPLY PROJECTION POLICY ON ACCOUNT TO ROLE policy_admin;

USE ROLE policy_admin;
--ポリシーをemailカラムに関連付け
ALTER TABLE testdb.policy_test.Employees
 MODIFY COLUMN email
 SET PROJECTION POLICY governance.projection_policies.my_projection_policy;

この時の権限は下図の通りです。

この状態でテーブルに対して以下のクエリを実行してみます。

USE ROLE prj_admin;
USE SCHEMA testdb.policy_test;
SELECT * FROM employees;

結果は下図の通り [email] カラムが含まれているためにポリシーの制約よるエラーとなります。

権限のあるロールで同じクエリを実行すると結果が表示されます。

結合キーに使用する

投影ポリシーを適用すると、対象のカラムを選択するクエリは実行できませんが、結合キーとして使用することは可能です。つまり、以下のようなクエリが実行できます。

USE ROLE prj_admin;
SELECT e.department, s.salary 
FROM Employees e
JOIN Salaries s ON e.email = s.email;

ここで出力にポリシーを適用したテーブルカラムを追加するとクエリは実行できません。

USE ROLE prj_admin;
SELECT 
    e.email
    ,e.department
    ,s.salary 
FROM Employees e
JOIN Salaries s ON e.email = s.email;

注意点として、ポリシーはテーブルカラムに適用されるので、結合先のテーブルのキーにポリシーが適用されていない場合、クエリは通ります。このような場合は結合先のテーブルキーにもポリシーを適用する必要があります。

USE ROLE prj_admin;
SELECT 
    s.email
    ,e.department
    ,s.salary 
FROM Employees e
JOIN Salaries s ON e.email = s.email;

また、セレクト句で指定できないだけで以下のようなクエリは実行できるので注意します。

USE ROLE prj_admin;
SELECT 
    e.department
    ,s.salary 
FROM Employees e
JOIN Salaries s ON e.email = s.email
WHERE e.email like '%jane%';

集計ポリシーの作成

次に、テーブルに対するクエリとして集計を強制する集計ポリシーを作成してみます。

ここでは以下のコマンドでポリシーを作成しました。ポリシーの作成にはスキーマ内の CREATE AGGREGATION POLICY 権限が必要です。投影ポリシーと同様に、ポリシー作成用のロールがスキーマの所有者なので、この権限を明示的に付与していません。

--ポリシー定義用ロールに切り替え
USE ROLE policy_admin;
CREATE SCHEMA governance.aggregation_policies;

--ポリシー定義
USE SCHEMA governance.aggregation_policies;
CREATE AGGREGATION POLICY my_agg_policy
  AS () RETURNS AGGREGATION_CONSTRAINT ->
    CASE
      WHEN IS_ROLE_IN_SESSION('SYSADMIN')
        THEN NO_AGGREGATION_CONSTRAINT()
      ELSE AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 2)
    END;

現在のロールが SYSADMIN 以上であれば制約無し、それ以外であればポリシーが適用される設定です。MIN_GROUP_SIZE は 2 なので各グループに 2 レコード以上が含まれる必要があります。

集計ポリシーを適用するには、以下のいずれかの権限が必要です。

  • アカウントに対する APPLY AGGREGATION POLICY 権限
  • 集計ポリシーに対して APPLY 権限を持ち、テーブルまたはビューに対して OWNERSHIP 権限を持つ

ここでは、アカウントに対する APPLY AGGREGATION POLICY 権限を付与することでポリシーを適用しました。

--アカウントに対する APPLY PROJECTION POLICY 権限の付与
USE ROLE ACCOUNTADMIN;
GRANT APPLY AGGREGATION POLICY ON ACCOUNT TO ROLE policy_admin;

USE ROLE policy_admin;
--ポリシーをEmployeeテーブルに関連付け
ALTER TABLE testdb.policy_test.Employees
SET AGGREGATION POLICY governance.aggregation_policies.my_agg_policy;

この時のポリシー管理用ロールの権限は下図の通りです。

この状態で Employee テーブルに対して以下のクエリを実行してみます。

USE ROLE prj_admin;
USE SCHEMA testdb.policy_test;
SELECT DEPARTMENT,COUNT(*) AS cnt FROM employees GROUP BY DEPARTMENT;

結果は下図のようになります。元データでは、一部の部門でユーザーが一人しかいない（レコードが１レコードしかない）状態でした。MIN_GROUP_SIZEは2なので、レコード数が2に満たないグループ（ここでは [DEPARTMENT] ）については、 remainder group にまとめられ、NULL として出力されています。 remainder group にまとめられたレコード数は 2以上のため、[CNT] カラムは NULL とならずに値が返されています。

集計ポリシーが適用されているので、先のような集約を含まないクエリはエラーとなります。

結合

集計ポリシーが含まれたテーブルを結合する場合、集計制約付きテーブルから取得したレコード数が、そのテーブルの最小グループサイズ以上である必要があります。
例えば、サンプルデータは下図のようになっており、Legal などレコード数が１しかない部門があります。

以下のように結合先のテーブルを用意し左側の各レコードが右側のレコード数分増加する結合クエリを使用します。

WITH temp AS (
    SELECT 1 AS temp_column
    UNION ALL
    SELECT 2
    UNION ALL
    SELECT 3
)
SELECT e.DEPARTMENT, COUNT(*)
FROM Employees e, temp t
GROUP BY e.DEPARTMENT;

このクエリの結果としては、下図のようにもともと１レコードしかない部門もレコード数が増加します。
※ポリシーが適用されない権限のあるユーザーでクエリを実行

同じクエリをポリシーが適用されるロールで実行すると下図のようになります。

グループ化した際に、集計ポリシーが適用されている側のテーブルのレコード数は１で変わらないので、 remainder group としてまとめられます。

エンティティ キー

集計ポリシー設定時にエンティティキーを指定する場合は、ポリシー適用時にENTITY KEYとして指定可能です。

ALTER TABLE viewership_log
  SET AGGREGATION POLICY my_agg_policy
  ENTITY KEY (first_name,last_name);

※クエリは以下のドキュメントより引用

BI ツールから参照してみる

さいごに投影ポリシー・集計ポリシーを適用したテーブルを BI ツール（Tableau）から参照してみます。
※Tableau Desktop は 2024.1 を使用

Tableau からポリシーを反映させる場合は、ライブ接続を使用します。ポリシーが適用されるロールで接続するとカラム自体は確認できます。

ただし、[今すぐ更新] などで対象のカラムを表示しようとするとエラーとなります。

シート作成画面でも同様に、ポリシーが適用されます。Tableau ではデフォルトで集計がかかるので下図のように集計ポリシーが反映された結果が表示されます。

集計ポリシーが適用されているテーブルの場合、メジャーの集計を解除しようとするとエラーとなります。

投影ポリシーについても同様で、ポリシーが適用されたカラムのピルを追加するとエラーとなります。

権限のある（すべてのクエリやカラムを表示できる）ロールでワークブックをパブリッシュします。

ポリシーが適用されるロールでダッシュボードを表示するとダッシュボード自体を表示することができませんでした。

集計ポリシーのみが適用される場合は、下図のようにポリシーが適用された状態で表示可能です。

そのため投影ポリシーを適用させたカラムを含むテーブルを使用するダッシュボードをユーザーに提供する際は、そもそも対象のカラムを含めないように作成する必要があります（ユーザーの権限に応じて動的にカラムを表示・非表示などはできません）。

さいごに

投影ポリシーと集計ポリシーについて確認してみました。マスキングポリシーのように BI ツールからも反映されることも確認できました。 こちらの内容が何かの参考になれば幸いです。

スキルアップ|初心者向け|書評|デベキャン|AWS認定|スキルアップ|デベキャンだより|勉強会|コミュニティ

関連記事

Snowflakeでドキュメントデータを処理しよう！Document AIのチュートリアル「Tutorial: Create a document processing pipeline with Document AI」をやってみた

さがら

2024.06.25

Quickstart を利用して Snowflake における RAG ベースの LLM アシスタントの構築手順を確認してみる #SnowflakeDB

安原朋紀

2024.06.11

Data Cloud Summit 2024の「PLATFORM KEYNOTE」で発表された内容に関する公式情報まとめ #SnowflakeDB #SnowflakeSummit #DataCloudSummit

さがら

2024.06.05

Snowflakeに取り込んだJSONデータをクエリして展開したい

荒木智仁

2024.06.05