Effect of CASCADE Keyword on UNDER Privileges

If you revoke the Under privilege on a typed table with the CASCADE option, the Under privilege is removed from the specified user, and any subtables created under the typed table by that user are dropped from the database.

If you revoke the Under privilege on a named ROW type with the CASCADE option when that data type is in use, the REVOKE fails. This exception to the default behavior of the CASCADE option occurs because the database server supports the DROP ROW TYPE statement with the RESTRICT keyword only.

For example, assume that user jeff creates a ROW type named rtype1 and grants the Under privilege on that ROW type to user mary. User mary now creates a ROW type named rtype2 under ROW type rtype1 and grants the Under privilege on ROW type rtype2 to user andy. Then user andy creates a ROW type named rtype3 under ROW type rtype2.

If user jeff now tries to revoke the Under privilege on ROW type rtype1 from user mary with the CASCADE option, the REVOKE statement fails, because ROW type rtype2 is still in use by ROW type rtype3.