Tracking database users password change history

Create a table to log password change activities (Better to create in sys schema)

create table pass_track (hist varchar2(500));

--------------------------------------------------------------------------------------------------
Create a procedure to insert new password and name of user who is changing password


create or replace procedure PRC_track_pass (chngby in varchar2, Usr in varchar2, npwd in varchar2) is
   pragma autonomous_transaction;
  begin
    insert into pass_track values (' New Password '||npwd||' Changed  by user '||chngby||' For User '||usr||' at '||systimestamp);
    commit;
 end;


 ----------------------------------------------------------------------------------------------------
Create a password verification function to attach it to user profile.


CREATE OR REPLACE FUNCTION fn_track_pass (
  username      VARCHAR2,
  password      VARCHAR2,
  old_password  VARCHAR2)
  RETURN BOOLEAN AS
BEGIN
    PRC_track_pass(user, username, password);
    RETURN TRUE;
END fn_track_pass;

------------------------------------------------------------------------------------------------------
Alter profile assigned to user to use password verification function.


ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION fn_track_pass;

-------------------------------------------------------------------------------------------------------

Now connect with user farhat and change password. Note if the user who changes password is not a dba user then old password must be used in order to change it.


alter user farhat identified by farhat444 replace oracle123

Now user password change activity is logged into table pass_track.

select * from pass_track

HIST
-----------------

New Password farhat444 Changed  by user FARHAT For User FARHAT at 06-OCT-16 07.47.34.624000 AM +03:00