Who are you? The Sequel

In an earlier post, I described some of the information available to us when we deliver a web page from an Oracle database and web server. Before we deliver a page, we can query the remote address of the party asking for the page. For example,

select owa_util.get_cgi_env('REMOTE_ADDR') remote_addr
from dual; 

REMOTE_ADDR
----------------- 
24.91.24.118

1 row selected.
 

But what does that number really mean? Where is it coming from? In this post, we'll take a closer look at the IP address, and we'll get a little more information about the address.

The address by itself may not mean very much to us.  Most of us in the U.S use IPV4 addressing, which looks something like 192.168.1.10  -- 4 numbers separated by 3 periods.  If you're familiar with IP addresses, you'll recognize that an address like 192.168 is a private address, one that is within your firewall.  But there's more information to be gleaned from these addresses than just inside the firewall or outside the firewall.  If we could see the name that the address belongs to, then we'd have a better idea of who owns the address.

Behind every name is an IP address.   If we start a command window or terminal window and ping Google, we see that ping returns the  www.google.com's IP address:

tom@linux-dv8000:~$ ping www.google.com 
PING www.google.com (208.117.233.54) 56(84) bytes of data. 

We want to do the opposite, a reverse name lookup.  This is the same problem you face when you get those mysterious 877- phone calls and you wonder who is calling you. Fortunately, our computer system's name resolver can resolve names into IP address, and the name resolver can do the reverse - return a name from an IP address.

The Oracle database includes a package that interfaces with the computer's name resolver:  utl_inaddr. The utl_inaddr package includes two functions.  GET_HOST_NAME takes an IP address and returns a domain name.  GET_HOST_ADDRESS takes a host name and returns an IP address.  Here are a couple of examples.  First, we call GET_HOST_ADDRESS to resolve a host name into an IP address; then we do the reverse, we call GET_HOST_NAME to find a host name given an IP address:

select utl_inaddr.get_host_address('www.msln.net') from dual;

UTL_INADDR.GET_HOST_ADDRESS('WWW.MSLN.NET')
---------------------------------------------------
169.244.19.130

1 row selected.

select utl_inaddr.get_host_name('169.244.19.130') from dual;

UTL_INADDR.GET_HOST_NAME('169.244.19.130')
---------------------------------------------------
scrane.msln.net

1 row selected.

So, 169.244.19.130 belongs to msln.net.   Point our browser at  http://msln.net, and we get the home page of the Maine School and Library Network.  Point our browser at 169.244.19.130, and we get 404 Not Found.  Knowing the host name tells us much more than just knowing the IP address.

There's just one caveat when using these functions:  depending on your DBAs and your installation, you may not have access to use the network functions.   If you get an error like

ORA-24247: network access denied by access control list (ACL) 

then it's time to visit the DBAs ard request that they add your schema or your user account to the network ACL.

Finally, here's an Oracle PL/SQL function that accepts either a host name or an IP address and returns either an IP address or a hostname.  The function starts by using a regular expression to identify the arguement as either a name or an address, and then the function calls the utl_inaddr to do the translation.  Notice at the end we have an exception block, to make sure we always return something to the caller. 

function resolve_host( host varchar2) return varchar2 is

  v_return    varchar2(256);

begin

  if regexp_like(host,'[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+') then

    -- It's an IP address
    select utl_inaddr.get_host_name(host)
    into v_return
    from dual;

  else

    -- It's a hostname
    select utl_inaddr.get_host_address(host)
    into v_return
    from dual;

  end if;

  return v_return;

exception
    when others then
      return host;

end resolve_host;