如何禁止特定IP访问Oracle数据库

最新推荐文章于 2021-04-05 04:09:14 发布

littlecatnotfat

最新推荐文章于 2021-04-05 04:09:14 发布

阅读量566

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle技术文章标签： oracle network 数据库 tcp 2010 数据库服务器

本文链接：https://blog.youkuaiyun.com/littlecatnotfat/article/details/6644340

Oracle技术专栏收录该内容

2 篇文章

订阅专栏

本文介绍如何通过修改sqlnet.ora文件限制特定IP地址访问Oracle数据库。包括确认客户端IP、验证数据库连通性、配置IP限制及重启监听器等步骤。

默认的服务器端sqlnet.ora文件的内容：

　　这里我们以Oracle10.2.0.3版本为例进行简述,先来看一下当前sqlnet.ora文件内容：

　　# This file is actually generated by netca. But if customers choose to
　　# install "Software Only", this file wont exist and without the native
　　# authentication, they will not be able to connect to the database on NT.
　　SQLNET.AUTHENTICATION_SERVICES = (NTS)

　　2.确认客户端的IP地址：

　　C:\Documents and Settings\Administrator>ipconfig
　　WindowsIP Configuration
　　Ethernet adapter Local Area Connection 2:
　　Media State . . . . . . . . . . . : Media disconnected
　　Ethernet adapter Local Area Connection:
　　Connection-specific DNS Suffix . :
　　IP Address. . . . . . . . . . . . : 9.123.112.16
　　Subnet Mask . . . . . . . . . . . : 255.255.255.0
　　Default Gateway . . . . . . . . . : 9.123.112.1

　　3.在客户端分别使用tnsping命令和sqlplus命令来验证数据库的连通性：

　C:\Documents and Settings\Administrator>tnsping irmdb
　　TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 06-APR-2010 11:05:09
　　Copyright (c) 1997, 2006, Oracle. All rights reserved.
　　Used parameter files:
　　C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
　　Used TNSNAMES adapter to resolve the alias
　　Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
　　(HOST = 9.123.112.34)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = irmdb)))
　　OK (20 msec)
　　C:\Documents and Settings\Administrator>sqlplus /nolog
　　SQL*Plus: Release 10.2.0.3.0 - Production on Tue Apr 6 11:05:12 2010
　　Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
　　Connected.

　　到这里说明在客户端两种方式都证明的数据库的可连通性。

　　4.限制客户端IP地址9.123.112.16对当前irmdb数据库的访问：

　　我们只需要在服务器端的sqlnet.ora文件中添加下面的内容即可。

　　第一行的含义：开启IP限制功能;

　　第二行的含义：允许访问数据库的IP地址列表，多个IP地址使用逗号分开，此例中我们写入数据库服务器的IP地址;

　　第三行的含义：禁止访问数据库的IP地址列表，多个IP地址使用逗号分开，此处我们写入欲限制的IP地址9.123.112.16。

　　5.重新启服务器端listener后生效(这里也可以通过lsnrctl reload方式实现)：

