Altering Users in Oracle

Once a user account has been created, the DBA's responsibility for that account does not end. Passwords expire and must be reset. Storage requirements change as applications grow. Users move between teams and their profile or tablespace assignments need updating. Accounts must be locked during investigations and unlocked when access is restored. All of these post-creation modifications are handled through the ALTER USER command, which allows a DBA to change any attribute of an existing user account without dropping and recreating it.

ALTER USER Syntax

The ALTER USER statement accepts the same clauses as CREATE USER. Only the clauses being changed need to be specified — omitted clauses retain their current values. The following example modifies multiple attributes of the coin_admin account in a single statement:

ALTER USER coin_admin
  IDENTIFIED BY CoinAdmin#2024
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  PROFILE default
  PASSWORD EXPIRE
  QUOTA 5000K ON users
  QUOTA 10M ON tools;

Common ALTER USER Operations

Most day-to-day user administration involves a subset of ALTER USER clauses rather than a full attribute update. The following examples cover the operations a DBA performs most frequently.

Changing a Password

A DBA can reset any user's password at any time without knowing the current password:

ALTER USER coin_admin IDENTIFIED BY NewSecurePass#2025;

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

ALTER USER coin_admin PASSWORD EXPIRE;

The user will be prompted to choose a new password at their next connection attempt. Until they do, they cannot access the database. This technique is commonly used during onboarding to ensure users establish their own credentials before first use, and during security incidents where a credential may have been compromised.

Locking and Unlocking an Account

To immediately prevent a user from connecting without removing the account:

ALTER USER coin_admin ACCOUNT LOCK;

All privileges, schema objects, and password settings remain intact. When access should be restored:

ALTER USER coin_admin ACCOUNT UNLOCK;

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

Changing Tablespace Assignments

If a user's storage requirements change, update their default tablespace and quota in a single statement:

ALTER USER coin_admin
  DEFAULT TABLESPACE app_data
  QUOTA UNLIMITED ON app_data
  QUOTA 0 ON users;

Setting QUOTA 0 on the old tablespace prevents the user from creating new objects there while leaving existing objects in place. Objects are never moved automatically when a default tablespace is changed — they remain in the tablespace where they were originally created.

Changing the Profile

As a user's role changes, their profile may need to change with it. A developer promoted to a DBA role might need a profile with different password complexity requirements or session limits:

ALTER USER coin_admin PROFILE dba_profile;

The new profile takes effect immediately for new sessions. Existing active sessions are not affected until they reconnect.

Modifying Multiple Attributes Simultaneously

ALTER USER can combine any number of clauses in a single statement. This reduces the number of DDL statements required and ensures related changes are applied together:

ALTER USER coin_admin
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA 101M ON users
  QUOTA 0 ON my_data;

Verifying Changes After ALTER USER

Always verify that ALTER USER produced the intended result. Query DBA_USERS to confirm account status, tablespace assignments, and profile:

SELECT username,
  account_status,
  lock_date,
  expiry_date,
  default_tablespace,
  temporary_tablespace,
  profile
FROM dba_users
WHERE username = 'COIN_ADMIN';

Verify quota changes through DBA_TS_QUOTAS:

SELECT tablespace_name,
  bytes / 1024 AS used_kb,
  max_bytes / 1024 AS quota_kb
FROM dba_ts_quotas
WHERE username = 'COIN_ADMIN';

A MAX_BYTES value of -1 confirms unlimited quota. A value of 0 confirms the quota has been revoked.

ALTER USER vs DROP and Recreate

A common mistake for DBAs new to Oracle is dropping and recreating a user account when only one attribute needs to change. This approach is destructive — it removes all schema objects owned by the user unless CASCADE is used, and it requires reassigning all privileges and roles from scratch. ALTER USER modifies only the specified attributes and leaves everything else untouched. It is always the correct tool for post-creation modifications.

Auditing ALTER USER Operations

Oracle Unified Auditing records ALTER USER operations by default when the standard audit policy is active. To create an explicit policy covering all user modification events:

CREATE AUDIT POLICY alter_user_policy
  ACTIONS ALTER USER;

AUDIT POLICY alter_user_policy;

Review recent ALTER USER events from the unified audit trail:

SELECT event_timestamp, db_username, action_name, return_code
FROM unified_audit_trail
WHERE action_name = 'ALTER USER'
ORDER BY event_timestamp DESC;

In regulated environments, auditing all ALTER USER operations provides the evidence trail required by compliance frameworks such as SOX and PCI-DSS to demonstrate that account modifications were authorized and tracked.

Oracle 23ai Enhancements

Oracle 23ai introduces schema-level privilege management that interacts with ALTER USER workflows. When a user's default tablespace changes, any schema-level privileges they hold on objects in the old tablespace remain valid — schema-level grants are independent of storage configuration. However, if the user's quota on a tablespace is reduced to zero, they cannot extend existing objects there even if they hold schema-level privileges to create new ones. Storage quotas and object privileges operate independently and must be managed separately.

The NO AUTHENTICATION clause introduced in Oracle 23ai for schema-only accounts is also applied through ALTER USER, allowing an existing account to be converted to a schema-only account that can no longer be used for interactive login:

ALTER USER legacy_app NO AUTHENTICATION;

This is a one-way operation — once an account is converted to schema-only it cannot be converted back to a password-authenticated account.

Summary

The ALTER USER command is the primary tool for all post-creation user account management in Oracle. It handles password resets, account locking and unlocking, tablespace and quota changes, profile reassignment, and password expiry — all without affecting the user's existing schema objects or privilege assignments. Use DBA_USERS and DBA_TS_QUOTAS to verify changes after each modification, and audit all ALTER USER operations through Oracle Unified Auditing to maintain a complete record of account changes for security and compliance purposes.