Revoking privileges granted WITH GRANT OPTION

If you revoke from user privileges or a role that you granted using the WITH GRANT OPTION keywords, you sever the chain of privileges granted by that user.

Thus, when you revoke privileges from users or from a role, you also revoke the same privilege resulting from GRANT statements in the following contexts:
  • Issued by your grantee
  • Allowed because your grantee specified the WITH GRANT OPTION clause
  • Allowed because subsequent grantees granted the same privilege or role using the WITH GRANT OPTION clause

The WITH GRANT OPTION clause is only valid in GRANT statements that assign privileges to specific users. The grantee cannot be the PUBLIC group or a role.

The following examples show the revocation of privileges. Suppose you, as the owner of the table items, issue the following statements to grant access privileges to user mary: 
REVOKE ALL ON items FROM PUBLIC;
GRANT SELECT, UPDATE ON items TO mary WITH GRANT OPTION;
User mary then uses her new privilege to grant users cathy and paul access to the items table: 
GRANT SELECT, UPDATE ON items TO cathy;
GRANT SELECT ON items TO paul;
Later you revoke privileges on the items table from user mary: 
REVOKE SELECT, UPDATE ON items FROM mary;

This single statement effectively revokes all privileges on the items table from users mary, cathy, and paul.

The CASCADE keyword has the same effect as this default condition.