Snowflakeのマスキングポリシーと行アクセスポリシーを試してみた
かわばたです。
マスキングポリシーは列レベルのセキュリティを実現し、ユーザーのロールに応じて「セル内のデータをどのように見せるか」を制御し、行アクセスポリシーは行レベルのセキュリティを実現し、「どの行をユーザーに見せるか」を制御します。
組織のデータガバナンス体制を構築するための機能について、実際にどのような形で使えそうか検証してみます。
対象読者
- マスキングポリシー・行アクセスポリシーについて知りたい方
- 組織のデータガバナンス体制構築に興味がある方
事前準備と検証環境
実際に検証するために、ロール・データベース・使用データ等を作成します。
必要な環境
Snowflakeのアカウント: かわばたはSnowflakeのトライアルアカウントEnterprise版で試しています。
ロールと権限
検証にあたり以下のロールを作成しました。
ロール名 | 内容 |
---|---|
GOVERNANCE_ADMIN | ポリシーの定義と管理を一元的に担う、高度な権限を持つロール。 |
HR_MANAGER | 個人情報など機密性の高いデータへのアクセスが許可されたビジネスロール。 |
SALES_MANAGER | 特定の地域のデータにのみアクセスが許可されたビジネスロール。 |
ANALYST | データへのアクセスが制限された、一般的な分析者向けのビジネスロール。 |
-- ロールの切り替え
USE ROLE SECURITYADMIN;
-- カスタムロールの作成
CREATE ROLE IF NOT EXISTS GOVERNANCE_ADMIN;
CREATE ROLE IF NOT EXISTS HR_MANAGER;
CREATE ROLE IF NOT EXISTS SALES_MANAGER;
CREATE ROLE IF NOT EXISTS ANALYST;
-- ロール階層の設定 (GOVERNANCE_ADMINが他のロールを管理できるように)
GRANT ROLE HR_MANAGER TO ROLE GOVERNANCE_ADMIN;
GRANT ROLE SALES_MANAGER TO ROLE GOVERNANCE_ADMIN;
GRANT ROLE ANALYST TO ROLE GOVERNANCE_ADMIN;
-- 現在のユーザーにGOVERNANCE_ADMINロールを付与してテストを容易にする
-- KAWABATA の部分はご自身のユーザー名に置き換えてください
GRANT ROLE GOVERNANCE_ADMIN TO USER KAWABATA;
次に、GOVERNANCE_ADMIN
ロールに、ポリシーを作成し、オブジェクトに適用するために必要な権限を付与します。
-- GOVERNANCE_ADMINにポリシー適用権限を付与
GRANT APPLY MASKING POLICY ON ACCOUNT TO ROLE GOVERNANCE_ADMIN;
GRANT APPLY ROW ACCESS POLICY ON ACCOUNT TO ROLE GOVERNANCE_ADMIN;
検証用にデータベースとスキーマを作成します。
-- ロールの切り替え
USE ROLE SYSADMIN;
-- 検証用データベースの作成
CREATE DATABASE IF NOT EXISTS GOVERNANCE_TEST;
-- データベース内にスキーマを作成
CREATE SCHEMA IF NOT EXISTS GOVERNANCE_TEST.RAW_DATA;
CREATE SCHEMA IF NOT EXISTS GOVERNANCE_TEST.POLICIES;
GOVERNANCE_ADMIN
にPOLICIES
スキーマでのポリシー作成権限を付与します。
-- ロールの切り替え
USE ROLE SECURITYADMIN;
-- GOVERNANCE_ADMINにPOLICIESスキーマでのポリシー作成権限を付与
GRANT USAGE ON DATABASE GOVERNANCE_TEST TO ROLE GOVERNANCE_ADMIN;
GRANT USAGE ON SCHEMA GOVERNANCE_TEST.POLICIES TO ROLE GOVERNANCE_ADMIN;
GRANT CREATE MASKING POLICY ON SCHEMA GOVERNANCE_TEST.POLICIES TO ROLE GOVERNANCE_ADMIN;
GRANT CREATE ROW ACCESS POLICY ON SCHEMA GOVERNANCE_TEST.POLICIES TO ROLE GOVERNANCE_ADMIN;
-- 他のロールにもデータベースとスキーマ、ウェアハウスへのUSAGE権限を付与
GRANT USAGE ON DATABASE GOVERNANCE_TEST TO ROLE HR_MANAGER;
GRANT USAGE ON DATABASE GOVERNANCE_TEST TO ROLE SALES_MANAGER;
GRANT USAGE ON DATABASE GOVERNANCE_TEST TO ROLE ANALYST;
GRANT USAGE ON SCHEMA GOVERNANCE_TEST.RAW_DATA TO ROLE HR_MANAGER;
GRANT USAGE ON SCHEMA GOVERNANCE_TEST.RAW_DATA TO ROLE SALES_MANAGER;
GRANT USAGE ON SCHEMA GOVERNANCE_TEST.RAW_DATA TO ROLE ANALYST;
使用データ
最後に生成AIで作成したダミーデータをGOVERNANCE_TEST.RAW_DATA
へ格納していきます。
-- ロールとコンテキストの切り替え
USE DATABASE GOVERNANCE_TEST;
USE SCHEMA RAW_DATA;
-- EMPLOYEESテーブルの作成
CREATE OR REPLACE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER,
FULL_NAME VARCHAR,
EMAIL VARCHAR,
SSN VARCHAR,
SALARY NUMBER(10, 2),
VISIBILITY_LEVEL VARCHAR -- 'PUBLIC' または 'CONFIDENTIAL'
);
-- SALES_TRANSACTIONSテーブルの作成
CREATE OR REPLACE TABLE SALES_TRANSACTIONS (
TRANSACTION_ID VARCHAR,
CUSTOMER_ID VARCHAR,
PRODUCT_ID VARCHAR,
SALE_AMOUNT NUMBER(10, 2),
SALE_DATE DATE,
SALES_REGION VARCHAR -- 'NA', 'EU', 'APAC'
);
-- EMPLOYEESテーブルへのデータ投入
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FULL_NAME, EMAIL, SSN, SALARY, VISIBILITY_LEVEL) VALUES
(1, 'Alice Johnson', 'alice.j@example.com', '123-456-7890', 90000.00, 'CONFIDENTIAL'),
(2, 'Bob Williams', 'bob.w@example.com', '234-567-8901', 120000.00, 'CONFIDENTIAL'),
(3, 'Charlie Brown', 'charlie.b@example.com', '345-678-9012', 75000.00, 'PUBLIC'),
(4, 'Diana Miller', 'diana.m@example.com', '456-789-0123', 150000.00, 'CONFIDENTIAL');
-- SALES_TRANSACTIONSテーブルへのデータ投入
INSERT INTO SALES_TRANSACTIONS (TRANSACTION_ID, CUSTOMER_ID, PRODUCT_ID, SALE_AMOUNT, SALE_DATE, SALES_REGION) VALUES
('TXN001', 'CUST101', 'PROD_A', 150.00, '2023-01-15', 'NA'),
('TXN002', 'CUST102', 'PROD_B', 200.50, '2023-01-16', 'EU'),
('TXN003', 'CUST103', 'PROD_A', 140.00, '2023-02-10', 'APAC'),
('TXN004', 'CUST101', 'PROD_C', 500.75, '2023-02-12', 'NA'),
('TXN005', 'CUST104', 'PROD_B', 220.00, '2023-03-05', 'EU'),
('TXN006', 'CUST105', 'PROD_D', 80.25, '2023-03-08', 'NA');
-- 各ビジネスロールにテーブルへのSELECT権限を付与
GRANT SELECT ON TABLE EMPLOYEES TO ROLE HR_MANAGER;
GRANT SELECT ON TABLE EMPLOYEES TO ROLE ANALYST;
GRANT SELECT ON TABLE SALES_TRANSACTIONS TO ROLE SALES_MANAGER;
GRANT SELECT ON TABLE SALES_TRANSACTIONS TO ROLE ANALYST;
ダイナミックデータマスキング
この章では、基本的なロールベースのマスキングから始め、条件付きマスキング、タグベースのアプローチへと段階的に検証していきます。
一般的に使用されるマスキング手法は以下の通りです。
マスキング手法 | 説明 | ユースケース |
---|---|---|
完全マスキング | 列の値を固定の文字列(例:'MASKED')に完全に置き換える。 | 社会保障番号、パスワードなど、値そのものを見せる必要がない機密情報。 |
部分マスキング | データの一部のみを表示し、残りをマスクする(例:クレジットカード番号の下4桁のみ表示)。 | クレジットカード番号、電話番号など、本人確認のために一部の情報が必要な場合。 |
仮名化/ハッシュ化 | SHA2 などのハッシュ関数を用いて、元の値を一意の仮名値に変換する。元の値は復元できないが、同じ入力値は常に同じ出力値になるため、分析上の結合キーとして利用できる。 |
ユーザーID、メールアドレスなど、個人を特定せずに分析を行いたい場合。 |
暗号化 | ENCRYPT /DECRYPT 関数とパスフレーズを用いてデータを暗号化し、特定のユーザーのみが復号できるようにする。 |
非常に機密性の高い医療情報や金融情報。 |
基本的なロールベースマスキング
マスキングポリシーの作成
作成したPOLICIES
スキーマに2つのマスキングポリシーを作成します。
USE ROLE GOVERNANCE_ADMIN;
USE SCHEMA GOVERNANCE_TEST.POLICIES;
-- EMAIL列用のマスキングポリシー
CREATE OR REPLACE MASKING POLICY email_mask AS (val STRING) RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() IN ('HR_MANAGER', 'GOVERNANCE_ADMIN') THEN val
ELSE '***MASKED***'
END;
-- SSN列用のマスキングポリシー
CREATE OR REPLACE MASKING POLICY ssn_mask AS (val STRING) RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() IN ('HR_MANAGER', 'GOVERNANCE_ADMIN') THEN val
ELSE 'XXX-XXX-XXXX'
END;
ポリシーの適用
作成したポリシーをEMPLOYEESテーブルの各列に適用します。
USE ROLE GOVERNANCE_ADMIN;
USE SCHEMA GOVERNANCE_TEST.RAW_DATA;
-- EMPLOYEESテーブルのEMAIL列にポリシーを適用
ALTER TABLE IF EXISTS EMPLOYEES MODIFY COLUMN EMAIL SET MASKING POLICY POLICIES.email_mask;
-- EMPLOYEESテーブルのSSN列にポリシーを適用
ALTER TABLE IF EXISTS EMPLOYEES MODIFY COLUMN SSN SET MASKING POLICY POLICIES.ssn_mask;
結果の検証
クエリを実行し、出力がどのように変わるかを確認します。
HR_MANAGER
ロールで確認すると以下のようにすべて確認できます。
-- HR_MANAGERロールでクエリを実行 (元の値が表示される)
USE ROLE HR_MANAGER;
SELECT FULL_NAME, EMAIL, SSN FROM GOVERNANCE_TEST.RAW_DATA.EMPLOYEES;
ANALYST
ロールで確認すると、以下のようにマスキングされていました。
-- ANALYSTロールでクエリを実行 (マスクされた値が表示される)
USE ROLE ANALYST;
SELECT FULL_NAME, EMAIL, SSN FROM GOVERNANCE_TEST.RAW_DATA.EMPLOYEES;
権限が付与されているユーザーに応じて表記が異なることが確認できました!
部分マスキング
部分マスキングについても同様に可能か確認していきます。
USE ROLE GOVERNANCE_ADMIN;
USE SCHEMA GOVERNANCE_TEST.POLICIES;
-- EMAIL列用の部分マスキングポリシー
CREATE OR REPLACE MASKING POLICY email_mask_partial AS (val STRING) RETURNS STRING ->
CASE
WHEN CURRENT_ROLE() IN ('HR_MANAGER', 'GOVERNANCE_ADMIN') THEN val
-- '@'より前の部分を'*****@'に置換
ELSE REGEXP_REPLACE(val, '.+\@', '*****@')
END;
-- ポリシーの適用
ALTER TABLE GOVERNANCE_TEST.RAW_DATA.EMPLOYEES MODIFY COLUMN EMAIL SET MASKING POLICY email_mask_partial;
ANALYST
ロールで確認すると、以下のように部分的にマスキングされていました。
条件付きデータマスキング
条件付きポリシーの作成
salary_mask
という名前のポリシーを作成します。
ここでは、visibility_level
カラムがPUBLIC
の時だけ値を表示し、それ以外は-1
を表記するように設定しています。
USE ROLE GOVERNANCE_ADMIN;
USE SCHEMA GOVERNANCE_TEST.POLICIES;
CREATE OR REPLACE MASKING POLICY salary_mask AS (val NUMBER, visibility_level VARCHAR) RETURNS NUMBER ->
CASE
WHEN CURRENT_ROLE() IN ('HR_MANAGER', 'GOVERNANCE_ADMIN') THEN val
WHEN visibility_level = 'PUBLIC' THEN val
ELSE -1 -- NUMBER型なので、マスク値もNUMBER型にする
END;
条件付きポリシーの適用
USE ROLE GOVERNANCE_ADMIN;
USE SCHEMA GOVERNANCE_TEST.RAW_DATA;
ALTER TABLE IF EXISTS EMPLOYEES MODIFY COLUMN SALARY
SET MASKING POLICY POLICIES.salary_mask
USING (SALARY, VISIBILITY_LEVEL);
結果の検証
HR_MANAGER
ロールで確認すると以下のようにすべて確認できます。
-- HR_MANAGERロールでクエリを実行 (すべての給与が表示される)
USE ROLE HR_MANAGER;
SELECT FULL_NAME, SALARY, VISIBILITY_LEVEL FROM GOVERNANCE_TEST.RAW_DATA.EMPLOYEES;
ANALYST
ロールで確認すると、以下のようにマスキングされていました。
-- ANALYSTロールでクエリを実行 (VISIBILITY_LEVELが'PUBLIC'の行のみ給与が表示される)
USE ROLE ANALYST;
SELECT FULL_NAME, SALARY, VISIBILITY_LEVEL FROM GOVERNANCE_TEST.RAW_DATA.EMPLOYEES;
付与した条件に応じて表記が異なることが確認できました!
タグベースマスキング
先ほどの検証では、ALTER TABLE... SET MASKING POLICY
という命令的なコマンドでポリシーを適用してきました。これは直接的で分かりやすいですが、オブジェクト数が増えると管理が煩雑になり、適用漏れなどリスクが高まります。
タグベースマスキングは「この列はPII(個人情報)である」と宣言するような形です。
タグの作成
USE ROLE SECURITYADMIN;
-- GOVERNANCE_ADMINロールにPOLICIESスキーマでのタグ作成権限を付与
GRANT CREATE TAG ON SCHEMA GOVERNANCE_TEST.POLICIES TO ROLE GOVERNANCE_ADMIN;
USE ROLE GOVERNANCE_ADMIN;
USE SCHEMA GOVERNANCE_TEST.POLICIES;
CREATE OR REPLACE TAG pii_email_tag COMMENT = 'Tag for PII email data';
CREATE OR REPLACE TAG pii_ssn_tag COMMENT = 'Tag for PII SSN data';
ポリシーとタグの関連付け
先ほど作成したポリシーをタグに紐づけます。
-- pii_email_tagにemail_maskポリシーを関連付ける
ALTER TAG POLICIES.pii_email_tag SET MASKING POLICY POLICIES.email_mask;
-- pii_ssn_tagにssn_maskポリシーを関連付ける
ALTER TAG POLICIES.pii_ssn_tag SET MASKING POLICY POLICIES.ssn_mask;
列へのタグ適用
EMPLOYEESテーブルの各列に、新しく作成したタグを適用します。
USE ROLE GOVERNANCE_ADMIN;
USE SCHEMA GOVERNANCE_TEST.RAW_DATA;
-- 既存のポリシーを一旦解除
ALTER TABLE IF EXISTS EMPLOYEES MODIFY COLUMN EMAIL UNSET MASKING POLICY;
ALTER TABLE IF EXISTS EMPLOYEES MODIFY COLUMN SSN UNSET MASKING POLICY;
-- EMAIL列にpii_email_tagを適用
ALTER TABLE EMPLOYEES MODIFY COLUMN EMAIL SET TAG POLICIES.pii_email_tag = 'sensitive_email';
-- SSN列にpii_ssn_tagを適用
ALTER TABLE EMPLOYEES MODIFY COLUMN SSN SET TAG POLICIES.pii_ssn_tag = 'sensitive_ssn';
INFORMATION_SCHEMA.POLICY_REFERENCES
テーブル関数を使用して、タグ経由でポリシーが適用されていることをクエリで確認してみます。
SELECT *
FROM TABLE(GOVERNANCE_TEST.INFORMATION_SCHEMA.POLICY_REFERENCES(
REF_ENTITY_NAME => 'GOVERNANCE_TEST.RAW_DATA.EMPLOYEES',
REF_ENTITY_DOMAIN => 'TABLE'
));
-- このクエリ結果で、POLICY_NAME、TAG_NAME、REF_COLUMN_NAMEが正しく設定されていることを確認
プログラムベースでは確認できました。
ANALYSTロールでも確認し、下記のようになりました。
タグが付いている部分がマスキングされていることが確認できました!
行アクセスポリシー
行アクセスポリシーはスキーマレベルのオブジェクトであり、保護されたテーブルに対するすべてのクエリに動的に適用されるWHERE句のように機能します。
実際に試していきます。
マッピングテーブルの作成
POLICIES
スキーマに、どのロールがどの地域にアクセスできるかを定義するマッピングテーブルを作成します。
USE ROLE GOVERNANCE_ADMIN;
USE SCHEMA GOVERNANCE_TEST.POLICIES;
-- GOVERNANCE_ADMINロールにPOLICIESスキーマでのテーブル作成権限を付与
GRANT CREATE TABLE ON SCHEMA GOVERNANCE_TEST.POLICIES TO ROLE GOVERNANCE_ADMIN;
CREATE OR REPLACE TABLE REGION_ACCESS_MAP (
ALLOWED_ROLE VARCHAR,
SALES_REGION VARCHAR
);
-- マッピングデータを投入
INSERT INTO REGION_ACCESS_MAP (ALLOWED_ROLE, SALES_REGION) VALUES
('SALES_MANAGER', 'NA'),
('SALES_MANAGER', 'EU');
-- SALES_MANAGERロールにマッピングテーブルへのSELECT権限を付与
GRANT SELECT ON TABLE REGION_ACCESS_MAP TO ROLE SALES_MANAGER;
行アクセスポリシーの作成
USE ROLE GOVERNANCE_ADMIN;
USE SCHEMA GOVERNANCE_TEST.POLICIES;
CREATE OR REPLACE ROW ACCESS POLICY region_filter_policy
AS (region VARCHAR) RETURNS BOOLEAN ->
CASE
WHEN CURRENT_ROLE() IN ('GOVERNANCE_ADMIN') THEN TRUE
WHEN CURRENT_ROLE() IN ('SALES_MANAGER') THEN
EXISTS (
SELECT 1 FROM GOVERNANCE_TEST.POLICIES.REGION_ACCESS_MAP
WHERE ALLOWED_ROLE = CURRENT_ROLE()
AND SALES_REGION = region
)
ELSE FALSE
END;
ポリシーの適用
-- ポリシーの適用
USE ROLE GOVERNANCE_ADMIN;
USE SCHEMA GOVERNANCE_TEST.RAW_DATA;
ALTER TABLE IF EXISTS SALES_TRANSACTIONS
ADD ROW ACCESS POLICY POLICIES.region_filter_policy ON (SALES_REGION);
結果の検証
-- GOVERNANCE_ADMINロールでクエリを実行 (すべての行が表示される)
USE ROLE GOVERNANCE_ADMIN;
SELECT * FROM GOVERNANCE_TEST.RAW_DATA.SALES_TRANSACTIONS;
-- 6行返される
-- SALES_MANAGERロールでクエリを実行 (NAとEUの行のみ表示される)
USE ROLE SALES_MANAGER;
SELECT * FROM GOVERNANCE_TEST.RAW_DATA.SALES_TRANSACTIONS;
-- 5行返される (NA: 3行, EU: 2行, APAC: 1行のうち、NAとEUの行)
-- マッピングテーブルの定義により、NAとEUの行のみが表示される
-- ANALYSTロールでクエリを実行 (どの行も表示されない)
USE ROLE ANALYST;
SELECT * FROM GOVERNANCE_TEST.RAW_DATA.SALES_TRANSACTIONS;
-- 0行返される
count
も確認してみましたが、同様に0件でした。
最後に
いかがでしたでしょうか。
マスキングポリシーと行アクセスポリシーは組織のデータガバナンス体制を構築する上で重要な機能だと感じました。
ただ、無計画に設定してしまい本来閲覧すべきデータ(決済金額や利用者数など)が見えなくなってしまうのは本末転倒なので、導入の際は慎重な確認作業が必要です。
この記事が何かの参考になれば幸いです!