I will provide a detailed guide on how to set Oracle database user password to never expire in this article. We’ll walk you through each step of the process with proper details that I have tested in my VirtualBox.
There are two ways to set Oracle database user password to never expire:
- By altering the PASSWORD_LIFE_TIME value of the user profile
- Create a new profile with password expiry settings and assign the profile to the user
But I will recommend going with the second step because if we alter the PASSWORD_LIFE_TIME value of the user profile then it will affect all the other users who have been assigned the same profile so it is better to create a new profile and assign the profile to the user.
Table of Contents
1. First check the account status and the associated profile of the user whose password needs to be set to never expire:
col username format a20
col ACCOUNT_STATUS format a20
col profile format a25
SELECT username, account_status,profile, expiry_date FROM dba_users WHERE username ='SAM';
USERNAME ACCOUNT_STATUS PROFILE EXPIRY_DA
-------------------- -------------------------------- --------------- ---------
SAM EXPIRED DEFAULT 26-MAY-24
2. Now find out all the resource limits related to the password for the default profile:
col resource_name format a30
col LIMIT format a10
SQL> SELECT resource_name, limit FROM dba_profiles WHERE profile = 'DEFAULT' and RESOURCE_TYPE='PASSWORD';
RESOURCE_NAME LIMIT
------------------------------ ----------
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
INACTIVE_ACCOUNT_TIME UNLIMITED
8 rows selected.
Here we can see the resource limit for PASSWORD_LIFE_TIME IS 180 days. It means the user’s password will expire after 180 days. But our motto is to turn off Oracle user password expiration. So to do that we will create a profile called “Password_never_expire” by copying all the resource values from the previous step except PASSWORD_LIFE_TIME.
3. Create a profile called Password_never_expire with PASSWORD_LIFE_TIME UNLIMITED:
CREATE PROFILE "Password_never_expire"
LIMIT
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
INACTIVE_ACCOUNT_TIME UNLIMITED;
4. Set Oracle database user password to never expire:
SQL> alter user SAM profile PASSWORD_NEVER_EXPIRE;
User altered.
SQL>
col username format a20
col ACCOUNT_STATUS format a20
col profile format a25
SELECT username, account_status,profile, expiry_date FROM dba_users WHERE username ='SAM';
USERNAME ACCOUNT_STATUS PROFILE EXPIRY_DA
-------------------- -------------------- ------------------------- ---------
SAM EXPIRED PASSWORD_NEVER_EXPIRE 26-MAY-24
Congratulations! We have set Oracle database user password to never expire but we can see that account_status is EXPIRED. If the user’s password has expired, the user won’t be able to log in until the password is reset.
5. Change the user password and check the status:
SQL> alter user SAM identified by Sam#2024;
User altered.
SQL> SELECT username, account_status,profile, expiry_date FROM dba_users WHERE username ='SAM';
USERNAME ACCOUNT_STATUS PROFILE EXPIRY_DA
-------------------- -------------------- ------------------------- ---------
SAM OPEN PASSWORD_NEVER_EXPIRE
Now the user account_status is open. You can reset the password without changing it by querying or modifying internal system tables sys.user$ or user$ but directly querying the sys.user$ or user$ table for passwords is not recommended due to Security Risks and Data Integrity issues.
You can follow my other article Create password verify function to assign more password complexity rules in Oracle profile.
