3.17.1 Specification format and rules for the DROP ROLE statement

The DROP ROLE statement deletes a role.

Organization of this subsection

(1) Specification format

DROP-ROLE-statement ::= DROP ROLE role-name [drop-behavior]
 
  drop-behavior ::= {CASCADE | RESTRICT}

● role-name

Specifies the name of the role to be deleted.

Role names that are not defined cannot be specified.

For rules on specifying role names, see 6.1.4 Specifying names.

● drop-behavior

drop-behavior ::= {CASCADE | RESTRICT}

This specification takes effect only in either of the following cases:

When the deletion of a role causes SELECT privileges held by HADB users to be revoked.
When the deletion of a role causes REFERENCES privileges held by HADB users to be revoked.

If the drop behavior is omitted, CASCADE is assumed.

CASCADE:

Specify this if you want to delete a role even if either of the following conditions is met:

Viewed tables defined by using the SELECT privileges to be revoked exist.

In this case, the corresponding viewed tables will be invalidated. Additionally, viewed tables dependent on the invalidated viewed tables will also be invalidated.
Referential constraints defined by using the REFERENCES privileges to be revoked exist.

In this case, the corresponding referential constraints will be deleted.

RESTRICT:

Specify this if you want the DROP ROLE statement to result in an error if either of the following conditions is met:

Viewed tables defined by using the SELECT privileges to be revoked exist.
Referential constraints defined by using the REFERENCES privileges to be revoked exist.

To execute the DROP ROLE statement, DBA privileges and CONNECT privileges are required.

Only roles defined by yourself (the HADB user with the authorization identifier currently connected to the HADB server) can be deleted. Roles defined by other HADB users cannot be deleted.
If a role is deleted, the role usage privileges granted to HADB users are revoked. Accordingly, all access privileges granted to HADB users through the deleted role are also revoked.
If deleting a role changes the access privileges of an HADB user who is currently connected to the HADB server, the changed access privileges take effect at the following timing:
- The next time the HADB user executes a transaction

Example

Delete the role (ROLE01).

DROP ROLE "ROLE01"