Snowflakeでマスキングポリシーを適用したテーブルをCTASで別テーブルにしたらマスクはどうなるのか

はじめに

Snowflakeにはダイナミックデータマスキングという便利機能があります。

ダイナミックデータマスキングは、マスキングポリシーを使用してクエリ時にテーブルとビューの列のプレーンテキストデータを選択的にマスクする、列レベルのセキュリティ機能です。

マスキングポリシーの基本を試す

マスキングポリシーというものを作った上で、対象テーブルの対象列にそのマスキングポリシーを適用するだけです。
以下のコードを流します。

use role sysadmin;
create database test_db;
create schema test_schema;
--対象のテーブル作成
create table masking_test_table
(
    user_name text,
    user_email text
);
--サンプルデータを2レコード
insert into masking_test_table values ('user1','[e-mail]');
insert into masking_test_table values ('user2','[e-mail]');
select * from masking_test_table;
--マスキングポリシーを作成（ACCOUNTADMINロールだけが生データを見れて、それ以外はマスクされた値で見せるという設定
CREATE OR REPLACE MASKING POLICY email_mask AS (val string) returns string ->
  CASE
    WHEN current_role() IN ('ACCOUNTADMIN') THEN VAL
    ELSE '*********'
  END;
--マスキングポリシーをmasking_test_tableのuser_email列に適用する
ALTER TABLE masking_test_table ALTER COLUMN user_email SET MASKING POLICY email_mask;

では動作確認です。

use role sysadmin;
select * from masking_test_table;

まずはマスクされるはずのsysadminでSELECTします。

マスクされた状態で表示されました。

use role accountadmin;
select * from masking_test_table;

マスクされないはずのaccountadminでSELECTします。

マスクされずに実際のメールアドレスデータで表示されました。

はい、便利。

CTASで別テーブルにしたらどうなるの？の検証

ここで疑問が出てきました。
このマスキングポリシーが適用されたテーブルをCREATE TABLE AS SELECT (CTAS)でコピって別テーブルを作ったらどうなるんでしょうか？
見れなかったものが見れるようになっちゃったりしないのでしょうか？

1.SYSADMINがCTASでテーブル作成

use role sysadmin;
create table ctas_by_sysadmin 
(
    user_name text,
    user_email text
)
as select user_name,user_email from masking_test_table;

sysadmin（user_emailを参照できないロール）を使ってCTASでテーブル作成しました。
「ctas_by_sysadmin」というテーブル名にしました。

use role sysadmin;
select * from ctas_by_sysadmin;

sysadminでSELECTします。

マスクされた状態で表示されました。

use role accountadmin;
select * from ctas_by_sysadmin;

accountadminでSELECTします。

あれ？accountadminでもマスクされた状態で表示されました。
なんとなくマスクされずに表示できるイメージでしたが違うんですね。

2.SYSADMINがCTASでテーブル作成（マスキングポリシー付き）

CTASの際に対象のカラムに[WITH MASKING POLICY ポリシー名]を付けることでマスキングポリシーを適用できるので、そのパターンをやってみます。

use role sysadmin;
create table ctas_by_sysadmin_with_policy
(
    user_name text,
    user_email text WITH MASKING POLICY email_mask
)
as select user_name,user_email from masking_test_table;

sysadmin（user_emailを参照できないロール）を使ってCTASでマスキングポリシーを適用しつつテーブル作成しました。
「ctas_by_sysadmin_with_policy」というテーブル名にしました。

use role sysadmin;
select * from ctas_by_sysadmin_with_policy;

sysadminでSELECTします。

マスクされた状態で表示されました。

use role accountadmin;
select * from ctas_by_sysadmin_with_policy;

accountadminでSELECTします。

マスクされた状態で表示されました。

ここまですべてマスクされるという結果になりました。
確証ありませんが私の考察です。
元テーブルのデータがマスク状態でした見れないsysadminがCTASで作ったテーブルなので値がマスクされた値でINSERTされたと推察します。
つまり実データがマスクされた値になってるということなのではと思います。
違ってたらコメントなどで教えてください。

3.ACCOUNTADMINがCTASでテーブル作成

じゃあ元テーブルのデータがマスクされずに見れるaccountadminでやってみましょう。
今回はgrant文が入ります。

use role accountadmin;
create table ctas_by_accountadmin 
(
    user_name text,
    user_email text
)
as select user_name,user_email from masking_test_table;

--accountadminで作成したテーブルに対してsysadminでSELECT可能にする
grant select on table ctas_by_accountadmin to role sysadmin;

accountadmin（user_emailを参照できるロール）を使ってCTASでテーブル作成しました。
「ctas_by_accountadmin」というテーブル名にしました。

use role sysadmin;
select * from ctas_by_accountadmin;

sysadminでSELECTします。

マスクされていない状態で表示されました。
このテーブルにはマスキングポリシーを適用していないからですね。
うっかりこれをやってしまうと見せちゃいけないものが見れる状態になっていたという事態が想像できます。

use role accountadmin;
select * from ctas_by_accountadmin;

accountadminでSELECTします。

こちらも同様にマスクされていない状態で表示されました。

4.ACCOUNTADMINがCTASでテーブル作成（マスキングポリシー付き）

マスキングポリシーを適用版です。

use role accountadmin;
create table ctas_by_accountadmin_with_policy
(
    user_name text,
    user_email text WITH MASKING POLICY email_mask
)
as select user_name,user_email from masking_test_table;

--accountadminで作成したテーブルに対してsysadminでSELECT可能にする
grant select on table ctas_by_accountadmin_with_policy to role sysadmin;

accountadmin（user_emailを参照できるロール）を使ってCTASでマスキングポリシーを適用しつつテーブル作成しました。
「ctas_by_accountadmin_with_policy」というテーブル名にしました。

use role sysadmin;
select * from ctas_by_accountadmin_with_policy;

sysadminでSELECTします。

おおっ、マスクされた状態で表示されました。
ちゃんとマスキングポリシーが適用されているということですね。

use role accountadmin;
select * from ctas_by_accountadmin_with_policy;

accountadminでSELECTします。

こちらは当然マスクされていない状態で表示されました。

まとめ

	SYSADMIN	ACCOUNTADMIN
1.SYSADMINがCTASでテーブル作成	マスク	マスク
2.SYSADMINがCTASでテーブル作成（マスキングポリシー付き）	マスク	マスク
3.ACCOUNTADMINがCTASでテーブル作成	実データ	実データ
4.ACCOUNTADMINがCTASでテーブル作成（マスキングポリシー付き）	マスク	実データ

実データを参照する権限がないロール（マスクされる側のロール）がCTASでデータをSELECTしてINSERTすると、マスクされたデータをINSERTする。
そのため、CTASでできたターブルは誰がSELECTしてもマスクされたデータしか見れない。
＝悪知恵を働かせてマスキングポリシーが適用されていない別テーブルをCTASで作って、なんとか見ようとしてもそれはできない。

実データを参照する権限があるロール（マスクされない側のロール）がCTASでデータをSELECTしてINSERTすると、実データをINSERTする。
CTASで作ったテーブルにマスキングポリシーを適用しないと、SELECT権限があれば誰でも実際の値が見れてしまうので注意が必要。

該当カラムにマスキングポリシーを適用すれば元テーブルと同様の制御が可能になる。

というような検証結果になりました。

追記

CTASとの比較としてcloneでも試しました。