REVOKE System Privilege Statement

Quick Links:

Syntax

REVOKE [ ADMIN OPTION FOR ] <system_privilege_name> [,...]
   FROM <userID> [,...]

Parameters

(back to top)

system_privilege_name 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

(back to top)

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

(back to top)

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

(back to top)

SQL–other syntaxes are vendor extensions to ISO/ANSI SQL grammar.
SAP Database products–syntax is not supported by SAP ASE.

Permissions

(back to top)

Requires administrative privilege over the system privilege being revoked.