Keywords: Oracle Database | ORA-28001 Error | Password Expiration
Abstract: This article provides an in-depth analysis of the ORA-28001 password expired error in Oracle databases, offering a comprehensive solution from diagnosis to resolution. By examining database open mode, user account status, and password policy profiles, along with specific SQL command examples, it systematically addresses password expiration issues to ensure normal database access.
Problem Diagnosis and Analysis
When encountering the ORA-28001 password expired error, a comprehensive diagnosis of the database state is essential. Key steps include checking the database open mode and user account status. Query the v$database view to obtain open_mode information, confirming whether the database is in a normal accessible state. Simultaneously, use the SELECT username, account_status FROM dba_users statement to inspect specific user account statuses, identifying accounts that are expired or locked.
Solution Implementation
The core solution for password expiration involves resetting the user password and unlocking the account. Use the ALTER USER command to change the user password, with the syntax ALTER USER username IDENTIFIED BY new_password. If the account is locked, additionally execute the ALTER USER username ACCOUNT UNLOCK command to unlock it. To ensure the operations take effect, it is recommended to commit the transaction afterward.
Password Policy Configuration Optimization
To prevent future password expirations, adjust the database's password lifecycle policy. Query the dba_profiles view to understand the current password policy settings, particularly the PASSWORD_LIFE_TIME parameter. Use the command ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED to set the password lifetime of the default profile to unlimited, fundamentally avoiding password expiration issues.
Complete Operational Flow Example
Below is a complete operational example: First, connect to SQL*Plus as SYSDBA, then check the database open mode with SELECT open_mode FROM v$database, followed by querying user status with SELECT username, account_status FROM dba_users WHERE username = 'target_user', reset the password with ALTER USER target_user IDENTIFIED BY new_password, unlock the account with ALTER USER target_user ACCOUNT UNLOCK, and finally modify the password policy with ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED and commit the changes.
Considerations and Best Practices
When resolving password expiration issues, ensure operations are performed using an account with sufficient privileges, such as SYSDBA. When resetting passwords, adhere to organizational security policies and avoid using overly simple passwords. Regularly inspecting user account statuses and password policy configurations, and establishing preventive maintenance mechanisms, can effectively reduce the occurrence of similar problems.