如何禁用Oracle 11g口令大小写？

我们知道，从Oracle 11g开始，默认情况下，数据库用户的口令严格区分大小写，这有别于以前版本的口令不区分大小写。

11g:

SQL> show user;
USER is "SYS"
SQL> select * from v$version;

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

SQL> conn HR/hr;
Connected.
SQL> conn HR/HR;
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>

10g:

SQL> show user;
USER is "SYS"
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> conn HUMAN/hr;
Connected.
SQL> conn HUMAN/HR;
Connected.
SQL>

而且，在DBA_USERS数据字典表的PASSWORD列中已经不再存储加密的口令，在11g以前版本的数据库中可以直接从DBA_USERS数据字典表中获取用户的加密口令，那么在11g数据库里如何查看用户加密后的口令呢？答案是需要查看查看USER$字典表，当然在10g版本的数据库中，也可以从USER$字典表中查看用户的加密口令：

11g:

SQL> show user;
USER is "SYS"
SQL> select * from v$version;

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

SQL> select username,password from dba_users where username='HR';

USERNAME                       PASSWORD
------------------------------ ------------------------------
HR

SQL> select name,password from user$ where name='HR';

NAME                           PASSWORD
------------------------------ ------------------------------
HR                             4C6D73C3E8B0F0DA

SQL>

10g:

SQL> show user;
USER is "SYS"
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> select username,password from dba_users where username='HUMAN';

USERNAME                       PASSWORD
------------------------------ ------------------------------
HUMAN                          CBFA7677C00F9AE1

SQL> select name,password from user$ where name='HUMAN';

NAME                           PASSWORD
------------------------------ ------------------------------
HUMAN                          CBFA7677C00F9AE1

SQL>

Oracle为什么会这么做呢？应该是为了保护数据库的安全性，才这么做的吧。在11g中，需要拥有SELECT ANY DICTIONARY的角色才可用查看USER$字典表。当然，我们不建议将SYS.USER$表的访问权限给普通用户，也不建议将SELECT ANY DICTIONARY的角色给普通用户。

那么，在11g数据库中如何禁用口令大小写呢？

SQL> show parameter sensitive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL> conn HR/hr;
Connected.
SQL> conn HR/HR;
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba;
Connected.
SQL> alter system set sec_case_sensitive_logon=false;

System altered.

SQL> show parameter sensitive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE
SQL> conn HR/hr;
Connected.
SQL> conn HR/HR;
Connected.
SQL>

最后，从下，我们可以看到虽然用户的口令是大小写区分的，但是存放用户口令的密文却是一样的。

SQL> select name,password from user$ where name='HR';

NAME                           PASSWORD
------------------------------ ------------------------------
HR                             4C6D73C3E8B0F0DA

SQL> alter user hr identified by Hr;

User altered.

SQL> select name,password from user$ where name='HR';

NAME                           PASSWORD
------------------------------ ------------------------------
HR                             4C6D73C3E8B0F0DA

SQL> alter user hr identified by hR;

User altered.

SQL> select name,password from user$ where name='HR';

NAME                           PASSWORD
------------------------------ ------------------------------
HR                             4C6D73C3E8B0F0DA

SQL>

从上，我们可以看到，在11g中，不管用户的口令是大写还是小写，最终存放在数据库中的密文口令都是相同的。应该是，Oracle在对口令加密之前，统一转换为大写或者是小写后，然后开始对口令加密，最后形成加密口令。那么究竟是大写还是小写呢，或者是什么其他手段就不得而知了，还有就是Oracle采用的是什么加密算法，我想这些oracle是绝对不会对外公布的^_^