> I wrote:
> > What I would like to do is write some code that will go through
> > RDB$USER_PRIVILEGES and clean it up automatically, remove all
> > the redundant entries and so on. I am wanting to clean-up user
> > roles, all other privileges have been more tightly managed.
>
> I finally got back to look at this problem again.
>
> For anyone that is interested... the above suggestion is quite
> safe because it quite simply does not work (there are system
> triggers in place that prevent direct updates, giving you an
> error if you try).
>
> What does work is to make sure you do your revoke calls using
> SYSDBA. Revoke as SYSDBA will revoke privileges that were
> granted by other users - a feature that does not work for any
> other user, not even the database owner.
>
> The fact that only SYSDBA or the grantor can revoke a privilege
> is stated Helen's book, but was something I had forgotten... I
> had gotten used to relying on the database owner (not SYSDBA in
> this case).
>
> [The above is referring to an old FB v1.5 app... I believe it
> all applies to recent FB versions too except that FB v2.5 is
> getting the ability to specify a grantor.]
>
> --
> Geoff Worboys
> Telesis Computing

Since SYSDBA is the only person who can create new accounts, I've never
found it worthwhile even creating a situation where DBOWNER grants user
privleges. DBOWNER for me, only ever grants rights to object(s) to a role,
the SYSDBA, while creating the user, will grant the role(s) to the user.
Therefore DBOWNER grants are kept in the hands of the developer, while user
grants are kept in the hands of the application/database operator/system
owner.
The only exception to this comment is were roles are not used. And my
counter to that would be to say that anyone in the situation is probably
making things more difficult to manage than they need to be. It's always
better to create roles even if the user logging never knows he/she is using
it. My little security app does this cleanup on request, also removing
grants from users who no longer exist on the server where the database is
deployed.
Alan