Ansichten erstellen und verwalten

Auf dieser Seite wird beschrieben, wie Sie Cloud Spanner-Ansichten für Datenbanken im GoogleSQL-Dialekt und Datenbanken im PostgreSQL-Dialekt erstellen und verwalten. Weitere Informationen zu Spanner-Ansichten finden Sie unter Ansichten – Übersicht.

Berechtigungen

Zum Erstellen, Gewähren und Widerrufen des Zugriffs auf eine Ansicht benötigen Sie die Berechtigung spanner.database.updateDdl.

Ansicht erstellen

Verwenden Sie zum Erstellen einer Ansicht die DDL-Anweisung CREATE VIEW, um die Ansicht zu benennen und die Abfrage anzugeben, die sie definiert. Diese Anweisung hat zwei Formen:

  • CREATE VIEW definiert eine neue Tabelle in der aktuellen Datenbank. Wenn eine Ansicht mit dem Namen view_name bereits vorhanden ist, schlägt die Anweisung CREATE VIEW fehl.

  • CREATE OR REPLACE VIEW definiert eine neue Tabelle in der aktuellen Datenbank. Wenn eine Ansicht mit dem Namen view_name bereits vorhanden ist, wird ihre Definition ersetzt.

Die Syntax für die Anweisung CREATE VIEW lautet:

{CREATE | CREATE OR REPLACE } VIEW  view_name
SQL SECURITY { INVOKER | DEFINER }
AS query

Da eine Ansicht eine virtuelle Tabelle ist, müssen Sie in der von Ihnen angegebenen query Namen für alle Spalten in dieser virtuellen Tabelle angeben.

Darüber hinaus prüft Spanner die von Ihnen angegebene query mithilfe der strikten Namensauflösung. Das bedeutet, dass alle in der Abfrage verwendeten Schemaobjektnamen so qualifiziert sein müssen, dass sie ein einzelnes Schemaobjekt eindeutig identifizieren. Beispielsweise müssen die Beispiele, die auf die Spalte SingerId in der Tabelle Singers folgen, als Singers.SingerId qualifiziert sein.

Sie müssen SQL SECURITY in der CREATE VIEW- oder CREATE OR REPLACE VIEW-Anweisung entweder als INVOKER oder als DEFINER angeben. Weitere Informationen zum Unterschied zwischen den beiden Sicherheitstypen finden Sie unter Ansichten – Übersicht.

Angenommen, die Tabelle Singers ist so definiert:

GoogleSQL 

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo BYTES(MAX)
) PRIMARY KEY (SingerId);

PostgreSQL 

CREATE TABLE Singers (
  SingerId   BIGINT PRIMARY KEY,
  FirstName  VARCHAR(1024),
  LastName   VARCHAR(1024),
  SingerInfo BYTEA
);

Sie können die Ansicht SingerNames mit den Rechten des Aufrufers so definieren:

CREATE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
   Singers.SingerId AS SingerId,
   Singers.FirstName || ' ' || Singers.LastName AS Name
FROM Singers;

Die virtuelle Tabelle, die erstellt wird, wenn die Ansicht SingerNames in einer Abfrage verwendet wird, hat zwei Spalten: SingerId und Name.

Diese Definition der Ansicht SingerNames ist zwar gültig, entspricht jedoch nicht der Best Practice für die Umwandlung von Datentypen zur Gewährleistung der Stabilität bei Schemaänderungen, wie im nächsten Abschnitt beschrieben.

Parametrisierte Ansicht erstellen

Wenn Sie parametrisierte Ansichten erstellen möchten, fügen Sie die Funktion SECURE_CONTEXT() in die WHERE-Klausel der SELECT-Anweisung ein.

