都知道在11g中密码大小写敏感了,同时可以设置sec_case_sensitive_logon参数可以忽略大小写敏感。

方法目前我想到的有2种,一种是可行的,还有一种是下面的实验主要内容。

方法1:就是在10g里新建用户名通过expdp,然后impdp倒入到11g的数据库中;

方法2:通过修改表 SYS.USER$ 来实现;

下面是实验内容:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> 
SQL> show parameter logon

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL> 
SQL> 
创建用户名:
SQL> create user oradbca identified by oracle;

User created.

SQL> grant create session to oradbca;

Grant succeeded.
小写密码登入成功
SQL> conn oradbca/oracle
Connected.
大写密码登入不成功
SQL> conn oradbca/ORACLE
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> 
SQL> 
SQL> conn / as sysdba
Connected.
SQL>  select t.username,t.account_status,t.password_versions from dba_users t where t.username='ORADBCA';

USERNAME                       ACCOUNT_STATUS                   PASSWORD
------------------------------ -------------------------------- --------
ORADBCA                        OPEN                             10G 11G


SQL> select name,password,spare4 from SYS.USER$ t where name='ORADBCA';


NAME       PASSWORD                       SPARE4
---------- ------------------------------ ------------------------------------------------------------------
ORADBCA    CC5873303EECAD49               S:89B029335C4B8CDB5E72C2D3232F5CBFF94579A7083758F667EE9473B2D5

由于password_versions的优先级大于sec_case_sensitive_logon这个参数,

所以我想能不能修改这个password_versions值来让大小写不敏感。

清空spare4值来让password_versions显示为10g,实验步骤为:

SQL> update SYS.USER$ t set SPARE4='' where name='ORADBCA';

1 row updated.

SQL> commit;

Commit complete.

SQL> 
SQL> select t.username,t.account_status,t.password_versions from dba_users t where t.username='ORADBCA';

USERNAME                       ACCOUNT_STATUS                   PASSWORD
------------------------------ -------------------------------- --------
ORADBCA                        OPEN                             10G

SQL> select name,password,spare4 from SYS.USER$ t where name='ORADBCA';

NAME       PASSWORD                       SPARE4
---------- ------------------------------ ------------------------------------------------
ORADBCA    CC5873303EECAD49

已经看到password_versions为10g了,现在我们验证一下是否大小写不敏感。

SQL> 
SQL> conn oradbca/oracle
Connected.
SQL> conn oradbca/ORACLE
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

看到还是不行,通过朋友的咨询,修改内部数据字典需要flush shared_pool

SQL> conn / as sysdba
Connected.
SQL> 
SQL> 
SQL>  alter system flush shared_pool;

System altered.

SQL> conn oradbca/ORACLE
Connected.
	

现在看到是可以了,密码忽略了大小写。