Revoke access to database from user with global permission?

Question

Consider a user with global database access:

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD 'xxx' WITH GRANT OPTION

Due to legacy issues and number of databases involved, I'd like to REVOKE permission from this user for a small subset of databases and leave the rest alone. Of course, this does not work:

REVOKE ALL PRIVILEGES ON mydatabase.* FROM 'myuser'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'myuser' on host '%'

I understand why that happens, because the access is global, but does MySQL have an exclusion mechanism I can use?

If not, can anyone point to a script where I can pull all database names in order to make the grants more granular? What are the pitfalls of dissecting a global grant into separate per-database grants?

Answer 1

Correct, MySQL does not have a privilege negation. If the user has privileges on *.* then you must revoke their global privilege and assign privileges specifically on the schemas you want them to access.

You can get a list of schemas using either of these SQL queries:

SHOW SCHEMAS;

Or

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME NOT IN (...the ones you don't want...);