Manage data permissions using views - AnalyticDB - Centro de documentación de Alibaba Cloud

Las vistas permiten exponer un subconjunto filtrado de una tabla a cuentas específicas sin otorgarles acceso directo a la tabla subyacente. Esto convierte a las vistas en un mecanismo eficaz para el control de acceso a nivel de fila: cada cuenta consulta únicamente la vista sobre la que tiene permiso, y AnalyticDB for MySQL aplica el filtro definido en dicha vista.

Funcionamiento

Al crear una vista con SQL SECURITY DEFINER, esta se ejecuta con los permisos de su creador (el definidor), no con los de la cuenta que realiza la consulta. Esto implica lo siguiente:

Las cuentas con permiso SELECT sobre una vista pueden consultarla sin necesidad de tener permisos sobre la tabla subyacente.
AnalyticDB for MySQL aplica automáticamente la cláusula WHERE de la vista, por lo que las cuentas solo ven las filas que esta expone.

Esto permite aislar los datos entre cuentas mediante la creación de una vista por cada partición de datos y la asignación de acceso a cada cuenta a su vista correspondiente.

Escenario

Una tabla customer almacena registros de clientes de varias provincias. El objetivo es otorgar a user1 acceso exclusivo a los registros de la Provincia 1 (province_id=1) y a user2 acceso exclusivo a los registros de la Provincia 2 (province_id=2).

Definición de la tabla:

CREATE TABLE `customer` (
  `id`          bigint AUTO_INCREMENT,
  `province_id` bigint NOT NULL,
  `user_info`   varchar,
  PRIMARY KEY (`id`)
) DISTRIBUTED BY HASH(`id`);

Datos de prueba:

INSERT INTO customer (province_id, user_info)
VALUES (1, 'Tom'), (1, 'Jerry'), (2, 'Jerry'), (3, 'Mark');

La tabla completa contiene cuatro filas distribuidas en tres provincias:

+---------------------+-------------+-----------+
| id                  | province_id | user_info |
+---------------------+-------------+-----------+
| 1369417242420617216 |           1 | Tom       |
| 1369417242424811520 |           1 | Jerry     |
| 1369417242424811522 |           3 | Mark      |
| 1369417242424811521 |           2 | Jerry     |
+---------------------+-------------+-----------+

Configuración del control de acceso basado en vistas

Requisitos previos

Antes de empezar, asegúrese de contar con lo siguiente:

Un clúster de AnalyticDB for MySQL con la base de datos adb_demo
Privilegios de administrador para crear vistas y otorgar permisos
Las cuentas user1 y user2 ya creadas (consulte CREATE USER)

Paso 1: Crear vistas para cada provincia

Cree una vista por provincia. Cada vista filtra la tabla customer según un valor específico de province_id. La cláusula SQL SECURITY DEFINER hace que la vista se ejecute con los permisos del creador, de modo que las cuentas que la consultan no necesitan acceso directo a customer.

-- View for Province 1: exposes only rows where province_id = 1
CREATE SQL SECURITY DEFINER VIEW v1 AS
  SELECT * FROM customer WHERE province_id = 1;

-- View for Province 2: exposes only rows where province_id = 2
CREATE SQL SECURITY DEFINER VIEW v2 AS
  SELECT * FROM customer WHERE province_id = 2;

Para consultar la sintaxis completa y los parámetros de CREATE VIEW, consulte CREATE VIEW.

Paso 2: Otorgar acceso a cada cuenta sobre su vista

Otorgue el permiso SELECT sobre la vista en lugar de sobre la tabla subyacente. De este modo, cada cuenta solo podrá leer las filas expuestas por su vista asignada, y la tabla subyacente customer permanecerá inaccesible para ambas cuentas.

-- user1 can query v1 (Province 1 data only).
-- Granting on the view, not on customer, prevents user1 from accessing other provinces.
GRANT SELECT ON v1 TO user1;

-- user2 can query v2 (Province 2 data only).
-- Granting on the view, not on customer, prevents user2 from accessing other provinces.
GRANT SELECT ON v2 TO user2;

Comprobación del resultado

Conéctese a la base de datos adb_demo con cada cuenta y ejecute una sentencia SELECT sobre la vista asignada. El siguiente ejemplo muestra tanto la consulta permitida como la denegada para cada cuenta.

Como user1:

-- user1 has SELECT on v1. This query returns Province 1 rows only.
SELECT * FROM v1;

+---------------------+-------------+-----------+
| ID                  | PROVINCE_ID | USER_INFO |
+---------------------+-------------+-----------+
| 1369417242420617216 |           1 | Tom       |
| 1369417242424811520 |           1 | Jerry     |
+---------------------+-------------+-----------+

-- user1 has no permission on v2. This query is denied.
SELECT * FROM v2;

ERROR 1815 (HY000): [9001, 2021083114191719216818804803453965343] : Access Denied

Como user2:

-- user2 has SELECT on v2. This query returns Province 2 rows only.
SELECT * FROM v2;

+---------------------+-------------+-----------+
| ID                  | PROVINCE_ID | USER_INFO |
+---------------------+-------------+-----------+
| 1369417242424811521 |           2 | Jerry     |
+---------------------+-------------+-----------+

-- user2 has no permission on v1. This query is denied.
SELECT * FROM v1;

ERROR 1815 (HY000): [9001, 2021083114191719216818804803453965343] : Access Denied

Pasos siguientes

Escale este patrón a provincias adicionales mediante la creación de una vista por cada valor de province_id y la asignación del permiso SELECT sobre dicha vista a la cuenta correspondiente.
Para revocar el acceso, ejecute REVOKE SELECT ON <view_name> FROM <account_name>.
Para consultar la referencia completa de opciones de creación de vistas, consulte CREATE VIEW.