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

[methods] how to restrict IP access to Oracle database

1.1 BLOG document structure map

1.2Preface 1.2.1 introduction and precautions for technical enthusiasts, after reading this article, you can master the following skills, and you can also learn some other knowledge that you do not know, ~ O (∩ _ ∩) obsession:

Three methods for ① to restrict IP access to Oracle Database (key points)

How ② writes information to the alarm log of Oracle

③ RAISE_APPLICATION_ERROR cannot throw an error to the client environment

④ system trigger

⑤ implied parameter: _ system_trig_enabled

1.3 this article introduces in detail three ways to restrict IP addresses to log in to Oracle databases.

1.3.1 this article introduces the project of the experimental environment

Source db

Db Typ

RAC

Db version

11.2.0.3.0

Db storage

ASM

OS version and kernel version

RHEL 6.5

Database server IP address

192.168.59.130

Client IP address

192.168.59.1 or 192.168.59.129

1.4 three ways to limit IP access to Oracle database 1.4.1 use login trigger 1.4.1.3 matters needing attention:

The object type triggered by ① can be DATABASE or "username .schema", such as:

AFTER LOGON ON DATABASE

AFTER LOGON ON SCOTT.SCHEMA

② when the triggered object type is DATABASE, the logged-in user cannot have the system permission of "ADMINISTER DATABASE TRIGGER"; when the triggered object type is "username .schema", the logged-in user cannot have the system permission of "ALTER ANY TRIGGER". Otherwise, these users will log in to the database normally and just write the corresponding error information to the alarm log. Therefore, users with the IMP_FULL_DATABASE and DBA roles, as well as SYS and EXFSYS users will not be able to restrict login in this way.

The default value of the ③ implicit parameter "_ SYSTEM_TRIG_ENABLED" is TRUE, which allows DDL and system triggers. When the implicit parameter "_ SYSTEM_TRIG_ENABLED" is set to FALSE, DDL and system triggers are disabled. Therefore, login cannot be restricted in this way when this value is set to FALSE.

1.4.2 the second way to make use of sqlnet.ora is to modify the $ORACLE_HOME/network/admin/sqlnet.ora file by adding the following:

TCP.VALIDNODE_CHECKING=YES # enable IP restriction function

TCP.INVITED_NODES= (127.0.0.1 ~ IP1 ~ IP2, …) # allow access to the list of IP addresses of the database. Multiple IP addresses are separated by commas

TCP.EXCLUDED_NODES= (IP1,IP2, …) # prohibit access to the IP address list of the database. Multiple IP addresses are separated by commas

Then restart the listener. In this way, the client will report a "ORA-12537: TNS:connection closed" error when logging in.

Issues to pay attention to:

① needs to set the parameter TCP.VALIDNODE_CHECKING to YES to activate this feature.

② must license or not disable the native IP address of the database server, otherwise listeners will not be able to start or stop listening through lsnrctl, because the process listener accesses the listener through the native IP, and the IP is disabled, but starting or shutting down through the service does not affect it.

③ is mainly configured by TCP.INVITED_NODES when the address set by parameter TCP.INVITED_NODES and TCP.EXCLUDED_NODES is the same.

After the ④ modification, the listening must be restarted to take effect, without the need to restart the database.

⑤ is only suitable for the TCP/IP protocol.

⑥ this configuration is suitable for Oracle 9i and above. The file protocol.ora was used in versions prior to Oracle 9i.

⑦ 's direct connection to the database on the server is not affected.

The restriction method of ⑧ is restricted by listeners.

⑨ this restriction is only for IP detection and is not supported for user name detection.

1.4.3 the third way to make use of the firewall is to modify the IPTABLES of the database server (configuration file: / etc/sysconfig/iptables) to restrict some IP from logging into the database server. As follows:

Iptables-I INPUT-s 192.168.59.129-j DROP

Service iptables save

Then, 192.168.59.129, the host will no longer be able to connect to the database server and will report a "ORA-12170: TNS:Connect timeout occurred" error.

