Snowflakeのデータガバナンス機能
Snowflakeが提供するデータガバナンス機能はいくつかあり、
個人情報等の機密データが含まれるテーブル/ビューを保護するために使用できます。
こういった機密データの保護に使える機能をまとめてみました。
| 機能 | 詳細 | 公式ドキュメント |
|---|---|---|
| ダイナミックマスキングデータマスキング | 特定の列内のデータを動的にマスクする | ダイナミックデータマスキングについて |
| 集約ポリシー | 個別の行へのアクセスは許可せず、集計情報のみアクセス許可を与える | Aggregation policies（英語） |
| 投影ポリシー | 特定の列へのアクセスを制御する | Projection policies（英語） |
実は、集約ポリシー・投影ポリシーは2024年2月15日にプレビューになったばかり！
以前から使えるダイナミックマスキングポリシーについては、この記事で分かりやすく解説していますので、ぜひこちらもご覧ください！
集計ポリシーはどんな時に使える？
さて、本記事で解説する集計ポリシーがどんなものかというと、上記の通り「集計処理を強制する」機能です。
個々のレコードを呼び出すことはできず、集計情報のみにアクセス可能となります。
よって、個人情報は見せたくないけど、集計したデータだけ出したいとき…
例えば、全国の顧客の電話番号と氏名の一覧がある場合、
岩手県の人はどれくらい居るのか知りたい！というケースでは
市外局番019で始まる、という条件でCountすれば件数が出せます。
一方で電話番号を含む行へ直接アクセスすることはできないので
個人情報を出さないけど集計値は見れるよね！やったぜ！といった感じです。
やってみよう！
今回はこんなケースを想定してみます。
・顧客情報が入ったテーブルがあり、それを元にPUBLICロールのユーザーに分析させたい
・個別の顧客情報はPUBLICロールに公開したくない
ではやっていきましょう！
サンプルデータ作成
SYSADMINロールでサンプルデータを作って、PUBLICでSELECTできるようにします。
--サンプルデータ作成
USE ROLE SYSADMIN;
CREATE DATABASE CUSTOMERS_DB;
CREATE SCHEMA CUSTOMERS_SCHEMA;
CREATE OR REPLACE TABLE CUSTOMERS
(
CUSTOMER_ID NUMBER,
CUSTOMER_NAME VARCHAR,
STATE VARCHAR,
REGIST_DATE DATE,
PHONE_NUMBER VARCHAR,
CREDIT_CARD_NUMBER VARCHAR
);
INSERT INTO CUSTOMERS
VALUES(100,'Phosphophyllite','3.5','2011-09-23','019-999-000','78673421'),
(101,'Antarcticite','3','2009-11-12','777-444-555','34567842'),
(103,'Cinnabar','2','2011-08-15','999-888-777','98765432'),
(104,'Cairngorm','7','2012-02-28','444-555-666','24681357'),
(105,'Bort','10','2013-04-17','333-222-111','13579246'),
(106,'Alexandrite','8.5','2014-09-30','019-777-888','98765432'),
(107,'lapis lazuli','5.5','2015-11-25','019-333-444','86421357');
--作ったテーブルのSELECT権限をPUBLICに付与
GRANT USAGE ON DATABASE CUSTOMERS_DB TO ROLE PUBLIC;
GRANT USAGE ON SCHEMA CUSTOMERS_DB.CUSTOMERS_SCHEMA TO ROLE PUBLIC;
GRANT SELECT ON TABLE CUSTOMERS_DB.CUSTOMERS_SCHEMA.CUSTOMERS TO ROLE PUBLIC; 集約ポリシーの作成と適用
SAMPLE_POLICYという集約ポリシーを作成していきます。
SYSADMINの時は集計制約をかけず、それ以外では集計制約をかけるよ！と定義しています。
また、集計制約（AGGREGATION_CONSTRAINT）の後にある MIN_GROUP_SIZE は、クエリ結果を返せる最小のレコード数を定義するものです。
例えばあらゆる条件で絞り込んでいった結果がレコードが1件絞れてしまった場合、その結果を返してしまうと機密情報が特定できてしまう可能性があります。
MIN_GROUP_SIZEは、これを防止するために設定する、クエリ可能なレコード数の閾値となります。
ここでは3と定義してみました。
-- 集約ポリシーの作成
CREATE OR REPLACE AGGREGATION POLICY SAMPLE_POLICY AS () RETURNS AGGREGATION_CONSTRAINT ->
CASE
WHEN CURRENT_ROLE() = 'SYSADMIN'
THEN NO_AGGREGATION_CONSTRAINT()
ELSE AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 3)
END;
-- 集約ポリシーを適用
ALTER TABLE CUSTOMERS SET AGGREGATION POLICY SAMPLE_POLICY;さて、これでCUSTOMERSテーブルに集約ポリシーが適用されました。
それぞれのロールでクエリしてみる
まず、SYSADMINでクエリすると…普通に個別の行が表示されますね。
同じようにPUBLICでクエリすると…集約ポリシー違反と表示されました！
今度は、PUBLICロールで、PHONE_NUMBERが019で始まるものに絞ってカウントしてみましょう！
USE ROLE PUBLIC;
SELECT COUNT(*) FROM CUSTOMERS WHERE STARTSWITH(PHONE_NUMBER, '019');結果は…ちゃんと集計値が表示されましたー！
ちなみに、999始まりのもので絞ってカウントすると…NULLになりました。
これは先ほど定義したMIN_GROUP_SIZE（クエリできる最少レコード数の定義）の機能です。
MIN_GROUP_SIZEを3にしたため3件以上であれば表示されますが
999始まりのレコードは1件しかないため結果が表示されなくなっています。
さいごに
この記事ではsnowflakeのデータガバナンス機能の1つ、集約ポリシーについて紹介しました。
投影ポリシーについても、また別の記事でご紹介できればと思います！
truestarではSnowflakeの検討、導入支援や環境構築からアプリ開発まで幅広くサポート可能です。
Snowflakeに興味がある、導入済みだけどもっとうまく活用したい等々ありましたら、ぜひこちらからご相談ください！
