データエンジニア向けSnowflakeプライバシー保護機能まとめと実践①

はじめに

データが企業の重要な資産として活用される現代、顧客や従業員の個人情報を適切に保護することは、コンプライアンスの遵守や信頼性の向上に不可欠です。一方で、データの活用とプライバシー保護のバランスを取ることは、多くの企業にとって難しい課題となっています。Snowflakeは、こうした課題に対応するため、柔軟なデータ管理と高度なプライバシー機能を両立するプラットフォームを提供しています。

本記事では私自身がsnowflakeの各プライバシー保護機能について学んだ内容に私なりの解釈も含めて、体系的にまとめたものとなります。

実際の実装においてはより詳細で複雑な実装となることが多いと思いますが、基本的な考え方を説明したものとして読んでいただければと思います。

あと、例によって長文記事ですいません。気長に読んでいただくか、それぞれの機能に飛んで読んでいただければと思います。

セキュリティとプライバシー保護の違い

本記事を書くに辺り、セキュリティとプライバシー保護はそれぞれ異なる位置付けの概念となりますので、前提としてまとめさせていただきます。

区分	セキュリティ	プライバシー保護
目的	外部からの不正アクセスや攻撃からの保護	個人情報など機密データの適切な取り扱い
焦点	アクセス制御と保護	データ使用目的と方法の制限
管理対象	「誰が」「何に」アクセスするか	「どのように」データを扱うか

Snowflakeにおける具体的機能

上記分類をベースにsnowflakeが提供する機能をマッピングしてみました。

カテゴリ	セキュリティ機能	プライバシー保護機能
認証/管理	- 多要素認証(MFA) - Federation認証 - SCIM - IP制限 - トラストセンター	- ガバナンスダッシュボード - タグベースのデータ分類 - データ系統追跡 - アクセスログ監査 - トラストセンター
制御	- ネットワークポリシー・ルール - ロールベースアクセス制御（RBAC） - 権限管理	- 動的データマスキング 　(列アクセスポリシー) - 行アクセスポリシー
保護	- エンドツーエンド暗号化 　 保存データ・転送データの暗号化 - キー管理・キーローテーション - Time Travel/ Fail-safe - セキュアデータシェアリング	- ハッシュ化・暗号化 - セキュアビュー - 集計ポリシー（PV） - 投影ポリシー（PV） - 差分プライバシー※近日

本記事が対象とするプライバシー保護機能

さて本記事では、上記のプライバシー保護機能のうち、主に制御と保護に分類している太文字の7つの機能をデータガバナンスの視点で整理して解説します。
RBACベースでのアクセスポリシーとなりますが、RBACについて偉大な先輩方がまとめられた記事がありますので本記事では割愛します。

これらの機能をどのように組み合わせて使うか、実運用での具体例を交えながら紹介し、企業のデータ管理戦略を強化するためのヒントになるものと考えています。**「誰が、いつ、どのデータに、どのようにアクセスできるべきか」**という課題に対して、柔軟かつ効果的な解決策を見つけてください。

なお、差分プライバシーについては、第二章で詳細を説明する予定です。通常の社内分析で多様する事があまりなく、データクリーンルームの文脈での実装要件としてまとめた方が分かりよいかなと思ったこともあり、それらも含めた内容として別途記事にしたいと思いますので、今しばらくお待ちください。

プライバシー保護機能の比較表

以下の表は、各プライバシー保護機能の主要な特徴、使用シナリオ、リスクや懸念事項を比較したものです。それぞれの機能は単独でも有用ですが、パフォーマンスへの影響や運用の複雑さを考慮する必要があります。これらの機能は適切に組み合わせることで、データ活用とプライバシー保護のバランスを最適化できます。エンジニアとして各機能の特性を理解し、用法・用量を守って正しく運用することが重要です。

機能	保護レベル	実装の複雑さ	性能影響	主な使用シナリオ	リスクや懸念事項
動的データマスキング	中	中	低	特定のユーザーや状況に応じたデータ保護	不適切な条件設定でデータ露出のリスク
行アクセスポリシー	高	中	中	ロール別・ユーザー別のアクセス制御	ポリシーの複雑化による管理オーバーヘッド
ハッシュ化・暗号化	高	低	低	機密データの保存・転送時の保護	鍵管理の複雑さ、暗号化処理の遅延
セキュアビュー	中	低	中	必要最低限のデータアクセスの提供	設計ミスによる露出リスク、パフォーマンス低下
集計ポリシー	中	中	中	小規模グループに対する統計的保護	厳格すぎるポリシーによる分析の制約
投影ポリシー	中	中	低	プライバシー項目の出力制御	設計ミスによる露出リスク
差分プライバシー	高	高	高	高度な匿名化と統計的有用性の両立	ノイズ追加でデータ精度が低下する可能性

---

保護レベルの定義

各機能の保護レベルは以下の基準で評価しています：

• 高: データの機密性を最大限に保護し、個人の特定リスクや漏洩リスクを大幅に低減。高度な数学的手法や複数の保護層を組み合わせて、強固なプライバシー保証を提供。
• 中: 一般的な脅威からデータを守るが、高度な攻撃や特定状況下での完全保護は保証できない。
• 低: 基本的なプライバシー保護を提供するが、他の高度な保護機能と組み合わせて使用するのが推奨される。 （※使い方次第なので機能として"低"は付けていません）

---

プライバシー機能俯瞰図

以下は、実施した内容および差分プライバシーを含む機能の俯瞰図です。それぞれの位置づけと目的についてご確認ください。
これらの機能は、それぞれの位置づけによって異なる手法や制御が行われていますので
全体の組み合わせの参考としてください。

機能の組み合わせ案

1. 包括的な個人情報保護

• 推奨機能: 動的データマスキング + 行アクセスポリシー + 暗号化
• 効果:
  • プライバシー項目の出力をデータマスキングで制御し、ID単位は行アクセスポリシーで制御
  • 暗号化により個人識別は可能だが、特定性のある情報へのアクセスを制限
• 使用シナリオ: 顧客データの多層的な保護

---

2. 柔軟な分析環境の構築

• 推奨機能: ハッシュ化 + セキュアビュー + 動的データマスキング + 集計ポリシー
• 効果:
  • セキュアビューで必要最小限のデータにアクセスを提供
  • 必要なデータマスキングをし、出力を集計に限る事で統計情報化し、プライバシー保護を強化
• 使用シナリオ: 出力を統計情報する事による多様な部門への透過的なデータアクセス管理

---

3. 高度な統計分析のためのプライバシー保護

• 推奨機能: セキュアビュー + 集計ポリシー + 投影ポリシー + 差分プライバシー
• 効果:
  • 差分プライバシーによって匿名性を維持しつつ、統計的有用性の高い分析結果を提供
  • セキュアビューで基本的なアクセス制御を行い、分析者に適切なデータを提供
• 使用シナリオ: 個人特定性を排除した統計情報提供プロジェクト（データクリーンルーム等）

---

4. 全方位的なデータ保護戦略

