Parametrisierte sichere Ansichten verwenden

In diesem Dokument wird beschrieben, wie Sie parametrisierte sichere Ansichten in Cloud SQL for MySQL verwenden, mit denen Sie den Datenzugriff basierend auf anwendungsspezifischen benannten Parametern wie Anmeldedaten von Anwendungsnutzern einschränken können. Parametrisierte sichere Ansichten verbessern die Sicherheit und Zugriffssteuerung, indem sie die Funktionalität von MySQL-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 unter Parametrisierte sichere Ansichten in Cloud SQL.

Hinweis

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

  1. Prüfen Sie, ob auf Ihrer Cloud SQL-Instanz MySQL 8.0.43 oder höher ausgeführt wird. Wenn auf Ihrer MySQL 8.0-Instanz eine frühere Nebenversion ausgeführt wird, können Sie ein Upgrade der Nebenversion durchführen.

  2. Aktivieren Sie das Datenbank-Flag cloudsql_parameterized_secure_view für Ihre Instanz.

    Für diese Flag-Änderung ist kein Neustart der Datenbank erforderlich. Weitere Informationen finden Sie unter Datenbank-Flags konfigurieren.

Parametrisierte sichere Ansicht erstellen

Führen Sie den DDL-Befehl CREATE VIEW aus, um eine parametrisierte sichere Ansicht zu erstellen. Beispiel:

CREATE VIEW v_orders
AS SELECT * FROM orders
WHERE customer_id = @local_customer_id;

Verwenden Sie die folgende Syntax:

-- Create a view with a parameter using the WHERE clause
CREATE VIEW VIEW_NAME AS
SELECT * FROM TABLE_NAME
WHERE CONDITION;
 
-- Create a view with a parameter using the HAVING clause
CREATE VIEW VIEW_NAME AS
SELECT * FROM TABLE_NAME
WHERE CONDITION_1      -- row level security
GROUP BY COLUMN_NAME
HAVING CONDITION_2;    -- grouping
 
-- Create a view with a parameter using the ON clause
CREATE VIEW VIEW_NAME AS
SELECT * FROM TABLE_NAME_1
JOIN TABLE_NAME_2
ON CONDITION_1       --  join criteria
WHERE CONDITION_2;   --  row level security

Ersetzen Sie Folgendes:

  • VIEW_NAME: der Name der parametrisierten sicheren Ansicht
  • TABLE_NAME oder TABLE_NAME_N: Der Name der Tabelle oder Tabellen, die in der parametrisierten Ansicht verwendet werden sollen.
  • COLUMN_NAME oder COLUMN_NAME_N: der Name der Tabellenspalte(n), die in der parametrisierten Ansicht verwendet werden sollen

  • CONDITION oder CONDITION_N: Die Bedingungsanweisung(en), die im Rahmen einer WHERE-, ON- oder HAVING-Klausel ausgewertet werden sollen. Sie können die Sitzungsvariable angeben, die als Parameter verwendet wird, wenn Sie später eine Abfrage für die Ansicht ausführen. Eine Bedingung sieht so aus:

    WHERE customer_id = @local_customer_id

    Sitzungsvariablen, @variable_name, sind nur in den WHERE-, ON- oder HAVING-Klauseln einer CREATE VIEW-Anweisung zulässig.

Parametrisierte sichere Ansicht abfragen

Wenn Sie eine parametrisierte sichere Ansicht in Cloud SQL for MySQL abfragen möchten, haben Sie zwei Möglichkeiten:

  • Weisen Sie Variablen Werte zu, indem Sie den Hinweis SET_VIEW_VARS verwenden, wenn Sie die parametrisierte sichere Ansicht abfragen. Wenn eine der Variablen, auf die in der Ansicht verwiesen wird, nicht festgelegt ist oder wenn in der SET_VIEW_VARS-Hinweisvariable nicht zugehörige Variablen enthalten sind, schlägt die Abfrage für die parametrisierte sichere Ansicht fehl.

  • Rufen Sie die Prozedur mysql.execute_parameterized_query auf, um die parametrisierte sichere Ansicht abzufragen. Wenn Sie die Prozedur mysql.execute_parameterized_query verwenden, geben Sie zwei Argumente an. Das erste Argument ist eine Abfrage für die parametrisierte sichere Ansicht ohne Hinweise zur Sitzungsvariable. Das zweite Argument enthält alle erforderlichen Sitzungsvariablen und die Werte, die Sie angeben möchten.

Hinweiswerte verwenden

So fragen Sie die parametrisierte sichere Ansicht mit dem Hinweis SET_VIEW_VARS ab:

-- Set the parameter and query
SELECT /*+ SET_VIEW_VARS(SESSION_VARIABLE = VALUE) */ *
FROM VIEW_NAME;

