DROP ROLE statement

Use the DROP ROLE statement to remove a user-defined role from the database.

This statement is an extension to the ANSI/ISO standard for SQL.

Syntax


1  DROP ROLE? IF EXISTS
1 role
1 'role'
Element Description Restrictions Syntax
role Name of the role to be dropped Must be registered in the local database. When a role name is enclosed in quotation marks, it is case sensitive. Owner name

Usage

Either the DBA or a user to whom the role was granted with the WITH GRANT OPTION keywords can issue the DROP ROLE statement. (Like a user name, a role is an authorization identifier, not a database object, so a role has no owner.)

If you include the optional IF EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if no role of the specified name is registered in the current database.

After you drop a role, no user can grant or enable the dropped role, and any user who had been assigned the role loses its privileges (such as table-level privileges or routine-level privileges) when the role is dropped, unless the same privileges were granted to PUBLIC or to the user individually. If the dropped role was the default role of a user, the default role for that user becomes NULL.

The following statement drops the role engineer: 
DROP ROLE engineer;

You cannot use the DROP ROLE statement to drop a built-in role, such as the EXTEND or DBSECADM roles of HCL OneDB™.