PLS-00201 error
Andrew Tanenbaum
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@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@db2 from db1.
Why?
Sybrand Bakker
Tanenbaum) wrote:
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
Daniel Morgan
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
damo...@x.washington.edu
(replace 'x' with 'u' to respond)
Hans Forbrich
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
Andrew Tanenbaum
> Andrew Tanenbaum wrote:
>
> Some fundamentals missing. Couple of counter questions:
> 1) Database version(s)
8.1.6
> 2) You appear to be using DB Links
> - have you defined them to connect with passwords?
No
> 3) Why are you granting "select any' priv?
> - could you get away with "GRANT SELECT ON table"?
The "select any" privilege was granted by the dba role, so i would
keep the same situation. But your solution is equally valid. Keep in
mind that the dev team I was inserted to is not the best and many
solutions are very messy (e.g. all the application users have dba
privilege !!!). I'm a graduate and i don't know a lot about oracle,
but this environment doesn't help me for sure
> 4) Who owns synonym for schema2.table_name?
schema1. It doesn't sound good
> 5) Who runs the procedure when the error occurs?
schema1
> - which authid for the procedure? definer or current_user?
no authid, the default is the owner, that is schema1
>
> and OT/6) Any relation to Andrew S.?
He's my uncle... i'm kidding :-)
> /Hans
Andrew Tanenbaum
There's no time to learn in my dev team, i have to write and write
code.
But I'm completely agree with you. I'm a new graduate and I've never
seen oracle before, but after 1 week i'm already involved in the code
development. And i've already seen many orrible things (e.g. all the
application users have dba privs, all the application logic is stored
on the client and not on the db server or on an application server
...)