Shulou(Shulou.com)05/31 Report--

This article introduces you how to restrict IP access in Oracle database, the content is very detailed, interested friends can refer to, hope to be helpful to you.

1. Through sqlnet.ora

a. Turn off the firewall on the database server, modify the sqlnet.ora file and put it under $ORACLE_HOME/network/admin. If you don't have one, you can add the following two lines

Tcp.validnode_checking = yes tcp.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 stop lsnrctl 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 report ORA-12547: TNS:lost contact error.

Tcp.invited_nodes means to open a whitelist. All those who are not on the whitelist are denied access. It can also be written as (192.168.31), 192.168.31.0 Universe (24), etc., indicating that this network segment can be accessed.

In addition, there is a parameter tcp.excluded_nodes, which indicates the blacklist. There is no introduction here. Those who are interested can do the experiment by themselves.

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. In order to avoid this situation, you need to use / etc/hosts.allow and / etc/hosts.deny to restrict a certain ip or ip segment before ssh can access the database server. First delete the sqlnet.ora added in the previous experiment, and then restart listening.

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, such as ssh,telnet.

The second all represents all network segments.

b. Modify / etc/hosts.allow

In the previous step, I banned all network segments, so I have 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:allow all: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.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

The ip address can also be written as follows

The form of wildcard 192.168.31.* indicates the network segment 192.168.31.

The network segment / mask 192.168.31.0 Universe 255.255.255.0 also indicates the network segment 192.168.31

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 of 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 also restricts other ip's ssh and database access to the server with some extended knowledge:

Iptables-L-n-- line-numbers # View the iptablesiptables-D INPUT 2 # in the current system to delete the rule numbered 2 in the input chain. The numbered numbers can be shared here on how to restrict IP access in the Oracle database through the previous command. I hope the above content can be of some help and can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.