Oracle procedure synonym operating over parent schema tables

Question

Here is my scenario:

Schema: USER_1 Package: PKG_1 Procedure: PROC_1 Table: MY_TABLE

Schema: USER_2

So I have two schemas, USER_2 has a synonym for almost every table in USER_1, it also has a synonym for PKG_1.PROC_1, which updates MY_TABLE. However, when using USER_2 synonym for PKG_1.PROC_1 the procedure update tables from USER_1 instead of USER_2...

How could I operate over tables from the same schema that is executing synonym of a procedure in another schema?

Basically, what I expect is to actually see a change on USER_2.MY_TABLE but no change is done there...

I did this with USER_2:

CREATE OR REPLACE SYNONYM MY_TABLE FOR USER_1.MY_TABLE;

CREATE OR REPLACE SYNONYM PKG_1 FOR USER_1.PKG_1;
EXECUTE PKG_1;

I have no errors and even the DBMS_OUTPUT.PUT_LINE logs are visible and correct when connected to USER_2. However, say PKG_1.PROC_1 deletes a record from MY_TABLE, if I do:

SELECT RECORD FROM MY_TABLE;

Using USER_2, I can see the record was not deleted, but:

SELECT RECORD FROM USER_1.MY_TABLE;

Was deleted.

Thank you for your time!

Answer 1

To have a package operate on objects in the schema of the caller you need to declare AUTHID CURRENT_USER on the package (aka invokers rights package).

The default is AUTHID DEFINER allowing the package to operate on objects available from the same schema as the package itself.