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
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
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
Copyright (c) 1982, 2011, Oracle. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle. All rights reserved.
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
Copyright (c) 1982, 2011, Oracle. All rights reserved.
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 | 不区分大小写 |
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/53956/viewspace-1322944/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/53956/viewspace-1322944/
714
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
