Question 17

Explanation

Here are the three true statements regarding the WITH GRANT OPTION clause:

1. If you revoke from user the privilege that you granted using the WITH GRANT OPTION keyword, you sever the chain of privileges.
   – This is true. Revoking a privilege granted with WITH GRANT OPTION causes a cascading revoke, removing that privilege from any users the grantee had passed it on to.

2. It can be used to pass on privileges to other users by the grantee.
   – Correct. This is the primary purpose of the clause: it allows the grantee to grant the same privilege to others.

3. It cannot be used to pass on privileges to PUBLIC by the grantee.
   – Also true. The grantee can grant privileges to specific users or roles, but not to the PUBLIC group unless they have broader administrative rights.

The other two statements are false:

• The grantee does not need the GRANT ANY OBJECT PRIVILEGE system privilege to use WITH GRANT OPTION; they only need to have received the privilege with that option themselves.

• Similarly, the grantee can include WITH GRANT OPTION when passing on a privilege, but only if they were granted it with that option in the first place.

Granting Object Privileges

You also use the GRANT statement to grant object privileges to roles and users. To grant an object privilege, you must fulfill one of the following conditions:

You own the object specified.

You possess the GRANT ANY OBJECT PRIVILEGE system privilege that enables you to grant and revoke privileges on behalf of the object owner.

The WITH GRANT OPTION clause was specified when you were granted the object privilege by its owner.

Specifying the GRANT OPTION

Specify WITH GRANT OPTION to enable the grantee to grant the object privileges to other users and roles. The user whose schema contains an object is automatically granted all associated object privileges with the GRANT OPTION. This special privilege allows the grantee several expanded privileges:

The grantee can grant the object privilege to any users in the database, with or without the GRANT OPTION, or to any role in the database.

If both of the following are true, the grantee can create views on the table and grant the corresponding privileges on the views to any user or role in the database. The grantee receives object privileges for the table with the GRANT OPTION. The grantee has the CREATE VIEW or CREATE ANY VIEW system privilege.

The GRANT OPTION is not valid when granting an object privilege to a role. Oracle prevents the propagation of object privileges through roles so that grantees of a role cannot propagate object privileges received by means of roles.

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 on the an object (sequence or system), with the ability to grant those privileges to others:

GRANT ALL ON object TO user 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).