【Snowflake】データベース内のオブジェクトはDatabase Roleで権限管理しよう!

記事の概要

• Database Roleを利用することで、よりセキュアに権限制御を行おう
• Database Roleを利用することで、Role選択において表示されるRole数を削減しよう.
• [発展]Database Roleを活用することで、Data Sharing時にプロバイダーアカウント指定の権限グループを利用しよう.

Database Roleってなんだっけ？

Database RoleとはDatabase内の限定された範囲の権限を制御するRole

基本的な考え

◽️概要

ユーザに対してTableのアクセス権限を付与する際にはユーザのRoleにTableへの権限を付与するのではなく、Database Roleに付与しそのDatabase RoleをRoleに継承させることができる

◽️Database Roleの基本

Database RoleはDatabase内で作成する
Databse RoleにはDatabase Role内のTABLEなどの権限を付与できる

1. Database Role AはDatabase DB1内で作成する
2. Databse Role AにはDatabase DB1内のTABLE Aなどの権限を付与できる

---

◽️データベースが異なれば権限制御できない

Database Roleは他のDatabase内のTABLEなどの権限を付与できない

3. Database Role Aは他のDatabase DB2内のTABLEなどの権限を付与できない

---

◽️Database Roleは継承が可能

同じデータベース内のDatabase RoleはDatabase Role間でRoleと同様継承可能

4. Database Role AはDatabase Role Cに継承できる

---

◽️データベースが異なればDatabase Roleも異なる

他のDatabase内のDatabase RoleはDatabase Role間で継承できない

5. DB2内のDatabase Role AはBD1内のDatabase Role Dに継承できない

---

◽️Database RoleはあくまでRoleに継承される

RoleはDatabase Roleに継承できない

6. Role ZはDatabase Role Aに継承できない

---

◽️Database RoleはRoleを介さない限りユーザは利用できない

Database RoleはUserに付与できない

7. Database Role AはUSERに継承できない

---

◽️Database Roleはデータベース内の権限制御のみ

Database Roleはあくまでデータベース内の権限制御を行い、データベース外の権限制御はできない

＜例＞
## 可能 🙆
grant create warehouse on account to role ROLE_Z;

## 不可能 🙅‍♀️
grant create warehouse on account to database role DB1.DATABASE_ROLE_A;

8. Database Role AにはWarehouse作成権限を付与できない

---

◽️Database Roleはデータベース毎で固有

同じ名前のDatabase Roleでもデータベースが異なっていれば作成可能

9. Role Z に対してDB1のDatabase Role A と BD2のDatabase Role Aを付与可能

---

権限構成：Roleのみを利用した場合とDatabase Roleも利用した場合

同じ意味(権限)を持つ、Roleを利用した例とDatabase Roleを利用した例

Role Zは、例①、例②どちらであっても同じ権限を持つことになる

例①：Role Ver

例②: Database Role Ver

Role Zにとって例①＝例②

Database Roleを利用すると何が嬉しい？

1. Database Role内のオブジェクトのアクセス制御をDatabase内で完結
2. 地味だけと、地味だけど、Role選択に出てこないんだぁ！！カオスにならない！
3. Data Sharing時にコンシューマアカウントにアクセスグループとしてDatabase Roleを渡せる

▶︎Database Role内のオブジェクトのアクセス制御をDatabase内で完結

show grants が見やすい

##############
## Role Ver ##
##############
show grants to role Role_Z;
> .... USAGE DATABASE DB1 ....
> .... USAGE SCHEMA DB1.SCHEMA_X ....
> .... SELECT TABLE DB1.SCHEMA_X.TABLE_A ....
> .... USAGE SCHEMA DB1.SCHEMA_Y ....
> .... SELECT TABLE DB1.SCHEMA_X.TABLE_1 ....
> .... SELECT TABLE DB1.SCHEMA_X.TABLE_2 ....
> .... SELECT TABLE DB1.SCHEMA_X.TABLE_N ....
> ..

#######################
## Database Role Ver ##
#######################
show grants to role Role_Z;
> .... USAGE DATABASE_ROLE DB1.DATABASE_ROLE_A ....
> .... USAGE DATABASE_ROLE DB1.DATABASE_ROLE_B ....

show grants to database role DB1.DATABASE_ROLE_A;
> .... USAGE DATABASE DB1 ....
> .... USAGE SCHEMA DB1.SCHEMA_X ....
> .... SELECT TABLE DB1.SCHEMA_X.TABLE_A ....

show grants to database role DB1.DATABASE_ROLE_B;
> .... USAGE DATABASE DB1 ....
> .... USAGE SCHEMA DB1.SCHEMA_Y ....
> .... SELECT TABLE DB1.SCHEMA_X.TABLE_1 ....
> .... SELECT TABLE DB1.SCHEMA_X.TABLE_2 ....
> .... SELECT TABLE DB1.SCHEMA_X.TABLE_N ....

▶︎地味だけと、地味だけど、Role選択に出てこないんだぁ！！ カオスにならない！

Database Roleを利用した場合、Role選択には出てこない!

Role Ver

Database Role Ver

▶︎Data Sharing時にコンシューマアカウントにアクセスグループとしてDatabase Roleを渡せる

Database Roleを利用しないSHAREされてきたデータベース内のTable閲覧権限などは細かく制御しにくい

Database Roleの利用がされてない場合

### SHAREされてきたデータベース: SHARE1
grant imported privileges on database SHARE1 to role ROLE_α;

