Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Roles vs. direct grants

52757Oct 17 2002
This is a newbie DBA question:
I've created a role, Role_X, with select grants from Schema_A. I have created Schema_B and granted it Role_X. I can select all the tables referenced in Role_X just fine with ad-hoc SQL statements.

However, when I reference a Schema_A table (which is referenced in Role_X) in a procedure in Schema_B, I get an error about "table does not exist". I do not have any synonyms, public or otherwise, so I'm referring to the table as "Schema_A.tablename". The only way I could get my Schema_B procedure to compile was when I created a direct select grant on the Schema_A table to Schema_B outside of Role_X.

Am I missing something here with regard to select grants in roles? Are they not applicable when used in packages/procedures?

Thanks, in advance, for your help.
Rebecca
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 14 2002
Added on Oct 17 2002
4 comments
990 views