Übergegen von Referenzen auf Tabellen, Ansichten, Funktionen und Abfragen an gespeicherte Prozeduren¶

Ein einfaches Beispiel¶

Angenommen, eine gespeicherte Prozedur mit Eigentümerrechten soll Zeilen in eine Tabelle einfügen, die über ein Eingabeargument angegeben wird. Im Folgenden finden Sie ein Beispiel, das in Snowflake Scripting geschrieben wurde:

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

Im Folgenden finden Sie ein ähnliches Beispiel, das in JavaScript geschrieben wurde:

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

Angenommen, dass Sie diese Prozedur für eine Tabelle aufrufen müssen, die einer anderen Rolle gehört:

USE ROLE table_owner;

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

Wenn Sie die gespeicherte Prozedur aufrufen und den Namen der Tabelle übergeben, schlägt die gespeicherte Prozedur fehl, weil der Eigentümer der gespeicherten Prozedur nicht über erforderlichen Berechtigungen für den Zugriff auf die Tabelle verfügt:

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.

Damit die gespeicherte Prozedur SQL-Aktionen auf der Tabelle als Aufrufer ausführen kann, erstellen Sie eine Referenz auf die Tabelle und übergeben diese Referenz anstelle des Tabellennamens.

Zum Erstellen der Referenz rufen Sie die Funktion SYSTEM$REFERENCE auf. Beispiel:

USE ROLE table_owner;

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

Das obige Beispiel übergibt die folgenden Argumente an die Funktion SYSTEM$REFERENCE:

• 'TABLE' für den Typ des Objekts.

• 'table_with_different_owner' für den Namen der Tabelle.

• 'SESSION' um anzugeben, dass der Geltungsbereich der Referenz auf die Sitzung beschränkt sein soll.

• 'INSERT' als die Berechtigung, die für das Ausführen der Aktion auf dem Objekt erforderlich ist.

Angeben des Geltungsbereichs der Referenz¶

Die Referenz ist entweder für die Dauer des Aufrufs, in dem die Referenz übergeben wird, oder für die Dauer der Sitzung gültig. Der Kontext, in dem die Referenz erstellt wird, bestimmt den Geltungsbereich:

• Wenn Sie mit einer einzigen Anweisung eine Referenz erstellen und an eine gespeicherte Prozedur übergeben, hat die Referenz die gleiche Sichtbarkeit wie eine Variable, die im äußersten Block der gespeicherten Prozedur deklariert wird:

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

  Copy

• Wenn Sie eine Referenz erstellen und die Referenz einer Sitzungsvariablen zuweisen, ist die Referenz für die Dauer der Sitzung gültig, auch wenn Sie die Sitzungsvariable zurücksetzen:

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

  Copy

Um festzulegen, dass der Geltungsbereich der Referenz die Dauer der Sitzung sein soll, unabhängig von dem Kontext, in dem die Referenz erstellt wird, übergeben Sie der Funktion SYSTEM$REFERENCE den Wert 'SESSION' als drittes Argument (session_scope):

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

Erteilen zusätzlicher Berechtigungen in einer Referenz¶

Standardmäßig erteilt eine Referenz eine Teilmenge von Berechtigungen, die auf dem Typ des referenzierten Objekts basieren. Beispielsweise wird durch eine Referenz auf eine Tabelle der aktiven Rolle oder der Sekundärrolle des Benutzers, die den Verweis erstellt hat, die SELECT-Berechtigung für die Tabelle erteilt. Die Standardberechtigungen hängen vom Objekttyp ab. Eine Liste der unterstützten Objekte, Berechtigungen und Standardberechtigungen finden Sie unter Unterstützte Objekttypen und Berechtigungen für Referenzen.

Um zusätzliche Berechtigungen zu erteilen, übergeben Sie diese Berechtigungen als zusätzliche Argumente an die Funktion SYSTEM$REFERENCE. So erteilen Sie beispielsweise die Berechtigungen INSERT, UPDATE und TRUNCATE für eine Tabelle:

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

Beachten Sie, dass Sie OWNERSHIP oder ALL nicht als Berechtigungen angeben können.

Nachdem eine Referenz erstellt wurde, spiegeln sich Änderungen an den Berechtigungen des Erstellers der Referenz in den der Referenz zugeordneten Berechtigungen wider. Wenn beispielsweise die Zuweisung der Berechtigung INSERT für den Ersteller einer Referenz widerrufen wird, ist die Berechtigung INSERT nicht länger der Referenz zugeordnet.

Verwenden von Referenzen auf Tabellen und Ansichten mit Maskierungsrichtlinien¶

