Restrict versus Cascade
The RESTRICT and CASCADE options specify how OpenSQL handles dependent privileges. The CASCADE option (default) directs OpenSQL to revoke the specified privileges plus all privileges that depend on the privileges being revoked. The RESTRICT option directs OpenSQL not to revoke the specified privilege if there are any dependent privileges.
The owner of an object can grant privileges on that object to any user. Privileges granted by users who do not own the object are dependent on the privileges granted WITH GRANT OPTION by the owner.
For example, if user jerry owns the employees table, he can grant tom the ability to select data from the table and to enable other users to select data from the table:
grant select on employees to tom with grant option;
User tom can now enable another user to select data from the employees table:
grant select on employees to sylvester with grant option;
The grant tom conferred on sylvester is dependent on the grant the table's owner jerry conferred on tom. In addition, sylvester can enable other users to select data from the employees table.
• To remove his grant to tom and all grants tom may have issued, jerry must specify REVOKE...CASCADE:
revoke select on employees from tom cascade;
As a result of this statement, the select privilege granted by tom to sylvester is revoked, as are any select grants issued by sylvester to other users conferring select privilege for the employees table.
• To prevent dependent privileges from being revoked, jerry must specify revoke... restrict:
revoke select on employees from tom restrict;
Because there are dependent privileges (tom has granted select privilege on the employees table to sylvester), this revoke statement will fail, and no privileges will be revoked.
The RESTRICT and CASCADE parameters have the same effect whether you are revoking a specific privilege or the grant option for a specific privilege. In either case, RESTRICT prevents the operation from occurring if there are dependent privileges, and CASCADE causes dependent privileges to be deleted. When you revoke a grant option with CASCADE, all dependent privileges are revoked, not just the grant option portion of the dependent privileges.
Last modified date: 01/30/2023