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.
