Re: How to get client's IP or name instead of OraUser

In article <4girg3$fr0_at_news.internetmci.com> rabouk_at_mcimail.com (Reza Abouk) writes:
>From: rabouk_at_mcimail.com (Reza Abouk)
>Subject: How to get client's IP or name instead of OraUser
>Date: Thu, 22 Feb 1996 22:43:53 GMT
 

>Hi,
 

> Is there a way to find out the IP address or the user name of
>the clients.
>A while back one DBA told me to put a line :
> OS_USER=any user name or IP address
>in the oracle.ini file, but that wasn't the solution.
>Feel free to call or send e-mail.

reza,

I have done this in the past. By adding the SQLNET_USERNAME parameter to the user's ORACLE.INI file (Windows client), you can track who is connecting to the database through querying some of Oracle's Dynamic Performance Tables. How this works is Oracle keeps track of which operating system processes are connecting to the database. If a remote user is connecting to the database, then the orasrv process established the connection. A daemon process is spawned to assume ownership for the local connection.

If there is a SQLNET_USERNAME entry in the ORACLE.INI and the username value for that parameter is equal to a valid user on the host server, then this username is recorded in the username column for th v$process view.

Here's a sample query:

col SID format a7
col St format a2

select 	  a.sid||','||a.serial# "SID"
	, substr(a.username, 1, 10) "Ora User"
	, substr(b.username, 1, 8) "Username"
	, substr(a.status, 1, 1) "St"
	, substr(a.program, 1, 25) "Program"
from
	v$session a, v4process b
where
	a.paddr = b.addr and 
	a.type = 'USER'

>Regards,
 

>Reza Abouk
>Sr. Systems Analyst
>Network Systems Integration Lab - Automation
>M.C.I Telecommunications
>(214) 918-2268
>rabouk_at_mcimail.com
Received on Fri Feb 23 1996 - 00:00:00 CET