• 推奨機能: 暗号化 + 行レベルセキュリティ + データマスキング + 差分プライバシー
• 効果:
  • データの保存、アクセス、表示、分析の各段階で高い保護レベルを提供
  • 高度な脅威に対しても強固な防御を実現
• 使用シナリオ: 複雑な法規制が適用される個人情報を最大限排除した外部へのデータ提供

---

まとめ

これらのプライバシー保護機能は、単独でも十分に有効ですが、データの性質や使用目的、法的要件に応じて適切に組み合わせることが求められます。エンジニアとしては、システムの要件に合わせて柔軟に設計し、運用上のトレードオフを理解したうえで、効果的な戦略を策定し、運用することが何よりも重要です。

これらの技術を活用することで、データ活用とプライバシー保護の両立を実現し、企業にとって持続可能なデータ戦略を構築できると考えています。

Snowflakeプライバシー保護機能：詳細ガイドと実践例

各プライバシー保護機能を体験出来るようにStepByStepでテストデータの準備から実際の実装までをまとめています。
手順の簡便性のためACCOUNTADMINの使用やテーブル、ロール、ポリシーの作成などがありますので、必要に応じてカスタマイズして実施してください。

実践用環境セットアップ

新規にデータベースを作り、その中のCUSTOMER_DATAテーブルを作成します
このCUSTOMER_DATAを中心に各機能を試せるようになっています
実践用（hands-on）環境の管理者や使用ユーザーなどの作成や権限設定、DB作成などが含まれています。
ロール名には極力、皆様の環境ロールと名称が被り、事故とならないよう、全て'PVIVACY_'を付けています。その名前使っている！というのであればご調整ください。

PRIVACY_HO_ADMIN	hands-on環境のDB管理ロール
PRIVACY_USER_ADMIN	hands-on環境の個人情報管理ロール
PRIVACY_ANALYST	分析ロール
PRIVACY_FINANCE	金融部門ロール
PRIVACY_CUSTOMER_SERVICE	顧客サービスロール

-- 環境セットアップ
-- 1. ウェアハウスの作成
CREATE WAREHOUSE IF NOT EXISTS ADMIN_XS WAREHOUSE_SIZE = 'X-SMALL' AUTO_SUSPEND = 300 AUTO_RESUME = TRUE;

-- 2. データベースとスキーマの作成
CREATE DATABASE IF NOT EXISTS PRIVACY_HO_DB;
CREATE SCHEMA IF NOT EXISTS PRIVACY_HO_DB.PRIVACY;

-- 3. カスタム管理者ロールの作成
CREATE ROLE IF NOT EXISTS PRIVACY_HO_ADMIN;

-- 4. ウェアハウスの権限付与
GRANT USAGE, OPERATE ON WAREHOUSE ADMIN_XS TO ROLE PRIVACY_HO_ADMIN;

-- 5. データベースレベルの権限付与
GRANT OWNERSHIP ON DATABASE PRIVACY_HO_DB TO ROLE PRIVACY_HO_ADMIN;
GRANT ALL PRIVILEGES ON DATABASE PRIVACY_HO_DB TO ROLE PRIVACY_HO_ADMIN;

-- 6. スキーマレベルの権限付与
GRANT OWNERSHIP ON SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_HO_ADMIN;
GRANT ALL PRIVILEGES ON SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_HO_ADMIN;

-- 7. フューチャーの権限付与
GRANT ALL PRIVILEGES ON FUTURE TABLES IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_HO_ADMIN;
GRANT ALL PRIVILEGES ON FUTURE VIEWS IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_HO_ADMIN;
GRANT ALL PRIVILEGES ON FUTURE MATERIALIZED VIEWS IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_HO_ADMIN;
GRANT ALL PRIVILEGES ON FUTURE PROCEDURES IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_HO_ADMIN;
GRANT ALL PRIVILEGES ON FUTURE FUNCTIONS IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_HO_ADMIN;
GRANT ALL PRIVILEGES ON FUTURE SEQUENCES IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_HO_ADMIN;

-- 8. マスキングポリシー関連の権限付与
GRANT CREATE MASKING POLICY ON SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_HO_ADMIN;
GRANT APPLY MASKING POLICY ON ACCOUNT TO ROLE PRIVACY_HO_ADMIN;

-- 9. 行アクセスポリシー関連の権限付与
GRANT CREATE ROW ACCESS POLICY ON SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_HO_ADMIN;
GRANT APPLY ROW ACCESS POLICY ON ACCOUNT TO ROLE PRIVACY_HO_ADMIN;

-- 10. 集計ポリシー関連の権限付与
GRANT CREATE AGGREGATION POLICY ON SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_HO_ADMIN;
GRANT APPLY AGGREGATION POLICY ON ACCOUNT TO ROLE PRIVACY_HO_ADMIN;

-- 11. 投影ポリシー関連の権限付与
GRANT CREATE PROJECTION POLICY ON SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_HO_ADMIN;
GRANT APPLY PROJECTION POLICY ON ACCOUNT TO ROLE PRIVACY_HO_ADMIN;

-- 12. セキュアビュー関連の権限付与
GRANT CREATE VIEW ON SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_HO_ADMIN;
GRANT MODIFY ON SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_HO_ADMIN;

-- 13. Time Travel関連の権限付与
GRANT EXECUTE TASK ON ACCOUNT TO ROLE PRIVACY_HO_ADMIN;
GRANT EXECUTE MANAGED TASK ON ACCOUNT TO ROLE PRIVACY_HO_ADMIN;

-- 16. モニタリング関連の権限付与
GRANT MONITOR EXECUTION ON ACCOUNT TO ROLE PRIVACY_HO_ADMIN;
GRANT MONITOR USAGE ON ACCOUNT TO ROLE PRIVACY_HO_ADMIN;

-- オプション: 特定のユーザーにPRIVACY_HO_ADMINロールを付与する場合
-- GRANT ROLE PRIVACY_HO_ADMIN TO USER [username];

--顧客テーブルの作成
USE ROLE PRIVACY_HO_ADMIN;
CREATE OR REPLACE TABLE CUSTOMER_DATA (
    customer_id INT,
    first_name STRING,
    last_name STRING,
    email STRING,
    phone_number STRING,
    credit_card_number STRING,
    annual_income FLOAT, --年収
    region STRING, -- North,East,South,West
    customer_rank INT, -- 顧客ランク（0-5）
    smartphone_type STRING, -- iOS,Android
    car_ownership STRING, -- 車所有（1:あり,0:なし）
    dependents INT, -- 扶養家族（0-10人）
    hobby STRING, -- 趣味
    consent INT    -- 許諾同意（1:あり、0:なし）
);

--顧客情報の生成
INSERT INTO CUSTOMER_DATA VALUES 
(1, 'John', 'Doe', '[e-mail]', '123-456-7890', '1234-5678-9012-3456', 75000.00, 'North', 3, 'iOS', '1', 2, 'Movies', 1),
(2, 'Jane', 'Smith', '[e-mail]', '234-567-8901', '2345-6789-0123-4567', 82000.00, 'South', 4, 'Android', '1', 1, 'Sports', 1),
(3, 'Bob', 'Johnson', '[e-mail]', '345-678-9012', '3456-7890-1234-5678', 68000.00, 'East', 2, 'iOS', '0', 0, 'Reading', 0),
(4, 'Alice', 'Williams', '[e-mail]', '456-789-0123', '4567-8901-2345-6789', 91000.00, 'West', 5, 'Android', '1', 3, 'Travel', 1),
(5, 'Charlie', 'Brown', '[e-mail]', '567-890-1234', '5678-9012-3456-7890', 78000.00, 'North', 3, 'iOS', '0', 1, 'Gaming', 1);

