Changing a User's Password

Password management is one of the most routine DBA responsibilities. Users forget passwords, security policies require periodic resets, and compliance frameworks mandate that credentials be rotated on a defined schedule. Oracle provides multiple methods for changing passwords — SQL*Plus command line, Oracle Enterprise Manager, Oracle Database Actions, and the orapwd utility for the special case of the SYS account. This lesson covers all of these methods along with profile-based password controls that automate enforcement.

Resetting a Password with ALTER USER

The primary method for resetting any user's password is the ALTER USER statement with the IDENTIFIED BY clause. A DBA can reset any user's password without knowing the current one:

ALTER USER coin_admin IDENTIFIED BY NewSecurePass#2025;

Oracle 23ai enforces password complexity through the profile's verification function. The default verification function ORA12C_STRONG_PASSWORD_VERIFY_FUNCTION requires a minimum of eight characters, at least one uppercase letter, one lowercase letter, one digit, and one special character. Passwords must not match the username or be a simple variation of it.

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

ALTER USER coin_admin PASSWORD EXPIRE;

The user will be prompted to choose a new password on their next connection attempt. The account is unusable until the reset is completed. This approach is recommended during onboarding and after any suspected credential compromise — the DBA never handles the new credential, reducing the risk of exposure.

Users can change their own password using the same syntax, but only for their own account:

-- Run as the user changing their own password
ALTER USER coin_admin IDENTIFIED BY MyNewPass#2025;

Only a user with the ALTER USER system privilege can change another user's password.

Changing a Password via Oracle Enterprise Manager

Oracle Enterprise Manager Cloud Control provides a graphical interface for password management that generates and executes the underlying ALTER USER statement without requiring the DBA to write SQL directly.

1. Navigate to Security → Users in the OEM Cloud Control interface.
2. Locate and select the COIN_ADMIN user from the user list.
3. Click the Edit button to open the user properties dialog.
4. Enter the new password in the Password field and confirm it in the Confirm Password field. The new password must meet the profile's complexity requirements.
5. Click Show SQL to review the ALTER USER statement that OEM will execute. This is useful for audit trail documentation.
6. Click Apply to execute the statement. OEM confirms success and the new password takes effect immediately.

Oracle Database Actions (SQL Developer Web), available in Oracle 23ai and Oracle Cloud environments, provides a similar browser-based interface under the Administration → Database Users section. Both tools generate and execute standard ALTER USER SQL — the underlying mechanism is identical regardless of which interface is used.

Changing the SYS Password

The SYS account requires special handling. Oracle strongly advises against using ALTER USER to change the SYS password while the database is open, because SYS is used by most internal recursive SQL operations. Attempting to change the SYS password via ALTER USER while the database is running can cause deadlocks. Additionally, if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is set to SHARED, the attempt will fail with:

ORA-28046: Password change for SYS disallowed

The correct method is to use the orapwd command-line utility to create a new password file containing the updated SYS password:

orapwd file='$ORACLE_HOME/dbs/orapwORCL' force=y
Enter password for SYS: [new password]

The force=y argument overwrites the existing password file. Without it, if a password file with the same name already exists, orapwd returns:

OPW-00005: File with same name exists - please delete or rename

After creating the new password file, set the initialization parameter:

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE SCOPE = SPFILE;

The password file must reside in the $ORACLE_HOME/dbs directory on Linux/UNIX or %ORACLE_HOME%\database on Windows. Because this file grants SYSDBA and SYSOPER privileges, it must be stored in a directory accessible only to DBAs and OS administrators — not in a world-readable location.

Oracle best practice recommends creating at least one DBA account other than SYS or SYSTEM for daily administrative work. If multiple DBAs administer the same database, each should have their own account with DBA privileges rather than sharing the SYS or SYSTEM credentials.

Forcing Periodic Password Changes with Profiles

Oracle profiles allow a DBA to enforce automatic password expiry on a schedule rather than relying on manual resets. Two profile parameters control this behavior:

