Snowflakeのロール管理

[Snowflake Advent Calendar 2022](https://qiita.com/advent-calendar/2022/snowflake)  5日目の記事です。

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

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

https://docs.snowflake.com/en/user-guide/security-access-control.html

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

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

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

https://docs.snowflake.com/en/user-guide/security-access-control-considerations.html#aligning-object-access-with-business-functions

アクセスロールとファンクショナルの構成例 (Snowflake公式ドキュメントから引用)
![](https://docs.snowflake.com/en/_images/role-hierarchy-practical.png)

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

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

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

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

<details>
<summary>SQLスクリプト</summary>

```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;
```
</details>

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

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

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

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

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

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

```SQL
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に関する説明はこちら。

https://docs.snowflake.com/en/user-guide/security-access-control-considerations.html#simplifying-grant-management-using-future-grants

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

```SQL
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 で付与されている権限を表示したい場合はこうする。

```SQL
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権限だけ持つことが分かる。

```SQL
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]タブに移動すると、下のようなロールの関係図を見ることができる。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1919551/4f0e87a6-4050-20ee-b7d5-545338b1b57c.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1919551/79de417a-d3e0-c4d3-faa3-6c12a42d1af3.png)