trigger after password change
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: trigger after password change

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510

    trigger after password change

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

  2. #2
    Join Date
    Apr 2001
    Posts
    19
    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

  3. #3
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  5. #5
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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 05:44 AM.
    siva prakash
    DBA

  6. #6
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    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 ;

  7. #7
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,458
    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).

  8. #8
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,458
    Ooops,

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

  9. #9
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,458
    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.

  10. #10
    Join Date
    Sep 2008
    Posts
    1
    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
  •  



Click Here to Expand Forum to Full Width