How to find out who has changed the user's password and when

In our day to activity we come across this situation when suddenly a password gets changed and no one takes responsibility who did this. So here is the way to find out the genius who is not taking responsibility. Or simply we can say it is the best way to find out who did the password change in oracle database.

Note: We can find out the user who changed the password and when he did, only when we have below 4 steps already in place. 

Step1- Create a table

SQL> create table reyaj (msg varchar2(1000));
Table created.

Step2- Create a prodedure

SQL> create or replace procedure reyaj_p (who in varchar2, what in varchar2)
  is
  pragma autonomous_transaction;
  begin
 insert into reyaj values (who||' modifies '||what||'''s password at '||systimestamp);
 commit;
  end;
   /
  Procedure created.

Step 3 - Create a function

SQL> CREATE OR REPLACE FUNCTION verify_function
  (username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS
  BEGIN
  reyaj_p (user, username);
  RETURN(TRUE);
  END;
 /
 Function created.

Step 4 - Alter default profile

SYS> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION verify_function;
Profile altered.

Step 5 - Change password 

SQL> alter user test identified by test321;
User altered.

Step 6 - Now query the table to find out who changed the password and when

SQL> select * from reyaj;

MSG
---------------------------------------------------------------------------------------------------------------------------
SYS modifies TEST's password at 02-MAY-16 02.37.27.319762 AM -05:00

Step 7 - We can test it again if it is working properly and creating a new user and chaning its password.

SQL> create user passtest identified by passtest123;
User created.

SQL> alter user passtest identified by passtest321;
User altered.

SQL> select * from reyaj;
MSG
---------------------------------------------------------------------------------------------------------------------------
SYS modifies TEST's password at 02-MAY-16 02.37.27.319762 AM -05:00
SYS modifies PASSTEST's password at 02-MAY-16 02.39.11.985540 AM -05:00
SYS modifies PASSTEST's password at 02-MAY-16 02.39.53.454250 AM -05:00