Criar e gerenciar visualizações

Nesta página, descrevemos como criar e gerenciar visualizações do Spanner para bancos de dados de dialeto do GoogleSQL e do PostgreSQL. Para mais informações sobre as visualizações do Spanner, consulte Visão geral das visualizações.

Permissões

Para criar, conceder e revogar o acesso a uma visualização, é preciso ter a permissão spanner.database.updateDdl.

Criar uma visualização

Para criar uma visualização, use a instrução DDL CREATE VIEW para nomear a visualização e fornecer a consulta que a define. Essa instrução tem dois formatos:

  • CREATE VIEW define uma nova visualização no banco de dados atual. Se uma visualização chamada view_name já existir, a instrução CREATE VIEW falhará.

  • CREATE OR REPLACE VIEW define uma nova visualização no banco de dados atual. Se uma visualização chamada view_name já existir, sua definição será substituída.

A sintaxe da instrução CREATE VIEW é:

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

Como uma visualização é uma tabela virtual, o query especificado precisa fornecer nomes para todas as colunas nessa tabela virtual.

Além disso, o Spanner verifica o query especificado usando a resolução de nome estrita, o que significa que todos os nomes de objetos de esquema usados na consulta precisam ser qualificados para identificar de maneira inequívoca um único objeto de esquema. Nos exemplos que seguem a coluna SingerId, na tabela Singers, os exemplos precisam ser qualificados como Singers.SingerId.

É preciso especificar SQL SECURITY como INVOKER ou DEFINER na instrução CREATE VIEW ou CREATE OR REPLACE VIEW. Para mais informações sobre a diferença entre os dois tipos de segurança, consulte Visão geral das visualizações.

Por exemplo, suponha que a tabela Singers seja definida da seguinte maneira:

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

É possível definir a visualização SingerNames com os direitos do invocador, conforme mostrado abaixo:

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

A tabela virtual criada quando a visualização SingerNames é usada em uma consulta tem duas colunas, SingerId e Name.

Embora essa definição da visualização SingerNames seja válida, ela não segue a prática recomendada de fazer o cast de tipos de dados para garantir a estabilidade entre alterações de esquema, conforme descrito na próxima seção.

Criar uma visualização parametrizada

Para criar visualizações parametrizadas, inclua a função SECURE_CONTEXT() na cláusula WHERE da instrução SELECT.

No exemplo a seguir, a visualização parametrizada concede ao usuário do aplicativo acesso às colunas SingerId, FirstName e LastName da tabela Singers. A visualização limita os resultados às linhas em que UserID corresponde a um parâmetro obrigatório, 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);

Para usar a visualização parametrizada, é preciso ter o privilégio SELECT em uma visualização. Para mais informações, consulte Conceder e revogar acesso a uma visualização.

Práticas recomendadas ao criar visualizações

Para minimizar a necessidade de atualizar a definição de uma visualização, transmita explicitamente o tipo de dados de todas as colunas da tabela na consulta que define a visualização. Ao fazer isso, a definição da visualização pode permanecer válida nas alterações de esquema para o tipo de uma coluna.

Por exemplo, a definição a seguir da visualização SingerNames pode se tornar inválida como resultado da alteração do tipo de dados de uma coluna na tabela Singers.

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

Para evitar que a visualização se torne inválida, converta explicitamente as colunas para os tipos de dados necessários, como mostrado no exemplo a seguir:

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;

Conceder e revogar acesso a uma visualização

Como usuário de controle de acesso refinado, você precisa ter o privilégio SELECT em uma visualização. Para conceder o privilégio SELECT em uma visualização a um papel de banco de dados:

GoogleSQL 

GRANT SELECT ON VIEW SingerNames TO ROLE Analyst;

PostgreSQL 

GRANT SELECT ON TABLE SingerNames TO Analyst;

Para revogar o privilégio SELECT em uma visualização de um papel de banco de dados:

GoogleSQL 

REVOKE SELECT ON VIEW SingerNames FROM ROLE Analyst;

PostgreSQL 

REVOKE SELECT ON TABLE SingerNames FROM Analyst;

Consultar uma visualização

A maneira de consultar uma visualização de direitos de um invocador ou de um definidor é a mesma. No entanto, dependendo do tipo de segurança da visualização, o Spanner pode ou não precisar verificar os objetos de esquema referenciados na visualização em relação ao papel de banco de dados do principal que invocou a consulta.

Consultar uma visualização de direitos do invocador

Se uma visualização tiver direitos de invocador, o usuário precisará ter privilégios em todos os objetos de esquema subjacentes da visualização para consultá-la.

Por exemplo, se um papel de banco de dados tiver acesso a todos os objetos referenciados pela visualização SingerNames, ele poderá consultar a visualização SingerNames:

SELECT COUNT(SingerID) as SingerCount
FROM SingerNames;

Consultar uma visualização de direitos do definidor

Se uma visualização tiver direitos de definidor, um usuário poderá consultá-la sem precisar de privilégios nos objetos subjacentes, desde que você conceda ao papel necessário o privilégio SELECT na visualização.

No exemplo a seguir, um usuário com o papel de banco de dados de analista quer consultar a visualização SingerNames. No entanto, o acesso do usuário é negado porque SingerNames é uma visualização de direitos do invocador, e o papel de analista não tem acesso a todos os objetos subjacentes. Nesse caso, se você decidir fornecer ao analista acesso à visualização, mas não quiser fornecer acesso à tabela Singers, poderá substituir o tipo de segurança da visualização pelos direitos do definidor. Depois de substituir o tipo de segurança da visualização, conceda ao papel de analista acesso à visualização. O usuário agora pode consultar a visualização SingerNames, mesmo que não tenha acesso à tabela Singers.

SELECT COUNT(SingerID) as SingerCount
FROM SingerNames;

Consultar uma visualização parametrizada

É possível consultar visualizações parametrizadas da mesma forma que outras visualizações. A visualização filtra os dados para transmitir o contexto no nível do aplicativo com segurança com base nos parâmetros seguros (por exemplo, ID do usuário).

Substituir uma visualização

É possível substituir uma visualização usando a instrução CREATE OR REPLACE VIEW para alterar a definição ou o tipo de segurança dela.

Substituir uma visualização é semelhante a descartar e recriar a visualização. Todas as concessões de acesso concedidas à visualização inicial precisam ser concedidas novamente após a substituição da visualização.

Para substituir uma visualização de direitos do invocador por uma visualização de direitos do definidor:

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

Exclua uma visualização

Depois que uma visualização é descartada, os papéis do banco de dados com privilégios nela não têm mais acesso. Para excluir uma visualização, use a instrução DROP VIEW.

DROP VIEW SingerNames;

Receber informações sobre uma visualização

Para receber informações sobre as visualizações em um banco de dados, consulte as tabelas no esquema INFORMATION_SCHEMA.

  • A tabela INFORMATION_SCHEMA.TABLES fornece os nomes de todas as visualizações definidas.

  • A tabela INFORMATION_SCHEMA.VIEWS fornece os nomes, a definição de visualização, o tipo de segurança e o texto de consulta de todas as visualizações definidas. Os usuários do FGAC que têm o privilégio SELECT na visualização podem receber informações sobre ela na tabela INFORMATION_SCHEMA.VIEWS. Outros usuários do FGAC precisam do papel spanner_info_reader se não tiverem o privilégio SELECT para a visualização.

Para verificar a definição e o tipo de segurança de uma visualização chamada ProductSoldLastWeek:

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