How Oracle databases restrict IP access

First, an overview

This article will show you how to restrict an IP access in the Oracle database, or restrict an IP segment to access it.

1. Through sqlnet.ora
2. Via /etc/hosts.deny and /etc/hosts.allow
3. Through iptables

Second, formal experiments

This lab environment is Centos6.10 + Oracle 11.2.0.4 single instance with a DATABASE server ip address of 192.168.31.71

1. Via sqlnet.ora

a. Turn off the firewall on the database server and modify sqlnet.ora file

The file is placed under $ORACLE_HOME/network/admin and if you don't create one in that directory, you can add the following two lines

tcp.validnode_checking = yes
tcp.invited_nodes = (192.168.31.71, 192.168.31.77)

It is important to note here that the native ip address must be added (cannot be written as localhost and 127.0.0.1), otherwise the listening startup will be reported incorrectly

b. Restart listening for changes to sqlnet.ora to take effect

lsnrctl stop
lsnrctl start

After setting up, only these two ip addresses 192.168.31.71 192.168.31.77 have access to the database, and other ip address accesses report ORA-12547: TNS:lost contact error tcp.invited_nodes means whitelisting, not white All access is denied on the list, it can also be written as (192.168.31., 192.168.31.0/24) and other ways, indicating that this segment can be accessed there is also a parameter tcp.excluded_nodes indicating the blacklist, here do not make an introduction, interested can do their own experiments

(Recommended tutorial: Oracle tutorial)

2. By/etc/hosts.deny and/etc/hosts.allow

sqlnet.ora is a database-level limitation, but if an ip can use root or oracle ssh to the database server, it can still access the database. To avoid this, you need to restrict an ip or ip segment to ssh the database server through /etc/hosts.allow and /etc/hosts.deny

Remove sqlnet.ora added by the previous experiment, and then restart the listening

lsnrctl stop
lsnrctl start

a. Modify /etc/hosts.deny

Add a line to the end of the file

all:all:deny

The first all means blocking all services that use tcp_wrappers library, for example, services such as ssh telnet and the second all represents all segments

b. Modify /etc/hosts.allow

In the previous step I banned all segments of the network, so in this step to open the specified segment

Modify /etc/hosts.allow to add at the end of the file

all:192.168.31.71:allow
all:192.168.31.47:allow

In the same format as hosts.deny the first line indicates that the native is released, and the second line indicates that a whitelist is opened for .47

Here's my other machine (i.e., not in alllow) ssh or telnet to connect 71, and you'll see the following error

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


[oracle@oracle19c1 ~]$ telnet 192.168.31.71 22
Trying 192.168.31.71...
Connected to 192.168.31.71.
Escape character is '^]'.
Connection closed by foreign host.

Even the database is not affected because the database service is not managed by hosts.deny and hosts.allow

[oracle@oracle19c1 ~]$ sqlplus sys/xxxxx@192.168.31.71:1521/orcltest as sysdba


SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 16 23:12:49 2020
Version 19.3.0.0.0


Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Where ip address can also be replaced by the following form wildcard form 192.168.31.* 192.168.31 This segment of the net segment/mask 192.168.31.0/255.255.255.0 also represents 192.168.31 segment

3. Through iptables

sqlnet.ora can restrict access to the database, /etc/hosts.deny and /etc/hosts.allow can restrict access to ssh so there is no way to restrict access to both the database and ssh the answer is linux comes with the firewall features. For the experiment, all previous modifications are cleared.

Use root execute the following command

service iptables start  # 打开防火墙服务
iptables -I INPUT -s 192.168.31.0/24 -p tcp --dport 1521 -j ACCEPT  # 允许192.168.31网段的ip访问本机1521端口
iptables -I INPUT ! -s 192.168.31.0/24 -p tcp --dport 22 -j DROP  # 拒绝非192.168.31网段的ip访问本机22端口
service iptables save  # 规则保存到配置文件/etc/sysconfig/iptables中

This restricts both ssh and database access to the server by other ip

Some extended knowledge: iptables -L -n --line-numbers # . view iptables iptables -D INPUT 2 # the current system , remove the rule numbered 2 in the input chain, and the numbered numbers can be obtained by the previous command

(Recommended microserancy: Oracle database getting started to combat)

Third, summary

1. If you are simply restricting access to the database by other ip use sqlnet.ora
2. If you want to restrict ssh connections from other ip to the server on which the database is located, use /etc/hosts.deny and /etc/hosts.allow
3. Together, the first two will basically keep your database secure. But if you're familiar with iptables use iptables directly to limit them. linux
4. Be sure to connect to the server when using /etc/hosts.deny and iptables or you'll easily lock yourself out.

This article comes from (Motian Wheel), Source: www.modb.pro/db/29270?ywm- Author: Yang Leopard

These are W3Cschool编程狮 introductions on how Oracle databases restrict IP access, and I hope this will help you.