Erläuterungen zu gespeicherten Prozeduren mit Aufruferrechten und Eigentümerrechten¶

Einführung¶

Eine gespeicherte Prozedur mit Aufruferrechten mit den Berechtigungen des Aufrufers ausgeführt. Der Hauptvorteil einer gespeicherten Prozedur mit Aufruferrrechten besteht darin, dass diese auf Informationen über diesen Aufrufer oder über die aktuelle Sitzung des Aufrufers zugreifen kann. Beispielsweise kann eine gespeicherte Prozedur mit Aufruferrechen die Sitzungsvariablen des Aufrufers lesen und in einer Abfrage verwenden.

Eine gespeicherte Prozedur mit Eigentümerrechen wird hauptsächlich mit den Berechtigungen des Eigentümers der gespeicherten Prozedur ausgeführt. Der Hauptvorteil einer gespeicherten Prozedur mit Eigentümerrechen besteht darin, dass der Eigentümer bestimmte Verwaltungsaufgaben, z. B. das Bereinigen alter Daten, an eine andere Rolle delegieren kann, ohne dieser Rolle allgemeinere Berechtigungen zu erteilen, z. B. Berechtigungen zum Löschen aller Daten aus einer bestimmten Tabelle.

Zum Zeitpunkt der Erstellung der gespeicherten Prozedur gibt der Ersteller an, ob die Prozedur mit Eigentümerrechen oder mit Aufruferrechten ausgeführt wird. Die Standardeinstellung ist eine Prozedur mit Eigentümerrechten.

Der Eigentümer der Prozedur kann mithilfe des Befehls ALTER PROCEDURE die gespeicherte Prozedur mit Eigentümerrechten in eine gespeicherte Prozedur mit Aufruferrechten (oder umgekehrt) ändern.

Berechtigungen für Datenbankobjekte¶

Eine gespeicherte Prozedur mit Aufruferrechten wird mit den Datenbankberechtigungen der Rolle ausgeführt, die die gespeicherte Prozedur aufgerufen hat. Anweisungen, die der Aufrufer außerhalb der gespeicherten Prozedur nicht ausführen konnte, können auch nicht innerhalb der gespeicherten Prozedur ausgeführt werden. Wenn beispielsweise die Rolle „Pflegekraft“ keine Berechtigungen zum Löschen von Zeilen aus der Tabelle medical_records hat, und dann aber ein Benutzer mit der Rolle „Pflegekraft“ eine gespeicherte Prozedur mit Aufruferrechten aufruft, in der Zeilen aus dieser Tabelle gelöscht werden sollen, dann schlägt diese gespeicherte Prozedur fehl.

Eine Prozedur mit Eigentümerrechten wird mit den Berechtigungen des Eigentümers der Prozedur ausgeführt. Wenn der Eigentümer über die Berechtigungen zum Ausführen einer Aufgabe verfügt, kann die gespeicherte Prozedur diese Aufgabe auch dann ausführen, wenn sie von einer Rolle aufgerufen wird, die nicht über die Berechtigungen zum direkten Ausführen dieser Aufgabe verfügt. Beispiel: Die Rolle mit dem Namen „Arzt“ verfügt über die Datenbankberechtigungen zum Löschen von Zeilen aus der Tabelle medical_records. Wenn diese Rolle „Arzt“ eine gespeicherte Prozedur erstellt, mit der Zeilen aus dieser Tabelle gelöscht werden, die älter als 7 Jahre sind, und dann die Rolle „Arzt“ der Rolle „Pflegekraft“ die entsprechenden Berechtigungen für die gespeicherte Prozedur gewährt, dann kann die Rolle „Pflegekraft“ die gespeicherte Prozedur ausführen (und alte Zeilen über diese gespeicherte Prozedur aus der Tabelle löschen), selbst wenn die Rolle „Pflegekraft“ keine Löschberechtigung für diese Tabelle hat.

Zugriff auf und Einstellung des Sitzungsstatus¶

Wie auch bei anderen SQL-Anweisungen wird eine CALL-Anweisung innerhalb einer Sitzung ausgeführt und erbt den Kontext dieser Sitzung, z. B. Variablen auf Sitzungsebene, aktuelle Datenbank usw. Der genaue Kontext, den die Prozedur erbt, hängt davon ab, ob es sich bei der gespeicherten Prozedur um eine gespeicherte Prozedur mit Aufruferrechten oder eine gespeicherte Prozedur mit Eigentümerrechten handelt.

