Snowflake Advent Calendar 2022 5日目の記事です。

概要

Snowflakeのロール管理方法であるアクセスロール (Access Role) とファンクショナルロール (Functional role) まわりの解説記事

前提条件

公式ドキュメントはざっくりと理解している。

アクセスロールとファンクショナルロール

Snowflakeの権限管理方法として、一定のアクセス権限の塊をアクセスロール、アクセスロールを継承しユーザーにGRANTするロールをファンクショナルロールとして、ロールを2層で管理する方法がある。

目立たないが公式ドキュメントにも載っている。

アクセスロールとファンクショナルの構成例 (Snowflake公式ドキュメントから引用)

構成例でアクセスロールとファンクショナルロールに対応するものは以下のとおり。

アクセスロール
- DB_FIN_RW
- DB_FIN_R
- DB_HR_R
ファンクショナルロール
- ACCOUNTANT
- ANALYST

説明用のオブジェクト作成

説明用にSnowflake公式ドキュメントの構成に沿ったオブジェクトを作成する。

SQLスクリプト

-- DB作成
use role sysadmin;

create database fin;
create table t1 (id int);
create table t2 (id int);

create database hr;
create table t1 (id int);
create table t2 (id int);

-- ロール作成
use role useradmin;
create role analyst;
create role db_hr_r;
create role db_fin_r;
create role db_fin_rw;
create role accountant;

-- ロールの継承
grant role db_fin_rw to role accountant;
grant role db_hr_r to role analyst;
grant role db_fin_r to role analyst;

grant role accountant,analyst to role sysadmin;

-- 各権限の付与
use role securityadmin;
-- Grant read-only permissions on database HR to db_hr_r role.
grant usage on database hr to role db_hr_r;
grant usage on all schemas in database hr to role db_hr_r;
grant select on all tables in database hr to role db_hr_r;

grant usage on future schemas in database hr to role db_hr_r;
grant select on future tables in database hr to role db_hr_r;

-- Grant read-only permissions on database FIN to db_fin_r role.
grant usage on database fin to role db_fin_r;
grant usage on all schemas in database fin to role db_fin_r;
grant select on all tables in database fin to role db_fin_r;

grant usage on future schemas in database fin to role db_fin_r;
grant select on future tables in database fin to role db_fin_r;

-- Grant read-write permissions on database FIN to db_fin_rw role.
grant usage on database fin to role db_fin_rw;
grant usage on all schemas in database fin to role db_fin_rw;
grant select,insert,update,delete on all tables in database fin to role db_fin_rw;

grant usage on future schemas in database fin to role db_fin_rw;
grant select,insert,update,delete on future tables in database fin to role db_fin_rw;

アクセスロールとファンクショナルロールの2層でロールを管理して嬉しいこと

アクセスロールとファンクショナルロールの2層でロールを管理して嬉しいことは以下の3つ

アクセスロールの名称に沿った権限の塊をロールに継承できる。
FUTURE によりアクセスロールの権限範囲を保てる
SHOW GRANTS での一覧性

1. アクセスロールの名称に沿った権限の塊をロールに継承できる。

Snowflakeではロールに対し、オブジェクトそれぞれの権限をGRANTする必要がある。アクセスロールにより、そのオブジェクトそれぞれの権限をロールとしてまとめて管理し、ロールに継承ができる。

例）アクセスロールの db_fin_r はデータベース fin に対して、読み取り権限のみ持っている。

show grants to role db_fin_r;

Result

created_on	privilege	granted_on	name	granted_to	grantee_name	grant_option	granted_by
2022-12-04T05:26:42.576-08:00	USAGE	DATABASE	FIN	ROLE	DB_FIN_R	false	SYSADMIN
2022-12-04T05:26:43.918-08:00	USAGE	SCHEMA	FIN.PUBLIC	ROLE	DB_FIN_R	false	SYSADMIN
2022-12-04T05:14:08.892-08:00	SELECT	TABLE	FIN.PUBLIC.T1	ROLE	DB_FIN_R	false	SYSADMIN
2022-12-04T05:14:08.902-08:00	SELECT	TABLE	FIN.PUBLIC.T2	ROLE	DB_FIN_R	false	SYSADMIN

2. FUTURE によりアクセスロールの権限範囲を保てる

FUTUREによる自動的な権限付与を行うことで、アクセスロールの権限範囲を保つことができる。

FUTUREに関する説明はこちら。

下のコマンドを実行することで、db_hr_r ロールは hr データベース内で新規作成されたスキーマのUSAGE権限とテーブルのSELECT権限を持つことができる。

grant usage on future schemas in database hr to role db_hr_r;
grant select on future tables in database hr to role db_hr_r;

こうすることでデータベース内の構成が変化しても　db_hr に対する r 権限を持つことが保証される。

ちなみに FUTURE で付与されている権限を表示したい場合はこうする。

SHOW FUTURE GRANTS TO ROLE db_fin_r;

Result

created_on	privilege	grant_on	name	grant_to	grantee_name	grant_option
2022-12-04T06:14:15.276-08:00	USAGE	SCHEMA	FIN.<SCHEMA>	ROLE	DB_FIN_R	false
2022-12-04T06:14:16.801-08:00	SELECT	TABLE	FIN.<TABLE>	ROLE	DB_FIN_R	false

3. SHOW GRANTS での一覧性

Snowflakeでは show grants to role でロールが持つ権限の一覧を見られるが、少し分かりづらい部分がある。

継承されたロールの権限を show grants to role で見ることができない。そのため、不用意にロールの継承を行うと、継承されたロールがどのような権限を持つか分かりづらい。
権限が多くなると show grants to role で表示される行数が多くなり、どの権限を持っているか分かりづらい。

ここでアクセスロールとファンクショナルロールを使用すると、アクセスロールの名前に書かれた権限をファンクショナルロールが持つことが分かり、 show grants to role で見る権限が分かりやすくなる。

例）ファンクショナルロールの権限
　analyst ロールは継承された db_fin_r ロールと db_hr_r ロールのUSAGE権限だけ持つことが分かる。

show grants to role analyst;

Result

created_on	privilege	granted_on	name	granted_to	grantee_name	grant_option	granted_by
2022-12-04T05:23:19.336-08:00	USAGE	ROLE	DB_FIN_R	ROLE	ANALYST	false	USERADMIN
2022-12-04T05:23:17.988-08:00	USAGE	ROLE	DB_HR_R	ROLE	ANALYST	false	USERADMIN

不用意な権限追加に注意

SYSADMINロールのデフォルト権限は意外と少なく CREATE DATABASE/WAREHOUSE のみ。

子のファンクショナルロールを経由して、意図しないCREATE/IMPORT SHARE権限などをSYSADMINに渡さないよう注意する。

【参考】Snowsightの機能

Snowsightの左メニューから[Admin] > [User & Roles]、[Roles]タブに移動すると、下のようなロールの関係図を見ることができる。

ロール横のメニューから[Focus on role]をクリックすると、クリックしたロールを中心とした樹形図も見られる。