一、背景： 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 (c) 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 (c) 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 (c) 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 (c) 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
