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 Tue Apr 02 2002 - 17:36:40 CST