Re: Linking IP address to Oracle process

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1996/11/12
Message-ID: <3287e15d.4284410_at_dcsun4>

Ok, i'll through in yet another solution for Oracle7.2.3 and up (that's 7.2.3 and up, not before).

Basically it works by using the audit trail, which asof 7.2.3 catches the tns string in the comment_text field of the audit trail.......

have fun.

• IP package
• AUDIT_TRAIL = TRUE must be set in the init.ora file
• AUDIT CONNECT; must have been executed by DBA for this to work
  --
  
• the ip package has two external interfaces:
• address; 'pure' pl/sql function returning ip address
• into_v$session; moves ip address into v$session
• so you can "select client_info from v$session"
  --
  
• USAGE:
  --
  
• Most commonly you would use the address function directly.
• For performance reasons I will suggest it is always used in views
• in the following manner:
  --
  
• create view my_view
• as
• select *
• from my_table
• where exists ( select NULL
• from allowed_ip_addresses
• where ip_address = ( SELECT IP.ADDRESS FROM DUAL )
• )
  --
  
• create view my_view
• as
• select * from my_other_table
• where ip_address = ( SELECT IP.ADDRESS FROM DUAL )
  --
  
• and so on. Basically I am suggesting the IP.ADDRESS be selected
• from DUAL. This will cause the PL/SQL routine IP.ADDRESS to be
• called ONCE per query, instead of once PER ROW per query. The
• overhead associated with calling pl/sql for every row will be
• too large to use this successfully if you don't employ the above
• 'trick'.
  --
  
• In short, instead of saying
• x = ip.address
• always say
• x = ( select ip.address from dual ).
• Instead of saying
• ip.address in ( select x from T .... )
• always say
• where exists ( select NULL from T ....
• AND x = ( select ip.address from DUAL )
  --
  

create or replace package ip
as

    pragma restrict_references( ip, wnds, wnps, rnps );

    function address return varchar2;
    pragma restrict_references( address, wnds, wnps );

    procedure into_v$session;
end ip;
/
show errors

create or replace package body ip
as

    theAddress varchar2(2000);

    function address return varchar2
    is
    begin

        return theAddress;
    end;

    procedure into_v$session
    is
    begin

        dbms_application_info.set_client_info( theAddress );     end;
begin

    select comment_text

      into theAddress
      from user_audit_trail
     where sessionid = userenv('SESSIONID');

    declare
        n     number;
    begin
        n := instr( theAddress, '(HOST=' );
        theAddress := substr( theAddress, n+6 );
        n := instr( theAddress, ')' );
        theAddress := substr( theAddress, 1, n-1 );

    end;
end ip;
/
show errors

grant execute on ip to public
/
create public synonym ip for ip
/

On 11 Nov 1996 17:00:58 GMT, sds_at_dataplusnet.com (Strategic Data Systems) wrote:

>In article <328259C1.32E4_at_bekaert.com>, MARREEL_CHRIS/BENG_at_bekaert.com says...
>>
>>Andy Finkenstadt wrote:
>>> 
>>> >> We have had a similar problem - using SQL*Net to connect to Oracle, how can
>>> >> we tell which session belongs to which user (given that our app is
>>> >> designed to connect to Oracle with a common Oracle userid).
>>> 
>>> Using "lsof -i" check for all processes that have connections to
>>> the local orasrv port.  The remote host name will be the machine
>>> connected to your local oracle database.
>>> 
>>> Next cross-check the local process number with the process number
>>> recorded in the v$session & v$process views.
>>> 
>>> Andy
>>> --
>>> Andrew Finkenstadt, The Printing House Ltd, Also a GEnie Sysop
>>> 
>>
>>This looks very interresting.  Even the guys at the ORACLE-support here in Belgium
>>didn't know that because we recently asked this question to the support-department.
>>Even the responce center of HP-Belgium told us it was IMPOSSIBLE.  I will forward them
>>the information.
>>
>>Where can I find the above mentioned "lsof"-program ?
>>I have it on my system (probably installed with other software from a third party) at the 

 moment, but I

>>don't have any documentation (man-pages....)
>>This "lsof"-program seems VERY interresting....
>>Can you tell me where I can find it ?
>>
>
>
>I modified the orginal, because the join os v$session and v$process didn't work
>
>
>accept PID prompt 'Enter PID = '
>column username format a8
>column sid format 9999
>column serial# format 99999999
>column sql_text format a45 word_wrap
>select
>        s.username,
>        s.sid,
>        s.serial#,
>        s.status,
>        decode(sql_text, NULL, 'NON-SQL (DDL, GRANT, etc.)', sql_text)
>sql_text
>from
>        v$sqlarea a,
>        v$session s,
>        v$process p
>where
>        s.sql_address = a.address(+)
>        and s.sql_hash_value = a.hash_value(+)
>        and s.username is not null
>        /*the following was changed*/
>        and s.paddr=p.addr
>        and p.spid=&PID
>order by
>        username
>/
>

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com

• Check out Oracle Governments web site! ----- Follow the link to "Tech Center" and then downloadable Utilities for some free software...

  ---

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Tue Nov 12 1996 - 00:00:00 CET