REVOKE rescinds explicit privileges from one or more users, proxy users, databases, or roles. The privileges might have been conferred either automatically or by a previous GRANT statement.
REVOKE has forms that differ in function and syntax:
| REVOKE Form | Purpose |
|---|---|
| REVOKE (Monitor Form) | Revoke performance monitoring. |
| REVOKE (Role Form) | Revoke role membership to users and other roles. |
| REVOKE (SQL Form) | Revoke access to, creation of, or logging of, database objects. |
| REVOKE LOGON | Revoke system logon privileges. |
| REVOKE MAP | Revoke existing contiguous or sparse maps from users and roles. |
| REVOKE ZONE | Revoke zone guest status from users or roles. |
| REVOKE CONNECT THROUGH | Revoke the ability to connect as a proxy permanent or proxy application user through a trusted user. |
Privileges That REVOKE Can and Cannot Remove
The REVOKE statements operate on explicit privileges recorded in the DBC.AccessRights table. Only those explicit privileges that have been granted automatically or explicitly can be revoked.
In most cases, implicit privileges only permit the user holding them to grant them to others, not to perform the SQL statements that correspond to those privileges. Generally, you must have explicit privileges to perform access protected SQL statements. The main exceptions to this rule are privileges on views, macros, and procedures.
REVOKE (SQL Form) and REVOKE (Monitor Form) Differences
The SQL and MONITOR forms of REVOKE are separate statements. To revoke all privileges for a user, including MONITOR, the grantor must perform both of the following statements:
REVOKE ALL PRIVILEGES ON object FROM user_name; REVOKE MONITOR PRIVILEGES FROM user_name;
Both statements assume that you have the appropriate privileges, either implicitly or explicitly, WITH GRANT OPTION.
ANSI Compliance
REVOKE (Role Form) and REVOKE (SQL Form) are ANSI/ISO SQL:2011-compliant. The other forms of REVOKE are extensions to the ANSI/ISO SQL:2011 standard.