　　C:\Documents and Settings\Administrator>lsnrctl stop
　　LSNRCTL for 32-bit Windows: Version 10.2.0.3.0 - Production on 06-APR-2010 11:07:48
　　Copyright (c) 1991, 2006, Oracle. All rights reserved.
　　Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rmesvr34.cn.ibm.com)(POR
　　T=1521)))
　　The command completed successfully
　　C:\Documents and Settings\Administrator>lsnrctl start
　　LSNRCTL for 32-bit Windows: Version 10.2.0.3.0 - Production on 06-APR-2010 11:07:52
　　Copyright (c) 1991, 2006, Oracle. All rights reserved.
　　Starting tnslsnr: please wait...
　　TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 - Production
　　System parameter file is C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
　　Log messages written to C:\oracle\product\10.2.0\db_1\network\log\listener.log
　　Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rmesvr34.cn.ibm.com)(POR
　　T=1521)))
　　Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rmesvr34.cn.ibm.com)(POR
　　T=1521)))
　　STATUS of the LISTENER
　　------------------------
　　Alias LISTENER
　　Version TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 - Produ
　　ction
　　Start Date 06-APR-2010 11:07:53
　　Uptime 0 days 0 hr. 0 min. 2 sec
　　Trace Level off
　　Security ON: Local OS Authentication
　　SNMP OFF
　　Listener Parameter File C:\oracle\product\10.2.0\db_1\network\admin\listener.o
　　ra
　　Listener Log File C:\oracle\product\10.2.0\db_1\network\log\listener.log
　　Listening Endpoints Summary...
　　(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rmesvr34.cn.ibm.com)(PORT=1521)))
　　Services Summary...
　　Service "PLSExtProc" has 1 instance(s).
　　Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
　　The command completed successfully
　　C:\Documents and Settings\Administrator>sqlplus /nolog
　　SQL*Plus: Release 10.2.0.3.0 - Production on Tue Apr 6 11:07:57 2010
　　Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
　　SQL> conn / as sysdba;
　　Connected.
　　SQL> alter system register;
　　System altered.
　　SQL> quit
　　Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Pr
　　oduction
　　With the Partitioning, OLAP and Data Mining options
　　C:\Documents and Settings\Administrator>lsnrctl status
　　LSNRCTL for 32-bit Windows: Version 10.2.0.3.0 - Production on 06-APR-2010 11:08:05
　　Copyright (c) 1991, 2006, Oracle. All rights reserved.
　　Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rmesvr34.cn.ibm.com)(PORT=1521)))
　　STATUS of the LISTENER
　　------------------------
　　Alias LISTENER
　　Version TNSLSNR for 32-bit Windows: Version 10.2.0.3.0 - Produ
　　ction
　　Start Date 06-APR-2010 11:07:53
　　Uptime 0 days 0 hr. 0 min. 12 sec
　　Trace Level off
　　Security ON: Local OS Authentication
　　SNMP OFF
　　Listener Parameter File C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
　　Listener Log File C:\oracle\product\10.2.0\db_1\network\log\listener.log
　　Listening Endpoints Summary...
　　(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rmesvr34.cn.ibm.com)(PORT=1521)))
　　Services Summary...
　　Service "PLSExtProc" has 1 instance(s).
　　Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
　　Service "irmdb" has 1 instance(s).
　　Instance "irmdb", status READY, has 1 handler(s) for this service...
　　Service "irmdb_XPT" has 1 instance(s).
　　Instance "irmdb", status READY, has 1 handler(s) for this service...
　　The command completed successfully

　　6.在客户端(9.123.112.16)分别再次使用tnsping命令和sqlplus命令来验证数据库的连通性：

　　C:\Documents and Settings\Administrator>tnsping irmdb
　　TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 06-APR-2010 11:09:20
　　Copyright (c) 1997, 2006, Oracle. All rights reserved.
　　Used parameter files:
　　C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora
　　Used TNSNAMES adapter to resolve the alias
　　Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
　　(HOST = 9.123.112.34)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = irmdb)))
　　TNS-12537: TNS:connection closed
　　C:\Documents and Settings\Administrator>sqlplus /nolog
　　SQL*Plus: Release 10.2.0.3.0 - Production on Tue Apr 6 11:09:23 2010
　　Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
　　ERROR:
　　ORA-12537: TNS:connection closed

　　到这里我们就可以证明，通过修改sqlnet.ora的方法，我们实现了限制指定机器IP访问数据库的功能。

　　最后需要特别注意的是tcp.invited_nodes允许列表的优先级要高于tcp.excluded_nodes受限IP列表。也就是说如果同一个IP地址如果同时出现在两个列表里的话，那么这个IP地址还是可以访问对应的数据库的。

note:网上找到来的，不是原创。贴在这里是为了以后怕我忘记。