💡

Snowflakeでリーダーアカウントに閲覧許可を出すProcedureを作ってみた

2022/06/01に公開

Snowflakeにおいてデータシェアリングを行う機能があります。
今回はお客様に、ユーザは発行できないが、リーダーアカウントでDWHのなかを全部見れる（＝SELECTできる）状態にしたい！
ってことで、Snowflake Scriptingを使って、実現してみました。

使い方

call public.grant_share_reader_account('[SCHEMA_NAME]');
指定スキーマにUSAGEを付与
指定スキーマのTABLE全部にSELECTを付与
指定のスキーマ以下のVIEWについて
- SCURE VIEWにする
- [READER_ACCOUNT_NAME]へSHARE設定する
上位ROLE( ACCOUNTADMIN　やカスタムロールの最上位ロール)で実施する

READER_ACCOUNTに閲覧許可を出す際に、TABLEについては、「ALL　TABLE」はできるのですが、「FUTRURE」がないため、新しいVIEW　（やTABLE？）が生成されると、追加でSHARE設定をしなければならないのです。

本来ならば、一つ一つのVIEWやTABLEについて、精査してGRANT TO SHARE を発行すべきなのですが、今回は常に全部見れる状態に！

というオーダなので、こちらをTASKに設定して、一日一回発行する形にしよかなっておもってます。

ハマった点

カーソルの設定で変数を叩き込む際に　？　演算子を使う
- view_names cursor for select table_name from information_schema.views where TABLE_SCHEMA = ?;
？演算子に埋め込むのの open [CURSOL_NAME] using ([変数名]) を使う

ってしないと怒られました。

-- use account_admin;

create or replace procedure public.grant_share_reader_account(schema_name string)
returns varchar not null
language sql
as
$$
declare
    role_name := '[READER_ACCOUNT_NAME]';
    view_names cursor for select table_name from information_schema.views where TABLE_SCHEMA = ?;
begin
    let all_command string := '';
    
    -- schema
    let schema_command string := 'grant usage on schema ' || :schema_name || ' to share ' || :role_name;
    execute immediate schema_command;

    -- tables
    let tables_command string := 'grant select on all tables in schema ' || :schema_name || ' to share ' || :role_name;
    execute immediate tables_command;

    -- views
    open view_names using (schema_name);
    for view_name in view_names do
        let alter_secure_command string := 'alter view ' || :schema_name || '.' || view_name.table_name || ' set secure';
        execute immediate alter_secure_command;
        let command string := 'grant select on view ' || :schema_name || '.' || view_name.table_name || ' to share ' || :role_name;
        all_command := all_command || ', ' || command;
        execute immediate command;
    end for;

    return :schema_name || '-' || :role_name || ':' || all_command;
end
$$;

call public.grant_share_reader_account('SCHEMA_1');

使い方

ハマった点

Discussion