oracle11g密码区分大小写问题

 

自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

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

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>';