代码改变世界

低版本客户端连接高版本数据库报错ORA-28040、ORA-01017

2023-01-17 22:41  AlfredZhao  阅读(1022)  评论(0编辑  收藏  举报

测试环境:

  • 客户端:Oracle 11.2.0.1
  • 服务端:Oracle 19.16

测试过程:

1.低版本客户端连接高版本数据库报错ORA-28040

使用oracle 11.2.0.1 的客户端,对19c的服务端进行连接时,报错:ORA-28040: No matching authentication protocol

C:\Users\Alfred>sqlplus sys/oracle@192.168.1.4/demo as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 1月 17 17:52:30 2023

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-28040: No matching authentication protocol

这个错误其实我在给客户做经验分享类的交流时,反复讲过,解决起来也很简单,同时也有MOS文档 2296947.1 依据:

  • 12.2: ORA-28040 Followed by ORA-1017 When Client is Under Version 12. (Doc ID 2296947.1)

如果在不方便升级客户端的情况下,只能在服务端,配置sqlnet.ora文件:

[oracle@bogon admin]$ pwd
/u01/app/oracle/product/19.3.0/db_1/network/admin
[oracle@bogon admin]$ cat sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=10

非常简单,且不用重启任何服务,不用重载监听,即可生效;
再次连接,不再报错ORA-28040。

2.低版本客户端连接高版本数据库报错ORA-01017

不再报错ORA-28040,但开始报错:ORA-01017: invalid username/password; logon denied

C:\Users\Alfred>sqlplus sys/oracle@192.168.1.4/demo as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 1月 17 21:17:09 2023

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


请输入用户名:

可是输入的密码确认是没问题的,使用高版本的客户端,同样的密码测试连接也是OK的。
那是什么问题呢?想到是密码版本问题,根据MOS 文档 2040705.1:

  • Lockout of all database authenticated users getting error ORA-01017: invalid username/password; logon denied (Doc ID 2040705.1)

When you inspect the DBA_USERS.PASSWORD_VERSIONS you only see 11G and 12C values but not 10G.

查询用户的PASSWORD_VERSIONS:

SQL> select username, password_versions from dba_users where password_versions is not null;

USERNAME		       PASSWORD_VERSIONS
------------------------------ -----------------
SYS			       11G 12C
SYSTEM			       11G 12C
CTXSYS			       11G 12C

With this solution you will also need to change the user password again so the DBA_USERS.PASSWORD_VERSIONS will get a 10G value, however the DES based verifiers are outdated and should only be used in exceptional cases when legacy client applications still need it.

alter user sys identified by oracle;

再次,尝试从11.2.0.1的客户端,对19c的服务端进行连接时,可以成功连接:

C:\Users\Alfred>sqlplus sys/oracle@192.168.1.4/demo as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 1月 17 21:57:33 2023

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> exit
从 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 断开

C:\Users\Alfred>

但是有个疑问,这里再次改过SYS密码后,查询PASSWORD_VERSIONS其实还是没有显示出来:

SQL> select username, password_versions from dba_users where password_versions is not null;

USERNAME		       PASSWORD_VERSIONS
------------------------------ -----------------
SYS			       11G 12C
SYSTEM			       11G 12C
CTXSYS			       11G 12C

考虑到SYS用户的特殊性,那么这里使用SYSTEM用户再次测试观察下:

sqlplus system/oracle@192.168.1.4/demo

同样,报错ORA-01017,输入的密码确认是没问题的,使用高版本的客户端,同样的密码测试连接也是OK的。

C:\Users\Alfred>sqlplus system/oracle@192.168.1.4/demo

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 1月 17 22:10:02 2023

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


请输入用户名:
C:\Users\Alfred>

修改system密码:

SQL> select username, PASSWORD_VERSIONS, PASSWORD_CHANGE_DATE from dba_users where PASSWORD_VERSIONS is not null;

USERNAME		       PASSWORD_VERSIONS PASSWORD_
------------------------------ ----------------- ---------
SYS			       11G 12C		 04-JAN-23
SYSTEM			       11G 12C		 04-JAN-23
CTXSYS			       11G 12C		 04-JAN-23

SQL> alter user system identified by oracle;

User altered.

SQL> select username, PASSWORD_VERSIONS, PASSWORD_CHANGE_DATE from dba_users where PASSWORD_VERSIONS is not null;

USERNAME		       PASSWORD_VERSIONS PASSWORD_
------------------------------ ----------------- ---------
SYS			       11G 12C		 04-JAN-23
SYSTEM			       10G 11G 12C	 17-JAN-23
CTXSYS			       11G 12C		 04-JAN-23

SQL>

看来,SYSTEM用户是比较正常显示的,改过密码后,PASSWORD_VERSIONS多了10G的显示,符合我们预期。
此时,再尝试从11.2.0.1的客户端,对19c的服务端进行连接时,确认system用户也可以成功连接了:

C:\Users\Alfred>sqlplus system/oracle@192.168.1.4/demo

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 1月 17 22:15:23 2023

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL>

其他业务用户,推断应该都和system用户的表现一致,这样也符合逻辑。

这里还观察到一个细节:

修改密码前,查询SYS和SYSTEM用户在user$中的password和spare4字段:

select name, password, spare4 from user$ where name in ('SYS','SYSTEM')
SQL> /

NAME		PASSWORD	SPARE4
--------------- --------------- --------------------------------------------------------------------------------
SYS				S:88EB5B08A9EC6EBAE68148FB711CF9416C26077B3512B6A5BF8A1F44C610;T:4EDCFBAD9376CD1
				ACEBA5567ECA2877B386ED292DD993B57CEEC07261A0137141A5C43941265FC7FD7A540D9D3EED87
				1B6EB1733EEBC2AED5A63CF02F69AFEBC89F026B2D4430CE35D6CCCD5C5DA7123

SYSTEM				S:9CFB5871B12A3CF7E01D864BFE348D521B15B78DEF75B8C95C0EC661792B;T:52418514B24923B
				6C1CC3A609B293A413C5B71C5B2A270A97A71F5E2A2857CFCAE98ECE47B17A1149BAAFF268654082
				469B1078901B0B55CFD377987E2C5AFC598045FFCE551D42CBDE2D38418BDFBE6

修改密码后,再次查询:

SQL> select name, password, spare4 from user$ where name in ('SYS','SYSTEM') ;

NAME		PASSWORD	SPARE4
--------------- --------------- --------------------------------------------------------------------------------
SYS				S:88EB5B08A9EC6EBAE68148FB711CF9416C26077B3512B6A5BF8A1F44C610;T:4EDCFBAD9376CD1
				ACEBA5567ECA2877B386ED292DD993B57CEEC07261A0137141A5C43941265FC7FD7A540D9D3EED87
				1B6EB1733EEBC2AED5A63CF02F69AFEBC89F026B2D4430CE35D6CCCD5C5DA7123

SYSTEM		2D594E86F93B17A S:CAA7AFAE43C3D06D50F6272A837ACDF4C3A2D092821AD7076534CCEEE6F7;T:B8FCFE4B975D3D9
		1		86C1CD27A21FB6F78397BD97889B017FE2F6B949981E85E5F1208C42A143367C3EA70AF7B39B6193
				C38D171CEEF893EAF6FB87A5C095F864B6517CEA65522ACCDE592D9A645FEDC3A

发现SYS用户在基表user$中也是没有变化,但是SYSTEM用户就比较正常了,而且会发现,SYSTEM用户在PASSWORD字段也有对应值了,这是因为PASSWORD_VERSIONS=10G版本时,是存在这个字段的。

3.总结经验

最后总结一下:

  • 1.低版本客户端连接高版本数据库,需要在服务端配置sqlnet.ora文件;
  • 2.高版本数据库的密码需要重新设置(可以和之前密码相同),确保PASSWORD_VERSIONS有低版本;
  • 3.不同PASSWORD_VERSIONS,在基表user$中存储密码的列也不一样;
  • 4.测试做实验强烈建议不要使用SYS这种特殊用户,因为现象很可能不一致;建议使用SYSTEM或者最好自己新建测试用户为佳。