My ultimate goal is to allow administration for the purposes of password resets with minimal possible permissions given. It seems to me that with the UserAdmin role, or even UserAdmin with SecurityAdmin, the “Users and Privileges part of MySQL Workbench should work; but it doesn’t. Does anyone know what custom role privileges and/or GRANT permissions are required to allow the use of Users and Privileges?
It seems the minimum I need to get it to “work” is
CREATE USER AND
SELECT; but that doesn’t help me do what I want to do, which is be able to assign some privileges (SELECT/EXECUTE) on different schemas. This does not seem to work as intended because even when I have the
RELOAD, or even
SHOW DATABASES in addition to
EXECUTE, I still get an error when trying to add or delete SELECT privileges on a specific schema that says: `(u’Error revoking privileges for @ in schema , ‘You must have the GRANT OPTION privilege, and you must have the privileges that you are reovking’).
Consider I have followed what the error implies (unless I am missing something), it should work, but it doesn’t.
So to clarify my question: I would like to know the least amount of administration privileges required for an administrative user to be able to:
1. create a user and/or modify passwords
2. assign SELECT and EXECUTE privileges to that user on specific schemas (without Workbench throwing errors that aren’t necessarily true — as even with the error, it creates the privileges, but it won’t remove them [i.e. I can’t delete it after I make it under that user]).
As far as I can tell, after much trial and error and process of elimination: the titles under “Administrative Roles” with associated descriptions, such as UserAdmin and SecurityAdmin, do not work as their descriptions describe. The only thing that accomplishes the above is to grant DBManager, which is what I was trying to avoid.
A partial answer: for just resetting Passwords, I was able to get it to function with CREATE USER, RELOAD, SELECT, and UPDATE. For actually granting schema privileges to the user, it continues to give an error up to DBA. For example, it successfully grants the privilege, but then throws the error about revoking (as mentioned above), then if you close the “Users and Privileges” window and reopen it, you can see the schema grant actually applied and saved, but you can’t delete it (same revoke error and it doesn’t stick like when adding). I still don’t understand what privilege is driving the error; much trial and error and process of elimination was done.