This discussion is archived
14 Replies Latest reply: Jan 29, 2013 8:05 PM by sb92075 RSS

IP Address of Client

657837 Newbie
Currently Being Moderated
Hi All,

I have forms and reports application running on 10g database. Users get access to application through Application Server 10g . I have a requirement to know the IP address of my clients machines.

But if i fire select osuser,machine from v$session where program ='frmweb.exe' I get the following output. Can anybody here suggest me how to get IP address of a particular client.

SQL> select osuser,machine from v$session where program ='frmweb.exe';

OSUSER MACHINE
------------------------------ ----------------------------------------------------------------
NT AUTHORITY\ANONYMOUS LOGON GIDC\GIDC-APP1
NT AUTHORITY\ANONYMOUS LOGON GIDC\GIDC-APP1
  • 1. Re: IP Address of Client
    NavneetU Expert
    Currently Being Moderated
    You can try ping to that machine name from command prompt which will show you the IP address of the machine.

    Regards
  • 2. Re: IP Address of Client
    657837 Newbie
    Currently Being Moderated
    How do i ping if i don't know the IP address.
  • 3. Re: IP Address of Client
    716550 Pro
    Currently Being Moderated
    Shobhit wrote:
    Hi All,

    I have forms and reports application running on 10g database. Users get access to application through Application Server 10g . I have a requirement to know the IP address of my clients machines.
    As far as my knowledge you may not be able to find the details from database as you are using middle tier. All the end users are interacting through middle tier. even if you query v$session it would show you the details of middle tier. Check if there are any possibilities to do it from middle tier.

    Anil Malkai
  • 4. Re: IP Address of Client
    NavneetU Expert
    Currently Being Moderated
    Hi,
    Please see this
    SQL> select osuser,machine from v$session where username ='SCOTT';
    
    OSUSER                         MACHINE
    ------------------------------ ----------------------------------------------------------------
    admin                  DB-1
    
    SQL>
    SQL> exit
    Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    C:\Documents and Settings\>ping BEBO\DB-1
    Ping request could not find host BEBO\DB-1. Please check the name and try again.
    
    C:\Documents and Settings>ping DB-1
    
    Pinging DB-1.bebo.com [192.16.17.62] with 32 bytes of data:
    
    Reply from 192.16.17.62: bytes=32 time<1ms TTL=128
    Reply from 192.16.17.62: bytes=32 time<1ms TTL=128
    Reply from 192.16.17.62: bytes=32 time<1ms TTL=128
    Reply from 192.16.17.62: bytes=32 time<1ms TTL=128
    
    Ping statistics for 192.16.17.62:
        Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
    Approximate round trip times in milli-seconds:
        Minimum = 0ms, Maximum = 0ms, Average = 0ms
    
    C:\Documents and Settings>
    So using the machine i can see the IP address

    Regards
  • 5. Re: IP Address of Client
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    Check with below ones

    SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual;


    - Pavan Kumar N
    ORACLE 9i/10g - OCP
    http://www.oracleinternals.blogspot.com
  • 6. Re: IP Address of Client
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Shobhit wrote:

    I have forms and reports application running on 10g database. Users get access to application through Application Server 10g . I have a requirement to know the IP address of my clients machines.
    Not possible.

    Reason: the clients do not connect to Oracle. The app server does.

    So from an Oracle perspective, the app server acts as a proxy for the clients. It makes the network connection to Oracle on behalf of the clients. Oracle does not know who the clients are - it only deals with the app server.

    Work-around: The app server needs to pass the client details (such as IP address) to Oracle. This can be done using a context or a static package variable.

    This can be however problematic. If this is part of a security mechanism, you know rely on the app server to supply data that needs to be trusted for security.

    Most app servers has a pool of connections to Oracle. These are re-used for different clients. A tiny bug in the app server side could result in it using an existing connection that was used for clientA to now service clientB. The bug results in the app server not informing that Oracle session of what the IP of clientB is. The Oracle session processes the request from clientB, thinking that is is clientA as that client's IP is still set in the session.

    In order to comprehensively address this "+use the client IP address+" solution that you want to use, we need to know the actual requirement or problem you are attempting to solve on the database server by knowing the IP address.

    Keep in mind that IP addresses are not "unique identifiers". It is easily spoofed.
  • 7. Re: IP Address of Client
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Shobhit wrote:

    SQL> select osuser,machine from v$session where program ='frmweb.exe';

    OSUSER MACHINE
    ------------------------------ ----------------------------------------------------------------
    NT AUTHORITY\ANONYMOUS LOGON GIDC\GIDC-APP1
    NT AUTHORITY\ANONYMOUS LOGON GIDC\GIDC-APP1
    If the intention is to resolve a hostname to an IP address using output like the above, the socket call gethostbyname needs to be used.

    This is not supported by the Oracle UTL_TCP package and an alternative means will need to be used to make this call such as using Java, using an external proc to call the kernel's gethostbyname function, or using an external command like "+nslookup+" and parsing its output.

    But I really fail to see the relevance of this in an Oracle application. Database applications do no need, and should not need, to deal with the IP stack in this fashion.
  • 8. Re: IP Address of Client
    damorgan Oracle ACE Director
    Currently Being Moderated
    Because Oracle's middleware teams do not fully understand the concept of auditing and security this is not possible by default.

    You need to create a procedure that intentionally passes this information through to the database as the first action following creation of a connection.
    You can then create a table that stores session id and ip address.

    Billy: This is a common audit requirement in some parts of the world. A need to know the specific user is hiding behind the app server.

    Oracle: Please get your act together on this. There is no excuse for middleware hiding connected users. The default behaviour should be pass-through of full audit trail information including operating system login.
  • 9. Re: IP Address of Client
    657837 Newbie
    Currently Being Moderated
    here i would like to add one more thing in this discussion that Oracle 10g Application Server is also having its own database which is not other then Oracle 10.2.

    When i explored V$session in database of application server i dint got any information which is related to application user.
  • 10. Re: IP Address of Client
    damorgan Oracle ACE Director
    Currently Being Moderated
    Consider too, though it do not know if this addresses your immediate issue, that OAS is a dead code line. You might want to look at WebLogic and see if it can do a better job.
  • 11. Re: IP Address of Client
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    damorgan wrote:

    Billy: This is a common audit requirement in some parts of the world. A need to know the specific user is hiding behind the app server.
    IP address identifies the platform - the user's identification should rather be via his/her account credentials. That is what we usually pass from app server to Oracle.

    IP address - usable in some cases. Many cases not. It depends on how the network has been configured. Most corporate networks use DHCP for dynamic IP allocation. Many maps MAC addresses in order to provide some consistency - allocating the same IP for a MAC. But this does not work for the dialup part of the network (most corporates provides remote access to the corporate network). Here IP address allocation is totally dynamic.

    Corporates using Microsoft networks may use the MS product called "Internet Acceleration and Security Server". Not sure what features it provides, but I have dealt with it from analysing corporate traffic (using Oracle). And this server technology can act as an application proxy of sorts - which means clients connect to it, and it connects to the application server. In such a case an app server does not see the actual clients. It sees this proxy server as the client.

    In general, IP address is no longer as meaningful at application level as in the past. A decade ago, networking was a lot less complex (a lot more rigid). Today, with firewalls, proxies, reverse-proxies, NAT, tunnels, IP load balancers and so on - the IP that one sees at app level may very well be totally meaningless in identifying the actual client platform sending one the request.
  • 12. Re: IP Address of Client
    470680 Newbie
    Currently Being Moderated
    Hi
    I am using 11g. I usually trace forms user sessions through weblogic enterprise manager. Enterprise manager - Forms - user sessions

    Hope this helps you.

    Regards
    Prasad Alexander George
  • 13. Re: IP Address of Client
    Padma.... Newbie
    Currently Being Moderated
    Hi,

    Assign the below mentioned value to a text or display item. This will capture the ip of the client machine and display.
    webutil_clientinfo.get_ip_address

    Thanks
    Padma...
  • 14. Re: IP Address of Client
    sb92075 Guru
    Currently Being Moderated
    Why do Newbies resurrect two year old dead threads?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points