Identifying when a password was last changed
Posted by John Hallas on May 16, 2016
Security asked to determine when certain passwords were last changed and I used the ptime column in user$ table to find out.
set lines 120 set pages 60 col name form a30 col Last_Changed form a12 SELECT name,ptime "Last_Changed" FROM sys.user$ a, dba_users b where a.name=b.username order by 1;
NAME Last_Changed ------------------------------ ------------ ANONYMOUS 24-AUG-13 APEX_030200 24-AUG-13 APEX_PUBLIC_USER 24-AUG-13 APPQOSSYS 24-AUG-13 CTXSYS 24-AUG-13 DBSNMP 13-MAY-16** DIP 24-AUG-13 EXFSYS 24-AUG-13 FLOWS_FILES 24-AUG-13 JOHN 13-MAY-16**** MDDATA 24-AUG-13 MDSYS 24-AUG-13 MGMT_VIEW 24-AUG-13 OLAPSYS 24-AUG-13 ORACLE_OCM 24-AUG-13 ORDDATA 24-AUG-13 ORDPLUGINS 24-AUG-13 ORDSYS 24-AUG-13 OUTLN 24-AUG-13 OWBSYS 24-AUG-13 OWBSYS_AUDIT 24-AUG-13 SCOTT 24-AUG-13 SI_INFORMTN_SCHEMA 24-AUG-13 SPATIAL_CSW_ADMIN_USR 24-AUG-13 SPATIAL_WFS_ADMIN_USR 24-AUG-13 SYS 13-MAY-16** SYSMAN 24-AUG-13 SYSTEM 13-MAY-16** WMSYS 24-AUG-13 XDB 24-AUG-13 XS$NULL 24-AUG-13
Note that you need to join with DBA_USERS otherwise the USERS$ view shows all the roles as well, such as RECOVERY_CATALOG_ROLE and DBA.
You will see that the date shown for most users is 24-AUG-13 which is the date the password was created by the seed database provided by Oracle which is used as part of an install. The accounts with two asterisks have their passwords created as part of user interaction when running DBCA
select name,ctime "Created" , ptime "Last_Changed" FROM sys.user$ a, dba_users b where a.name=b.username and a.name = 'SYS' order by 1 /
NAME Created Last_Changed ------------------------------ --------- ------------ SYS 24-AUG-13 13-MAY-16
Oracle DBA - A lifelong learning experience 
NormanDunbar said
Hi John,
Hope you are well.
You can avoid the join with dba_users if you only select those rows in sys.user$ where TYPE#=1. Those are the proper users while those with type#=0 are the roles.
HTH
Cheers,
Norm.
John Hallas said
Thanks Norman – very helpful.
Although I was trying to demonstrate my magnificent sql skills with a join – just about learnt how to do that 🙂
NormanDunbar said
Looks like you’ve mastered it then. ☺
Abhishek H said
I am seeing 12.2 misbehaving
SQL> alter user sys identified by “XXXXXXXXXXX”
User altered.
SQL> select ptime from user$ where name=’SYS’;
PTIME
———
02-MAR-18
SQL> select sysdate from dual;
SYSDATE
———
21-SEP-18
John Hallas said
Let me have a look and I will come back to you. John
Gorakh said
Thanks it helps!..