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)
Best regards,
Abdoul Rahaman
IT Consultant
Globe Marine Services Co
Jeddah
