Check User Password Expiration and changed time

This blog will help you to find out the user password expiration and changed details in brief. You can check out the when the user password is going to expire or need to change before expiration.

You can find details about users which are created in Oracle. By running a query from a command prompt, the user information is stored in various system tables – ALL_USERS and DBA_USERS, depending on what user information you wish to retrieve.

Using the below script you can find out the details of last password changed and other details.

set lines 200
col profile format a22
col username format a30
col account_Status format a14

SQL > select username,profile,account_status,ptime Pwd_Change_Time,expiry_date “EXPIRED/EXPIRING ON”
from sys.dba_users, sys.user$ where dba_users.user_id=user$.user#
and profile not in ('PROFILE_NAME1','PROFILE_NAME2','DEFAULT')
order by profile,username,PWD_CHANGE_TIME desc ;

Check User Password Expiration

Here check the ptime “password changed time” for any user by the below query.

set linesize 200
col ptime for a50
col name format a35
col profile format a30

SQL > SELECT b.name ,b.ptime ,a.profile, b.ptime+90 “USER EXPIRY”
FROM sys.user$ b, dba_users a
WHERE b.name = a.username and b.name in ('&user_name')
and a.profile='&PROFILE_NAME';

To find invalid logins by users who has returncode=1017 in oracle database you can fire this statement for any users:

set pages 200 lines 300
COL USERNAME format a30
col USERHOST format a25
col ACTION_NAME format a25
col OS_USERNAME format a20

SQL > select username,USERHOST,ACTION_NAME, OS_USERNAME,TIMESTAMP from dba_audit_trail
where username=upper('&user') and action_name='LOGON' and returncode=1017 order by TIMESTAMP;

SQL > select username,USERHOST,ACTION_NAME, OS_USERNAME,TIMESTAMP from dba_audit_trail
where action_name='LOGON' and returncode=1017 order by TIMESTAMP;

TO CHECK OUT THE USER ACCOUNT IS LOCKED OR NOT.

col username format a30
col account_status format a20
select username,account_status from dba_users where username='&USER_NAME';

Click here to learn about Check Failed Login attempts count in Oracle Database

Or you can follow the ORACLE-BASE

Please share this Blog with your colleagues or friends. Your suggestions and feedback are very helpful for everyone who come to this site and learn it from oracleocpworld.com.
Please comment here for your any query related to above post. You can email me on : oracleocpworld@gmail.com.