--今回の実践例ではロールベースでのプライバシー制御をします
--またロールを切替ながらの手順になるので、ロールを任意に変える場合は都度ご調整ください。
--管理者ロールの設定
USE ROLE ACCOUNTADMIN;
CREATE ROLE PRIVACY_USER_ADMIN;
GRANT USAGE ON WAREHOUSE ADMIN_XS TO ROLE PRIVACY_USER_ADMIN;
GRANT USAGE ON DATABASE PRIVACY_HO_DB TO ROLE PRIVACY_USER_ADMIN;
GRANT USAGE ON SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_HO_ADMIN;
GRANT SELECT ON PRIVACY_HO_DB.PRIVACY.CUSTOMER_DATA TO ROLE PRIVACY_USER_ADMIN;
CREATE WAREHOUSE IF NOT EXISTS USER_XS WAREHOUSE_SIZE = 'X-SMALL' AUTO_SUSPEND = 300 AUTO_RESUME = TRUE;
GRANT USAGE ON WAREHOUSE USER_XS TO ROLE PRIVACY_USER_ADMIN;

-- 2. ビジネスロールの作成
CREATE ROLE IF NOT EXISTS PRIVACY_ANALYST;
CREATE ROLE IF NOT EXISTS PRIVACY_CUSTOMER_SERVICE;
CREATE ROLE IF NOT EXISTS PRIVACY_FINANCE;
CREATE ROLE IF NOT EXISTS PRIVACY_USER_ADMIN;

-- 3. ウェアハウスの権限付与
GRANT USAGE ON WAREHOUSE USER_XS TO ROLE PRIVACY_ANALYST;
GRANT USAGE ON WAREHOUSE USER_XS TO ROLE PRIVACY_CUSTOMER_SERVICE;
GRANT USAGE ON WAREHOUSE USER_XS TO ROLE PRIVACY_FINANCE;
GRANT USAGE ON WAREHOUSE USER_XS TO ROLE PRIVACY_USER_ADMIN;

-- 4. データベースの権限付与
GRANT USAGE ON DATABASE PRIVACY_HO_DB TO ROLE PRIVACY_ANALYST;
GRANT USAGE ON DATABASE PRIVACY_HO_DB TO ROLE PRIVACY_CUSTOMER_SERVICE;
GRANT USAGE ON DATABASE PRIVACY_HO_DB TO ROLE PRIVACY_FINANCE;
GRANT USAGE ON DATABASE PRIVACY_HO_DB TO ROLE PRIVACY_USER_ADMIN;

-- 5. スキーマの権限付与
GRANT USAGE ON SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_ANALYST;
GRANT USAGE ON SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_CUSTOMER_SERVICE;
GRANT USAGE ON SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_FINANCE;
GRANT USAGE ON SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_USER_ADMIN;

-- 6. 既存オブジェクトへの SELECT 権限付与
-- アナリスト向け
GRANT SELECT ON ALL TABLES IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_ANALYST;
GRANT SELECT ON ALL VIEWS IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_ANALYST;
GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_ANALYST;

-- カスタマーサービス向け
GRANT SELECT ON ALL TABLES IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_CUSTOMER_SERVICE;
GRANT SELECT ON ALL VIEWS IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_CUSTOMER_SERVICE;
GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_CUSTOMER_SERVICE;

-- 財務部門向け
GRANT SELECT ON ALL TABLES IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_FINANCE;
GRANT SELECT ON ALL VIEWS IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_FINANCE;
GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_FINANCE;

-- ユーザー管理者向け
GRANT SELECT ON ALL TABLES IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_USER_ADMIN;
GRANT SELECT ON ALL VIEWS IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_USER_ADMIN;
GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_USER_ADMIN;

-- 7. 今後作成されるオブジェクトへのSELECT権限付与
-- アナリスト向け
GRANT SELECT ON FUTURE TABLES IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_ANALYST;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_ANALYST;
GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_ANALYST;

-- カスタマーサービス向け
GRANT SELECT ON FUTURE TABLES IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_CUSTOMER_SERVICE;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_CUSTOMER_SERVICE;

-- 金融部門向け
GRANT SELECT ON FUTURE TABLES IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_FINANCE;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_FINANCE;

-- ユーザー管理者向け
GRANT SELECT ON FUTURE TABLES IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_USER_ADMIN;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA PRIVACY_HO_DB.PRIVACY TO ROLE PRIVACY_USER_ADMIN;

-- オプション: 特定のユーザーへのロール付与が必要な場合は以下のように実行
-- GRANT ROLE PRIVACY_ANALYST TO USER [username];
-- GRANT ROLE PRIVACY_CUSTOMER_SERVICE TO USER [username];
-- GRANT ROLE PRIVACY_FINANCE TO USER [username];
-- GRANT ROLE PRIVACY_USER_ADMIN TO USER [username];

事前セットアップは以上で完了です

1. データマスキング1（ダイナミックデータマスキング）

公式ドキュメント

目的・効果:

• 機密データを隠蔽し、権限のないユーザーの不正アクセスを防止
• 必要最小限の情報開示を実現し、ユーザー権限に応じた段階的な制御を提供
• データ利用の利便性とプライバシー保護のバランスを維持

実装例:

-- メールアドレスに対するマスキングポリシー
-- PRIVACY_USER_ADMIN ロール: フル出力（顧客管理のため必要）
-- PRIVACY_CUSTOMER_SERVICE ロール: 部分マスキング（登録確認のため部分的に表示）
-- その他のロール: フルマスク（業務上メールアドレス不要）
USE ROLE PRIVACY_HO_ADMIN;
CREATE OR REPLACE MASKING POLICY email_mask AS (val string) RETURNS string ->
  CASE
    WHEN CURRENT_ROLE() IN ('PRIVACY_CUSTOMER_SERVICE') THEN REGEXP_REPLACE(val, '^[^@]+', '******')
    WHEN CURRENT_ROLE() IN ('PRIVACY_USER_ADMIN') THEN val
    ELSE '********'
  END;

ALTER TABLE CUSTOMER_DATA MODIFY COLUMN email SET MASKING POLICY email_mask;

アウトプット制御イメージ:

-- PRIVACY_USER_ADMIN ロールでのクエリ
USE ROLE PRIVACY_USER_ADMIN;
SELECT customer_id,email FROM CUSTOMER_DATA ;

CUSTOMER_ID	EMAIL
1	[e-mail]
2	[e-mail]
3	[e-mail]
4	[e-mail]
5	[e-mail]

結果: フル出力される

-- PRIVACY_CUSTOMER_SERVICE ロールでのクエリ
USE ROLE PRIVACY_CUSTOMER_SERVICE;
SELECT customer_id,email FROM CUSTOMER_DATA ;

