Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Definer Rights vs User

Re: Definer Rights vs User

From: Mark & Kristi Wagoner <mwagoner_at_iac.net>
Date: Wed, 3 Apr 2002 18:31:05 -0500
Message-ID: <a8g3bq$1dp4$1@genma.iac.net>


Thanks to all for the help!

To be honest, I had thought about implementing VPD (and would love to check it out) but, as always, the project schedule doesn't leave room to investigate it. As the saying goes, "there is never enough time to do it right, but there is always time to do it over." Oh well, maybe the next version...

"Thomas Kyte" <tkyte_at_oracle.com> wrote in message news:a8f5go0qgo_at_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 - 17:31:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US