呼び出し元権限と所有者権限のストアドプロシージャの理解

ストアドプロシージャは、 呼び出し元 権限または 所有者 権限のいずれかで実行されます。両方で同時に実行することはできません。このトピックでは、呼び出し元権限ストアドプロシージャと所有者権限ストアドプロシージャの違いについて説明します。

このトピックの内容:

概要

呼び出し元権限ストアドプロシージャは、呼び出し元権限で実行されます。呼び出し元権限ストアドプロシージャの主な利点は、その呼び出し元または呼び出し元の現在のセッションに関する情報にアクセスできることです。たとえば、呼び出し元権限ストアドプロシージャは、呼び出し元のセッション変数を読み取り、クエリでその変数を使用できます。

所有者権限ストアドプロシージャは、主にストアドプロシージャの所有者の権限で実行されます。所有者権限ストアドプロシージャの主な利点は、所有者が特定のテーブルからすべてのデータを削除する権限などのより一般的な権限をそのロールに付与することなく、古いデータのクリーンアップなどの特定の管理タスクを別のロールに委任できることです。

ストアドプロシージャの作成時に、作成者は、プロシージャを所有者権限で実行するか、呼び出し元権限で実行するかを指定します。デフォルトは所有者権限です。

所有者は、 ALTER PROCEDURE コマンドを実行することにより、所有者権限ストアドプロシージャから呼び出し元権限ストアドプロシージャ(またはその逆)にプロシージャを変更できます。

データベースオブジェクトの権限

呼び出し元権限ストアドプロシージャは、ストアドプロシージャを呼び出したロールのデータベース権限で実行されます。呼び出し元がストアドプロシージャの外部で実行できなかったステートメントは、ストアドプロシージャの内部でも実行できません。たとえば、「Nurse」という名前のロールに medical_records テーブルから行を削除する権限がない場合、「Nurse」ロールを持つユーザーがそのテーブルから行を削除しようとする呼び出し元権限ストアドプロシージャを呼び出すと、ストアドプロシージャは失敗します。

所有者権限プロシージャは、プロシージャの所有者の権限で実行されます。つまり、所有者がタスクを実行する権限を持っている場合、ストアドプロシージャは、そのタスクを直接実行する権限を持たないロールから呼び出された場合でも、そのタスクを実行できます。たとえば、「Doctor」という名前のロールに medical_records テーブルから行を削除するデータベース権限があり、「Doctor」ロールがそのテーブルから7年より古い行を削除するストアドプロシージャを作成する場合、「Doctor」ロールが「Nurse」ロールにストアドプロシージャの適切な権限を付与すると、「Nurse」ロールは、テーブルに対する削除権限がない場合でも、ストアドプロシージャを実行(そして、そのストアドプロシージャを介してテーブルから古い行を削除)できます。

セッション状態

他の SQL ステートメントと同様に、 CALL ステートメントはセッション内で実行され、セッションレベルの変数、現在のデータベースなど、そのセッションからコンテキストを継承します。プロシージャが継承する正確なコンテキストは、ストアドプロシージャが呼び出し元権限ストアドプロシージャであるか、所有者権限ストアドプロシージャであるかによって異なります。

呼び出し元権限ストアドプロシージャがセッションに変更を加えた場合、それらの変更は CALL の終了後も保持できます。所有者権限ストアドプロシージャは、セッション状態を変更することはできません。

呼び出し元権限ストアドプロシージャ

呼び出し元権限ストアドプロシージャは、セッション内で次のルールを順守します。

  • 所有者の権限ではなく、呼び出し元の権限で実行します。

  • 呼び出し元の現在のウェアハウスを継承します。

  • 呼び出し元が現在使用しているデータベースとスキーマを使用します。

  • 呼び出し元のセッション変数を表示、設定、設定解除できます。

  • 呼び出し元のセッションパラメーターを表示、設定、設定解除できます。

以下のセクションでは、呼び出し元権限ストアドプロシージャが呼び出し元のセッションレベル変数を読み書きする方法について詳しく説明します。

