LoginSignup
3
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@fkdfkdfkd(FKD)inKDDIアジャイル開発センター株式会社

シェア先でPIIマスキングを細かく制御したいならデータベースロールを使おう

Last updated at Posted at 2025-09-28

はじめに

Snowflakeを使っていて、PIIを含むデータを別アカウントにシェアしたいけど、開発者には「テーブルがちゃんと存在するか」の確認だけさせたい。

そんな状況ありませんか？

そんなときに使えるのがデータベースロール。

データベースロールがなかった頃、シェアから作成したオブジェクトに対しては「見えるか見えないか」の二択しかなくて、確認のためだけに開発者がセキュリティルームを行き来する...みたいなことになりがちでした。

データベースロール + ダイナミックマスキングの組み合わせで、シェア先でも柔軟にPII制御できるようになったので試してみました。

やりたいこと

  • 開発者：テーブル・ビューの存在確認だけしたい（PIIの値は見なくていい）
  • 利用者：実際のPII含むデータを見たい
  • 両方とも同じシェアから取得したい

開発者がやりたいのは単純で：

  • テーブルやビューが正常にデプロイされているか(シェアが取り込まれているか)確認したい
  • PIIの値自体には興味がない

ということなのですが、データベースロールがなかった頃、あるいはデータベースロールを使っていない環境下だと、この確認作業のためだけにセキュリティルームでの作業が必須でした。

デプロイ確認のためだけに、なぜPII列の実データを見る必要があるのか...という感じです。

前提環境

検証用に以下を準備しました：

  • プロバイダーアカウント：データを提供する通常のSnowflakeアカウント
  • コンシューマーアカウント：Reader Accountを使用
  • 必要なロール：簡単にするためACCOUNTADMINを使用

プロバイダー側の実装

1. 検証環境の準備

-- 検証用データベース・スキーマ作成
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS PII_TEST_DB;
CREATE SCHEMA IF NOT EXISTS PII_TEST_DB.DEMO;

2. PIIタグとマスキングポリシーの作成

ここがポイントです。IS_DATABASE_ROLE_IN_SESSION()を使って、特定のデータベースロールがアクティブかどうかで表示内容を切り替えます：

-- PIIタグ作成（自動伝搬設定付き）
CREATE OR REPLACE TAG PII_TEST_DB.DEMO.PII
    PROPAGATE = ON_DEPENDENCY_AND_DATA_MOVEMENT;

-- VARCHAR型用マスキングポリシー
CREATE OR REPLACE MASKING POLICY PII_TEST_DB.DEMO.PII_VARCHAR_MASK AS 
(val varchar) RETURNS varchar ->
  CASE
    -- データベースロール（実データ表示用）経由は実データ表示
    WHEN IS_DATABASE_ROLE_IN_SESSION('ANALYTICS_RAW_PII_VIEWS_SELECT_ACCESS_ROLE')
    THEN val
    -- システムユーザーは実データ表示
    WHEN CURRENT_ROLE() IN ('SYSADMIN', 'ACCOUNTADMIN')
    THEN val
    -- その他はマスク表示
    ELSE '***MASKED***'
  END;

-- NUMBER型用マスキングポリシー
CREATE OR REPLACE MASKING POLICY PII_TEST_DB.DEMO.PII_NUMBER_MASK AS 
(val number) RETURNS number ->
  CASE
    WHEN IS_DATABASE_ROLE_IN_SESSION('ANALYTICS_RAW_PII_VIEWS_SELECT_ACCESS_ROLE')
    THEN val
    WHEN CURRENT_ROLE() IN ('SYSADMIN', 'ACCOUNTADMIN')
    THEN val
    ELSE -1
  END;

-- タグにマスキングポリシーを適用
ALTER TAG PII_TEST_DB.DEMO.PII 
SET MASKING POLICY PII_TEST_DB.DEMO.PII_VARCHAR_MASK;

ALTER TAG PII_TEST_DB.DEMO.PII 
SET MASKING POLICY PII_TEST_DB.DEMO.PII_NUMBER_MASK;

3. テストデータの作成とタグ適用

-- テストテーブル作成
CREATE OR REPLACE TABLE PII_TEST_DB.DEMO.CUSTOMER_DATA (
    customer_id NUMBER,
    customer_name VARCHAR(100),
    email VARCHAR(200),
    phone_number VARCHAR(20),
    created_at TIMESTAMP
);

