Logon Trigger Not Working?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Logon Trigger Not Working?

  1. #1
    Join Date
    Jan 2001
    Posts
    515

    Logon Trigger Not Working?

    I have a database login trigger which is supposed to turn on auditing for a user with DBA privilege

    CREATE OR REPLACE TRIGGER dba_logon_audit after logon on database
    declare

    v_username varchar2(30);
    v_cnt number default 0;

    begin


    select user into v_username from dual;
    select count(*) into v_cnt from dba_role_privs where grantee = v_username and granted_role = 'DBA';


    if v_cnt > 0 then

    execute immediate 'audit select any table by '||v_username;
    execute immediate 'audit delete any table by '||v_username;
    execute immediate 'audit insert any table by '||v_username;
    execute immediate 'audit update any table by '||v_username;
    execute immediate 'audit execute any procedure by '||v_username;
    execute immediate 'audit alter any procedure by '||v_username;
    execute immediate 'audit create any index by '||v_username;
    execute immediate 'audit drop any index by '||v_username;
    execute immediate 'audit alter any index by '||v_username;
    execute immediate 'audit create any table by '||v_username;
    execute immediate 'audit drop any table by '||v_username;
    execute immediate 'audit alter any table by '||v_username;
    execute immediate 'audit create any sequence by '||v_username;
    execute immediate 'audit drop any sequence by '||v_username;
    execute immediate 'audit alter any sequence by '||v_username;
    execute immediate 'audit create any trigger by '||v_username;
    execute immediate 'audit drop any trigger by '||v_username;
    execute immediate 'audit alter any trigger by '||v_username;
    execute immediate 'audit create any view by '||v_username;
    execute immediate 'audit drop any view by '||v_username;
    execute immediate 'audit alter any view by '||v_username;
    execute immediate 'audit alter database by '||v_username;
    execute immediate 'audit alter profile by '||v_username;


    end if;

    end dba_logon_audit;


    Once I am logged in as a DBA it looks like the audit statements did not run??

    I have the audit_trail set to DB and this is Oracle 8.1.7.

    Thanks

  2. #2
    Join Date
    Nov 2001
    Location
    The Netherlands
    Posts
    19
    Hi,

    Do you have SYSDBA privileges when the trigger doesn't work? For SYS-user and SYSDBA privileges, triggers don't fire.

    Regards,

    Arian

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Quote Originally Posted by Alien
    Hi,

    Do you have SYSDBA privileges when the trigger doesn't work? For SYS-user and SYSDBA privileges, triggers don't fire.

    Regards,

    Arian

    they sure do fire, but if they are used to stop you logging on then it will be ignored - they always fire

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