Snowflake におけるオブジェクト所有者を変更してみた

本記事では、Snowflakeのオブジェクト所有者が持つ OWNERSHIP 権限を別のロールに譲渡する方法について検証してみました。

併せて、オプションとして指定可能な [ REVOKE | COPY ] CURRENT GRANTS の違いについても検証します。

OWNERSHIP 権限とは
テーブルとロールの用意
OWNERSHIP 権限の譲渡
- パターン1：REVOKE
- パターン2：COPY
まとめ

OWNERSHIP 権限とは

公式ドキュメントによると、以下のように記載されてます。

オブジェクトを所有するということは、ロールがオブジェクトに OWNERSHIP 権限を持っていることを意味します。セキュリティ保護可能な各オブジェクトは、単一のロールによって所有されます。通常はデフォルトで、このロールがオブジェクトの作成に使用されます。このロールがユーザーに割り当てられると、ユーザーはオブジェクトに対する制御を実質的に共有します。 GRANT OWNERSHIP コマンドを使用すると、オブジェクトの所有権をあるロールから別のロール（データベースロールを含む）に譲渡できます。このコマンドは、各コンテナ内のセキュリティ保護が可能なオブジェクトも指定します。

通常のスキーマでは、所有者のロールにはデフォルトでオブジェクトに対するすべての権限があります。これには、オブジェクトに対する権限を他のロールに付与する、または取り消す機能も含まれます。さらに、所有権をあるロールから別のロールに移すことができます。しかし、管理アクセススキーマでは、オブジェクトの所有者は付与を決定する能力を失います。スキーマの所有者（つまり、スキーマに対する OWNERSHIP 権限があるロール）または MANAGE GRANTS 権限があるロールのみが、スキーマ内のオブジェクトに対する権限を付与できます。

docs.snowflake.com

まとめると以下のようになります。

各オブジェクトに対する OWNERSHIP 権限は単一のロールが所持し、通常はオブジェクトを作成したロールが所持している
通常のスキーマでは、オブジェクトに対する全ての権限を持っており、他のロールに対して権限の付与や剥奪、OWNERSHIP 権限を譲渡することができる
MANAGED スキーマの場合は、オブジェクトに対する全ての権限のみを所持している

OWNERSHIP 権限を持つロールは、そのオブジェクトに対する個別の権限を持たずとも様々な操作が実行できます。その一方で、誤ったロールが OWNERSHIP 権限を持っていると、想定外の操作が実行されてしまうことも考えられます。

そのような状態となった場合には、OWNERSHIP 権限を他の適切なロールに譲渡する必要があります。

では、実際にテーブルやロールなどを準備し、OWNERSHIP 権限の譲渡を実践してみたいと思います。

テーブルとロールの用意

今回は、以下の画像のような構成でオブジェクトを作成します。

今回は、誤ったロール（FIRST_OWNER）でテーブル（TEST_TABLE）を作成したことで付与されてしまった OWNERSHIP 権限を、適切なロール（LAST_OWNER）に譲渡するというシナリオを想定しています。

OTHER_ROLE は OWNERSHIP 権限の譲渡には直接関係しないロールですが、指定可能なオプションに関連するために作成しています。

各ロールの作成

use role USERADMIN;

create or replace role FIRST_OWNER;
create or replace role LAST_OWNER;
create or replace role OTHER_ROLE;

-- SYSADMINにロールを継承
grant role FIRST_OWNER to role SYSADMIN;
grant role LAST_OWNER to role SYSADMIN;
grant role OTHER_ROLE to role SYSADMIN;

DB、Schema、Warehouse の作成と権限の付与

use role SYSADMIN;

create database TEST_ONWERSHIP_DB;
create schema TEST_ONWERSHIP_DB.TEST_SCHEMA;

-- DB と Schema を利用する権限を各ロールに付与
use role SECURITYADMIN;

grant usage on database TEST_ONWERSHIP_DB to role FIRST_OWNER;
grant usage on database TEST_ONWERSHIP_DB to role LAST_OWNER;
grant usage on database TEST_ONWERSHIP_DB to role OTHER_ROLE;

grant usage on schema TEST_ONWERSHIP_DB.TEST_SCHEMA to role FIRST_OWNER;
grant usage on schema TEST_ONWERSHIP_DB.TEST_SCHEMA to role LAST_OWNER;
grant usage on schema TEST_ONWERSHIP_DB.TEST_SCHEMA to role OTHER_ROLE;

grant create table on schema TEST_ONWERSHIP_DB.TEST_SCHEMA to role FIRST_OWNER;

use role ACCOUNTADMIN;

-- Warehouse はデフォルトで存在するものを各ロールに付与
grant usage on warehouse COMPUTE_WH to role FIRST_OWNER;
grant usage on warehouse COMPUTE_WH to role LAST_OWNER;
grant usage on warehouse COMPUTE_WH to role OTHER_ROLE;

テーブルの作成と権限の付与

use role FIRST_OWNER;

