REVOKE rescinds explicit privileges from one or more users, proxy users, databases, or roles. The privileges may have been granted 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 CONNECT THROUGH | Revoke the ability to connect as a proxy permanent or proxy application user through a trusted user. |
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.
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.
Typically, implicit privileges permit the user holding those privileges to grant those privileges to others, not to perform the SQL statements that correspond to those privileges. You must typically have explicit privileges to perform access protected SQL statements. 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.