WITH GRANT OPTION keywords

The WITH GRANT OPTION keywords convey the privilege or role to a user with the right to grant the same privileges or role to other users.

You create a chain of privileges that begins with you and extends to user as well as to whomever user subsequently conveys the right to grant privileges. If you include WITH GRANT OPTION, you can no longer control the dissemination of privileges.

The following example grants the Alter and Select privileges to user mark on the cust_seq sequence object, with the ability to grant those privileges to others:

GRANT ALL ON cust_seq TO mark WITH GRANT OPTION;

If you revoke from user the privilege that you granted using the WITH GRANT OPTION keyword, you sever the chain of privileges. That is, when you revoke privileges from user, you automatically revoke the privileges of all users who received privileges from user or from the chain that user created (unless user, or the users who received privileges from user, were granted the same set of privileges by someone else).

The following examples illustrate this situation. You, as the owner of the table items, issue the following statements to grant access to user mary:

REVOKE ALL ON items FROM PUBLIC;
GRANT SELECT, UPDATE ON items TO mary WITH GRANT OPTION;

User mary uses her privilege to grant users cathy and paul access to the table:

GRANT SELECT, UPDATE ON items TO cathy;
GRANT SELECT ON items TO paul;

Later you revoke the access privileges for user mary on the items table:

REVOKE SELECT, UPDATE ON items FROM mary;

This single statement effectively revokes all privileges on the items table from users mary, cathy, and paul. If you want to create a chain of privileges with another user as the source of the privilege, use the AS grantor clause.

In HCL OneDB™, the WITH GRANT OPTION keywords are valid only for users. They are not valid when a role is the grantee of a privilege or of another role. You cannot specify WITH GRANT OPTION in a statement that grants a privilege to the PUBLIC group.

The Database Server Administrator cannot include the WITH GRANT OPTION keywords in the GRANT EXTEND or GRANT DBSECADM statements. The DBSA cannot delegate to another user the authorization to grant the built-in EXTEND or DBSECADM roles. If more than one user needs either of these authorizations, they should be included in the DBSA group when the database server is installed.

In addition to the GRANT DBSECADM statement, none of the other security administration options of the GRANT statement support the WITH GRANT OPTION keywords. For more information about these statements and their syntax, see Security Administration Options.