Oracle Drop Users

As a database administrator, you will encounter such instances where removing a specific user from an Oracle database is beneficial, whether trying to clean up the database and remove invalid objects or optimizing your database performance.

This post will discuss how you can use the DROP USER command in Oracle to delete a given user from the database.

Oracle Drop User Statement

As stated, the DROP USER command enables you to remove a user account from the database. This also includes removing all associated schema objects from the database.

The following is a code snippet that demonstrates the syntax of the drop user command in the Oracle server:

DROP USER username [CASCADE];

We start by invoking the DROP USER command, followed by the username of the user that we wish to remove from the database.

The CASCADE option allows Oracle to remove all associated schema objects such as tables, views, functions, etc., for that user.

You can also overcome this by removing all the linked schema objects before removing the user account. For simplicity, use the CASCADE option and allow the Database engine to remove the associated schema objects before dropping the target user account.

In some cases, Oracle may fail to remove the schema objects if other objects in the database reference them. In such a case, Oracle only invalidates the target references before removing the user. This releases the target account for removal without any restriction or error.

Keep in mind that this can affect the various objects such as materialized views. Similarly, the database engine will not remove the roles that were created by the target user.

Example DROP USER Command Illustration

The following are examples demonstrating how to drop a database user in Oracle.

Create a Database User

Start by logging in to the Oracle database and create a new user:

$ sqlplus sys as sysdba;

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 26 02:53:44 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Once logged into the server, create a user for demonstration purposes. You can skip this step if you already have a target user that you wish to remove.

SQL> CREATE USER python IDENTIFIED BY password124;
USER created.

The previous command creates a user with no schema objects. In this case, we can run the DROP USER command without getting any error or initially removing the associated objects.

Example 1: Oracle Remove User without Associated Schema Objects

In this example, we demonstrate how to remove a user without any linked schema objects by calling the DROP USER command as follows:

SQL> DROP USER python;
USER dropped.

As you can see, we successfully removed the Python user without removing the schema objects or calling the CASCADE parameter.

Example 2: Oracle Remove User with Schema Objects

To demonstrate how to use the DROP USER command on a user with associated schema objects, let us start by creating a user with the permissions to create a table and session.

An example statement is as follows:

SQL> CREATE USER python IDENTIFIED BY password124 QUOTA UNLIMITED ON users;

USER created.
SQL> GRANT CREATE TABLE, CREATE SESSION TO python;
GRANT succeeded.

The previous statements a Python user with an unlimited quota on the users’ table. Since this is a test account, you can set any required quota size.

Once the user is setup, log in as that account and create a new table as follows:

SQL> CONNECT python/password124
Connected.

Once connected, create a table:

CREATE TABLE sample_data(
id NUMBER,
first_name varchar2(50),
ip_address varchar2(20),
btc_address varchar2(50),
credit_card varchar2(50),
identifier varchar2(40),
CONSTRAINT sample_pk PRIMARY KEY(id)
);

Next, exit the Python’s user session.

SQL> disconnect
Disconnected
SQL> CONNECT sys AS sysdba
Enter password:
Connected.

In this instance, if we attempt to remove the Python user by calling the DROP USER command, we get a message which tells us that we need to cascade the associated objects as follows:

SQL> DROP USER python;

Error Message:

ERROR at line 1:
ORA-01922: CASCADE must be specified TO DROP 'PYTHON'

Hence, to remove the Python user, we must first drop the associated table or specify the cascade option as follows:

SQL> DROP USER python CASCADE;
USER dropped.

In such as case, Oracle can remove the user and the related schema objects.

Conclusion

In this post, we discussed and illustrated the usage of the DROP USER command in Oracle databases. Use the DROP USER command to remove a user with no associated schema objects from the database. If the user has linked schema objects, include the CASCADE option to remove the user and the related objects.

Oracle Drop Users