ALTER PROFILE default LIMIT
  PASSWORD_LIFE_TIME 90
  PASSWORD_GRACE_TIME 7;

PASSWORD_LIFE_TIME 90 sets the maximum number of days a password may be used before it must be changed. PASSWORD_GRACE_TIME 7 defines the grace period — the number of days after the life time expires during which the user is warned at each login to change their password. After the grace period ends without a password change, the account is locked automatically.

In the example above, a user assigned the default profile has 90 days to use their password. Starting on day 91, they receive a warning at each login for 7 days. If they have not changed their password by day 98, the account locks and the DBA must intervene to unlock it and expire the password:

ALTER USER coin_admin ACCOUNT UNLOCK;
ALTER USER coin_admin PASSWORD EXPIRE;

Additional password history controls prevent users from recycling recent passwords:

ALTER PROFILE default LIMIT
  PASSWORD_LIFE_TIME 90
  PASSWORD_GRACE_TIME 7
  PASSWORD_REUSE_TIME 365
  PASSWORD_REUSE_MAX 12;

PASSWORD_REUSE_TIME 365 prevents a password from being reused for 365 days. PASSWORD_REUSE_MAX 12 requires at least 12 password changes before a previous password can be reused. Both parameters must be set together — setting only one causes Oracle to ignore both.

View current profile settings for the default profile:

SELECT resource_name, limit
FROM dba_profiles
WHERE profile = 'DEFAULT'
AND resource_type = 'PASSWORD'
ORDER BY resource_name;

Temporarily Connecting as Another User

A DBA sometimes needs to connect as a specific user to troubleshoot privilege-related problems — for example, to reproduce an error that only occurs under that user's privilege set. In Oracle 19c and later the recommended approach is proxy authentication, which allows the DBA to connect on behalf of another user without changing their password:

-- Grant proxy authentication
ALTER USER coin_admin GRANT CONNECT THROUGH system;

-- Connect as DBA proxying as coin_admin
sqlplus system[coin_admin]@dbname

The proxy connection runs under coin_admin's privilege set while the audit trail records the actual connecting user (system), maintaining a clear accountability trail. When troubleshooting is complete, revoke the proxy:

ALTER USER coin_admin REVOKE CONNECT THROUGH system;

An older technique involved temporarily changing the target user's password, connecting as that user, then restoring the original encrypted password using the IDENTIFIED BY VALUES clause with the hexadecimal hash retrieved from DBA_USERS. This technique relied on an undocumented feature, and the PASSWORD column in DBA_USERS no longer exposes the password hash in Oracle 19c and later for security reasons. Proxy authentication is the supported replacement.

Password Files and Administrative Authentication

The Oracle password file authenticates privileged connections (SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM) when the database is unavailable or the connecting user is remote. It is the mechanism that allows a DBA to connect and start up a database that is not yet mounted:

sqlplus sys/SysPass#2024@dbname as sysdba

Oracle 23ai enhances the password file with support for stronger hashing algorithms. When recreating a password file for a 23ai database, use the format parameter to specify the current format:

orapwd file='$ORACLE_HOME/dbs/orapwORCL' format=12.2 force=y

Auditing Password Changes

Oracle Unified Auditing captures ALTER USER operations including password changes. Create an explicit audit policy to ensure all password modifications are recorded:

CREATE AUDIT POLICY password_change_policy
  ACTIONS ALTER USER;

AUDIT POLICY password_change_policy;

Review recent password change events:

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

Summary

Oracle provides three methods for password management: ALTER USER IDENTIFIED BY for direct resets, ALTER USER PASSWORD EXPIRE to force user-driven resets, and orapwd for the SYS account specifically. Profile parameters PASSWORD_LIFE_TIME, PASSWORD_GRACE_TIME, PASSWORD_REUSE_TIME, and PASSWORD_REUSE_MAX automate enforcement without manual DBA intervention. Use proxy authentication rather than temporary password changes when connecting as another user for troubleshooting. Audit all password change operations through Oracle Unified Auditing to maintain a complete compliance record.

Changing Users Password - Exercise