スキーマの所有者だけが権限管理可能となる「Managed Access Schema」を試してみた #SnowflakeDB

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

さがらです。

Snowflakeで、スキーマの所有者だけが権限管理可能となる「Managed Access Schema」を試してみたので、その内容をまとめてみます。

Managed Access Schemaとは

改めて、Managed Access Schemaですが、このオプションを有効にすることで、スキーマの所有者(OWNERSHIP)権限を持つロールだけが、スキーマ内のテーブルやビューなど各オブジェクトの権限操作が可能となります。

以下は、公式DocのWITH MANAGED ACCESSの説明文の引用です

管理スキーマを指定します。管理アクセススキーマは、スキーマの所有者とともに権限管理を集中化します。
通常のスキーマでは、オブジェクトの所有者（つまり、オブジェクトに対する OWNERSHIP 権限を持つロール）は、オブジェクトに対する追加の権限を他のロールに付与できます。管理スキーマでは、スキーマの所有者が、スキーマ内のオブジェクトに対する 将来の付与 を含むすべての権限付与を管理します。オブジェクト所有者は、オブジェクトに対する OWNERSHIP 権限を保持します。ただし、オブジェクトの権限付与を管理できるのはスキーマ所有者のみです。

どういった時に役立つのか？

例えば、dbt×Snowflakeで下図のようなロール構成を取ったときに、各ロールの役割と、各オブジェクトの所有関係は下記のようになります。

• dbt_admin_role：各データベースとスキーマの管理者であり、各データベースとスキーマに対する所有者(OWNERSHIP)権限を持つ
• dbt_xxx_dev_role：各スキーマ内のテーブルやビューを作成するロールとなるため、テーブルやビューに対する所有者(OWNERSHIP)権限を持つ

このとき、デフォルトの設定だと、オブジェクトの所有者はそのオブジェクトに対する権限を自由に別のロールに付与できてしまいます。本来スコープ外のロールに各テーブルの閲覧権限が渡ってしまうリスクがある、ということです。

ここで、Managed Access Schemaの出番です！

前述しました通り、Managed Access Schemaが有効化されたスキーマにおいては、スキーマの所有者(OWNERSHIP)権限を持つロールだけが、スキーマ内のテーブルやビューなど各オブジェクトの権限操作が可能となります。このため、各テーブルを作成してテーブルの所有者(OWNERSHIP)権限を持つロールは権限操作ができなくなります。

試してみた

ということで、実際にManaged Access Schemaを試してみます！

前提条件

まず、以下の3つのロールが登場します。

• sagara_admin_role：対象のスキーマへの所有者(OWNERSHIP)権限を持つ
• sagara_dev_role：対象のスキーマ内のテーブルの所有者(OWNERSHIP)権限を持つ
• sagara_unknown_role：正体不明のロール、今回対象のテーブルへのアクセス権は渡したくない

この上で、以下のコマンドを実行し、各ロールでスキーマとテーブルを作成します。

-- スキーマの作成
use role sagara_admin_role;
create schema managed_access_test;

-- sagara_dev_roleに対して、スキーマ内でテーブル作成ができるように権限を付与
grant usage,create table on schema managed_access_test to role sagara_dev_role;

-- テーブルの作成
use role sagara_dev_role;
use schema managed_access_test;
create table table_aaa (aaa string);

このコマンドを実行することで、スキーマの所有者はsagara_admin_role、テーブルの所有者はsagara_dev_roleという状況にすることが出来ました

Before：Managed Access Schema有効化前の挙動

では、Managed Access Schemaを有効化しないと、どのような挙動となるのかを確かめてみます。

以下のコマンドを実行し、テーブルの所有者であるsagara_dev_roleから正体不明のロールsagara_unknown_roleに権限を付与してみます。

-- sagara_dev_roleから、sagara_unknown_roleへのテーブル権限付与
use role sagara_dev_role;
grant select on table table_aaa to role sagara_unknown_role;

すると、何事もなく権限を付与できてしまいました。

show grantsコマンドを実行して付与されているのかを確かめてみると、やはり付与されていました。

After：Managed Access Schema有効化後の挙動

では、ここでManaged Access Schemaを有効化するとどうなるのか見てみます！

既存のスキーマに対してManaged Access Schemaを有効化するには、ALTER SCHEMAコマンドで可能です。具体的には以下のコマンドを実行すればOKですが、対象のスキーマのOWNERSHIP権限と、グローバルのMANAGE GRANTS権限を持つロールで実行する必要があるため注意です！

以下のコマンドでは、sagara_admin_roleへのMANAGE GRANTS権限の付与から、Managed Access Schemaの有効化までを行っています。

-- sagara_admin_roleへのMANAGE GRANTS権限の付与
use role securityadmin;
grant manage grants on account to role sagara_admin_role;

-- Managed Access Schemaの有効化
use role sagara_admin_role;
alter schema managed_access_test enable managed access;

この状態で、以下のコマンドを実行し、テーブルの所有者であるsagara_dev_roleから正体不明のロールsagara_unknown_roleに権限を付与してみます。

-- sagara_dev_roleから、sagara_unknown_roleへのテーブル権限付与
use role sagara_dev_role;
grant select on table table_aaa to role sagara_unknown_role;

すると、下図のようにエラーとなりました！スキーマ内の各オブジェクトの権限管理はスキーマ所有者のみ行えるようになったことがわかりますね。

おまけ：新しくスキーマを作る際にManaged Access Schemaの有効化してみる

先程は既存のスキーマに対してManaged Access Schemaの有効化をしてみましたが、MANAGE GRANTS権限が必要なのが少しネックに感じました。

そのため、MANAGE GRANTS権限を一度取り消して、新しくスキーマを作成する際にManaged Access Schemaの有効化してみることを試してみます。

-- MANAGE GRANTS権限を取り消す
use role securityadmin;
revoke manage grants on account from role sagara_admin_role;

-- with managed accessつきでスキーマ作成してみる
use role sagara_admin_role;
create schema managed_access_test_2 with managed access;

-- スキーマの情報を見る
show schemas;

上記のコマンドを実行すると、スキーマmanaged_access_test_2はManaged Access Schemaが有効化されていました！新しくスキーマを作成する際にManaged Access Schemaを有効化するならば、MANAGE GRANTS権限は不要のようです。

最後に

Snowflakeでスキーマの所有者だけが権限管理可能となる「Managed Access Schema」を試してみました。

本番運用では非常に役立つ機能だと思いますので、ぜひ活用してみてください！