この場合、ROLE_αにはデータベースSHARE1の 全てのオブジェクト（Table等） のアクセス権が付与されてしまう.
ROLE_αにSHARE1内の特定のTableのみアクセスできる権限を付与するには、SHARE1から別のたとえばDBα内にVIEWを貼ることになる

create view DBα.SCHEMA1.SCHEMA_X_TABLE_A as select * from SHARE1.SCHEMA_X.TABLE_A;
grant select on view DBα.SCHEMA1.SCHEMA_X_TABLE_A to role ROLE_α;

Database Roleを利用する場合

### SHAREされてきたデータベース: SHARE1
grant database role SHARE1.DATABASE_ROLE_A to role ROLE_α;

こうすることで
ROLE_αはTABLE_Aのアクセス権を持つことができる

詳しくは下記の通り

https://community.snowflake.com/s/article/How-to-use-Database-Roles-in-a-Data-Share

まとめ

データベース内のオブジェクト権限制御にはDatabase Roleを利用するととても嬉しいことがいっぱいだぞ！
みんなもDatabase Roleを利用しよう！

⭐️Tips

よく使うSQL

### Database Roleの作成
CREATE OR REPLACE DATABASE ROLE database_name.databaserole_name;

### Database Roleの削除
DROP DATABASE ROLE database_name.databaserole_name;

### Database Role一覧
SHOW DATABASE ROLES IN DATABASE database_name;

### Database Roleに付与されている権限一覧
SHOW GRANTS TO DATABASE ROLE database_name.databaserole_name;

### Database Roleに対するDatabase Roleの継承
GRANT DATABASE ROLE database_name.databaserole_name1 TO DATABASE ROLE database_name.databaserole_name2;

### Roleに対するDatabase Roleの継承
GRANT DATABASE ROLE database_name.databaserole_name TO ROLE role_name;

### Database Roleへのデータベースの権限付与＜例＞
GRANT USAGE ON DATABASE database_name TO DATABASE ROLE database_name.databaserole_name;

### Database Roleへのスキーマの権限付与＜例＞
GRANT USAGE ON SCHEMA database_name.schema_name TO DATABASE ROLE database_name.databaserole_name;

### Database Roleへのテーブルの権限付与＜例＞
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO DATABASE ROLE database_name.databaserole_name;
GRANT SELECT ON TABLE schema_name.table_name TO DATABASE ROLE database_name.databaserole_name;

参考（下記図の構成を実際に作成してみよう）

## ロール作成
CREATE OR REPLACE ROLE role_z;

## DB1作成
CREATE OR REPLACE DATABASE db1;
CREATE OR REPLACE SCHEMA db1.schema_x;
CREATE OR REPLACE TABLE db1.schema_x.table_a(id int);
CREATE OR REPLACE TABLE db1.schema_y.table_1(id int);
CREATE OR REPLACE TABLE db1.schema_y.table_2(id int);
CREATE OR REPLACE TABLE db1.schema_y.table_3(id int);

## DB1 データベースロール作成
CREATE OR REPLACE DATABASE ROLE db1.database_role_a;
GRANT USAGE ON DATABASE db1 TO DATABASE ROLE db1.database_role_a;
GRANT USAGE ON SCHEMA db1.schema_x TO DATABASE ROLE db1.database_role_a;
GRANT SELECT ON TABLE db1.schema_x.table_a TO DATABASE ROLE db1.database_role_a;

CREATE OR REPLACE DATABASE ROLE db1.database_role_b;
GRANT USAGE ON DATABASE db1 TO DATABASE ROLE db1.database_role_b;
GRANT USAGE ON SCHEMA db1.schema_y TO DATABASE ROLE db1.database_role_b;
GRANT SELECT ON ALL TABLES IN SCHEMA db1.schema_y TO DATABASE ROLE db1.database_role_b;

## DB2作成
CREATE OR REPLACE DATABASE db2;
CREATE OR REPLACE SCHEMA db2.schema_x;
CREATE OR REPLACE TABLE db2.schema_x.table_a(id int);
CREATE OR REPLACE TABLE db2.schema_y.table_1(id int);
CREATE OR REPLACE TABLE db2.schema_y.table_2(id int);
CREATE OR REPLACE TABLE db2.schema_y.table_3(id int);

## DB2 データベースロール作成
CREATE OR REPLACE DATABASE ROLE db2.database_role_a;
GRANT USAGE ON DATABASE db2 TO DATABASE ROLE db2.database_role_a;
GRANT USAGE ON SCHEMA db2.schema_x TO DATABASE ROLE db2.database_role_a;
GRANT SELECT ON TABLE db2.schema_x.table_a TO DATABASE ROLE db2.database_role_a;

CREATE OR REPLACE DATABASE ROLE db2.database_role_b;
GRANT USAGE ON DATABASE db2 TO DATABASE ROLE db2.database_role_b;
GRANT USAGE ON SCHEMA db2.schema_y TO DATABASE ROLE db2.database_role_b;
GRANT SELECT ON ALL TABLES IN SCHEMA db2.schema_y TO DATABASE ROLE db2.database_role_b;

## データベースロールをロールに継承
GRANT DATABASE ROLE db1.database_role_a TO ROLE role_z;
GRANT DATABASE ROLE db1.database_role_b TO ROLE role_z;

GRANT DATABASE ROLE db2.database_role_a TO ROLE role_z;
GRANT DATABASE ROLE db2.database_role_b TO ROLE role_z;