August 02, 2013

How to who has changed the user password and when

How to who has changed the user password and when

$ sqlplus "/as sysdba"

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

Table created.

SYS> create or replace procedure sachin.p (who in varchar2, what in varchar2)
  2  is
  3    pragma autonomous_transaction;
  4  begin
  5    insert into sachin.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     sachin.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 sachin identified by sachin;

User altered.

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

1 row selected.

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

2 rows selected.

REFERENCE:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/autotransaction_pragma.htm

http://www.oracle-base.com/articles/misc/autonomous-transactions.php

No comments:

Post a Comment