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
$ 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