配置数据库监听白名单-云搜网

一、背景：

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
|
uniq

5

192.168.1.1

192.168.1.2

192.168.1.3

192.168.1.4

192.168.1.71

3.2 地址格式化

1 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

1
2
3

[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 关闭监听

1
2
3
4
5

[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

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 start

LSNRCTL
for
Linux: Version 11.2.0.4.0 - Production on 28-JUL-2020 19:30:25

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 手工注册监听

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 sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 28 19:30:29 2020

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

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://blog.itpub.net/20674423/viewspace-2707617/

配置数据库监听白名单

相关推荐

热门推荐

分类目录