Re: IDENTIFYING USERS

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1996/12/16
Message-ID: <32b4a8b3.1302953@dcsun4>

On 15 Dec 1996 21:15:59 GMT, beatonb_at_cadvision.com (Bill Beaton) wrote:

>In article <58pm1t$kbe_at_crc-news.doc.ca>,
> collinsde_at_ic.gc.ca (denise collins) writes:
>>
>>I am fairly new to developing in Oracle. Is there any simple way
>>(with the aid of a DBA) to capture the TCP/IP address of an end-user
>>who is logged on to Oracle under windows 3.1/windows 95. Any help would
>>be greatly appreciated.
>>
>>Regards, Denise.

another thing that works as well would be this pl/sql. NOTE: you must have 7.2.3 and up, nothing less then 7.2.3 will work with this.

• 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 )
• ) --
• OR --
• 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
/

>
>Denise,
>I tried replying to you, but your mail domain (ic.gc.ca) doesn't
>seem to be known to the DNS severs around here.
>
>1. What you have to do is obtain the package 'lsof', and compile and
> install it on the ORACLE server (assuming it uses UNIX ... NT is out
> of luck).
>
>2. Generate the query that you want from the v$ tables, and put its
> output to a temporary location.
>
>3. Run lsof against the ORACLE sockets that you have active ... i.e.
> 1521 & 1525 or SQL*NET V2 and 1. Save to a temp location.
>
>4. Sort non-numerically each temp file by UNIX process ID.
>
>5. Outer join the V$ output against the lsof output.
>
>6. Format the output for reporting (I use awk). Note that
> a. Local server connections will not show an IP number or name
> b. SQL*NET V1 connections will show 2 processes.
>
>If you email me with a valid address, I can send you an actual working
>sample, and the tar file for lsof.
>
>Bill
>
>--
>----------------------------------
>E-Mail: Beatonb_at_cadvision.com or Bill_Beaton_at_calgary.qc-data.com
>----------------------------------

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 Mon Dec 16 1996 - 00:00:00 CST