Password Last Change Time

I read that the PTIME column in the SYS.USER$ table shows when a user’s password was last changed so I decided to try it out in an Oracle 10 database:

SQL> SELECT VERSION FROM PRODUCT_COMPONENT_VERSION
  2  WHERE PRODUCT LIKE 'Oracle Database%'
  3  /

VERSION
--------------------
10.2.0.3.0

SQL>

I noted the time and created a user:

SQL> SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')
  2  DATE_AND_TIME1 FROM DUAL
  3  /

DATE_AND_TIME1
--------------------
06-JAN-2011 12:06:37

SQL> CREATE USER ANDREW IDENTIFIED BY REID
  2  /

SQL>

User created.

SQL>

I waited a few seconds:

SQL> EXEC DBMS_LOCK.SLEEP(5);

PL/SQL procedure successfully completed.

SQL>

I checked when the user was created and confirmed that this matched DATE_AND_TIME1:

SQL> SELECT TO_CHAR(CREATED,'DD-MON-YYYY HH24:MI:SS')
  2  USER_CREATION_TIME
  3  FROM DBA_USERS WHERE USERNAME = 'ANDREW'
  4  /

USER_CREATION_TIME
--------------------
06-JAN-2011 12:06:37

SQL>

I checked the password last change time and confirmed that this agreed with the time when the user was created:

SQL> SELECT TO_CHAR(PTIME,'DD-MON-YYYY HH24:MI:SS') PLCT
  2  FROM SYS.USER$ WHERE NAME = 'ANDREW'
  3  /

PLCT
--------------------
06-JAN-2011 12:06:37

SQL>

I noted the new time and changed the user’s password:

SQL> SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')
  2  DATE_AND_TIME2 FROM DUAL
  3  /

DATE_AND_TIME2
--------------------
06-JAN-2011 12:06:42

SQL> ALTER USER ANDREW IDENTIFIED BY NEW_PASSWORD
  2  /

User altered.

SQL>

I waited a few more seconds and noted the new time:

SQL> EXEC DBMS_LOCK.SLEEP(5);

PL/SQL procedure successfully completed.

SQL> SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')
  2  DATE_AND_TIME3 FROM DUAL
  3  /

DATE_AND_TIME3
--------------------
06-JAN-2011 12:06:47

SQL>

I checked the password last change time again and confirmed that it matched the time when the password was changed (i.e. DATE_AND_TIME2):

SQL> SELECT TO_CHAR(PTIME,'DD-MON-YYYY HH24:MI:SS') PLCT
  2  FROM SYS.USER$ WHERE NAME = 'ANDREW'
  3  /

PLCT
--------------------
06-JAN-2011 12:06:42

SQL>