Revoke access for one of the many databases in MySQL

Question

I have multiple MySQL databases on the server, DB 1, mysql and DB 2. I created a user User A. I want to revoke access for User A only for a single DB, say mysql, as this would have several log tables. I tried basic REVOKE syntax which is not working.

revoke select on mysql.* from user@localhost;

Please note that the revoke should only happen for mysql DB and not for DB 1 and DB 2.

can you show output for```SHOW GRANTS FOR user@localhost;``` — spike 王建, Aug 19 '20 at 05:23
+------------------------------------------------------+ | Grants for user@localhost | +------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' | +------------------------------------------------------+ 1 row in set (0.00 sec) — Sachindra, Aug 19 '20 at 05:52
Please add your MySQL version. Before 8.0.16, you cannot do that, see [How to “subtract” privileges in MySQL](https://stackoverflow.com/q/8131849), so you would need to remove the "all" rights, and then add privileges for each and every db. — Solarflare, Aug 19 '20 at 06:33
also, as per the help link you gave, "REVOKE INSERT, UPDATE ON mysql.* FROM user@localhost;", it says "ERROR 1141 (42000): Unknown error 1141" — Sachindra, Aug 19 '20 at 06:50

Solarflare · Accepted Answer · 2020-08-19T07:10:53.387

Before MySQL 8.0.16, you cannot substract privileges,

The privileges that a user holds for a database, table, column, or routine are formed additively as the logical OR of the account privileges at each of the privilege levels, including the global level. It is not possible to deny a privilege granted at a higher level by absence of that privilege at a lower level.

Trying to do so should give you an error like

Error Code: 1141. There is no such grant defined for user 'user' on host 'localhost'

You have to add permissions for all databases individually, e.g. allow db1 and db2 specifically, instead of allowing all and removing mysql.

Starting with MySQL 8.0.16, you can finally remove access to individual databases (but for now only on databases, not on e.g. individual tables or columns):

As of MySQL 8.0.16, it is possible to explicitly deny a privilege granted at the global level by revoking it for particular databases, if the partial_revokes system variable is enabled:

GRANT SELECT, INSERT, UPDATE ON *.* TO u1;
REVOKE INSERT, UPDATE ON db1.* FROM u1;

The partial_revokes system variable has to be set for this:

Enabling this variable makes it possible to revoke privileges partially. Specifically, for users who have privileges at the global level, partial_revokes enables privileges for specific schemas to be revoked while leaving the privileges in place for other schemas. For example, a user who has the global UPDATE privilege can be restricted from exercising this privilege on the mysql system schema. (Or, stated another way, the user is enabled to exercise the UPDATE privilege on all schemas except the mysql schema.) In this sense, the user's global UPDATE privilege is partially revoked.

The bold marked part is exactly what you are trying to do.

Thank You, this was really helpful. Just a basic question: by default, does the user has insert/update/delete privilege for mysql DB in MySQL ? — Sachindra, Aug 19 '20 at 09:48
By default, a user has no permissions at all, you have to grant them explicitely (e.g. add grants). Just before 8.0.16, you have to add a permissions for each database (and cannot express "all database except mysql" shorter) — Solarflare, Aug 19 '20 at 10:35
if I mention "GRANT ALL PRIVILEGES ON *.* TO user@localhost", does that provide update privilege to that user for mysql DB as well? Or is there a restriction not to update the mysql DB and its tables by any other user than root ? — Sachindra, Aug 19 '20 at 12:29
Yes, `GRANT ALL PRIVILEGES ON *.* TO` creates a superuser, which can do everything, including everything in the mysql database. The mysql database is not different than any other database in the system (well, at least with respect to grants and such). — Solarflare, Aug 19 '20 at 13:44
if we hv an encrypted DB and we wish to store encryption key, keyring, where do we update keyring path for our db?? — Sachindra, Sep 16 '20 at 20:39

Revoke access for one of the many databases in MySQL

1 Answers1