摘要：
今天听了恩墨联合墨天轮平台分享的数据库安全专题直播，了解到一个新词：数据库白名单，马上用起来
出于提高数据库安全性等目地，考虑对oracle的访问进行限制，通过允许一些IP连接数据库或拒绝一些IP访问数据库进行控制；
当然也可以通过防火墙策略完成，但基于root账号与应用账号权限分离，出于oracle设置会更为方便维护，而在oracle数据库层面，
通过配置sqlnet.ora 文件进行控制，若该文件有则进行编辑，若无则直接新建。
说明：以下实验以新建配置文件 sqlnet.ora 为例
配置文件sqlnet.ora
sqlnet.ora 配置文件路径一般是：$ORACLE_HOME/network/admin/
#cat $ORACLE_HOME/network/admin/sqlnet.ora tcp.validnode_checking=yes tcp.invited_nodes=(192.168.100.28,127.0.0.1) #tcp.excluded_nodes=(192.168.100.1)
配置说明
1）tcp.validnode_checking 设置为yes，启用策略
2）tcp.invited_nodes ：允许访问的IP或者主机名，也可以叫做白名单
3）tcp.excluded_nodes：不允许访问的主机名或者IP
一般来说，tcp.invited_nodes 及tcp.excluded_nodes，我们配置配置一项即可
配置生效：
1）若是第一次配置，需要重启；影响：造成连接中断，要考虑应用停机维护
– 关闭监听
lsnrctl stop
– 启动监听
lsnrctl start
2）再次配置的，只需要重载即可；影响：不影响已有或者叫做现有连接，对业务的影响较小
– 监听重载
lsnrctl reload
获取白名单IP方式
若设置白名单，可从应用部署方面考虑，获取连接数据库的应用服务器IP地址；也可以从监听日志获取一段时间的访问数据库的IP地址，并整理确认改IP地址访问是否合规。
linux下，监听日志获取方法，可使用sed获取今天访问的IP，例如：
fgrep "14-JUL-2022 " listener.log|fgrep "establish" |awk -F '*' '{print $1 " " $3} '|awk -F '(' '{print $4}'|awk -F "=" '{print $2}' |awk -F ")" '{print $1}'|sort -n |uniq -c
配置完成后，进行验证
1）若未配置本机的IP地址，监听的时候，提示报错如下：
– 启动监听报错
[oracle@orcl10028 admin]$ lsnrctl start LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 14-JUL-2022 16:04:59 Copyright (c) 1991, 2011, Oracle. All rights reserved. Starting /opt/oracle/app/product/11.2.0/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.3.0 - Production System parameter file is /opt/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora Log messages written to /opt/oracle/app/diag/tnslsnr/orcl10028/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.220.128)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.100.28)(PORT=1521))) TNS-12537: TNS:connection closed TNS-12560: TNS:protocol adapter error TNS-00507: Connection closed Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 14-JUL-2022 16:04:59 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /opt/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /opt/oracle/app/diag/tnslsnr/orcl10028/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.220.128)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "orcl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
– 本地连接测试
[oracle@orcl10028 admin]$ tnsping orcl TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-JUL-2022 16:05:52 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: /opt/oracle/app/product/11.2.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.28)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl))) TNS-12547: TNS:lost contact
将本地IP添加进去后，并重启/重载监听
[oracle@orcl10028 admin]$ tnsping lims TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 14-JUL-2022 16:39:11 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: /opt/oracle/app/product/11.2.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.100.209)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = lims))) OK (0 msec)
2）未配置白名单IP，会拒绝连接过来，登录提示 ”ORA-12547:TNS:丢失连接“
