Snowflakeで付与された権限を他のロールにも付与できる「WITH GRANT OPTION」を試してみた #SnowflakeDB

※本エントリは、Snowflakeをもっと使いこなそう！ Advent Calendar 2022の１１日目の記事となります。

さがらです。

Snowflakeで、付与された権限を他のロールにも付与できる「WITH GRANT OPTION」を試してみたので、その内容をまとめてみます。

WITH GRANT OPTIONとは

Snowflakeではロールという概念を各ユーザーに付与して、そのロールが持つ権限によって操作することが出来る内容が決まっています。

ロールに対して各種操作権限を付与する際はGRANT ... TO ROLEという構文を使用します。

基本的に、付与された権限はそのロールだけが使用できる仕様で、他のロールに対して付与は出来ないようになっています。（所有者権限など、一部の権限は除く）

しかし、GRANT ... TO ROLEには「WITH GRANT OPTION」というオプションがあり、このオプションを付与してGRANT ... TO ROLEを実行すると、付与された権限を使用できるだではなく、付与された権限を別のロールに付与することが可能となります。

これが、「WITH GRANT OPTION」です。

試してみた

では、WITH GRANT OPTIONがどのように作用するのか、試してみます！

今回は、sagara_admin_roleというロールが所有者権限を保持しているテーブルに対する権限をsagara_dev_roleに付与することを考え、「WITH GRANT OPTION」の有無でどの様に挙動が変わるのかを確かめてみます。

事前準備

下記のコマンドを実行して、検証用のテーブルを作成します。

-- 検証用のテーブルを作る
use role sagara_admin_role;
use schema public;
create table table_aaa (aaa string);

WITH GRANT OPTIONがない場合

まず、通常のGRANT文を実行して、付与された権限を別のロールsagara_unknown_roleに付与できるかどうかを確かめてみます。

-- WITH GRANT OPRIONなしver
use role sagara_admin_role;
grant select on table_aaa to role sagara_dev_role;

-- 権限確認用
show grants to role sagara_dev_role;

上記のコマンドを実行したあと、対象の権限についてgrant_option列はfalseになっていますね。

続いて、実際に権限が付与できるかを確かめるため、以下のコマンドを実行します。

-- ロールを変更し、権限が付与できるか確かめる
use role sagara_dev_role;
grant select on table_aaa to role sagara_unknown_role;

すると、「実行されない付与：権限が不十分です。」と返ってきてしまいました。ただ、デフォルトの設定で他のロールに付与できたら色々と問題がありますからね、これは正しい挙動と言えます！

WITH GRANT OPTIONがある場合

続いて、「WITH GRANT OPTION」をオプションとして設定した場合に、付与された権限を別のロールsagara_unknown_roleに付与できるかどうかを確かめてみます。

-- 一度、先程付与した権限をrevokeする
revoke select on table_aaa from role sagara_dev_role;

-- WITH GRANT OPRIONありver
use role sagara_admin_role;
grant select on table_aaa to role sagara_dev_role with grant option;

-- 権限確認用
show grants to role sagara_dev_role;

上記のコマンドを実行したあと、対象の権限についてgrant_option列はtrueになっていました！

続いて、実際に権限が付与できるかを確かめるため、以下のコマンドを実行します。

-- ロールを変更し、権限が付与できるか確かめる
use role sagara_dev_role;
grant select on table_aaa to role sagara_unknown_role;

すると、今度は「ステートメントは正常に実行されました。」と出てきました。これは権限付与のコマンドが正常終了した場合に返ってくるメッセージです。

実際に、権限も正しく付与されていました。

WITH GRANT OPTIONの使いどころ

では、「WITH GRANT OPTION」の使いどころについて考えてみます。

Snowflakeでは各オブジェクトに関する権限管理は、「対象のオブジェクトの所有者（Managed Access Schemaを有効化している場合はそのスキーマの所有者）」、「所有者権限を持つロールの親に位置するロール」しか出来ません。

そのため、所有者権限を持つロールの子供に位置する開発者ロールや分析者用ロールといったロールに対して、操作権限を自由に付与させるようにしたい場合に役立つのではないのでしょうか。

ただ、下手に「WITH GRANT OPTION」を付与してしまうと不用意にいろんな方に権限が行き渡ってしまうリスクがあるため、なかなか使い所が難しい機能だとは思いますね…

また、この「WITH GRANT OPTION」を使う場合には、usageやselectなどデータを見るために必要な権限だけに留めておき、insertやtruncateなどデータ更新・削除に関わる権限の付与には極力付けないほうが良いと思います。不用意な方にデータ更新・削除をされてしまうリスクがありますので…

最後に

Snowflakeで、付与された権限を他のロールにも付与できる「WITH GRANT OPTION」を試してみました。

使いどころは難しい機能かもしれませんが、所有者権限を持つロールの配下のロールに、少しだけ付与権限の委譲をさせたい場合には検討してみてください。