oracle11g密码区分大小写问题

最新推荐文章于 2021-04-15 08:16:53 发布

原创最新推荐文章于 2021-04-15 08:16:53 发布 · 234 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

ORACLE 同时被 2 个专栏收录

22 篇文章

订阅专栏

UNIX

10 篇文章

订阅专栏

本文详细阐述了从Oracle 10g到11g版本升级过程中，通过公共数据库链接连接时遇到的用户名密码大小写敏感问题，并提供了解决方案。在11g版本中，数据库链接的密码默认情况下区分大小写，导致直接从10g版本连接到11g版本时出现错误。为解决此问题，用户在创建数据库链接时需要确保密码全部使用大写字母，以匹配数据库内部对密码的存储方式。

自11g开始,oracle缺省区分帐号的密码大小写(case sensitivity), 而11g以下版本则不区分, 在数据字典中以大写形式存在

下面的测试是在10.2.0.3 建连到11.1.0.7.2的db link
kytetang(汤建国) 15:42:20
create database link gbisd connect to etl identified by Loxxxf using 'gbisd';

SQL> select * from etl.t_part_space@gbisd;
select * from etl.t_part_space@gbisd
*
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from GBISD
tigerqiu(邱翔虎) 15:47:41
SQL> create public database link gbisd connect to etl identified by "Loxxxf " using 'gbisd';

Database link created.

Elapsed: 00:00:00.00
SQL> select 1 from dual@gbisd;

1
----------
1

Elapsed: 00:00:00.10

而通过sqlplus 连接正常
[eva4k1:~]$sqlplus etl/Loxxxf @gbisd

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Apr 27 16:10:23 2010

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Real Application Testing options

SQL>

workagroup:

在建db link时将username,password全部用双引号包括起来

参数资料
Bug : ORA-01017 ORA-02063 WHILE CONNECTING FROM 10G TO 11G VIA PUBLIC DBLINK

Cause
The following Bug 6738104 was logged for this issue which was closed as not a bug saying the cause being introduction of password case sensitivity feature in 11g

When one creates a database link connection, a user name and password for the connection needs to be defined. When the database link is created, the password is case sensitive. Before a user can connect from a pre-release 11g database to a 11g release database and as the password case sensitivity is enabled by default, you must re-create the password for this database link using all uppercase letters.

The reason you need to re-create the password using all uppercase letters is so that it will match how Oracle Database stores database link passwords. Oracle Database always stores this type of password in uppercase letters, even if the password had originally been created using lower or mixed case letters. If case sensitivity is disabled, the user can enter the password using the case the password was created in.

Solution
++ As the password case sensitivity is enabled by default, create the database link to the 11g database as shown below :

SQL> create public database link <link_name> connect to "<user_name>" identified by "<password>" using '<tns_alias>';