Managing Users in Oracle

Oracle Database provides three core SQL commands for managing user accounts throughout their lifecycle. These commands cover every stage from initial account creation through ongoing administration to permanent removal. Understanding when and how to use each command is a foundational skill for any Oracle DBA working in Oracle 19c environments, which remain widely deployed through their Premier Support window ending in 2029 and Extended Support through 2032.

CREATE USER

The CREATE USER statement establishes a new database account. You must hold the CREATE USER system privilege to execute it. The command defines the username, the authentication method, and optionally the default tablespace, temporary tablespace, storage quotas, and profile.

Full syntax:

CREATE USER username IDENTIFIED BY password
  DEFAULT TABLESPACE tablespace_name
  TEMPORARY TABLESPACE temp_tablespace
  QUOTA size ON tablespace_name
  PROFILE profile_name
  ACCOUNT UNLOCK;

Practical example:

CREATE USER app_user IDENTIFIED BY SecurePass#2024
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users
  QUOTA 200M ON app_data;

A newly created user has an empty privilege domain. Before the user can connect to the database, they must be granted the CREATE SESSION privilege at minimum:

GRANT CREATE SESSION TO app_user;

Oracle assigns the DEFAULT profile automatically unless a different profile is specified. This profile governs password complexity rules, failed login limits, password expiry intervals, and session resource limits. In production environments it is good practice to assign a purpose-built profile rather than relying on the default.

Oracle 23ai introduces the IF NOT EXISTS clause, which prevents errors when a script runs against a database where the account already exists:

CREATE USER IF NOT EXISTS app_user IDENTIFIED BY SecurePass#2024
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp;

Oracle also supports external and global authentication as alternatives to password-based login. External authentication delegates credential verification to the operating system or a directory service, while global authentication integrates with Oracle Internet Directory for centralized identity management across multiple databases.

ALTER USER

The ALTER USER statement modifies any attribute of an existing user account. You must hold the ALTER USER system privilege, with one exception: users may change their own password without requiring the privilege.

Changing a Password

A DBA can reset any user's password directly:

ALTER USER app_user IDENTIFIED BY NewSecurePass#2025;

To force the user to set their own password at next login without specifying it yourself, expire the current password:

ALTER USER app_user PASSWORD EXPIRE;

When a password is expired, Oracle prompts the user to choose a new one upon connection. The user cannot access the database until the reset is completed. This approach is commonly used during account provisioning so that users establish their own credentials before first use.

Locking and Unlocking an Account

When a user needs to be temporarily prevented from logging in without removing their account, lock it:

ALTER USER app_user ACCOUNT LOCK;

The account lock is immediate. All privileges, schema objects, and password settings remain intact. Existing active sessions are not terminated by the lock — they remain open until they disconnect or are killed explicitly. To restore access:

ALTER USER app_user ACCOUNT UNLOCK;

Account locking is the recommended approach for handling employee leaves, security investigations, and maintenance windows. It avoids the overhead of dropping and recreating an account along with all of its privilege assignments.

Modifying Tablespace and Profile Assignments

Change the default tablespace:

ALTER USER app_user DEFAULT TABLESPACE new_tablespace;

Update storage quota:

ALTER USER app_user QUOTA 500M ON users;

Assign a different profile:

ALTER USER app_user PROFILE developer_profile;

In Oracle 23ai, ALTER USER also supports schema-level privilege grants, allowing a DBA to grant all privileges on all current and future objects within a schema through a single statement. This significantly reduces the administrative overhead of managing application schemas with large numbers of objects.

DROP USER

The DROP USER statement permanently removes a user account from the database. You must hold the DROP USER system privilege. This operation is a DDL statement and takes effect immediately — it cannot be rolled back.

If the user owns no schema objects, a simple drop is sufficient:

DROP USER app_user;

If the user owns any tables, views, indexes, sequences, or other objects, Oracle will return an error unless the CASCADE option is included:

DROP USER app_user CASCADE;

DROP USER CASCADE removes the user account and all objects owned by that user in a single operation. It also removes any foreign key constraints in other schemas that reference tables owned by the dropped user, preventing referential integrity violations. Privileges granted to the dropped user on other objects are also revoked automatically.

Considerations Before Dropping a User

Because DROP USER CASCADE is irreversible, several precautions are warranted before executing it in a production environment:

1. Back up the schema first. Use Oracle Data Pump to export the user's schema objects before dropping, providing a recovery path if the removal was unintended:

   expdp system/password SCHEMAS=app_user DIRECTORY=backup_dir DUMPFILE=app_user.dmp;

2. Check for dependencies. Query DBA_DEPENDENCIES to identify objects in other schemas that depend on objects owned by the user being dropped:

   SELECT owner, name, type
   FROM dba_dependencies
   WHERE referenced_owner = 'APP_USER';

3. Verify active sessions. Confirm the user has no open sessions before dropping. Dropping a user with active sessions will succeed, but can cause in-flight transactions to fail abruptly:

   SELECT sid, serial#, status
   FROM v$session
   WHERE username = 'APP_USER';

4. Large object consideration. If the user owns tables with LOB columns or large partitioned objects, the cascade drop may take considerable time as Oracle deallocates storage segment by segment.

Querying User Information from the Data Dictionary

Oracle maintains a comprehensive set of data dictionary views for monitoring and auditing user accounts. The primary view is DBA_USERS:

SELECT
  username,
  account_status,
  lock_date,
  expiry_date,
  default_tablespace,
  temporary_tablespace,
  profile,
  created
FROM dba_users
ORDER BY username;

To review system privileges granted directly to a user:

SELECT privilege, admin_option
FROM dba_sys_privs
WHERE grantee = 'APP_USER';

To review role assignments:

SELECT granted_role, admin_option, default_role
FROM dba_role_privs
WHERE grantee = 'APP_USER';

To review object privileges granted to a user:

SELECT owner, table_name, privilege, grantable
FROM dba_tab_privs
WHERE grantee = 'APP_USER';

Auditing User Management Operations

Oracle Unified Auditing, enabled by default from Oracle 19c onward, automatically captures user management events. To audit all CREATE USER, ALTER USER, and DROP USER operations:

CREATE AUDIT POLICY user_mgmt_policy
  ACTIONS CREATE USER, ALTER USER, DROP USER;

AUDIT POLICY user_mgmt_policy;

Audit records are written to the unified audit trail and queried from UNIFIED_AUDIT_TRAIL:

SELECT event_timestamp, db_username, action_name, return_code
FROM unified_audit_trail
WHERE action_name IN ('CREATE USER', 'ALTER USER', 'DROP USER')
ORDER BY event_timestamp DESC;

In Oracle 23ai, audit policy management is further enhanced with schema-level audit conditions and improved retention controls, making it easier to satisfy compliance requirements under frameworks such as SOX, HIPAA, and PCI-DSS without excessive audit trail volume.

Summary

The three commands that govern the Oracle user account lifecycle are CREATE USER, ALTER USER, and DROP USER. CREATE USER establishes the account with its authentication method, tablespace assignments, and profile. ALTER USER handles all ongoing modifications including password changes, account locking, and profile reassignment. DROP USER CASCADE permanently removes the account and all owned objects when the account is no longer needed. Each operation should be accompanied by appropriate auditing to maintain a complete record of user management activity for security and compliance purposes.