Re: Is the client IP address available

"Billy Verreynne" <vslabs_at_onwe.co.za> writes:

> Kevin Wise wrote in message <8booah$ia6$1_at_schbbs.mot.com>...
> >Is there any way to determine the IP address of the client from PL/SQL or
> >Java? I am trying to write a trigger that records the IP address where
> >certain changes are coming from. Thanks!
>
> My guess is no. If you're lucky, the client may specify an ip address in the
> connection string that you can pick up in V$SESSIONS - e.g. Cognus'
> Impromptu tool used to do (still does?) this.

If this is just for information that might be good enough. For actual security you really need the actual ip address not just what the client claimed its hostname is, which is what I believe you'll find in the v$session table. I expect clients can provide any string they feel like and easily spoof this information.

It's much more important that you provide everyone with unique usernames and using auditing tables to log that. Don't ever issue a shared username for a whole group and you'll at least be able to narrow the security breach to a single person. Still it is standard practice to log the ip address and time stamp from any network request, it would be nice to do this for some applications in Oracle.

One way to get the actual ip address would be to call netstat and parse the output... I think the local port number is even in v$session so this would actually be possible. However it's not really practical and it's a really bassackwards way of doing it.

> On NT for example, the domain (or workgroup) and NetBIOS name of the machine
> is specified (a feature of SQL*Net/Net8 on NT I believe).

the NetBIOS name would be a singularly unhelpful piece of data unless it's part of the authentication scheme you're using with Oracle.

> If network security is critical, then use a firewall to protect your
> servers. If you need a middle layer between the client and the server, then
> use 3 tier architecture and an application server that plays cop.

I would second that, using TNS as my application network layer would make me really nervous. I don't know the protocol, it's complicated, and it exposes much more functionality than I need. I would rather expose a limited application layer protocol that I can log and monitor all I want.

Still, it's general practice now to consider even internal trusted connections as suspect and at least log important information like ip address and authentication information on those connections.

-- 
greg