Shulou(Shulou.com)06/01 Report--

This article mainly introduces the Oracle database how to restrict IP access, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

I. Overview

This article will show you how to restrict access to a certain IP or a certain IP segment in an Oracle database.

Through sqlnet.ora

Through / etc/hosts.deny and / etc/hosts.allow

Through iptables

II. Formal experiment

The experimental environment is Centos6.10 + Oracle 11.2.0.4 single instance, and the database server ip address is 192.168.31.71.

1. Through sqlnet.ora

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

The file is placed under $ORACLE_HOME/network/admin. If not, create one in that directory to add the following two lines

Tcp.validnode_checking = yestcp.invited_nodes = (192.168.31.71, 192.168.31.77)

It should be noted here that the native ip address must be added (cannot be written as localhost and 127.0.0.1), otherwise the listening startup will report an error.

b. Restart monitoring to allow sqlnet.ora changes to take effect

Lsnrctl stoplsnrctl start

After setting, only these two ip addresses 192.168.31.71 and 192.168.31.77 can access the database, and other ip addresses will be reported to ORA-12547: TNS:lost contact error tcp.invited_nodes means to open a whitelist, and all those who are not in the whitelist can be denied access, it can also be written as (192.168.31.31, 192.168.31.0max 24) and so on. It shows that this network segment can be accessed, and there is another parameter, tcp.excluded_nodes, which indicates the blacklist. No introduction is made here, and those who are interested can do the experiment themselves.

(recommended tutorial: Oracle tutorial)

two。 Through / etc/hosts.deny and / etc/hosts.allow

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

First delete the sqlnet.ora added in the previous lab, and then restart the monitoring

Lsnrctl stoplsnrctl start

a. Modify / etc/hosts.deny

Add a line at the end of the file

All:all:deny

The first all means to disable all services that use the tcp_ wrapperslibrary, for example, services such as ssh,telnet, and the second all represents all network segments.

b. Modify / etc/hosts.allow

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

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

All:192.168.31.71:allowall:192.168.31.47:allow

The format is the same as hosts.deny, with the first line indicating the release of the machine and the second line indicating the activation of the whitelist for .47

If you use my other machine (that is, not in allow) ssh or telnet to connect to this machine 71, you will get the following error

[oracle@oracle19c1 ~] $ssh 192.168.31.71ssh_exchange_identification: read: Connection reset by peer [oracle@oracle19c1 ~] $telnet 192.168.31.71 22Trying 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 sysdbaSQL*Plus: Release 19.0.0.0.0-Production on Sun Aug 16 23:12:49 2020Version 19.3.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

The ip address can also be replaced with the following wildcard form 192.168.31.* to indicate 192.168.31 this network segment / mask 192.168.31 network segment / mask 255.255.255.0 also means 192.168.31 network segment

3. Through iptables

Sqlnet.ora can restrict database access, / etc/hosts.deny and / etc/hosts.allow can restrict ssh access, is there any way to restrict both database access and ssh access? the answer is the firewall function that comes with linux. For the purpose of the experiment, clear all the previous changes.

Use root to execute the following command

Service iptables start # turn on firewall service iptables-I INPUT-s 192.168.31.0 ACCEPT 24-p tcp-- dport 1521-j ACCEPT # allow ip of 192.168.31 network segment to access native port 1521 iptables-I INPUT!-s 192.168.31.0 impulse 24-p tcp-- dport 22-j DROP # deny ip access to native port 22 service iptables save # rules are saved to configuration file / etc/sysconfig/iptables

This restricts other ip's ssh and database access to the server at the same time

Some extended knowledge: iptables-L-n-- line-numbers # View iptablesiptables-D INPUT 2 # in the current system delete the rule numbered 2 in the input chain, the numbering can be obtained from the previous command

Thank you for reading this article carefully. I hope the article "how to restrict IP access to Oracle Database" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.