Ansichten erstellen und verwalten

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

Berechtigungen

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

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, muss die von Ihnen angegebene query Namen für alle Spalten in dieser virtuellen Tabelle enthalten.

Darüber hinaus prüft Spanner die query von Ihnen angegebene strikte 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 SingerId Spalte in der Singers Tabelle folgen, als Singers.SingerId qualifiziert sein.

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

Angenommen, die Tabelle Singers ist wie folgt 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 wie folgt 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 SingerNames Ansicht 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.

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 vermeiden, 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 entziehen

Als Nutzer der detaillierten 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 den Rechten des Aufrufers oder einer Ansicht mit den Rechten des Definierers erfolgt auf dieselbe Weise. Je nach Sicherheitstyp der Ansicht muss Spanner jedoch möglicherweise die in der Ansicht referenzierten Schemaobjekte mit der Datenbankrolle des Prinzipals vergleichen, der die Abfrage aufgerufen hat.

Ansicht mit den Rechten des Aufrufers abfragen

Wenn eine Ansicht die Rechte des Aufrufers 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 in der Ansicht SingerNames verwiesen wird, kann sie die Ansicht SingerNames abfragen:

SELECT COUNT(SingerID) as SingerCount
FROM SingerNames;

Ansicht mit den Rechten des Definierers abfragen

Wenn eine Ansicht die Rechte des Definierers hat, kann ein Nutzer die Ansicht abfragen, ohne Berechtigungen für die zugrunde liegenden Objekte zu benötigen, sofern 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 jedoch verweigert, da SingerNames eine Ansicht mit den Rechten des Aufrufers ist und die Rolle „Analyst“ keinen Zugriff auf alle zugrunde liegenden Objekte hat. Wenn Sie in diesem Fall dem Analysten Zugriff auf die Ansicht gewähren möchten, aber keinen Zugriff auf die Singers Tabelle gewähren möchten, können Sie den Sicherheitstyp der Ansicht in „Rechte des Definierers“ ändern. Nachdem Sie den Sicherheitstyp der Ansicht geändert 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;

Ansicht ersetzen

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

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

So ersetzen Sie eine Ansicht mit den Rechten des Aufrufers durch eine Ansicht mit den Rechten des Definierers:

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 zum Löschen einer Ansicht die Anweisung DROP VIEW.

DROP VIEW SingerNames;

Informationen zu einer Ansicht abrufen

Sie können Informationen zu Ansichten in einer Datenbank abrufen, indem Sie Tabellen in ihrem INFORMATION_SCHEMA-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. Nutzer der detaillierten Zugriffssteuerung mit der Berechtigung SELECT für die Ansicht können Informationen zur Ansicht aus der Tabelle INFORMATION_SCHEMA.VIEWS abrufen. Andere Nutzer der detaillierten Zugriffssteuerung benötigen die Rolle spanner_info_reader, wenn sie keine 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';