【Snowflake】強い権限を持つロールをユーザーに付与したくないですが、権限調整はしたいです!可能でしょうか?

【Snowflake】強い権限を持つロールをユーザーに付与したくないですが、権限調整はしたいです!可能でしょうか?

権限調整をするロールの実行できるユーザーを増やしてみよう。
Clock Icon2024.10.09

みなさんこんにちは、クルトンです。

今回は表題の通りのお話です。ロール周りで権限調整などが必要になるほど、Snowflake内でのロールやユーザー、データベースなどのオブジェクト数が充実してくると「AccountAdminのような強い権限を持つロールはユーザー付与したくないけれど、ロールに対して権限調整をしてもらいたい」など運用面での課題が出てくるかと思います。

今回はStreamlit in Snowflakeとプロシージャを使いつつ、実現していきます。具体的に本ブログで権限調整を試す対象は、ロール同士です。ロール継承をさせたり外したりするStreamlitアプリを作成します。(おまけとして、他オブジェクトでの権限調整ができるようにアプリを作っています。)

前提

Streamlit in Snowflakeを使いますので、そちらを自分のアカウントで使えるかどうかチェックしてください。

すでにデータベースやスキーマ、ウェアハウスは作っている状態で始めます。また、AccountAdminロールでSQLワークシートを利用してプロシージャなどを作成し、Streamlitアプリの方は、権限の少ないロールでGUIを使って作成する事といたします。

つまりGRANT文などを発行できる権限の強いロールと、GRANT文などが発行できない権限の弱いロールの2つが必要となります。

本ブログの内容を利用する際の注意点

今回動作検証をしているのは主にロールの権限調整周りです。また、同じデータベースの同じスキーマ内に対してプロシージャやStreamlitアプリを作成しています。今後Streamlitアプリの作成場所とプロシージャの作成場所や、権限調整対象のオブジェクトを選択時にデータベースやスキーマを選択できるよう改良する必要があるかと思います。

やってみた

プロシージャ作成

まずは強めの権限を持っているロール(例えばACCOUNTADMINロール)でプロシージャを作っていきます。

USE ROLE ACCOUNTADMIN;
USE DATABASE <データベース名>;
USE SCHEMA <スキーマ名>;

-- ロール権限調整用のプロシージャ
CREATE OR REPLACE PROCEDURE GRANT_REVOKE_ROLE_PROCEDURE(
    command string
    , source_role string
    , target_role string
)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'main'
EXECUTE AS OWNER
AS
$$
def main(session, command, source_role, target_role):

    with session:
        if command.upper()=='GRANT':
            query = f"GRANT ROLE {source_role} TO ROLE {target_role}"
        elif command.upper()=='REVOKE':
            query = f"REVOKE ROLE {source_role} FROM ROLE {target_role}"
        else:
            return "そのコマンド入力で受け付けておりません。"
        data = session.sql(query).collect()
    return data
$$
;

-- ロール以外の権限調整用プロシージャ
CREATE OR REPLACE PROCEDURE GRANT_REVOKE_OBJECT_PROCEDURE(
    command string
    , source_object_type string
    , source_object string
    , target_role string
    , privilege_type string
    , future_flag boolean
)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'main'
EXECUTE AS OWNER
AS
$$
def main(session, command, source_object_type, source_object, target_role, privilege_type,future_flag):

    with session:
        if command.upper()=='GRANT':
            if future_flag == True:
                query = f"GRANT {privilege_type} ON FUTURE {source_object_type}S TO ROLE {target_role}"
            else:
                query = f"GRANT {privilege_type} ON {source_object_type} {source_object} TO ROLE {target_role}"
        elif command.upper()=='REVOKE':
            if future_flag == True:
                query = f"REVOKE {privilege_type} ON FUTURE {source_object}s FROM ROLE {target_role}"
            else:
                query = f"REVOKE {privilege_type} ON {source_object_type} {source_object} FROM ROLE {target_role}"
        else:
            return "そのコマンド入力で受け付けておりません。"
        data = session.sql(query).collect()
    return data
$$
;

-- プロシージャが使えるよう、使用権限のみ与えておく(中身の修正などはできない)
GRANT USAGE ON PROCEDURE GRANT_REVOKE_ROLE_PROCEDURE(string,string,string) TO ROLE <権限弱い方のロール>;
GRANT USAGE ON PROCEDURE GRANT_REVOKE_OBJECT_PROCEDURE(string,string,string,string,string,boolean) TO ROLE <権限弱い方のロール>;

プロシージャの記載方法自体は、別のブログや公式ドキュメントなどをご参考にしてください。簡単に説明すると、2024/10/09現在使えるPythonのバージョンで最新のもので、Pythonコードを記載しており、内容としてはGRANT文やREVOKE文を発行するように作っています。(単純な作りですね。)

一つ注意が必要なのがEXECUTE AS OWNERの部分です。作成したプロシージャを実行する際の実行時ロールについてのオプションです。今回は、権限調整をするためにACCOUNTADMINロールを使いたかったので、EXECUTE AS OWNERを記載しています。
(デフォルト値でEXECUTE AS OWNERではあるため、記載なくとも動きますが、仕様変更が今後起こる可能性はあるかもしれないため、あえて明記しています。)

