Einige oder alle Informationen auf dieser Seite gelten möglicherweise nicht für Cloud de Confiance von S3NS. Weitere Informationen finden Sie unter Unterschiede zu Google Cloud.

Google uses AI technology to translate content into your preferred language. AI translations can contain errors.

Parametrisierte sichere Ansichten verwenden

MySQL | PostgreSQL | SQL Server

In diesem Dokument wird beschrieben, wie Sie parametrisierte sichere Ansichten in Cloud SQL for PostgreSQL verwenden, mit denen Sie den Datenzugriff basierend auf anwendungsspezifischen benannten Parametern wie Anwendungsnutzeranmeldedaten einschränken können. Parametrisierte sichere Ansichten verbessern die Sicherheit und Zugriffssteuerung, indem sie die Funktionalität von PostgreSQL-Ansichten erweitern. Diese Ansichten verringern auch die Risiken, die mit der Ausführung nicht vertrauenswürdiger Abfragen aus Anwendungen verbunden sind, da automatisch eine Reihe von Einschränkungen für jede ausgeführte Abfrage erzwungen werden.

Weitere Informationen finden Sie in der Übersicht über parametrisierte sichere Ansichten und im Tutorial zu parametrisierten sicheren Ansichten.

Hinweis

In diesem Dokument wird davon ausgegangen, dass Sie eine Cloud SQL for PostgreSQL-Instanz erstellt haben.

Bevor Sie parametrisierte sichere Ansichten verwenden können, müssen Sie Folgendes tun:

Aktivieren Sie das Datenbank-Flag cloudsql.enable_parameterized_views für Ihre Cloud SQL-Instanz. Für diese Flag-Änderung ist ein Neustart der Datenbank erforderlich. Weitere Informationen finden Sie unter Datenbank-Flags konfigurieren.
Verwenden Sie Cloud SQL Studio oder psql, um die parameterized_views-Erweiterung in einer beliebigen Datenbank zu erstellen, in der eine parametrisierte Ansicht erstellt wird:
```
-- Requires cloudsql.enable_parameterized_views set to on
CREATE EXTENSION parameterized_views;
```
Wenn die Erweiterung erstellt wird, erstellt das System auch ein Schema mit dem Namen parameterized_views. So sind die APIs im Namespace dieses Schemas enthalten und geraten nicht mit vorhandenen APIs in Konflikt.

Parametrisierte sichere Ansicht erstellen

So erstellen Sie eine parametrisierte sichere Ansicht:

Führen Sie den DDL-Befehl CREATE VIEW mit der Option security_barrier aus:
```
CREATE VIEW VIEW_NAME WITH (security_barrier) AS
SELECT COLUMN_NAME, COLUMN_NAME_N
FROM TABLE_NAME ALIAS
WHERE CONDITION;
```
Ersetzen Sie Folgendes:
- VIEW_NAME: der Name der parametrisierten sicheren Ansicht
- TABLE_NAME: der Name der Tabelle, die in der parametrisierten sicheren Ansicht verwendet werden soll
- ALIAS: Der Alias für den Tabellennamen, der in der parametrisierten sicheren Ansicht verwendet werden soll.
- COLUMNNAME oder COLUMN_NAMEN: Der Name der Tabellenspalte oder -spalten, die in der parametrisierten sicheren Ansicht verwendet werden sollen.
- CONDITION: Die Bedingungsanweisung, mit der Anwendungsnutzer eingeschränkt werden, sodass sie nur die Zeilen sehen können, auf die sie Zugriff haben. Fügen Sie erforderliche Parameter mit der Syntax $@PARAMETER_NAME in der WHERE-Klausel hinzu. Ein häufiger Anwendungsfall ist die Überprüfung des Werts einer Spalte mit WHERE COLUMN = $@PARAMETER_NAME.
  
  $@PARAMETER_NAME gibt einen benannten Ansichtsparameter an. Der Wert wird bereitgestellt, wenn Sie die execute_parameterized_query API verwenden. Für benannte Ansichtsparameter gelten die folgenden Anforderungen:
  - Parameter für benannte Ansichten müssen mit einem Buchstaben (a–z) oder einem Unterstrich (_) beginnen.
  - Nachfolgende Zeichen können Buchstaben, Unterstriche oder Ziffern (0–9) sein.
  - Bei benannten Ansichtsparametern wird zwischen Groß- und Kleinschreibung unterschieden. $@PARAMETER_NAME wird beispielsweise anders interpretiert als $@parameter_name.
  Im Folgenden sehen Sie ein Beispiel für das Erstellen einer parametrisierten sicheren Ansicht, in der ein benannter Ansichtsparameter verwendet wird:
