【Snowflake】オブジェクトタグを活用したマスキングポリシーの管理

Snowflakeのオブジェクトタグを利用してダイナミックデータマスキングのマスキングポリシー管理の効率化するプロシージャを作ってみたので紹介します。

※2022年6月14‐16日に開催されたSnowflake Summit 2022でタグベースのマスキングが新機能として発表されました。この記事は2022年2月に書いたものですが、今後はこのような仕組みを作らなくてもオブジェクトタグを利用してダイナミックマスキングのポリシー管理が出来るようになります。待っていた機能なので便利になりますね！(2022年6月29日追記)

Snowflakeのダイナミックデータマスキング機能とは

Snowflakeのダイナミックデータマスキングは列レベルのセキュリティ機能のひとつです。
ダイナミックデータマスキングについて(Snowflake公式ドキュメント)

ダイナミックデータマスキングはユーザが作成したマスキングポリシーを使用してクエリ実行時にテーブルとビューの列を動的にマスキングをします。
定義したマスキングポリシーに適合する権限(ロール)等が設定されたユーザで検索した場合は正しい値が見え、それ以外のユーザが検索するとマスキングポリシーで指定した値でマスクされたものしか見えません。

マスキングポリシーはテーブルやビューの属性ではなくオブジェクトであり、1つのマスキングポリシーを複数のテーブルやビューへ適用する事が出来る便利な機能になっています。

マスキングポリシーはCREATE MASKING POLICYで作成することができます。
基本的なDDLは以下のとおりです。この例で作成したマスキングポリシー「JP_MASK_POLICY」が適用された列は、ACCOUNTADMINロールとSYSADMINロールが付与されたユーザで検索すると正しい値が戻りますが、それ以外のユーザが検索するとデータは「＊」でマスクされます。

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

作成したマスキングポリシーJP_MASKING_POLICYを既存のtest_location_securityテーブルのfor_jp列に適用してみます。

alter table test_location_security modify column for_jp set masking policy jp_masking_policy;

ACCOUNTADMINロールまたはSYSADMINロールが付与されていないユーザでtest_location_securityテーブルを検索すると以下のようにfor_jp列がマスクされた状態で戻ります。

このようにSnowflakeでは簡単にデータマスキングを実装することができます。また上記のとおり作成したマスキングポリシーは他のテーブルやビューにも適用することができる非常に便利な機能です。
ただ、適用先が数オブジェクトの場合は良いのですが、対象オブジェクト数が増えるとメンテナンス(例えばマスキングポリシーの内容を見直して再適用など)する際に作業が煩雑になったり、新規オブジェクトが作成した際にポリシーの適用が漏れてしまうようなオペミスが発生・・なんてことが無いとは言えません。

オブジェクトタグを使ってみよう

Snowflakeにはオブジェクトにタグ付けをする機能があります。
オブジェクトのタグ付けの仕様についてはSnowflakeの公式ドキュメントをご参照ください。
Snowflakeのオブジェクトタグ

タグはマスキングポリシー同様にテーブルやビューの属性ではなく1つのオブジェクトで、1つのタグを異なるオブジェクト(アカウント、テーブルやテーブルの列)や複数個のテーブルへ付与できます。
このタグの特性を活かすことで異なるオブジェクトに対し同じ意味付けを与えることができ、TAG_REFERENCES ビュー等を利用すれば、タグやタグ値からタグが付与されたオブジェクト情報が引けるので、タグをうまく利用すればオブジェクトをグループ化して管理できるようになります。

例としてmasking_columnタグを作ってみます。

create tag masking_column;

既存のproctest_location_sec_01テーブルのfor_jp列へ作成したmasking_columnタグにタグ値としてjp_maskingを指定して設定してみます。

alter table tag_sectest.hokkaido.proctest_location_sec_01 alter column for_jp set tag  masking_column='jp_masking';

同じタグやタグ値は別のテーブルproctest_location_sec_02にも設定することもできます。

alter table tag_sectest.hokkaido.proctest_location_sec_02 alter column for_jp set tag  masking_column='jp_masking';

設定したタグはaccount_usageスキーマのtag_referencesビューのtag_value列に、上記の例であれば【jp_masking】を条件に指定して検索することで、同じタグ値が設定されたオブジェクトがリストアップされて便利です。

ダイナミックデータマスキングの管理にタグを使ってみる

同じマスキングポリシーを設定したい列が複数のテーブルに存在する場合、上記の例のように設定したい列に同じタグとタグ値を設定しておくとデータベースの管理が非常に楽になります。
ただ、管理上は便利になるのですが、現在のSnowflakeにはタグをキーにしてマスキングポリシーを適用する手段がありません。そこで今回はタグでマスキングポリシーを一括管理できると便利だろうと思い立ち今回は以下のようなストアドプロシージャを作成してみました。

proc_column_tag_policy_ctl(set|unset,オブジェクト型,データベース名,スキーマ名,タグ名,タグ値,適用するポリシー名)

このプロシージャを実行する前に上記の例のようにダイナミックデータマスキングを使いマスクしたい列にタグを設定する必要があります。

ストアドプロシージャは以下のようなSnowflake Scriptingを書いてみました。
(サクッと書いたのでコードが冗長なのはご勘弁を💦)

