Tricky Oracle Access by IP Address!!

Author: Brian Peasland

Sometimes it happens that we want to grant access to our Oracle database filtered by IP address. A typical scenario can be where you want to allow a number of IP addresses to connect to your database or ban a number of IP addresses from log in to your database. In this article we will discuss the secrets of restricting access to your database through a list of IP addresses.

Oracle enables you to restrict database access based on IP address by modifying the SQLNET.ORA file. The SQLNET.ORA file is Oracle configuration file that typically resides $ORACLE_HOME/NETWORK/ADMIN directory on UNIX systems and ORACLE_HOME\network\admin directory on Windows systems. If SQLNET.ORA file is not found there then you will have to see if you have a TNS_ADMIN environment variable pointing to a different directory because SQLNET.ORA file can also be stored in the directory specified by the TNS_ADMIN environment variable.

Below steps can be used to authorize users from accessing Oracle database based on their IP Address.

1. Turn On Hostname/IP Checking for Listeners:

Open SQLNET.ORA file in a text editor and add below line

tcp.validnode_checking = yes

2. Supply lists of nodes to be Allowed/Denied:

Now you will have to use tcp.invited_nodes and tcp.excluded_nodes to supply a list of nodes that you want to allow or deny for getting access to your database. Make sure that you always enter localhost as an invited node. Also you must ensure that all node addresses come in one line and no wildcards are used. Remember the list of included nodes have higher precedence over the list of excluded nodes.

tcp.invited_nodes = (localhost,hostname1,hostname2)
tcp.excluded_nodes = (hostname1,hostname2)

One thing that you should keep in mind is that if you are only using the tcp.invited_nodes then only those specific nodes will be allowed to access your database and all other IP addresses will be denied from accessing your database.

Similarly if you are only using tcp.excluded_nodes then only those specific nodes will be denied from getting access your database and all other IP addresses will be allowed to access your database.

3. Restart Listeners:

Finally you will need to restart your listeners by running below commands.

$LSNRCTL STOP
$LSNRCTL START

SQLNET.ORA for Allowed IP Addresses:

Suppose you want to allow users from IP addresses 70.127.349.101 and 70.127.349.160 only to access your database. In such scenario your SQLNET.ORA file will look like

tcp.validnode_checking = yes
tcp.invited_nodes = (localhost,70.127.349.101,70.127.349.160)

SQLNET.ORA for Banned IP Addresses:

Suppose you want to ban users from IP addresses 70.127.349.216, 192.176.420.301 and 70.127.349.191 from getting access to your database. In such scenario your SQLNET.ORA file will look like

tcp.validnode_checking = yes
tcp.excluded_nodes = (70.127.349.216, 192.176.420.301, 70.127.349.191)

Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle Security Guide: Beware of all DBAs having full OS access!!
   Great Tips on solving Temporary Tablespace Problems!
   Beware of Database Myths
   Oracle: Achieving performance goal - Part II