Alan McDonald wrote:

> > > unsuccessful metadata update SYSDBA is not grantor of Role on MANAGER
> > > to 0S0ASDFASDF.
> >
> > You have to use GRANTED BY here:
> >
> > revoke manager from 0S0ASDFASDF granted by rdb$admin
>
> So we're saying SYSDBA has to first make system table enquiries to find out
> who granted the role and then make the adjustment to the revoke statement?
> That doesn't sound right or basically logical to me. It's tough enough
> already without SYSDBA being forced to jump thru all those hoops. SYSDBA can
> delete everyone from the security database, and delete all the objects no
> matter who made them but can't revoke a role until he finds out who granted
> it?

Yes. AFAIU, the reasoning was that a user can be granted the same privilege multiple times, by different users, and REVOKE should only remove the privilege instance (== row in RDB$USER_PRIVILEGES) that was granted by the current user (or the user specified after GRANTED BY).

FWIW, this complies with the SQL standard. If SYSDBA (or RDB$ADMIN, or the database owner) wants to remove this kind of multiple-grantor but otherwise equal privileges now without finding out the grantors first, it has to be done with a searched delete statement on RDB$USER_PRIVILEGES. Which is a hack of course, like any direct manipulation of metadata.

It would be better if this were possible in SQL, e.g. by implementing CASCADE for REVOKE statements (like PostgreSQL has done).


Cheers,
Paul Vinkenoog