Re: Which user executes my procedure?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 11 Feb 2000 07:19:12 -0500
Message-ID: <h0v7assr2csgpftdvf2l4jfm1t9jqt61d5@4ax.com>

A copy of this was sent to Klim Samgin <klimsamgin_at_yahoo.com> (if that email address didn't require changing) On Fri, 11 Feb 2000 09:12:59 GMT, you wrote:

>Hi, All!
>
>I've got PL/SQL procedure and I granted execute on it
>to some db users.
>
>Can my procedure to find out which user execute it?
>This may help to configure procedure behaviour for each user.
>

user, uid, and userenv( 'schemaid' ) will help you out. consider:

SQL> connect /
Connected.

SQL> set serveroutput on

SQL> create or replace procedure whos_running_me   2 as
  3 l_schemaid varchar2(25);
  4 begin

  5      dbms_output.put_line( 'The user named ' || USER ||
  6                            ' with userid ' || to_char(UID) ||
  7                            ' is running me' );
  8  
  9      select userenv( 'schemaid' )
 10        into l_schemaid
 11        from dual;
 12  
 13      dbms_output.put_line( 'The effective userid ' ||
 14                            '(the priv set the procedure is ' ||
 15                            'running with) is '  || l_schemaid );

 16 end;
 17 /

Procedure created.

SQL> grant execute on whos_running_me to public;

Grant succeeded.

SQL> 
SQL> 
SQL> exec whos_running_me

The user named OPS$TKYTE with userid 30026 is running me The effective userid (the priv set the procedure is running with) is 30026

PL/SQL procedure successfully completed.

SQL> connect scott/tiger
Connected.

SQL> set serveroutput on

SQL> exec ops$tkyte.whos_running_me

The user named SCOTT with userid 34 is running me The effective userid (the priv set the procedure is running with) is 30026

PL/SQL procedure successfully completed.

>Thanks,
>Klim.
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Feb 11 2000 - 06:19:12 CST