Oracle Database ORA-28001: Password has expired error solution

Overview

In daily Oracle database operations, you may encounter errorsORA-28001: Password has expired. This error indicates that the password of the database user account you are trying to use has expired, resulting in the inability to log in or perform any actions properly. This article describes the causes, solutions, and preventive measures of this problem in detail, helping developers and database administrators deal with such problems quickly and effectively.

Error explanation

ORA-28001: Password has expiredIt is an error code returned by the Oracle database, indicating that the password of the user account has expired and needs to be reset before it can be used. Often, this is related to Oracle's security policies to ensure regular password changes, thereby improving security.

My example

"[INFO]Start backup..." 
expdp v3xuser/123456@orcl dumpfile= directory=dumpdir 

Export: Release 19.0.0.0.0 - Production on Saturday 12moon 21 02:10:40 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

UDE-28001: The operation has been generated ORACLE mistake 28001
ORA-28001: Password has expired

username: 

My scenario is that during the daily database backup statement running, I found that the backup bat file was run and the following error was reported:

Among them, my database user is v3xuser and the password is 123456. It prompts me that the password of this user has expired. My idea is to first look at the database password expiration policy and find that its expiration time is 180 days (that is, if you change your password now, you will report this error after 180 days. For the sake of database security, you will update your password again). If an error is reported, it means that the password of the database user account you are using has expired, resulting in the inability to log in normally or perform any operations.
So there are 2 solutions:

1: Just modify the password directly, but your database password will expire, and you have to modify the password once after it expires. This is the password expiration strategy.

2: Deal with this problem at once and modify the password policy to permanently, so that you can use a password, which is permanently valid.

Resolve steps (where my V3XUSER user is replaced with your user name)

1. Query the user's configuration file

First, determine the userv3xuserWhich configuration file is used:

-- Query v3xuser Configuration file
SELECT profile
FROM dba_users
WHERE username = 'V3XUSER';

Assume the result returned isDEFAULTConfiguration file.

2. Check the current password policy

Query the default configuration file (DEFAULT) password strategy, especiallyPASSWORD_LIFE_TIMEand other related parameters:

-- Check DEFAULT Password policy for configuration files
SELECT resource_name, limit
FROM dba_profiles
WHERE profile = (SELECT profile FROM dba_users WHERE username = 'V3XUSER')
AND resource_name IN (
    'PASSWORD_LIFE_TIME',
    'PASSWORD_GRACE_TIME',
    'PASSWORD_REUSE_TIME',
    'PASSWORD_REUSE_MAX'
);

Example of expected output: (The PASSWORD_LIFE_TIME here is 90, indicating the expiration time of 90 days)

3. Adjust the validity period of the password to be permanent

Set the default configuration file (DEFAULT) ofPASSWORD_LIFE_TIMESet toUNLIMITED, so that the password never expires:

-- Will DEFAULT Configuration file PASSWORD_LIFE_TIME Set as UNLIMITED
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

4. Unlock the account and reset the password

even thoughPASSWORD_LIFE_TIMESet toUNLIMITED, If the password has expired before this, you still need to unlock the account and reset the password:

-- Unlock V3XUSER Account
ALTER USER v3xuser ACCOUNT UNLOCK;

-- Change V3XUSER Password
ALTER USER v3xuser IDENTIFIED BY new_password;

Please make surenew_passwordCompare with the organization's security strategy and is complex enough. (The new_password here, just write the password directly, without double quotes or single quotes)

5. Verify the changes again

Query againv3xuseraccount status and password information, confirm whether the change takes effect:

-- Query v3xuser Account status and password information
SELECT username, account_status, expiry_date, last_login
FROM dba_users
WHERE username = 'V3XUSER';

Example of expected output:

at this time,ACCOUNT_STATUSShould be displayed asOPEN, andEXPIRY_DATEShould beNULL, means that the password never expires.

6. Optional steps: Check grace period

You can further check and adjust the grace period (PASSWORD_GRACE_TIME) to prevent similar problems in the future:

-- Check DEFAULT Configuration file PASSWORD_GRACE_TIME
SELECT resource_name, limit
FROM dba_profiles
WHERE profile = (SELECT profile FROM dba_users WHERE username = 'V3XUSER')
AND resource_name = 'PASSWORD_GRACE_TIME';

-- If adjustments to the grace period is required
ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME 0; -- Expired immediately
-- or
ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME 7; -- Grace period7sky

Summarize

Through the above steps, you can solve it safely and effectivelyORA-28001: Password has expiredThe problem. The key is:

• Query the user's configuration file.
• Check the current password policy.
• The password is valid forever.
• Unlock the account and reset the password.
• Verify the changes again.
• Check and adjust the grace period.

These steps not only solve current problems, but also provide guidance for future maintenance.

Preventive measures

To reduce the possibility of encountering similar problems again in the future, the following precautions are recommended:

• Regularly update passwords: Even if the password is set, it is recommended to update the password regularly to enhance security.
• Using Multi-Factor Authentication (MFA): Enable multi-factor authentication can increase account security.
• Monitor account activities: Regularly check the account activity log and promptly detect abnormal behaviors.
• Educational users: Ensure all users understand and comply with their organization's security policies.

Conclusion

Password management of Oracle databases is an important part of ensuring data security. Through the steps and suggestions provided in this article, you can effectively handle themORA-28001: Password has expiredreport an error and take precautions to avoid problems again in the future.

This is the article about Oracle database ORA-28001: Password has expired error solution. For more related Oracle ORA-28001: Password has expired, please search for my previous article or continue browsing the related articles below. I hope everyone will support me in the future!