create or replace procedure proc_column_tag_policy_ctl(ctrl varchar,table_type varchar,db_nm varchar,schema_nm varchar,tag_nm varchar,tag_val varchar,policy_nm varchar)
returns varchar
language sql
as
$$
begin
	declare
		v_db_nm varchar:=upper(db_nm);
		v_schema_nm varchar:=upper(schema_nm);
		v_tag_nm varchar:=upper(tag_nm);
		v_tag_val varchar:=upper(tag_val);
		v_policy_nm varchar:=upper(policy_nm);
		v_ctl varchar:=lower(ctrl);
		v_table_type varchar:=upper(table_type);
		sql_stmt varchar;
		c1 cursor for select g.object_database ,g.object_schema,t.table_name,t.table_type,g.column_name,g.tag_name,g.tag_value 
		from snowflake.account_usage.tag_references g
		inner join snowflake.account_usage.tables t
		on g.object_id = t.table_id;
	begin
		if (v_table_type = 'TABLE') then
			if (v_ctl ='set' or v_ctl ='unset') then
				for records in c1 do
					if (records.object_database = v_db_nm and records.object_schema = v_schema_nm and records.tag_name = v_tag_nm and upper(records.tag_value) = v_tag_val and records.table_type='BASE TABLE' ) then
						if (v_ctl ='set') then
							sql_stmt :='alter table '||records.object_database||'.'||records.object_schema||'.'||records.table_name||' alter column '||records.column_name||' set masking policy '||v_policy_nm;
							execute immediate :sql_stmt;
						elseif (v_ctl ='unset') then
							sql_stmt :='alter table '||records.object_database||'.'||records.object_schema||'.'||records.table_name||' alter column '||records.column_name||' unset masking policy';
							execute immediate :sql_stmt;
						end if;
					end if;
				end for;
			else
				return '[Error] First argument must be set or unset.';
			end if;
		elseif (v_table_type ='VIEW' or v_table_type='MATERIALIZED VIEW') then
			if (v_ctl ='set' or v_ctl ='unset') then
				for records in c1 do
					if (records.object_database = v_db_nm and records.object_schema = v_schema_nm and records.tag_name = v_tag_nm and upper(records.tag_value) = v_tag_val and records.table_type = v_table_type ) then
						if (v_ctl ='set') then
							sql_stmt :='alter view '||records.object_database||'.'||records.object_schema||'.'||records.table_name||' alter column '||records.column_name||' set masking policy '||v_policy_nm;
							execute immediate :sql_stmt;
						elseif (v_ctl ='unset') then
							sql_stmt :='alter view '||records.object_database||'.'||records.object_schema||'.'||records.table_name||' alter column '||records.column_name||' unset masking policy';
							execute immediate :sql_stmt;
						end if;
					end if;
				end for;
			else
				return '[Error] First argument must be set or unset.';
			end if;
		else
			return '[Error] Second argument is an invalid object type.';
		end if;
	 end;
end;
$$
;

以下は使い方の例です。

TAG_SECTESTデータベースのHOKKAIDOスキーマ内に存在するMASKING_COLUMNタグのタグ値にJP_MASKINGが設定された列を持つテーブルにマスキングポリシーJP_MASK_POLICYを一括適用します。

call proc_column_tag_policy_ctl('SET','TABLE','TAG_SECTEST','HOKKAIDO','MASKING_COLUMN','JP_MASKING','JP_MASK_POLICY');

テーブル同様にビューにも実行することができます。

call proc_column_tag_policy_ctl('SET','VIEW','TAG_SECTEST','HOKKAIDO','MASKING_COLUMN','JP_MASKING','JP_MASK_POLICY');

同じくマテリアライズドビューも対応しています。
余談ですがマスキングポリシーが適用されたテーブルからマテリアライズドビューを作成することはできません。マニュアルをご確認ください。

call proc_column_tag_policy_ctl('SET','MATERIALIZED VIEW','TAG_SECTEST','HOKKAIDO','MASKING_COLUMN','JP_MASKING','JP_MASK_POLICY');

マスキングポリシーを一括解除することもできます。
TAG_SECTESTデータベースのHOKKAIDOスキーマ内に存在するテーブルでMASKING_COLUMNタグのタグ値にJP_MASKINGが設定された列のマスキングポリシーを一括解除します。

call proc_column_tag_policy_ctl('UNSET','TABLE','TAG_SECTEST','HOKKAIDO','MASKING_COLUMN','JP_MASKING','JP_MASK_POLICY');

今回はダイナミックデータマスキングのポリシー管理を対象にしてみましたが、管理対象のオブジェクトが多くなるとタグを活用したこのような仕組みがあると便利じゃないかなと思います。

最後に

最後に注意点です。Snowflakeを利用されている方であればご存じかと思いますが、Snowflakeのサービス仕様でACCOUNT_USAGEスキーマのビューには遅延(最大120分)があるため、ユーザが構成変更した直後ではビューに情報が反映されていない場合があるので注意が必要です。
このプロシージャもACCOUNT_USAGEスキーマのビューを使用していますが、今後は即時性の高いinformation_schemaのビューを参照するように改修していく予定です。

タグを活用したデータベース管理の一例としてご参考になれば幸いです。