Error
Error Code: ORA-28223

Oracle ORA-28223: Password Change Required

📦 Oracle Database
📋

Description

The ORA-28223 error occurs when a user authenticated globally through Oracle Internet Directory (OID) attempts their first login to the database. OID password policy requires a password change upon initial connection, blocking further access until completed.
💬

Error Message

ORA-28223: first login requires password change
🔍

Known Causes

2 known causes
⚠️
First Login Attempt
The user is connecting to the database for the first time after being globally authenticated via OID.
⚠️
OID Password Policy
The password policy configured in OID mandates a password change on the initial login for new or reset accounts.
🛠️

Solutions

4 solutions available

1. Immediate Password Change via SQL*Plus easy

The quickest way to resolve ORA-28223 is to log in and change the password immediately.

1
Connect to the Oracle database using SQL*Plus as the user experiencing the error. You will be prompted for the current password.
sqlplus username/old_password@your_tns_alias
2
Once connected, you will likely be prompted to change the password. Enter a new password when requested.
3
If you are not prompted automatically, you can manually initiate the password change using the `ALTER USER` command.
ALTER USER username IDENTIFIED BY new_password;
4
Commit the change.
COMMIT;
5
Exit SQL*Plus.
EXIT;

2. Password Change via Oracle Enterprise Manager (OEM) easy

Use Oracle Enterprise Manager for a graphical interface to change the password.

1
Log in to your Oracle Enterprise Manager console.
2
Navigate to the target database.
3
Go to the 'Security' or 'User Administration' section.
4
Locate the user experiencing the ORA-28223 error and select the 'Edit' or 'Manage' option.
5
Find the 'Change Password' option and enter the new password.
6
Apply or save the changes.

3. Administrator-Initiated Password Reset medium

An administrator can force a password change for the user.

1
Connect to the database as a user with `ALTER USER` privileges (e.g., `SYSTEM` or `SYS`).
sqlplus system/system_password@your_tns_alias
2
Use the `ALTER USER` command to reset the password. The `FORCE` clause is crucial here as it ensures the password change is enforced on the next login.
ALTER USER username IDENTIFIED BY new_password FORCE;
3
Commit the change.
COMMIT;
4
Inform the user that their password has been reset and they will be prompted to change it on their next login.

4. Disabling Initial Password Change Requirement (Use with Caution) advanced

Temporarily disable the requirement for an immediate password change. This is generally not recommended for production environments.

1
Connect to the database as a user with `ALTER USER` privileges (e.g., `SYSTEM` or `SYS`).
sqlplus system/system_password@your_tns_alias
2
Modify the user's profile to remove the `PASSWORD_CHANGE_TIME` constraint or set it to a very distant future date. First, identify the user's current profile.
SELECT profile FROM dba_users WHERE username = 'USERNAME';
3
Then, alter the profile. This example assumes the user is using the `DEFAULT` profile. Replace `DEFAULT` with the actual profile name if different. Setting `PASSWORD_CHANGE_TIME` to NULL effectively removes the constraint.
ALTER PROFILE DEFAULT LIMIT PASSWORD_CHANGE_TIME UNLIMITED;
4
Alternatively, you can set it to a very large number of days in the future.
ALTER PROFILE DEFAULT LIMIT PASSWORD_CHANGE_TIME 99999;
5
Commit the change.
COMMIT;
6
Now the user can log in without being immediately forced to change their password. It is highly recommended to re-enable this security feature later by altering the profile back to its original settings or by using `ALTER USER ... FORCE`.