Is there a way to fire off a trigger after a password change?
Printable View
Is there a way to fire off a trigger after a password change?
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
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 thatQuote:
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 ?
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
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('[email protected]', v_subject, v_body);
mailit(v_recip, v_subject, v_body);
END ;
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).
Ooops,
sorry it does include the password change, but also ALL other alter's on the schema objects.
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('[email protected]', v_subject, v_body);
mailit(v_recip, v_subject, v_body);
END IF;
END;
/
PS: Logon with DBA priviledges!
Good luck.
how about when a sys or system password is changed.
Why don't you test it and then tell us how it works?Quote:
Originally Posted by jay11idba