Im folgenden Beispiel gewährt die parametrisierte Ansicht dem Anwendungsnutzer Zugriff auf die Spalten SingerId, FirstName und LastName aus der Tabelle Singers. In der Ansicht werden die Ergebnisse auf Zeilen beschränkt, in denen UserID mit dem erforderlichen Parameter app_end_userid übereinstimmt.

  CREATE VIEW SingerNames
  SQL SECURITY DEFINER
  AS SELECT
   Singers.SingerId AS SingerId,
   Singers.FirstName || ' ' || Singers.LastName AS Name
  FROM Singers
  WHERE UserID = CAST(SECURE_CONTEXT('app_end_userid'), INT64);

Wenn Sie die parametrisierte Ansicht verwenden möchten, benötigen Sie die Berechtigung SELECT für eine Ansicht. Weitere Informationen finden Sie unter Zugriff auf eine Ansicht gewähren und widerrufen.

Best Practices für das Erstellen von Ansichten

Um die Notwendigkeit zu minimieren, die Definition einer Ansicht zu aktualisieren, müssen Sie den Datentyp aller Tabellenspalten in der Abfrage, die die Ansicht definiert, explizit umwandeln. Wenn Sie das tun, kann die Definition der Ansicht während Schemaänderungen am Spaltentyp gültig bleiben.

Beispielsweise kann die folgende Definition der Ansicht SingerNames ungültig werden, wenn der Datentyp einer Spalte in der Tabelle Singers geändert wird.

CREATE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
   Singers.SingerId AS SingerId,
   Singers.FirstName || ' ' || Singers.LastName AS Name
FROM Singers;

Sie können verhindern, dass die Ansicht ungültig wird, indem Sie die Spalten explizit in die erforderlichen Datentypen umwandeln, wie im folgenden Beispiel gezeigt:

GoogleSQL 

CREATE OR REPLACE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
 CAST(Singers.SingerId AS INT64) AS SingerId,
 CAST(Singers.FirstName AS STRING) || " " || CAST(Singers.LastName AS STRING) AS Name
FROM Singers;

PostgreSQL 

CREATE OR REPLACE VIEW SingerNames
SQL SECURITY INVOKER
AS SELECT
 CAST(Singers.SingerId AS bigint) AS SingerId,
 CAST(Singers.FirstName AS varchar) || ' ' || CAST(Singers.LastName AS varchar) AS Name
FROM Singers;

Zugriff auf eine Ansicht gewähren und widerrufen

Als Nutzer mit detaillierter Zugriffssteuerung benötigen Sie die Berechtigung SELECT für eine Ansicht. So gewähren Sie einer Datenbankrolle die Berechtigung SELECT für eine Ansicht:

GoogleSQL 

GRANT SELECT ON VIEW SingerNames TO ROLE Analyst;

PostgreSQL 

GRANT SELECT ON TABLE SingerNames TO Analyst;

So entziehen Sie einer Datenbankrolle die Berechtigung SELECT für eine Ansicht:

GoogleSQL 

REVOKE SELECT ON VIEW SingerNames FROM ROLE Analyst;

PostgreSQL 

REVOKE SELECT ON TABLE SingerNames FROM Analyst;

Ansicht abfragen

Die Abfrage einer Ansicht mit Aufruferrechten oder einer Ansicht mit Erstellerrechten ist identisch. Je nach Sicherheitstyp der Ansicht muss Spanner jedoch möglicherweise die in der Ansicht referenzierten Schemaobjekte mit der Datenbankrolle des Hauptkontos abgleichen, das die Abfrage aufgerufen hat.

Ansicht mit Aufruferrechten abfragen

Wenn eine Ansicht Aufruferrechte hat, muss der Nutzer Berechtigungen für alle zugrunde liegenden Schemaobjekte der Ansicht haben, um sie abfragen zu können.

Wenn eine Datenbankrolle beispielsweise Zugriff auf alle Objekte hat, auf die die Ansicht SingerNames verweist, kann sie die Ansicht SingerNames abfragen:

SELECT COUNT(SingerID) as SingerCount
FROM SingerNames;

Ansicht mit den Rechten des Erstellers abfragen

