3.14.1 Specification format and rules for the DROP USER statement

(1) Specification format

DROP-USER-statement ::= DROP USER authorization-identifier [drop-behavior]
 
  drop-behavior ::= {CASCADE | RESTRICT}

To Page Top

(2) Explanation of specification format

authorization-identifier

Specify the authorization identifier of the HADB user to be deleted.

Note the following rules for specifying an authorization identifier:

• If you want to use lowercase letters, enclose the authorization identifier in double quotation marks ("). When not enclosed in double quotation marks, lowercase letters will be treated as uppercase.

  Example: DROP USER adbuser01 ...

  In this case, the authorization identifier is treated as ADBUSER01.

• Because an authorization identifier is specified as a name, we recommend that you enclose it in double quotation marks (").

For details about the rules for specifying an authorization identifier, see 6.1.4 Specifying names.

• drop-behavior

drop-behavior ::= {CASCADE | RESTRICT}

Specifies whether to delete the HADB user if either of the following conditions is met:

• The HADB user to be deleted owns a schema.

• The HADB user to be deleted has granted access privileges to other HADB users.

The following table describes the possible specifications for drop-behavior.

Specification of drop-behavior	Description	Handling of viewed tables in other schemata	Handling of foreign keys in other schemata
If drop-behavior is omitted	The HADB user is deleted even if either of the following conditions is met: The HADB user to be deleted owns a schema. The HADB user to be deleted has granted access privileges to other HADB users. When the DROP USER statement is run, the schemata owned by the HADB user to be deleted are also deleted. Also, all access privileges that have been granted to other HADB users are revoked. In addition, all dependent privileges for the revoked access privileges are revoked.	If viewed tables defined in other schemata depend on the tables that will be deleted by the DROP USER statement, those dependent viewed tables are invalidated.	If the tables that will be deleted by the DROP USER statement are referenced by foreign keys defined in other schemata, those foreign keys are also deleted.
If CASCADE is specified		If viewed tables defined in other schemata depend on the tables that will be deleted by the DROP USER statement, those dependent viewed tables are also deleted.
If RESTRICT is specified	The DROP USER statement results in an error if either of the following conditions is met: The HADB user to be deleted owns a schema. The HADB user to be deleted has granted access privileges to other HADB users.	The viewed tables in other schemata are not affected because the DROP USER statement results in an error.	The foreign keys in other schemata are not affected because the DROP USER statement results in an error.

To Page Top

(3) Privileges required at execution

To execute the DROP USER statement, the DBA privilege and the CONNECT privilege are required.

To Page Top

(4) Rules

1. It is possible to delete HADB users other than yourself.

2. The HADB user whose authorization identifier is currently connected to the HADB server cannot be deleted.

3. If the deleted HADB user had granted another HADB user the DBA privilege, the CONNECT privilege, or the schema definition privilege, those privileges are not revoked.

4. If the HADB user to be deleted has granted access privileges to other HADB users, all the granted access privileges are revoked. The dependent privileges for the revoked access privileges are also revoked. Therefore, revoking access privileges might affect viewed tables and referential constraints. For details, see (4) Rules in 3.17.2 Revoking access privileges.

5. The HADB users having the audit privilege cannot be deleted. To delete HADB users who have the audit privilege, ask an HADB user who has the audit admin privilege to revoke the audit privilege of the HADB users, and then delete them.

To Page Top

(5) Examples

Example 1

Delete HADB user ADBUSER01.

DROP USER "ADBUSER01" CASCADE

Example 2

Delete HADB user ADBUSER01. However, if ADBUSER01 owns a schema, make the DROP USER statement result in an error.

DROP USER "ADBUSER01" RESTRICT

To Page Top