Re: Definer Rights vs User

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 3 Apr 2002 07:01:44 -0800
Message-ID: <a8f5go0qgo@drn.newsguy.com>

In article <a8dfa9$5e3$1_at_genma.iac.net>, "Mark says...
>
>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
>
>

first a comment -- and then a way to get the owner of the procedure in DR mode.

The comment -- VPD would be an easier way to do this perhaps. You could use partitioning for physical separation, VPD for the effect that company A sees company A's data and company B see's company B's data only.

Anyway:

ops$tkyte_at_ORA8I.WORLD> create or replace procedure p 2 as
3 begin
4 for x in ( select sys_context( 'userenv', 'CURRENT_SCHEMA' ) data from dual )

  5          loop
  6                  dbms_output.put_line( x.data );
  7          end loop;

8 end;
9 /

Procedure created.

ops$tkyte_at_ORA8I.WORLD> grant execute on p to public;

Grant succeeded.

ops$tkyte_at_ORA8I.WORLD> 
ops$tkyte_at_ORA8I.WORLD> exec p
OPS$TKYTE

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA8I.WORLD> @connect scott/tiger ops$tkyte_at_ORA8I.WORLD> set termout off

scott_at_ORA8I.WORLD> REM GET afiedt.buf NOLIST
scott_at_ORA8I.WORLD> set termout on
scott_at_ORA8I.WORLD> exec ops$tkyte.p

OPS$TKYTE PL/SQL procedure successfully completed.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Wed Apr 03 2002 - 09:01:44 CST