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

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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 reassumes its status as the default role, but it has a different role ID number than it had before being dropped.

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.