14 Replies Latest reply: Jan 29, 2013 10:05 PM by sb92075 RSS

    IP Address of Client

    657837
      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
          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
            How do i ping if i don't know the IP address.
            • 3. Re: IP Address of Client
              Anil Malkai
              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
                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
                  Pavan Kumar
                  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
                    Billy~Verreynne
                    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
                      Billy~Verreynne
                      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
                        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
                          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
                            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
                              Billy~Verreynne
                              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
                                user467677
                                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....
                                  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
                                    Why do Newbies resurrect two year old dead threads?