はじめに
前回書いた下記の投稿でダイナミックデータマスキングのテーブルをCTASで作り直したらどうなるかを検証しました。
CTASはわかったのでcloneも試してみようと思います。
下準備
これは前回と同様です。
コードだけ書いておきます。
すでに前回実行済みなのでここに掲載するだけで流しません。
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','user1@examle.com');
insert into masking_test_table values ('user2','user2@examle.com');
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;
use role accountadmin;
select * from masking_test_table;
cloneで別テーブルにしたらどうなるの？の検証
1.SYSADMINでcloneでテーブル作成
use role sysadmin;
create table clone_by_sysadmin clone masking_test_table;
sysadmin（user_emailを参照できないロール）を使ってcloneでテーブル作成しました。
「clone_by_sysadmin」というテーブル名にしました。
use role sysadmin;
select * from clone_by_sysadmin;
sysadminでSELECTします。
マスクされた状態で表示されました。
use role accountadmin;
select * from clone_by_sysadmin;
accountadminでSELECTします。
マスクされずに実際の値で見れました。
クローン元のmasking_test_tableと同じです。
desc table clone_by_sysadmin;
クローンしたclone_by_sysadminテーブルの詳細を見てみます。
横スクロールが入っちゃったので左側が見えなくなりましたが、2行目がuser_emailです。
polocy_nameにEMAIL_MASKのポリシーが適用されていますね。
GUIでも見れます。
前回、CTASでテーブルを作った時にこのdescコマンドは確認していなかったのでちょっとCTASでやってみます。
use role sysadmin;
create table ctas2_by_sysadmin
(
user_name text,
user_email text
)
as select user_name,user_email from masking_test_table;
desc table ctas2_by_sysadmin;
「ctas2_by_sysadmin」という名前で2を付けて作成しました。
そしてdescで確認。
policy_nameは入っていませんね。
GUIでも見てみます。
やはりマスキングポリシーは付いていません。
ということで
- CTASだとポリシーは明示的に付けなければ付かない。
- cloneだとポリシーは付いている。（ポリシーも含めてクローンされる。）
という違いがあることがわかりました。
2.ACCOUNTADMINでcloneでテーブル作成
use role accountadmin;
create table clone_by_accountadmin clone masking_test_table;
--accountadminで作成したテーブルに対してsysadminでSELECT可能にする
grant select on table clone_by_accountadmin to role sysadmin;
accountadmin（user_emailを参照できるロール）を使ってcloneでテーブル作成しました。
「clone_by_accountadmin」というテーブル名にしました。
SYSADMINでも参照できるようにselect権限を付与します。
use role sysadmin;
select * from clone_by_accountadmin;
sysadminでSELECTします。
マスクされた状態で表示されました。
use role accountadmin;
select * from clone_by_accountadmin;
accountadminでSELECTします。
マスクされずに実際の値で見れました。
先ほどと同様、クローン元のmasking_test_tableと同じです。
desc table clone_by_accountadmin;
クローンしたclone_by_accountadminテーブルの詳細を見てみます。
これも先ほどと同様、ポリシーが付いていますね。
GUIの方は省略します。
まとめ
- cloneの場合は元テーブルと同じようにマスキングポリシーも含めてクローンするので、元テーブルと全く同じになる。
- テーブルをクローンしたロールがマスキングポリシーの適用ロールかそうでないかは影響しない。
ということです。
わかりやすいですね。
結論
CTASよりclone。
cloneなら別データができるわけではないのでストレージ容量もかからないし。