Wenn eine Ansicht die Rechte des Erstellers hat, kann ein Nutzer die Ansicht abfragen, ohne Berechtigungen für die zugrunde liegenden Objekte zu benötigen, solange Sie der erforderlichen Rolle die Berechtigung SELECT für die Ansicht gewähren.

Im folgenden Beispiel möchte ein Nutzer mit der Datenbankrolle „Analyst“ die Ansicht SingerNames abfragen. Der Zugriff wird dem Nutzer jedoch verweigert, da SingerNames eine Ansicht mit Aufruferrechten ist und die Rolle „Analyst“ keinen Zugriff auf alle zugrunde liegenden Objekte hat. Wenn Sie dem Analysten Zugriff auf die Ansicht, aber nicht auf die Tabelle Singers gewähren möchten, können Sie den Sicherheitstyp der Ansicht durch die Rechte des Erstellers ersetzen. Nachdem Sie den Sicherheitstyp der Ansicht ersetzt haben, gewähren Sie der Rolle „Analyst“ Zugriff auf die Ansicht. Der Nutzer kann jetzt die Ansicht SingerNames abfragen, obwohl er keinen Zugriff auf die Tabelle Singers hat.

SELECT COUNT(SingerID) as SingerCount
FROM SingerNames;

Parametrisierte Ansicht abfragen

Parametrisierte Ansichten werden wie andere Ansichten abgefragt. In der Ansicht werden Daten gefiltert, um den Kontext auf Anwendungsebene anhand der sicheren Parameter (z. B. Nutzer-ID) sicher zu übergeben.

Ansicht ersetzen

Sie können eine Ansicht mit der Anweisung CREATE OR REPLACE VIEW ersetzen, um die Ansichtsdefinition oder den Sicherheitstyp der Ansicht zu ändern.

Das Ersetzen einer Ansicht ähnelt dem Löschen und Neuerstellen der Ansicht. Alle Zugriffsberechtigungen, die für die ursprüngliche Ansicht gewährt wurden, müssen nach dem Ersetzen der Ansicht noch einmal gewährt werden.

So ersetzen Sie eine Ansicht mit Aufruferrechten durch eine Ansicht mit Erstellerrechten:

CREATE OR REPLACE VIEW SingerNames
SQL SECURITY DEFINER
AS SELECT
   Singers.SingerId AS SingerId,
   Singers.FirstName || ' ' || Singers.LastName AS Name
FROM Singers;

Ansicht löschen

Nachdem eine Ansicht gelöscht wurde, haben Datenbankrollen mit Berechtigungen für diese Ansicht keinen Zugriff mehr. Verwenden Sie die Anweisung DROP VIEW, um eine Ansicht zu löschen.

DROP VIEW SingerNames;

Informationen zu einer Ansicht abrufen

Informationen zu Ansichten in einer Datenbank erhalten Sie, indem Sie Tabellen im Schema INFORMATION_SCHEMA abfragen.

  • Die Tabelle INFORMATION_SCHEMA.TABLES enthält die Namen aller definierten Ansichten.

  • Die Tabelle INFORMATION_SCHEMA.VIEWS enthält die Namen, die Ansichtsdefinition, den Sicherheitstyp und den Abfragetext aller definierten Ansichten. FGAC-Nutzer, die die Berechtigung SELECT für die Ansicht haben, können Informationen zur Ansicht aus der Tabelle INFORMATION_SCHEMA.VIEWS abrufen. Andere Nutzer mit detaillierter Zugriffssteuerung benötigen die Rolle spanner_info_reader, wenn sie nicht die Berechtigung SELECT für die Ansicht haben.

So prüfen Sie die Ansichtsdefinition und den Sicherheitstyp einer Ansicht mit dem Namen ProductSoldLastWeek:

  SELECT *
  FROM INFORMATION_SCHEMA.VIEWS
  WHERE TABLE_NAME = 'ProductSoldLastWeek';