Re: Definer Rights vs User

"Mark & Kristi Wagoner" <mwagoner_at_iac.net> wrote in message news:<a8dfa9$5e3$1_at_genma.iac.net>...
> I have a 9.0.1 database with several schemas. Schema A is the "master"
> schema, containing information about the others. The other schemas are for
> working with different companies, one company per schema.
>
> I have a company_profile view in schema A that returns one row based on the
> calling schema. I do this by using the where clause "username = (select
> user from dual)". Within each company schema I have a package that is to
> return the company_profile information. My goal is to have a call to the
> procedure in company 1's schema return the row for company 1, while a call
> to the procedure in company 2's schema will return the row for company 2.
> All procedures have been created using definer rights.
>
> Everything works if I run the procedures as the owner of the various company
> schemas. The problem arises when my normal "users" try to invoke the
> procedures. It seems "select user from dual" returns the name of the person
> calling the procedure rather than the owner of the package.
>
> According to the Oracle documentation,
>
> "In a sequence of calls, if an invoker-rights subprogram is the first
> subprogram called, the current user is the session user. That remains true
> until a definer-rights subprogram is called, in which case the owner of that
> subprogram becomes the current user. If the definer-rights subprogram calls
> any invoker-rights subprograms, they execute with the privileges of the
> owner. When the definer-rights subprogram exits, control reverts to the
> previous current user."
>
> I was under the impression that "current user" would be the name returned by
> the "select user from dual" statement, but this does not appear to be the
> case. The procedure is executing with the rights of the schema owner, but
> the name of the top-level caller is still returned.
>
> Is there a way to dynamically get the name of the procedure's owner rather
> than the caller?
>
> Thanks

The query "select user from dual" is functioning as expected returning the session user. I have not played with definer vs current user rights in relation of getting the effective user but you might check out the value of the current_user via the sys_context function and see if this value changes as you change procedures. The sys_context function provides several different 'user' id values including current and session _user.

UT1> l
  1 select
  2 rpad('current_user ',30,'
')||sys_context('USERENV','CURRENT_USER')   3 ,rpad('os_user ',30,'
')||sys_context('USERENV','os_user')
  4 ,rpad('proxy_userid ',30,'
')||sys_context('USERENV','proxy_userid')   5 ,rpad('session_user ',30,'
')||sys_context('USERENV','session_user')   6* from sys.dual
UT1> /

current_user                  MPOWEL01
os_user                       mpowel01
proxy_userid
session_user                  MPOWEL01

HTH -- Mark D Powell -- Received on Wed Apr 03 2002 - 09:04:37 CST