Connect to Oracle through a firewall

First some assumptions:

  • You connect to a remote Oracle Server using TCP/IP
  • The remote server/network is protected by a Firewall
  • The connection not uses “Oracle Shared Server”, “Oracle Connection Manager” or “Resident Connection Pooling” (is a standard dedicated connection).

These are lots of assumptions, but the vast majority of Oracle installations are agree with all of them. If all the above are true, then the connection mus go this way (image from public Oracle Documentation):

oracleconnection

The client connects to the IP and PORT where the listener are listening on (one listener can listen in multiple IP:PORT combinations on same host). The listener creates a new “dedicated server process” on the server host and the client connects directly to the “dedicated server process”.

In more detail at network level on the client we can find this:

root@client:~# netstat -pan
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
...
tcp        0      0 192.168.56.1:49858      192.168.56.101:1521     ESTABLISHED 8627/sqlplus
...

And on the server this:

[root@ol7-121-rac1 ~]# netstat -pan | grep 192.168.56.101:1521
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
...
tcp        0      0 192.168.56.101:1521     0.0.0.0:*               LISTEN      840/tnslsnr
tcp        0      0 192.168.56.101:1521     192.168.56.1:49858      ESTABLISHED 16174/orcl
...

We can view on the client a connection to one of the IP’s of the server (192.168.51.101) on port 1521 (the default port for the listener), the connection originates on one of the client IP’s (192.168.56.1) and over an “ephemeral port” (one free port from range 32768 to 61000 in most cases, and the port 49858 in this particular one).

And on the server we have two processes using port 1521, one in status ESTABLISHED (the one used by our connection to the ORCL database) and another in status LISTENING used by the listener to accept new incoming connections.

Then the rule to add to our firewall for the Oracle connections must allow pass packets with destination the IP and PORT where the database listener is listening on.

In case of a “iptables” firewall must be something like this:

iptables -A INPUT -p tcp --dport 1521 -j ACCEPT

As you can view on the server side two processes are using the same port on the same IP and some people think only one process can use every IP:PORT combination (for example me, until some time ago).

One connection must be defined by four values ORIGIN_IP:ORIGIN_PORT,DESTINATION_IP:DESTINATION_PORT and with these values the operating system knows the process to send the information coming from that connection, this four values needs to be always unique, in our case are:

192.168.56.101:1521 192.168.56.1:49858

And for example if I open another connection from the same client to the same database appears this:

[root@ol7-121-rac1 ~]# netstat -pan | grep 192.168.56.101:1521
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
...
tcp        0      0 192.168.56.101:1521     192.168.56.1:49858      ESTABLISHED 16174/oracledos1
tcp        0      0 192.168.56.101:1521     192.168.56.1:53826      ESTABLISHED 28850/oracledos1  ...

The four values continues to be unique and the kernel knows where to send incoming packages of data (and the same thing happens on client side)

root@client:~# netstat -pan
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
...
tcp        0      0 192.168.56.1:53826      192.168.56.101:1521     ESTABLISHED 9662/sqlplus
tcp        0      0 192.168.56.1:49858      192.168.56.101:1521     ESTABLISHED 8627/sqlplus

The “Oracle Server Processes” used by the clients to work on the database in most of the cases uses the same IP:PORT as the listener, then, only one rule is needed on the firewall to allow the Oracle traffic.

Finally, and the line corresponding to the listener from the above output?

[root@ol7-121-rac1 ~]# netstat -pan | grep 192.168.56.101:1521
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
...
tcp        0      0 192.168.56.101:1521     0.0.0.0:*               LISTEN      840/tnslsnr
...

Well as you can see on the “Foreing Address” appears a wildcard of all possible values, then only one process can have the status “LISTEN” on one combination of local IP:PORT, and this process receives all new incoming connections, the rest of the processes (on status STABLISHED) receive only data belonging to they particular TCP/IP connection.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s