This part can refer to the network configuration, wheat seedlings from the Internet to find a lot.

We can use the following iptables settings to restrict users from accessing the security of the linux operating system where oracle resides.

1. Be clear about the default iptables policy of the operating system

Centos6.0 is installed on my machine. After installation, the system will provide the default policy policy for iptables. First of all, we need to know the default policy.

Iptables-F

2. Develop an IP of port 22 and 1521 to the local area network. In this example, the client ip is 192.168.1.125 the IP of the machine where the Oracle is located is 192.168.1.144. Here, only this client can access ports 22 and 1521, and no other IP in the local area network is allowed to access.

Iptables-An INPUT-s 192.168.1.125 eth0 32-I eth0-p tcp-- dport 22-j ACCEPT

Iptables-An INPUT-s 192.168.1.125 ACCEPT 32-I eth0-p tcp-- dport 1521-j ACCEPT

Iptables-An INPUT-s 192.168.1.0 INPUT 24-p tcp-- dport 22-j DROP

Iptables-An INPUT-s 192.168.1.0 INPUT 24-p tcp-- dport 1521-j DROP

In this way, no IP except 192.168.1.125 on the same network segment can access the database server, not even the ping command.

3. Develop the OUTPUT chain of 22 and 1521 to 192.168.1.125, otherwise the pmon process of oracle instance that has been started cannot be dynamically registered to port 1521.

Iptables-An OUTPUT-d 192.168.1.125 Universe 32-p tcp-- sport 22-j ACCEPT

Iptables-An OUTPUT-d 192.168.1.125 Universe 32-p tcp-- sport 1521-j ACCEPT

4. Save the currently set iptables rules

Service iptables save

At this point, the system will save the set rules to the / etc/sysconfig/iptables file.

Otherwise, all the rules previously set after the restart will become invalid.

Close all ports 80 first.

Open the 80 ports of the 192.168.1.0 / 24 end of the ip segment

Open 80 ports of ip segment at the end of 211.123.16.123 / 24 of ip segment

# iptables-I INPUT-p tcp-- dport 80-j DROP

# iptables-I INPUT-s 192.168.1.0 24-p tcp-- dport 80-j ACCEPT

# iptables-I INPUT-s 211.123.16.123 max 24-p tcp-- dport 80-j ACCEPT

The above are temporary settings.

1. Back up iptables first

# cp / etc/sysconfig/iptables / var/tmp

two。 Then save the iptables

# service iptables save

3. Restart the firewall

# service iptables restart

Here are the ports. Seal them all and then open some IP.

Iptables-I INPUT-p tcp-- dport 9889-j DROP

Iptables-I INPUT-s 192.168.1.0 INPUT 24-p tcp-- dport 9889-j ACCEPT

If NAT forwarding is used, remember to cooperate with the following to take effect.

Iptables-I FORWARD-p tcp-- dport 80-j DROP

Iptables-I FORWARD-s 192.168.1.0 FORWARD 24-p tcp-- dport 80-j ACCEPT

The common IPTABLES rules are as follows:

Can only send and receive email, everything else is closed.

Iptables-I Filter-m mac--mac-source 00:0F:EA:25:51:37-j DROP

Iptables-I Filter-m mac--mac-source 00:0F:EA:25:51:37-p udp-- dport 53-j ACCEPT

Iptables-I Filter-m mac--mac-source 00:0F:EA:25:51:37-p tcp-- dport 25-j ACCEPT

Iptables-I Filter-m mac--mac-source 00:0F:EA:25:51:37-p tcp-- dport 110j ACCEPT

IPSEC NAT strategy

Iptables-I PFWanPriv-d 192.168.100.2-j ACCEPT

Iptables-t nat-A PREROUTING-p tcp-- dport 80-d $INTERNET_ADDR-j DNAT-- to-destination 192.168.100.2 PREROUTING 80

Iptables-t nat-A PREROUTING-p tcp-- dport 1723-d $INTERNET_ADDR-j DNAT-- to-destination 192.168.100.2 PREROUTING 1723

