When you create a package, sproc or function in one schema (say,
SCHEMA1) that accesses objects in another schema (
SCHEMA2), even when
SCHEMA1 has appropriate permissions to the respective
SCHEMA2 objects, granted through roles, Oracle will complain that the
SCHEMA2 object cannot be found. Even if you are calling a package as
SCHEMA2 that is set to
authid current_user, it complains. Apparently, to get around this problem, you have to explicitly grant permissions to
grant select on schema2.some_object to schema1;).
I have come against this issue several times, now: It's always something I forget about because it seems so counter-intuitive! Could anyone offer an explanation as to why roles aren't respected by PL/SQL objects?