16.20 - REVOKE - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Control Language

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1149-162K

Purpose

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.