Iptables-t nat-A PREROUTING-p udp-- dport 1723-d $INTERNET_ADDR-j DNAT-- to-destination 192.168.100.2 PREROUTING 1723

Iptables-t nat-A PREROUTING-p udp-- dport 500-d $INTERNET_ADDR-j DNAT-- to-destination 192.168.100.2

Iptables-t nat-A PREROUTING-p udp-- dport 4500-d $INTERNET_ADDR-j DNAT-- to-destination 192.168.100.2

NAT of the FTP server

Iptables-I PFWanPriv-p tcp-- dport 21-d 192.168.100.200-j ACCEPT

Iptables-t nat-A PREROUTING-p tcp-- dport 21-d $INTERNET_ADDR-j DNAT-- to-destination 192.168.100.200

Only access to the specified URL is allowed

Iptables-A Filter-p udp-- dport 53-j ACCEPT

Iptables-A Filter-p tcp-- dport 53-j ACCEPT

Iptables-A Filter-d www.3322.org-j ACCEPT

Iptables-A Filter-d img.cn99.com-j ACCEPT

Iptables-A Filter-j DROP

Open some ports of an IP, others are closed

Iptables-A Filter-p tcp-- dport 80-s 192.168.100.200-d www.pconline.com.cn-j ACCEPT

Iptables-A Filter-p tcp-- dport 25-s 192.168.100.200-j ACCEPT

Iptables-A Filter-p tcp-- dport 109-s 192.168.100.200-j ACCEPT

Iptables-A Filter-p tcp-- dport 110-s 192.168.100.200-j ACCEPT

Iptables-A Filter-p tcp-- dport 53-j ACCEPT

Iptables-A Filter-p udp-- dport 53-j ACCEPT

Iptables-A Filter-j DROP

Multiple port

Iptables-A Filter-p tcp-m multiport-- destination-port 22, 53 REJECT 80110-s 192.168.20.3-j REJECT

Continuous port

Iptables-A Filter-p tcp-m multiport-- source-port 22 source-port 22 53Power80110-s 192.168.20.3-j REJECT iptables-A Filter-p tcp-- source-port 2:80-s 192.168.20.3-j REJECT

Specify the time to surf the Internet

Iptables-A Filter-s 10.10.10.253-m time-- timestart 6:00-- timestop 11:00-- days Mon,Tue,Wed,Thu,Fri,Sat,Sun-j DROP

Iptables-A Filter-m time-- timestart 12:00-- timestop 13:00-- days Mon,Tue,Wed,Thu,Fri,Sat,Sun-j ACCEPT

Iptables-A Filter-m time-- timestart 17:30-- timestop 8:30-- days Mon,Tue,Wed,Thu,Fri,Sat,Sun-j ACCEPT

Disable multiple port services

Iptables-A Filter-m multiport-p tcp-- dport 21 women 23 people 80-j ACCEPT

NAT the WAN port to PC

Iptables-t nat-A PREROUTING-I $INTERNET_IF-d $INTERNET_ADDR-j DNAT-- to-destination 192.168.0.1

NAT the WAN port 8000 to 192. one hundred and sixty eight. one hundred. Port 80 of 200

Iptables-t nat-A PREROUTING-p tcp-- dport 8000-d $INTERNET_ADDR-j DNAT-- to-destination 192.168.100.200

The port to be transferred to the MAIL server

Iptables-t nat-A PREROUTING-p tcp-- dport 110-d $INTERNET_ADDR-j DNAT-- to-destination 192.168.100.200

Iptables-t nat-A PREROUTING-p tcp-- dport 25-d $INTERNET_ADDR-j DNAT-- to-destination 192.168.100.200

Only PING 202is allowed. ninety-six. one hundred and thirty four. 133, all other services are prohibited

Iptables-A Filter-p icmp-s 192.168.100.200-d 202.96.134.133-j ACCEPT

Iptables-A Filter-j DROP