Wenn eine gespeicherte Prozedur mit Aufruferrechten Änderungen an der Sitzung vornimmt, können diese Änderungen nach dem Ende von CALL bestehen bleiben. Gespeicherte Prozeduren mit Eigentümerrechten dürfen den Sitzungsstatus nicht ändern.

Gespeicherte Prozeduren mit Aufruferrechten¶

Gespeicherte Prozeduren mit Aufruferrechten halten sich in einer Sitzung an die folgenden Regeln:

• Sie werden mit den Berechtigungen des Aufrufers ausgeführt, nicht mit den Berechtigungen des Eigentümers.

• Sie übernehmen das aktuelle Warehouse des Aufrufers.

• Sie verwenden die Datenbank und das Schema, die der Aufrufer gerade verwendet.

• Kann Sitzungsvariablen des Aufrufers anzeigen, festlegen und aufheben.

• Kann Sitzungsparameter des Aufrufers anzeigen, festlegen und aufheben.

Der Abschnitt unten enthält weitere Details dazu, wie gespeicherte Prozeduren mit Aufruferrechten Variablen des Aufrufers auf Sitzungsebene lesen und schreiben können.

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

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

SET SESSION_VAR1 = 'some interesting value';
READ SESSION_VAR1;
SET SESSION_VAR2;
SELECT *
  FROM table1
  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
  EXECUTE AS CALLER
  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;

Gespeicherte Prozeduren mit Eigentümerrechten¶

Gespeicherte Prozeduren mit Eigentümerrechten unterliegen in einer Sitzung den folgenden Regeln:

• Sie werden mit den Berechtigungen des Eigentümers ausgeführt, nicht mit den Berechtigungen des Aufrufers.

  Tipp

  Wenn Sie eine gespeicherte Prozedur mit Eigentümerrechten benötigen, um Aktionen auf einer Tabelle, einer Ansicht oder einer Funktion auszuführen, für die der Aufrufer die Zugriffsberechtigung hat, können Sie den Aufrufer eine Referenz auf diese Tabelle, Ansicht oder Funktion übergeben lassen.

  Weitere Informationen dazu finden Sie unter Übergegen von Referenzen auf Tabellen, Ansichten, Funktionen und Abfragen an gespeicherte Prozeduren.

• Sie übernehmen das aktuelle Warehouse des Aufrufers.

• Sie verwenden die Datenbank und das Schema, in denen die gespeicherte Prozedur erstellt wurde, nicht die Datenbank und das Schema, die der Aufrufer aktuell verwendet.

• Auf die meisten Aufrufer-spezifischen Informationen kann nicht zugegriffen werden. Beispiel:

  • Sitzungsvariablen des Aufrufers können nicht angezeigt, festgelegt oder aufgehoben werden.

  • Auflisten der Parameter mit SHOW PARAMETERS kann nicht ausgeführt werden.

  • Es kann nur eine Teilmenge der vom Aufrufer festgelegten Sitzungsparameter verwendet werden. So kann beispielsweise in SQL-Befehlen, die Datumswerte ausgeben, der DATE_OUTPUT_FORMAT-Parameter verwendet werden, der für die Sitzung des Aufrufers festgelegt ist.

    Die Liste dieser Parameter finden Sie unter Erläuterungen zu den Auswirkungen der Sitzungsparameter eines Anrufers auf eine Prozedur mit Eigentümerrechten.

  • Sitzungsparameter des Aufrufers können weder festgelegt noch aufgehoben werden.

  • INFORMATION_SCHEMA-Tabellenfunktionen (z. B. AUTOMATIC_CLUSTERING_HISTORY), die Ergebnisse basierend auf dem aktuellen Benutzer zurückgeben, können nicht abgefragt werden.

• Nicht-Eigentümern werden keine Informationen über die Prozedur aus der Ansicht PROCEDURES angezeigt.

Einschränkungen für Sitzungsvariablen und Sitzungsparameter werden nachstehend ausführlicher beschrieben.

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

Entfernen einiger Einschränkungen bei gespeicherten Prozeduren mit Eigentümerrechten¶

Für gespeicherte Prozeduren mit Eigentümerrechten gelten neben den Einschränkungen für Sitzungsvariablen und Sitzungsparameter mehrere zusätzliche Einschränkungen. Diese Einschränkungen beziehen sich auf Folgendes:

• Die integrierten Funktionen, die innerhalb einer gespeicherten Prozedur aufgerufen werden können.

• Möglichkeit, ALTER USER-Anweisungen auszuführen.

• Überwachen gespeicherter Prozeduren zum Zeitpunkt der Ausführung.

• Befehle SHOW und DESCRIBE.

• Die Arten von SQL-Anweisungen, die innerhalb einer gespeicherten Prozedur aufgerufen werden können.

