🛡️
Snowflakeで現場担当者レベルにプロシージャを用いてマスキングポリシー適用カラム含むテーブルを更新してもらう方法

2024/12/12に公開
Security
Snowflakeに関する前回の記事はこちら
今回はまぁまぁマニアックな内容です。笑

 シナリオ個人情報保護観点でPII(個人を特定できる識別子)に対してマスキングポリシーがかけられている
現場担当者レベルは権限的にはSecondary RoleをONにすればPIIのマスクが外せる
プロシージャのExecute権限を限定的に持たせている
StageへのUpload権限も限定的に持たせている
現場管理のテーブルがあり、別ソースからダウンロードしているデータをSnowflakeで一括管理するために手作業でやる必要がある(別ソースDBからやむをえずSnowflakeとのパイプラインが引けない)
挿入するデータにマスクはかかっていないがテーブルにはマスクがかけられているので、そのまま挿入するとマスクされたものとそうでないものが混在してしまう
といった場合を想定しています。

結構マニアックですが需要は一定あると思います。レガシーな会社であればあるほどまだまだデータの管理が内製の古いシステムだったり、モダンなDBではないのでパイプラインを引けない・もしくはセキュリティ的に不可能だったりと、このようなDownloadとUploadをしているケースはあります。

またそれを現場担当に任せており、Data Engineerがやってもいいが無駄に労力をかけたくない、みたいな状況も併せ持ってます。
そのような状況で、この記事は大いに役立つことを期待しています。

 マスキングポリシーとはSnowflakeのマスキングポリシーは、カラム単位でデータをマスクする仕組みです。たとえば、顧客IDやメールアドレスなど個人を特定できる情報を、特定ロールを持たないユーザーにはXXXXXXやHash化した形で隠蔽し、適切なロールを有するユーザーには元の値を表示できます。
