Forum Stats

  • 3,839,736 Users
  • 2,262,532 Discussions
  • 7,901,052 Comments

Discussions

in package authid current_user not current user table trigger running

user3970448
user3970448 Member Posts: 4 Blue Ribbon
edited Aug 4, 2021 8:21PM in SQL & PL/SQL

Hi!

there is a problem below

Database version: 19c

schema 1: PKG_OWNER

  • table: PKG_OWNER.table1 (empty)
  • trigger: PKG_OWNER.table1_upd
  • package: PKG_OWNER.test_pkg authid current_user  update table1

schema 2: PKG_CALLER

  • table: PKG_CALLER.table1 (not empty)
  • trigger: PKG_CALLER.table1_upd
  • synonym: PKG_CALLER.test_pkg for PKG_OWNER.test_pkg

Test:

logon : PKG_CALLER user

begin test_pgk.proc1; end; --> dml: update table1 (--> current user: PKG_CALLER.table1)

-> error: PKG_OWNER.table1_upd trigger -> no data found

so it is not the PKG_CALLER.table1_upd trigger that is called, but the PKG_OWNER.table1_upd


specific error message:

LOGON: BCST_DB

ORA-01403: nincs adat

ORA-06512: a(z)

"NORBO_DB.KES_BIZFEJ_UPD", helyen a(z) 22. sornál

ORA-04088: hiba a(z) 'NORBO_DB.KES_BIZFEJ_UPD' trigger futása közben


Please anyone share your suggestions

Thanks,

Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,610 Red Diamond
    Answer ✓

    Post complete test case - I can't reproduce it:

    DROP USER NORBO_DB CASCADE
    /
    DROP USER BCST_DB CASCADE
    /
    CREATE USER NORBO_DB IDENTIFIED BY NORBO_DB DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS
    /
    GRANT CREATE SESSION TO NORBO_DB
    /
    CREATE USER BCST_DB IDENTIFIED BY BCST_DB DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS
    /
    GRANT CREATE SESSION TO BCST_DB
    /
    CREATE TABLE NORBO_DB.TABLE1(N NUMBER)
    /
    CREATE OR REPLACE
      TRIGGER NORBO_DB.TABLE1_UPD
        BEFORE UPDATE
        ON NORBO_DB.TABLE1
        BEGIN
            DBMS_OUTPUT.PUT_LINE('NORBO_DB.TABLE1_UPD - current user is ' || SYS_CONTEXT('USERENV','CURRENT_USER'));
    END;
    /
    CREATE OR REPLACE
      PACKAGE NORBO_DB.TEST_PKG
        AUTHID CURRENT_USER
        IS
          PROCEDURE PROC1;
    END;
    /
    CREATE OR REPLACE
      PACKAGE BODY NORBO_DB.TEST_PKG
        IS
          PROCEDURE PROC1
            IS
            BEGIN
                UPDATE TABLE1 SET N = 99;
          END;
    END;
    /
    CREATE TABLE BCST_DB.TABLE1(N NUMBER)
    /
    CREATE OR REPLACE
      TRIGGER BCST_DB.TABLE1_UPD
        BEFORE UPDATE
        ON BCST_DB.TABLE1
        BEGIN
            DBMS_OUTPUT.PUT_LINE('BCST_DB.TABLE1_UPD - current user is ' || SYS_CONTEXT('USERENV','CURRENT_USER'));
    END;
    /
    CREATE OR REPLACE SYNONYM BCST_DB.TEST_PKG FOR NORBO_DB.TEST_PKG
    /
    GRANT EXECUTE ON NORBO_DB.TEST_PKG TO BCST_DB
    /
    
    

    Now I:

    SQL> CONNECT [email protected]/BCST_DB
    Connected.
    SQL> SET SERVEROUTPUT ON
    SQL> BEGIN
      2      TEST_PKG.PROC1;
      3  END;
      4  /
    BCST_DB.TABLE1_UPD - current user is BCST_DB
    
    
    PL/SQL procedure successfully completed.
    
    
    SQL>
    

    SY.

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,610 Red Diamond
    Answer ✓

    Post complete test case - I can't reproduce it:

    DROP USER NORBO_DB CASCADE
    /
    DROP USER BCST_DB CASCADE
    /
    CREATE USER NORBO_DB IDENTIFIED BY NORBO_DB DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS
    /
    GRANT CREATE SESSION TO NORBO_DB
    /
    CREATE USER BCST_DB IDENTIFIED BY BCST_DB DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS
    /
    GRANT CREATE SESSION TO BCST_DB
    /
    CREATE TABLE NORBO_DB.TABLE1(N NUMBER)
    /
    CREATE OR REPLACE
      TRIGGER NORBO_DB.TABLE1_UPD
        BEFORE UPDATE
        ON NORBO_DB.TABLE1
        BEGIN
            DBMS_OUTPUT.PUT_LINE('NORBO_DB.TABLE1_UPD - current user is ' || SYS_CONTEXT('USERENV','CURRENT_USER'));
    END;
    /
    CREATE OR REPLACE
      PACKAGE NORBO_DB.TEST_PKG
        AUTHID CURRENT_USER
        IS
          PROCEDURE PROC1;
    END;
    /
    CREATE OR REPLACE
      PACKAGE BODY NORBO_DB.TEST_PKG
        IS
          PROCEDURE PROC1
            IS
            BEGIN
                UPDATE TABLE1 SET N = 99;
          END;
    END;
    /
    CREATE TABLE BCST_DB.TABLE1(N NUMBER)
    /
    CREATE OR REPLACE
      TRIGGER BCST_DB.TABLE1_UPD
        BEFORE UPDATE
        ON BCST_DB.TABLE1
        BEGIN
            DBMS_OUTPUT.PUT_LINE('BCST_DB.TABLE1_UPD - current user is ' || SYS_CONTEXT('USERENV','CURRENT_USER'));
    END;
    /
    CREATE OR REPLACE SYNONYM BCST_DB.TEST_PKG FOR NORBO_DB.TEST_PKG
    /
    GRANT EXECUTE ON NORBO_DB.TEST_PKG TO BCST_DB
    /
    
    

    Now I:

    SQL> CONNECT [email protected]/BCST_DB
    Connected.
    SQL> SET SERVEROUTPUT ON
    SQL> BEGIN
      2      TEST_PKG.PROC1;
      3  END;
      4  /
    BCST_DB.TABLE1_UPD - current user is BCST_DB
    
    
    PL/SQL procedure successfully completed.
    
    
    SQL>
    

    SY.

  • user3970448
    user3970448 Member Posts: 4 Blue Ribbon

    Thank you really!

    The operating code is a bit more complex, and there is no update directly, but in the procedure of another package - KES_PKG - which also exists in norbo_db and bcst_db and is created with an authid definer. The NORBO_DB.TST_PKG package runs in vain as a BCST_DB user and calls the NORBO_DB.KES_PKG package, not the BCST_DB.KES_PKG. The problem was solved by also changing NORBO_DB.KES_PGK to AUTHID CURRENT_USER.