Creating Users   «Prev  Next»

Lesson 2Managing Users
Objective List Commands used to manage Users

Managing Users in Oracle

There are three primary commands used to manage users[1] in an Oracle database. In the context of this module, the term "manage" is used to denote the processes of creating, modifying, and deleting users. The three commands are:
CREATE USER The CREATE USER command is used when you want to add a new user to your database. It allows you to specify a username, a password, default and temporary tablespaces, and other information about a user.
ALTER USER The ALTER USER command is very similar to CREATE USER and allows you to make any necessary changes to a user definition after it has been created. One frequent use of ALTER USER is to change and reset passwords.
DROP USER The DROP USER command is used when you want to completely remove a user from your database.


Purpose: Use the CREATE USER statement to create and configure a database user, which is an account through which you can log in to the database, and to establish the means by which Oracle Database permits access by the user. You can enable a user to connect to the database through a proxy application or application server. For syntax and discussion, refer to ALTER USER. Prerequisites: You must have the CREATE USER system privilege. When you create a user with the CREATE USER statement, the user's privilege domain is empty. To log on to Oracle Database, a user must have the CREATE SESSION system privilege. Therefore, after creating a user, you should grant the user at least the CREATE SESSION system privilege. This name can contain only characters from your database character set and must follow the rules described in the section Schema Object Naming Rules. Oracle recommends that the user name contain at least one single-byte character regardless of whether the database character set also contains multibyte characters.


Purpose: Use the ALTER USER statement:
  1. To change the authentication or database resource characteristics of a database user
  2. To permit a proxy server to connect as a client without authentication

Prerequisites: You must have the ALTER USER system privilege. However, you can change your own password without this privilege.
Semantics: The keywords, parameters, and clauses described in this section are unique to ALTER USER or have different semantics than they have in CREATE USER. Keywords, parameters, and clauses that do not appear here have the same meaning as in the CREATE USER statement.

Drop User Cascade in Oracle 19c

Yes, the DROP USER CASCADE command is still very much used and relevant in Oracle 19c. It remains the primary way to safely drop a user and all their associated objects from the database.
  • To remove both a user and all the database objects they own (tables, indexes, views, etc.) from the database in a single command.
  • Ensures all related objects are removed, avoiding orphaned objects and potential inconsistencies.

  • The standard syntax is: `DROP USER username CASCADE;`
  • Replace `username` with the actual name of the user you want to drop.

  • Prevents errors: Trying to drop a user with existing objects without specifying `CASCADE` will result in an error, protecting against accidental data loss.
  • Maintains referential integrity: Automatically removes any foreign key constraints referencing tables owned by the dropped user, ensuring consistency across the database.
  • Removes associated privileges: Eliminates any privileges granted to the dropped user on other objects within the database.

  • You can manually drop each object owned by the user before dropping the user itself. However, this is a tedious and error-prone process, especially for users with many objects.
  • You can use `DROP USER username;`, but this will leave orphaned objects in the database, potentially causing issues later.

  • `DROP USER CASCADE` is an irreversible operation. Use it cautiously, backing up your database before proceeding.
  • If the user owns large objects (LOBs), dropping them might take time due to the deletion process.

In conclusion, DROP USER CASCADE remains a vital tool for safely removing users and their associated objects in Oracle 19c. Its use is strongly recommended to avoid data inconsistencies and ensure a clean and streamlined user management process.**

Oracle Drop User Cascade

In order to drop a user, you must have the Oracle DROP USER system privilege. The command line syntax for dropping a user can be seen below:
If a user owns any database objects, that user can only be dropped with the Oracle DROP USER CASCADE command. The Oracle DROP USER CASCADE command drops a user and all owned objects. The user will not be dropped and an error message will be returned if you a user owns objects and you fail to use the Oracle DROP USER CASCADE command. As the DROP USER CASCADE command is a DDL command, after the Oracle DROP USER CASCADE command has been executed, a rollback to re-create the user and his objects cannot be completed.

[1]user: Specify the name of the user to be created.

SEMrush Software