```
CREATE VIEW user_specific_items WITH (security_barrier) AS
SELECT item_id, item_name, description, owner_id
FROM items t
WHERE owner_id = $@app_user_id;
```
Gewähren Sie SELECT für die Ansicht für alle Datenbanknutzer, die die Ansicht abfragen dürfen.
Gewähren Sie die Berechtigung USAGE für das Schema, das die in der Ansicht definierten Tabellen enthält, für alle Datenbanknutzer, die die Ansicht abfragen dürfen.

Weitere Informationen finden Sie unter Zugriff auf Anwendungsdaten mit parametrisierten sicheren Ansichten sichern und steuern (Tutorial).

Sicherheit für Ihre Anwendung konfigurieren

So konfigurieren Sie die Sicherheit für Ihre Anwendungen mit parametrisierten sicheren Ansichten:

Erstellen Sie die parametrisierte sichere Ansicht als Nutzer mit Administratorzugriff. Dies ist ein Cloud SQL-Datenbanknutzer, der administrative Vorgänge für die Anwendung ausführt.
Erstellen Sie eine neue Datenbankrolle zum Ausführen von Abfragen für parametrisierte sichere Ansichten. Dies ist eine Cloud SQL-Datenbankrolle, die von der Anwendung verwendet wird, um eine Verbindung zur Datenbank herzustellen und sich anzumelden.
1. Der neuen Rolle Berechtigungen für die sicheren Ansichten gewähren. Das umfasst in der Regel SELECT-Berechtigungen für die Ansichten und USAGE für die Schemas.
2. Beschränken Sie die Objekte, auf die diese Rolle zugreifen kann, auf die minimal erforderliche Menge an öffentlichen Funktionen und Objekten, die die Anwendung benötigt. Vermeiden Sie den Zugriff auf Schemas und Tabellen, die nicht öffentlich sind.
Wenn Sie die Datenansichten abfragen, stellt die Anwendung die Werte der erforderlichen Datenansichtsparameter bereit, die mit der Identität des Anwendungsnutzers verknüpft sind.

Weitere Informationen finden Sie unter Nutzer erstellen und verwalten.

Parametrisierte sichere Ansicht abfragen

Verwenden Sie eine der folgenden Optionen, um eine parametrisierte sichere Ansicht abzufragen:

JSON-basiert: Mit dieser API können Sie die Abfrage einmalig ausführen und JSON-Zeilen zurückgeben.
CURSOR-basiert: Verwenden Sie diese API, wenn Sie länger laufende oder große Abfragen haben und die Ergebnisse in Batches abrufen möchten.
PREPARE .. AS RESTRICTED-Anweisung: Verwenden Sie PREPARE .. AS RESTRICTED, um den Abfrageplan zu definieren, und führen Sie dann EXECUTE ... WITH VIEW PARAMETERS (...) aus, um ihn mit bestimmten Parametern für die Ansicht auszuführen.

JSON API

Führen Sie die Funktion execute_parameterized_query() mit der folgenden Syntax aus:

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
);

Die Funktion gibt eine Tabelle mit JSON-Objekten zurück. Jede Zeile in der Tabelle entspricht dem row_to_json()-Wert der ursprünglichen Zeile des Abfrageergebnisses.

Bei Verwendung dieser API wird die Größe des Ergebnissatzes nach Größe (in KB) und nach Anzahl der Zeilen begrenzt. Sie können diese Limits mit parameterized_views.json_results_max_size und parameterized_views.json_results_max_rows konfigurieren.

CURSOR API

Führen Sie die Funktion execute_parameterized_query() mit einem Cursornamen aus. Dadurch wird ein transaktionsbezogener CURSOR erstellt und zurückgegeben:

-- Must be in a transaction block
BEGIN;

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    cursor_name => CURSOR_NAME,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
);

FETCH ALL FROM CURSOR_NAME;

END;

Vorbereitete Anweisungen

Mit der Methode für vorbereitete Anweisungen können Sie einen Abfrageplan einmal vorbereiten und dann mehrmals mit verschiedenen Werten für die Positions- und benannten Parameter der Abfrage ausführen.

So verwenden Sie eine vorbereitete Anweisung:

