Re: PLS-00201 error

On 27 Sep 2004 03:04:55 -0700, andrew.tanenbaum_at_katamail.com (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?

Without version of Oracle difficult to tell. The workaround you describe looks like a security hole to me, and the workaround would also require a database link. Do you have such a loopback link set up?

The normal solution is either make direct grants or create the procedure with authid current_user.

--
Sybrand Bakker, Senior Oracle DBA