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.
This statement is ANSI SQL:2011 compliant.
- 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.