In den folgenden Abschnitten werden diese Einschränkungen ausführlicher erläutert:

Verschachtelte gespeicherte Prozeduren mit unterschiedlichen Rechten¶

Wenn eine gespeicherte Prozedur mit Eigentümerrechten von einer gespeicherten Prozedur mit Aufruferrechten aufgerufen wird oder umgekehrt, gelten die folgenden Regeln:

• Eine gespeicherte Prozedur verhält sich wie eine gespeicherte Prozedur mit Aufruferrechten nur dann, wenn es sich bei der Prozedur und allen in der Aufrufhierarchie darüberliegenden Prozeduren um gespeicherte Prozeduren mit Aufruferrechten handelt.

• Eine gespeicherte Prozedur mit Eigentümerrechten verhält sich stets wie eine gespeicherte Prozedur mit Eigentümerrechten, unabhängig davon, von wo aus sie aufgerufen wird.

• Jede gespeicherte Prozedur, die direkt oder indirekt von einer gespeicherten Prozedur mit Eigentümerrechten aufgerufen wird, verhält sich wie eine gespeicherte Prozedur mit Eigentümerrechten.

Auswählen zwischen Eigentümerrechten und Aufruferrechten¶

Erstellen Sie eine gespeicherte Prozedur als gespeicherte Prozedur mit Eigentümerrechten, wenn alle der folgenden Bedingungen erfüllt sind:

• Sie möchten eine Aufgabe bzw. Aufgaben an einen oder mehrere andere Benutzer delegieren, die mit den Berechtigungen des Eigentümers und nicht den Berechtigungen des Aufrufers ausgeführt werden sollen.

  Wenn Sie beispielsweise möchten, dass ein Benutzer ohne DELETE-Berechtigung für eine Tabelle eine gespeicherte Prozedur aufrufen kann, mit der alte, aber nicht aktuelle Daten gelöscht werden, werden Sie wahrscheinlich eine gespeicherte Prozedur mit Eigentümerrechten verwenden. Diese Prozedur wird eine DELETE-Anweisung enthalten, die einen Filter (eine WHERE-Klausel) enthält, um zu steuern, welche Daten über den Filter gelöscht werden können.

  Tipp

  Wenn Sie eine gespeicherte Prozedur mit Eigentümerrechten benötigen, um Aktionen auf einer Tabelle, einer Ansicht oder einer Funktion auszuführen, für die der Aufrufer die Zugriffsberechtigung hat, können Sie den Aufrufer eine Referenz auf diese Tabelle, Ansicht oder Funktion übergeben lassen.

  Weitere Informationen dazu finden Sie unter Übergegen von Referenzen auf Tabellen, Ansichten, Funktionen und Abfragen an gespeicherte Prozeduren.

• Die Einschränkungen von gespeicherten Prozeduren mit Eigentümerrechten verhindern nicht, dass die gespeicherte Prozedur ordnungsgemäß funktioniert.

Erstellen Sie eine gespeicherte Prozedur als gespeicherte Prozedur mit Aufruferrechten, wenn Folgendes zutrifft:

• Die gespeicherte Prozedur kann nur für Objekte verwendet werden, bei denen der Aufrufer der Eigentümer ist oder die erforderlichen Berechtigungen besitzt.

• Die Einschränkungen von gespeicherten Prozeduren mit Eigentümerrechten würden verhindern, dass die gespeicherte Prozedur ordnungsgemäß funktioniert. Verwenden Sie beispielsweise eine Prozedur mit Aufruferrechten, wenn der Aufrufer in der gespeicherten Prozedur Informationen aus seiner Umgebung verwenden muss (z. B. Sitzungsvariablen oder Kontoparameter).

Wenn eine bestimmte Prozedur entweder mit den Rechten des Aufrufers oder mit den Rechten des Eigentümers ordnungsgemäß funktioniert, kann die folgende Regel Ihnen bei der Auswahl der zu verwendenden Rechte helfen:

• Wenn es sich bei einer Prozedur um eine Prozedur mit Eigentümerrechten handelt, hat der Aufrufer keine Berechtigung zur Anzeige des Codes in der gespeicherten Prozedur (es sei denn, der Aufrufer ist gleichzeitig Eigentümer). Wenn Sie verhindern möchten, dass Aufrufer den Quellcode der Prozedur anzeigen, erstellen Sie die Prozedur als Prozedur mit Eigentümerrechten. Wenn Sie umgekehrt möchten, dass Aufrufer den Quellcode lesen können, erstellen Sie die Prozedur als Prozedur mit Aufruferrechten.