Creare e gestire le viste

Questa pagina descrive come creare e gestire le viste Spanner. Per ulteriori informazioni per informazioni sulle viste Spanner, consulta Informazioni sulle viste.

Autorizzazioni

Per creare, concedere e revocare l'accesso a una vista, devi disporre del Autorizzazione spanner.database.updateDdl.

Crea una vista

Per creare una vista, utilizza l'istruzione DDL CREATE VIEW per assegnare un nome visualizzare e fornire la query che lo definisce. Questa affermazione presenta due forme:

  • CREATE VIEW definisce una nuova vista nel database attuale. Se una vista denominata view_name esiste già, l'istruzione CREATE VIEW non riesce.

  • CREATE OR REPLACE VIEW definisce una nuova vista nel database attuale. Se una visualizzazione denominato view_name esiste già, la relativa definizione è sostituite.

La sintassi dell'istruzione CREATE VIEW è:

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

Poiché una vista è una tabella virtuale, il valore query che specificare deve fornire i nomi per tutte le colonne in quella tabella virtuale.

Inoltre, Spanner controlla query che specifichi utilizzando la risoluzione rigida dei nomi, il che significa che tutti i nomi degli oggetti dello schema usati nella query devono essere qualificati in modo da identificare in modo inequivocabile un a schema singolo. Ad esempio, negli esempi che seguono SingerId della tabella Singers deve essere qualificato come Singers.SingerId.

Devi specificare SQL SECURITY come INVOKER o DEFINER nel Dichiarazione CREATE VIEW o CREATE OR REPLACE VIEW. Per ulteriori informazioni la differenza tra i due tipi di sicurezza, consulta Informazioni sulle viste.

Ad esempio, supponiamo che la tabella Singers sia definita come segue:

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
);

Puoi definire la vista SingerNames con i diritti dell'invocatore come segue:

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

La tabella virtuale creata quando la vista SingerNames viene utilizzata in una query ha due colonne, SingerId e Name.

Sebbene questa definizione della vista SingerNames sia valida, non rispetta le la best practice per trasmettere tipi di dati per garantire stabilità nello schema modifiche, come descritto nella sezione successiva.

Best practice per la creazione delle viste

Per ridurre al minimo la necessità di aggiornare la definizione di una vista, trasmetti esplicitamente i dati il tipo di tutte le colonne della tabella nella query che definisce la vista. In questo caso, la definizione della vista può rimanere valida in tutte le modifiche allo schema di un tipo di colonna.

Ad esempio, la seguente definizione della vista SingerNames potrebbe diventare non valido in seguito alla modifica del tipo di dati di una colonna nella tabella Singers.

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

Per evitare che la visualizzazione diventi non valida, trasmetti esplicitamente le colonne al i tipi di dati desiderati, come segue:

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;

Concedere e revocare l'accesso a una vista

In qualità di utente con controllo dell'accesso granulare, devi disporre del privilegio SELECT su una vista. Per concedere il privilegio SELECT per una vista a un ruolo di database:

GoogleSQL 

GRANT SELECT ON VIEW SingerNames TO ROLE Analyst;

PostgreSQL 

GRANT SELECT ON TABLE SingerNames TO Analyst;

Per revocare il privilegio SELECT su una vista da un ruolo di database:

GoogleSQL 

REVOKE SELECT ON VIEW SingerNames FROM ROLE Analyst;

PostgreSQL 

REVOKE SELECT ON TABLE SingerNames FROM Analyst;

Eseguire una query su una vista

Il modo per eseguire query sui diritti di un invocatore o sulla visualizzazione dei diritti di un definitore è lo stesso. Tuttavia, a seconda del tipo di sicurezza della vista, Spanner può o meno dover verificare gli oggetti dello schema a cui viene fatto riferimento nella vista rispetto il ruolo database dell'entità che ha richiamato la query.

Eseguire query sulla visualizzazione diritti di un invocatore

Se una visualizzazione possiede i diritti dell'invocatore, l'utente deve disporre di privilegi su tutte le degli oggetti schema della vista al fine di eseguire query su quest'ultima.

Ad esempio, se un ruolo di database ha accesso a tutti gli oggetti a cui fa riferimento SingerNames vista, può eseguire query sulla vista SingerNames:

SELECT COUNT(SingerID) as SingerCount 
FROM SingerNames;

Eseguire query sulla vista diritti di un definitore

Se una vista dispone dei diritti di chi definisce, un utente può eseguire query sulla vista senza dover gli oggetti sottostanti, purché concedi il ruolo richiesto SELECT privilegio per la vista.

Nell'esempio seguente, un utente con il ruolo Analista database vuole eseguire una query la vista SingerNames. Tuttavia, all'utente viene negato l'accesso perché SingerNames è la vista diritti di un invocatore e il ruolo Analista non ha accesso a tutte le oggetti sottostanti. In questo caso, se decidi di fornire all'analista l'accesso alla vista, ma non voglio concedere l'accesso all'Singers tabella, puoi sostituire il tipo di sicurezza della vista con i diritti del definitore. Dopo aver sostituito il tipo di sicurezza della vista, concedi il parametro Accesso con il ruolo Analista alla vista. Ora l'utente può eseguire query sulla vista SingerNames anche se non ha accesso alla tabella Singers.

SELECT COUNT(SingerID) as SingerCount 
FROM SingerNames;

Sostituire una visualizzazione

Puoi sostituire una visualizzazione utilizzando l'istruzione CREATE OR REPLACE VIEW per modificare la definizione della vista o il tipo di sicurezza della vista.

La sostituzione di una vista è un processo simile all'eliminazione e alla nuova creazione della vista. Qualsiasi accesso le concessioni assegnate alla vista iniziale devono essere concesse di nuovo dopo aver sostituito vista.

Per sostituire la visualizzazione dei diritti di un invocatore con una visualizzazione dei diritti del definitore:

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

Eliminare una vista

Dopo aver eliminato una vista, i ruoli del database con privilegi non vengono più assegnati access. Per eliminare una visualizzazione, utilizza l'istruzione DROP VIEW. 

DROP VIEW SingerNames;

Ottenere informazioni su una vista

Puoi ottenere informazioni sulle viste in un database eseguendo query sulle tabelle nel suo Schema INFORMATION_SCHEMA.

  • INFORMATION_SCHEMA.TABLES fornisce i nomi di tutte le viste definite.

  • INFORMATION_SCHEMA.VIEWS fornisce i nomi, la definizione della visualizzazione, il tipo di sicurezza e il testo tutte le viste definite. Utenti FGAC con privilegio SELECT per la vista può ottenere informazioni sulla vista dalla tabella INFORMATION_SCHEMA.VIEWS. Gli altri utenti FGAC devono avere il ruolo spanner_info_reader se non hanno SELECT privilegio per la visualizzazione.

Per controllare la definizione e il tipo di sicurezza di una vista denominata ProductSoldLastWeek:

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