Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Called by procedure or user?
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);
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;
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;
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
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