Oracle SQL Scripts for User Password, Account Info, and History

In Oracle, it is essential to monitor and manage user account details such as password changes, account creation, last login times, password expiry dates, and password change history. This post will walk you through the most useful SQL scripts to retrieve this information.

1. Check User Account Creation Date

To check the account creation date for a specific user, use the following query:

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SELECT CREATED FROM DBA_USERS WHERE USERNAME = 'user_name';

2. Check Last Password Change Time

To check the last time the password was changed for a specific user, use this query:

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SELECT PTIME FROM SYS.USER$ WHERE NAME = 'user_name';

After altering the password:

SQL> ALTER USER HR IDENTIFIED BY TestPwd2025#;
User altered.

SQL> SELECT PTIME FROM SYS.USER$ WHERE NAME = 'HR';
PTIME
---------
07-JAN-2024

3. Check Last Login Date

To check when a user last logged in, use this query:

SELECT LAST_LOGIN FROM DBA_USERS WHERE USERNAME = 'user_name';

4. Check Password Expiry Date

To find the password expiry date for a specific user, run this query:

SELECT EXPIRY_DATE FROM DBA_USERS WHERE USERNAME = 'user_name';

Note: If the result is empty, it means the password does not have an expiry date set, which is typically configured as “unlimited” in the user’s profile.

5. Check Password Change History

You can check the history of password changes for an Oracle user by querying the USER$ and USER_HISTORY$ tables. The following query retrieves the password change dates for the specified user:

SELECT A.NAME, B.PASSWORD_DATE AS "Password Changed Date"
FROM SYS.USER$ A, SYS.USER_HISTORY$ B
WHERE A.USER# = B.USER# 
AND A.NAME = 'HR'
ORDER BY B.PASSWORD_DATE;

These SQL scripts allow you to efficiently retrieve important user account details in Oracle, providing valuable insights for security audits and account management.