CUSTOMER_ID	EMAIL
1	******@email.com
2	******@email.com
3	******@email.com
4	******@email.com
5	******@email.com

結果: 部分マスキングされる

-- PRIVACY_ANALYST ロールでのクエリ
USE ROLE PRIVACY_ANALYST;
SELECT customer_id,email FROM CUSTOMER_DATA ;

CUSTOMER_ID	EMAIL
1	********
2	********
3	********
4	********
5	********

結果: フルマスクされる

-- プライバシー検証のため一時的にポリシーを解除
USE ROLE PRIVACY_USER_ADMIN;
ALTER TABLE CUSTOMER_DATA MODIFY COLUMN email UNSET MASKING POLICY;

2. データマスキング2（マスキング関数の利用）

目的・効果:

複数テーブルに共通するマスキングロジックを関数で一元管理
ロールやデータ項目が増加した場合でも、柔軟かつ効率的な管理を実現

実装例:

-- マスキング関数の作成
-- フル出力、部分マスキング、完全マスキングの3パターンの制御
CREATE OR REPLACE FUNCTION mask_credit_card(cc_number string, access_level string)
RETURNS string AS
$$
    CASE
        WHEN access_level = 'FULL' THEN cc_number
        WHEN access_level = 'PARTIAL' THEN CONCAT(REPEAT('*', LENGTH(cc_number) - 4), RIGHT(cc_number, 4))
        ELSE REPEAT('*', LENGTH(cc_number))
    END
$$;

-- マスキングポリシーの作成
-- PRIVACY_FINANCE、PRIVACY_USER_ADMINはフル出力（決済業務で必要、顧客調査時に必要）
-- PRIVACY_CUSTOMER_SERVICEは部分マスキング（顧客対応では下4桁が分かればよい）
-- それ以外は完全マスキング（クレジット番号が必要となる業務がない）
CREATE OR REPLACE MASKING POLICY credit_card_masking AS (cc_number string) RETURNS string ->
    CASE
        WHEN CURRENT_ROLE() IN ('PRIVACY_USER_ADMIN', 'PRIVACY_FINANCE') THEN cc_number
        WHEN CURRENT_ROLE() = 'PRIVACY_CUSTOMER_SERVICE' THEN mask_credit_card(cc_number, 'PARTIAL')
        ELSE mask_credit_card(cc_number, 'MASKED')
    END;

-- テーブルにマスキングポリシーを適用
ALTER TABLE CUSTOMER_DATA MODIFY COLUMN credit_card_number
SET MASKING POLICY credit_card_masking;

アウトプット制御イメージ:

-- PRIVACY_FINANCE ロールでのクエリ
USE ROLE PRIVACY_FINANCE;
SELECT customer_id,credit_card_number FROM CUSTOMER_DATA;

CUSTOMER_ID	CREDIT_CARD_NUMBER
1	1234-5678-9012-3456
2	2345-6789-0123-4567
3	3456-7890-1234-5678
4	4567-8901-2345-6789
5	5678-9012-3456-7890

結果: フル出力される

-- PRIVACY_CUSTOMER_SERVICE ロールのクエリ:
USE ROLE PRIVACY_CUSTOMER_SERVICE;
SELECT customer_id,credit_card_number FROM CUSTOMER_DATA;

CUSTOMER_ID	CREDIT_CARD_NUMBER
1	***************3456
2	***************4567
3	***************5678
4	***************6789
5	***************7890

結果: 部分マスキング（下4桁のみ表示）

-- PRIVACY_ANALYST（その他） ロールのクエリ:
USE ROLE PRIVACY_ANALYST;
SELECT customer_id,credit_card_number FROM CUSTOMER_DATA;

CUSTOMER_ID	CREDIT_CARD_NUMBER
1	*******************
2	*******************
3	*******************
4	*******************
5	*******************

結果: 完全マスキング

権限に応じて、フル出力、部分マスキング、フルマスキングが制御されている

追加シナリオ

FINANCE部門では、決済システムの精査において特定のカード発行者を識別する必要があるため、カード番号の先頭6桁のみを取得するようにポリシーを見直しました。

-- マスキング関数の修正
-- フル出力、部分マスキング、完全マスキングに加え、部分マスキング先頭8桁以降のマスキングを追加(0000-00までを出力するため7桁)
USE ROLE PRIVACY_HO_ADMIN;
CREATE OR REPLACE FUNCTION mask_credit_card(cc_number string, access_level string)
RETURNS string AS
$$
    CASE
        WHEN access_level = 'FULL' THEN cc_number
        WHEN access_level = 'PARTIAL1' THEN CONCAT(REPEAT('*', LENGTH(cc_number) - 4), RIGHT(cc_number, 4))
        WHEN access_level = 'PARTIAL2' THEN 
        CONCAT(SUBSTR(cc_number, 1, 7), REPEAT('*', LENGTH(cc_number) - 7))
        ELSE REPEAT('*', LENGTH(cc_number))
    END
$$;

-- マスキングポリシーの解除
ALTER TABLE CUSTOMER_DATA MODIFY COLUMN credit_card_number UNSET MASKING POLICY;

-- マスキングポリシーの修正
-- PRIVACY_USER_ADMINはフル出力（顧客調査時に必要）
-- PRIVACY_FINANCEは部分マスキング（決済業務で先頭6桁のみ必要）
-- PRIVACY_CUSTOMER_SERVICEは部分マスキング（顧客対応では下4桁が分かればよい）
-- それ以外は完全マスキング（クレジット番号が必要となる業務がない）
CREATE OR REPLACE MASKING POLICY credit_card_masking AS (cc_number string) RETURNS string ->
    CASE
        WHEN CURRENT_ROLE() IN ('ADMIN') THEN cc_number
        WHEN CURRENT_ROLE() = 'CUSTOMER_SERVICE' THEN mask_credit_card(cc_number, 'PARTIAL1')
        WHEN CURRENT_ROLE() = 'FINANCE' THEN mask_credit_card(cc_number, 'PARTIAL2')
        ELSE mask_credit_card(cc_number, 'MASKED')
    END;

-- マスキングポリシーの付与
ALTER TABLE CUSTOMER_DATA MODIFY COLUMN credit_card_number SET MASKING POLICY credit_card_masking;

-- PRIVACY_FINANCE ロールのクエリ
USE ROLE PRIVACY_FINANCE;
SELECT customer_id,credit_card_number, FROM CUSTOMER_DATA;

CUSTOMER_ID	CREDIT_CARD_NUMBER
1	1234-56************
2	2345-67************
3	3456-78************
4	4567-89************
5	5678-90************

結果: 目的に沿ってプライバシー保護を強化出来ている
※実際の企業ではPCIDSS対応などにより、自社のDBにそもそもクレジット番号を持っているケースは少ないと思いますが、イメージとして掴んでください。

-- 検証後にポリシーを解除
USE ROLE PRIVACY_HO_ADMIN;
ALTER TABLE CUSTOMER_DATA MODIFY COLUMN credit_card_number UNSET MASKING POLICY;

