あるロールを削除した時そのロールが所有していたオブジェクトの所有者はどうなるのか確かめてみた #SnowflakeDB

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

さがらです。

Snowflakeで、あるロールを削除した時そのロールが所有していたオブジェクトの所有者はどうなるのか確かめてみたので、その内容をまとめてみます。

結論：DROP ROLEコマンドを実行するロールに譲渡されます

実は、DROP ROLEコマンドの公式Docにすでに結論が書いてあります！

ドロップされたロールが所有するオブジェクトの所有権は、 DROP ROLE コマンドを実行するロールに譲渡されます。これらの各オブジェクトの所有権を別のロールに譲渡するには、 GRANT OWNERSHIP ... COPY CURRENT GRANTS を使用します。

そのため、結論はすでにわかっているのですが、実際に試してみたのでその内容を記事にしてみます。

試してみた

ということで、適当なロールaaa_roleを作ってそのロールでデータベースオブジェクトをいくつか作り、aaa_roleを削除した時に、各データベースオブジェクトの所有権がロールを削除したロールに切り替わるのかを見てみます。

事前準備

まず、適当なロールaaa_roleを作り、データベースを作成できる権限を付与します。

-- 削除するロールの準備
use role securityadmin;
create role aaa_role;

use role accountadmin;
grant create database on account to role aaa_role;

show grants to role aaa_role;

作成したロールをユーザーに付与するのも忘れずに行います。

-- 作成したロールをユーザーに付与
use role securityadmin;
grant role aaa_role to user <ユーザー名>;

続いて、aaa_roleに切り替えて、新しいデータベースaaa_dbを作り、デフォルトのpublicスキーマの中にaaa_tableを作ります。

-- ロールを切り替えてデータベースオブジェクトを作成
use role aaa_role;

create database aaa_db;

use database aaa_db;
use schema public;
create table aaa_table (no int);

show grants to role aaa_role;

すると、aaa_dbデータベース、publicスキーマ、aaa_tableテーブルという3つのオブジェクトに対するOWNERSHIP権限は、全てaaa_roleが持っていることがわかります。これで準備は完了です！

ロールを削除して所有権を確かめてみた

では本題の、対象のaaa_roleロールをドロップすると、aaa_roleがOWNERSHIP権限を持っていたオブジェクトの所有権はどうなるのか、確かめてみます。

以下のクエリを実行して、aaa_roleロールの削除から、各オブジェクトのOWNERSHIP権限を誰が持っているのかも確認してみます。

-- 対象のロールの削除
use role securityadmin;
drop role aaa_role;

-- 各オブジェクトについて権限を確認する
show grants on database aaa_db;
show grants on schema aaa_db.public;
show grants on table aaa_db.public.table;

すると、3つのオブジェクト全てのOWNERSHIP権限は、aaa_roleの削除を行ったsecurityadminに付与されていました！これで公式Docの「ドロップされたロールが所有するオブジェクトの所有権は、 DROP ROLE コマンドを実行するロールに譲渡されます。」という記載について確認が取れました。

おまけ：OWNERSHIP権限を別のロールに付与してみる

DROP ROLEコマンドを行ったロールにOWNERSHIP権限が付与されることはわかったのですが、DROP ROLEコマンドを行うのはSECURITYADMINやACCOUNTADMINなど、管理者レベルのロールで行うことが多いと思いますので、SECURITYADMINやACCOUNTADMInがデータベースオブジェクトの所有者になってしまうと取り回しが不便なことが多いと思います。

そんなときに便利なのが、GRANT OWNERSHIP ... COPY CURRENT GRANTSコマンドです！（DROP ROLEコマンドの公式Docにも記載があるコマンドです。）

今回はアカウント全体のデータベースオブジェクトの管理者となるべきSYSADMINロールに、OWNERSHIP権限を付与してみたいと思います。

-- SECURITYADMINが保持するOWNERSHIP権限について、SYSADMINに権限委譲する
grant ownership on database aaa_db to role sysadmin copy current grants;
grant ownership on schema aaa_db.public to role sysadmin copy current grants;
grant ownership on table aaa_db.public.aaa_table to role sysadmin copy current grants;

1つ例として、このコマンドを実行した後aaa_dbの所有者を確認すると、OWNERSHIP権限はSYSADMINになっていることが確認できました！

最後に

Snowflakeで、あるロールを削除した時そのロールが所有していたオブジェクトの所有者はどうなるのか確かめてみました。

公式Docに「ドロップされたロールが所有するオブジェクトの所有権は、 DROP ROLE コマンドを実行するロールに譲渡されます。」とは書いてありましたが、実際にコマンドを知っていないとすぐに別ロールに権限委譲することが難しいと思いますので、参考になれば幸いです！