Q

User unable to access table in schema of another user

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).

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.
This was first published in May 2006

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close