ORA-28001: the password has expired

ORA-28001: the password has expired error occurs when the Oracle user account password expires and the user attempts to login to the account. If the password expiry policy is set to reset the user password on a regular basis and the user is not reset within the expiry period, the user password will expire. If the user attempts to login after the password has expired, an error message ORA-28001: the password has expired will be displayed to the user. If the user attempts to login within the grace period, which is normally 7 days after the expiry date, the user is permitted to change the password. If a user attempts to login after 7 days, an error message is displayed, and a privileged Oracle user can reset the password for the normal user.



Oracle Error Message

if the oracle user account is expired and the user attempts to login to the account, the following error message will be shown to the login user. User can change the password if the user tries to login within the grace period.

ORA-28001: the password has expired
Cause: The user’s account has expired and the password needs to be changed
Action: change the password or contact the DBA


How to change password in Grace Period in SQL Developer

From SQL Developer, The following steps will help to change the password

  • Right-click on the connection name in the left panel
  • A popup menu will be displayed. In the popup meant, select the “Reset Password” option.
  • It will open a dialog window with title as “Enter New Password” in the SQL Developer.
  • Enter the current password, new password and confirmation password.
  • Click OK button.
  • The password will be reset successfully.


How to change expired password in SQL Developer

If a user’s password has expired and he or she attempts to log in after the grace period has expired, the user will be denied access to Oracle. To change the expired password in SQL Developer, a privilege user login is required.

First login with privileges user account in the SQL Developer and run the below command to reset the password.

SQL> ALTER USER scott ACCOUNT UNLOCK;
User altered.

SQL> ALTER USER Scott IDENTIFIED BY tiger;         
User altered


How to change password using SQL Plus

If an Oracle user account has expired and the user attempts to login within the grace period, which is normally 7 days after expiry, the user will be presented with a password expiry error message and will be able to change the password. When logging into the Oracle database in SQL Plus, the oracle user can reset the password.

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 11 01:16:27 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter user-name: hr
Enter password: 
ERROR:
ORA-28001: the password has expired

Changing password for hr
New password:
Retype new password:
Password changed
Connected.


How to change expired password using SQL Plus

The expired password can be reset by logging in as a privilege oracle account user. To reset the password, you must contact your system administrator.

[oracle@localhost ~]$ sqlplus sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 11 01:22:21 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select account_status from dba_users where username='HR';

ACCOUNT_STATUS
--------------------------------
EXPIRED

SQL> ALTER USER hr ACCOUNT UNLOCK;
User altered.

SQL> alter user hr identified by hr;
User altered.

SQL> select account_status from dba_users where username='HR';

ACCOUNT_STATUS
--------------------------------
OPEN

SQL> connect hr/hr
Connected.

SQL>


How to remove the password expiry

When a database user is created, a profile is attached. The default profile name is “DEFAULT,” and it is automatically attached to new database users. The profile includes a password policy, and by default, passwords expire after 180 days. Modify the profile as follows to prevent this and allow users to keep their password indefinitely.

alter profile "DEFAULT" limit password_life_time unlimited;


How to change password expiry configurations

The password expiry configurations can be altered using the alter profile command. password reuse max – the maximum number of new passwords that must be used before returning to an earlier one. password reuse time – the number of days that must pass before a password can be reused. If you set both of these to something other than unlimited, users must meet both criteria in order to change it. So, update the profile as follows to force at least 5 new passwords and one year before you can repeat a password.

alter profile my_custom_profile limit password_reuse_max 5 password_reuse_time 365;



Leave a Reply