Hi all:
I have two schemas in my database (Oracle10g). I have got some tables in schema1. I have given grants (select, insert, update, delete) to a role and have attached that role to schema2. Also, I have created synonyms for all those tables in schema2.
When I login to DB through schema2, I am able to do all my operations on the table of schema1. But when I try to build a package involving any of those tables, it throws ORA-00942: table or view does not exist.
But if I run "grant select on <table> to schema2;" and recompile the package, it goes through without any error.
My question: Is there any way wherein I can get over this problem by still using role to manage my grants? or to create a package, is it necessary to have direct select grants?
regards,
Ankit.