Cómo crear y administrar vistas

En esta página, se describe cómo crear y administrar vistas de Spanner para bases de datos de dialecto de GoogleSQL y de PostgreSQL. Para obtener más información sobre las vistas de Spanner, consulta Descripción general de las vistas.

Permisos

Para crear, otorgar y revocar el acceso a una vista, debes tener el permiso spanner.database.updateDdl.

Crea una vista

Para crear una vista, usa la declaración DDL CREATE VIEW para asignarle un nombre y proporcionar la consulta que la define. Esta sentencia tiene dos formas:

  • CREATE VIEW define una vista nueva en la base de datos actual. Si ya existe una vista llamada view_name, la sentencia CREATE VIEW falla.

  • CREATE OR REPLACE VIEW define una vista nueva en la base de datos actual. Si ya existe una vista con el nombre view_name, se reemplaza su definición.

La sintaxis de la sentencia CREATE VIEW es la siguiente:

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

Debido a que una vista es una tabla virtual, la query que especifiques debe proporcionar nombres para todas las columnas de esa tabla virtual.

Además, Spanner verifica la query que especificas con la resolución de nombres estricta, lo que significa que todos los nombres de objetos de esquema que se usan en la consulta deben estar calificados de modo que identifiquen de forma inequívoca un solo objeto de esquema. Por ejemplo, en los ejemplos que siguen, la columna SingerId de la tabla Singers debe calificarse como Singers.SingerId.

Debes especificar el SQL SECURITY como INVOKER o DEFINER en la sentencia CREATE VIEW o CREATE OR REPLACE VIEW. Para obtener más información sobre la diferencia entre los dos tipos de seguridad, consulta Descripción general de las vistas.

Por ejemplo, supongamos que la tabla Singers se define como se muestra a continuación:

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

Puedes definir la vista SingerNames con los derechos del invocador, como se muestra a continuación:

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

La tabla virtual que se crea cuando se usa la vista SingerNames en una consulta tiene dos columnas, SingerId y Name.

Si bien esta definición de la vista SingerNames es válida, no cumple con la práctica recomendada de convertir los tipos de datos para garantizar la estabilidad en los cambios de esquema, como se describe en la siguiente sección.

Crea una vista parametrizada

Para crear vistas parametrizadas, incluye la función SECURE_CONTEXT() en la cláusula WHERE de la sentencia SELECT.

En el siguiente ejemplo, la vista parametrizada otorga al usuario de la aplicación acceso a las columnas SingerId, FirstName y LastName de la tabla Singers. La vista limita los resultados a las filas en las que UserID coincide con un parámetro obligatorio, 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 la vista parametrizada, debes tener el privilegio SELECT en una vista. Para obtener más información, consulta Otorga y revoca el acceso a una vista.

Prácticas recomendadas para crear vistas

Para minimizar la necesidad de actualizar la definición de una vista, convierte de forma explícita el tipo de datos de todas las columnas de la tabla en la consulta que define la vista. Cuando lo haces, la definición de la vista puede seguir siendo válida en los cambios de esquema del tipo de una columna.

Por ejemplo, la siguiente definición de la vista SingerNames podría dejar de ser válida como resultado de cambiar el tipo de datos de una columna en la tabla Singers.

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

Para evitar que la vista se vuelva no válida, puedes convertir de forma explícita las columnas en los tipos de datos necesarios, como se muestra en el siguiente ejemplo:

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;

Otorga y revoca el acceso a una vista

Como usuario del control de acceso detallado, debes tener el privilegio SELECT en una vista. Para otorgar el privilegio SELECT en una vista a un rol de base de datos, haz lo siguiente:

GoogleSQL 

GRANT SELECT ON VIEW SingerNames TO ROLE Analyst;

PostgreSQL 

GRANT SELECT ON TABLE SingerNames TO Analyst;

Para revocar el privilegio SELECT en una vista de un rol de base de datos, haz lo siguiente:

GoogleSQL 

REVOKE SELECT ON VIEW SingerNames FROM ROLE Analyst;

PostgreSQL 

REVOKE SELECT ON TABLE SingerNames FROM Analyst;

Consulta una vista

La forma de consultar una vista de privilegios del invocador o del definidor es la misma. Sin embargo, según el tipo de seguridad de la vista, es posible que Spanner deba verificar o no los objetos de esquema a los que se hace referencia en la vista con el rol de base de datos de la principal que invocó la consulta.

Consulta una vista de privilegios del invocador

Si una vista tiene derechos de invocador, el usuario debe tener privilegios en todos los objetos de esquema subyacentes de la vista para poder consultarla.

Por ejemplo, si un rol de base de datos tiene acceso a todos los objetos a los que hace referencia la vista SingerNames, puede consultar la vista SingerNames:

SELECT COUNT(SingerID) as SingerCount
FROM SingerNames;

Consulta una vista de derechos del definidor

Si una vista tiene derechos de definidor, un usuario puede consultar la vista sin necesidad de privilegios en los objetos subyacentes, siempre y cuando otorgues al rol requerido el privilegio SELECT en la vista.

En el siguiente ejemplo, un usuario con el rol de base de datos de analista desea consultar la vista SingerNames. Sin embargo, se le deniega el acceso al usuario porque SingerNames es una vista de derechos del invocador y el rol de analista no tiene acceso a todos los objetos subyacentes. En este caso, si decides proporcionar al analista acceso a la vista, pero no quieres proporcionarle acceso a la tabla Singers, puedes reemplazar el tipo de seguridad de la vista por los derechos del definidor. Después de reemplazar el tipo de seguridad de la vista, otorga al rol de analista acceso a la vista. Ahora, el usuario puede consultar la vista SingerNames, aunque no tenga acceso a la tabla Singers.

SELECT COUNT(SingerID) as SingerCount
FROM SingerNames;

Consulta una vista parametrizada

Las vistas parametrizadas se consultan de la misma manera que otras vistas. La vista filtra los datos para pasar el contexto a nivel de la aplicación de forma segura según sus parámetros seguros (por ejemplo, el ID de usuario).

Reemplaza una vista

Puedes reemplazar una vista con la sentencia CREATE OR REPLACE VIEW para cambiar la definición de la vista o el tipo de seguridad de la vista.

Reemplazar una vista es similar a descartarla y volver a crearla. Cualquier permiso de acceso otorgado a la vista inicial se debe volver a otorgar después de reemplazar la vista.

Para reemplazar una vista de privilegios del invocador por una vista de privilegios del definidor, haz lo siguiente:

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

Borrar una vista

Después de que se borra una vista, los roles de base de datos con privilegios en ella ya no tienen acceso. Para borrar una vista, usa la sentencia DROP VIEW.

DROP VIEW SingerNames;

Obtén información sobre una vista

Puedes obtener información sobre las vistas de una base de datos consultando las tablas de su esquema INFORMATION_SCHEMA.

  • La tabla INFORMATION_SCHEMA.TABLES proporciona los nombres de todas las vistas definidas.

  • La tabla INFORMATION_SCHEMA.VIEWS proporciona los nombres, la definición de la vista, el tipo de seguridad y el texto de la consulta de todas las vistas definidas. Los usuarios de FGAC que tienen el privilegio SELECT en la vista pueden obtener información sobre la vista en la tabla INFORMATION_SCHEMA.VIEWS. Otros usuarios de FGAC necesitan el rol spanner_info_reader si no tienen el privilegio SELECT para la vista.

Para verificar la definición de la vista y el tipo de seguridad de una vista llamada ProductSoldLastWeek, haz lo siguiente:

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