Sichere UDFs

Unter diesem Thema werden Konzepte und Syntax einschließlich der Beispiele für die Verwendung von sicheren UDFs (benutzerdefinierte Funktionen) behandelt.

Unter diesem Thema:

Übersicht

Warum sollte ich Secure UDFs verwenden?

Einige der internen Optimierungen für SQL-UDFs erfordern den Zugriff auf die zugrunde liegenden Daten in den Basistabellen. Durch diesen Zugriff können Daten, die für Benutzer der UDF verborgen sind, indirekt über programmatische Methoden verfügbar gemacht werden. Sichere UDFs nutzt diese Optimierungen nicht. Dadurch wird sichergestellt, dass Benutzer nicht einmal indirekt auf die zugrunde liegenden Daten zugreifen können.

Außerdem ist der SQL-Ausdruck oder der JavaScript-Code zum Erstellen einer UDF, auch als Definition oder Text UDF bezeichnet, für Benutzer in den folgenden Befehlen und Schnittstellen sichtbar:

For security or privacy reasons, you might not wish to expose the underlying tables or algorithmic details for a UDF. With secure UDFs, the definition and details are visible only to authorized users (i.e. users who are granted the role that owns the UDF).

Wann sollte ich eine sichere UDF verwenden?

SQL-UDFs sollten als sicher definiert werden, wenn sie speziell für den Datenschutz bestimmt sind (d. h. den Zugriff auf vertrauliche Daten einschränken, die nicht allen Benutzern der zugrunde liegenden Tabellen zugänglich sein sollten).

Sichere UDFs sollten nicht für SQL-UDFs verwendet werden, die zur Vereinfachung der Abfrage definiert sind, z. B. solche, die zur Vereinfachung der Abfrage von Daten erstellt wurden, für die Benutzer die zugrunde liegende Datendarstellung nicht verstehen müssen. Dies liegt daran, dass der Snowflake-Abfrageoptimierer beim Auswerten von sicheren UDFs die für reguläre UDFs verwendeten Optimierungen umgeht. Dies kann die Abfrageleistung für sichere UDFs reduzieren.

Tipp

Bei der Entscheidung, ob Sie eine sichere UDF verwenden möchten, sollten Sie den Zweck der UDF berücksichtigen und den Kompromiss zwischen Datenschutz/Sicherheit und Abfrageleistung abwägen.

Wenn Ihre Daten so sensibel sind, dass Sie entscheiden, dass Zugriffe über einen Objekttyp (wie UDFs) sicher sein sollen, sollten Sie unbedingt dafür sorgen, dass Zugriffe über andere Objekttypen (z. B. Ansichten) ebenfalls sicher sind.

Wenn Sie zum Beispiel nur die sicheren UDFs für den Zugriff auf eine bestimmte Tabelle zulassen, sollten alle Ansichten, die Sie für den Zugriff auf dieselbe Tabelle zulassen, wahrscheinlich ebenfalls sicher sein.

Wie werden Daten durch eine nicht reguläre UDF offengelegt?

Benutzer fragen sich vielleicht, wie die Sicherheit einer regulären UDF umgangen werden könnte. In bestimmten Situationen kann ein Benutzer Informationen über Zeilen ableiten, die er nicht direkt sehen kann. Weitere Details dazu finden Sie unter Pushdown (unter diesem Thema).

Pushdown

Um zu verstehen, wie Daten indirekt verfügbar gemacht werden können, hilft es, das Konzept von Pushdown zu verstehen.

Was ist Pushdown?

Pushdown verbessert die Leistung, indem nicht benötigte Zeilen während der Abfrageverarbeitung so früh wie möglich herausgefiltert werden. Pushdown kann auch den Speicherverbrauch reduzieren. Durch Pushdown können vertrauliche Daten jedoch indirekt angezeigt werden.

Betrachten Sie die folgende Abfrage:

SELECT col1
  FROM tab1
  WHERE location = 'New York';

Ein Ansatz zur Bearbeitung der Abfrage ist folgender:

  1. Lesen Sie alle Zeilen aus der Tabelle in den Speicher (d. h. führen Sie die FROM-Klausel aus).

  2. Scannen Sie die Zeilen im Speicher und filtern Sie alle Zeilen heraus, die nicht mit New York übereinstimmen (d. h. führen Sie die WHERE-Klausel aus).

  3. Wählen Sie col1 aus den noch im Speicher verbleibenden Zeilen aus (d. h. führen Sie die Liste SELECT aus).

Man kann sich das als eine „Erst laden, dann filtern“-Strategie vorstellen, die einfach, aber ineffizient ist.

