I created a schema named myconfigs which has 3 tables, a role which has select, update, delete on the three tables named myconfig_user. I have another schema named workuser that has been granted myconfig user role. If I do select * from myconfig.table1 the select statement works. If I put the select statement in a package named workuser.package1.selecttable1 proc I get insufficient privileges.
If I grant select access on myconfigs.table1 to workuser I can create the package. Why doesn't the role give sufficient privileges to access the tables in workuser.package stored procedures?