一、背景：
在合肥等地区，都构建了全面的区域性战略布局，加强发展的系统性、市场前瞻性、产品创新能力，以专注、极致的服务理念，为客户提供网站制作、网站设计 网站设计制作按需网站建设,公司网站建设,企业网站建设,品牌网站建设,全网营销推广,成都外贸网站制作,合肥网站建设费用合理。
2020年伊始，我们的工作中多了一个词"护网行动"，之前闻所未闻；这是一个从国家层面提出的安全概念，目的是为了保障信息安全。各个组织机构会定期组织安防演练。咱们数据库层面为了应对这次安防演练也提出了自己的思想，数据库白名单策略限制非法设备对数据库进行访问。这是这次配置监听白名单的整个背景。
二、技术策略：
编辑sqlnet.ora文件
#开启ip限制功能
1 |
tcp.validnode_checking=
yes |
#允许访问数据库的IP地址列表，多个IP地址使用逗号分开
1 |
tcp.invited_nodes=(192.168.1.5，192.168.1.6，10.10.10.2) |
#禁止访问数据库的IP地址列表，多个IP地址使用逗号分开
1 |
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 从监听日志中获取层级访问的设备地址：
1 2 3 4 5 6 7 |
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
|
uniq5192.168.1.1192.168.1.2192.168.1.3192.168.1.4192.168.1.71 |
3.2 地址格式化
1 2 |
tr
-s
"\n"
","
192.168.1.1,192.168.1.2,192.168.1.3,192.168.1.4,192.168.1.71 |
3.3 编辑sqlnet.ora
1 2 3 |
[oracle@TestDB
/u01/app/oracle/product/11
.2.0
/db_1/network/admin
]$
cat
sqlnet.ora tcp.validnode_checking=
yestcp.invited_nodes=(192.168.1.1,192.168.1.2,192.168.1.3,192.168.1.4,192.168.1.71) |
3.4 关闭监听
1 2 3 4 5 |
[oracle@TestDB
/u01/app/oracle/product/11
.2.0
/db_1/network/admin
]$lsnrctl stopLSNRCTL
for
Linux: Version 11.2.0.4.0 - Production on 28-JUL-2020 19:30:20Copyright (c) 1991, 2013, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=TestDB)(PORT=1521)))The
command
completed successfully |
3.5 重新启动监听
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
[oracle@TestDB
/u01/app/oracle/product/11
.2.0
/db_1/network/admin
]$lsnrctl startLSNRCTL
for
Linux: Version 11.2.0.4.0 - Production on 28-JUL-2020 19:30:25Copyright (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 - ProductionSystem parameter
file
is
/u01/app/oracle/product/11
.2.0
/db_1/network/admin/listener
.oraLog messages written to
/u01/app/oracle/diag/tnslsnr/TestDB/listener/alert/log
.xmlListening 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 LISTENERVersion TNSLSNR
for
Linux: Version 11.2.0.4.0 - ProductionStart Date 28-JUL-2020 19:30:25Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File
/u01/app/oracle/product/11
.2.0
/db_1/network/admin/listener
.oraListener Log File
/u01/app/oracle/diag/tnslsnr/TestDB/listener/alert/log
.xmlListening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TestDB)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))The listener supports no servicesThe
command
completed successfully |
3.6 手工注册监听
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
[oracle@TestDB
/u01/app/oracle/product/11
.2.0
/db_1/network/admin
]$sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 28 19:30:29 2020Copyright (c) 1982, 2013, 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 optionsSQL> alter system register;System altered.SQL> !lsnrctl statusLSNRCTL
for
Linux: Version 11.2.0.4.0 - Production on 28-JUL-2020 19:30:36Copyright (c) 1991, 2013, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=TestDB)(PORT=1521)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR
for
Linux: Version 11.2.0.4.0 - ProductionStart Date 28-JUL-2020 19:30:25Uptime 0 days 0 hr. 0 min. 11 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File
/u01/app/oracle/product/11
.2.0
/db_1/network/admin/listener
.oraListener Log File
/u01/app/oracle/diag/tnslsnr/TestDB/listener/alert/log
.xmlListening 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://blog.itpub.net/20674423/viewspace-2707617/
