Re: Odd experience with Oracle documentation

Walter Zimmer wrote:
>
> Hi !
>
> I'm just now in a somewhat disappointed state so all I ask is if these
> experiences I made are common.
>
> My Problem is:
> My wow-Oracle-Account (scott at the moment) which gets accessed
> by wow needs to read a table from another schema. So I went into sqlplus,
> logged in with scott/tiger and typed:
> 'SELECT * from <other_schema>.<table>;' and it worked great !
>
> Now, for wow I need to put this in a package and every time I try to
> compile this I get 'PLS-00201: Must declare <other_schema>.<table>'
>

text removed

>
> Thanks for sharing your experiences,
> Walter
>
> walter.zimmer_at_rz.uni-ulm.de

My guess is that you receive SELECT privilege on the other schema's table through a role. When you create a procedure that references objects under another schema, you must have explicit privileges on that object. The procedure ignores any privileges you have received through a role.

The same thing applies if you attempt to create any object, such as a view, or a snapshot, based on an object in another schema.

For procedures, this fact is documented on page 6-10 of the Oracle7 Server Application Developer's Guide, version 7.2.

A more generic explanation is on 18-13 of the Oracle7 Server Concepts manual, version 7.2.

-- 
Bob Swisshelm
Eli Lilly and Company
swisshelm_at_lilly.com