データベース、スキーマ、テーブルをクローンする際の権限継承について確認してみた #SnowflakeDB

Snowflake でオブジェクト、特にデータベース、スキーマ、およびテーブルをクローンする際の権限継承について確認してみました。

本記事の内容

Snowflake ではクローンとしてオブジェクト（データベース、スキーマ、テーブルなど）の複製が可能です。
この時の権限に関する主な仕様として、以下があります。

• クローン元のオブジェクト（ソースオブジェクト）がデータベースやスキーマの場合、クローンとして作成されたオブジェクトは、ソースオブジェクト内の子オブジェクトのクローンに対して付与された権限を継承する
• データベースやスキーマのクローン自体は、ソースオブジェクトに対して付与された権限を継承しない
• テーブルについては、クローン時にCOPY GRANTSオプションを使用することで、元のテーブルに付与されたアクセス権限を継承することも可能

また、テーブルクローン時のCOPY GRANTSについては、その有無によって将来の権限が付与されるかどうかも異なり、具体的には以下の通りとなっています。

• COPY GRANTSなし
  • 元のテーブルに付与されたアクセス権限は継承されないが、将来の権限は付与される
• COPY GRANTSあり
  • OWNERSHIP を除く元のテーブルのアクセス権限が継承されるが、将来の権限は付与されない

上記の内容は、実際には以下のドキュメント記載の通りなのですが、どういった挙動かを触ってみて理解しておきたく記事としました。

環境の用意

ここでは、データベース、スキーマ、テーブル単位でオブジェクトのクローンを行うこととし、そのための環境を用意します。
以降のコマンドで検証用の環境を用意します。

ロール階層の定義

以下の通りロールを用意しておきます。

コマンドは以下を使用しました。

use role useradmin;

/*role 定義*/
--access role
create role ts_readonly;
create role ts_crud;

--functional role
create role ts_analyst;
create role ts_dev;

create role ts_admin;

/*role 階層を定義*/
grant role ts_readonly to role ts_crud;
grant role ts_readonly to role ts_analyst;
grant role ts_crud to role ts_dev;

grant role ts_analyst to role ts_admin;
grant role ts_dev to role ts_admin;

grant role ts_admin to role sysadmin;

ロール階層は以下のようになります。 カスタムロールになるので、検証用環境における管理者ロールである ts_admin の子ロールとし、ts_admin 自体は sysadmin に紐づけておきます。

ウェアハウス、データベースの作成

以下のコマンドで、検証用のデータベース「ts_db」を作成し、その中の Public スキーマに user という名称のテーブルを作成しています。

/*warehouseを作成*/
use role sysadmin;

create warehouse ts_wh
    with warehouse_size = 'XSMALL'
    warehouse_type = 'standard'
    auto_suspend = 60
    initially_suspended = TRUE 
    auto_resume = TRUE ;

--warehouseの所有権をPJ管理者ロールに移譲
grant ownership on warehouse ts_wh to role ts_admin;

/*PJ用データベースを作成*/
create database ts_db;

grant ownership on database ts_db to role ts_admin;
grant ownership on schema ts_db.public to role ts_admin;

--PJ管理者にスイッチ
use role ts_admin;
use warehouse ts_wh;
use schema ts_db.public;

/*テーブル作成*/
create table user (id INT);
insert into user (id) values (1);

権限付与

アクセスロールとして用意したロールにそれぞれ以下の権限を付与します。

• ts_readonly
  • 検証用データベース（ts_db）の USAGE
  • ts_db データベースの Public スキーマへの USAGE, SELECT 権限
  • 検証用のウェアハウスの USAGE
• ts_crud
  • Public スキーマ内のテーブルへの INSERT, UPDATE, DELETE 権限

ts_crud には、読み書きアクセスを与えたいので、ts_readonly に付与した SELECT 以外のテーブルへの権限を与えています。ts_crud をはじめとした検証用に作成した各ロールは ts_readonly ロールの親にあたるので、ts_readonly に付与した権限を継承します。

コマンドは以下の通りです。

/*accessroleへの権限付与*/
--子ロールのtest_readに権限を与えたので親ロールも同じ権限を持つ

use role securityadmin;

--ts_readonly
grant usage on warehouse ts_wh to role ts_readonly;
grant usage on database ts_db to role ts_readonly;
grant usage on schema ts_db.public to role ts_readonly;
grant select on all tables in schema ts_db.public to role ts_readonly;

--ts_crud
grant insert,update,delete on all tables in schema ts_db.public to role ts_crud;

権限の確認

show grants onコマンドで、指定のオブジェクトに対する各権限が付与されているロールを見てみます。

• 検証用テーブル

テーブルには、アクセスロールに権限を付与したので下図のようになっています。

show grants on table ts_db.public.user;

• スキーマ

ts_readonly に USAGE 権限を付与し、各ロールに継承させているので、下図のようになります。

show grants on schema ts_db.public;

• データベース

スキーマと同様の形となります。

show grants on database ts_db;

将来の権限を付与

クローン時における将来の付与についても見ておきたいので、ここでは ts_readonly ロールに対して、Public スキーマに将来作成されるテーブルへの SELECT 権限を付与しておきます。

grant select on future tables in schema ts_db.public to role ts_readonly;
show future grants in schema ts_db.public;

オブジェクトをクローンする

データベースのクローン

以下のコマンドでデータベースのクローンを行います。
クローン時のロールには sysadmin を使用します。

use role sysadmin;
create database clone_ts_db clone ts_db;

データベースの権限
クローン先のデータベースの権限を確認します。

所有者はクローンを実行した sysadmin となり、その他の権限は継承されていません。
※ソースオブジェクトにあった ts_readonly ロールの USAGE 権限はありません。

show grants on database clone_ts_db;

スキーマの権限
ソースオブジェクトはデータベースなので、その子オブジェクトであるスキーマに対する権限を確認します。
こちらは、所有権も含めソースオブジェクトの権限が継承されていることが確認できます。

show grants on schema clone_ts_db.public;

テーブルの権限
スキーマと同様にデータベースの子オブジェクトのため、所有権も含めた各種権限は、ソースオブジェクトの内容が継承されます。

show grants on table clone_ts_db.public.user;

将来の権限
ここでは、ソースオブジェクト（データベース）の子オブジェクトであるスキーマに将来の権限を付与したので継承されます。
※データベースに将来の権限を付与していた場合、継承されません。

show future grants in schema clone_ts_db.public;

スキーマのクローン

以下のコマンドでスキーマのクローンを行います。
この際、ロールには sysadmin を使用し、ソースオブジェクトと同じデータベース内に異なるスキーマとして作成します。

use role sysadmin;
create schema ts_db.clone_public clone ts_db.public;

スキーマの権限
スキーマのクローンを行ったので、クローン先のスキーマに関する権限は継承されません。
クローンを実行した sysadmin がクローン先スキーマの所有者となります

show grants on schema ts_db.clone_public;

テーブルの権限
データベースのクローン時と同様に、スキーマの子オブジェクトであるテーブルの各種権限は所有権も含めて継承されます。

show grants on table ts_db.clone_public.user;

将来の権限
ソースオブジェクトであるスキーマに将来の権限を付与しており、クローンとして異なるスキーマを作成したので、この将来の権限は継承されません。
※スキーマをクローンすると、ソーススキーマからの将来の権限はクローンにコピーされません。

show future grants in schema ts_db.clone_public;

テーブルのクローン

クローン先のテーブルは、ソーステーブルと同じスキーマに作成します。
注意点として、クローンを作成するスキーマには ts_readonly ロールに対してスキーマ内に将来作成されるテーブルに対する SELECT 権限を付与します。

テーブルのクローン時は copy grantsオプションを使用できるのでそれぞれ試してみます。

CREATE TABLE | Snowflake DOCUMENTATION

copy grants オプションなし

create table public.clone_user_nocp clone ts_db.public.user;

この場合の権限は以下の通りです。

• これまでのクローン時と同様にソースオブジェクトからの権限は継承されない
• クローン先のスキーマには将来の権限を付与しているので、下図赤枠の権限（テーブルに対する ts_readonly ロールの SELECT 権限）が付与されている

show grants on public.clone_user_nocp;

copy grants オプションあり

次にcopy grantsオプションを使用し、テーブルをクローンします。

--もとのテーブルの権限が複製される。ただし、future grant は適用されない！！
create table public.clone_user_withcp clone ts_db.public.user copy grants;

copy grantsオプションを使用時の権限は以下の通りです。

• copy grants オプションにより、OWNERSHIP を除くソーステーブルの権限が継承されます
• クローン先のスキーマの将来の権限は付与されません

show grants on public.clone_user_withcp;

この例ですと、ソース テーブルの権限として将来の権限と同じもの（ts_readonly ロールに対する SELECT 権限）があるので、もう少しわかりやすく、一度ソース テーブルから、ts_readonly ロールに対する SELECT 権限を取り消します。

use role securityadmin;
revoke select on table ts_db.public.user from role ts_readonly;
show grants on table ts_db.public.user;

この状態で、再度 copy grants オプションを使用し、別名でテーブルのクローンを作成します。

use role sysadmin;
create table public.clone_user_withcp_2 clone ts_db.public.user copy grants;
show grants on table public.clone_user_withcp_2;

権限は以下の通りとなり、元のテーブルに付与された明示的なアクセス権限を継承しつつも、スキーマの将来の権限（readonly ロールに対する SELECT 権限）は付与されていないことがわかります。

さいごに

主要なオブジェクトのクローンとその際の権限の継承について実際に確認してみました。

子オブジェクトの権限が継承される点や、テーブルのクローン時はcopy grantsオプションによってソースオブジェクトの権限も継承できるので非常に便利です。
またテーブルのクローン時にcopy grantsオプションを使用すると、将来の権限は付与されないので、不用意に権限が付与されない形にもなっています。

ただし、クローン先の環境では使用するロール自体をわける場面もあるので、そういった際にどのようにして権限が引き継がれる、または引き継がれないのか、という点は注意しなければと感じます。

こちらの内容が何かの参考になれば幸いです。