簡単な例として、EMAIL列にマスキングポリシーを適用するとします。
CREATE MASKING POLICY mask_email AS
(value STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() LIKE '%PII_ACCESS%' THEN value
    ELSE sha2(value,256)
  END;
これによってEMAILはハッシュ化されてテーブル間の突合にも使えて尚且つPIIを守ることができます。

 Secondary Roleでのマスク解除現場担当者はメインロールとしてREADのみのロールを持っているとします。

このロールでは基本的にDBやSchemaに対してUsageを持っておりselectはできるものだとします。このロールでは基本的にPIIは全てマスクされているものとして、ただ運用観点でEMAILをマスクなしで閲覧しないといけない場合があるとする。

その場合は、このユーザーに対してPII_ACCESSロールを渡しておきます。

このロールを選択時はマスキングポリシーに対してマスクを外す役目を果たすので、
USE SECONDARY ROLE PII_ACCESS;
を実行します。そうするとそのworksheetにおけるsessionではメインロールとは別にPII_ACCESSが有効になります。

そうすることでそのworksheetにおけるクエリーの実行ではEMAILはunmaskされた状態になるのです。
!この時にダウンロードしてもマスクはかかっていないので、注意は必要

 プロシージャとはSnowflakeのプロシージャは、データベース内でロジックをカプセル化する仕組みです。（要はこういう流れで処理してください、という一連の流れを順番に実行させることのできる仕組み）

従来のデータベースにおけるストアドプロシージャに似た概念で、SnowflakeではSQLまたはJavaScriptで記述でき、以下のような特徴があります。
サーバーサイドで実行：

プロシージャはSnowflake上で実行されるため、外部からデータを持ち出さずにロジックを完結できる。
SQLまたはJavaScriptで定義可能：

CREATE PROCEDURE文でLANGUAGE SQLまたはLANGUAGE JAVASCRIPTを指定して定義します。

単純なDML操作やDDL発行を行う場合はSQLベース、より柔軟な処理（条件分岐、文字列操作、APIコールなど）が必要な場合はJavaScriptベースが有利です。
権限とコンテキストが重要：

プロシージャ内で実行されるSQLは、呼び出し元や定義者のロール、EXECUTE AS CALLER・EXECUTE AS OWNER設定などによって実行権限が変化します。

これにより、マスキングポリシーやSecondary Roleの有効化状態が実行結果に影響します。
今回はEXECUTE AS CALLERで行います。EXECUTE AS OWNERでは権限が強すぎる場合があり、意図しない処理が行われる可能性があるためです。（定義者の権限で行なってしまうので、場合によってはマスクされるべきものがマスクされていないなどが発生する）

 簡易例：SQLでのプロシージャCREATE OR REPLACE PROCEDURE simple_insert()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
  INSERT INTO my_table VALUES (1, 'test');
  RETURN 'Insert done';
$$;
上記のようなシンプルなプロシージャは、CALL simple_insert();で呼び出し可能です。
特徴でも触れましたが、SQLベースでは単純な処理が得意です。記述も楽なので、基本はSQLで書くのですが、Secondary Roleとの相性は良くありません。
プロシージャ内で単純にUSE SECONDARY ROLE ALL;を実行してもうまく動作しないケースが報告されています。その場合、SnowflakeのプロシージャをJavaScriptで記述し、snowflake.execute()を用いてSQLを実行する手法が有効です。

 Javascriptで実行するときsnowflake.execute({sqlText: "USE SECONDARY ROLE PII_ACCESS"});
これを最初に宣言しておきます。
基本的にJavascriptでのプロシージャの書き方は
snowflake.execute({sqlText: "ここをプロシージャ用に書いたSQLを乗せる"});
これです。

複雑なことをしないのであれば

snowflake.execute({sqlText: ""});

の記述で事足ります。

 実際の直面した課題と解決例ここでは実際に行った例を説明します。

実際のコード例は以下です（もちろんダミーです）

##はコメントアウトなので実際には消してください。
CREATE OR REPLACE PROCEDURE WH.Schema.your_procedure()
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
var result;
try {
    // ロールの切り替え
    snowflake.execute({sqlText: "USE SECONDARY ROLE PII_ACCESS"}); ##これでEMAILのマスクを外します

    // ステージングテーブルを作成 //　該当のファイルをアップロードできるように仮テーブルを念の為作成します
    snowflake.execute({sqlText: `        
        create or replace temporary TABLE WH.Schema.FOR_STAGE (
        	EMAIL VARCHAR(16777216),
        	BUSINESS_DATE DATE,
        	FLAG VARCHAR(16777216)
        )COMMENT='適宜更新します'
        ;
    `});

    // CSVファイルをステージングテーブルにロード
    snowflake.execute({sqlText: `
        COPY INTO Schema.FOR_STAGE
        FROM '@"WH"."Schema"."STAGE_NAME"/'
        PATTERN='.*No\\\\\\\\.1_.*\\\\\\\\.csv'  ## ここではアップロードするときに「No.1」などを確実につけてもらいます。そうすることで意図しないデータを含めないようにしてます。また管理のしやすさもあります
        FILE_FORMAT = (
            TYPE=CSV,
            FIELD_DELIMITER=',',
            DATE_FORMAT = 'YYYY-MM-DD',
            TIME_FORMAT='HH24:MI:SS',
            TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS',
            TRIM_SPACE=FALSE,
            FIELD_OPTIONALLY_ENCLOSED_BY='"',
            REPLACE_INVALID_CHARACTERS=TRUE,
            SKIP_HEADER=1 //ここをERROR_ON_COLUMN_COUNT_MISMATCH=FALSEにしたりも選択肢として
        )
        PURGE=TRUE  ## データの挿入ができたらStageにあるデータは消す
        ON_ERROR=ABORT_STATEMENT　## 念の為ミスったら処理とめる
        ## MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE; を含める場合もあります。状況に応じて
        ;
    `});


    // FOR_STAGEに挿入されたデータの総行数を念の為取得（あと確認のため）
    var totalRowsResult = snowflake.execute({sqlText: `
        SELECT COUNT(*) FROM Schema.FOR_STAGE;
    `});
    var totalRows = totalRowsResult.next() ? totalRowsResult.getColumnValue(1) : 0;


    // ステージングテーブルからデータを挿入
    snowflake.execute({sqlText: `
        INSERT INTO WH.Schema.TARGET_TABLE (
            EMAIL,
            BUSINESS_DATE,
            FLAG
        )
        SELECT
            EMAIL,
            BUSINESS_DATE,
            FLAG
        FROM WH.Schema.FOR_STAGE;
    `});

    // ステージングテーブルの削除
    snowflake.execute({sqlText: "DROP TABLE IF EXISTS Schema.FOR_STAGE;"}); ## 念の為使い終わったSTAGE用のテーブルは消します


    // 念の為重複削除する
    snowflake.execute({sqlText: `
        CREATE OR REPLACE TABLE WH.Schema.TARGET_TABLE AS
        SELECT DISTINCT * FROM WH.Schema.TARGET_TABLE;
        `});　## 今回はInsertのみで対応したので重複がある場合を考慮してこの処理をしてます。しかし本来はMERGE処理で適切に行う方が的確です

    // 結果の返却
    result = "正常に更新が行われました。アップロードされたデータのテーブル総行数は " + totalRows + " です。テーブル総行数が0の時はStageにデータを再度アップロードしてからお試しください。";
} catch (err) {
    result = "エラーが発生しました: " + err.message;
}
return result;
$$
;
これによって達成される結果は
Secondary roleをJavascriptで使うことでその効果を引き継いだ状態で処理ができる
現場担当者が任意のタイミングでマスクされたデータを使って適切にテーブルにデータを挿入できる
挿入したデータのマスクはテーブルの設定に依存するので、仮にSecondary roleを適用していない場合ではちゃんとマスクされた状態になっていること
これをボタン一個で現場の人が実行できることです
テックではない方にとってはボタン一個で作業が終わるのは楽なので、一つの業務効率だと思って実践するといいでしょう。
より複雑な処理はJavascriptで行うために、日々勉強あるのみですね。

以上です。
シナリオ

マスキングポリシーとは

Secondary Roleでのマスク解除

プロシージャとは

簡易例：SQLでのプロシージャ

Javascriptで実行するとき

実際の直面した課題と解決例

Discussion
