Passing references for objects and queries to stored procedures¶

In cases in which you call a stored procedure and pass an identifier for a table, view, function, or procedure to a stored procedure, you might need to:

ストアドプロシージャが所有者権限を使用している場合でも、ストアドプロシージャがオブジェクトに対して SQL アクションを実行できるようにします。
識別子が修飾されていない場合や部分的に修飾されている場合に、ストアドプロシージャがオブジェクトの完全修飾名を解決できるようにする。

In these cases, you can create and pass in a reference to the object (for example, the table, view, function, or procedure). A reference is a unique identifier for an object. Within the stored procedure, when you execute SQL actions on a reference to an object, the actions are performed using the active role or secondary roles of the user who created the reference. In addition, if the object identifier is not fully qualified, the name of the object is resolved by using the current database and schema when the reference was created (in other words, the database and schema of the user who created the reference).

Similarly, if you need to pass in a query to a stored procedure and use that query in the FROM clause of a SELECT statement, you can create and pass in a query reference. Within the stored procedure, the query is performed using the active role or secondary roles of the user who created the query reference. As is the case with references to objects, if the object name in the query is not fully qualified, the name of the object is resolved by using the database and schema that were in use when the query reference was created.

このトピックでは、参照の作成方法と使用方法について説明します。

Background: The problem with passing objects and queries to stored procedures¶

所有者権利のストアドプロシージャが、入力引数で指定されたテーブルに行を挿入するように設計されているとします。以下は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;
$$;

Copy

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);
$$;

Copy

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

USE ROLE table_owner;

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

Copy

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

USE ROLE table_owner;

CALL insert_row('table_with_different_owner');

Copy

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'));

Copy

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

オブジェクトの型に 'TABLE'。
テーブルの名前に 'table_with_different_owner'。
参照はセッションにスコープされる必要があることを指示する 'SESSION'。
オブジェクトに対してアクションを実行するために必要な権限として 'INSERT'。

注釈

変更する予定のないオブジェクトへの参照を作成する必要があり（たとえば、ストアドプロシージャがクエリするテーブルを渡す場合など）、その参照を（セッション全体ではなく）呼び出しのスコープに対して有効にしたい場合は、 SYSTEM$REFERENCE を呼び出す代わりに TABLE キーワードを使用できます。詳細については、 TABLE キーワードを使用して、テーブル、ビュー、またはクエリへの参照を作成します。をご参照ください。

参照のスコープの指定¶

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

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

Copy

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

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

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

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

Copy

権限として 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;

Copy

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);
$$;

Copy

ストアドプロシージャは所有者権限を使用します。ストアドプロシージャの所有者が 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');

Copy

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 を呼び出す代わりに、TABLE キーワードを使用できます。詳細については、 TABLE キーワードを使用して、テーブル、ビュー、またはクエリへの参照を作成します。をご参照ください。

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)
);

Copy

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

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

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

Copy

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

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

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

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

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

Copy

TABLE("<object_name_that_requires_double_quotes>")

Copy

TABLE(IDENTIFIER('string_literal_for_object_name'))

Copy

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

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

CALL my_procedure(TABLE(my_table));

Copy

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

Copy

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

Copy

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

Copy

注釈

You cannot use the TABLE keyword with the name of a function or procedure.

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

TABLE(<select_statement>)

Copy

例:

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

Copy

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));

Copy

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

オブジェクト名やクエリにバインド変数を使用することはできません。
TABLE キーワードで作成された参照は、呼び出しの間有効です。参照に別のスコープを指定することはできません。
参照にはオブジェクトのタイプに付与されるデフォルト権限があります。

現在の制限¶

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

GET_DDL と SYSTEM$GET_TAG は、入力引数として参照をサポートしていません。
You can only create references to tables, views, functions, and procedures.
参照を含むクエリでは、プランキャッシュと結果キャッシュは使用されません。
クエリ参照の場合、
- インラインビューとして機能する SELECT ステートメントのクエリ参照のみを作成できます。
- クエリ参照を作成するときに、バインド変数やセッション変数を指定することはできません。
- ストアドプロシージャでは、 SELECT ステートメントの FROM 句でのみクエリ参照を使用できます。