Method to Track Oracle Schema User Password and Modification History; Find out Current, New Password

advertisements

_____________________________________________________________________________________________________________________

This is the solution to find out the Oracle user current password by recording the password history. This would be helpful to find out who changed the password for a user, when he changed, for which user with new password. From the recorded history you can find out the previous/old as well as current password for the Oracle user. This is achieved by oracle autonomous_transaction and profile PASSWORD_VERIFY_FUNCTION.

Here are the steps:

Here these objects like table, Procedure, Function are created under sys user. Only sys can query the changes.

SQL> conn / as sysdba

Connected.

// Create a tracking table with a character column.
SQL> create table pass_track (hist varchar2(200));

Table created.

//Create a procedure with autonomous_transaction to insert the records to the table
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 ('NewPwd:'||npwd||' Changed  by '||chngby||' For User:'||usr||' At:'||systimestamp);
    commit;
 end;
/
Procedure created.

//Create a function to call the procedure and which will be associated to the user profile. Here Default is the 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;
/

Function created.

// Assign the function to the profile which you wanted to get monitored
ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION fn_track_pass;

Demo

Assumption: Scott has DBA privilege to change the password.

SQL> CONN SCOTT

Enter password:
Connected.
SQL> alter user test identified by tiger1;
 
User altered.
 
 
SQL> conn scott
Enter password:
Connected.

SQL> password

Changing password for SCOTT
Old password:
New password:
Retype new password:
Password changed

SQL> conn / as sysdba

Connected.
SQL>SQL> alter user scott identified by manager123;

User altered.

SQL> select * from sys.pass_track;

HIST
---------------------------------------------------------------------
NewPwd:tiger1 Changed  by SCOTT For User:TEST At:23-JUN-14 02.41.15.000109 PM +02:00
NewPwd:manager12 Changed  by SCOTT For User:SCOTT At:23-JUN-14 02.43.29.466028 PM +02:00
NewPwd:manager123 Changed  by SYS For User:SCOTT At:23-JUN-14 02.44.40.472892 PM +02:00

_____________________________________________________________________________________________________________________

Website Stats