User Account Management and Dropping Users in Oracle

User Account Management in Oracle

User Account Removal Necessity

• User accounts may be removed when:

  • An employee departs from the organization.

  • An application is retired, making the application schema unnecessary.

Syntax for Dropping a User

• The command syntax for dropping a user is straightforward:

  • Command: DROP USER username;

• Privileges Required:

  • The executing user must possess the DROP USER system privilege.

Dropping an Empty Schema

• If the schema is empty, the user can be dropped without complications.

Dropping a Non-Empty Schema

• If the schema contains objects:

  • Use the CASCADE option to automatically drop all objects associated with the user.

• When utilizing CASCADE:

  • Any tables with foreign key references from another schema will also be removed.

  • Private or public synonyms that reference tables in the dropped schema will not be automatically dropped, leading to potential errors.

Error Handling During User Removal

• If an attempt to drop a user fails, various error codes may appear:

  • Example: Aura 01922 error message:

  • This occurs when there are still tables that prevent the user from being dropped.

  • Prior to using CASCADE, query the objects that hinder the operation.

  • Resolution: After confirming the user’s assurance that the objects are non-essential (e.g., a temporary copy of a table), the CASCADE option can be successfully applied.

User Still Connected to Database

• Another scenario may also prevent user deletion:

  • Example: Aura 01940 error message:

  • This happens if the user remains connected to the database.

• **Possible Actions: **

  • Request the user to log off.

  • If the user is unaware of their connection, have the Database Administrator (DBA) terminate their session.

• Once the user is logged off or the session is terminated, repeat the drop user command.

Irreversibility of the Drop User Command

• The DROP USER command is irreversible without performing a database restore.

• Important considerations include:

  • In a production environment, ensure corporate policies are followed to avoid accidental loss of critical application tables.

  • If important tables must be retained, they should be archived in advance to facilitate future access needs (e.g., for requests arising a week later).