When you create a package, stored procedure 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 SCHEMA1 (e.g., 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?
Best Answer
When you execute PL/SQL in a package/procedure/trigger, Oracle disables roles (this is not the case for anonymous blocks). As a result, you have to grant permissions directly, rather than through roles.
Jonathan Lewis explains this well here, plus it's covered in the documentation here.
Invoker/definers rights are documented here.