Alan McDonald wrote:
I have been using RDB$ADMIN role for a while. I can grant it to users, they then have the ability to create and delete other users and grant roles to them. But I see now that RDB$ADMIN is not enough to revoke roles from all users. I get an exception saying the USERNAME was not the user which granted ROLENAME to OTHERUSERNAME. Now it’s a task to find the user who actually granted the role
SYSDBA does not overwrite this either. SYSDBA logged as any role including RDB$ADMIN does not give me the ability to revoke the role. It must be the user (not just the RDB$ADMIN role) who granted the role. So is this the way it’s meant to happen?
Can anyone tell me which system table gives me a clue as to who granted the role so I can get that person to login and revoke it?
Dmitry Yemanov answers:
SYSDBA/RDB$ADMIN doesn't "owerwrite" it, but it may be used for that provided that you specify the GRANTED BY clause for the REVOKE statement. And yes, it's the way it's supposed to happen.
To find out who granted the role you can query the RDB$USER_PRIVILEGES table, search for 'M' (membership) privileges.