Erstellen Sie die vorbereitete Anweisung.
```
-- Prepare the statement
PREPARE PREPARED_STATEMENT_NAME (QUERY_PARAM_TYPE_1, QUERY_PARAM_TYPE_N)
AS RESTRICTED SQL_QUERY;
```
Ersetzen Sie Folgendes:
- PREPARED_STATEMENT_NAME: der Name der vorbereiteten Anweisung
- QUERY_PARAM_TYPE_N: Der Datentyp des Abfrageparameters, z. B. TEXT.
- SQL_QUERY: Die SQL-Abfrage, die als Teil der vorbereiteten Anweisung mit dem angegebenen Wert oder den angegebenen Werten ausgeführt werden soll.
Führen Sie die vorbereitete Anweisung aus.
```
-- Execute the statement with query parameters and view parameters
EXECUTE PREPARED_STATEMENT_NAME (QUERY_VALUE_1, QUERY_VALUE_N)
WITH VIEW PARAMETERS (PARAMETER_NAME_1 := 'PARAMETER_VALUE_1', PARAMETER_NAME_N := 'PARAMETER_VALUE_N');
```
Ersetzen Sie Folgendes:
- PREPARED_STATEMENT_NAME: Der Name der vorbereiteten Aussage.
- QUERY_VALUE_N: der Wert oder die Werte, die als Parameter für die SQL-Abfrage angegeben werden sollen
- PARAMETER_NAME_N: Der Name des oder der benannten Ansichtsparameter, die Sie beim Erstellen der parametrisierten sicheren Ansicht definiert haben. Sie erstellen den benannten Ansichtsparameter aus der Spalte der Tabelle.
- PARAMETER_VALUE_N: Der Wert oder die Werte für den benannten Ansichtsparameter, der die parametrisierte sichere Ansicht auf die Zeilen beschränkt, die dem Wert zugeordnet sind.

Bereinigen Sie die vorbereitete Anweisung.

-- Cleanup
DEALLOCATE PREPARED_STATEMENT_NAME>;

Im folgenden Beispiel wird eine parametrisierte sichere Ansicht mit dem Namen user_specific_items verwendet, für die der benannte Ansichtsparameter $@app_user_id erforderlich ist.

-- Prepare a query with a positional parameter $1 for the item_name pattern
PREPARE get_items_by_name (TEXT) AS RESTRICTED
SELECT item_id, item_name FROM user_specific_items
WHERE item_name LIKE $1;

Nachdem Sie die vorbereitete Anweisung erstellt haben, können Sie sie mehrmals ausführen und sowohl für die Abfrage als auch für den benannten Ansichtsparameter verschiedene Werte zuweisen.

Beispiel:

-- Execute for user 123, looking for items like '%Laptop%'
EXECUTE get_items_by_name ('%Laptop%')
WITH VIEW PARAMETERS (app_user_id := '123');

Die zweite Abfrageausführung:

-- Execute for user 456, looking for items like '%Book%'
EXECUTE get_items_by_name ('%Book%')
WITH VIEW PARAMETERS (app_user_id := '456');

In der WITH VIEW PARAMETERS-Klausel werden die benannten Ansichtsparameter (123, 456) für die parametrisierte sichere Ansicht angegeben, getrennt von den Positionsparametern für die vorbereitete Abfrage (%Laptop%, %Book%).

Bereinigen Sie zum Schluss die vorbereitete Anweisung.

-- Clean up the get_item_by_name prepared statement
DEALLOCATE get_items_by_name;

Erzwungene Einschränkungen bei Abfragen

Im Folgenden finden Sie die eingeschränkten Vorgänge für Abfragen, die Sie mit den in Parametrisierte sichere Ansicht abfragen beschriebenen Optionen ausführen:

Schreibgeschützt: Nur schreibgeschützte SELECT-Anweisungen sind zulässig. DML (INSERT, UPDATE, DELETE) und DDL (CREATE, ALTER) sind nicht zulässig.
Keine Verschachtelung: Rekursive Aufrufe von execute_parameterized_query sind nicht zulässig.
Einschränkungen für Erweiterungen: Bestimmte Erweiterungen, die neue Hintergrundsitzungen starten (z.B. dblink, pg_cron), sind nicht zulässig.
EXPLAIN-Anweisungen sind nicht zulässig, um potenziellen Informationsverlust durch Abfragepläne zu verhindern.

Alle parametrisierten Ansichten auflisten

In der Ansicht parameterized_views.all_parameterized_views werden alle parametrisierten Ansichten (mit mindestens einem benannten Parameter $@...) aufgeführt.

SELECT * FROM parameterized_views.all_parameterized_views;