如何通過視圖管控資料許可權 - 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