Re: A problem with user_users

In article <375583b2_at_news.uk.ibm.net>,
  "Nicolas Bronke" <news_at_TRINITY.de> wrote:
>
> Peter Teoh <petermag_at_singnet.com.sg> schrieb in im Newsbeitrag:
> 37557539.AA6903C7_at_singnet.com.sg...>
> > HOW DO YOU DISPLAY THE USER CONNECTION INFORMATION (NORMALLY
PROVIDED BY
> > USER_USERS) WHILE RUNNING INSIDE A STORED PROCEDURE NAMESPACE?
> >
> I did not check your procedure and whether you are right. If you are
right,
> try that
> select * from all_users where username=user
>
> kind regards
> Nicolas
>
>

In versions 7.0 - 7.3 stored procedures execute as the owner of the procedure so the correct answer is TS for select * from user_users. In ver 8 there is an option to execute procedures as the end user, but I have not tried it. I have seen posts complaining about the feature not working, but I did not see the follow-up.

Anyway, It looks like Peter's solution will work:

UT1> execute mpowel01.who_am_i
SEFIN PL/SQL procedure successfully completed.

for code:
create or replace procedure who_am_i as --
v_user varchar2(20) ;
begin
select username
into v_user
from all_users
where username = user ;
dbms_output.put_line(v_user);
end;
/

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jun 03 1999 - 08:31:14 CDT