ダイナミックデータマスキングのまとめ

ダイナミックデータマスキングは、必要最小限の情報開示を実現する強力な機能です。これにより、業務に必要な情報だけをユーザーに提供し、不要な情報へのアクセスを防止できます。また、適切に運用することで、エンドユーザーはプライバシーに関する不安なく業務に専念できるでしょう。

ただし、非常に複雑なマスキングを行う場合にはパフォーマンスへの影響が懸念されます。運用要件に応じた適切な設計が求められます。

3. 行アクセスポリシー (RAP)

公式ドキュメント

目的・効果:

• ユーザーやロールに基づいて、データの特定の行へのアクセスを制御
• 部門や地域ごとにデータを分離し、適切な範囲でのデータ利用を実現
• 利用者権限に応じたアクセス制御により、柔軟なプライバシー保護を提供

---

実装例:

-- PRIVACY_USER_ADMIN は全ての行にアクセス可能
-- PRIVACY_ANALYST は consent=1 の会員のみ参照可能
USE ROLE PRIVACY_HO_ADMIN;
CREATE OR REPLACE ROW ACCESS POLICY consent_access_policy AS (consent INT) RETURNS BOOLEAN ->
    CASE
        WHEN CURRENT_ROLE() = 'PRIVACY_USER_ADMIN' THEN TRUE
        WHEN CURRENT_ROLE() = 'PRIVACY_ANALYST' AND consent = 1 THEN TRUE
        ELSE FALSE
    END;

ALTER TABLE CUSTOMER_DATA ADD ROW ACCESS POLICY consent_access_policy ON (consent);

アウトプット制御イメージ:

-- PRIVACY_USER_ADMIN ロールでのクエリ
USE ROLE PRIVACY_USER_ADMIN;
SELECT CUSTOMER_ID,REGION,HOBBY,CONSENT FROM CUSTOMER_DATA;

CUSTOMER_ID	REGION	HOBBY	CONSENT
1	North	Movies	1
2	South	Sports	1
3	East	Reading	0
4	West	Travel	1
5	North	Gaming	1

結果: 全ての行が表示される

-- PRIVACY_ANALSTユーザーは、CONCENT=1'顧客同意あり' の会員のみ出力される
USE ROLE PRIVACY_ANALYST;
SELECT CUSTOMER_ID,REGION,HOBBY,CONSENT FROM CUSTOMER_DATA;

CUSTOMER_ID	REGION	HOBBY	CONSENT
1	North	Movies	1
2	South	Sports	1
4	West	Travel	1
5	North	Gaming	1

結果: consent=1 の行のみ表示される

追加シナリオ

ロールベースでの権限設定に加え、DBアクセスユーザーの所属する地域によって、同地域の顧客情報しか見れないようにする要件が発生しました。
それに伴い、新たにユーザー情報マスタを利用し、地域ごとのデータアクセスを制御したいと考えました。

-- ユーザー情報マスタテーブルの作成
USE ROLE PRIVACY_HO_ADMIN;
CREATE TABLE USER_MASTER (
    LOGIN_ID VARCHAR(50),
    USER_NAME VARCHAR(100),
    REGION VARCHAR(20)
);

-- ログインユーザーを North 地域に設定※ユーザーIDは実行ユーザーに調整してください
INSERT INTO USER_MASTER (LOGIN_ID, USER_NAME, REGION) VALUES ('TEST_0001', 'TEST_ANALYST', 'North');

-- 行アクセスポリシーの作成
-- PRIVACY_USER_ADMINは全件、PRIVACY_ANALYSTはユーザーマスタより自身のREGIONの顧客のみに制限
--行アクセスポリシーを解除
USE ROLE PRIVACY_HO_ADMIN;
ALTER TABLE CUSTOMER_DATA DROP ROW ACCESS POLICY consent_access_policy;

CREATE OR REPLACE ROW ACCESS POLICY concent_access_policy 
AS (consent INT, region VARCHAR) 
RETURNS BOOLEAN ->
    CASE
        WHEN CURRENT_ROLE() = 'PRIVACY_USER_ADMIN' THEN TRUE
        WHEN CURRENT_ROLE() = 'PRIVACY_ANALYST' THEN
            consent = 1 AND EXISTS (
                SELECT 1 
                FROM USER_MASTER 
                WHERE USER_MASTER.REGION = region
                AND USER_MASTER.LOGIN_ID = CURRENT_USER()
            )
        ELSE FALSE
    END;

-- 行アクセスポリシーを適用
    ALTER TABLE CUSTOMER_DATA ADD ROW ACCESS POLICY concent_access_policy ON (consent,region);

-- PRIVACY_USER_ADMIN ロールでのクエリ
USE ROLE PRIVACY_USER_ADMIN;
SELECT CUSTOMER_ID, REGION, CONSENT FROM CUSTOMER_DATA;

CUSTOMER_ID	REGION	CONSENT
2	South	1
3	East	0
4	West	1
1	North	1
5	North	1

USE ROLE PRIVACY_ANALYST;
SELECT CUSTOMER_ID,REGION,CONSENT from CUSTOMER_DATA;

CUSTOMER_ID	REGION	CONSENT
1	North	1
5	North	1

結果: North 地域のデータのみ出力される

USE ROLE PRIVACY_HO_ADMIN;
UPDATE USER_MASTER
SET USER_NAME = 'TEST_ANALYST', REGION = 'East' WHERE LOGIN_ID = 'TEST_0001';

USE ROLE PRIVACY_ANALYST;
SELECT CUSTOMER_ID,REGION,CONSENT from CUSTOMER_DATA;

結果：East 地域は同意者がいないため0件

このように、ユーザー情報マスタを利用することで、地域や部門ごとにデータアクセスを制御することが可能です。これにより、複数の組織やグループ会社が同じテーブルを共有しつつ、必要なデータのみアクセスさせることができます。

注意点

行アクセスポリシーは複雑なクエリを伴う場合、パフォーマンスへ影響する場合があります。大量のデータへの複雑なロール設計を伴う場合は、設計段階でのパフォーマンス検討が不可欠です。

-- 検証後にポリシーを解除
USE ROLE PRIVACY_HO_ADMIN;
ALTER TABLE CUSTOMER_DATA DROP ROW ACCESS POLICY concent_access_policy ;

行アクセスポリシーのまとめ

行アクセスポリシーは、データの状態や利用者の権限に応じた柔軟なアクセス制御を提供する強力な機能です。プライバシーポリシーの変更や事業の拡大に応じてポリシーを調整することで、顧客の同意に沿ったデータ管理を維持できます。また、複数の部門や地域ごとのデータ利用においても、適切なアクセス範囲を確保できるため、セキュリティと利便性の両立が可能になります。

適切に設計・運用することで、プライバシー保護の一環としてのデータ管理を強化し、組織全体の信頼性を向上させることが期待できます。

4. ハッシュ化・暗号化

目的・効果:

• データを保存する際にハッシュ化または暗号化することで、不正アクセスからデータを保護します。
• 法規制（GDPR、CCPAなど）のデータ保護要件に適合します。