また、実は以前まではSnowflake内とのコネクションを確立するためにconfig設定などを書いていました。今回試すStreamlitアプリ上ではすでにコネクションが繋がっている状態で実行する事もあり、ありがたい事にクレデンシャルな情報を記載する必要がないです。

ここまでで、権限調整用のプロシージャを作成しました。

権限調整対象のロールを作成

プロシージャ作成後、続いて以下のSQLを実行してください。

CREATE ROLE SOURCE_ROLE_FOR_ST_TEST; -- 付与元のロール
CREATE ROLE TARGET_ROLE_FOR_ST_TEST; -- 付与先のロール

これで権限調整のためにSQLワークシートで実行する必要があるものは完了です。

Streamlitアプリを作成する

まずStreamlitを作成する画面へページ遷移します。SQLワークシートを開いていると、以下の画像のように、ページ左側のページから「Streamlit」を選択してください。
001

続いて、ロールを選択します。ページ左下のユーザー名が記載されている箇所をクリック後に「SwitchRole」という箇所があるので、そちらからロールを選択します。(プロシージャのUSAGE権限を付与したロールを選んでください。)
002

続いて、ページ右上にある「+ Streamlit App」と書かれているボタンをクリックしてください。
003

「+ Streamlit App」ボタンをクリック後に出てくる画面で、以下の内容を記載してください。その後「Create」ボタンをクリックします。

  • App title
    • アプリのタイトルです。自分はデフォルトのままにしました。
  • App location
    • プロシージャを作る際に「USE DATABASE」と「USE SCHEMA」で選んだものを選択しました。(スキーマを新規で作成していない場合は、スキーマ選択が出来ずPUBLICスキーマへ作成されます。)
  • App warehouse
    • 今回のアプリは簡単なものなので、XSMALLサイズで良いです。

ページ遷移後の画面で、画面左側へPythonコードを記載できます。サンプルがすでに記載されているかと思いますが、そちらを全てコメントアウトするか、削除してください。
その後、以下のPythonコードをコピペして貼り付けてください。

# ブログ用権限調整アプリ
import streamlit as st
from snowflake.snowpark.context import get_active_session

st.title('Snowflake権限管理アプリ')
st.info('OWNERSHIP権限を除き、権限調整をするためのアプリです。', icon="🚨")

session = get_active_session()
future_flag = 'NO'

# オブジェクトの種類を選択(権限の付与元)
source_object_type = st.selectbox('権限を付与元オブジェクトの種類', ['TABLE', 'VIEW', 'SCHEMA', 'DATABASE', 'ROLE'])

# オブジェクトの種類を選択(権限を与える先)
if source_object_type=='ROLE':
    target_object_type = st.selectbox('権限を与えたいオブジェクトの種類', ['ROLE', 'USER'])
else:
    target_object_type = st.selectbox('権限を与えたいオブジェクトの種類', ['ROLE'])

# 権限の種類を選択
if source_object_type=='TABLE' or source_object_type=='VIEW':
    privilege_type = st.selectbox('権限の種類', ['SELECT', 'INSERT', 'UPDATE', 'DELETE'])
    future_flag = st.selectbox('将来権限ですか?', ['NO', 'YES'])
if source_object_type=='SCHEMA' or source_object_type=='DATABASE':
    privilege_type = st.selectbox('権限の種類', ['USAGE'])

if future_flag == 'NO':
    source_object = st.text_input(f'権限を与えたい{target_object_type}名を入力してください','<付与元オブジェクト名>')
target_objcet = st.text_input('権限付与元のオブジェクト名を入力してください', '<付与先オブジェクト名>')

# 権限付与する場合の具体的なSQL
if source_object_type =='ROLE':
    st.write(f'権限を付与する場合は「GRANT ROLE {source_object} TO {target_object_type} {target_objcet}」という内容のSQLを実行します。')
else:
    if future_flag=='YES':
        st.write(f'権限を付与する場合は「GRANT {privilege_type} ON FUTURE {source_object_type}S TO ROLE {target_objcet}」という内容のSQLを実行します。')
    else:
        st.write(f'権限を付与する場合は「GRANT {privilege_type} ON {source_object_type} {source_object} TO ROLE {target_objcet}」という内容のSQLを実行します。')

# 権限を付与するボタンの設定
if st.button('権限を付与'):
    if source_object_type=='ROLE':
        query = session.call('GRANT_REVOKE_ROLE_PROCEDURE','GRANT', source_object, target_objcet)
        st.success(f"ROLE '{target_objcet}' に {source_object_type} '{source_object}' を付与しました。")
    else:
        query = session.call('GRANT_REVOKE_OBJECT_PROCEDURE','GRANT',source_object_type,source_object,target_objcet,privilege_type,future_flag)
        st.success(f"ROLE '{target_objcet}' に {source_object_type} '{source_object}' の {privilege_type}権限を付与しました。")

