Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Differentiating apache calls vs program references
Differentiating apache calls vs program references [message #508652] Mon, 23 May 2011 11:06 Go to next message
Baconator
Messages: 11
Registered: March 2011
Junior Member
I'm looking for a reliable (secure) way to determine whether a procedure has been called directly via mod_plsql or as a reference within another procedure regardless of who called that procedure.


Basically I don't want a private procedure because other packages need to call this procedure, but I don't want users to call it directly.

Obviously I could block it in apache using mod_rewrite.

Additionally I have thought of Custom Owa or Global Owa, but these seem to be too cumbersome.

I can't seem to find my old code, but there was a section of code that would allow me to see the calling procedure. I'm looking into that now.
Re: Differentiating apache calls vs program references [message #508654 is a reply to message #508652] Mon, 23 May 2011 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 67133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can't find the original thread on AskTom but here's Tom Kyte's "who_called_me" procedure:
create or replace procedure who_called_me 
  (owner      out varchar2,
   name       out varchar2,
   lineno     out number,
   caller_t   out varchar2,
   deep       in  number default 3)
as
   call_stack  varchar2(4096) := dbms_utility.format_call_stack;
   n           number;
   found_stack boolean        := FALSE;
   line        varchar2(255);
   cnt         number         := 0;
begin

   loop
      n := instr (call_stack, chr(10));
      exit when (cnt = deep 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 = deep 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 ');
            elsif line like 'anonymous%' then
               n := length ('anonymous block ');
            else
               n := null;
            end if;
            if n is not null then
               caller_t := ltrim(rtrim(upper(substr(line, 1, n-1))));
            else
               caller_t := 'TRIGGER';
            end if;
            line := substr (line, nvl(n,1));
            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;
/

SQL> create or replace procedure p1
  2  is
  3    owner    varchar2(10);
  4    name     varchar2(10);
  5    lineno   pls_integer;
  6    caller_t varchar2(20);
  7  begin 
  8    who_called_me (owner, name, lineno, caller_t);
  9    dbms_output.put_line(caller_t||' '||owner||'.'||name||' called me at line '||lineno);
 10  end;
 11  /

Procedure created.

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

Procedure created.

SQL> exec p2;
PROCEDURE MICHEL.P2 called me at line 4

PL/SQL procedure successfully completed.

Regards
Michel

[Updated on: Mon, 23 May 2011 11:22]

Report message to a moderator

Re: Differentiating apache calls vs program references [message #508655 is a reply to message #508654] Mon, 23 May 2011 11:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's one of the AskTom threads:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:382218730166

Regards
Michel

[Edit: missing word]

[Updated on: Mon, 23 May 2011 13:18]

Report message to a moderator

Re: Differentiating apache calls vs program references [message #508657 is a reply to message #508655] Mon, 23 May 2011 11:58 Go to previous messageGo to next message
Baconator
Messages: 11
Registered: March 2011
Junior Member
That was the original thread I used, awesome.

Per Package OWA authentication seems to be a good idea, but I am hesistant to switch the dads.conf to that mode from Basic considering any overhead from the boolean function call every page load.

I found another application server feature to do this, but I would feel more comfortable knowing it's secure without having to remember this odd file when replicating the application server:

Quote:
Access exclusion_list through the PL/SQL Gateway configuration file (wdbsvr.app). This configuration file describes settings for the PL/SQL Gateway module. For UNIX it is located at:

<ORACLE_HOME>/Apache/modplsql/cfg/wdbsvr.app

For NT, it is located at:

<ORACLE_HOME>\Apache\modplsql\cfg\wdbsvr.app

where <ORACLE_HOME> is the location of your Oracle9i Application Server installation.

Example 1:

exclusion_list=testschema.testpkg.*,sys.*,dbms_*,pkg?.*
Re: Differentiating apache calls vs program references [message #508661 is a reply to message #508657] Mon, 23 May 2011 14:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Tom Kyte's who_called_me procedure was added to the owa_util package, as shown below.

SCOTT@orcl_11gR2> desc owa_util
...
PROCEDURE WHO_CALLED_ME
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OWNER                          VARCHAR2                OUT
 NAME                           VARCHAR2                OUT
 LINENO                         NUMBER                  OUT
 CALLER_T                       VARCHAR2                OUT

SCOTT@orcl_11gR2>

Re: Differentiating apache calls vs program references [message #508663 is a reply to message #508661] Mon, 23 May 2011 14:27 Go to previous messageGo to next message
Michel Cadot
Messages: 67133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is also true in 9.2 and 10.2 (I forgot it).

Regards
Michel
Re: Differentiating apache calls vs program references [message #508665 is a reply to message #508663] Mon, 23 May 2011 14:41 Go to previous message
Baconator
Messages: 11
Registered: March 2011
Junior Member
So now that we have 5 summarized options, which is best? owa authentication is obviously best practices but it seems like it could add quite a bit of overhead depending on it's implementation; Is it run everytime the procedure is called or only when mod_plsql uses oracle? Also I wonder if it makes a full round-trip between the application server and database server for the authorize request before every package is actually requested. That could cost 100ms minimum.

1. mod_rewrite
2. htaccess
3. check "who_called_me"
4. use owa security methods (global/local/perpackage)
5. check "path_info" server variable for your package name.
Previous Topic: Calling a URL through a trigger
Next Topic: Passing date parameter into anomynous pl/sql block in APEX
Goto Forum:
  


Current Time: Wed May 27 05:10:10 CDT 2020