I have a customer who is looking to consolidate all role grants and revokes to a single user (the roles themselves, not the access rights of those roles). However, they don't want that user to be the creator of the roles, nor do they want that user to be the one who grants the access rights to them. The idea is that the DBA's would determine when new roles are needed, create the role, grant it privileges, and this other administrative user would be the only person who has the ability to grant them to other users. The only way I have found to be able to grant the privileges of assigning a role you didnt create is by doing this:
GRANT rolename TO username WITH ADMIN OPTION;
The username here would be the administrative user that we want to manage role memberships. However, this also grants all of the access rights of all these roles to that user, which essentially makes them a super user, when we just wanted an administrative user with little to no acccess to any real data in the system.
Am I missing something obvious here? Is there some way to go about this that I'm not aware of?
You could encapsulate this within a stored procedure and grant EXEC PROC to the Role Managing Admin user.
I am unsure if this helps your requirement though.