Showing posts with label AUDITING. Show all posts
Showing posts with label AUDITING. Show all posts

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