Es ist normalerweise effizienter, so früh wie möglich zu filtern. Frühes Filtern bedeutet, „den Filter tiefer in den Abfrageplan hineinzudrücken“ oder einfach „Pushdown“.

In unserer Beispielabfrage wäre es effizienter, dem Tabellensuchcode mitzuteilen, dass keine Datensätze geladen werden sollen, die nicht mit der WHERE-Klausel übereinstimmen. Dies spart keine Filterzeit (die Position jeder Zeile muss immer noch einmal gelesen werden), kann jedoch beträchtlichen Speicherplatz sparen und die nachfolgende Verarbeitungszeit reduzieren, da weniger Zeilen verarbeitet werden müssen.

In manchen Fällen können Sie die Daten noch effizienter verarbeiten. Nehmen Sie beispielsweise an, dass die Daten nach Bundesstaat partitioniert sind (d. h. alle Daten für New York befinden sich in einer Mikropartition, alle Daten für Florida in einer anderen Mikropartition usw.). In diesem Szenario:

  • Snowflake muss nicht alle Zeilen im Speicher speichern.

  • Snowflake muss nicht alle Zeilen lesen.

Wir definieren dies lose als eine andere Form des „Pushdowns“.

Das Prinzip des „Hineindrücken der Filter“ gilt für eine Vielzahl von Abfragen. Häufig wird der Filter, der am selektivsten ist (die meisten Daten aussortiert), am tiefsten hineingedrückt (frühestens ausgeführt), um die Arbeit zu reduzieren, die die verbleibende Abfrage erledigen muss.

Pushdown kann mit anderen Techniken kombiniert werden, wie z. B. Clustering (Sortieren der Daten), um die Menge an irrelevanten Daten zu reduzieren, die gelesen, geladen und verarbeitet werden müssen.

Wie können UDFs Daten schützen?

Wenn das Optimierungsprogramm die Filter so anordnet, dass ein allgemeiner Filter ausgeführt werden kann, bevor die zum Schutz der Daten verwendeten Filter angewendet werden, können die zugrunde liegenden Details verfügbar gemacht werden. Daher besteht die Lösung darin, zu verhindern, dass der Optimierer bestimmte Filtertypen hineindrückt (im Allgemeinen, um zu verhindern, dass der Optimierer bestimmte Arten von Optimierungen verwendet, insbesondere den Filter-Pushdown), wenn diese Optimierungen nicht sicher sind.

Wenn Sie eine UDF als „sicher“ deklarieren, wird das Optimierungsprogramm angewiesen, bestimmte Filter nicht hineinzudrücken (allgemeiner ausgedrückt, bestimmte Optimierungen nicht zu verwenden). Das Verhindern bestimmter Arten von Optimierungen kann sich jedoch auf die Leistung auswirken.

Beispiel für indirekte Datenexposition durch Pushdown

Das folgende Beispiel zeigt eine Möglichkeit, wie Pushdown indirekt dazu führen kann, dass die zugrunde liegenden Details einer Abfrage angezeigt werden. Dieses Beispiel konzentriert sich auf Ansichten. Die gleichen Prinzipien gelten jedoch für UDFs.

Angenommen, es gibt eine Tabelle, in der Informationen zu Patienten gespeichert werden:

CREATE TABLE patients
  (patient_ID INTEGER,
   category VARCHAR,      -- 'PhysicalHealth' or 'MentalHealth'
   diagnosis VARCHAR
   );

INSERT INTO patients (patient_ID, category, diagnosis) VALUES
  (1, 'Mental', 'paranoia'),
  (2, 'Physical', 'lung cancer');

Es gibt zwei Ansichten, von denen eine Informationen zur psychischen Gesundheit und die andere Informationen zur körperlichen Gesundheit enthält:

CREATE VIEW mental_health_view AS
  SELECT * FROM patients WHERE category = 'MentalHealth';

CREATE VIEW physical_health_view AS
  SELECT * FROM patients WHERE category = 'PhysicalHealth';

Die meisten Benutzer haben keinen direkten Zugriff auf die Tabelle. Benutzern wird stattdessen eine von zwei Rollen zugewiesen:

  • MentalHealth, die über Berechtigungen zum Lesen von mental_health_view verfügt, oder

  • PhysicalHealth, die über Berechtigungen zum Lesen von physical_health_view verfügt.

Nehmen wir nun an, dass ein Arzt, der nur über Berechtigungen auf Daten zur körperlichen Gesundheit verfügt, wissen möchte, ob derzeit Angaben zu psychisch kranken Patienten in der Tabelle enthalten sind. Der Arzt kann eine Abfrage erstellen, die der folgenden ähnelt:

