oracle查询大小写敏感参数,【学习笔记】Oracle 11G密码大小写敏感的设置方法 sec_case_sensitive_logon参数...

天萃荷净

分享一篇Oracle数据库密码大小写敏感设置案例，从11.1开始密码大小写敏感了，同时可以设置sec_case_sensitive_logon参数可以忽略大小写敏感(USER$.SPARE4)

从11.1开始密码大小写敏感了，同时可以设置sec_case_sensitive_logon参数可以忽略大小写敏感。

通过朋友咨询的不设置sec_case_sensitive_logon参数让密码大小写不敏感的实验这篇文章疑惑,同时也感谢朋友让我学习到了新知识,我通过测试证明了如下结论：

1.password_versions的优先级大于sec_case_sensitive_logon

2.通过修改USER$.SPARE4为空实现了屏蔽ORACLE 11g密码大小写敏感

10g创建用户导出数据

SQL> create user ora10g identified by oracleplus;

User created.

SQL> grant connect to ora10g;

Grant succeeded.

C:\Documents and Settings\Administrator>expdp \"/ as sysdba \" DIRECTORY=exp_dp

DUMPFILE=chf.dmp schemas=ora10g

Export: Release 10.2.0.1.0 - Production on 星期三, 15 5月, 2013 22:59:45

Copyright (c) 2003, 2005, Oracle. All rights reserved.

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

启动 "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" DIRECTORY=exp_dp DUMPF

ILE=chf.dmp schemas=ora10g

正在使用 BLOCKS 方法进行估计...

处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA

使用 BLOCKS 方法的总估计: 0 KB

处理对象类型 SCHEMA_EXPORT/USER

处理对象类型 SCHEMA_EXPORT/ROLE_GRANT

处理对象类型 SCHEMA_EXPORT/DEFAULT_ROLE

处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

已成功加载/卸载了主表 "SYS"."SYS_EXPORT_SCHEMA_01"

******************************************************************************

SYS.SYS_EXPORT_SCHEMA_01 的转储文件集为:

C:\CHF.DMP

作业 "SYS"."SYS_EXPORT_SCHEMA_01" 已于 23:00:19 成功完成

11g创建用户

SQL> select * from v$version;

BANNER

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

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

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL> show parameter logon

NAME TYPE VALUE

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

sec_case_sensitive_logon boolean TRUE

SQL> create user ora11g identified by oracleplus;

User created.

SQL> grant connect to ora11g;

Grant succeeded.

SQL> conn ora11g/oracleplus

Connected.

SQL> conn ora11g/oracleplus

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

这里证明,在sec_case_sensitive_logon=true的情况下,数据库密码是大小写敏感

导入10g创建用户dmp文件

[oracle@localhost ~]$ impdp '"/ as sysdba"' directory=exp_dp dumpfile=CHF.DMP

Import: Release 11.2.0.3.0 - Production on Wed May 15 23:07:20 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. 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

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=exp_dp dumpfile=CHF.DMP

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 23:07:21

查询ora10g和ora11g用户区别

SQL> conn ora10g/oracleplus

Connected.

SQL> conn ora10g/oracleplus

Connected.

SQL> conn / as sysdba

Connected.

SQL> select t.username,t.account_status,t.password_versions from dba_users t where t.username in ('ORA11G','ORA10G');

USERNAME ACCOUNT_STATUS PASSWORD

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

ORA11G OPEN 10G 11G

ORA10G OPEN 10G

SQL> select name,password,spare4 from SYS.USER$ t where name in ('ORA11G','ORA10G');

NAME PASSWORD SPARE4

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

ORA10G F3CF2F0CB35CB6CA

ORA11G 559D84354181EB8E S:BFE2625310D9382E9AEA6EE0AA2988E82C17B3EA23E3DAC23800490C2621

这里可以发现我们从低版本(10g)导入到11g中的用户登录是不区分大小写,而11g本身创建的用户是区分大小写,而他们的区别仅仅是在dba_users.password_versions中有不一样,跟踪到基表发现就是USER$.SPARE4列不一样(10g的该列为空).

猜想:password_versions的优先级大于sec_case_sensitive_logon这个参数

验证猜想

如果是password_versions的优先级大于sec_case_sensitive_logon那么，如果我修改了USER$.SPARE4,使得dba_users.password_versions变成和10g导入的库一样,是否就可以实现不区分密码大小写的问题,如果不缺乏证明：password_versions的优先级大于sec_case_sensitive_logon这个参数,反之失败.

SQL> update SYS.USER$ t set SPARE4='' where name='ORA11G';

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> alter system flush shared_pool;

System altered.

SQL> conn ora11g/oracleplus

Connected.

SQL> conn ora11g/oracleplus

Connected.

由此得出两个结论:

1.password_versions的优先级大于sec_case_sensitive_logon

2.通过修改USER$.SPARE4为空实现了屏蔽ORACLE 11g密码大小写敏感

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle 11G密码大小写敏感的设置方法 sec_case_sensitive_logon参数