【Snowflake】Snowflake ~ Dynamic masking ~

■ はじめに

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

目次

【1】ダイナミックマスキング
【2】適用方法
 1)使用上の注意
【3】サンプル
 例1:Hello world
 例2:条件付きのマスキング
【4】関連するSQL文

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

* 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

【2】適用方法

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

1)使用上の注意

* マスキングポリシー削除や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;」
  で紐づけたマスキングポリシーを外してから再実行

【3】サンプル

例1:Hello world

1)マスキングポリシーを作成

* 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;

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

* 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文実行時に適用することも可能

3)動作確認

* '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;

例2:条件付きのマスキング

1)マスキングポリシーを作成

-- ★注意:ポリシーを一旦外さないとreplace/dropできないので unset
--  => 上記「1)使用上の注意」参照
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;

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

-- 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) -- ★注意
;

3)動作確認

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

テストデータ および SELECT文

-- SELECT
SELECT * FROM test_user;

【4】関連するSQL

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

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

1)CREATE MASKING POLICY

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

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

2)ALTER MASKING POLICY

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

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

3)DROP MASKING POLICY

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

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

4)SHOW MASKING POLICIES

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

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

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

5)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