DROP USER Statement (drop_user_statement) 

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.

Syntax

<drop_user_statement> ::= DROP USER <user_name> [<cascade_option>]

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.

Dropping a user of the database user class DBA does not affect any database users created by this user. The database administrator (SYSDBA user) becomes the new owner of these database users.

<cascade_option>

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

·        If RESTRICT specified as CASCADE option 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.

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.