SQL Query to Check Password Expiry Date in Oracle | How to Turn Off?

Database user password expiry date is important to access the database and there is an SQL query to check password expiry date in Oracle. Here I would add some frequent problems you may have when accessing the database. Also, I added answers to solve such kind of problems

1. how to check user password expiry date in Oracle
   1. 1^st Method using DBA_USERS view
   2. 2^nd Method using USER_USERS view
2. How do I turn off Oracle password expiration?

3. Change Oracle User Password Expiry Date

4. The system user password has expired

1. how to check user password expiry date in Oracle

1. 1^st Method using DBA_USERS view

In Oracle, there is a view called DBA_USERS that has all the user-related information including expiry_date. Sometimes column does have a value sometimes it is the available date that the user password expires the date. Other than the password and expiration date the view is contained below columns.

This is the Systax for the query and you can have your user name for check related information.

--
SELECT * FROM DBA_USERS WHERE USERNAME = 'YOUR_USERNAME'; 
--

Explanation of the columns:

• USERNAME: Your Username that is needed to check the Expiry date.
• ACCOUNT_STATUS: Account status for checking whether the current state like ‘OPEN’, ‘LOCKED’, or ‘EXPIRED’.
• EXPIRY_DATE: Set the expiration date for the username.

Once you execute a query with your current values and if the EXPIRY_DATE is in the past or ACCOUNT_STATUS is ‘EXPIRED’, then your password already expired to use.

If you want to check other user-related information just remove where condition. I need to point out you have to require privileges similar to admin to view that kind of information.

--
SELECT USERNAME, ACCOUNT_STATUS, EXPIRY_DATE FROM DBA_USERS; 
--

2. 2^nd Method using USER_USERS view.

You can see the current user-related information using the USER_USERS view.  Here you can see your logged user holding user information directly. The view contains the below information.

--
select * from USER_USERS;
--

Special Note: DBA_USERS contains all the user-related information and USER_USERS contains current user permitted user information.

2. How do I turn off Oracle password expiration?

According to the password expiry policy change expiry date validation and to check the user profile you can first run the below query

--
select profile from DBA_USERS where username = '<username>';
--

Then you can change the profile limitation to UNLIMITED like below

--
alter profile <profile_name> limit password_life_time UNLIMITED;
--

To check the limit of the user profile run the below query using the above information

--
select resource_name,limit from dba_profiles where profile='<profile_name>';
--

3. Change Oracle User Password Expiry Date

To change the user password expiry data you have to alter it again. Or you can create a new user profile with a new expiry date. To create a new profile execute the below query:

--
CREATE PROFILE LIFE_PROFILE LIMIT
    PASSWORD_LIFE_TIME 1 --> = 1 day
    PASSWORD_GRACE_TIME 1; --> = 1 day

ALTER USER user7 PROFILE LIFE_PROFILE;
--

If you log into the database on the second day status will show ACCOUNT_STATUS = EXPIRED(GRACE). log in with this user

--
C:\>sqlplus user7/*****@yourDB

ERROR:
ORA-28002: the password will expire within 0 days
--

If you run the query to check the status of user

--
SELECT ACCOUNT_STATUS 
FROM DBA_USERS 
WHERE USERNAME = 'user7';

ACCOUNT_STATUS                  
--------------------------------
EXPIRED(GRACE)     

1 row selected.
--

If you want to change it to another date run above query we explain on How do I turn off Oracle password expiration?

4. The system user password has expired

If the system user password expired then once you log in to the database with the sys user error message will show like below

--
Open up cmd.exe and type in SQLPLUS
 Enter user-name: SYSTEM
 Enter password: the expired password 
--

Important: Keep in mind user information is very confidential information therefore use them with proper privileges otherwise those can be a problem in the future. Also, you have to have proper privileges to change them or view them.

Ref: https://stackoverflow.com/questions/1095871/how-do-i-turn-off-oracle-password-expiration?rq=3