Zenn
🦔

Snowflakeのカスタムロール〜Procedureでやってみた

2022/05/17に公開
Snowflake
tech

Snowflakeの権限設定で、

  • general
  • ds
  • analyst
  • viewer
    といった役割をもたせる運用をしています。

カスタムロールは、2層構造で設計しました。https://medium.com/snowflake/a-functional-approach-for-snowflakes-role-based-access-controls-5f0e84e80146

Mediumにあったやつを参考に

  • ユーザロール
  • データアクセスロール
    として、ユーザがどこのスキーマへのアクセスができるかを付与

という形をとってます。

dwhの構成としては、下記のようにSchema構成にしています。

  • datalake層
    • datalake_1 （データソースごとに大まかにSchemaを分けてます）
    • datalake_2
    • ・・・・
    • datalake_spot
    • public
  • datawarehouse層
  • datamart層

こんな感じです。

Snowflakeでは、schemaごとにオブジェクト種で設定しないとうまく動かないので、

  • 読み込みのみ（Selectとか）
  • 書き込み（create, select, insert, delete とか）
    をSchemaごとに一括でカスタムロールに設定する procedure を作ってみた

使い方は、

call public.grant_in_schema_rw('datamart', 'data_rw');
call public.grant_in_schema_r('public', 'data_r');

こんな感じ

肝は、identifier(:schema_name) の部分。
オブジェクト名に、そのまま「:id」などは使えないので、identifierをつけることでできます
※BigQueryでのテンプレートは where句の先にしか使えなくて、テーブル名などに使うには、envsubst とかつかってやらねば、うまくできない・・・・

create or replace procedure grant_in_schema_r(schema_name varchar, role_name varchar)
returns varchar not null
language sql
as
$$
begin
    grant select on all tables in schema identifier(:schema_name) to role identifier(:role_name);
    grant select on future tables in schema identifier(:schema_name) to role identifier(:role_name);
    grant select on all views in schema identifier(:schema_name) to role identifier(:role_name);
    grant select on future views in schema identifier(:schema_name) to role identifier(:role_name);
    grant usage on all functions in schema identifier(:schema_name) to role identifier(:role_name);
    grant usage on future functions in schema identifier(:schema_name) to role identifier(:role_name);
    grant usage on all procedures in schema identifier(:schema_name) to role identifier(:role_name);
    grant usage on future procedures in schema identifier(:schema_name) to role identifier(:role_name);
    grant usage on all sequences in schema identifier(:schema_name) to role identifier(:role_name);
    grant usage on future sequences in schema identifier(:schema_name) to role identifier(:role_name);
    grant select on all stages in schema identifier(:schema_name) to role identifier(:role_name);
    grant select on future stages in schema identifier(:schema_name) to role identifier(:role_name);    
    grant select on all streams in schema identifier(:schema_name) to role identifier(:role_name);
    grant select on future streams in schema identifier(:schema_name) to role identifier(:role_name);
    return schema_name;
end;
$$
;

create or replace procedure public.grant_in_schema_rw(schema_name varchar, role_name varchar)
returns varchar not null
language sql
as
begin
    grant all privileges on schema identifier(:schema_name) to role identifier(:role_name);
    grant all privileges on all tables in schema identifier(:schema_name) to role identifier(:role_name);
    grant all privileges on future tables in schema identifier(:schema_name) to role identifier(:role_name);
    grant all privileges on all views in schema identifier(:schema_name) to role identifier(:role_name);
    grant all privileges on future views in schema identifier(:schema_name) to role identifier(:role_name);
    grant all privileges on all sequences in schema identifier(:schema_name) to role identifier(:role_name);
    grant all privileges on future sequences in schema identifier(:schema_name) to role identifier(:role_name);
    grant all privileges on all functions in schema identifier(:schema_name) to role identifier(:role_name);
    grant all privileges on future functions in schema identifier(:schema_name) to role identifier(:role_name);
    grant all privileges on all procedures in schema identifier(:schema_name) to role identifier(:role_name);
    grant all privileges on future procedures in schema identifier(:schema_name) to role identifier(:role_name);
    grant all privileges on all file formats in schema identifier(:schema_name) to role identifier(:role_name);
    grant all privileges on future file formats in schema identifier(:schema_name) to role identifier(:role_name);
    grant all privileges on all stages in schema identifier(:schema_name) to role identifier(:role_name);
    grant all privileges on future stages in schema identifier(:schema_name) to role identifier(:role_name);
    grant all privileges on all streams in schema identifier(:schema_name) to role identifier(:role_name);
    grant all privileges on future streams in schema identifier(:schema_name) to role identifier(:role_name);
    grant all privileges on all tasks in schema identifier(:schema_name) to role identifier(:role_name);
    grant all privileges on future tasks in schema identifier(:schema_name) to role identifier(:role_name);
    return schema_name;
end;

※上記の　procedure を実行するロールについては適当な（＝ACCOUNTADMINとかSECURITYADMIN）ロールを使わないとEXCEPTIONが発生しましたorz

Discussion