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?

Some fundamentals missing. Couple of counter questions:

1. Database version(s)
2. You appear to be using DB Links
   • have you defined them to connect with passwords?
3. Why are you granting "select any' priv?
   • could you get away with "GRANT SELECT ON table"?
4. Who owns synonym for schema2.table_name?
5. Who runs the procedure when the error occurs?
   • which authid for the procedure? definer or current_user?

and OT/6) Any relation to Andrew S.?

/Hans Received on Tue Sep 28 2004 - 04:48:01 CEST