Ersetzen Sie Folgendes:

  • SESSION_VARIABLE: Der benannte Parameter der parametrisierten sicheren Ansicht, für den Sie unterschiedliche Werte angeben können. Sie definieren den Namen der Sitzungsvariablen, wenn Sie die Ansicht erstellen. Verwenden Sie dazu die @variable_name-Notation in der Ansichtsbedingungsklausel.
  • VALUE: ein Wert für diesen benannten Parameter
  • VIEW_NAME: Name der parametrisierten sicheren Ansicht

Beispiel:

SELECT /*+ SET_VIEW_VARS(local_customer_id = 12345) */ *
FROM v_orders;

Parametrisierte Abfrage verwenden

Verwenden Sie die folgende Syntax, um die parametrisierte sichere Ansicht mit der Prozedur mysql.execute_parameterized_query abzufragen:

CALL mysql.execute_parameterized_query(
  'SELECT * FROM DATABASE_NAME.VIEW_NAME',
  'SESSION_VARIABLE=VALUE');

Ersetzen Sie Folgendes:

  • SESSION_VARIABLE: Der benannte Parameter der parametrisierten sicheren Ansicht, für den Sie unterschiedliche Werte angeben können. Sie definieren den Namen der Sitzungsvariablen, wenn Sie die Ansicht erstellen. Verwenden Sie dazu die @variable_name-Notation in der Ansichtsbedingungsklausel.
  • VALUE: ein Wert für diesen benannten Parameter
  • DATABASE_NAME: Name der Datenbank der Ansicht
  • VIEW_NAME: Name der parametrisierten sicheren Ansicht

Beispiel:

   CALL mysql.execute_parameterized_query(
    'SELECT * FROM db.v_orders',
    'local_customer_id = 5, earliest_year = 2021');

Beschrä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:

  • Die Abfrage kann nur eine SELECT-Anweisung sein.
  • Die Abfrage darf keine Aufrufe von benutzerdefinierten Funktionen oder gespeicherten Prozeduren enthalten.
  • In der Abfrage können keine Variablen zugewiesen werden.
  • Wenn Sie die CALL mysql.execute_parameterized_query-Methode verwenden, darf die Abfrage nicht aus mehreren Anweisungen bestehen, die zu einer einzelnen Anweisung verkettet werden. Die Abfrageanweisung SELECT * FROM a; DROP TABLE a; ist beispielsweise ungültig.

Parametrisierte sichere Ansichten in Logs ansehen

Wenn Cloud SQL for MySQL-Datenbanknutzer versuchen, eine parametrisierte sichere Ansicht zu erstellen, wird im MySQL-Fehlerlog eine Meldung auf Informationsebene protokolliert, die der folgenden ähnelt:

 User variables permitted in %s.%s through Parameterized
 Secure View feature

Fehlerbehebung bei parametrisierten sicheren Ansichten

Problem Fehlerbehebung
Das Flag cloud_parameterized_secure_view ist für die Instanz nicht festgelegt. Wenn das Flag cloudsql_parameterized_secure_view auf OFF gesetzt ist, führt jeder Versuch, eine vorhandene parametrisierte sichere Ansicht abzufragen, zu folgendem Fehler:

Cannot operate on a Parameterized Secure View without `cloudsql_parameterized_secure_view` being set. Please turn the flag on first before querying Parameterized Secure Views

Wenn Sie versuchen, eine parametrisierte sichere Ansicht ohne festgelegtes Flag zu erstellen, wird der folgende Fehler angezeigt:

View's SELECT contains a variable or parameter

Sie können prüfen, ob die parametrisierten sicheren Ansichten für eine Cloud SQL-Instanz aktiviert sind, indem Sie die globale Variable prüfen:

SHOW VARIABLES LIKE 'cloudsql_parameterized_secure_view';
Die Abfrage schlägt mit dem Fehler View's SELECT contains a variable or parameter fehl. Variablen sind außerhalb von WHERE-, HAVING- oder ON-Klauseln nicht zulässig. Die Verwendung einer Variablen außerhalb dieser Klauseln führt zu einem Fehler.
Abfrage schlägt mit dem Fehler User variable `%s` used in Parameterized Secure View %s must be set in the query before using SET_VIEW_VARS hint fehl Legen Sie die Sitzungsvariable fest, bevor Sie die Abfrage ausführen.
Abfrage schlägt mit einem MySQL-Versionsfehler fehl Wenn Sie versuchen, eine vorhandene parametrisierte sichere Ansicht mit einer MySQL-Version vor 8.0.43 abzufragen, kann der folgende Fehler auftreten:

Variable %s found in view %s. Please upgrade to 8.0.43 or above to use Parameterized Secure Views

Nächste Schritte