Wenn Sie die Referenz auf eine Tabelle oder Ansicht verwenden, für die eine Maskierungsrichtlinie gilt, ist die Referenzrolle die Aufruferrolle (die von INVOKER_ROLE zurückgegebene Rolle), unabhängig davon, ob die Referenz in einer Abfrage, gespeicherten Prozedur oder benutzerdefinierten Funktion verwendet wird.

Das Verwenden einer Referenz ändert nicht die aktuelle Rolle (die von CURRENT_ROLE zurückgegebene Rolle).

Erstellen von Referenzen in gespeicherten Prozeduren¶

Wenn Sie eine gespeicherte Prozedur mit Eigentümerrechten schreiben, erstellen Sie keine Referenz innerhalb des Textkörpers (Body) der gespeicherten Prozedur.

Eine Referenz, die in einer gespeicherten Prozedur mit Eigentümerrechten erstellt wird, verwendet die Rolle des Eigentümers der gespeicherten Prozedur. Referenzen müssen die Rolle des Benutzers verwenden, der die gespeicherte Prozedur aufruft. Für eine gespeicherte Prozedur mit Eigentümerrechten muss der Benutzer, der die gespeicherte Prozedur aufruft, die Referenz erstellen und diese an die gespeicherte Prozedur übergeben.

Wenn Sie eine gespeicherte Prozedur mit Aufruferrechten schreiben, können Sie eine Referenz im Textkörper (Body) der gespeicherten Prozedur erstellen.

Verwenden von Abfragereferenzen¶

Wenn Sie eine Abfrage übergeben müssen, die in einer gespeicherten Prozedur in der FROM-Klausel einer SELECT-Anweisung verwendet wird, erstellen Sie eine Abfragereferenz und übergeben diese Referenz.

Angenommen, eine gespeicherte Prozedur übergibt eine SELECT-Anweisung, die in der FROM-Klausel einer anderen SELECT-Anweisung verwendet werden soll. In dem folgenden Beispiel soll als Abfrageargument eine SELECT-Anweisung verwendet werden. Dieses Beispiel ist in Snowflake Scripting geschrieben:

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;

Im Folgenden finden Sie ein ähnliches Beispiel, das in JavaScript geschrieben wurde:

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

Die gespeicherte Prozedur verwendet Eigentümerrechte. Wenn der Eigentümer der gespeicherten Prozedur nicht die Berechtigung hat, die Tabelle in der SELECT-Anweisung abzufragen, schlägt der Aufruf der gespeicherten Prozedur fehl.

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.

Damit die gespeicherte Prozedur die Abfrage als Aufrufer ausführen kann, erstellen Sie eine Abfragereferenz für die SELECT-Anweisung, und übergeben Sie dann diese Referenz anstelle der SELECT-Anweisung.

Um die Referenz zu erstellen, rufen Sie die Funktion SYSTEM$QUERY_REFERENCE auf, und geben Sie dabei die folgenden Argumente an:

• 'SELECT x FROM table_with_different_owner' als Abfrage.

  Wenn die SELECT-Anweisung einfache Anführungszeichen oder andere Sonderzeichen (z. B. Zeilenumbrüche) enthält, müssen Sie diese Zeichen mit Backslashes maskieren.

• true um anzugeben, dass der Geltungsbereich der Abfragereferenz auf die Sitzung beschränkt sein soll.

Beispiel:

USE ROLE table_owner;

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

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

Innerhalb der gespeicherten Prozedur können Sie eine Abfragereferenz zur FROM-Klausel einer Abfrage hinzufügen. Beispiel:

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

Weitere Informationen zu dieser Funktion finden Sie unter SYSTEM$QUERY_REFERENCE.

Weitere Informationen zu den Einschränkungen beim Erstellen und Verwenden von Abfragereferenzen finden Sie unter Aktuelle Einschränkungen.

Aktuelle Einschränkungen¶

Derzeit haben Referenzen die folgenden Einschränkungen:

• GET_DDL und SYSTEM$GET_TAG unterstützen keine Referenzen als Eingabeargumente.

• Sie können nur Referenzen auf Tabellen, Ansichten und Funktionen erstellen.

• Bei Abfragen, die Referenzen enthalten, werden Plan-Cache und Ergebnis-Caching nicht verwendet.

• Bei Abfragereferenzen:

  • Sie können nur Abfragereferenzen für SELECT-Anweisungen erstellen, die als Inline-Ansichten dienen.

  • Wenn Sie eine Abfragereferenz erstellen, können Sie keine Bindungs- oder Sitzungsvariable angeben.

  • In Ihrer gespeicherten Prozedur können Sie eine Abfragereferenz nur in der FROM-Klausel einer SELECT-Anweisung verwenden.