-- テストデータ投入
INSERT INTO PII_TEST_DB.DEMO.CUSTOMER_DATA VALUES
(1, '田中太郎', 'tanaka@example.com', '090-1234-5678', CURRENT_TIMESTAMP()),
(2, '佐藤花子', 'sato@example.com', '080-9876-5432', CURRENT_TIMESTAMP()),
(3, '鈴木一郎', 'suzuki@example.com', '070-1111-2222', CURRENT_TIMESTAMP());

-- PIIを含む列にタグ適用
ALTER TABLE PII_TEST_DB.DEMO.CUSTOMER_DATA
MODIFY COLUMN customer_name 
SET TAG PII_TEST_DB.DEMO.PII = 'personal_name';

ALTER TABLE PII_TEST_DB.DEMO.CUSTOMER_DATA
MODIFY COLUMN email 
SET TAG PII_TEST_DB.DEMO.PII = 'email';

ALTER TABLE PII_TEST_DB.DEMO.CUSTOMER_DATA
MODIFY COLUMN phone_number 
SET TAG PII_TEST_DB.DEMO.PII = 'phone';

PROPAGATE = ON_DEPENDENCY_AND_DATA_MOVEMENTを設定しているため、データパイプラインの中でCTASでテーブルを作成してもタグが自動継承されます。

この設定を入れていないと、CLONEやビュー作成の時しかタグが有効になりません。
つまり、テーブルの洗い替え等でCTASを実行してしまうとPIIマスキングが外れてしまいますので要注意です。

データパイプラインを構成する場合には必須の考慮点です。

4. シェア用ビューの作成

-- シェア用のセキュアビュー作成
CREATE OR REPLACE SECURE VIEW PII_TEST_DB.DEMO.CUSTOMER_SUMMARY
AS SELECT 
    customer_id,
    customer_name,  -- PIIタグ継承
    email,          -- PIIタグ継承  
    phone_number,   -- PIIタグ継承
    created_at
FROM PII_TEST_DB.DEMO.CUSTOMER_DATA;

5. データベースロールの作成と権限付与

シェア先での制御用にデータベースロールを作成します：

-- PII表示可能ロール（利用者用）
CREATE DATABASE ROLE PII_TEST_DB.ANALYTICS_RAW_PII_VIEWS_SELECT_ACCESS_ROLE;

-- PII非表示ロール（開発者用）
CREATE DATABASE ROLE PII_TEST_DB.ANALYTICS_MASKED_PII_VIEWS_SELECT_ACCESS_ROLE;

-- 各ロールに必要な権限を付与
GRANT USAGE ON SCHEMA PII_TEST_DB.DEMO
    TO DATABASE ROLE PII_TEST_DB.ANALYTICS_RAW_PII_VIEWS_SELECT_ACCESS_ROLE;
    
GRANT SELECT ON VIEW PII_TEST_DB.DEMO.CUSTOMER_SUMMARY
    TO DATABASE ROLE PII_TEST_DB.ANALYTICS_RAW_PII_VIEWS_SELECT_ACCESS_ROLE;

GRANT USAGE ON SCHEMA PII_TEST_DB.DEMO
    TO DATABASE ROLE PII_TEST_DB.ANALYTICS_MASKED_PII_VIEWS_SELECT_ACCESS_ROLE;
    
GRANT SELECT ON VIEW PII_TEST_DB.DEMO.CUSTOMER_SUMMARY
    TO DATABASE ROLE PII_TEST_DB.ANALYTICS_MASKED_PII_VIEWS_SELECT_ACCESS_ROLE;

6. シェア設定

データベースロールも含めてシェアします：

-- シェア作成
CREATE SHARE PII_DEMO_SHARE;

-- DB、スキーマ、ビューの権限
GRANT USAGE ON DATABASE PII_TEST_DB TO SHARE PII_DEMO_SHARE;
GRANT USAGE ON SCHEMA PII_TEST_DB.DEMO TO SHARE PII_DEMO_SHARE;
GRANT SELECT ON VIEW PII_TEST_DB.DEMO.CUSTOMER_SUMMARY TO SHARE PII_DEMO_SHARE;

-- データベースロールもシェア
GRANT DATABASE ROLE PII_TEST_DB.ANALYTICS_RAW_PII_VIEWS_SELECT_ACCESS_ROLE
    TO SHARE PII_DEMO_SHARE;
