【Snowflake】最強の呪文 SECONDARY ROLES を制御・制限しよう

本記事で参考になるケース

• ユーザの USE SECONDARY ROLESを制御・制限したい！

前提 SECONDARY ROLE とは？

通常ユーザは自身が利用できるROLEを選択してその権限を行使する.
この時、他のROLEの権限は行使できない.

<例>

## ROLE_A と ROLE_A に継承されている ROLE_D の権限を行使する. この時、ROLE_B, C の権限は行使できない.
USE ROLE ROLE_A;　

## ROLE_B の権限を行使する. この時、ROLE_A, C, D の権限は行使できない.
USE ROLE ROLE_B;

## ROLE_C の権限を行使する. この時、ROLE_A, B, D の権限は行使できない.
USE ROLE ROLE_C;

## ROLE_D の権限を行使する. この時 ROLE_A, B, C の権限は行使できない
USE ROLE ROLE_D; 

最強の呪文 USE SECONDARY ROLES ALL;

しかしユーザはUSE SECONDARY ROLES ALL;を利用することにより全てのROLEの権限が行使可能となる

## ROLE_A, B, C, D の全ての権限を行使する.
USE SECONDARY ROLES ALL;

※幾つかのROLEの権限を同時に行使する場合

## ROLE_B,Cの権限を行使する.
USE ROLE ROLE_B;
USE SECONDARY ROLES ROLE_C;

SECONDARY ROLES を制限する

August 07-08, 2024 — 8.29 Release Notes　によりついに制限することが可能となりました

SESSION POLICYを用いて制限を行います

CREATE OR REPLACE SESSION POLICY deny_secondary_roles_all
  ALLOWED_SECONDARY_ROLES = ();

CREATE OR REPLACE SESSION POLICY allow_secondary_roles_all
  ALLOWED_SECONDARY_ROLES = ('ALL');

アカウント全体でデフォルトでSECONDARY ROLESを禁止する

ALTER ACCOUNT SET SESSION POLICY deny_secondary_roles_all;

### USERは実行できない. ROLE_Bの権限のみ行使可能
USE ROLE ROLE_B; 
❌ USE SECONDARY ROLES ALL; 

特定のユーザのSECONDARY ROLESを禁止する

ALTER USER user_α SET SESSION POLICY deny_secondary_roles_all;

### user_α は実行できない. ROLE_Bの権限のみ行使可能
USE ROLE ROLE_B; 
❌ USE SECONDARY ROLES ALL; 

### user_β は実行できる. ROLE_A,B,C,Dの権限を行使可能
USE ROLE ROLE_B; 
⭕️ USE SECONDARY ROLES ALL; 

### user_γ は実行できる. ROLE_A,B,C,Dの権限を行使可能
USE ROLE ROLE_B;
⭕️ USE SECONDARY ROLES ALL; 

アカウント全体で禁止し、特定のユーザのみUSE SECONDARY ROLESを許可する

## デフォルトでアカウント全体で USE SECONDARY ROLES を禁止
ALTER ACCOUNT SET SESSION POLICY deny_secondary_roles_all;

## 特定のユーザのみ USE SECONDARY ROLES を許可
ALTER USER user_α SET SESSION POLICY allow_secondary_roles_all;

### user_α は実行できる. ROLE_A,B,C,Dの権限を行使可能
USE ROLE ROLE_B;
⭕️ USE SECONDARY ROLES ALL; 

### user_β は実行できない. ROLE_Bの権限のみ行使可能
USE ROLE ROLE_B;
❌ USE SECONDARY ROLES ALL; 

### user_γ は実行できない. ROLE_Bの権限のみ行使可能
USE ROLE ROLE_B;
❌ USE SECONDARY ROLES ALL; 

特定の SECONDARY ROLEのみ許可する.　[ USE SECONDARY ROLES ALL] の意味が変わります!!!

特定のROLEのみSECONDARY ROLEを許可する

CREATE OR REPLACE SESSION POLICY allow_secondary_roles_role_b_role_c;
  ALLOWED_SECONDARY_ROLES = (ROLE_B, ROLE_C);

ALTER USER user_α SET SESSION POLICY allow_secondary_roles_role_b_role_c;

ケース①

### user_α は実行できる. ROLE_B,Cの権限を行使できる.
USE ROLE ROLE_B;
⭕️ USE SECONDARY ROLES ROLE_C;

ケース②

### user_α は実行できる. ROLE_B,Cの権限を行使できる.
USE ROLE ROLE_B;
⭕️ USE SECONDARY ROLES ALL; 

注意！！！　
この場合、ROLE_A の権限を行使は不可能

ケース① = ケース② であり、user_αは同じROLE_B,Cの権限を行使できる.

POINT　(制限したようで制限できていない例)

下記の場合、user_αは同じ権限を行使可能 ケース③ = ケース④

ケース③ 　　SECONDARY ROLE を ROLE_B, ROLE_C に制限

### 管理者実行
CREATE OR REPLACE SESSION POLICY allow_secondary_roles_role_b_role_c;
  ALLOWED_SECONDARY_ROLES = (ROLE_B, ROLE_C);

ALTER USER user_α SET SESSION POLICY allow_secondary_roles_role_b_role_c;

### user_α の実行

USE ROLE ROLE_A;
USE SECONDARY ROLES ALL; ## 許可されている ROLE_B, ROLE_C の権限を利用できる
-- USE SECONDARY ROLES ROLE_B, ROLE_C; でもOK

ケース④　　何も制限しない

### user_α の実行
USE SECONDARY ROLES ALL;

どちらのケースも ROLE_A, (ROLE_D), ROLE_B, ROLE_C の権限を行使　が可能

まとめ

最強の呪文、SECONDARY ROLESを制御可能となりました.
適宜利用していきましょう！

参考

https://docs.snowflake.com/en/user-guide/session-policies-using#label-session-policies-no-sec-roles-user