Gestión de permisos de datos a través de vistas

Escenario

Después de conectarse a un clúster como usuario dbadmin, cree una tabla de ejemplo customer.

1

CREATE TABLE customer (id bigserial NOT NULL, province_id bigint NOT NULL, user_info varchar, primary key (id)) DISTRIBUTE BY HASH(id);

Inserte datos de prueba en la tabla de ejemplo customer.

1
2

INSERT INTO customer(province_id,user_info) VALUES (1,'Alice'),(1,'Jack'),(2,'Jack'),(3,'Matu');
INSERT 0 4

Consulte la tabla customer.

1
2
3
4
5
6
7
8

SELECT * FROM customer;
 id | province_id | user_info
----+-------------+-----------
  3 |           2 | Jack
  1 |           1 | Alice
  2 |           1 | Jack
  4 |           3 | Matu
(4 rows)

Implementación

Puede crear una vista para cumplir con los requisitos del escenario anterior. El procedimiento es el siguiente:

1. Después de conectarse a un clúster como usuario dbadmin, cree las vistas v1 y v2 para las provincias 1 y 2 en modo dbadmin.
   Ejecute la sentencia CREATE VIEW para crear la vista v1 para consultar los datos de la provincia 1.

   1 2	CREATE VIEW v1 AS SELECT * FROM customer WHERE province_id=1;

   Ejecute la sentencia CREATE VIEW para crear la vista v2 para consultar los datos de la provincia 2.

   1 2	CREATE VIEW v2 AS SELECT * FROM customer WHERE province_id=2;

2. Cree los usuarios u1 y u2.

   1 2	CREATE USER u1 PASSWORD '*********'; CREATE USER u2 PASSWORD '*********';

3. Ejecute la sentencia GRANT para otorgar el permiso de consulta de datos al usuario de destino.

   Otorgue el permiso en el esquema de vista de destino a u1 y u2.

   1	GRANT USAGE ON schema dbadmin TO u1,u2;

   Otorgue a u1 el permiso para consultar los datos de la provincia 1 en la vista v1.

   1	GRANT SELECT ON v1 TO u1;

   Otorgue a u2 el permiso para consultar los datos de la provincia 2 en la vista v2.

   1	GRANT SELECT ON v2 TO u2;

Verificación del resultado de la consulta

• Cambie a u1 para conectarse al clúster.

  1	SET ROLE u1 PASSWORD '*********';

  Consulte la vista v1. u1 solo puede consultar los datos de la vista v1.

  1 2 3 4 5 6

  SELECT * FROM dbadmin.v1; id | province_id | user_info ----+-------------+----------- 1 | 1 | Alice 2 | 1 | Jack (2 rows)

  Si u1 intenta consultar datos en la vista v2, se muestra la siguiente información de error:

  1 2	SELECT * FROM dbadmin.v2; ERROR: SELECT permission denied to user "u1" for relation "dbadmin.v2"

  El resultado muestra que el usuario u1 solo puede ver los datos de la provincia 1 (province_id = 1).

• Utilice u2 para conectarse al clúster.

  1	SET ROLE u2 PASSWORD '*********';

  Consulte la vista v2. u2 solo puede consultar los datos de la vista v2.

  1 2 3 4 5

  SELECT * FROM dbadmin.v2; id | province_id | user_info ----+-------------+----------- 3 | 2 | Jack (1 row)

  Si u2 intenta consultar datos en la vista v1, se muestra la siguiente información de error:

  1 2	SELECT * FROM dbadmin.v1; ERROR: SELECT permission denied to user "u2" for relation "dbadmin.v1"

  El resultado muestra que el usuario u2 solo puede ver los datos de la provincia 2 (province_id = 2).