GRANT DATABASE ROLE PII_TEST_DB.ANALYTICS_MASKED_PII_VIEWS_SELECT_ACCESS_ROLE
    TO SHARE PII_DEMO_SHARE;

-- リーダーアカウントにシェア
ALTER SHARE PII_DEMO_SHARE ADD ACCOUNTS=YOUR_READER_ACCOUNT_LOCATOR;

コンシューマー側での動作確認

1. シェアからデータベース作成

-- リーダーアカウント側でシェアからDB作成
USE ROLE ACCOUNTADMIN;
CREATE DATABASE PII_SHARED_DB FROM SHARE PROVIDER_ACCOUNT.PII_DEMO_SHARE;

2. 検証用ロールの準備

-- 検証用の開発者ロール作成（存在しない場合）
CREATE ROLE IF NOT EXISTS DEVELOPER_FUNC_ROLE;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE DEVELOPER_FUNC_ROLE;
GRANT ROLE DEVELOPER_FUNC_ROLE TO USER YOUR_USERNAME;

3. 初期状態の確認（データベースロール未付与）

-- developer_func_roleでアクセス（データベースロール未付与状態）
USE ROLE DEVELOPER_FUNC_ROLE;
USE SECONDARY ROLE NONE;

SELECT * FROM PII_SHARED_DB.DEMO.CUSTOMER_SUMMARY;
-- 結果：エラー。オブジェクトの存在すら確認できない。

データベースロールを付与していない状態では、そもそもテーブル/ビューにアクセスできません。

4. PII非表示ロールを付与

-- 開発者にPII非表示ロールを付与
USE ROLE ACCOUNTADMIN;
GRANT DATABASE ROLE PII_SHARED_DB.ANALYTICS_MASKED_PII_VIEWS_SELECT_ACCESS_ROLE
    TO ROLE DEVELOPER_FUNC_ROLE;

-- 再度アクセス
USE ROLE DEVELOPER_FUNC_ROLE;
USE SECONDARY ROLE NONE;
SELECT * FROM PII_SHARED_DB.DEMO.CUSTOMER_SUMMARY;

こんな感じでマスキングが効いた状態で閲覧できます！

CUSTOMER_ID | CUSTOMER_NAME | EMAIL         | PHONE_NUMBER | CREATED_AT
1           | ***MASKED***  | ***MASKED***  | ***MASKED*** | 2025-01-15 10:30:00
2           | ***MASKED***  | ***MASKED***  | ***MASKED*** | 2025-01-15 10:30:01  
3           | ***MASKED***  | ***MASKED***  | ***MASKED*** | 2025-01-15 10:30:02

開発者が欲しかった情報（テーブル存在、データ件数、スキーマ構造）は確認できて、PIIは見えない状態です。

5. PII表示可能ロールに切り替え

-- PII表示可能ロールも付与してみる
USE ROLE ACCOUNTADMIN;
GRANT DATABASE ROLE PII_SHARED_DB.ANALYTICS_RAW_PII_VIEWS_SELECT_ACCESS_ROLE
    TO ROLE DEVELOPER_FUNC_ROLE;

USE ROLE DEVELOPER_FUNC_ROLE;
USE SECONDARY ROLE NONE;
SELECT * FROM PII_SHARED_DB.DEMO.CUSTOMER_SUMMARY;

今度は実データが表示されます：

CUSTOMER_ID | CUSTOMER_NAME | EMAIL              | PHONE_NUMBER  | CREATED_AT
1           | 田中太郎      | tanaka@example.com | 090-1234-5678 | 2025-01-15 10:30:00
2           | 佐藤花子      | sato@example.com   | 080-9876-5432 | 2025-01-15 10:30:01
3           | 鈴木一郎      | suzuki@example.com | 070-1111-2222 | 2025-01-15 10:30:02

同じユーザーロールでも、どのデータベースロールが付与されているかによって表示内容が変わります。これが今回の肝です。

実際の開発においては、これをCICDによって自動で付与させる感じですね。

運用での活用方法

この仕組みで以下のような運用が可能になります：

利用者向け設定

-- 利用者用ロールにはPII表示可能ロールを付与
CREATE ROLE CONSUMER_FUNC_ROLE;
GRANT DATABASE ROLE PII_SHARED_DB.ANALYTICS_RAW_PII_VIEWS_SELECT_ACCESS_ROLE
    TO ROLE CONSUMER_FUNC_ROLE;

