Package compile with roles vs direct id grants

[ptbarnum]

When we use roles to grant access from one schema to another using a role to grant select access to a group of tables verses assigning each schema the select rights, we are unable to compile a package from within the Package editor. If I cut and paste the script from the package editor to a PL/SQL Developer SQL Window it executes fine but from within the package editor I get a "insufficient priviledges to access object" on the table in the other schema. Even if I right click the package and click recompile It compiles successfully.

Any help would be appreciated.

------------------
Peter Barnum
Principal Consultant
Apex Consulting Group Inc.

 

[Marco Kalter]

What happens if you run the same "create or replace package" statement in SQL*Plus, under the same Oracle user?

------------------
Marco Kalter
Allround Automations

 

[ptbarnum]

Thanks for the response.

SQL*Plus gives a message that the package compiled with errors.

In the meantime we found a quote in a PL/SQL manual that says a user needs to be granted rights to all tables referenced in a package directly, not through a role, to be able to compile a package.

Can you confirm that for us?

------------------
Peter Barnum
Principal Consultant
Apex Consulting Group Inc.

 

[MarkP]

Yes, that's true.

Regards, Mark.