Script to list the users who haven't changed the password.

There are at times , where you might be know the list of users who have not changed the password since their account creation. 

Accessing sys.user$ will provide this information very easily . 

Here is the SQL that will list all the users who have not changed the password.You may want to add a filter to exclude the users who are authenticated by other means ( external / global ).

SELECT u.username ,
  u.account_status ,
  u.created ,
  to_char(u1.ctime , 'dd-mon-yyyy hh:mi:ss') as CreatedTime ,
  to_char(u1.ptime ,'dd-mon-yyyy hh:mi:ss') as  ChangedTime  
FROM dba_users u ,
  sys.user$ u1
WHERE u1.name = u.username
AND u1.ctime  = u1.ptime 

Here , ctime is the timestamp at which the user was created 
and ptime is the timestamp at which the user was modified. 


Let us create two users user1 , user2 and change the password for user1 after 90 seconds. 

SQL> grant create session to user1 identified by u1;

Grant succeeded.

SQL> grant create session to user2 identified by u2;

Grant succeeded.

SQL> exec dbms_lock.sleep(90);

PL/SQL procedure successfully completed.

SQL> alter user user1 identified by u11;

User altered.

Note : The above grant statements are shortcut to create users and assign privilege.

Let us examine the value of ctime and ptime in "USER$" table. 
As you can see , it reflects the updated time of the password  . 

SQL> set linesize 1000
SQL> col username format a10
SQL> SELECT u.username ,
  2    u.account_status ,
  3    u.created ,
  4    to_char(u1.ctime , 'dd-mon-yyyy hh:mi:ss') as CreatedTime ,
  5    to_char(u1.ptime ,'dd-mon-yyyy hh:mi:ss') as  ChangedTime
  6  FROM dba_users u ,
  7    sys.user$ u1
  8  WHERE u1.name = u.username
  9  AND u.username in ( 'USER1', 'USER2') ;

USERNAME   ACCOUNT_STATUS                   CREATED   CREATEDTIME          CHANGEDTIME
---------- -------------------------------- --------- -------------------- --------------------
USER2      OPEN                             16-DEC-15 16-dec-2015 03:12:43 16-dec-2015 03:12:43
USER1      OPEN                             16-DEC-15 16-dec-2015 03:12:43 16-dec-2015 03:14:13


Comments Welcome.