# 権限を取り外す場合の具体的なSQL
if source_object_type =='ROLE':
    st.write(f'権限を取り外す場合は「REVOKE ROLE {source_object} FROM {target_object_type} {target_objcet}」という内容のSQLを実行します。')
else:
    if future_flag=='YES':
        st.write(f'権限を取り外す場合は「REVOKE {privilege_type} ON FUTURE {source_object_type}S FROM ROLE {target_objcet}」という内容のSQLを実行します。')
    else:
        st.write(f'権限を取り外す場合は「REVOKE {privilege_type} ON {source_object_type} {source_object} FROM ROLE {target_objcet}」という内容のSQLを実行します。')

# 権限を取り外すボタンの設定
if st.button('権限を取り消し'):
    if source_object_type=='ROLE':
        query = session.call('GRANT_REVOKE_ROLE_PROCEDURE','REVOKE', source_object, target_objcet)
        st.success(f"ROLE '{target_objcet}' から {source_object_type} '{source_object}' を取り外しました。")
    else:
        query = session.call('GRANT_REVOKE_OBJECT_PROCEDURE','REVOKE',source_object_type,source_object,target_objcet,privilege_type,future_flag)
        st.success(f"ROLE '{target_objcet}' から {source_object_type} '{source_object}' の {privilege_type}権限を取り外しました。")

上記コードへ書き換えた後、ページ右上の「RUN」ボタンをクリックしてください。修正した内容が反映されるはずで、以下のような画面になるはずです。
004

Pythonコード内で使っているモジュールについて簡単に説明すると、「st.<UIパーツ名>」でStreamlitのモジュールを使ってUIのボタンなどを配置できます。そこまで難しくないと思いますので、Pythonコードを読んでみて、可能ならアプリを動作させてみてください。

コードの概要としては、どういったオブジェクトに対してどのような権限を割り振るかを地道にプログラミングしたものになります。権限の内容や対象オブジェクトについてはSELECTBOXで、オブジェクト名については直接記入できるようにしています。
(アプリを動作できる方は、色々とSELECTBOXを動かしてみていただけますと、画面がユーザーの選択に合わせて変化する様子も確認できます。)

Sreamlitアプリを動作させてみた

今回はロール同士の権限調整を試したいので、以下のように設定します。(テキストボックスにロール名を直接入力後に、Enterキーを押すことを忘れないでください。)
005

では、まずは継承をしてみます。現時点では以下のようになっているかと思います。
006

そこで、Streamlitアプリの「権限を付与」ボタンをクリックします。
007

そうすると、GRANT文が発行できないロールであっても、ちゃんとロール継承させるGRANT文を発行できているのが確認できました。
008

次に権限を取り外してみます。「権限を取り外し」ボタンをクリックします。
009

権限が取り外せているのを確認しました。
010

後片付け

権限の強いロール(本ブログではACCOUNTADMINロール)で、以下のSQLを実行します。

DROP PROCEDURE GRANT_REVOKE_ROLE_PROCEDURE(string,string,string);
DROP PROCEDURE GRANT_REVOKE_OBJECT_PROCEDURE(string,string,string,string,string,boolean);
DROP ROLE SOURCE_ROLE_FOR_ST_TEST;
DROP ROLE TARGET_ROLE_FOR_ST_TEST;

続いて、Streamlitアプリの右上の「RUN」ボタンとなりの3点マークから、Deleteをクリックし削除してください。
011

ちなみに

Streamlit内で以下のようなSQLを書いてAccountAdminで実行したとしても、通りません。

session = get_active_session()
# ロールのリスト取得用にSHOW文を実行する
roles = session.sql("SHOW ROLES;")

こちら理由としましては、Streamlit in Snowflakeのコネクションは、2024/10/09現在カレントユーザーが設定されていないからです。実はSHOW ROLESについてはユーザーが実行している事になっているため、カレントユーザーがないと実行できないという事になります。
詳しくはSnowflakeコミュニティでのこちらの記事をご確認ください。

上記の記事内において、Snowflake社員さんによると「プライベートプレビュー(PrPr)でカレントユーザーが設定されるように出来る」との事ですので、もしSnowflake社の担当の方がついているような契約をしている場合は、質問を投げかけるのも良いと思います。

終わりに

今回は、Streamlit in Snowflakeについて実際に動かしてみました。
想像以上にアプリが簡単に作成できるので、とても便利だと思いつつ、少し注意が必要な事もあります。(「ちなみに」で書いた内容です。)
また、今回はDatabaseやスキーマに関して意識的に選択するような方法をしていないため、エラーが出る可能性もあるので、今後改良が必要そうです。(ROLEでの動作検証しかできていないため……。)

しかし、今まででは権限の都合で作業ができなかったユーザーに作業を依頼するという運用面を考えると、とても便利な機能であるという認識になりました!
その他個人的には今後も色々と使っていきたく思いました。

本日はここまで。
それでは、また!

Share this article

facebook logohatena logotwitter logo

© Classmethod, Inc. All rights reserved.