ITPub博客

首页 > 数据库 > Oracle > 11g sec_case_sensitive_logon参数探究

11g sec_case_sensitive_logon参数探究

原创 Oracle 作者:oliseh 时间:2014-11-06 15:20:57 0 删除 编辑

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",表明对应用户的口令是兼容11g11g以前版本的,能够根据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;

 

###passwordspare4列均有值

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

 

 

###最后还原chh1password设置

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1641735