我们知道，从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是绝对不会对外公布的^_^