以下のYamagishiさんの記事がとても分かりやすいので、参考にしてください。
https://zenn.dev/dataheroes/articles/20241015-salted-hash-udf-with-snowflake-secret

ハッシュ化・暗号化のまとめ

顧客の個人情報や識別子は、これまでのダイナミックデータマスキングや行アクセスポリシーとは異なり、識別する必要がありますが、実際の値は必要ありません。ハッシュ化や暗号化は、このような場合に有効な手段です。
顧客同意の範囲内で、外部サービス（広告配信やSaaS）への提供や社内での分析において、適切に識別子として利用することが望ましいです。
また、変換後の物理的な値をテーブルに格納する方がクエリパフォーマンスの観点から効率的です。どこでどのような処理を実装するかは、全体設計の中で適切に判断してください。

5. セキュアビュー

公式ドキュメント

目的・効果:

• 通常のビューと異なり、DDL情報を秘匿出来る
• 機密性の高い情報や社外へのデータシェアなどで活用できる

実装例:

-- CUSTOMER_DATAより、新たにセキュアビューを追加
USE ROLE PRIVACY_HO_ADMIN;
CREATE OR REPLACE SECURE VIEW customer_summary_role_based AS
SELECT 
    customer_id,
    ENCRYPT(first_name,'SECURE') first_name,
    ENCRYPT(last_name,'SECURE') last_name,
    CASE 
        WHEN CURRENT_ROLE() = 'PRIVACY_USER_ADMIN' THEN EMAIL-- PRIVACY_USER_ADMINロールはメールをそのまま表示
        ELSE '********'  -- その他のロールはメアドをマスキング
    END AS EMAIL,
    region,
    CASE 
        WHEN CURRENT_ROLE() = 'PRIVACY_FINANCE' THEN annual_income  -- PRIVACY_FINANCEロールは収入をそのまま表示
        ELSE NULL  -- その他のロールは収入をNULLとして表示
    END AS annual_income
FROM CUSTOMER_DATA;

GRANT SELECT ON VIEW customer_summary_role_based TO ROLE PRIVACY_FINANCE;
GRANT SELECT ON VIEW customer_summary_role_based TO ROLE PRIVACY_ANALYST;

アウトプット制御イメージ:

-- PRIVACY_FINANCE ロールでのクエリ
USE ROLE PRIVACY_FINANCE;
SELECT customer_id,email,region,annual_income FROM customer_summary_role_based;

CUSTOMER_ID	EMAIL	REGION	ANNUAL_INCOME
1	********	North	75000
2	********	South	82000
3	********	East	68000
4	********	West	91000
5	********	North	78000

結果: EMAILはマスキング、ANNUAL_INCOMEは出力されている

-- PRIVACY_ANALYST ロールでのクエリ
USE ROLE PRIVACY_ANALYST;
SELECT customer_id,email,region,annual_income FROM customer_summary_role_based;

CUSTOMER_ID	EMAIL	REGION	ANNUAL_INCOME
1	[e-mail]	North
2	[e-mail]	South
3	[e-mail]	East
4	[e-mail]	West
5	[e-mail]	North

結果: EMAILは出力されるが、ANNUAL_INCOMEがNULLで出力される

-- セキュアビューのソースを確認してみる
USE ROLE PRIVACY_ANALYST;
SHOW VIEWS LIKE 'customer_summary_role_based';

created_on	name	reserved	database_name	schema_name	owner	comment	text	is_secure	is_materialized	owner_role_type	change_tracking
2024-10-23 18:18:16.358 +0900	CUSTOMER_SUMMARY_ROLE_BASED		PRIVACY_HO_DB	PRIVACY	PRIVACY_HO_ADMIN			true	false	ROLE	OFF

結果: VIEWの is_secureがtrueとなり、textにSQL文が表示されない

このようにロールにより、ビューの項目別に出力が制御されており、
ソースの取得も出来ないように制御されている

セキュアビューのまとめ

セキュアビューは、主にロジックを非公開にするために使用され、プライバシー保護のためのハッシュ化、暗号化、マスキングなどのロジックの秘匿を目的とします。データシェアなどの外部とのコラボレーションでは、このような制御が必要不可欠です。
一方で、セキュアビューは内部構造が秘匿されるため、クエリオプティマイザの機能が制限され、クエリパフォーマンスに影響が出る場合があります。そのため、通常のビューとセキュアビューを使い分ける方法を慎重に考え、適用することが重要です。

6. 集計ポリシー

公式ドキュメント

目的・効果:

• 特定の条件を満たす場合にのみ集計結果を表示し、少数のレコードに基づく情報開示を防ぐ
• 統計的開示制御を実現し、個人の特定リスクを軽減する

実装例:

--PRIVACY_USER_ADMINは全件出力可、それ以外は5件以上の集計結果しか出力しない
USE ROLE PRIVACY_HO_ADMIN;
CREATE AGGREGATION POLICY min_count_policy
AS () RETURNS AGGREGATION_CONSTRAINT ->
  CASE
    WHEN CURRENT_ROLE() = 'PRIVACY_USER_ADMIN'
      THEN NO_AGGREGATION_CONSTRAINT()
    ELSE AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 5)
  END;

--検証用のビューの作成
CREATE OR REPLACE VIEW CUSTOMER_DATA_AGGREGATE AS SELECT * FROM CUSTOMER_DATA;

--ポリシーの適用
ALTER VIEW CUSTOMER_DATA_AGGREGATE SET AGGREGATION POLICY  min_count_policy;

GRANT SELECT ON VIEW CUSTOMER_DATA_AGGREGATE TO ROLE PRIVACY_USER_ADMIN;
GRANT SELECT ON VIEW CUSTOMER_DATA_AGGREGATE TO ROLE PRIVACY_ANALYST;

アウトプット制御イメージ:

--PRIVACY_USER_ADMIN ロールでの出力
USE ROLE PRIVACY_USER_ADMIN;
select CUSTOMER_ID,ANNUAL_INCOME,REGION from CUSTOMER_DATA_AGGREGATE;

CUSTOMER_ID	ANNUAL_INCOME	REGION
1	75000	North
2	82000	South
3	68000	East
4	91000	West
5	78000	North

結果: 5件が出力される

USE ROLE PRIVACY_USER_ADMIN;
select region,count(*) from CUSTOMER_DATA_AGGREGATE group by region

REGION	COUNT(*)
North	2
South	1
East	1
West	1

結果: それぞれの地域でカウントされた集計値が出力されている

--他のロールで全件出力出来るか試してみる
USE ROLE PRIVACY_ANALYST;
select CUSTOMER_ID,ANNUAL_INCOME,REGION from CUSTOMER_DATA_AGGREGATE;

-- 結果:
SQLコンパイルエラー：集計ポリシー違反。詳細については、ポリシー管理者に連絡してください。

結果: 怒られました・・

--Regionごとの数値が5件を満たしていない場合
USE ROLE PRIVACY_ANALYST;
select region,count(*) from CUSTOMER_DATA_AGGREGATE group by region

REGION	COUNT(*)
	5

結果: REGION単位で出力されず、合計値が出力

