Snowflakeのタグベースのマスキングポリシーをタグの値でマスク可否を判断するようにして試してみた

use role sysadmin;
create or replace database security;
create or replace schema security.masking;
create or replace schema security.tags;

use role sysadmin;
create or replace database app_data;
create or replace schema app_data.public;

use role securityadmin;         
create or replace role analysis_role;     
create or replace role dev_role;

-- sysadminの子ロールに
grant role dev_role to role sysadmin;
grant role analysis_role to role sysadmin;

-- securityデータベースの権限付与
grant usage on database security to role sysadmin;
grant usage on database security to role dev_role;

grant usage on all schemas in database security to role sysadmin;
grant usage on all schemas in database security to role dev_role;

-- app_dataデータベースの権限付与
grant usage on database app_data to role sysadmin;
grant usage on database app_data to role dev_role;
grant usage on database app_data to role analysis_role;

grant usage on all schemas in database app_data to role sysadmin;
grant usage on all schemas in database app_data to role dev_role;
grant usage on all schemas in database app_data to role analysis_role;

-- app_data.publicスキーマのselect権限をfuture grantで付与
grant select on future tables in schema app_data.public to role analysis_role;

-- securityデータベースの各スキーマの権限を付与
grant all on schema security.masking to role sysadmin;
grant all on schema security.tags    to role sysadmin;

-- app_data.publicスキーマでテーブルの作成権限をdev_roleに付与
grant create table on schema app_data.public to role dev_role;

-- 既に作成済のウェアハウスcompute_whの権限を付与
grant usage on warehouse compute_wh to role dev_role;
grant usage on warehouse compute_wh to role analysis_role;

-- タグに対するマスキングポリシー付与権限をsysadminに付与
use role accountadmin;
grant apply masking policy on account to role sysadmin;

use role sysadmin;
use schema security.tags;

-- タグの作成
create or replace tag pii_info comment = 'pii flag: "pii" or "-"';

use schema security.masking;

-- string用のマスキングポリシーの作成
create or replace masking policy pii_mask_string
  as (val string)
  returns string ->
    case
      when system$get_tag_on_current_column('security.tags.pii_info') = 'pii'
           and current_role() = 'ANALYSIS_ROLE'
      then '***masked***'
      else val
    end;

-- number用のマスキングポリシーの作成
create or replace masking policy pii_mask_number
  as (val number)
  returns number ->
    case
      when system$get_tag_on_current_column('security.tags.pii_info') = 'pii'
           and current_role() = 'ANALYSIS_ROLE'
      then -9999
      else val
    end;

-- 作成したタグに、作成したマスキングポリシーを付与
alter tag security.tags.pii_info
  set masking policy security.masking.pii_mask_string;

alter tag security.tags.pii_info
  set masking policy security.masking.pii_mask_number;

-- dev_roleでも作成したタグを付与できるように、権限を付与
grant apply on tag security.tags.pii_info to role dev_role;

use role sysadmin;
use schema app_data.public;

create or replace table customer_sys (
  id     number  with tag (security.tags.pii_info = '-'),
  email  string  with tag (security.tags.pii_info = 'pii'),
  phone  string  with tag (security.tags.pii_info = 'pii'),
  amount number  with tag (security.tags.pii_info = 'pii'),
  dob    date    with tag (security.tags.pii_info = '-')
);

insert into customer_sys values
  (1,'john.doe@example.com','123-456-7890',1000,'1980-01-01'),
  (2,'jane@example.com'    ,'098-765-4321',2000,'1990-12-12');

-- sysadminから確認　※マスキングされない
use role sysadmin;
select * from app_data.public.customer_sys;

use role analysis_role;
select * from app_data.public.customer_sys;

use role dev_role;
use schema app_data.public;

create or replace table customer_dev (
  id        number  with tag (security.tags.pii_info = '-'),
  name      string  with tag (security.tags.pii_info = 'pii'),
  ssn       string  with tag (security.tags.pii_info = 'pii'),
  salary    number  with tag (security.tags.pii_info = 'pii')
);

insert into customer_dev values
  (1,'alice','999-99-9999',75000),
  (2,'bob'  ,'888-88-8888',63000);

-- dev_roleから確認　※マスキングされない
use role dev_role;
select * from app_data.public.customer_dev;

-- analysis_roleから確認 ※マスキングされる
use role analysis_role;
select * from app_data.public.customer_dev;