SQL & PL/SQL

Oracle version: 10.2.0.4.0

It's my understanding that - when using invokers rights - that the user executing the package needs EXPLICIT grants to all the underlying objects the procedures in side that package reference that they're calling.

Is this correct?

Assuming it's correct - I'm seeing behaviour that doesn't make sense.

We have two schemas - A and B. There are packages definited with authid current_user in B that reference tables in A. The user executing the package in B does NOT have explicit permissions to the objects in A but - it's still allowing them to access the objects.

I did a quick review of that users privileges and - it has a ROLE that has that access but - it does NOT have explicit access.

So - I tried to replicate this same scenario and - was unable to do so in a test environment. Then - the only difference I could see was that the objects I had referenced in my test package were being referenced by schema_owner.table_name rather than schema_owner_table_name_public_synonym. So - I changed the object reference to use the public synonym and - it worked. Even though my test user didn't have explicit grants on that table either (though - it had them through a role).

What am I missing here?

In the O'Reilly book I've seen the following quote: "The invoker rights model checks the directly-granted privileges assigned to the invoker at the time of program execution to resolve any external references to database objects (but not PL/SQL program units). Even with invoker rights, however, roles are ignored."

With the fact it says 'roles are ignored' - I'm not sure how this is working (if only by accident through some bug perhaps)...?

Any insight is appreciated.

Edited by: wtlshiers on May 5, 2011 12:58 PM