Oracle Logon Trigger ReCompile

0 votes
1 answer
1138 views
oracle trigger oracle-11g oracle-12c logon
                          Typically when an oracle object goes invalid due to a dependent object being modified, the next execution of the invalid object will compile it and everything will be execute as expected (assuming no errors caused by the modification). This works as expected for standard table triggers:

    -- connect to DB
    connect myschema@TESTDB

    -- Create table
    create table myschema.t as select * from all_objects;

    -- Create before insert
    create or replace trigger myschema.trg before insert on t
    for each row
    begin
    :new.object_id :=12;
    end;
    /

    -- Grant insert privilege to table
    grant insert on myschema.t to otherschema;

    -- Modify underlying table to make trigger go invalid
    alter table myschema.t drop column owner;

    -- Connect as otherschema
    connect otherschema@TESTDB

    -- View status of trigger (invalid)
    select object_name, status from dba_objects where owner = 'MYSCHEMA';
    OBJECT_NAME       STATUS
    -----------       -------
    TRG               INVALID

    -- insert record into myschema.t
    insert into myschema.t 
    select OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,
    DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,
    TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,
    NAMESPACE,EDITION_NAME 
    from all_objects 
    where rownum=1;

    -- View status of trigger (valid)
    select object_name, status from dba_objects where owner = 'MYSCHEMA';
    OBJECT_NAME       STATUS
    -----------       -------
    TRG               VALID

All of that is as expected... now lets look at a logon trigger:

    -- Connect as privileged schema
    connect mydba@TESTDB

    -- Create table in privileged schema
    create table mydba.test_table ( c1 varchar2(100), c2 date );

    -- Create logon trigger
    create or replace trigger mydba.test_logon_trg
    after logon on database
    declare
    v_variable varchar2(100);
    begin
    select c1 into v_variable 
    from mydba.test_table;     
    exception when no_data_found
    then
      null;
    end;
    /

    -- Modify table to make trigger go invalid
    alter table mydba.test_table modify ( c1 varchar2(200) );

    -- Check status of trigger ( invalid )
    select owner, object_name, status 
    from dba_objects 
    where owner = 'MYDBA' and object_name = 'TEST_LOGON_TRG';
    
    -- Connect as non-privileged user (if you use a privileged, i.e. DBA,  account it bypasses the trigger)
    connect myschema@TESTDB
    
    ORA-04045: errors during recompilation/revalidation of 
    MYDBA.TEST_LOGON_TRG
    ORA-01031: insufficient privileges
    
It looks like the database tries to compile the invalid trigger as expected, but it throws a privilege warning. The MYDBA schema has the DBA privilege and so it should have all the privileges necessary.
                        
Asked by Nick S (631 rep)
May 31, 2018, 05:25 PM
Last activity: Feb 6, 2019, 06:01 PM
Oracle Logon Trigger ReCompile

Related Questions
