Re: PLS-00201 error

Andrew Tanenbaum wrote:
> I have this situation: I created a synonym for schema2.table_name and
> i use it in a procedure owned by schema1. Schema1 is dba and has the
> grant to select any table by dba role. When i execute the procedure i
> get the PLS-00201 error because i cannot access to schema2.table_name
> with the grant given by the dba role, and so i have to explicitly
> grant schema1 user to select any table ecc... But the weird issue is
> another one. If i create a synonym like schema2.table_name_at_db1 and
> schema1, with dba role and not explicit grants, executes the procedure
> stored on db2, i get no error. So i have to explicitly grant schema1
> when it accesses schema2.table_name, i don't when it accesses
> schema2.table_name_at_db2 from db1.
> Why?

Because that is the Oracle security model and has been for a very long time.

What is weird is not Oracle's behavior but rather the fact that you have apparently stumbled this far without ever learning the basics.

I strongly urge you to take a good Oracle class and learn the basics before you make a mess banging code.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)