-
trigger after password change
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 ?
siva prakash
DBA
-
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
-
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
Last edited by prakashs43; 12-04-2002 at 06:44 AM.
siva prakash
DBA
-
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 ;
-
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('dba@mycompany.com', 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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|