Creare e gestire le visualizzazioni

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

Autorizzazioni

Per creare, concedere e revocare l'accesso a una visualizzazione, 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 affermazione 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 relativa 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, il query specificato deve fornire i nomi di 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 inequivocabile un singolo oggetto 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 ulteriori informazioni sulla differenza tra i due tipi di sicurezza, consulta Informazioni sulle 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 dell'invoker 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 la best practice di conversione dei tipi di dati per garantire la stabilità nelle modifiche dello schema, come descritto nella sezione successiva.

Best practice per la creazione di viste

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

Ad esempio, la seguente definizione della vista SingerNames potrebbe diventare invalida a seguito della 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 non sia valida, puoi eseguire il casting esplicito delle colonne ai 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

Nota: se crei una visualizzazione dei diritti del definitore, utilizza il controllo dell'accesso granulare insieme alla visualizzazione, altrimenti la visualizzazione dei diritti del definitore non aggiunge alcun controllo dell'accesso aggiuntivo.

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

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 potrebbe o meno dover controllare gli oggetti dello schema a cui viene fatto riferimento nella vista rispetto al ruolo del database del principale che ha invocato la query.

Esegui una query sulla visualizzazione dei diritti di un invocatore

Se una vista ha i diritti dell'autore dell'invocazione, 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;

Eseguire una query sulla visualizzazione dei diritti di un definitore

Se una vista dispone dei diritti di definizione, un utente può eseguire query sulla vista senza dover disporre di privilegi sugli oggetti sottostanti, a condizione che tu abbia assegnato al ruolo richiesto il privilegio SELECT sulla vista.

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

SELECT COUNT(SingerID) as SingerCount 
FROM SingerNames;

Sostituire una visualizzazione

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

La sostituzione di una visualizzazione è simile al rilascio e alla ricreazione della visualizzazione. Tutti i permessi di accesso concessi alla visualizzazione iniziale devono essere concessi di nuovo dopo aver sostituito la visualizzazione.

Per sostituire la visualizzazione dei diritti di un invocatore con quella di un 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 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;

Visualizzare le informazioni su una vista

Puoi ottenere informazioni sulle visualizzazioni in un database eseguendo query sulle tabelle nello schema INFORMATION_SCHEMA.

• La tabella INFORMATION_SCHEMA.TABLES fornisce i nomi di tutte le visualizzazioni 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 disporre del ruolo spanner_info_reader se non dispongono del privilegio SELECT 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';