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

簡単な例¶

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

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

以下は JavaScript で記述された同様の例です。

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スクリプトにあります。

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;

以下は JavaScript で記述された同様の例です。

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 関数を呼び出します。

• クエリとして '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 をご参照ください。

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

現在の制限¶

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

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

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

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

• クエリ参照の場合、

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

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

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