Ask the Expert

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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: