Cómo crear y administrar vistas

En esta página, se describe cómo crear y administrar vistas de Spanner. Para ver más para obtener información sobre las vistas de Spanner, consulta Acerca 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 asignar un nombre al 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 llamada 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

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

Además, Spanner verifica el query especificas con la resolución de nombres estricta, lo que significa que todos los nombres de objetos del esquema usados en la consulta deben estar calificados de manera que identifiquen sin ambigüedades un único objeto de esquema. Por ejemplo, en los ejemplos que siguen, la columna SingerId en la tabla Singers debe calificarse como Singers.SingerId.

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

Por ejemplo, supongamos que la tabla Singers se define de la siguiente manera:

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 de invocador de la siguiente manera:

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 tipos de datos para garantizar la estabilidad en todo el esquema cambios, 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, asigna de forma explícita el tipo de datos de todas las columnas de la tabla en la consulta que define la vista. Cuando lo hagas, 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 convertirse en no válido 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 deje de ser válida, puedes transmitir las columnas de forma explícita a los tipos de datos deseados, como se indica a continuación:

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

Nota: Si creas una vista de derechos del definidor, usa el control de acceso detallado junto con la vista. De lo contrario, la vista de derechos del definidor no agregará ningún control de acceso adicional.

Como usuario de control de acceso detallado, debes tener el privilegio SELECT en una vista. Para otorgar el privilegio SELECT a 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 desde una función de base de datos, haz lo siguiente:

GoogleSQL

REVOKE SELECT ON VIEW SingerNames FROM ROLE Analyst;

PostgreSQL

REVOKE SELECT ON TABLE SingerNames FROM Analyst;

Cómo consultar una vista

La forma de consultar los derechos de un invocador o la vista de derechos de un definidor es la misma. Sin embargo, según el tipo de seguridad de la vista, Spanner puede o no necesitar comparar los objetos de esquema a los que se hace referencia en la vista el rol de base de datos de la principal que invocó la consulta.

Consulta la vista de derechos de un invocador

Si una vista tiene derechos de invocación, el usuario debe tener privilegios en todas los objetos de esquema de la vista para 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 la vista de derechos de un definidor

Si una vista tiene derechos de definición, un usuario puede consultarla sin necesidad. privilegios en los objetos subyacentes, siempre y cuando otorgues el rol necesario el SELECT en la vista.

En el siguiente ejemplo, un usuario con el rol de base de datos de analista quiere consultar la vista SingerNames. Sin embargo, se le niega 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 proporcionarle al analista acceder a la vista, pero no quieres proporcionarles acceso a Singers puedes reemplazar el tipo de seguridad de la vista por derechos de quien definió. Después de reemplazar el tipo de seguridad de la vista, otorga acceso a la vista al rol de analista. Ahora el usuario puede consultar la vista SingerNames a pesar de que no tienen acceso a la tabla Singers.

SELECT COUNT(SingerID) as SingerCount 
FROM SingerNames;

Cómo reemplazar una vista

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

Reemplazar una vista es similar a descartarla y volver a crearla. Cualquier acceso los otorgamientos otorgados a la vista inicial deben volver a otorgarse después de reemplazar el vista.

Para reemplazar la vista de derechos de un invocador por la vista de derechos de un 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 descarta una vista, los roles de base de datos con privilegios en ella ya no tienen el acceso a los datos. Para borrar una vista, usa la sentencia DROP VIEW.

DROP VIEW SingerNames;

Obtén información sobre una vista

Para obtener información sobre las vistas de una base de datos, consulta las tablas en su esquema INFORMATION_SCHEMA.

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

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

Para comprobar la definición de vista y el tipo de seguridad de una vista llamada ProductSoldLastWeek:

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