Changing a User's Password in Oracle AI Database 26ai

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 enforces password complexity through a profile's assigned verification function. Oracle provides several built-in options of increasing strictness, including ora12c_verify_function and the stricter ora12c_strong_verify_function, both of which typically require a minimum length, a mix of uppercase and lowercase letters, at least one digit, and at least one special character, and reject passwords that match the username or a simple variation of it. Which function actually applies depends on how the assigned profile's PASSWORD_VERIFY_FUNCTION setting is configured; it is worth checking the specific profile rather than assuming a single universal default.

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 one's own password is a standing exception to that rule.

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) 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. These two parameters are meant to be set together as a pair; Oracle's own worked examples always show them configured jointly rather than independently, and setting only one is not a configuration worth relying on.

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. 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 hash itself is not exposed through the DBA_USERS view 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

Auditing Password Changes

Unified auditing captures ALTER USER operations, including password changes, and it is worth being precise about its current status: starting with Oracle AI Database 26ai, traditional auditing is fully desupported, so unified auditing is not simply the recommended default, it is the only auditing mechanism available for any new configuration. 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. One correction worth making directly: the username column on this view is DBUSERNAME, with no underscore, not DB_USERNAME as sometimes shown; the underscored form fails with an invalid identifier error.

SELECT event_timestamp, dbusername, 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 unified auditing, now the only auditing mechanism current Oracle releases support, to maintain a complete compliance record.

Changing Users Password - Exercise