By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
Why is a procedure owned by user1 not able to access a table in the schema of user2 through a private synonym, wherein user1 has the 'select any table' system privilege? He is however able to access the table at the SQL prompt (Oracle 9i Release 2).
You are running into one of the oddities of Oracle. A user has no problem accessing another user's objects so long as the user has been granted privileges on that object. However, if you grant privileges on that object through a role, you will not be able to access that object in a stored procedure. The stored procedure requires you to grant access to that user explicitly, not through a role.