REVOKE (SQL Form) Statement | SQL Data Control Language | Teradata Vantage - REVOKE (SQL Form) - Advanced SQL Engine - Teradata Database

Revokes one or more explicit privileges on a database, user, table, view, procedure, UDF, function mapping, or macro from a role, group of roles, user, or group of users or removes the GRANT option from explicit privileges.

REVOKE takes effect immediately when revoked users issue their next request.

For information about revoking the NONTEMPORAL privilege, see Teradata Vantage™ - Temporal Table Support , B035-1182 .

Required Privileges

To revoke a privilege, you must first have the privileges to grant it. You must either own the database object, or someone must first grant the privilege, either automatically or explicitly, to you, either directly or by means of a role, using WITH GRANT OPTION.

If the object is a view or macro, the submitting user also must have the applicable privileges, WITH GRANT OPTION, on the objects referenced by the view or macro.

Syntax

REVOKE [ GRANT OPTION FOR ] {
  { ALL [ PRIVILEGES ] | [ ALL BUT ] privilege [,...] } ON object |
    map_privilege [,...] |
    role_privilege [,...] |
    profile_privilege [,...] |
    zone_privilege [,...]
  }
  { TO | FROM }
  { user_revokee [,...] | PUBLIC | role_revokee [,...] } [;]

object

{ { database_name | user_name | role_name | PUBLIC } |

  { database_name. | user_name. } object_name |

  object_name |

  PROCEDURE [ database_name. | user_name. ] procedure_name |

  SPECIFIC FUNCTION [ database_name. | user_name. ] specific_function_name |

  FUNCTION [ database_name. | user_name. ] function_name
    ( [ function_parameter [,...] ] ) |

  TYPE [SYSUDTLIB.] UDT_name
}

user_revokee

[ALL] user_name

role_revokee

[ database_name. | user_name. ] role_name

function_parameter

[ parameter_name ] data_type

data_type

