💡

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

ログインするとコメントできます