セッション変数

MyProcedure という名前のストアドプロシージャが、セッションレベルの変数を読み取って設定する SQL ステートメントを実行するとします。この例では、読み取りコマンドと設定コマンドの詳細は重要ではないため、ステートメントは擬似コードとして表されます。

  • READ SESSION_VAR1

  • SET SESSION_VAR2

ストアドプロシージャは、次の擬似コードに似ています。

CREATE PROCEDURE MyProcedure()
...
$$
   READ SESSION_VAR1;
   SET SESSION_VAR2;
$$
;

同じセッションで次の一連のステートメントを実行するとします。

SET SESSION_VAR1 = 'some interesting value';
CALL MyProcedure();
SELECT *
    FROM table
    WHERE column1 = $SESSION_VAR2;

これは、次のシーケンスを実行するのと同じです。

SET SESSION_VAR1 = 'some interesting value';
READ SESSION_VAR1;
SET SESSION_VAR2;
SELECT *
    FROM table
    WHERE column1 = $SESSION_VAR2;

つまり、

  • ストアドプロシージャは、プロシージャが呼び出される前にステートメントによって設定された変数を見ることができます。

  • ストアドプロシージャの後のステートメントは、プロシージャ内で設定された変数を見ることができます。

疑似コードに依存しない完全な例については、 呼び出し元権限と所有者権限のストアドプロシージャでのセッション変数の使用 (このトピック内)をご参照ください。

多くのストアドプロシージャでは、現在のデータベースや現在のセッションレベル変数などのコンテキスト情報を継承する必要があります。

ただし、状況に応じて、ストアドプロシージャをさらに分離する場合があります。例えば、ストアドプロシージャがセッションレベルの変数を設定する場合、セッションレベルの変数がストアドプロシージャ外の将来のステートメントに影響を与えないようにする場合があります。

ストアドプロシージャを残りのセッションからより適切に分離するには:

  • セッションレベルの変数を直接使用しないでください。代わりに、それらを明示的なパラメーターとして渡します。これにより、呼び出し元は、ストアドプロシージャが使用するセッションレベルの変数を正しく考えるようになります。

  • ストアドプロシージャ内で設定したセッションレベルの変数をクリーンアップします(他の場所で使用される可能性が低い名前を使用。そうすると、ストアドプロシージャの呼び出し前に存在するセッション変数を誤ってクリーンアップする恐れがない)。

次のストアドプロシージャは、セッション変数を直接使用するのではなく、パラメータとしてセッション変数の値を使用します。

SET Variable_1 = 49;
CREATE PROCEDURE sv_proc2(PARAMETER_1 FLOAT)
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    AS
    $$
        var rs = snowflake.execute( {sqlText: "SELECT 2 * " + PARAMETER_1} );
        rs.next();
        var MyString = rs.getColumnValue(1);
        return MyString;
    $$
    ;
  CALL sv_proc2($Variable_1);

次のストアドプロシージャは、通常とは異なる名前で一時的なセッション変数を作成し、ストアドプロシージャが終了する前にその変数をクリーンアップします。プロシージャ呼び出しの後のステートメントが、クリーンアップされたセッション変数を使用しようとすると、そのステートメントは失敗します。

CREATE PROCEDURE sv_proc1()
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    AS
    $$
        var rs = snowflake.execute( {sqlText: "SET SESSION_VAR_ZYXW = 51"} );

        var rs = snowflake.execute( {sqlText: "SELECT 2 * $SESSION_VAR_ZYXW"} );
        rs.next();
        var MyString = rs.getColumnValue(1);

        rs = snowflake.execute( {sqlText: "UNSET SESSION_VAR_ZYXW"} );

        return MyString;
    $$
    ;

CALL sv_proc1();
-- This fails because SESSION_VAR_ZYXW is no longer defined.
SELECT $SESSION_VAR_ZYXW;

注釈

