Background documentationDROP USER Statement (drop_user_statement) Locate this document in the navigation structure

 

The DROP USER statement (drop_user_statement) deletes a database user definition. The metadata for the database user to be dropped is deleted from the database catalog.

Structure

Syntax Syntax

  1. <drop_user_statement> ::=
  DROP USER <user_name> [<cascade_option>]
End of the code.
Examples

SQL Tutorial, Database Users and Their Privileges

Explanation

The current database user must have owner authorization over the database user that is to be dropped.

The specified database user cannot be logged on to the database system when the DROP USER statement is executed.

Deleting a database user of the database user class DBA does not affect any database users created by this user. The database system administrator becomes the new owner of these database users.

All database objects that the database user being dropped has created in schemas, which he or she does not own, are retained. The owner of the schema in question becomes the new owner of these objects.

<cascade_option>

  • If no CASCADE option is specified, or the CASCADE option CASCADE is specified, all schemas, synonyms and tables of the database user to be dropped, including indexes, privileges, view tables and so on based on these objects, are deleted along with the user.

  • If the CASCADE option RESTRICT is specified, and the database user to be dropped does not belong to a user group, but is the owner of synonyms or tables, the DROP USER statement will fail.