Skip to Main Content
Forums

MySQL Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

MySQL Privilege Restriction Using Partial Revokes

User_J6P3HNov 23 2021

MySQL version 5.7.32。

I have a user 'test'@'%' in MySQL database.
mysql> show grants for test;
+--------------------------------------------------------------------------------+
| Grants for test@% |
+--------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO 'test'@'%' |
+--------------------------------------------------------------------------------+

mysql> desc employees.salaries;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| salary | int(11) | NO | | NULL | |
| from_date | date | NO | PRI | NULL | |
| to_date | date | NO | | NULL | |
+-----------+---------+------+-----+---------+-------+

I want to revoke select privilege which on employees.salaries.salary.
mysql> revoke select(salary) on employees.salaries from test;
ERROR 1147 (42000): There is no such grant defined for user 'test' on host '%' on table 'salaries'

This privilege Cannot be revoked directly.
Is there any other way to achieve？
Thanks！

Comments

Your GRANT ALL to test for all tables provides that account with access to all schemas on the server.
If you want to keep test out of salaries but allow others, you first need to set up the grants on the employees.salaries table before blocking the test account. You have to do the equivalent of building the fence around the property before barring them or there is no way to block their access.
https://dev.mysql.com/doc/refman/8.0/en/grant.html#grant-column-privileges

dvohra21

The SELECT privilege can only be revoked at the level it is granted. Use
mysql> REVOKE SELECT ON employees.salaries FROM 'test'@'%';

1 - 2

Post Details

Added on Nov 23 2021
2 comments
384 views