ストアドプロシージャへのテーブル、ビュー、関数、およびクエリの参照の受け渡し¶

背景: テーブル、ビュー、クエリをストアドプロシージャに渡す際の問題¶

所有者権利のストアドプロシージャが、入力引数で指定されたテーブルに行を挿入するように設計されているとします。以下はSnowflake Scriptingと JavaScript で書かれた例です。

USE ROLE stored_proc_owner;

CREATE OR REPLACE PROCEDURE insert_row(table_identifier VARCHAR)
RETURNS TABLE()
LANGUAGE SQL
AS
$$
BEGIN
  LET stmt VARCHAR := 'INSERT INTO ' || table_identifier || ' VALUES (10)';
  LET res RESULTSET := (EXECUTE IMMEDIATE stmt);
  RETURN TABLE(res);
END;
$$;

USE ROLE stored_proc_owner;

CREATE OR REPLACE PROCEDURE insert_row(table_identifier VARCHAR)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS
$$
  let res = snowflake.execute({
    sqlText: "INSERT INTO IDENTIFIER(?) VALUES (10);",
    binds : [TABLE_IDENTIFIER]
  });
  res.next()
  return res.getColumnValue(1);
$$;

別のロールが所有するテーブルに対してこのプロシージャを呼び出す必要があるとします。

USE ROLE table_owner;

CREATE OR REPLACE TABLE table_with_different_owner (x NUMBER) AS SELECT 42;

ストアドプロシージャを呼び出してテーブル名を渡すと、ストアドプロシージャの所有者はテーブルにアクセスする十分な権限を持っていないため、ストアドプロシージャは失敗します。

USE ROLE table_owner;

CALL insert_row('table_with_different_owner');

002003 (42S02): Uncaught exception of type 'STATEMENT_ERROR' on line 4 at position 25 : SQL compilation error:
Table 'TABLE_WITH_DIFFERENT_OWNER' does not exist or not authorized.

ストアドプロシージャが呼び出し元としてテーブルに対して SQL アクションを実行できるようにするには、テーブル名ではなくテーブルへの 参照を作成 し、その参照を渡します。

参照の作成¶

参照を作成するには、 SYSTEM$REFERENCE 関数を呼び出します。例:

USE ROLE table_owner;

CALL insert_row(SYSTEM$REFERENCE('TABLE', 'table_with_different_owner', 'SESSION', 'INSERT'));

上記の例では、 SYSTEM$ REFERENCE 関数に以下の引数を渡しています。

• オブジェクトの型に 'TABLE'。

• テーブルの名前に 'table_with_different_owner'。

• 参照は セッションにスコープされる 必要があることを指示する 'SESSION'。

• オブジェクトに対してアクションを実行するために必要な権限 として 'INSERT'。

参照のスコープの指定¶

参照は、参照が渡された呼び出しの継続時間、またはセッションの継続時間のいずれかで有効です。参照が作成されるコンテキストによってスコープが決定されます。

