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
insert into pass_track values ('NewPwd:'||npwd||' Changed by '||chngby||' For User:'||usr||' At:'||systimestamp);
//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,
RETURN BOOLEAN AS
PRC_track_pass(user, username, password);
Function created.// Assign the function to the profile which you wanted to get monitored
ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION fn_track_pass;
DemoAssumption: Scott has DBA privilege to change the password.
SQL> CONN SCOTTEnter password:
SQL> alter user test identified by tiger1;
SQL> conn scott
SQL> passwordChanging password for SCOTT
Retype new password:
SQL> conn / as sysdbaConnected.
SQL>SQL> alter user scott identified by manager123;
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