C言語(または、Javaなどの類似の言語)でプログラムする場合、ストアドプロシージャ内で設定するセッション変数は、C言語の関数の実行が終了すると消えるC言語のローカル変数とは 異なります 。ストアドプロシージャをその環境から分離するには、C言語よりも SQL でより多くの作業が必要です。

所有者権限ストアドプロシージャ

所有者権限ストアドプロシージャは、セッション内で次のルールに従います。

  • 呼び出し元の権限ではなく、所有者の権限で実行します。

  • 呼び出し元の現在のウェアハウスを継承します。

  • 呼び出し元が現在使用しているデータベースとスキーマではなく、ストアドプロシージャが 作成されている データベースとスキーマを使用します。

  • ほとんどの呼び出し元固有の情報にアクセスできません。例:

    • 呼び出し元のセッション変数は表示、設定、または設定解除できません。

    • 特定のセッションパラメータ( こちら に記載)のみを読み取ることができ、呼び出し元のセッションパラメータを設定または設定解除することはできません。

    • 現在のユーザーに基づいて結果を返す AUTOMATIC_CLUSTERING_HISTORY などの INFORMATION_SCHEMA テーブル関数をクエリできません。

  • PROCEDURES ビューからプロシージャに関する情報を表示することを非所有者に許可しないでください。

セッション変数とセッションパラメータの制限については、以下で詳しく説明します。

セッション変数

ストアドプロシージャは、ストアドプロシージャの外部で作成された SQL 変数 にアクセスできません。この制限により、あるユーザーが作成または所有するストアドプロシージャが、別のユーザー(ストアドプロシージャ呼び出し元)によって作成された SQL 変数を読み取れないようにします。

ストアドプロシージャが、現在のセッションの SQL 変数に保存されている値を必要とする場合、それらの変数の値は、ストアドプロシージャに明示的な引数として渡す必要があります。例:

SET PROVINCE = 'Manitoba';
CALL MyProcedure($PROVINCE);

セッションパラメーター

状況に応じて、ストアドプロシージャが呼び出し元のセッションパラメーターを読み取れるようにすることが役立つ場合があります。ストアドプロシージャは、その呼び出し元またはセッションの動作をカスタマイズできます。例えば、ストアドプロシージャは、呼び出し元の優先 DATE_OUTPUT_FORMAT を使用できます。

ただし、他のケースでは、呼び出し元のセッションパラメーターを読み取ることが望ましくない場合があります。

  • ストアドプロシージャの作成者(所有者)が特定のセッションパラメーターを設定しているが、ストアドプロシージャの呼び出し元がそのパラメーターを設定していない場合、作成者以外のユーザーから呼び出された場合、ストアドプロシージャは失敗するか、異なる動作をする可能性があります。

  • あるユーザーがストアドプロシージャを作成し、別のユーザーがそのストアドプロシージャを呼び出し、そのストアドプロシージャがすべてのセッションパラメーターの読み取りを許可されている場合、呼び出し元がそれを知ることなく、呼び出し元が設定したセッションパラメーターをストアドプロシージャの作成者(所有者)は読み取ることができます。

これらの潜在的な問題を軽減するために、Snowflakeでは、ストアドプロシージャが呼び出し元のセッションパラメーターの特定のサブセットのみを使用できるようにします(以下のリストを参照)。

ストアドプロシージャ内のステートメントがサポートされていないパラメーターを参照する場合、ストアドプロシージャは呼び出し側のセッションレベルパラメーターではなく、所有者のアカウントレベルパラメーターの値を使用します。

ストアドプロシージャの所有者がアカウントパラメーターを明示的に設定したことがない場合、ストアドプロシージャはアカウントパラメーターのデフォルト値を使用します。

