Oracle7 Server SQL Reference

DROP USER

Purpose

To remove a database user and optionally remove the user's objects.

Prerequisites

You must have DROP USER system privilege.

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the user's creation label or you must satisfy one of the following criteria:

If the user's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges

If the user's creation label is lower than your DBMS label, you must have WRITEDOWN system privilege.

If the user's creation label and your DBMS label are not comparable, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

Syntax

Keywords and Parameters

user

is the user to be dropped.

CASCADE

drops all objects in the user's schema before dropping the user. You must specify this option to drop a user whose schema contains any objects.

Usage Notes

Oracle7 does not drop users whose schemas contain objects. To drop such a user, you must perform one of the following actions:

explicitly drop the user's objects before dropping the user

drop the user and objects together using the CASCADE option

If you specify the CASCADE option and drop tables in the user's schema, Oracle7 also automatically drops any referential integrity constraints on tables in other schemas that refer to primary and unique keys on these tables. The CASCADE option causes Oracle7 to invalidate, but not drop, the following objects in other schemas:

views or synonyms for objects in the dropped user's schema

stored procedures, functions, or packages that query objects in the dropped user's schema

Oracle7 does not drop snapshots on tables or views in the user's schema or roles created by the user.

Example I

If BRADLEY's schema contains no objects, you can drop BRADLEY by issuing the statement:

DROP USER bradley

Example II

If BRADLEY's schema contains objects, you must use the CASCADE option to drop BRADLEY and the objects:

DROP USER bradley CASCADE