【Snowflake】Snowflake ～ Dynamic masking ～

Snowflake

■ はじめに

　個人情報の扱いが厳しくなった昨今。
そこで、Snowflake におけるデータをマスキングする方法を
調べてみた。

目次

【１】ダイナミックマスキング
【２】適用方法
　１）使用上の注意
【３】サンプル
　例１：Hello world
　例２：条件付きのマスキング
【４】関連するSQL文

【１】ダイナミックマスキング

* Snowflakeのマスク機能。

https://docs.snowflake.com/ja/user-guide/security-column-ddm-intro.html

* 以下、公式ではないけど、読み易いサイト。

https://knowledge.insight-lab.co.jp/snowflake/dynamic-data-masking
https://dev.classmethod.jp/articles/dbt-package-dbt_snow_mask/
https://qiita.com/ninomiyatsu/items/025435abf54bd1ac7d94

【２】適用方法

１）マスキングポリシーを作成
２）マスキングポリシーを対象テーブルに適用
３）動作確認
 => 後述「【３】サンプル」を参考にやってみるといいかも

１）使用上の注意

* マスキングポリシー削除やREPLACEする際には、
　一旦、ALTER TABLEでマスキングポリシーUNSETしなくてはならない
 => そうしないと以下のエラーが発生する

ポリシーが紐づいたままでREPLACE/DROPした場合のエラー 

SQL complication error:
Plicy TEL_MASKING cannot be dropped/replaced
as it is associated with one or more entities.

 => 「alter table  test_user modify column tel_number unset masking policy;」
　　で紐づけたマスキングポリシーを外してから再実行

【３】サンプル

例１：Hello world

１）マスキングポリシーを作成 

* CREATE MASKING POLICYで、マスキングポリシーを作成する

https://docs.snowflake.com/ja/sql-reference/sql/create-masking-policy.html

create or replace masking policy tel_masking as (tel_val varchar) returns varchar ->
  case when
    current_role() in ('ACCOUNTADMIN','SYSADMIN') 
      then
        tel_val
      else
        '********'
  end;

２）マスキングポリシーを対象テーブルに適用 

* ALTER TABLE ... ALTER COLUMNで、
　マスキングポリシーを対象テーブルに適用する

https://docs.snowflake.com/ja/sql-reference/sql/alter-table-column.html

-- テーブル作成(テスト用)
create table test_user if not exists (
  id varchar default null,
  name varchar(100) default null,
  country varchar(2) default null,
  birth_date date default null,
  tel_number varchar(20)
);

-- ALTER TABLE ... ALTER COLUMNで適用する
-- ALTER TABLE <table_name>
--  ALTER COLUMN <target_column>
--  SET MASKING POLICY <policy_name>
--  [ USING ( <col1_name> , cond_col_1 , ... ) ]
alter table
  test_user
alter column
  tel_number
set masking policy
  tel_masking
;

-- 補足：CREATE TABLE文実行時に適用することも可能

３）動作確認 

* 'ACCOUNTADMIN','SYSADMIN'とそれ以外のロールで
　切り替えて動作確認。

テストデータ および SELECT文 

-- INSERT
INSERT INTO
  test_user  (id, name, country, birth_date, tel_number)
VALUES
  ('x0001', 'Mike', 'US', '1982-10-11', '01-2345-6789'),
  ('x0002', 'Tom', 'US', '2000-01-23', '02-2345-6789'),
  ('x0003', 'Alice', 'JP', '1945-12-31', '11-2345-6789'),
  ('x0004', 'Sam', 'AU', '1945-09-05', '1234'),
  ('x0005', 'Kevin', 'JP', '2011-05-01', NULL),
  ('x0006', 'Ken', NULL, '2011-05-01', '2345-6789')
;

-- SELECT
SELECT * FROM test_user;

例２：条件付きのマスキング

１）マスキングポリシーを作成 

-- ★注意：ポリシーを一旦外さないとreplace/dropできないので unset
--  => 上記「１）使用上の注意」参照
alter table  test_user modify column tel_number unset masking policy;

-- 引数「country」を追加し、マスクも複雑にした 
create or replace masking policy tel_masking as (tel_val varchar, country varchar) returns varchar ->
  case when
    current_role() in ('ACCOUNTADMIN','SYSADMIN') 
      then
        tel_val
      else
        case when
          country in ('US', 'AU')
        then
          RPAD(SUBSTRING(tel_val, 1, 8), LENGTH(tel_val), '*')
        else
          '********'
        end
  end;

２）マスキングポリシーを対象テーブルに適用 

-- ALTER TABLE ... ALTER COLUMNで適用する
-- ALTER TABLE <table_name>
--  ALTER COLUMN <target_column>
--  SET MASKING POLICY <policy_name>
--  [ USING ( <col1_name> , cond_col_1 , ... ) ]
alter table
  test_user
alter column
  tel_number
set masking policy
  tel_masking
using
  (tel_number, country) -- ★注意
;

３）動作確認 

* 'ACCOUNTADMIN','SYSADMIN'とそれ以外のロールで
　切り替えて動作確認。

テストデータ および SELECT文 

-- SELECT
SELECT * FROM test_user;

【４】関連するSQL文

* 以下の公式サイトに一覧がある

https://docs.snowflake.com/ja/user-guide/security-column-ddm-intro.html#dynamic-data-masking-ddl

１）CREATE MASKING POLICY

* ダイナミックマスキングの作成

https://docs.snowflake.com/ja/sql-reference/sql/create-masking-policy.html

２）ALTER MASKING POLICY

* ダイナミックマスキングの変更

https://docs.snowflake.com/ja/sql-reference/sql/alter-masking-policy.html

３）DROP MASKING POLICY

* ダイナミックマスキングの削除

https://docs.snowflake.com/ja/sql-reference/sql/drop-masking-policy.html

４）SHOW MASKING POLICIES

* マスキングポリシー情報(※)を一覧表示

※ 作成日、データベースとスキーマの名前、所有者、利用可能なコメントなど

https://docs.snowflake.com/ja/sql-reference/sql/show-masking-policies.html

５）DESCRIBE MASKING POLICY

* マスキングポリシーの詳細について説明 (定義内容などが分かる)

https://docs.snowflake.com/ja/sql-reference/sql/desc-masking-policy.html

関連記事

Snowflake ～ 基礎知識編 ～
https://dk521123.hatenablog.com/entry/2021/11/02/130111
Snowflake ～ 入門編 / Hello world ～
https://dk521123.hatenablog.com/entry/2021/11/22/212520
Snowflake ～ 基本編 / CREATE・ALTER TABLE ～
https://dk521123.hatenablog.com/entry/2022/09/03/012113
Snowflake ～ 文字列操作関連 ～
https://dk521123.hatenablog.com/entry/2022/10/01/000000