Disable BT configuration

Iptables-A Filter-p tcp-dport 6000 tcp 20000-j DROP

Disable QQ Firewall configuration

Iptables-A Filter-p udp-- dport! 53-j DROP

Iptables-A Filter-d 218.17.209.0 Filter 24-j DROP

Iptables-A Filter-d 218.18.95.0 Filter 24-j DROP

Iptables-A Filter-d 219.133.40.177-j DROP

Based on MAC, you can only send and receive email, and reject everything else.

Iptables-I Filter-m mac--mac-source 00:0A:EB:97:79:A1-j DROP

Iptables-I Filter-m mac--mac-source 00:0A:EB:97:79:A1-p tcp-- dport 25-j ACCEPT

Iptables-I Filter-m mac--mac-source 00:0A:EB:97:79:A1-p tcp-- dport 110j ACCEPT

Disable MSN configuration

Iptables-A Filter-p udp-- dport 9-j DROP

Iptables-A Filter-p tcp-- dport 1863-j DROP

Iptables-A Filter-p tcp-- dport 80-d 207.68.178.238-j DROP

Iptables-A Filter-p tcp-- dport 80-d 207.46.110.0 max 24-j DROP

Only PING 202is allowed. ninety-six. one hundred and thirty four. 133 PING is not allowed in other public network IP

Iptables-A Filter-p icmp-s 192.168.100.200-d 202.96.134.133-j ACCEPT

Iptables-A Filter-p icmp-j DROP

Disable a MAC address from accessing internet:

Iptables-I Filter-m mac--mac-source 00:20:18:8F:72:F8-j DROP

Disable PING for an IP address:

Iptables-A Filter-p icmp-s 192.168.0.1-j DROP

Disable an IP address service:

Iptables-A Filter-p tcp-s 192.168.0.1-- dport 80-j DROP

Iptables-A Filter-p udp-s 192.168.0.1-- dport 53-j DROP

Only some services are allowed, others are denied (2 rules)

Iptables-A Filter-p tcp-s 192.168.0.1-- dport 1000-j ACCEPT

Iptables-A Filter-j DROP

Disable a port service for a certain IP address

Iptables-A Filter-p tcp-s 10.10.10.253-- dport 80-j ACCEPT

Iptables-A Filter-p tcp-s 10.10.10.253-- dport 80-j DROP

Disable a port service for a certain MAC address

Iptables-I Filter-p tcp-m mac--mac-source 00:20:18:8F:72:F8-- dport 80-j DROP

Disable a MAC address from accessing internet:

Iptables-I Filter-m mac--mac-source 00VOG 11VOV 2233MULTIZOUR 44MAV 55-j DROP

Disable PING for an IP address:

Iptables-A Filter-p icmp-s 192.168.0.1-j DROP

1.5 this article summarizes that in Oracle, there are three ways to restrict specific IP access to the database. The first is to use login triggers, as follows:

CREATE OR REPLACE TRIGGER CHK_IP_LHR

AFTER LOGON ON DATABASE

DECLARE

V_IPADDR VARCHAR2 (30)

V_LOGONUSER VARCHAR2 (60)

BEGIN

SELECT SYS_CONTEXT ('USERENV',' IP_ADDRESS')

SYS_CONTEXT ('USERENV',' SESSION_USER')

INTO V_IPADDR, V_LOGONUSER

FROM DUAL

IF V_IPADDR LIKE ('192.168.59%') THEN

