Buddy Yuan的个人技术博客

在Oracle 11g 数据库里面密码有了大小写区分,大小写区分主要依赖于 “PASSWORD_VERSIONS” and “sec_case_sensitive_logon”.在11g dba_users视图里面有一个新的列叫“PASSWORD_VERSIONS”,它有三种可能的值”10G”,”10G 11G”,”11G”.同时大小写的时候,这几种可能的值还要受参数sec_case_sensitive_logon的影响.PASSWORD_VERSIONS其实是通过下列计算得出来的从user$视图中:decode(length(u.password),16,’10G ‘,NULL)||NVL2(u.spare4, ’11G ‘ ,NULL)  ,当PASSWORD_VERSIONS是”10G 11G”的时候,user$中”PASSWORD”和”SPARE4″都有值.sec_case_sensitive_logon参数的影响.

SQL> select USERNAME,PASSWORD_VERSIONS from dba_users where username='JESS';

USERNAME PASSWORD
------------------------------ --------
JESS 10G 11G

SQL> select NAME,PASSWORD,SPARE4 from user$ where name='JESS';

NAME PASSWORD SPARE4
------------------------------ ------------------------------ ----------------------------------------------------------------------
JESS AE92C7E0840F21B7 S:1B85AB359229C62AFD033254DA68DE47E87B372E7EDDE6D3025ABB8E7209

SQL> show parameter sec_case_sensitive_logon

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE

SQL> connect jess/jess
Connected.
SQL> connect jess/JESS
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

SQL> alter system set sec_case_sensitive_logon = false ;
System altered.

SQL> connect jess/jess
Connected.

SQL> connect jess/JESS
Connected.