Blog

How to set Oracle database user password to never expire:

Oracle database user password to never expire

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.

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
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.

Leave a Reply