GRANT OPTION Clause
To enable an authorization ID to grant a privilege to another authorization ID, specify the WITH GRANT OPTION clause. The owner of an object can grant any privilege to any authorization ID (or to public). The authorization ID to whom the privilege is granted WITH GRANT OPTION can grant only the specified privilege. Any authorization ID can grant privileges that were granted to PUBLIC WITH GRANT OPTION to any other authorization ID.
The GRANT OPTION cannot be specified for database privileges.
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 user laura did not specify the WITH GRANT OPTION clause, user evan cannot authorize another user to select data from tony.mytable. User laura can grant SELECT privilege, but cannot grant, for example, INSERT privilege. If user tony revokes SELECT permission from user laura (using the REVOKE statement), user tony must specify how the DBMS must handle any dependent privileges that user laura has issued.
The choices are:
REVOKE...CASCADE
Revokes all dependent privileges. In the preceding example, SELECT permission is revoked from user evan.
REVOKE...RESTRICT
Does not revoke specified privileges if there are dependent privileges. In the preceding example, SELECT privileges are not revoked from user laura because her grant to user evan depends on the privileges she received from user tony.