MySQL Privilege Restriction Using Partial Revokes

User_J6P3H

MySQL version 5.7.32。

I have a user 'test'@'%' in MySQL database.

mysql> show grants for test;

+--------------------------------------------------------------------------------+

| Grants for [email protected]%                               |

+--------------------------------------------------------------------------------+

| 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！

Dave Stokes-MySQL Community Team-Oracle

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