Creare e gestire le visualizzazioni

Questa pagina descrive come creare e gestire le viste Spanner per i database con dialetto GoogleSQL e PostgreSQL. Per saperne di più sulle viste Spanner, consulta la panoramica delle viste.

Autorizzazioni

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

Crea una vista

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

  • CREATE VIEW definisce una nuova vista nel database corrente. Se esiste già una vista denominata view_name, l'istruzione CREATE VIEW non va a buon fine.

  • CREATE OR REPLACE VIEW definisce una nuova vista nel database corrente. Se esiste già una vista denominata view_name, la sua definizione viene sostituita.

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, l'elemento query che specifichi deve fornire i nomi per tutte le colonne della tabella virtuale.

Inoltre, Spanner controlla il query specificato utilizzando la risoluzione dei nomi rigorosa, il che significa che tutti i nomi degli oggetti dello schema utilizzati nella query devono essere qualificati in modo da identificare in modo univoco un singolo oggetto dello schema. Ad esempio, negli esempi che seguono, la colonna SingerId nella tabella Singers deve essere qualificata come Singers.SingerId.

Devi specificare SQL SECURITY come INVOKER o DEFINER nell'istruzione CREATE VIEW o CREATE OR REPLACE VIEW. Per saperne di più sulla differenza tra i due tipi di sicurezza, consulta la panoramica delle visualizzazioni.

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 di chi richiama come mostrato di seguito:

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 la best practice di eseguire il cast dei tipi di dati per garantire la stabilità tra le modifiche dello schema, come descritto nella sezione successiva.

Crea una vista parametrizzata

Per creare viste parametrizzate, includi la funzione SECURE_CONTEXT() nella clausola WHERE dell'istruzione SELECT.

Nell'esempio seguente, la vista parametrizzata concede all'utente dell'applicazione l'accesso alle colonne SingerId, FirstName e LastName della tabella Singers. La vista limita i risultati alle righe in cui UserID corrisponde a un parametro obbligatorio, app_end_userid.

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

Per utilizzare la vista con parametri, devi disporre del privilegio SELECT su una vista. Per saperne di più, consulta Concedi e revoca l'accesso a una visualizzazione.

Best practice per la creazione di visualizzazioni

Per ridurre al minimo la necessità di aggiornare la definizione di una vista, esegui il cast esplicito del tipo di dati di tutte le colonne della tabella nella query che definisce la vista. In questo modo, la definizione della vista può rimanere valida anche in caso di modifiche allo schema del tipo di una colonna.

Ad esempio, la seguente definizione della vista SingerNames potrebbe diventare non valida 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;

Puoi evitare che la vista diventi non valida eseguendo il cast esplicito delle colonne ai tipi di dati necessari, come mostrato nell'esempio seguente:

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;

Concedi e revoca l'accesso a una vista

In qualità di utente con controllo dell'accesso granulare dell'accesso, devi disporre del privilegio SELECT su una vista. Per concedere il privilegio SELECT su 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;

Esegui una query su una vista

Il modo per eseguire query su una vista con diritti dell'invoker o del definer è lo stesso. Tuttavia, a seconda del tipo di sicurezza della vista, Spanner potrebbe o meno dover controllare gli oggetti dello schema a cui fa riferimento la vista rispetto al ruolo del database dell'entità che ha richiamato la query.

Esegui una query su una vista con diritti di chi esegue la chiamata

Se una vista dispone dei diritti di chi richiama, l'utente deve disporre dei privilegi su tutti gli oggetti schema sottostanti della vista per poter eseguire query.

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

SELECT COUNT(SingerID) as SingerCount
FROM SingerNames;

Esegui una query su una vista con diritti del definer

Se una vista dispone dei diritti del definer, un utente può eseguire query sulla vista senza bisogno di privilegi sugli oggetti sottostanti, purché tu conceda al ruolo richiesto il privilegio SELECT sulla vista.

Nell'esempio seguente, un utente con il ruolo di database Analyst vuole eseguire una query sulla vista SingerNames. Tuttavia, all'utente viene negato l'accesso perché SingerNames è una vista con diritti del chiamante e il ruolo Analyst non ha accesso a tutti gli oggetti sottostanti. In questo caso, se decidi di fornire all'analista l'accesso alla vista, ma non vuoi fornire l'accesso alla tabella Singers, puoi sostituire il tipo di sicurezza della vista con i diritti del definer. Dopo aver sostituito il tipo di sicurezza della vista, concedi l'accesso alla vista al ruolo Analyst. Ora l'utente può eseguire query sulla vista SingerNames anche se non ha accesso alla tabella Singers.

SELECT COUNT(SingerID) as SingerCount
FROM SingerNames;

Esegui query su una vista parametrizzata

Puoi eseguire query sulle viste parametrizzate nello stesso modo delle altre viste. La vista filtra i dati per trasmettere in modo sicuro il contesto a livello di applicazione in base ai suoi parametri di sicurezza (ad esempio, l'ID utente).

Sostituisci una visualizzazione

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

La sostituzione di una vista è simile all'eliminazione e alla ricreazione della vista. Tutte le autorizzazioni di accesso concesse alla vista iniziale devono essere concesse nuovamente dopo la sostituzione della vista.

Per sostituire una vista con diritti di chi richiama con una vista con diritti di chi definisce:

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

Elimina una visualizzazione

Dopo l'eliminazione di una vista, i ruoli del database con privilegi su di essa non hanno più accesso. Per eliminare una vista, utilizza l'istruzione DROP VIEW.

DROP VIEW SingerNames;

Ottieni informazioni su una visualizzazione

Puoi ottenere informazioni sulle viste in un database eseguendo query sulle tabelle nel relativo schema INFORMATION_SCHEMA.

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

  • La tabella INFORMATION_SCHEMA.VIEWS fornisce i nomi, la definizione della vista, il tipo di sicurezza e il testo della query di tutte le viste definite. Gli utenti FGAC che dispongono del privilegio SELECT sulla vista possono ottenere informazioni sulla vista dalla tabella INFORMATION_SCHEMA.VIEWS. Gli altri utenti FGAC devono avere il ruolo spanner_info_reader se non dispongono del privilegio SELECT per la vista.

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

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