Re: Called by procedure or user?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 19 Jun 1998 03:23:56 GMT
Message-ID: <358ad95e.34358935@192.86.155.100>

A copy of this was sent to oldhairy_at_my-dejanews.com (if that email address didn't require changing) On Thu, 18 Jun 1998 16:24:11 GMT, you wrote:

>Hi all,
>
> Is there a way to dynamically verify if a procedure/function is being
>called by an user (via SQL*Plus or Forms or any other client...) or by
>another procedure/function?
>
> No regular users own any objects, all procedures and functions belongs to
>the "application owner" and the users get access to them via roles. In our
>analysis phase we determined the only solution that will suit our needs is to
>find a way to verify if the procedure/function is being called by the client
>thus performing the security checks or by another procedure/function and not
>performing any security checks since we assumed it was done previously.
>
> We have checked the dynamic tabes (V$) but to no avail.
>
> Any ideas?
>
> Thanks, Ed.
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading

You can use the call stack to get this information. Below is a routine, who_called_me that parses this out for you (tested in 7.3 and 8.0).... If you install it, then you can:

SQL> create or replace procedure p1
  2 as

  3          l_owner varchar2(35);
  4          l_name  varchar2(35);
  5          l_lineno        number;
  6          l_caller_t      varchar2(35);
  7  begin
  8          who_called_me( l_owner, l_name, l_lineno, l_caller_t );
  9          dbms_output.put_line( l_owner || ',' || l_name || ',' || l_caller_t

);
 10 end;
 11 /

Procedure created.

SQL>
SQL> create or replace procedure p2
  2 as
  3 begin
  4 p1;
  5 end;
  6 /

Procedure created.

SQL>
SQL> exec p1
,,ANONYMOUS BLOCK PL/SQL procedure successfully completed.

SQL> exec p2
TKYTE,P2,PROCEDURE PL/SQL procedure successfully completed.

So, the caller_t (caller type) will tell you if it comes from another procedure or an anonymous block....

who called me is:

create or replace procedure who_called_me( owner      out varchar2,
                         name       out varchar2,
                         lineno     out number,
                         caller_t   out varchar2 )

as

    call_stack varchar2(4096) default dbms_utility.format_call_stack;     n number;
    found_stack BOOLEAN default FALSE;

    line        varchar2(255);
    cnt         number := 0;

begin
--

    loop

        n := instr( call_stack, chr(10) );
        exit when ( cnt = 3 or n is NULL or n = 0 );
--
        line := substr( call_stack, 1, n-1 );
        call_stack := substr( call_stack, n+1 );
--
        if ( NOT found_stack ) then
            if ( line like '%handle%number%name%' ) then
                found_stack := TRUE;
            end if;
        else
            cnt := cnt + 1;
            -- cnt = 1 is ME
            -- cnt = 2 is MY Caller
            -- cnt = 3 is Their Caller
            if ( cnt = 3 ) then
                lineno := to_number(substr( line, 13, 6 ));
                line   := substr( line, 21 );
                if ( line like 'pr%' ) then
                    n := length( 'procedure ' );
                elsif ( line like 'fun%' ) then
                    n := length( 'function ' );
                elsif ( line like 'package body%' ) then
                    n := length( 'package body ' );
                elsif ( line like 'pack%' ) then
                    n := length( 'package ' );
                else
                    n := length( 'anonymous block ' );
                end if;
                caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
                line := substr( line, n );
                n := instr( line, '.' );
                owner := ltrim(rtrim(substr( line, 1, n-1 )));
                name  := ltrim(rtrim(substr( line, n+1 )));
            end if;
        end if;

    end loop;
end;
/  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jun 18 1998 - 22:23:56 CDT