Re: Definer Rights vs User

When using definer-rights (the default), USER (or select user from dual) returns the name of the caller, and 'select username from user_users' returns the name of the owner.
When using invoker-rights, both return the caller.

create or replace procedure sp_user_test -- AUTHID CURRENT_USER
-- uncomment previous line to use invoker rights is
  l_username user_users.username%TYPE;
begin
  select username
  into l_username
  from user_users;
  dbms_output.put_line('username = ' || l_username);   dbms_output.put_line('user = ' || USER); end;
/

Marc

>>>>>>>>>>>>>>>>>> Oorspronkelijk bericht <<<<<<<<<<<<<<<<<<

Op 2002-04-03, 1:36:40, schreef "Mark & Kristi Wagoner" <mwagoner_at_iac.net> over het thema Definer Rights vs User:

> 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
Received on Wed Apr 03 2002 - 08:14:51 CST