DROP ROLE SQL Statement | Teradata Vantage - 17.10 - DROP ROLE - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

Drops a specified role.

A DROP ROLE statement removes the privileges on database objects that were granted to the dropped role.

The effect of a role drop (including EXTERNAL roles) is immediate. Users who are logged on with the dropped role as an active role lose the access privileges that were granted to the role.

A DROP ROLE statement only eliminates the specified role. If you no longer need roles that were nested within a dropped role, you must drop them individually.

Default role settings for all users with the dropped role as their default role do not reset to NULL. Affected users receive no warnings or errors the next time they log on. The system does not use the obsolete default role for privileges validation.

If a dropped default role is later recreated, it automatically becomes the default role again, but it has a different role ID number than it had before being dropped.

ANSI Compliance

This statement is ANSI SQL:2011 compliant.

Required Privileges

The following users can drop a specified role, whether it is a database role or an external role.
  • User DBC.
  • A user who has the DROP ROLE privilege.
  • A user who has been granted the specified role WITH ADMIN OPTION.
  • A user whose current role has the specified role as a nested role, and the nested role was granted to the current role WITH ADMIN OPTION.

The role creator does not have the implicit privilege to drop a role. A role creator who does not have the DROP ROLE privilege and who loses WITH ADMIN OPTION on a role cannot drop the role.