現在サポートされているパラメーターのリストには次が含まれます(リストは時間の経過とともに変更される可能性があります)。

  • AUTOCOMMIT

  • BINARY_INPUT_FORMAT

  • BINARY_OUTPUT_FORMAT

  • DATE_INPUT_FORMAT

  • DATE_OUTPUT_FORMAT

  • ENABLE_UNLOAD_PHYSICAL_TYPE_OPTIMIZATION

  • ERROR_ON_NONDETERMINISTIC_MERGE

  • ERROR_ON_NONDETERMINISTIC_UPDATE

  • JDBC_TREAT_DECIMAL_AS_INT

  • JSON_INDENT

  • LOCK_TIMEOUT

  • MAX_CONCURRENCY_LEVEL

  • ODBC_USE_CUSTOM_SQL_DATA_TYPES

  • PERIODIC_DATA_REKEYING

  • QUERY_TAG

  • QUERY_WAREHOUSE_NAME

  • ROWS_PER_RESULTSET

  • STATEMENT_QUEUED_TIMEOUT_IN_SECONDS

  • STATEMENT_TIMEOUT_IN_SECONDS

  • STRICT_JSON_OUTPUT

  • TIMESTAMP_DAY_IS_ALWAYS_24H

  • TIMESTAMP_INPUT_FORMAT

  • TIMESTAMP_LTZ_OUTPUT_FORMAT

  • TIMESTAMP_NTZ_OUTPUT_FORMAT

  • TIMESTAMP_OUTPUT_FORMAT

  • TIMESTAMP_TYPE_MAPPING

  • TIMESTAMP_TZ_OUTPUT_FORMAT

  • TIMEZONE

  • TIME_INPUT_FORMAT

  • TIME_OUTPUT_FORMAT

  • TRANSACTION_ABORT_ON_ERROR

  • TRANSACTION_DEFAULT_ISOLATION_LEVEL

  • TWO_DIGIT_CENTURY_START

  • UNSUPPORTED_DDL_ACTION

  • USE_CACHED_RESULT

  • WEEK_OF_YEAR_POLICY

  • WEEK_START

セッションパラメータの詳細については、 パラメーターALTER SESSION、 および SHOW PARAMETERS をご参照ください。

所有者権限ストアドプロシージャに関するいくつかの制限を追加

所有者権限ストアドプロシージャには、セッション変数とセッションパラメータに関連する制限の他に、いくつかの追加の制限があります。これらの制限は以下に影響します。

  • GET_DDL 関数を呼び出して、ストアドプロシージャの DDL を取得する機能。

  • ALTER USER ステートメントを実行する機能。

  • 実行時のストアドプロシージャの監視。

  • SHOW および DESCRIBE コマンド。

  • ストアドプロシージャ内から呼び出すことができる SQL ステートメントのタイプ。

次のセクションでは、これらの制限について詳しく説明します。

注釈

所有者権限ストアドプロシージャに関するほとんどの制限は、所有者を 含む すべての呼び出し元に適用されます。

GET_DDL およびストアドプロシージャ

ストアドプロシージャが所有者権限ストアドプロシージャとして作成されている場合、呼び出し元(所有者以外)は GET_DDL( <プロシージャ名>, ... ) の呼び出しでストアドプロシージャの本体を表示することはできません。

これにより、ストアドプロシージャの作成者は、ストアドプロシージャのソースコード内の機密情報を保護できます。

ALTER USER

次の制限は、所有者権限ストアドプロシージャの ALTER USER ステートメントに適用されます。

  • 所有者権限ストアドプロシージャは、セッションで現在のユーザーを 暗黙的 に使用する ALTER USER ステートメントを実行できません。(しかし、所有者権限ストアドプロシージャは、ユーザーが現在のユーザーでない限り、ユーザーを 明示的 に識別する ALTER USER ステートメントを実行できます。)

実行時のストアドプロシージャのモニター

所有者権限ストアドプロシージャの所有者にも呼び出し元にも、ストアドプロシージャの実行をモニターする権限があるとは限りません。

WAREHOUSE MONITOR 権限を持つユーザーは、そのストアドプロシージャ内の個々のウェアハウス関連の SQL ステートメントの実行を監視できます。ほとんどのクエリと DML ステートメントは、ウェアハウス関連のステートメントです。 CREATE、 ALTER などの DDL ステートメントはウェアハウスを使用しないため、ストアドプロシージャのモニターの一部としてはモニターできません。

SHOW および DESCRIBE コマンド

所有者権限ストアドプロシージャには、呼び出し元以外のユーザーに関する情報を読み取るための十分な権限がありません。たとえば、 SHOW USERS LIKE <現在のユーザー> を実行すると現在のユーザーに関する情報が表示されますが、現在のユーザーが唯一のユーザーでない限り、より一般的な SHOW USERS は機能しません。

次の SHOW コマンドが 許可されています

  • SHOW DATABASES

  • SHOW SCHEMAS

  • SHOW WAREHOUSES

SQL ステートメントの制限

呼び出し元の権限のストアドプロシージャは、ストアドプロシージャの外部で実行するために十分な権限を持っている SQL ステートメントを実行できますが、所有者の権限のストアドプロシージャは SQL ステートメントのサブセットのみを呼び出すことができます。

次の SQL ステートメントは、所有者の権限のストアドプロシージャ内から呼び出すことが できます

  • SELECT

  • DML

  • DDL(ALTER USER ステートメントの制限については上記を参照。)

  • GRANT/REVOKE

  • 変数の割り当て。

  • DESCRIBE および SHOW。(上記の制限を参照。)

他の SQL ステートメントは、所有者の権限のストアドプロシージャ内から呼び出すことはできません。

異なる権限を持つネストされたストアドプロシージャ

所有者権限ストアドプロシージャが呼び出し元権限ストアドプロシージャによって呼び出された場合、またはその逆の場合は、次の規則が適用されます。

  • ストアドプロシージャとその上の呼び出し階層全体が呼び出し元権限ストアドプロシージャである場合に限り、ストアドプロシージャは呼び出し元権限ストアドプロシージャとして動作します。

  • 所有者権限ストアドプロシージャは、どこから呼び出された場合でも、常に所有者権限ストアドプロシージャとして動作します。

  • 所有者権限ストアドプロシージャから直接または間接的に呼び出されるストアドプロシージャは、所有者権限ストアドプロシージャとして動作します。

所有者権限と呼び出し元権限の選択

次の すべて が当てはまる場合、所有者権限ストアドプロシージャとしてストアドプロシージャを作成します。

  • 呼び出し元権限ではなく、所有者権限で実行する別のユーザーにタスクを委任する場合。たとえば、テーブルに対する DELETE 権限のないユーザーが、現在のデータではなく古いデータを削除するストアドプロシージャを呼び出せるようにする場合は、所有者権限ストアドプロシージャを使用できます。そのプロシージャには、フィルターを介して削除できるデータを制御するフィルター( WHERE 句)を含む DELETE ステートメントが含まれます。

  • 所有者権限ストアドプロシージャの制限は、ストアドプロシージャの正常な動作を妨げません。

次の条件に該当する場合、呼び出し元権限ストアドプロシージャとしてストアドプロシージャを作成します。

  • ストアドプロシージャは、呼び出し元が所有するオブジェクトまたは必要な権限を持っているオブジェクトに対してのみ動作します。

  • 所有者権限ストアドプロシージャの制限により、ストアドプロシージャが機能しなくなります。たとえば、ストアドプロシージャの呼び出し元がその呼び出し元の環境(セッション変数やアカウントパラメーターなど)を使用する必要がある場合、呼び出し元権限プロシージャを使用します。

特定の手順が発信者の権限または所有者の権限のいずれかで正しく機能する場合、次のルールは使用する権限を選択するのに役立ちます。

  • プロシージャが呼び出し元権限プロシージャである場合、呼び出し元には、ストアドプロシージャ内のコードを表示する権限がありません(呼び出し元が所有者でもある場合を除く)。呼び出し元がプロシージャのソースコードを表示できないようにする場合は、所有者権限プロシージャとしてプロシージャを作成します。逆に、呼び出し元にソースコードを読み取らせたい場合は、呼び出し元の権限手続きとしてプロシージャを作成します。