MySQL Bugs: #16692: Allow deny in privileges

Bug #16692	Allow deny in privileges
Submitted:	21 Jan 2006 1:12	Modified:	9 May 2018 10:12
Reporter:	Michael Caldwell	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S4 (Feature request)
Version:	5.0.16	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

Description:
I believe the request verification should go through the entire ACL. I have a system where i need an app_admin to create a separate database for each user that is accessed by app_user. app_user needs to have access to all the new databases, but I do not want app_admin to have grant privileges and i don't want either of them to have access to the mysql database.

How to repeat:
I would like to be able to do the following grant create, drop on *.* to app_admin, revoke all on mysql.* from app_admin, grant select, insert, update, delete on *.* to app_user, revoke all on mysql.* from app_user.

Suggested fix:
If the verification system went through user, db, table, and column privileges all the time I could grant at the user level and revoke at the db level.

I would like to second this request.

I am right now trying to write a script to publish an application.  The old database needs to become read-only.

I would love to be able to do this by just running something similar to
REVOKE INSERT,UPDATE,DELETE ON old_db.* FROM *.*;

Instead of having to hunt through each grant table to ensure nobody but root has those privs.

Could have a runtime option to swap the allow,deny order similar to Apache.
and/or
Have additional 'revoke' tables that limit access instead of granting.

William,  Try --partial-revokes=ON  option.

https://dev.mysql.com/doc/refman/8.0/en/partial-revokes.html

I'd like it to work for individual tables/columns but it doesn't.