trigger after password change

[gopi]

Is there a way to fire off a trigger after a password change?

[SBALAJIKUM]

fire off?? what u mean?

can u pl clearlly tell whe the trigger is fireing and when u want to disable it.
u can use
alter trigger name disable;

to disable the trigger.


regards

[prakashs43]

MAY BE U CAN WRITE triger
ON UPDATE
AFTER UPDATE ON DBA_USERS TABLE
WHEN PASSWORD COLUMN CHANGES.

are u looking for this ?

[pando]

Originally posted by prakashs43
MAY BE U CAN WRITE triger
ON UPDATE
AFTER UPDATE ON DBA_USERS TABLE
WHEN PASSWORD COLUMN CHANGES.

are u looking for this ?

no way you can do that

[prakashs43]

if we write the trigger directly we get this message

ORA-04089: cannot create triggers on objects owned by SYS

but u can try this with some more conditions

CREATE TRIGGER audit_password_change AFTER ALTER
ON SCHEMA
BEGIN
INSERT INTO audit_password_change VALUES ( 'password changed' ) ;
END ;

tested

[gopi]

Prakash,

I took your advice and have made great progress. Is there a way to have the following trigger execute for every user that changes the password (not just scott)? Otherwise I would create this trigger every time I create a user (which is okay).


CREATE OR REPLACE TRIGGER system.xpc
AFTER ALTER ON scott.SCHEMA
v_subject varchar2(240);
v_body VARCHAR2(2000);
v_user VARCHAR2(50);
v_recip VARCHAR2(75);
.
.
BEGIN
.
.
.
.
mailit('dba@mycompany.com', v_subject, v_body);
mailit(v_recip, v_subject, v_body);
END ;


[LKBrwn_DBA]

AFTER ALTER ON XXX.SCHEMA specifies to fire the trigger whenever an ALTER statement modifies a database object in the data dictionary for that schema (which NOT includes the schema password).

[LKBrwn_DBA]

Ooops,

sorry it does include the password change, but also ALL other alter's on the schema objects.

[LKBrwn_DBA]

You are better off with the following:

CREATE OR REPLACE PACKAGE USRPW IS
INITPW VARCHAR2(32);
END USRPW;
/
CREATE OR REPLACE TRIGGER pw_trg
AFTER LOGON ON DATABASE
DECLARE
PW VARCHAR2(32);
BEGIN
SELECT PASSWORD INTO PW FROM DBA_USERS WHERE USERNAME=USER;
USRPW.INITPW := PW;
END;
/
CREATE OR REPLACE TRIGGER logoff_trg
BEFORE LOGOFF ON DATABASE
DECLARE
PW VARCHAR2(32);
v_subject varchar2(240);
v_body VARCHAR2(2000);
v_user VARCHAR2(50);
v_recip VARCHAR2(75);
BEGIN
SELECT PASSWORD INTO PW FROM DBA_USERS WHERE USERNAME=USER;
...etc...
IF USRPW.INITPW != PW THEN
mailit('dba@mycompany.com', v_subject, v_body);
mailit(v_recip, v_subject, v_body);
END IF;
END;
/

PS: Logon with DBA priviledges!
Good luck.

[jay11idba]

how about when a sys or system password is changed.