開発者向け設定

-- 開発者ロールにはPII非表示ロールのみ付与
GRANT DATABASE ROLE PII_SHARED_DB.ANALYTICS_MASKED_PII_VIEWS_SELECT_ACCESS_ROLE
    TO ROLE DEVELOPER_FUNC_ROLE;

注意点

1. 検証の際はUSE SECONDARY ROLE NONEを実行しよう

リーダーアカウントでの検証時は、必ずUSE SECONDARY ROLE NONE;を実行することをお勧めします。

特に検証環境では、検証目的のためにより上位の権限が利用できるケースがほとんどかなと思います。
そのような状況下では、セカンダリロールが有効だと意図しない権限で実行される可能性があります。

本番環境では厳密に権限分離をしているはずなので、環境差分による思わぬ挙動の違いに遭遇する可能性があります。

2. シェア先でのデータ操作に注意

今回確認したのはシェアから作成したビューに対する直接的なマスキングの挙動確認でした。
シェアされたテーブルをもとに、さらにデータパイプラインを介して新しいテーブルを作成する場合、操作を行うロールによって結果が変わります：

  • マスク表示ロールで操作：作成されるテーブルもマスク値のまま
  • 実データ表示ロールで操作：作成されるテーブルに生の値が保存される

マスクされた値が正となったまま、後続データモデルでは元の値が復元できない、みたいなことにもなるので要注意です。

なお、リーダーアカウントでは「読み取り専用」となるのでこのような操作はしない(できない)かと思いますが、通常アカウントにシェアする際は気にしておいた方がいいですね。

補足 : データベースロール x REFERENCE_USAGE の話

ビュー(セキュアビュー)をシェアする際、そのビューを構成するテーブルが複数データベースにまたがる時は REFERENCE_USAGE という権限が必要になります。

この REFERENCE_USAGEは、データベースロールと相性が悪いです。

https://docs.snowflake.com/ja/user-guide/data-sharing-multiple-db

データベースロールを使用して、複数のデータベースのデータを共有することはできません。REFERENCE_USAGE 権限を データベースロール に付与することはできません。

とあるのです。

そこで気になるのが、タグの場合はどうなるんだ？ということです。

データベース設計によっては、タグやマスキングポリシーを1つのデータベースで一元管理するケースがあるかと思います。

つまり、シェアする対象のデータベースロール・テーブルが入っているデータベースとは異なるデータベース配下にタグがある時にはどうすればいいかが気になると思います。

https://docs.snowflake.com/ja/user-guide/object-tagging/interaction#data-sharing

共有ビューとタグが異なるデータベースに存在する場合は、タグを含むデータベースに対する REFERENCE_USAGE 権限を共有に付与します。

と書いてあります。

おぉ...

じゃあデータベースロールを使用するケースにおいて、この仕様は噛み合わないんじゃないのか？と思いました。

実際に試してみた結果

こちらですが...

試してみたところ、なんと期待通り動きました！(朗報)

異なるデータベースにあるタグがシェア越しに使用でき、マスキングも機能するのです。

サポートにも問い合わせてみたところ、偶然動いたわけでもなくSnowflakeとしてサポートしている機能/動作とのことでした。

操作の流れは以下です。

  • シェアに、データベース(DB_A)/スキーマ/テーブルやビューが使用できるようにGRANTする
    • このテーブル・ビューには、異なるデータベース(DB_B)配下のタグを使用(SET)しているものとします
  • シェアに、データベース(DB_A)のデータベースロールを使用できるようにGRANTする
  • シェアに、タグが入っている別データベース(DB_B)に対してREFERENCE_USAGE権限を付与する

ちょっと不思議な感じがしますが、これで動作しました。

マニュアルからは読み取れない仕様かなと思いましたので試した&ここに記載しておきました。

まとめ

データベースロールとダイナミックマスキングポリシーの組み合わせで、シェア先でいい感じにPII制御ができるようになりました。

従来の「見えるか見えないか」の2択から脱却して、同じデータに対して利用者タイプに応じた表示制御を実現できるのは大きな進歩です。

デプロイ確認のためのセキュリティルーム往復が減りそうです。

同じような課題で困っている方の参考になれば幸いです。

以上です。

3
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?