-- データ3件（North）を追加して再確認
USE ROLE PRIVACY_HO_ADMIN;
INSERT INTO CUSTOMER_DATA VALUES
(6, 'David', 'Miller', '[e-mail]', '678-901-2345', '6789-0123-4567-8901', 85000.00, 'North', 4, 'Android', '1', 1,'Sports',1);
INSERT INTO CUSTOMER_DATA VALUES
(7, 'Terry', 'Miller', '[e-mail]', '678-901-2345', '6789-0123-4567-8902', 80000.00, 'North', 2, 'iOS', '0', 0, 'Reading',1);
INSERT INTO CUSTOMER_DATA VALUES
(8, 'Anna', 'Miller', '[e-mail]', '678-901-2345', '6789-0123-4567-8903', 90000.00, 'North', 2, 'iOS', '0', 0, 'Reading',0);

--元テーブルの集計値
select region,count(*) from CUSTOMER_DATA group by region

REGION	COUNT(*)
North	5
South	1
East	1
West	1

結果: Northのみ閾値を5件を超えているため、出力される

--検証用ビューで再度集計
USE ROLE PRIVACY_ANALYST;
select region,count(*) from CUSTOMER_DATA_AGGREGATE group by region

REGION	COUNT(*)
North	5

結果: 5件を超えたNorthは出力され、それ以外は出力されない

--Northを削除して合計を5件未満にする
USE ROLE PRIVACY_HO_ADMIN;
delete from CUSTOMER_DATA where REGION ='North';

SELECT CUSTOMER_ID,ANNUAL_INCOME,SMARTPHONE_TYPE from CUSTOMER_DATA;

CUSTOMER_ID	ANNUAL_INCOME	SMARTPHONE_TYPE
2	82000	Android
3	68000	iOS
4	91000	Android

--合計が5件未満でクエリを実行する
USE ROLE PRIVACY_ANALYST;
select region,count(*) from CUSTOMER_DATA_AGGREGATE group by region

REGION	COUNT(*)

結果: 条件を満たさないので、1件も出力されない

--削除したデータ群をTime Travelで戻す
--クエリIDをクエリヒストリーで確認する
USE ROLE ACCOUN_ADMIN;
SELECT 
    query_id,
    query_text,
    database_name,
    schema_name,
    warehouse_name,
    user_name,
    role_name,
    execution_status,
    start_time,
    end_time
FROM snowflake.account_usage.query_history
WHERE 1=1
    AND start_time >= DATEADD(Hour, -1, CURRENT_TIMESTAMP())  -- 過去1時間以内
    AND LOWER(query_text) LIKE LOWER('%delete from CUSTOMER_DATA %')
    AND EXECUTION_STATUS ='SUCCESS';

-- 抽出したクエリIDでTime Travel で戻す
USE ROLE PRIVACY_HO_ADMIN;
insert into PRIVACY_HO_DB.PRIVACY.CUSTOMER_DATA
SELECT * FROM PRIVACY_HO_DB.PRIVACY.CUSTOMER_DATA BEFORE(STATEMENT => '01b7e11c-0000-70fd-0000-2849078d6422')
where REGION ='North'

このように集計ポリシーを設定することで、K-匿名性に対して十分な効果があります。ただし、検索条件を微調整することにより、集計結果の差分を元に個人を特定するリスクが発生する可能性があります。

簡単な差分攻撃の例

USE ROLE PRIVACY_ANALYST;
select  COUNT(*) as "件数" ,SUM(ANNUAL_INCOME) as "年収合計" from CUSTOMER_DATA_AGGREGATE WHERE region IN('North') ;
件数	年収合計
5	408000

select  COUNT(*) as "件数" ,SUM(ANNUAL_INCOME) as "年収合計" from CUSTOMER_DATA_AGGREGATE WHERE region IN('North','West') ;
件数	年収合計
6	499000

上記の"West"条件追加による差分の1件を元に、
その差分の会員がREGION 'West'であり、ANNUAL_INCOME 91000 だと推定出来てしまう

上記のように差分を細かく抽出する事でプライバシー攻撃をする事が可能となっています。

集計ポリシーのまとめ

集計ポリシーは、匿名化処理を施したデータの出力において、K-匿名性を実現し、統計情報化するために非常に有効な手段です。ただし、出力を一定件数以上の集計に限ることはできる一方で、出力結果の差分により個人を特定しようとする差分攻撃には弱い側面があります。出力を制御して大きな値のみを表示したり、詳細な検索を制限するなどのアプリケーション層での制御を行う事も可能ですが、その場合はデータ活用の自由度、操作性、提供コストとのトレードオフとなります。
出力内容が細かい粒度になるほど、リスクも高まるため、集計ポリシーだけで完全なプライバシー保護ができるわけではありません。自社が提供するデータサービスにおいて、これらの保護が十分に行われているかは、充分に確認する必要があります。

7.投影ポリシー

公式ドキュメント

目的・効果:

• SQLクエリ結果の出力において、列の投影（表示）を許可または禁止する
• データ共有時に、識別子列（名前や電話番号など）の表示を制約しながら、その値に基づく照合や分析を可能にする
• 特に、パートナー企業間での安全なデータ共有を実現する

実装例:

USE ROLE PRIVACY_HO_ADMIN;
-- 顧客売上データの作成
CREATE OR REPLACE TABLE CUSTOMER_SALES (
    sales_id INT,
    customer_id INT,
    sale_date DATE,
    product_name STRING,
    amount FLOAT,
    payment_method STRING,
    store_region STRING
);

-- 顧客売上データの生成
INSERT INTO CUSTOMER_SALES 
SELECT 
    SEQ4() as sales_id,
    UNIFORM(1, 5, RANDOM()) as customer_id,
    DATEADD('day', UNIFORM(1, 365, RANDOM()), '2023-01-01') as sale_date,
    CASE UNIFORM(1, 5, RANDOM())
        WHEN 1 THEN 'Laptop'
        WHEN 2 THEN 'Smartphone'
        WHEN 3 THEN 'Tablet'
        WHEN 4 THEN 'Desktop PC'
        WHEN 5 THEN 'Printer'
    END as product_name,
    UNIFORM(100, 2000, RANDOM()) as amount,
    CASE UNIFORM(1, 3, RANDOM())
        WHEN 1 THEN 'Credit Card'
        WHEN 2 THEN 'Debit Card'
        WHEN 3 THEN 'Cash'
    END as payment_method,
    CASE UNIFORM(1, 4, RANDOM())
        WHEN 1 THEN 'North'
        WHEN 2 THEN 'South'
        WHEN 3 THEN 'East'
        WHEN 4 THEN 'West'
    END as store_region
FROM TABLE(GENERATOR(ROWCOUNT => 10000));

-- 顧客データに対する投影ポリシー
CREATE OR REPLACE PROJECTION POLICY customer_projection_policy
AS () RETURNS PROJECTION_CONSTRAINT ->
CASE
    WHEN CURRENT_ROLE() = 'PRIVACY_USER_ADMIN'
        THEN PROJECTION_CONSTRAINT(ALLOW => true)
    ELSE PROJECTION_CONSTRAINT(ALLOW => false)
