Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 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?

share|improve this question

1 Answer 1

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.

share|improve this answer
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? –  Xophmeister Jan 28 '13 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 '13 at 10:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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