-- テーブルを作成
create table TEST_ONWERSHIP_DB.TEST_SCHEMA.TEST_TABLE(
    ID NUMBER,
    NAME VARCHAR
);

use warehouse COMPUTE_WH;

-- テーブルに仮のデータを格納
insert into TEST_ONWERSHIP_DB.TEST_SCHEMA.TEST_TABLE VALUES
    (1, 'John')
    ,(2, 'Bob')
    ,(3, 'Sara');

-- OTHER_ROLE にテーブル対する権限を付与
grant select on TEST_ONWERSHIP_DB.TEST_SCHEMA.TEST_TABLE to role OTHER_ROLE;

ここまでの作業が完了した段階で、FIRST_OWNER と OTHER_ROLE からは TEST_TABLE にアクセスが可能となっており、LAST_OWNER からはアクセスができない状態になっています。

FIRST_OWNER は OWNERSHIP 権限、TEST_TABLE は USAGE 権限をそれぞれ持っているため TEST_TABLE にアクセスが可能です。逆に LAST_OWNER は権限が不足しており、アクセスができない旨のエラーが表示されます。

テーブルへのアクセスの結果

FIRST_OWNER の場合

use role FIRST_OWNER;
select * from TEST_ONWERSHIP_DB.TEST_SCHEMA.TEST_TABLE;

LAST_OWNER の場合

use role LAST_OWNER;
select * from TEST_ONWERSHIP_DB.TEST_SCHEMA.TEST_TABLE;

OTHER_ROLE の場合

use role OTHER_ROLE;
select * from TEST_ONWERSHIP_DB.TEST_SCHEMA.TEST_TABLE;

各ロールの権限の確認結果

FIRST_ROLE の場合

show grants to role FIRST_OWNER;

LAST_ROLE の場合

show grants to role LAST_OWNER;

OTHER_ROLE の場合

show grants to role OTHER_ROLE;

それでは本題の OWNERSHIP 権限の譲渡を実際に検証します。

OWNERSHIP 権限の譲渡

OWNERSHIP 権限の譲渡については、以下の公式ドキュメントを参考に行います。 docs.snowflake.com

上記のドキュメントにはオプションとして指定可能な REVOKE と COPY について、以下のような記載があります。

所有権が新しいロールに転送されたときに、オブジェクトの既存のアウトバウンド権限をすべて削除するか、転送するかを指定します。

注釈

アウトバウンド権限とは、所有権が変更される個々のオブジェクトに対して付与される権限を指します。

ロールの所有権を譲渡する場合の現在の付与は、（ロール階層を作成するために）現在のロールに付与されたすべてのロールを意味します。現在の権限がコピーされた状態でロールの所有権が譲渡された場合、 SHOW GRANTS コマンドの出力には、現在のロールに子ロールを付与する者として新しい所有者が表示されます。

上記のアウトバウンド権限が何を指しているのかが個人的に分からなかったため、それぞれのパターンでの処理結果を確認していきます。

パターン1：REVOKE

まず、REVOKE を使用する場合のクエリは下記のとおりです。

use role FIRST_OWNER;

grant ownership on TEST_ONWERSHIP_DB.TEST_SCHEMA.TEST_TABLE to role LAST_OWNER REVOKE CURRENT GRANTS;

処理結果として、OWNERSHIP 権限が移ったことにより、FIRST_OWNER からはテーブルにアクセスできず、代わりに LAST_OWNER からはテーブルにアクセスできるようになっています。

また、OWNERSHIP 権限の譲渡と同時に対象のオブジェクトに関連する既存の権限が全て削除されます。実際に OTHER_ROLE に付与されている権限を確認すると、事前に付与していた USAGE 権限が削除されていることが分かります。

show grants to role OTHER_ROLE;

OWNERSHIP 権限の譲渡後の状態

OWNERSHIP 権限の譲渡前の状態

このことから OWNERSHIP 権限の譲渡に合わせて、対象のオブジェクトの権限を1から付与し直したい場合に有効なオプションと考えられます。

パターン2：COPY

次に、COPY を使用する場合は MANAGE GRANT 権限が別途必要となるため、始めに権限の付与が必要となります。権限の付与を含めたクエリは下記の通りです。

use role SECURITYADMIN;

grant MANAGE GRANTS on account to role FIRST_OWNER;

use role FIRST_OWNER;

grant ownership on TEST_ONWERSHIP_DB.TEST_SCHEMA.TEST_TABLE to role LAST_OWNER COPY CURRENT GRANTS;

こちらも REVOKE と同様に、OWNERSHIP 権限が移ったことにより、FIRST_OWNER からはテーブルにアクセスできず、代わりに LAST_OWNER からはテーブルにアクセスできるようになります。

また、REVOKE とは異なり OWNERSHIP 権限を譲渡しても対象のオブジェクトに関連する既存の権限は残っています。しかし、granted_by のカラムが LAST_OWNER に変化していることから、LAST_OWNER が権限を付与した状態に変化していることが分かります。