Saturday 20 February 2016

How to know who has changed the user password and when

$ sqlplus "/as sysdba"

SYS> create table shoaib.log (msg varchar2(1000));

Table created.


SYS> create or replace procedure shoaib.p (who in varchar2, what in varchar2)
  2  is
  3    pragma autonomous_transaction;
  4  begin
  5    insert into shoaib.log values (who||' modifies '||what||'''s password at '||systimestamp);
  6    commit;
  7  end;
  8  /

Procedure created.

SYS> CREATE OR REPLACE FUNCTION verify_function
  2  (username varchar2,
  3    password varchar2,
  4    old_password varchar2)
  5    RETURN boolean IS 
  6  BEGIN 
  7     shoaib.p (user, username);
  8     RETURN(TRUE);
  9  END;
 10  /

Function created.

SYS> ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION verify_function;

Profile altered.

SYS> alter user shoaib identified by shoaib;

User altered.

SYS> select * from shoaib.log;
MSG
--------------------------------------------------------------------------------------------------------------------
SYS modifies shoaib's password at 23/04/1998 18:54:34.390 +01:00

1 row selected.

SYS> connect shoaib/shoaib
Connected.
shoaib> password
Password changed
shoaib> select * from shoaib.log;
MSG
--------------------------------------------------------------------------------------------------------------------
SYS modifies shoaib's password at 23/04/1998 18:54:34.390 +01:00
shoaib modifies shoaib's password at 23/04/1998 18:55:04.093 +01:00

2 rows selected

No comments:

Post a Comment