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

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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google