SELECT * FROM physical_health_view
  WHERE 1/IFF(category = 'MentalHealth', 0, 1) = 1;

Diese Abfrage entspricht:

SELECT * FROM patients
  WHERE
    category = 'PhysicalHealth' AND
    1/IFF(category = 'MentalHealth', 0, 1) = 1;

Snowflake verwendet (mindestens) zwei Methoden, um diese Abfrage zu verarbeiten.

  • Methode 1:

    1. Lesen Sie alle Zeilen in der Patiententabelle.

    2. Wenden Sie den Sicherheitsfilter der Ansicht an (d. h. filtern Sie die Zeilen heraus, deren Kategorie nicht PhysicalHealth ist).

    3. Wenden Sie die WHERE-Klausel in der Abfrage an (d. h. filtern Sie basierend auf WHERE 1/IFF(category = 'MentalHealth', 0, 1) = 1).

  • Methode 2 ändert die Reihenfolge der Filter, sodass die Abfrage wie folgt ausgeführt wird:

    1. Lesen Sie alle Zeilen in der Patiententabelle.

    2. Wenden Sie die WHERE-Klausel in der Abfrage an (d. h. filtern Sie basierend auf WHERE 1/IFF(category = 'MentalHealth', 0, 1) = 1).

    3. Wenden Sie den Sicherheitsfilter der Ansicht an (d. h. filtern Sie die Zeilen heraus, deren Kategorie nicht PhysicalHealth ist).

Logisch scheinen diese beiden Sequenzen äquivalent zu sein; sie geben die gleiche Reihe von Zeilen zurück. Abhängig davon, wie selektiv diese beiden Filter sind, kann eine Reihenfolge der Verarbeitung schneller sein. Der Abfrageplaner von Snowflake wählt möglicherweise den Plan, der schneller ausgeführt wird.

Angenommen, das Optimierungsprogramm wählt den zweiten Plan aus, in dem die Klausel WHERE 1/IFF(category = 'MentalHealth', 0, 1) = 1 vor dem Sicherheitsfilter ausgeführt wird. Wenn die Patiententabelle irgendwelche Zeilen enthält, in denen category = 'MentalHealth' enthalten ist, gibt die Funktion IFF 0 für diese Zeile zurück, und die Klausel wird tatsächlich zu WHERE 1/0 = 1, sodass die Anweisung einen Fehler durch Null teilt. Der Benutzer mit physical_health_view-Berechtigungen sieht keine Zeilen für Personen mit psychischen Problemen, kann jedoch ableiten, dass mindestens eine Person in der Kategorie psychische Gesundheit existiert.

Beachten Sie, dass diese Technik nicht immer dazu führt, dass darunterliegende Details sichtbar gemacht werden. Es hängt stark von den Entscheidungen ab, die der Abfrageplaner trifft, und davon, wie die Ansichten (oder UDFs) geschrieben werden. Dieses Beispiel zeigt jedoch, dass ein Benutzer Informationen zu Zeilen ableiten kann, die er nicht direkt anzeigen kann.

Erstellen von sicheren UDFs

Sichere UDFs werden mit dem Schlüsselwort SECURE mit der Standard-DDL für Funktionen definiert:

  • Geben Sie zum Erstellen einer sicheren Funktion das Schlüsselwort SECURE im Befehl CREATE FUNCTION an.

  • Um eine vorhandene Funktion in eine sichere oder zurück in eine reguläre Funktion zu konvertieren, setzen oder löschen Sie das Schlüsselwort SECURE im

  • ALTER FUNCTION-Befehl.

Interagieren mit sicheren UDFs

Anzeigen der Definition für sichere Funktionen

Die Definition einer sicheren Funktion ist nur für berechtigte Benutzer verfügbar, d. h. für Benutzer, denen die Rolle zugewiesen wurde, die die Funktion besitzt. Wenn ein nicht autorisierter Benutzer einen der folgenden Befehle oder eine der folgenden Schnittstellen verwendet, wird die Funktionsdefinition nicht durch eine der folgenden Optionen angezeigt:

Ermitteln, ob eine Funktion sicher ist

Die Spalte IS_SECURE in der Ansicht FUNCTIONS des Information Schema gibt an, ob eine Funktion sicher ist. Beispiel:

select is_secure from information_schema.functions where function_name = 'MYFUNCTION';

Anzeigen von Details der sicheren Funktion in Query Profile

Die internen Details einer sicheren Funktion werden nicht in Query Profile (in der Weboberfläche) angezeigt. Dies gilt sogar für den Eigentümer der sicheren Funktion, da Nicht-Eigentümer möglicherweise auf das Query Profile eines Eigentümers zugreifen können.

