配置数据库监听白名单

╰半夏微凉° 2023-01-21 03:28 23阅读 0赞

一、背景：

2020年伊始，我们的工作中多了一个词"护网行动"，之前闻所未闻；这是一个从国家层面提出的安全概念，目的是为了保障信息安全。各个组织机构会定期组织安防演练。咱们数据库层面为了应对这次安防演练也提出了自己的思想，数据库白名单策略限制非法设备对数据库进行访问。这是这次配置监听白名单的整个背景。

二、技术策略：

编辑sqlnet.ora文件

#开启ip限制功能

tcp.validnode\_checking=yes  
\#允许访问数据库的IP地址列表，多个IP地址使用逗号分开

tcp.invited\_nodes=(192.168.1.5，192.168.1.6，10.10.10.2)  
\#禁止访问数据库的IP地址列表，多个IP地址使用逗号分开

tcp.excluded\_nodes=(192.168.1.1，10.10.10.1)

注：

1、需要重启监听器生效。

2、这个方式只是适合TCP协议，适用于9i以上版本。在9i之前的版本使用文件protocol.ora。

3、第二行和第三行任写一行即可，如果tcp.invited\_nodes与tcp.excluded\_nodes都存在，以tcp.invited\_nodes为主。

4、不要禁止服务器本机的IP地址，否则通过lsnrctl将不能启动或停止监听，因为该过程监听程序会通过本机的IP访问监听器。

三、操作步骤

3.1 从监听日志中获取层级访问的设备地址：

grep HOST listener.log |awk -F ‘HOST=’ ‘\{print $3\}’ |awk ‘\{print $1\}’ |awk -F ‘)’ ‘\{print $1\}’ |grep -v jdbc|sort|uniq |wc -l && grep HOST listener.log |awk -F ‘HOST=’ ‘\{print $3\}’ |awk ‘\{print $1\}’ |awk -F ‘)’ ‘\{print $1\}’ |grep -v jdbc|sort|uniq  
5  
192.168.1.1  
192.168.1.2  
192.168.1.3  
192.168.1.4  
192.168.1.71

3.2 地址格式化

tr -s “\\n” “,” <ip.txt; echo  
192.168.1.1,192.168.1.2,192.168.1.3,192.168.1.4,192.168.1.71

3.3 编辑sqlnet.ora

\[oracle@TestDB/u01/app/oracle/product/11.2.0/db\_1/network/admin\]$cat sqlnet.ora  
tcp.validnode\_checking=yes  
tcp.invited\_nodes=(192.168.1.1,192.168.1.2,192.168.1.3,192.168.1.4,192.168.1.71)

3.4 关闭监听

\[oracle@TestDB/u01/app/oracle/product/11.2.0/db\_1/network/admin\]$lsnrctl stop  
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 28-JUL-2020 19:30:20  
Copyright © 1991, 2013, Oracle. All rights reserved.  
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=TestDB)(PORT=1521)))  
The command completed successfully

3.5 重新启动监听

## \[oracle@TestDB/u01/app/oracle/product/11.2.0/db\_1/network/admin\]$lsnrctl start   LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 28-JUL-2020 19:30:25   Copyright © 1991, 2013, Oracle. All rights reserved.   Starting /u01/app/oracle/product/11.2.0/db\_1/bin/tnslsnr: please wait…   TNSLSNR for Linux: Version 11.2.0.4.0 - Production   System parameter file is /u01/app/oracle/product/11.2.0/db\_1/network/admin/listener.ora   Log messages written to /u01/app/oracle/diag/tnslsnr/TestDB/listener/alert/log.xml   Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TestDB)(PORT=1521)))   Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=TestDB)(PORT=1521)))   STATUS of the LISTENER ##

Alias LISTENER  
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production  
Start Date 28-JUL-2020 19:30:25  
Uptime 0 days 0 hr. 0 min. 0 sec  
Trace Level off  
Security ON: Local OS Authentication  
SNMP OFF  
Listener Parameter File /u01/app/oracle/product/11.2.0/db\_1/network/admin/listener.ora  
Listener Log File /u01/app/oracle/diag/tnslsnr/TestDB/listener/alert/log.xml  
Listening Endpoints Summary…  
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TestDB)(PORT=1521)))  
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))  
The listener supports no services  
The command completed successfully

3.6 手工注册监听

## \[oracle@TestDB/u01/app/oracle/product/11.2.0/db\_1/network/admin\]$sqlplus / as sysdba   SQL\*Plus: Release 11.2.0.4.0 Production on Tue Jul 28 19:30:29 2020   Copyright © 1982, 2013, 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   SQL> alter system register;   System altered.   SQL> !lsnrctl status   LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 28-JUL-2020 19:30:36   Copyright © 1991, 2013, Oracle. All rights reserved.   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=TestDB)(PORT=1521)))   STATUS of the LISTENER ##

Alias LISTENER  
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production  
Start Date 28-JUL-2020 19:30:25  
Uptime 0 days 0 hr. 0 min. 11 sec  
Trace Level off  
Security ON: Local OS Authentication  
SNMP OFF  
Listener Parameter File /u01/app/oracle/product/11.2.0/db\_1/network/admin/listener.ora  
Listener Log File /u01/app/oracle/diag/tnslsnr/TestDB/listener/alert/log.xml  
Listening Endpoints Summary…  
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TestDB)(PORT=1521)))  
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))  
Services Summary…  
Service “ORCL” has 1 instance(s).  
Instance “ORCL1”, status READY, has 1 handler(s) for this service…  
Service “ORCL1XDB” has 1 instance(s).  
The command completed successfully

来自 “开源世界 ” ，链接：[http://ym.baisou.ltd/?id=515][http_ym.baisou.ltd_id_515]，如需转载，请注明出处，否则将追究法律责任。

[http_ym.baisou.ltd_id_515]: http://ym.baisou.ltd/?id=515