REVOKE Statement | SQL Data Control Language | Teradata Vantage - 17.05 - REVOKE - Teradata Database

Teradata Vantageā„¢ - SQL Data Control Language

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1149-170K

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.

Implicit privileges are determined by ownership and cannot be revoked. You can affect implicit privileges by using the GIVE statement to change ownership. For more information, see GIVE.

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.