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`)
) DISTRIBUTE 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 namedv2
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. - Execute the following statement to create the v1 view to query data of Province 1:
- 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 thev1
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 thev2
view:GRANT SELECT ON v2 TO user2;
- Execute the following statement to grant the
Result validation
- If you use the
user1
account to connect to theadb_demo
database of an AnalyticDB for MySQL cluster, theuser1
account can query only data of thev1
view. Sample statement:
The following data is returned after you execute the preceding statement:SELECT * FROM v1;
+---------------------+-------------+-----------+ | ID | PROVINCE_ID | USER_INFO | +---------------------+-------------+-----------+ | 1369417242420617216 | 1 | Tom | | 1369417242424811520 | 1 | Jerry | +---------------------+-------------+-----------+
If you use theuser1
account to query data of thev2
view, the following error message appears:ERROR 1815 (HY000): [9001, 2021083114191719216818804803453965343] : Access Denied
- If you use the
user2
account to connect to theadb_demo
database of an AnalyticDB for MySQL cluster, theuser2
account can query only data of thev2
view. Sample statement:
The following data is returned after you execute the preceding statement:SELECT * FROM v2;
+---------------------+-------------+-----------+ | ID | PROVINCE_ID | USER_INFO | +---------------------+-------------+-----------+ | 1369417242424811521 | 2 | Jerry | +---------------------+-------------+-----------+
If you use theuser2
account to query data of thev1
view, the following error message appears:ERROR 1815 (HY000): [9001, 2021083114191719216818804803453965343] : Access Denied