END;
-- 顧客テーブルへの投影ポリシー適用
ALTER TABLE customer_data MODIFY COLUMN customer_id SET PROJECTION POLICY customer_projection_policy;
ALTER TABLE customer_data MODIFY COLUMN first_name SET PROJECTION POLICY customer_projection_policy;
ALTER TABLE customer_data MODIFY COLUMN last_name SET PROJECTION POLICY customer_projection_policy;
ALTER TABLE customer_data MODIFY COLUMN phone_number SET PROJECTION POLICY customer_projection_policy;
ALTER TABLE customer_data MODIFY COLUMN credit_card_number SET PROJECTION POLICY customer_projection_policy;

-- 売上データに対する投影ポリシー
CREATE OR REPLACE PROJECTION POLICY customer_sales_projection_policy
AS () RETURNS PROJECTION_CONSTRAINT ->
CASE
    WHEN CURRENT_ROLE() = 'PRIVACY_USER_ADMIN'
        THEN PROJECTION_CONSTRAINT(ALLOW => true)
    ELSE PROJECTION_CONSTRAINT(ALLOW => false)
END;

-- 売上テーブルへの投影ポリシー適用
ALTER TABLE customer_sales MODIFY COLUMN customer_id SET PROJECTION POLICY customer_sales_projection_policy;

ここまでで準備完了です
それでは実際にクエリを実行しましょう

-- プライバシー保護項目の出力制御① 全項目での出力
USE ROLE PRIVACY_ANALYST;
SELECT * FROM customer_data c JOIN customer_sales s ON c.customer_id = s.customer_id LIMIT 5;

SQLコンパイルエラー：データメトリック関数は
 CUSTOMER_ID
 FIRST_NAME
 LAST_NAME
 PHONE_NUMBER
 CREDIT_CARD_NUMBER
 CUSTOMER_ID の戻り値の型をサポートしません。

-- 結果:ポリシーを設定した項目は出力されないように制御出来ている

-- プライバシー保護項目の出力制御② 出力項目に保護項目があるクエリ
USE ROLE PRIVACY_ANALYST;
SELECT 
    c.customer_id,
    c.region,
    COUNT(DISTINCT c.customer_id) as customer_count,
    SUM(s.amount) as total_sales,
    AVG(s.amount) as avg_sale_amount
FROM customer_data c
JOIN customer_sales s ON c.customer_id = s.customer_id
GROUP BY    c.customer_id,c.region;

SQLコンパイルエラー：データメトリック関数は CUSTOMER_ID の戻り値の型をサポートしません。

-- 結果:集計時の出力が制御出来ている

-- プライバシー保護項目の出力制御③ 出力項目に保護項目がないクエリ
USE ROLE PRIVACY_ANALYST;
SELECT ANNUAL_INCOME,REGION,CUSTOMER_RANK,SMARTPHONE_TYPE,CAR_OWNERSHIP,DEPENDENTS,HOBBY,CONSENT,sales_id,sale_date,product_name,amount,payment_method,store_region FROM customer_data c JOIN customer_sales s ON c.customer_id = s.customer_id Limit 5;

ANNUAL_INCOME	REGION	CUSTOMER_RANK	SMARTPHONE_TYPE	CAR_OWNERSHIP	DEPENDENTS	HOBBY	CONSENT	SALES_ID	SALE_DATE	PRODUCT_NAME	AMOUNT	PAYMENT_METHOD	STORE_REGION
68000	East	2	iOS	0	0	Reading	0	0	2023-09-10	Printer	152	Debit Card	West
78000	North	3	iOS	0	1	Gaming	1	1	2023-02-24	Desktop PC	946	Cash	East
82000	South	4	Android	1	1	Sports	1	2	2023-08-06	Smartphone	1930	Debit Card	South
68000	East	2	iOS	0	0	Reading	0	3	2023-03-04	Printer	1211	Credit Card	South
75000	North	3	iOS	1	2	Movies	1	4	2023-10-11	Smartphone	1094	Credit Card	South

-- 結果:結合条件に指定はできており、非制御項目は従来通り出力出来ている

-- プライバシー保護項目の出力制御④ 条件指定
SELECT 
    c.region,
    COUNT(DISTINCT c.customer_id) as customer_count,
    SUM(s.amount) as total_sales,
    AVG(s.amount) as avg_sale_amount
FROM customer_data c
JOIN customer_sales s ON c.customer_id = s.customer_id
and  c.customer_id ='1'
GROUP BY    c.region;

REGION	CUSTOMER_COUNT	TOTAL_SALES	AVG_SALE_AMOUNT
North	1	2210328	1062.657692308

-- 結果:Where 句ではプライバシー保護項目は自由に取り扱える
-- between, Like, in, <=　など 特に制限はありません

投影ポリシーのまとめ

投影ポリシーはプライバシー項目の出力制御において非常に有効な手段です。出力を制御しつつ、テーブル間の結合や条件指定における柔軟性を両立することが出来るため、エンジニアのデータ設計の自由度を高めることが出来ます。一方でWhere句で自由に設定出来ることで、出力結果の差分により個人を特定しようとする差分攻撃には弱い側面があります。出力を制御して大きな値のみを表示したり、詳細な検索を制限するなどのアプリケーション層での制御を行う事も可能ですが、その場合はデータ活用の自由度、操作性、提供コストとのトレードオフとなります。

第一章のまとめ

Snowflakeは、プライバシー保護を実現するためにさまざまな機能を提供しており、それらは強力で柔軟なプライバシー保護を実現できるものです。 ただし、自社のデータが複雑化し、詳細化していく中で、プライバシー保護の取り組みもさらに高度化していく必要があります。
第一章では個々のプライバシー保護機能の紹介をさせていただきました。
第二章では各機能の組み合わせに加え、Snowflakeが新たに一般提供した差分プライバシーという機能も踏まえた設計例を紹介させていただこうと思います。（現在執筆中です）

本記事に関して

今回の内容には私自身の経験則や実際に試した内容を元にまとめたもので、これを以ってベストプラクティスとは思っておりません。ですが、このような考え方や実装方法はなかなか体系だった情報がなく、よりよりプライバシー保護を考えるため、自分自身の学んだ内容を拙い内容ではありますが、公開させていただこうと思った次第です。
また実際の弊社の実装とは異なる部分やより複雑なケースもあり、そこまでの公開はできませんがある程度のエッセンスは含んでおります。

顧客に対しては、個人情報利用の目的を明確に伝え、理解と同意を得た上で正しく運用することが重要です。同意の範囲内で適切に運用し、より良いサービスを提供することは顧客との約束とだと考えており、そのためにプライバシーテクノロジーを最適かつ最大限に活用することは、データ基盤の必須要件となっています。
弊社ではこれまでにもプライバシー保護を高めるための取り組みを行っていましたが、改善すべき点はまだまだあります。よりセキュアで信頼性の高いデータ基盤を目指して引き続き取り組んでいきます。
長文で読みづらいかもしれませんが、同じように悩み、挑戦されている皆様にとって、本記事が一助となれば幸いです。また逆にこんなやり方あるよ、もっとこうした方が良いといったアドバイスも大変嬉しく思います。