Re: Who's the procedure owner?

In article <8o65dr$t2e$1_at_nnrp1.deja.com>,   Luis Cabral <luiscabral_at_starmedia.com> wrote:
> Hello
>
> Say user1 has a procedure called proc1 in the database.
> Say user2 has the very same procedure, also called proc1.
> Say user3 is executing the procedure proc1. By the way,
> user3 can be accessing user1.proc1 or user2.proc1,
> depending on how the synonyms are defined, ok?
>
> Inside proc1, how can I know on which schema the
> procedure is running (user1 or user2)? If I use the
> "user" function, I will get "user3" (the connected
> user).
>
> Thank you
>

1) I do not believe the procedure should need to know, but 2) There is probably a better way to do this but here is what occurred to me. The procedure could contain a select against a user1 object that no other user except user1 has acess to. If the select works the procedure is the user1 version and if a ORA-00942 table or view not found error is returned [which the procedure would trap] the procedure is the user2 version. If would be wise to also select against a user2 table also to verify that a user3 version of the procedure was not created without a logic change.

If you are version 8.1.6 then I believe that a procedure can be ran as the owner or as the user so you should consider if running as user3 would eliminate the need to know who the procedure owner is.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.