Authorization Requirements

Unless you are a Security Administrator or the Super ID, you can revoke only those privileges that you have previously granted to the user. If you are a Security Administrator, then you are exempt from the above restriction. In addition, successfully revoking a privilege will revoke all instances of that privilege granted by any Security Administrators. It will not affect any owner-derived grants of that privilege (that is the owner-derived hierarchy of grants is not affected). However, Security Administrators can revoke owner-derived grants using REVOKE BY authid-grantor. Since Security Administrators may be the target of an owner-derived grant, they may hold any privilege derived WITH GRANT OPTION, in which case they may revoke that privilege like any ordinary user.

If you are the Super ID, then your revoke privileges depend on the Security Administrator's Group. If the Security Administrator's Group is empty, then you may revoke any privilege on any object. Such revokes behave like a REVOKE BY authid-grantor where the authid-grantor is the object owner.

If the Super ID is designated as a Security Administrator, then the Super ID has the same REVOKE privileges as any other Security Administrator. However, in this case, if BY authid-grantor is omitted, then the implied grantor is the Super ID instead of the object owner and successfully revoking a privilege will revoke all instances of that privilege granted by any Security Administrators (that is the Super ID behaves like any other Security Administrator ID). It will not affect any owner-derived grants of that privilege (that is the owner-derived hierarchy of grants is not affected). If the Security Administrator's Group is not empty and the Super ID is not designated as a Security Administrator, the Super ID will have the same restrictions as any ordinary user with respect to the REVOKE statement.

The authorization rights for revoking privileges on a schema are the same as the rights for granting and revoking privileges on objects. If the Security Administrator's Group is not empty and the Super ID is not designated as a Security Administrator, the Super ID will have the same restrictions as any ordinary user with respect to the REVOKE statement.