DROP ROLE SQL Statement | Teradata Vantage - DROP ROLE - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
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 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.