2

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?

1 Answer 1

2

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.

2
  • 1
    +1 for the references, but this doesn't really explain to me the motivation behind this decision. The best I could quickly glean is that roles are just for user maintenance, but that seems an arbitrary constraint. What security concern does disabling roles within PL/SQL solve? Jan 28, 2013 at 10:51
  • Oracle has always worked this way. The only people that can say "why?" are the original developers/design team. You will not find such an answer anywhere. I imagine it because of complications arising from pl/sql compiling to bytecode and associated permissions checking overhead.
    – Philᵀᴹ
    Jan 28, 2013 at 10:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.