The Grant Option
To enable a user to grant a privilege to another user, specify the WITH GRANT OPTION clause.
For example, if user tony, creates a table called mytable, and issues the following statement:
grant select on tony.mytable to laura
with grant option;
user laura can select data from tony.mytable and can authorize user evan to select data from tony.mytable by issuing the following statement:
grant select on tony.mytable to evan;
Because laura did not specify the WITH GRANT OPTION clause, evan cannot authorize another user to select data from tony.mytable.
The owner of an object can grant any privilege to any user (or to public). The user to whom the privilege is granted with grant option can grant only the specified privilege. In the preceding example, laura can grant select privilege but cannot grant, for example, insert privilege.
In the previous example, the second grant (to evan) depends on the first grant (to laura). If tony revokes select permission from laura (using the REVOKE statement), tony must specify how OpenSQL should handle dependent grants that laura has issued. The choices are:
• Revoke with cascade—Revokes all dependent grants; in the preceding example, select permission will be revoked from user, evan.
• Revoke with restrict—Do not revoke specified grant if there are dependent grants. In the preceding example, select permission will not be revoked from laura because her grant to evan depends on the grant she received from tony.
For more details, see
REVOKE and in the
Database Administrator Guide.Last modified date: 01/30/2023