Crear y gestionar vistas

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

Permisos

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

Crear una vista

Para crear una vista, usa la instrucción DDL CREATE VIEW para darle un nombre y proporciona la consulta que la define. Esta instrucción tiene dos formas:

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

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

La sintaxis de la instrucción CREATE VIEW es la siguiente:

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

Como una vista es una tabla virtual, el query que especifiques debe proporcionar nombres para todas las columnas de esa tabla virtual.

Además, Spanner comprueba el query que especifiques mediante la resolución estricta de nombres, lo que significa que todos los nombres de objetos de esquema utilizados en la consulta deben estar cualificados para que identifiquen de forma inequívoca un único objeto de esquema. Por ejemplo, en los ejemplos que siguen, la columna SingerId de la tabla Singers debe calificarse como Singers.SingerId.

Debes especificar SQL SECURITY como INVOKER o DEFINER en la instrucción CREATE VIEW o CREATE OR REPLACE VIEW. Para obtener más información sobre la diferencia entre los dos tipos de seguridad, consulta el artículo Información general sobre 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, tal 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.

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

Prácticas recomendadas para crear vistas

Para minimizar la necesidad de actualizar la definición de una vista, convierta explícitamente el tipo de datos de todas las columnas de la tabla en la consulta que define la vista. De esta forma, la definición de la vista puede seguir siendo válida aunque se cambie el tipo de una columna.

Por ejemplo, la siguiente definición de la vista SingerNames podría dejar de ser válida si se cambia el tipo de datos de una columna de 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 deje de ser válida, puedes convertir explícitamente las columnas a 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;

Conceder y revocar el acceso a una vista

Como usuario con control de acceso pormenorizado, debes tener el privilegio SELECT en una vista. Para conceder el privilegio SELECT en una vista a un rol de base de datos, sigue estos pasos:

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, sigue estos pasos:

GoogleSQL 

REVOKE SELECT ON VIEW SingerNames FROM ROLE Analyst;

PostgreSQL 

REVOKE SELECT ON TABLE SingerNames FROM Analyst;

Consultar una vista

La forma de consultar los derechos de un invocador o de un definidor es la misma. Sin embargo, en función del tipo de seguridad de la vista, es posible que Spanner tenga que comprobar los objetos de esquema a los que se hace referencia en la vista con el rol de base de datos de la entidad que ha invocado la consulta.

Consultar la vista de derechos de un 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;

Consultar la vista de derechos de un definidor

Si una vista tiene derechos de definidor, un usuario puede consultar la vista sin necesidad de tener privilegios en los objetos subyacentes, siempre que le concedas al rol necesario el privilegio SELECT en la vista.

En el siguiente ejemplo, un usuario con el rol de analista de la base de datos quiere 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 Analista no tiene acceso a todos los objetos subyacentes. En este caso, si decide dar acceso a la vista al analista, pero no quiere que tenga acceso a la tabla Singers, puede sustituir el tipo de seguridad de la vista por derechos del definidor. Después de sustituir el tipo de seguridad de la vista, concede el rol Analista 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;

Sustituir una vista

Puede sustituir una vista mediante la instrucción CREATE OR REPLACE VIEW para cambiar la definición de la vista o el tipo de seguridad de la vista.

Sustituir una vista es similar a eliminarla y volver a crearla. Los permisos de acceso que se hayan concedido a la vista inicial se deben volver a conceder después de sustituirla.

Para sustituir la vista de derechos de un invocador por la vista de derechos de un definidor, sigue estos pasos:

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

Eliminar una vista

Una vez que se ha eliminado una vista, los roles de base de datos que tengan privilegios en ella ya no tendrán acceso. Para eliminar una vista, usa la instrucción DROP VIEW.

DROP VIEW SingerNames;

Obtener información sobre una vista

Para obtener información sobre las vistas de una base de datos, puedes consultar 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 tengan el privilegio SELECT en la vista pueden obtener información sobre la vista de 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 comprobar la definición de la vista y el tipo de seguridad de una vista llamada ProductSoldLastWeek, sigue estos pasos:

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