Snowflakeのすべてのロールに付与された権限を確かめたい。

Snowflakeの権限を棚卸するときに、Snowflakeのすべてのロールに付与された権限を確かめたくなるので、やり方を整理した。

データベース内のオブジェクトの権限を確かめたい場合は、 Information Schemaにある OBJECT_PRIVILEGES ビューを確認する。

確認用SQL

Snowflake Scriptingですべてのロールに対し SHOW GRANTS TO ROLE を実行し、結果をテーブルに格納する。

-- 結果格納先のテーブルを作成
create or replace temp TABLE GRANTS (
	"created_on" TIMESTAMP_LTZ(3),
	"privilege" VARCHAR(16777216),
	"granted_on" VARCHAR(16777216),
	"name" VARCHAR(16777216),
	"granted_to" VARCHAR(16777216),
	"grantee_name" VARCHAR(16777216),
	"grant_option" VARCHAR(16777216),
	"granted_by" VARCHAR(16777216)
);

begin
    -- ロールの一覧を取得
    show roles;
    let c1 CURSOR FOR select "name" from table(result_scan(last_query_id()));
    for r in c1 do
        -- show grantsの結果をロールごとに挿入
        let role_name string := r."name";
        show grants to role identifier(:role_name);
        insert into grants select * from table(result_scan(last_query_id()));
    end for;
end;

結果の確認

select * from grants;

補足

IDENTIFIERの引数

文字列をオブジェクト識別子に変換するIDENTIFIERの引数は文字列、セッション変数、バインド変数、Snowflakeスクリプト変数しか受け取らない。
そのため、レコードの変数を直接受け取れず、Snowflakeスクリプト変数を介する必要がある。

-- NGなパターン
-- カーソル内のレコードを参照
for r in c1 do
    show grants to role identifier(r.name);
end for;

-- OKなパターン
for r in c1 do
    let role_name string := r."name";
    show grants to role identifier(:role_name);
end for;