sec_case_sensitive_logon引數一句話概括就是決定使用者的口令是否大小寫敏感。看似簡單，其背後還是深藏著不少學問。當我們在11g裡建立一個使用者時，無論sec_case_sensitive_logon=TRUE or FALSE，建立後會根據使用者輸入的口令在sys.user$.password列存入一個Hash值，同時也會在sys.user$.SPARE4列存入另一個Hash值，這兩個Hash值不相等，因為生成的演算法不一樣。

sec_case_sensitive_logon=FALSE的情況下，用這個使用者登入時會用sys.user$.password列值去校驗登入時的口令，這時的口令是不區分大小寫的。

sec_case_sensitive_logon=TRUE的情況下，使用這個使用者登入時會用sys.user$.SPARE4列值去校驗登入時的口令，這時的口令是區分大小寫的。

在11g版本以前口令是不區分大小寫的，所以只會用到sys.user$.password列去存放口令的hash值，sys.user$.spare4列為空，下面是在10.2.0.4版本下的輸出

oracle@qb550135:/home/oracle>sqlplus '/as sysdba';

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Nov 6 13:29:47 2014

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

* select name,password,spare4 from user$ where rownum=1

NAME PASSWORD SPARE4

------------------------------ ------------------------------ ------------------------------

SYS 707611B32103A8CE

11g 的dba_users檢視裡有一列password_versions，值為"10G 11G"，表明對應使用者的口令是相容11g及11g以前版本的，能夠根據sec_case_sensitive_logon的取值決定口令是否區分大小寫

select username,password_versions from dba_users

USERNAME PASSWORD

------------------------------ --------

AID 10G 11G

AD 10G 11G

我們還是透過實驗來說話，驗證一下上面的說法

###建立一個使用者

show parameter sec_case_sensitive_logon

sec_case_sensitive_logon boolean TRUE

create user chh1 identified by abcd_1234;

grant connect,resource to chh1;

###password、spare4列均有值

col name format a15

col password format a35

col spare4 format a70

set linesize 130

select name,password,spare4 from user$ where name='CHH1';

NAME PASSWORD SPARE4

--------------- ----------------------------------- ----------------------------------------------------------------------

CHH1 85B334ABD23A90CA S:84E67D126034C3722A9C2B3CA63918AF38C830CE00BE96A95311D5681DD7

###sec_case_sensitive_logon=TRUE登入測試

alter system set sec_case_sensitive_logon=TRUE;

因為sec_case_sensitive_logon=TRUE，所以使用sqlplus chh1/abcd_1234能夠登入，使用sqlplus chh1/ABCD_1234不能登入

sqlplus chh1/abcd_1234

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 6 13:57:05 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> quit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

sqlplus chh1/ABCD_1234

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 6 13:57:11 2014

ERROR:

ORA-01017: invalid username/password; logon denied

###sec_case_sensitive_logon=FALSE登入測試，使用sqlplus chh1/abcd_1234能夠登入，使用sqlplus chh1/ABCD_1234也能夠登入

alter system set sec_case_sensitive_logon=FALSE;

sqlplus chh1/abcd_1234

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 6 13:58:33 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> quit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

sqlplus chh1/ABCD_1234

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 6 13:58:39 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

###清空sys.user$.spare4列，僅保留sys.user$.password列，無論sec_case_sensitive_logon取值如何，登入時不會區分口令大小寫

SQL> show parameter sec_case_sensitive_logon

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

sec_case_sensitive_logon boolean FALSE

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

NAME PASSWORD SPARE4

--------------- ----------------------------------- ----------------------------------------------------------------------

CHH1 85B334ABD23A90CA S:84E67D126034C3722A9C2B3CA63918AF38C830CE00BE96A95311D5681DD7

---下面我們將spare4欄位置為空

SQL>alter user chh1 identified by values '85B334ABD23A90CA';

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

NAME PASSWORD SPARE4

--------------- ----------------------------------- ----------------------------------------------------------------------

CHH1 85B334ABD23A90CA

