Bypass "table or view does not exist" in package compilation

Question

There are two schemas in a Oracle database.

MYSCHEMA that is controlled by me.

OTHERSCHEMA that is not controlled by me.

I just know I can get result from select * from OTHERSCHEMA.OTHEROBJECT. However, OTHEROBJECT is a synonym.

In my package, I have a statement like

insert into MYSCHEMA.MYTABLE(COL1) select COL1 from OTHERSCHEMA.OTHEROBJECT;

But it gave me Table or view does not exist.

How can I solve or bypass this problem? Thanks!

Answer 1

I assume you received the privilege to select from otherschema.otherobject by means of a role as opposted to a direct grant (such as grant all on otherschema.otherobject to myschema). If this is the case, the privileges within this role will not be used to determine what rights you have within a PL/SQL block.

See also How Roles Work in PL/SQL Blocks (Oracle Docu, where it says under Roles Used in Named Blocks with Definer's Rights:

All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. Roles are not used for privilege checking and you cannot set roles within a definer's rights procedure.)