• 単一のステートメントでストアドプロシージャへの参照を作成して渡す場合、その参照はストアドプロシージャの一番外側のブロックで宣言された変数と同じ可視性を持ちます。

  CALL select_from_table(SYSTEM$REFERENCE('TABLE', 'my_table');
  

  Copy

• 参照を作成し、その参照を セッション変数 に割り当てると、セッション変数の設定を解除しても、その参照はセッションの間有効です。

  SET tableRef = (SELECT SYSTEM$REFERENCE('TABLE', 'my_table'));
  
  SELECT * FROM IDENTIFIER($tableRef);
  

  Copy

参照が作成されたコンテキストに関係なく、参照のスコープをセッションの継続時間に指定するには、 SYSTEM$REFERENCE 関数の第3引数 （session_scope）に 'SESSION' を渡します。

CALL insert_row(SYSTEM$REFERENCE('TABLE', 'table_with_different_owner', 'SESSION', 'INSERT'));

参照での追加権限の付与¶

デフォルトでは、参照は、参照されるオブジェクトの型に基づいて、権限のサブセットを付与します。たとえば、テーブルへの参照は、その参照を作成したユーザーのアクティブロールまたはセカンダリロールに対して、そのテーブルの SELECT 権限を付与します。デフォルトの権限は、オブジェクト型によって異なります。サポートされるオブジェクト、権限、デフォルト権限のリストについては、 参照のサポートされるオブジェクト型と権限 をご参照ください。

追加の権限を付与するには、 SYSTEM$REFERENCE 関数の追加引数としてその権限を指定します。たとえば、テーブルに対する INSERT、 UPDATE、 TRUNCATE の権限を付与する場合は次のようにします。

SELECT SYSTEM$REFERENCE('TABLE', 'table_with_different_owner', 'SESSION', 'INSERT', 'UPDATE', 'TRUNCATE');

権限として OWNERSHIP や ALL は指定できないことに注意してください。

参照が作成された後、参照の作成者の権限に対する変更は、参照に関連付けられた権限に反映されます。たとえば、参照の作成者の INSERT 権限が取り消された場合、 INSERT 権限はその参照に関連付けられなくなります。

マスキングポリシーのあるテーブルおよびビューへの参照の使用¶

マスキングポリシーを持つテーブルやビューへの参照を使用する場合は、参照がクエリ、ストアドプロシージャ、またはユーザー義関数のいずれで使用されるかに関係なく、参照ロールは呼び出し元ロール（INVOKER_ROLE によって返されるロール）になります。

参照を使用しても、現在のロール（CURRENT_ROLE が返すロール）は変更されません。

ストアドプロシージャでの参照の作成¶

所有者権利のストアドプロシージャ を記述する場合は、ストアドプロシージャ本文内に参照を作成 しない でください。

所有者権限のストアドプロシージャで作成された参照は、ストアドプロシージャの所有者ロールを使用します。参照は、ストアドプロシージャを呼び出したユーザーのロールを使用する必要があります。所有者権限のストアドプロシージャでは、ストアドプロシージャを呼び出すユーザーが参照を作成し、ストアドプロシージャに渡す必要があります。

呼び出し元権限のストアドプロシージャを記述している場合は、ストアドプロシージャの本文内で参照を作成することができます。

クエリ参照の使用¶

ストアドプロシージャにある SELECT ステートメントの FROM 句で使用するクエリを渡す必要がある場合は、クエリ参照を作成して渡します。

たとえば、ストアドプロシージャが、別の SELECT ステートメントの FROM 句で使用することを想定している SELECT ステートメントを渡すとします。以下の例では、クエリ引数は SELECT ステートメントを想定しています。この例はSnowflakeスクリプトと JavaScript によるものです。

USE ROLE stored_proc_owner;

CREATE OR REPLACE PROCEDURE get_num_results(query VARCHAR)
  RETURNS INTEGER
  LANGUAGE SQL
  AS
  DECLARE
    row_count INTEGER DEFAULT 0;
    stmt VARCHAR DEFAULT 'SELECT COUNT(*) FROM (' || query || ')';
    res RESULTSET DEFAULT (EXECUTE IMMEDIATE :stmt);
    cur CURSOR FOR res;
  BEGIN
    OPEN cur;
    FETCH cur INTO row_count;
    RETURN row_count;
  END;

USE ROLE stored_proc_owner;

CREATE OR REPLACE PROCEDURE get_num_results(query VARCHAR)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS
$$
  let res = snowflake.execute({
    sqlText: "SELECT COUNT(*) FROM (" + QUERY + ");",
  });
  res.next()
  return res.getColumnValue(1);
$$;

ストアドプロシージャは所有者権限を使用します。ストアドプロシージャの所有者が SELECT ステートメント内のテーブルにクエリを実行する権限を持っていない場合は、ストアドプロシージャの呼び出しに失敗します。

USE ROLE table_owner;
CREATE OR REPLACE TABLE table_with_different_owner (x NUMBER) AS SELECT 42;

CALL get_num_results('SELECT x FROM table_with_different_owner');

002003 (42S02): Uncaught exception of type 'STATEMENT_ERROR' on line 4 at position 29 : SQL compilation error:
Object 'TABLE_WITH_DIFFERENT_OWNER' does not exist or not authorized.

ストアドプロシージャが呼び出し元としてクエリを実行できるようにするには、 SELECT ステートメントではなく、 SELECT ステートメントのクエリ参照を作成し、その参照を渡します。

クエリ参照を作成するために、 SYSTEM$QUERY_REFERENCE 関数を呼び出すことができます。

SYSTEM$QUERY_REFERENCE 関数を呼び出す場合は、次のように渡します。

• クエリとして 'SELECT x FROM table_with_different_owner'。

  SELECT ステートメントに一重引用符やその他の特殊文字（例: 改行）が含まれている場合は、 それらの文字をバックスラッシュでエスケープ する必要があることに注意してください。

• クエリ参照はセッションにスコープされる必要があることを指示する true。

例:

USE ROLE table_owner;

CALL get_num_results(
  SYSTEM$QUERY_REFERENCE('SELECT x FROM table_with_different_owner', true)
);

+-----------------+
| GET_NUM_RESULTS |
|-----------------|
|               1 |
+-----------------+

ストアドプロシージャ内で、クエリの FROM 句にクエリ参照を追加できます。例:

snowflake.execute({
  sqlText: "SELECT COUNT(*) FROM (" + QUERY + ");"
});

この関数の詳細については、 SYSTEM$QUERY_REFERENCE をご参照ください。

クエリ参照の作成と使用に関する制限については、 現在の制限 をご参照ください。

TABLE キーワードを使用して、テーブル、ビュー、またはクエリへの参照を作成します。¶

ストアドプロシージャがクエリするテーブル、ビュー、または変更しないセキュアビューへの参照を作成する必要があり、その参照を（セッション全体ではなく）呼び出しの範囲内で有効にしたい場合は、以下の構文で TABLE キーワードを使用します。

TABLE( [[<database_name>.]<schema_name>.]<object_name> )

TABLE("<object_name_that_requires_double_quotes>")

TABLE(IDENTIFIER('string_literal_for_object_name'))

TABLE キーワードは、オブジェクト型の引数を指定することなく、テーブルまたはビューの SYSTEM$REFERENCE 関数を呼び出すための、より単純な構文を提供します。TABLE キーワードを使用する場合、参照は単に SELECT 権限を与えるだけであり、参照のスコープは呼び出しです（セッションではありません）。

以下の例では、ストアドプロシージャ my_procedure を呼び出し、テーブルとビューへの参照を渡しています。

CALL my_procedure(TABLE(my_table));

CALL my_procedure(TABLE(my_database.my_schema.my_view));

CALL my_procedure(TABLE("My Table Name"));

CALL my_procedure(TABLE(IDENTIFIER('my_view')));

クエリへの参照を作成したい場合で、参照が（セッション全体ではなく）呼び出しの範囲でのみ有効である必要がある場合、SYSTEM$QUERY_REFERENCE 関数を呼び出す代わりに TABLE キーワードを使用できます。TABLE キーワードを使用するには、以下の構文を使用します。

TABLE(<select_statement>)

例:

CALL my_procedure(TABLE(SELECT * FROM my_view));

CALL my_procedure(TABLE(WITH c(s) as (SELECT $1 FROM VALUES (1), (2)) SELECT a, count(*) FROM T, C WHERE s = a GROUP BY a));

次の点に注意してください。

• オブジェクト名やクエリにバインド変数を使用することはできません。

• TABLE キーワードで作成された参照は、呼び出しの間有効です。参照に別のスコープを指定することはできません。

• 参照には オブジェクトのタイプに付与されるデフォルト権限 があります。

現在の制限¶

現在、参照には以下の制限があります。

• GET_DDL と SYSTEM$GET_TAG は、入力引数として参照をサポートしていません。

• 作成できるのは、テーブル、ビュー、関数への参照のみです。

• 参照を含むクエリでは、プランキャッシュと結果キャッシュは使用されません。

• クエリ参照の場合、

  • インラインビューとして機能する SELECT ステートメントのクエリ参照のみを作成できます。

  • クエリ参照を作成するときに、バインド変数やセッション変数を指定することはできません。

  • ストアドプロシージャでは、 SELECT ステートメントの FROM 句でのみクエリ参照を使用できます。