当前位置: 首页 > 工具软件 > tns > 使用案例 >

oracle12546权限被拒绝,tnsping遇到TNS-12546: TNS: 权限被拒绝

尤茂材
2023-12-01

从客户端tnsping 一台数据库,报错“TNS-12537:TNS:连接关闭”,奇怪~~,服务器端可以tnsping通,也可以正常访问数据库,listener状态也正常,为什么客户端连接不上,查了一下原因,为sqlnet.ora文件做了Validnode Verification 的限制。

Information from metalink: You can configure the sqlnet.ora file to allow and deny access to the database via the validnode checking parmeters in sqlnet.ora file :

TCP.VALIDNODE_CHECKING

Use to specify whether to screen access to the database.Value is either YES or ON

e.g.TCP.VALIDNODE_CHECKING = YES

TCP.EXCLUDED_NODES

Use to specify which clients using the TCP/IP protocol are denied access to the database. Hostname and ipaddress can be used

e.g.TCP.EXCLUDED_NODES= (138.3.33.33)

TCP.INVITED_NODESUse to specify which clients using the TCP/IP protocol are allowed access to the database. Hostname and ipadddress can be used.

e.g.TCP.INVITED_NODES=(138.4.44.44, hammer)

Please note that without the servers hostname or ipaddress in the invitied list, then PMON will not register with the listener.

Any changes to the values requires the TNS listener to be reloaded

问题重现:

1.客户端tnsping不通

C:\Documents and Settings\Home>tnsping devdb

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 08-5月 -2009 10:49:13

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

已使用的参数文件:

C:\oracle\10.2.0\client_1\network\admin\sqlnet.ora

已使用 TNSNAMES 适配器来解析别名

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)

(HOST = 172.22.3.111)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = devdb)))

TNS-12537: TNS: 连接关闭

2.检查服务器端listener状态

C:\Documents and Settings\Administrator>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 08-5月 -2009 11:08:46

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.22.3.111)(PORT=1521)))

LISTENER 的 STATUS

------------------------

别名                      LISTENER

版本                      TNSLSNR for 32-bit Windows: Version 10.2.0.4.0 - Produ

ction

启动日期                  08-5月 -2009 10:54:52

正常运行时间              0 天 0 小时 13 分 58 秒

跟踪级别                  off

安全性                    ON: Local OS Authentication

SNMP                      OFF

监听程序参数文件          E:\oracle\product\10.2.0\db_1\network\admin\listener.ora

监听程序日志文件          E:\oracle\product\10.2.0\db_1\network\log\listener.log

监听端点概要...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.22.3.111)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))

服务摘要..

服务 "devdb_XPT" 包含 1 个例程。

例程 "devdb", 状态 READY, 包含此服务的 1 个处理程序...

服务 "PLSExtProc" 包含 1 个例程。

例程 "PLSExtProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序...

服务 "devdb" 包含 1 个例程。

例程 "devdb", 状态 READY, 包含此服务的 1 个处理程序...

命令执行成功

3.检查服务器端tnsping状态

C:\Documents and Settings\Administrator>tnsping devdb

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 08-5月 -2009 11:08:33

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

已使用的参数文件:

E:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora

已使用 TNSNAMES 适配器来解析别名

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)

(HOST = 172.22.3.111)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = devdb)))

OK (20 毫秒)

4.检查服务器端$ORACLE_HOME\NETWORK\log\listener.log文件

08-5月 -2009 10:49:43 * 12546

TNS-12546: TNS: 权限被拒绝

TNS-12560: TNS: 协议适配器错误

TNS-00516: 许可被拒绝

08-5月 -2009 10:49:45 * 12546

TNS-12546: TNS: 权限被拒绝

TNS-12560: TNS: 协议适配器错误

TNS-00516: 许可被拒绝

5.检查$ORACLE_HOME\NETWORK\ADMIN\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)

tcp.validnode_checking = YES

tcp.invited_nodes = (182.16.X.X,182.16.X.X,182.23.X.X,172.16.X.X)

果真有访问db的ip限制,把客户端ip地址添加到sqlnet.ora文件中172.22.3.111

6.服务器端重启监听,客户端测试成功

服务器端重启监听

C:\Documents and Settings\Administrator>lsnrctl start

C:\Documents and Settings\Administrator>lsnrctl stop

客户端测试成功

C:\Documents and Settings\Home>tnsping devdb

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 08-5月 -2009 11:18:56

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

已使用的参数文件:

C:\oracle\10.2.0\client_1\network\admin\sqlnet.ora

已使用 TNSNAMES 适配器来解析别名

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)

(HOST = 172.22.3.111)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = devdb)))

OK (30 毫秒)

参考文献:

Subject:

What is Validnode Verification and How to Use It

Doc ID:

462933.1

Type:

HOWTO

Modified Date :

21-APR-2009

Status:

PUBLISHED

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9252210/viewspace-594500/,如需转载,请注明出处,否则将追究法律责任。

 类似资料: