Controlling the Scope of REVOKE with the RESTRICT Option
The RESTRICT keyword causes the REVOKE statement to fail when any
of the following dependencies exist:
- A view depends on a Select privilege that you are attempting to revoke.
- A foreign-key constraint depends on a References privilege that you attempt to revoke.
- You attempt to revoke a privilege from a user who subsequently granted this privilege to another user or to a role.
REVOKE does not fail if it specifies a user who has the right to grant the privilege to others
but has not exercised that right. For example, assume that user clara specifies WITH GRANT
OPTION when they grant the Select privilege on the customer table to user ted. Further
assume that user ted, in turn, grants the Select privilege on the customer table to
user tania. The following statement that clara issued has no effect, because
ted has used his authority to grant the Select privilege:
REVOKE SELECT ON customer FROM ted RESTRICT;
In contrast, if user ted does not grant the Select privilege
to tania or to any other user, the same REVOKE statement succeeds.
Even if ted does grant the Select privilege to another user,
either of the following statements succeeds:
REVOKE SELECT ON customer FROM ted CASCADE;
REVOKE SELECT ON customer FROM ted;