Forum Stats

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

Discussions

Categories

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:
· Share on TwitterShare on Facebook

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.

    · Share on TwitterShare on Facebook

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.

    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook