Snowflake のタスクの実行プログラムを外部ファイル化する方法2（Streamlitアプリ）

# 概要
　[Snowflake のタスクの実行プログラムを外部ファイル化する方法](https://qiita.com/mshdtksk/items/bd7e06f2d0b35857b661)の記事で，ストアドプロシージャやタスクの権限がないロールのユーザに対して，ストアドプロシージャを外部ファイル化することで，擬似的にその権限がないユーザでも，任意のSQLまたはPythonプログラムに編集可能な仕組みを紹介した．一方で，本仕組みの場合，システムとユーザの責任の分離に関する課題がある点についても紹介した．
 　今回は，ストアドプロシージャやタスクの権限がないロールのユーザに対して，ストアドプロシージャを編集可能な仕組みをStreamlitにてアプリとして提供することを考える．この仕組みの特徴として，編集した任意のSQLをユーザ側で動作確認を行えるため，Streamlitのアプリの作成はシステム側の責任で行い，Streamlitのアプリを経由して実行するSQLの動作確認はユーザ側の責任で行えるため，SQLの編集以降はユーザ側の責任とすることができる．今回は，タスクは事前に準備している想定で，ストアドプロシージャの中にあるSQL部分のみを編集する形としているが，タスク部分もユーザ側で編集可能な作りとすることで，完全にシステムとユーザの責任を分離することが可能である．

# サンプルコード
```SQL
import snowflake.connector
import streamlit as st
import pandas as pd

# Snowflake接続設定
def get_snowflake_connection():
    return snowflake.connector.connect(
        user="<USERNAME>",
        password="<PASSWORD>",
        account="<ACCOUNT>",
        database="<DATABASE_NAME>",
        schema="<SCHEMA_NAME>",
        role="<ROLE_NAME>",
        warehouse="<WAREHOUSE_NAME>"
    )

# ストアドプロシージャを作成する関数
def create_stored_procedure(proc_name, sql_code):
    try:
        conn = get_snowflake_connection()
        cursor = conn.cursor()

        # ストアドプロシージャのSQL
        procedure_sql = f"""
        CREATE OR REPLACE PROCEDURE {proc_name}()
        RETURNS STRING
        LANGUAGE SQL
        EXECUTE AS CALLER
        AS $$
        BEGIN
            {sql_code}
            RETURN 'Procedure executed successfully';
        END;
        $$;
        """
        cursor.execute(procedure_sql)
        return "ストアドプロシージャが正常に作成されました！"
    except Exception as e:
        return f"エラーが発生しました: {e}"
    finally:
        cursor.close()
        conn.close()

# ストアドプロシージャを実行する関数
def execute_stored_procedure(proc_name):
    try:
        conn = get_snowflake_connection()
        cursor = conn.cursor()

        # ストアドプロシージャの実行
        exec_sql = f"CALL {proc_name}();"
        cursor.execute(exec_sql)
        result = cursor.fetchone()  # 結果を取得
        return f"ストアドプロシージャの実行結果: {result[0]}"
    except Exception as e:
        return f"エラーが発生しました: {e}"
    finally:
        cursor.close()
        conn.close()

# Streamlit UI
st.title("Snowflakeストアドプロシージャ作成＆実行ツール")

# ユーザー入力
proc_name = st.text_input("ストアドプロシージャ名", placeholder="例: my_stored_procedure")
sql_code = st.text_area("ストアドプロシージャ内のSQLを入力", height=200, placeholder="例: SELECT * FROM my_table;")

# ストアドプロシージャ作成
if st.button("作成"):
    if not proc_name.strip():
        st.error("ストアドプロシージャ名を入力してください。")
    elif not sql_code.strip():
        st.error("SQLコードを入力してください。")
    else:
        result = create_stored_procedure(proc_name.strip(), sql_code.strip())
        if "エラー" in result:
            st.error(result)
        else:
            st.success(result)

# ストアドプロシージャ実行
if st.button("実行"):
    if not proc_name.strip():
        st.error("ストアドプロシージャ名を入力してください。")
    else:
        result = execute_stored_procedure(proc_name.strip())
        if "エラー" in result:
            st.error(result)
        else:
            st.success(result)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3880471/e083fb46-8aab-c32a-50a7-2bcdf895f384.png)



# 応用（セキュリティ強化）
サンプルコードで実装すると，ストアドプロシージャやタスクの権限がないロールのユーザに対して，ストアドプロシージャを編集可能な状態を実現できるが，ストアドプロシージャやタスクの権限を持ったユーザでの実行と成るため，本アプリを経由することで，本来ユーザができないSQLまで実行できてしまう可能性が残る．そのため，その点を解消するために，2点の制限を加えることで，ユーザのロールの範囲内でのストアドプロシージャを編集しかできないようにした．
* ストアドプロシージャ内のSQLの先頭に，use role \<ROLE_NAME>を記載することで，そのユーザが実行できないSQLは抑止する．Snowflake側の仕様により，use roleによるロールの変更は1つのSQL内で1度しか反映されないため，ユーザ側でuse roleを追加してもロールの変更はできないことを利用している．
* ストアドプロシージャ名の入力ボックスをなくし，proc_name = "\<DATABASE_NAME>.\<SCHEMA_NAME>.\<PROCEDURE_NAME>"に変更することでストアドプロシージャ名をユーザ側で指定させないように抑止する

```SQL
import snowflake.connector
import streamlit as st
import pandas as pd

# Snowflake接続設定
def get_snowflake_connection():
    return snowflake.connector.connect(
        user="<USERNAME>",
        password="<PASSWORD>",
        account="<ACCOUNT>",
        database="<DATABASE_NAME>",
        schema="<SCHEMA_NAME>",
        role="<ROLE_NAME>",
        warehouse="<WAREHOUSE_NAME>"
    )

# ストアドプロシージャを作成する関数
def create_stored_procedure(proc_name, sql_code):
    try:
        conn = get_snowflake_connection()
        cursor = conn.cursor()

        # ストアドプロシージャのSQL
        procedure_sql = f"""
        CREATE OR REPLACE PROCEDURE {proc_name}()
        RETURNS STRING
        LANGUAGE SQL
        EXECUTE AS CALLER
        AS $$
        BEGIN
            use role <ROLE_NAME>
            {sql_code}
            RETURN 'Procedure executed successfully';
        END;
        $$;
        """
        cursor.execute(procedure_sql)
        return "ストアドプロシージャが正常に作成されました！"
    except Exception as e:
        return f"エラーが発生しました: {e}"
    finally:
        cursor.close()
        conn.close()

# ストアドプロシージャを実行する関数
def execute_stored_procedure(proc_name):
    try:
        conn = get_snowflake_connection()
        cursor = conn.cursor()

        # ストアドプロシージャの実行
        exec_sql = f"CALL {proc_name}();"
        cursor.execute(exec_sql)
        result = cursor.fetchone()  # 結果を取得
        return f"ストアドプロシージャの実行結果: {result[0]}"
    except Exception as e:
        return f"エラーが発生しました: {e}"
    finally:
        cursor.close()
        conn.close()

# Streamlit UI
st.title("Snowflakeストアドプロシージャ作成＆実行ツール")

# ユーザー入力
proc_name = "<DATABASE_NAME>.<SCHEMA_NAME>.<PROCEDURE_NAME>"
sql_code = st.text_area("ストアドプロシージャ内のSQLを入力", height=200, placeholder="例: SELECT * FROM my_table;")

# ストアドプロシージャ作成
if st.button("作成"):
    if not proc_name.strip():
        st.error("ストアドプロシージャ名を入力してください。")
    elif not sql_code.strip():
        st.error("SQLコードを入力してください。")
    else:
        result = create_stored_procedure(proc_name.strip(), sql_code.strip())
        if "エラー" in result:
            st.error(result)
        else:
            st.success(result)

# ストアドプロシージャ実行
if st.button("実行"):
    if not proc_name.strip():
        st.error("ストアドプロシージャ名を入力してください。")
    else:
        result = execute_stored_procedure(proc_name.strip())
        if "エラー" in result:
            st.error(result)
        else:
            st.success(result)
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3880471/4444fc28-66e0-b0fd-e574-90d051587389.png)

# 修正
*EXECUTE AS OWNER*を記載しないと，デフォルトで*EXECUTE AS OWNER
*が追加され，AS $$以降の実行SQLに権限がない場合エラーとなるため，
EXECUTE AS CALLERを明示的に記載する必要がある．
* 変更前
```
CREATE OR REPLACE PROCEDURE {proc_name}()
    RETURNS STRING
    LANGUAGE SQL
    AS $$
```

* 変更前（自動適用）
```
CREATE OR REPLACE PROCEDURE {proc_name}()
    RETURNS STRING
    LANGUAGE SQL
    EXECUTE AS OWNER
    AS $$
```

* 変更後
```
CREATE OR REPLACE PROCEDURE {proc_name}()
    RETURNS STRING
    LANGUAGE SQL
    EXECUTE AS CALLER
    AS $$
```