Best Practices für sichere UDFs

Sichere UDFs verhindern, dass Benutzern möglicherweise Daten aus Tabellenzeilen offengelegt werden, die von der Funktion gefiltert werden. Es gibt jedoch immer noch Möglichkeiten, dass ein Dateneigentümer versehentlich Informationen über die zugrunde liegenden Daten preisgibt, wenn UDFs nicht sorgfältig konstruiert sind. In diesem Abschnitt werden einige mögliche Gefahren erläutert, die vermieden werden müssen.

Sequenzgenerierte Spalten

Eine gängige Praxis zum Generieren von Ersatzschlüsseln ist die Verwendung einer Sequenz oder einer Spalte mit automatischer Inkrementierung. Wenn diese Schlüssel für Benutzer verfügbar sind, die keinen Zugriff auf alle zugrunde liegenden Daten haben, kann ein Benutzer möglicherweise Details der zugrunde liegenden Datenverteilung erraten.

Angenommen, wir haben eine Funktion get_widgets_function(), die die Spalte ID verfügbar macht. Wenn ID aus einer Sequenz generiert wird, kann ein Benutzer von get_widgets_function() die Gesamtanzahl der erstellten Widgets ableiten, die zwischen den Erstellungszeitstempeln von zwei Widgets erstellt wurden, auf die der Benutzer Zugriff hat. Betrachten Sie die folgende Abfrage und ihr Ergebnis:

select * from table(get_widgets_function()) order by created_on;

------+-----------------------+-------+-------+-------------------------------+
  ID  |         NAME          | COLOR | PRICE |          CREATED_ON           |
------+-----------------------+-------+-------+-------------------------------+
...
 315  | Small round widget    | Red   | 1     | 2017-01-07 15:22:14.810 -0700 |
 1455 | Small cylinder widget | Blue  | 2     | 2017-01-15 03:00:12.106 -0700 |
...

Basierend auf dem Ergebnis könnte der Benutzer vermuten, dass zwischen dem 7. Januar und dem 15. Januar 1.139 Widgets (1455 - 315) erstellt wurden. Wenn diese Information zu sensibel ist, um sie Benutzern einer Funktion offenzulegen, können Sie eine der folgenden Alternativen verwenden:

  • Legen Sie die sequenzgenerierte Spalte nicht als Teil der Funktion offen.

  • Verwenden Sie anstelle der sequenzgenerierten Werte zufällige Bezeichner (z. B. generiert durch UUID_STRING).

  • Programmieren Sie eine Verschleierung der Bezeichner.

Größe der gescannten Daten

Für Abfragen, die sichere Funktionen enthalten, legt Snowflake weder den Umfang der gescannten Daten (in Byte oder in Mikropartitionen) noch die Gesamtdatenmenge offen. Dies dient zum Schutz der Informationen vor Benutzern, die nur Zugriff auf eine Teilmenge der Daten haben. Benutzer sind jedoch möglicherweise weiterhin in der Lage, anhand der Performance der Abfragen Rückschlüsse auf den Umfang der zugrunde liegenden Daten zu ziehen. Beispielsweise kann eine Abfrage, die doppelt so lange ausgeführt wird, doppelt so viele Daten verarbeiten. Während solche Beobachtungen bestenfalls ungefähr sind, kann es in manchen Fällen unerwünscht sein, dass sogar diese Art von Informationen offengelegt wird.

In solchen Fällen ist es am besten, Daten pro Benutzer/Rolle zu materialisieren, anstatt den Benutzern Funktionen in den Basisdaten anzuzeigen. Im Fall der Tabelle widgets würde für jede Rolle, die Zugriff auf Widgets hat, eine Tabelle erstellt werden, die nur die Widgets enthält, auf die diese Rolle zugreifen kann, und der Rolle würde Zugriff auf ihre Tabelle gewährt werden. Dies ist viel umständlicher als die Verwendung einer einzelnen Funktion. In extrem hochsicheren Situationen kann dies jedoch gerechtfertigt sein.

Sichere UDFs und Data Sharing

Bei Verwendung sicherer UDFs mit Data Sharing kann die Funktion CURRENT_ACCOUNT verwendet werden, um Benutzer eines bestimmten Kontos für den Zugriff auf Zeilen in einer Basistabelle zu autorisieren.

Beachten Sie, dass Snowflake nicht die Verwendung von CURRENT_ROLE oder CURRENT_USER in sicheren UDFs empfiehlt, die für andere Konten freigegeben werden, da der Eigentümer der freigegebenen Daten normalerweise nicht die Rollen und Benutzer des Kontos kontrollieren kann, für das sie freigegeben wurden.