oracle case sensitive,【11g】使用SEC_CASE_SENSITIVE_LOGON参数控制密码大小写敏感性-CSDN博客

在Oracle的11g之前的版本中密码是不区分大小写的(使用双引号强制除外)。在Oracle的11g版本中对此有所增强。从此密码有了大小写的区分，这个大小写敏感特性是通过SEC_CASE_SENSITIVE_LOGON参数来控制的。简单探索一下。1.创建Secooler用户，注意它的密码同时包含大写字母和小写字母。sys@ora11gR2> create user Secooler identified by Secooler;User created.sys@ora11gR2> grant connect to secooler;Grant succeeded.从这个授权成功上可见，用户名仍然不区分大小写。2.尝试使用全小写的密码登陆sys@ora11gR2> conn secooler/secoolerERROR:ORA-01017: invalid username/password; logon deniedWarning: You are no longer connected to ORACLE.报错，显然不行，此时大小写敏感性检查已经启用。3.使用“正确”的密码“Secooler”进行登陆，成功。sys@ora11gR2> conn secooler/SecoolerConnected.secooler@ora11gR2> show user;USER is "SECOOLER"4.看一下“幕后黑手”SEC_CASE_SENSITIVE_LOGON参数的描述sys@secooler> show parameter sec_case_sensitive_logonNAME TYPE VALUE-------------------------- ---------- -----------sec_case_sensitive_logon boolean TRUEsys@ora11gR2> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description2 from x$ksppi a,x$ksppcv b3 where a.indx = b.indx4 and a.ksppinm = 'sec_case_sensitive_logon'5 /NAME VALUE DESCRIPTION------------------------- ------ -----------------------------------------sec_case_sensitive_logon TRUE case sensitive password enabled for logon该参数默认值是“TRUE”，因此，默认情况下密码大小写是敏感的。5.将SEC_CASE_SENSITIVE_LOGON参数修改为“FALSE”sys@ora11gR2> alter system set sec_case_sensitive_logon = FALSE;System altered.6.此时系统将不对密码做大小写敏感验证sys@ora11gR2> conn secooler/secoolerConnected.7.即使修改SEC_CASE_SENSITIVE_LOGON参数为“FALSE”，在系统中记录的也是区分大小写的形式，只是不进行验证而已。1)创建另外一个用户sec，并授权sys@ora11gR2> create user sec identified by SeC;User created.sys@ora11gR2> grant connect to sec;Grant succeeded.2)在SEC_CASE_SENSITIVE_LOGON参数为“FALSE”时尝试连接，成功，因为此时不进行校验。sys@ora11gR2> conn sec/secConnected.3)恢复SEC_CASE_SENSITIVE_LOGON参数为“TRUE”，再次尝试连接将会失败。sec@ora11gR2> conn / as sysdbaConnected.sys@ora11gR2> alter system set sec_case_sensitive_logon = TRUE;System altered.sys@ora11gR2> conn sec/secERROR:ORA-01017: invalid username/password; logon deniedWarning: You are no longer connected to ORACLE.4)使用“正确”的密码进行尝试，成功。sys@ora11gR2> conn sec/SeCConnected.5)结论11g环境中密码是以区分大小写的形式进行存储的。8.Oracle 11g文档参考(11gR1和11gR2内容相同)http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/initparams218.htm#REFRN10299