WITH GRANT OPTION vs. WITH ADMIN OPTION

Role or system privileges are granted using WITH ADMIN OPTION, and table-specific privileges are granted using WITH GRANT OPTION.

Revoking a privilege granted using WITH GRANT will cascade revoke any and all privileges assigned by the target user to other users, but revoking a privilege granted using WITH ADMIN will only revoke privileges for the target user, leaving intact all the othwer users target user might have granted privileges to.

Example:

WITH GRANT OPTION

DBA can grant object privileges using with grant option (select,update,insert) to A, and A, in turn, grants those privileges to B.

DBA--------->A--------->B

DBA can only revoke the privileges from A but not from B, because WITH GRANT OPTION implies that we can only revoke those privileges that we have granted. Only the user who granted privileges can revoke them. But if DBA revoke A's privileges, the priveleges will cascade revoke from B as well.



WITH ADMIN OPTION

We grant the system privileges to A using with admin option(CREATE TABLE,CREATE SESSION)

DBA------>A-------->B

Here DBA can revoke privileges from either A or B. But if DBA only revokes privileges from A, they won't be cascade revoked from B.

Users with ADMIN OPTION for a system privilege can revoke the privilege of any other database user, the revoker does not have to be the same user who originally granted the privilege.