RAISE_APPLICATION_ERROR ('- 2000 years, 'User' | | V_LOGONUSER | |'is not allowed to connect from'| | V_IPADDR)

END IF

END

/

Issues to pay attention to:

The object type triggered by ① can be DATABASE or "username .schema", such as:

AFTER LOGON ON DATABASE

AFTER LOGON ON SCOTT.SCHEMA

② when the triggered object type is DATABASE, the logged-in user cannot have the system permission of "ADMINISTER DATABASE TRIGGER"; when the triggered object type is "username .schema", the logged-in user cannot have the system permission of "ALTER ANY TIGGER". Otherwise, these users will log in to the database normally and just write the corresponding error information to the alarm log. Therefore, users with the IMP_FULL_DATABASE and DBA roles, as well as SYS and EXFSYS users will not be able to restrict login in this way.

The default value of the ③ implicit parameter "_ SYSTEM_TRIG_ENABLED" is TRUE, which allows DDL and system triggers. When the implicit parameter "_ SYSTEM_TRIG_ENABLED" is set to FALSE, DDL and system triggers are disabled. Therefore, login cannot be restricted in this way when this value is set to FALSE.

The second is to modify the $ORACLE_HOME/network/admin/sqlnet.ora file by adding the following:

TCP.VALIDNODE_CHECKING=YES # enable IP restriction function

TCP.INVITED_NODES= (127.0.0.1 ~ IP1 ~ IP2, …) # allow access to the list of IP addresses of the database. Multiple IP addresses are separated by commas

TCP.EXCLUDED_NODES= (IP1,IP2, …) # prohibit access to the IP address list of the database. Multiple IP addresses are separated by commas

Then restart the listener. In this way, the client will report a "ORA-12537: TNS:connection closed" error when logging in.

Issues to pay attention to:

① needs to set the parameter TCP.VALIDNODE_CHECKING to YES to activate this feature.

② must license or not disable the native IP address of the database server, otherwise listeners will not be able to start or stop listening through lsnrctl, because the process listener accesses the listener through the native IP, and the IP is disabled, but starting or shutting down through the service does not affect it.

③ is mainly configured by TCP.INVITED_NODES when the address set by parameter TCP.INVITED_NODES and TCP.EXCLUDED_NODES is the same.

After the ④ modification, the listening must be restarted to take effect, without the need to restart the database.

⑤ is only suitable for the TCP/IP protocol.

⑥ this configuration is suitable for Oracle 9i and above. The file protocol.ora was used in versions prior to Oracle 9i.

⑦ 's direct connection to the database on the server is not affected.

The restriction method of ⑧ is restricted by listeners.

⑨ this restriction is only for IP detection and is not supported for user name detection.

The third is to modify the database server's IPTABLES (configuration file: / etc/sysconfig/iptables) to restrict some IP from logging in to the database server. As follows:

Iptables-An INPUT-s 192.168.59.1 eth0 32-I eth0-p tcp-- dport 1521-j DROP

Service iptables save

The 192.168.59.1 host will no longer be able to connect to the database server through port 1521 and will report a "ORA-12170: TNS:Connect timeout occurred" error.

About Me

.

● author: wheat seedlings, only focus on the database technology, pay more attention to the application of technology

● article is updated synchronously on itpub (http://blog.itpub.net/26736162), blog Park (http://www.cnblogs.com/lhrbest) and personal Wechat official account (xiaomaimiaolhr).

● article itpub address: http://blog.itpub.net/26736162/viewspace-2135609/

● article blog park address: http://www.cnblogs.com/lhrbest/p/6575975.html

● pdf version of this article and wheat seedling cloud disk address: http://blog.itpub.net/26736162/viewspace-1624453/

● QQ group: 230161599 WeChat group: private chat

● contact me, please add QQ friend (642808185), indicate the reason for adding

● was completed in Taixing apartment from 08:00 on 2017-03-18 to 22:00 on March 18 of 2017.

The content of the ● article comes from the study notes of wheat seedlings, and some of it is sorted out from the Internet. Please forgive me if there is any infringement or improper place.

Copyright ● all rights reserved, welcome to share this article, please reserve the source for reprint

.

Pick up your phone and use Wechat client to scan the picture on the left below to follow the Wechat official account of wheat seedlings: xiaomaimiaolhr, scan the QR code on the right to join the QQ group of wheat seedlings, and learn the most practical database technology.

Cdn.qqmail.com/zh_CN/htmledition/p_w_picpaths/function/qm_open/ico_mailme_02.png ">

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.