Hitachi

Hitachi Advanced Database SQL Reference


3.14.1 Specification format and rules for the DROP USER statement

The DROP USER statement deletes an HADB user.

Organization of this subsection

(1) Specification format

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

(2) Explanation of specification format

[Figure] 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.

(3) Privileges required at execution

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

(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.

(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