{ INTEGER | SMALLINT | BIGINT | BYTEINT | DATE |

  { TIME | TIMESTAMP } [ (fractional_seconds_precision) ] [WITH TIME ZONE] |

  INTERVAL YEAR [(precision)] [TO MONTH] |

  INTERVAL MONTH [(precision)] |

  INTERVAL DAY [(precision)]
    [TO { HOUR | MINUTE | SECOND [(fractional_seconds_precision)] }] |

  INTERVAL HOUR [(precision)]
    [TO { MINUTE | SECOND [(fractional_seconds_precision)] }] |

  INTERVAL MINUTE [(precision)] [TO SECOND [(fractional_seconds_precision)]] |

  INTERVAL SECOND [ (precision [, fractional_seconds_precision ] ) |

  PERIOD (DATE) |

  PERIOD ( { TIME | TIMESTAMP } [(precision)] [WITH TIME ZONE] ) |

  REAL |

  DOUBLE PRECISION |

  FLOAT [ (integer) ] |

  NUMBER [ ( { integer | * } [, integer]... ) ] |

  { DECIMAL | NUMERIC } [ ( integer [, integer]... ) ] |

  { CHAR | BYTE | GRAPHIC } [ (integer) ] |

  { VARCHAR | CHAR VARYING | VARBYTE | VARGRAPHIC } [ (integer) ] |

  LONG VARCHAR |

  LONG VARGRAPHIC |

  { BINARY LARGE OBJECT | BLOB | CHARACTER LARGE OBJECT | CLOB }
    ( integer [ G | K | M ] ) |

  [SYSUDTLIB.] { XML | XMLTYPE } [ ( integer [ G | K | M ] ) ]
    [ INLINE LENGTH integer ] |

  [SYSUDTLIB.] JSON [ ( integer [ K | M ] ) ] [ INLINE LENGTH integer ]
    [ CHARACTER SET { UNICODE | LATIN } | STORAGE FORMAT { BSON | UBJSON } ] |

  [SYSUDTLIB.] ST_GEOMETRY [ (integer [ K | M ]) ] [ INLINE LENGTH integer ] |

  [SYSUDTLIB.] DATASET [ (integer [ K | M ]) ]
    [ INLINE LENGTH integer ] [storage_format] |

  [SYSUDTLIB.] { UDT_name | MBR | ARRAY_name | VARRAY_name }
}

Syntax Elements

GRANT OPTION FOR

Only the WITH GRANT OPTION authority is removed from the specified privilege set for the specified grantees for the corresponding explicit privileges they have on the specified database object.

REVOKE GRANT OPTION FOR revokes the ability to grant the specified privilege set to others, but does not revoke the explicit privileges themselves from the specified users or roles.

This option does not apply to grantees that are roles.

ALL

PRIVILEGES

Revoke from the specified user or role all explicitly granted non-MONITOR database, but not table, privileges that can be granted on the specified object, and that are held, either implicitly or explicitly, WITH GRANT OPTION by the user executing the REVOKE.

PRIVILEGES is optional.

REVOKE ALL does not revoke the INDEX and REFERENCES privileges. To revoke these privileges, you must do so explicitly.

To revoke all explicit database privileges for a user, including MONITOR, the revoker must perform at least two statements, shown as follows:

REVOKE ALL PRIVILEGES ON  object 
FROM  user_name;

REVOKE MONITOR PRIVILEGES
FROM  user_name;

ALL PRIVILEGES includes all explicit database privileges.

If you specify ALL PRIVILEGES, then only the explicit privileges held with grant authority are revoked from the grantee.

ANSI/ISO SQL requires ALL to be followed by the keyword PRIVILEGES.

ALL BUT

Revoke all explicit database privileges from the specified database object, except those specified in the privilege set, that can be granted on the specified object and that are held, either implicitly or explicitly, WITH GRANT OPTION by the user performing the REVOKE statement.

privilege

One or more of the privileges listed in Privilege Dictionary.

INSERT, REFERENCES, SELECT, and UPDATE have separate table- and column-level options. See GRANT (SQL Form).

You cannot grant the UPDATE privilege on a GENERATED ALWAYS identity column.

The ANSI/ISO SQL:2011 standard does not support REVOKE on a database or user, or ALL BUT. These are Teradata extensions to the ANSI/ISO SQL-2011 standard.

You can specify any combination of privileges; however, the user submitting the request must itself have all of the specified privileges, either implicitly or explicitly, WITH GRANT OPTION.

If DATABASE, FUNCTION, MACRO, PROCEDURE, PROFILE, ROLE, TABLE, TRIGGER, USER, VIEW, or ZONE is specified without CREATE or DROP, both CREATE and DROP are revoked.

If you specify CHECKPOINT, then the privilege is revoked both for the SQL statement and for the HUT commands DUMP and RESTORE.

If you specify either DUMP or RESTORE individually, then the system revokes the privilege to perform the corresponding HUT command only.

If you specify RESTORE, then the system also revokes the privileges to perform the HUT commands ROLLBACK, ROLLFORWARD, and DELETE JOURNAL.

You cannot revoke privileges on a trigger, only on its containing database or its subject table.

map_privilege

Revoke create and drop map privileges from users and roles.

You can specify the following privileges:

• CREATE MAP
• DROP MAP
• MAP

MAP is shorthand, not a separate privilege. Specify MAP to revoke both the CREATE MAP and DROP MAP privileges.

role_privilege

One of the following privileges:

• CREATE ROLE
• DROP ROLE
• ROLE

  ROLE is shorthand for both CREATE ROLE and DROP ROLE, not a separate privilege.

Role privileges can only be revoked from a set of users or a role. They cannot be revoked from an object.

profile_privilege

One of the following privileges:

• CREATE PROFILE
• DROP PROFILE
• PROFILE

  PROFILE is shorthand for both CREATE PROFILE and DROP PROFILE, not a separate privilege.

Profile privileges can only be revoked from a set of users or a role. They cannot be revoked from an object.

zone_privilege

One of the following privileges:

• CREATE ZONE
• DROP ZONE
• ZONE

  ZONE is shorthand for both CREATE ZONE and DROP ZONE, not a separate privilege.

Zone privileges can only be revoked from a set of users or a zone. They cannot be revoked from an object.

For information about implementing Teradata Secure Zones, see Teradata Vantage™ - Advanced SQL Engine Security Administration, B035-1100.

database_name

user_name

role_name

PUBLIC

Name of a database, user, or role on which the explicitly granted privileges are to be revoked, or PUBLIC. All objects contained by this database or user space are affected.

database_name.object_name

user_name.object_name

Name of the immediately owning database and optionally and the name of the object or the user name and the name of the object (table, view, procedure, join index, or macro) on which explicitly granted privileges are being revoked.

Only the specified object, and not all objects in the database or user space, is affected by revoking the privilege set.

object_name

Name of the table, view, join index, procedure, function, function mapping, macro, or authorization name from which explicitly granted privileges are to be revoked.

You should always qualify object names when revoking privileges because the system checks database names before it checks object names.

• If the object name is not qualified and the system finds a database with that name, Teradata Database assumes it is a database name.
• If the object name is not qualified and no database having that name is found, Teradata Database assumes it is an object within the current default database.
• If neither a database nor an object is found with the specified name, Teradata Database returns an error to the requestor.

PROCEDURE

database_name

user_name

procedure_name

Name of the procedure from which privileges are to be revoked.

database_name and user_name are optional. The procedure name can be qualified by its containing database or user when necessary.

SPECIFIC FUNCTION

database_name

user_name

specific_function_name

Specific name of the UDF from which privileges are to be revoked.

database_nameand user_name are optional. The specific function name can be qualified by its containing database or user when necessary.

FUNCTION

database_name

user_name

function_name

Name of the UDF from which privileges are to be revoked.

database_name and user_name are optional. The specific function name can be qualified by its containing database or user when necessary.

parameter_name

data_type

Parenthetical comma-separated list of data types and optional parameter names for the variables to be passed to the UDF. This is used to uniquely identify overdetermined function names.

BLOB and CLOB types must be represented by a locator. For a description of locators, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146. Teradata Database does not support in-memory LOB parameters: an AS LOCATOR phrase must be specified for each LOB parameter and return value.

You must specify opening and closing parentheses, even if no parameters are passed to the function.

The data type associated with each parameter is the type of the parameter or returned value. All Teradata Database data types are valid. Character data can also specify a CHARACTER SET clause that specifies the server character set used for the parameter.

TYPE

SYSUDTLIB.

UDT_name

Name of a UDT on which a privilege set is to be revoked.

SYSUDTLIB. is optional.

The various TYPE privileges can be revoked as follows:

• You can revoke UDTMETHOD only from the SYSUDTLIB database.
• You can revoke UDTTYPE only from the SYSUDTLIB database.
• You can revoke UDTUSAGE from the SUSUDTLIB database, from TYPE, or from both.

TO

FROM

Recipient of the REVOKE statement action, which can be a set of users, ALL users, a role, or PUBLIC.

You can specify a maximum of 25 names per REVOKE request.

For compatibility, ANSI/ISO SQL requires you to specify FROM rather than TO.

ALL user_name

User from whom explicit privileges are revoked. You can specify up to 25 user names.

ALL user_name specifies that the privileges are to be granted to or revoked from the specified user, and every user owned by that user now and in the future.

If you do not specify ALL user_name, then the revocation does not cascade through the hierarchy.

ALL user_name is a Teradata extension to ANSI/ISO SQL.

PUBLIC

The explicit privileges are to be revoked from all currently defined Teradata Database users and are not to be granted to future users.

ALL DBC is equivalent to PUBLIC.

database_name

user_name

Containing database or user for role_name if something other than the current database or user.

role_name

Name of a role from which privileges are revoked.

You can specify up to 25 role names.