How to Restrict IP Access to Oracle Database Using sqlnet.ora, Hosts Files, and iptables

Overview

The article presents three ways to limit access to an Oracle database by IP address or subnet: editing sqlnet.ora, using TCP‑wrapper files /etc/hosts.deny and /etc/hosts.allow, and configuring iptables firewall rules.

Experiment Environment

CentOS 6.10 with Oracle 11.2.0.4 single instance; the database server IP is 192.168.31.71.

1. Restrict via sqlnet.ora

Edit the file located at $ORACLE_HOME/network/admin/sqlnet.ora (create it if missing) and add the following lines, then restart the listener.

tcp.validnode_checking = yes</code>
<code>tcp.invited_nodes = (192.168.31.71, 192.168.31.77)

Restart commands:

lsnrctl stop</code>
<code>lsnrctl start

After the change only the two listed IPs can connect; other IPs receive ORA-12547: TNS:lost contact. The parameter tcp.invited_nodes defines a whitelist (e.g., (192.168.31.*, 192.168.31.0/24)). A blacklist can be set with tcp.excluded_nodes, though it is not covered here.

2. Restrict via /etc/hosts.deny and /etc/hosts.allow

Because sqlnet.ora protects only the database layer, SSH access must also be limited. Use TCP‑wrapper files to deny all services first, then allow the desired IPs.

Remove the previous sqlnet.ora changes and restart the listener:

lsnrctl stop</code>
<code>lsnrctl start

Add to /etc/hosts.deny: all:all:deny Add to /etc/hosts.allow to whitelist specific hosts:

all:192.168.31.71:allow</code>
<code>all:192.168.31.47:allow

Testing from a non‑whitelisted machine shows SSH and telnet connections are rejected, while the database connection still works because the Oracle service is not governed by the hosts files.

[oracle@oracle19c1 ~]$ ssh 192.168.31.71
ssh_exchange_identification: read: Connection reset by peer

[oracle@oracle19c1 ~]$ sqlplus sys/[email protected]:1521/orcltest as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Note: tcp.invited_nodes also accepts wildcard ( 192.168.31.*) or CIDR ( 192.168.31.0/255.255.255.0) notation.

3. Restrict via iptables

To enforce both database and SSH restrictions with a single firewall, clear previous configurations and apply the following rules as root.

service iptables start
iptables -I INPUT -s 192.168.31.0/24 -p tcp --dport 1521 -j ACCEPT
iptables -I INPUT ! -s 192.168.31.0/24 -p tcp --dport 22 -j DROP
service iptables save

Additional useful commands:

iptables -L -n --line-numbers   # view current rules
iptables -D INPUT 2            # delete rule number 2 (number obtained from previous command)

Conclusion

If you only need to block other IPs from reaching the Oracle database, modify sqlnet.ora.

If you also need to block SSH access to the database server, use /etc/hosts.deny and /etc/hosts.allow.

When you are comfortable with Linux firewalls, apply iptables rules to handle both cases in one place.

Always verify that you retain a way to reach the server before applying hosts.deny or iptables rules, otherwise you may lock yourself out.