如何通过视图管控数据权限 - 云原生数据仓库AnalyticDB

本文介绍如何通过视图实现给不同的账号授予查询同一表中不同数据的权限，来提供数据的安全性。

场景需求

已创建了一张包含不同省份客户的表customer，建表语句如下：

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

往表customer中插入测试数据，语句如下：

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

通过SELECT语句可查询到表中当前数据如下：

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

要求user1仅能查看省份1（即province_id=1）的数据，而user2仅能查看省份2（即province_id=2）的数据。

实现方式

您可以通过如下操作步骤实现上述场景需求：

为省份1和省份2分别创建一个视图v1和v2，语句如下：
- 查询省份1数据的视图创建语句。
```
CREATE SQL SECURITY DEFINER VIEW v1 AS SELECT * FROM customer WHERE province_id=1;
```
- 查询省份2数据的视图创建语句。
```
CREATE SQL SECURITY DEFINER VIEW v2 AS SELECT * FROM customer WHERE province_id=2;
```
说明
创建视图语句中各参数的详细介绍，请参见CREATE VIEW。
视图创建成功后，通过GRANT语句将对应的数据查询权限授予给目标用户，语句如下：
说明
创建账号的方法，请参见CREATE USER。
- 授予user1通过v1视图查询省份1数据的权限：
```
GRANT SELECT ON v1 TO user1;
```
- 授予user2通过v2视图查询省份2数据的权限：
```
GRANT SELECT ON v2 TO user2;
```

查询结果验证

使用user1账号连接AnalyticDB for MySQL集群的adb_demo数据库后，user1仅能查询到视图v1数据，查询语句如下：

SELECT * FROM v1;

查询结果如下：

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

若user1想要查询视图v2中的数据，则会出现如下错误：

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

使用user2账号连接AnalyticDB for MySQL集群的adb_demo数据库后，user2仅能查询到视图v2数据，查询语句如下：

SELECT * FROM v2;

查询结果如下：

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

若user2想要查询视图v1中的数据，也会出现如下错误：

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