This discussion is archived
8 Replies Latest reply: Oct 17, 2012 1:30 AM by 952768 RSS

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

956144 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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.

Legend

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