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 she grants 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;