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
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)
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
>
> 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
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
...)