DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: ON LOGON trigger not working

  1. #1
    Join Date
    Sep 2008
    Posts
    3

    ON LOGON trigger not working

    Hi DBAs,

    I'm trying to create a ON LOGON trigger that runs a stored procedure which grants privileges to a particular user upon logon. The stored procedure works well when it is ran independently, however when the user logs in the stored procedure is not executed by the trigger. No errors either.
    Here's the trigger and the stored procedure.

    CREATE OR REPLACE TRIGGER ON_LOGON AFTER LOGON ON DATABASE WHEN ( USER = 'TEST' )
    DECLARE
    USERID VARCHAR2(25);
    BEGIN
    SELECT USER INTO USERID from dual;
    grants_restore(USERID);
    EXCEPTION
    WHEN OTHERS THEN NULL;
    END;

    PROCEDURE:

    CREATE OR REPLACE PROCEDURE grants_restore
    (USERID IN varchar2)
    IS
    CURSOR tabpriv_cur IS
    SELECT UC_DBA_FIELD_DESC,RECNAME,USER_NAME from sysadm.ps_uc_dba_details where USER_NAME='TEST';
    priv_source SYSADM.ps_uc_dba_details.UC_DBA_FIELD_DESC%type;
    object_source SYSADM.ps_uc_dba_details.RECNAME%type;
    grantee_source SYSADM.ps_uc_dba_details.USER_NAME%type;
    BEGIN
    OPEN tabpriv_cur;
    LOOP
    FETCH tabpriv_cur INTO priv_source,object_source,grantee_source;
    IF tabpriv_cur%FOUND THEN
    EXECUTE IMMEDIATE 'GRANT ' || priv_source || ' ON ' || object_source ||' TO '|| grantee_source;
    ELSE
    EXIT;
    END IF;
    END LOOP;
    END;

    Am I missing anything here?
    Thanks in advance.

    Regards
    Badri

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    does that trigger even compile - im guessing not

  3. #3
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    And an EXCEPTION WHEN OTHERS THEN NULL; for the pièce de résistance : oh , day....

    Regards,

    Chiappa

  4. #4
    Join Date
    Sep 2008
    Posts
    3
    The trigger and the procedure compiles without any errors. The procedure runs independently without any errors. The trigger is not working for some reason though.

    SQL> alter trigger on_logon compile;

    Trigger altered.

    SQL> alter procedure grants_restore compile;

    Procedure altered.

  5. #5
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Add some error messages with DBMS_OUTPUT and see if its working, step by step.

    But - as mentioned above - an error handling EXCEPTIONS section might be quicker

  6. #6
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Quote Originally Posted by JChiappa
    And an EXCEPTION WHEN OTHERS THEN NULL; for the pièce de résistance : oh , day....

    Regards,

    Chiappa
    How often do you see that?
    Tom Kyte says it's tantamount to saying "and if this doesn't work then I don't care"

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