After Logon Trigger Not Firing
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: After Logon Trigger Not Firing

  1. #1
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175

    After Logon Trigger Not Firing

    I am on Oracle 9.2. My actual goal is to trace certain SCHEMA.
    In the script below, both insert and execute immediate do not work, independently or together. The Schema name is TEST.

    Am I missing something? Test has DBA Privs. Also there is some thing more that is happening. When I put any line in the trigger (any valid line at all) except NULL, I get a trace file for about 700 bytes. But further actions in the same session are not getting recorded in the Trace File.

    CREATE OR REPLACE TRIGGER TEST.TEST_TRIGGER
    AFTER LOGON
    ON TEST.SCHEMA
    DECLARE
    tmpVar NUMBER;
    BEGIN
    EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE=TRUE';
    -- and/or
    INSERT INTO testtab VALUES (1010);
    COMMIT;
    END;
    Last edited by patnams; 10-19-2004 at 03:27 PM.
    Sridhar R Patnam

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I believe that logon triggers do not fire for DBA users.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175
    Originally posted by slimdave
    I believe that logon triggers do not fire for DBA users.
    I suspected that and tried without DBA Privs, still no good. Same thing happens, generates a trace file that produces nothing when I do tkprof.

    Do I have to
    create trigger only in SYS?
    Restart the database?
    set any parameters?
    Last edited by patnams; 10-19-2004 at 04:44 PM.
    Sridhar R Patnam

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by slimdave
    I believe that logon triggers do not fire for DBA users.
    Logon triggers do fire for DBA users (they don't fire only for users that connect AS SYSDBA/SYOPER). However for DBA users, they simply ignore any possible failure that might occure during execution of the logon trigger. In other words, if something fails inside the trigger, the non-DBA users are prevented from loging in to the database, while DBA users are not.

    Patnams, I belive you have to use authonomous transaction for that kind of operations inside your trigger - put "PRAGMA AUTOMOMOUS_TRANSACTION;" inside your DECLARATION section.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Apr 2002
    Location
    Phoenix, AZ
    Posts
    175

    this format works

    I just used this format of trigger (see script below) and it works, but the earlier question still remains. I was looking on AskTom, some one got it working by "compiling the trigger from a schema that has Create Any Trigger and other privileges but NOT DBA role".

    I tried and it did not work.

    this one works though,

    CREATE OR REPLACE TRIGGER TEST.trace_test
    AFTER LOGON
    ON DATABASE WHEN (USER='TEST')
    ..
    ..
    Sridhar R Patnam

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