RECRUIT

風音屋では、データエンジニア、データアナリスト、データコンサルタントを募集しています。

書籍執筆者やOSSコントリビューターなど、業界を代表する20名以上のアドバイザーと一緒にベストプラクティスを追求できる環境です。

ご興味のある方は、ぜひご応募ください。

8 minute read

風音屋データエンジニアの濱田（@hrkhjp）です。

Snowflakeで特定のロールに付与した権限をテストする際、「権限を付与していないはずなのにアクセスできてしまう」といった、意図通りに動作せず検証がうまく進まない経験はありませんか？

その原因の一つが、今回解説する セカンダリロール の存在です。

この記事では、セカンダリロールの仕組みと、それが権限検証に与える影響、そして セカンダリロールを適切に制御して正しく権限を検証する方法について、具体的な SQL 例を交えながら解説します。

セカンダリロールとは何か？

まず、Snowflake におけるロールの基本的な概念をおさらいしましょう。

Snowflake では、ユーザーに複数のロールを付与することができます。 そのため、クエリ実行時には「どのロールを利用するのか」を 1 つだけ指定する必要があります。このとき、指定されたアクティブなロールを プライマリロール と呼びます。クエリを実行する際、基本的にはこのプライマリロールの権限が使われます。

一方で、ユーザに複数のロールが付与されている場合、「やりたいことに合わせていちいちロールを選択するのは不便だ」という考え方があります。これを解消してくれるのが セカンダリロール です。

プライマリロール以外に、自身に付与されているロールをセカンダリロールとして指定することで、セカンダリロールのいずれかが権限を持っているだけでクエリを実行することができます。これにより、「どのロールに権限が付与されているのか」を意識せずにクエリを実行することができます。

セカンダリロールが権限検証に与える影響

ここで重要なのは、

  • Snowflakeのクエリ実行時には、プライマリロールとすべてのセカンダリロールが持つ権限が合算されて評価される
  • デフォルトでは、ユーザに付与されているすべてのロールがセカンダリロールとして指定されている

という点です。

つまり、プライマリロールに必要な権限がなくても、自身に付与されたロールのいずれかが権限を持っていればクエリは成功してしまいます。これが、特定のロールの権限をテストしたい時の混乱の原因となります。

セカンダリロールの確認方法

現在有効になっているセカンダリロールは、以下の SQL で確認できます。

SELECT CURRENT_SECONDARY_ROLES();

このクエリを実行して得られるのは、以下のような結果です。

{"roles":"ROLE1,ROLE2,ROLE3","value":"ALL"}

この例では「セカンダリロールとして ALL が指定されていて、ROLE1、ROLE2、ROLE3 が有効になっている」ことがわかります。

セカンダリロールによって正しく権限を検証できない例

それでは、実際にセカンダリロールによって権限の検証がどのように妨げられるかを見ていきましょう。

ここでは、TEST_ROLE というロールの権限を検証しようとしても、ACCOUNTADMIN のような強力なロールがセカンダリロールとして有効になっているために、検証が失敗するケースを再現します。

セットアップ

まず、テスト用のデータベース、テーブル、そして TEST_ROLE ロールを作成します。

-- 検証用のオブジェクトを作成
USE ROLE SYSADMIN;
CREATE OR REPLACE DATABASE DEMO_DB;
CREATE OR REPLACE SCHEMA DEMO_DB.DEMO_SCHEMA;
CREATE OR REPLACE TABLE DEMO_DB.DEMO_SCHEMA.SALES (
  item_id INT,
  saled_at TIMESTAMP_NTZ
);

-- 検証用のロールを作成
USE ROLE SECURITYADMIN;
CREATE OR REPLACE ROLE TEST_ROLE; 
GRANT USAGE ON DATABASE DEMO_DB TO ROLE TEST_ROLE;
GRANT USAGE ON SCHEMA DEMO_DB.DEMO_SCHEMA TO ROLE TEST_ROLE;
GRANT SELECT ON TABLE DEMO_DB.DEMO_SCHEMA.SALES TO ROLE TEST_ROLE;

-- 自分のユーザーにロールを付与
-- <your_user_name>はご自身のユーザー名に置き換えてください
GRANT ROLE TEST_ROLE TO USER <your_user_name>;

検証

1. TEST_ROLE に切り替えて、テーブルにアクセスできることを確認する

USE ROLE TEST_ROLE;
SELECT * FROM DEMO_DB.DEMO_SCHEMA.SALES;

TEST_ROLE には SELECT 権限を付与したため、当然このクエリは成功します。

Query produced no results

テーブルにデータは無いため結果は0件ですが、エラーにならずクエリが成功している点にご注目ください。

2. TEST_ROLE から SELECT 権限を剥奪する

次に、このロールからテーブルへの SELECT 権限を取り上げます。

USE ROLE SECURITYADMIN;
REVOKE SELECT ON TABLE DEMO_DB.DEMO_SCHEMA.SALES FROM ROLE TEST_ROLE;

これで TEST_ROLE ではアクセスできなくなるはずです。

3. 再度 TEST_ROLE でテーブルにアクセスする

権限が剥奪されたことを確認するため、もう一度 TEST_ROLE で SELECT 文を実行します。

USE ROLE TEST_ROLE;
SELECT * FROM DEMO_DB.DEMO_SCHEMA.SALES;

すると、なぜか成功してしまいます。

Query produced no results

4. PUBLIC でもテーブルにアクセスしてみる

さらに、今度は PUBLIC ロールに切り替えて SELECT 文を実行してみます。

