8 Replies Latest reply: Oct 17, 2012 3:30 AM by 952768 RSS

    ORACLE WEB: How can I get IP address and machine name of client?

    956144
      Hi everybody,
      On cliente/server, I used to get the user IP address using SYS_CONTEXT and IP_ADDRESS, and the terminal name with SYS_CONTEXT and HOST.
      But in web environment, these functions returned Server IP and name.
      I have to build a trigger to obtain the information of user IP address and terminal name to save on audit table.
      Can someone help me with this?
      Thank you really much
      Marcela
        • 1. Re: ORACLE WEB: How can I get IP address and machine name of client?
          952768
          Global example to audit only sys:
          CREATE OR REPLACE TRIGGER SYS.db_logon_audit_trigger
          AFTER LOGON ON DATABASE
          DECLARE
          i_process varchar(160);
          BEGIN
          
          if (user='SYS' and sys_context('USERENV','AUTHENTICATION_METHOD')<>'JOBS') then
          
              select process into i_process from v$session where audsid = SYS_CONTEXT('USERENV','SESSIONID') and sid = SYS_CONTEXT('USERENV','SID') and rownum=1;
              
              --sys_context('USERENV', 'HOST')
              --sys_context('USERENV', 'OS_USER')
              --sys_context('USERENV', 'TERMINAL')
              --sys_context('USERENV', 'INSTANCE_NAME')
              --sys_context('USERENV', 'SESSIONID')
              --to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS.FF6')
              --i_process
          
          end if;
          
          END;
          /
          You can store your variables in a specific table if you want.
          • 2. Re: ORACLE WEB: How can I get IP address and machine name of client?
            956144
            Thanks a lot SpecDev........
            But I have to obtain the client IP address and terminal name where a users are working and they make a change on some tables. For each register modified, I have to save the change and the IP / terminal name from that modification was made.
            You're not the user SYS, and you don't have permission to access v$session table.....
            • 3. Re: ORACLE WEB: How can I get IP address and machine name of client?
              952768
              You can use triggers on a table and run a procedure from system to check the ip and machine name and store it into a audit table.
              You can also choose to work with auditing the Oracle way (FGA) : http://www.oracle.com/technetwork/database/security/index-083815.html

              What kind of information do you need?
              - How to create trigger
              - How to audit (Oracle functionaliteit)
              - How to solve grants on v$session
              - How to store audit information into a table
              - How to receive ip and machine name from a user.

              There are so many ways to do this....
              • 4. Re: ORACLE WEB: How can I get IP address and machine name of client?
                956144
                Thank you very much for your help.

                I have audit tables already. These tables are completed by triggers before update.

                I need the information about how to receive ip and machine name from a user, NOT from the server.
                On cliente/server I obtained data using sys_context and the information was ok. But now on web, the data obtained are about the server instead the user.

                Regards.
                • 5. Re: ORACLE WEB: How can I get IP address and machine name of client?
                  952768
                  Your webserver is connected to the database right?
                  Are you using php or asp? Because you have to receive this information from your client with webserver technology. After that you can store it in your database.
                  • 6. Re: ORACLE WEB: How can I get IP address and machine name of client?
                    Tridus
                    MarcelaT wrote:
                    Thank you very much for your help.

                    I have audit tables already. These tables are completed by triggers before update.

                    I need the information about how to receive ip and machine name from a user, NOT from the server.
                    On cliente/server I obtained data using sys_context and the information was ok. But now on web, the data obtained are about the server instead the user.

                    Regards.
                    If it's the web server connecting to the database, you can't. The database is only going to see the web server, and not the user connected to the web server. The information you want is on the web server, and you'll need to pass it to the database as part of the operation.
                    • 7. Re: ORACLE WEB: How can I get IP address and machine name of client?
                      956144
                      Thanks for the answers........

                      Yes it's connected to web server, so I'll never see the user directly.

                      Based on your comments I suppose this problem must be solve through some Java code.............it's not possible on the old way.......

                      I'm in trouble! ja
                      • 8. Re: ORACLE WEB: How can I get IP address and machine name of client?
                        952768
                        If your actions are logged into the access.log of your webserver it should be possible to import this information into your database.
                        If the URL is giving you enough information about the action of your client, it could be used for auditing.