您好,欢迎光临! 请 |

如何禁用Oracle 11g口令大小写?

作者: | 分类:Data Dictionary, Oracle | Tag:, | 评论:0 
字号:T|T

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

顶一下
(0)
100%
踩一下
(0)
100%

发表评论

插入图片

NOTICE1:请申请gravatar头像,没有头像的评论可能不会被回复|头像相关帮助!