SQL> show parameter sec_case_sensitive_logon

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

sec_case_sensitive_logon boolean FALSE

---sec_case_sensitive_logon=FALSE，登入時不區分口令大小寫

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

sqlplus chh1/ABCD_1234

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 6 14:09:42 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> quit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

sqlplus chh1/abcd_1234

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 6 14:09:47 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

---查詢dba_users，發現chh1使用者的password_versions值變為10G了，表示資料字典裡存放的chh1使用者的口令hash值只適合與不區分大小寫方式的登入

select username,password_versions from dba_users where username='CHH1';

USERNAME PASSWORD_VERSIONS

------------------------------ ------------------------------

CHH1 10G

---即便我們將sec_case_sensitive_logon改為TRUE，登入時口令也不區分大小寫

alter system set sec_case_sensitive_logon=TRUE;

show parameter sec_case_sensitive_logon

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

sec_case_sensitive_logon boolean TRUE

sqlplus chh1/AbCD_1234

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 6 14:16:07 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> quit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

sqlplus chh1/Abcd_1234

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 6 14:16:13 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

###恢復sys.user$.spare4列，清空sys.user$.password列，此時只有sec_case_sensitive_logon設為TRUE時才能正常登陸，登入時會區分口令大小寫

show parameter sec_case_sensitive_logon

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

sec_case_sensitive_logon boolean TRUE

---下面我們將spare4欄位置為空

alter user chh1 identified by values 'S:84E67D126034C3722A9C2B3CA63918AF38C830CE00BE96A95311D5681DD7';

select name,password,spare4 from user$ where name='CHH1'

NAME PASSWORD SPARE4

------------------------------ ------------------------------ ----------------------------------------------------------------------

CHH1 S:84E67D126034C3722A9C2B3CA63918AF38C830CE00BE96A95311D5681DD7

---sec_case_sensitive_logon=TRUE時，登入口令區分大小寫

sqlplus chh1/Abcd_1234

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 6 14:25:17 2014

ERROR:

ORA-01017: invalid username/password; logon denied

Enter user-name:

sqlplus chh1/abcd_1234

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 6 14:25:22 2014

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

---將sec_case_sensitive_logon改為FALSE，即便口令輸入正確也無法登陸

alter system set sec_case_sensitive_logon=FALSE;

SQL> show parameter sec_case_sensitive_logon

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

sec_case_sensitive_logon boolean FALSE

sqlplus chh1/abcd_1234

SQL*Plus: Release 11.2.0.3.0 Production on Thu Nov 6 14:28:19 2014

ERROR:

ORA-01017: invalid username/password; logon denied

Enter user-name:

---檢視此時的dba_users.password_versions，變為了11G

select username,password_versions from dba_users where username='CHH1'

USERNAME PASSWORD_VERSIONS

------------------------------ ------------------------------

CHH1 11G

###最後還原chh1的password設定

alter user chh1 identified by values '85B334ABD23A90CA;S:84E67D126034C3722A9C2B3CA63918AF38C830CE00BE96A95311D5681DD7';

select name,password,spare4 from user$ where name='CHH1';

NAME PASSWORD SPARE4

------------------------------ -------------------- --------------------------------------------------

CHH1 85B334ABD23A90CA S:84E67D126034C3722A9C2B3CA63918AF38C830CE00BE96A95311D5681DD7

select username,password_versions from dba_users where username='CHH1'

USERNAME PASSWORD

------------------------------ --------

CHH1 10G 11G

實驗結果歸納如下表：

sec_case_sensitive_logon	Sys.user$.Password	Sys.user$.spare4	dba_users.password_versions	登入是否區分大小寫
TRUE	非空	空	10g	不區分大小寫
	空	非空	11g	區分大小寫
	非空	非空	10g 11g	區分大小寫
FALSE	非空	空	10g	不區分大小寫
	空	非空	11g	無法登陸
	非空	非空	10g 11g	不區分大小寫

11g sec_case_sensitive_logon引數探究

相關文章