USE ROLE PUBLIC;
SELECT * FROM DEMO_DB.DEMO_SCHEMA.SALES;

なんと、こちらも成功してしまいました。

Query produced no results

実行結果と考察

このように、権限を REVOKE したにもかかわらず、SELECT 文は 成功してしまいます。 さらに、なんと権限を一切付与していない PUBLIC ロールからでも正常にクエリを実行することができてしまいます。

これでは、権限が正しく剥奪されたか検証できません。

それでは、なぜクエリが成功してしまうのか見ていきましょう。もちろん、ここで確認するのはセカンダリロールです。

SELECT CURRENT_SECONDARY_ROLES();

{"roles":"ACCOUNTADMIN,TEST_ROLE","value":"ALL"}

たとえば、筆者の環境では、上記のように ACCOUNTADMIN ロールがセカンダリロールとして有効になっていました。この ACCOUNTADMIN ロールは、テーブルを作成した SYSADMIN ロールから権限を継承しています。これにより、プライマリロールである TEST_ROLE に権限がなくてもクエリが成功してしまったのです。

このように、自身が意図しない強力なロールがセカンダリロールとして有効になっている場合、そのロールが持つ権限によって、プライマリロールに権限がなくてもクエリが成功してしまう仕組みになっています。

セカンダリロールを無効化して正しく権限を検証する

意図しない権限の影響を排除し、プライマリロールの権限のみを正確にテストするには、USE SECONDARY ROLES NONE; を使用してセカンダリロールを一時的に無効化します。

検証

1. 現在のセッションで、セカンダリロールをすべて無効化する

以下の SQL を実行することで、このセッションではプライマリロールの権限のみが評価されるようになります。

USE SECONDARY ROLES NONE;

2. 先ほどと同じ SELECT 文を実行する

プライマリロールを TEST_ROLE に切り替えて SELECT 文を実行します。

USE ROLE TEST_ROLE;
SELECT * FROM DEMO_DB.DEMO_SCHEMA.SALES;

すると、今度は想定通りに失敗します。

002003 (42S02): SQL compilation error:
Object 'DEMO_DB.DEMO_SCHEMA.SALES' does not exist or not authorized.

同様に、PUBLIC ロールでも試してみます。

USE ROLE PUBLIC;
SELECT * FROM DEMO_DB.DEMO_SCHEMA.SALES;

そもそもデータベースの USAGE 権限を付与していないので、こちらも当然失敗します。

Database 'DEMO_DB' does not exist or not authorized.

実行結果と考察

今度は SELECT 文は失敗し、期待通り権限エラーが発生しました。

ここで、セカンダリロールを再度確認してみましょう。

SELECT CURRENT_SECONDARY_ROLES();

すると、有効になっているロールが存在しないことがわかります。

{"roles":"","value":""}

これにより、TEST_ROLE の権限のみが評価され、「SALES テーブルへの SELECT 権限がない」という状態を正しく検証することができました。

セカンダリロールを制御する方法

セカンダリロールの挙動は、セッションレベルまたはユーザーレベルで制御できます。

一時的に無効にする方法（セッションレベル）

今回の検証で利用した方法です。USE SECONDARY ROLES を利用して、現在のセッションでのみセカンダリロールを無効化します。

特定のロールの権限をピンポイントでテストしたい場合に非常に便利です。セッションを閉じたり、USE SECONDARY ROLES ALL; を実行したりすると元の状態（すべてのロールが有効）に戻ります。

-- セカンダリロールを無効化
USE SECONDARY ROLES NONE;

-- セカンダリロールを再度有効化
USE SECONDARY ROLES ALL;

恒久的に無効にする方法（ユーザーレベル）

ユーザーのプロパティ DEFAULT_SECONDARY_ROLES を変更することで、新しいセッションを開始した際のデフォルトの挙動を制御できます。

毎回 USE SECONDARY ROLES NONE; を実行するのが面倒であればこちらの設定をしましょう。

-- セカンダリロールをデフォルトで無効化
ALTER USER <your_user_name> SET DEFAULT_SECONDARY_ROLES = ();

-- セカンダリロールを再度デフォルトで有効化
ALTER USER <your_user_name> SET DEFAULT_SECONDARY_ROLES = ('ALL');

さらに、「Enterprise Edition」以上の Edition を利用しているのであれば、以下のような柔軟な設定を行うことも可能です。

  • 「アカウント内のすべてのユーザーに対してセカンダリロールを許可しない」
  • 「特定のユーザーに対してセカンダリロールを許可しない」
  • 「ユーザーに特定のセカンダリロールの使用を許可する」

詳細については、公式ドキュメントの セッションポリシーでセカンダリロールを指定する をご確認ください。

まとめ

  • Snowflake でロールの権限を検証する際は、意図せず有効になっているセカンダリロールの存在を常に意識することが重要です。
  • 権限を操作したのに挙動が変わらない、といった場合は、まず CURRENT_SECONDARY_ROLES() で有効になっているロールを確認しましょう。
  • 特定のロールの権限を正確にテストしたい場合は、USE SECONDARY ROLES NONE; を活用して、他のロールの影響を排除することが不可欠です。
  • 必要に応じて ALTER USER でユーザーのデフォルト設定を変更することも検討しましょう。

このセカンダリロールの挙動を理解することで、Snowflakeの権限周りの設定やテストがよりスムーズに進められるようになります。

参考リンク

Tags:

Posted:

Author: hrkhjp

濱田（@hrkhjp）。 風音屋のデータエンジニア。自己紹介や入社の経緯はこちら。

You May Also Enjoy