Permission management by using views - AnalyticDB - Alibaba Cloud Documentation Center

This topic describes how to grant the corresponding permissions to different accounts to query different data in the same table.

Scenarios and requirements

A table named customer that contains customers from different provinces is created. Sample statement:

Create Table `customer` (
 `id` bigint AUTO_INCREMENT,
 `province_id` bigint NOT NULL,
 `user_info` varchar,
 primary key (`id`)
) DISTRIBUTED BY HASH(`id`);

Test data is inserted into the customer table. Sample statement:

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

The following data is returned after you execute the SELECT statement to query data in the customer table:

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

The requirements are for you to grant the user1 account the permissions to query data of Province 1 (province_id=1) and grant the user2 account the permissions to query data of Province 2 (province_id=2).

Implementation methods

You can perform the following steps to meet the preceding requirements:

Execute the following statements to create a view named v1 for Province 1 and a view named v2 for Province 2.
- Execute the following statement to create the v1 view to query data of Province 1:
```
CREATE SQL SECURITY DEFINER VIEW v1 AS SELECT * FROM customer WHERE province_id=1;
```
- Execute the following statement to create the v2 view to query data of Province 2:
```
CREATE SQL SECURITY DEFINER VIEW v2 AS SELECT * FROM customer WHERE province_id=2;
```
Note
For more information about the parameters used in view creation statements, see CREATE VIEW.
After the views are created, execute the following statements to grant the corresponding permissions to the accounts.
Note
For information about how to create an account, see CREATE USER.
- Execute the following statement to grant the user1 account the permissions to query data of Province 1 by using the v1 view:
```
GRANT SELECT ON v1 TO user1;
```
- Execute the following statement to grant the user2 account the permissions to query data of Province 2 by using the v2 view:
```
GRANT SELECT ON v2 TO user2;
```

Result validation

If you use the user1 account to connect to the adb_demo database of an AnalyticDB for MySQL cluster, the user1 account can query only data of the v1 view. Sample statement:

SELECT * FROM v1;

The following data is returned after you execute the preceding statement:

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

If you use the user1 account to query data of the v2 view, the following error message appears:

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

If you use the user2 account to connect to the adb_demo database of an AnalyticDB for MySQL cluster, the user2 account can query only data of the v2 view. Sample statement:

SELECT * FROM v2;

The following data is returned after you execute the preceding statement:

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

If you use the user2 account to query data of the v1 view, the following error message appears:

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