REVOKE System Privilege Statement

Syntax

REVOKE [ADMIN OPTION FOR] system_privilege [,...]
FROM userID [,...]

Parameters

• system_privilege – must be an existing system privilege.
• userID – must be the name of an existing user or role that has a login password. Separate multiple userIDs with commas.
• ADMIN OPTION FOR – each system_privilege must currently be granted to each userID specified with administrative privileges.
  Note: This clause revokes only the administrative privileges of the system privilege; the system privilege itself remains granted. However, if the system privilege was originally granted with the WITH ADMIN ONLY OPTION clause, the ADMIN OPTION FOR clause completely revokes the system privilege. Under this scenario, use of the ADMIN OPTION FOR clause is not required to revoke administrative privileges.

Examples

• Example 1 – revokes the BACKUP DATABASE system privilege from user Jim.

  REVOKE BACKUP DATABASE FROM Jim 

• Example 2 – assuming the BACKUP DATABASE system privilege was originally granted to user Jim with the WITH ADMIN OPTION clause, this example revokes the ability to administer the BACKUP DATABASE system privilege from user Jim. The ability to perform tasks authorized by the system privilege remains. However, if the BACKUP DATABASE system privilege was originally granted to user Jim with the WITH ADMIN ONLY OPTION clause, this example removes all permissions to the system privilege from user Jim.

  REVOKE ADMIN OPTION FOR BACKUP DATABASE FROM Jim 

Usage

Depending on how the system privilege was initially granted, using the ADMIN OPTION FOR clause when revoking a system privilege has different results. If you the system privilege was originally granted with the WITH ADMIN OPTION clause, including the ADMIN OPTION FOR clause in the revoke statement revokes only the ability to administer the system privilege (that is, grant the system privilege to another user). The ability to actually use the system privilege remains. However, if the system privilege was originally granted with the WITH ADMIN ONLY OPTION clause, including the ADMIN OPTION FOR clause in the revoke statement is semantically equivalent to revoking the entire system privilege. Finally, if the system privilege was originally grant with the WITH NO ADMIN OPTION clause, and the ADMIN OPTION FOR clause is included in the revoke statement, nothing is revoked because there were no administrative system privileges granted in the first place.

Standards

Permissions