oracle 19c sec_case_sensitive_logon参数问题

jiawang

11g前，密码不区分大小写，11g开始引入了sec_case_sensitive_logon参数控制密码敏感，默认true，大小写敏感。


安装完19C后，将sec_case_sensitive_logon调成false，设置密码大小写不敏感后，发现即使使用正确的密码也无法登录了。


原来19C中早已废弃了sec_case_sensitive_logon参数，重启数据库也可以看到提示：


ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance


废弃参数可以理解，但是使用正确的密码无法连接数据库就有些匪夷所思了。


所以在19C中不建议在设置sec_case_sensitive_logon参数。


MOS上相关的文章有很多，例如：


DBUA Removes SEC_CASE_SENSITIVE_LOGON Init Parameter After Upgrading to 12c (Doc ID 2107717.1)


SEC_CASE_SENSITIVE_LOGON init parameter has been deprecated in 12c, hence ideally you should comment out this parameter before upgrade to 12c. But if you still set SEC_CASE_SENSITIVE_LOGON system parameter, the Database Upgrade Assistant (DBUA) removes the SEC_CASE_SENSITIVE_LOGON system parameter during the upgrade process if it exists in the parameter file (reference Bug 16238456 ).


18c: All user connections fail with ORA-01017 except SYS when SEC_CASE_SENSITIVE_LOGON=FALSE (Doc ID 2502204.1)


What Is the SEC_CASE_SENSITIVE_LOGON Parameter and How Is It Used? (Doc ID 2378179.1)




The SEC_CASE_SENSITIVE_LOGON parameter enables or disables password case sensitivity in the database. It is defaulted to a TRUE value.


True - Database logon passwords are case sensitive


False - Database logon passwords are not case sensitive


For additional information please refer to the following Database Security Guide: https://docs.oracle.com/database ... ation.htm#DBSEG3225


测试过程如下：


查看数据库版本




SQL> select banner from v$version;


BANNER


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


Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production


启动所有PDB


SQL> alter pluggable database all open;


SQL> show pdbs


    CON_ID CON_NAME  OPEN MODE  RESTRICTED


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


2 PDB$SEED  READ ONLY  NO


3 CJCPDB  READ WRITE NO


查看当前参数值




SQL> show parameter sec_case_sensitive_logon


NAME     TYPE VALUE


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


sec_case_sensitive_logon     boolean TRUE


创建新用户


SQL> create user c##chen identified by a;


User created.


SQL> grant connect to c##chen;


Grant succeeded.


密码区分大小写




SQL> conn c##chen/a


Connected.


SQL> conn c##chen/A


ERROR:


ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.


更改参数




SQL> show parameter sec_case_sensitive_logon


NAME     TYPE VALUE


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


sec_case_sensitive_logon     boolean TRUE


SQL> conn  as sysdba


Connected.


SQL> alter system set sec_case_sensitive_logon=false;


System altered.


新用户无法登录(即使使用正确的密码)




SQL> conn c##chen/a


ERROR:


ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.


SQL> conn c##chen/A


ERROR:


ORA-01017: invalid username/password; logon denied


改回参数




SQL> conn  as sysdba


Connected.


SQL> alter system set sec_case_sensitive_logon=true;


System altered.


SQL> conn c##chen/A


ERROR:


ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.


可以正常登录


SQL> conn c##chen/a


Connected.


SQL> show user


USER is "C##CHEN"


也可以通过设置sqlnet.ora，实现大小写不敏感


该参数用来限制可以连接到数据库服务器上的最小客户端版本。


比如设置值为10，即10g，11g等以上客户端版本可以连接到数据库服务器上。




[oracle@cjcos01 admin]$ pwd


/u01/app/oracle/product/19.0.0/dbhome_1/network/admin


[oracle@cjcos01 admin]$ cat sqlnet.ora


SQLNET.ALLOWED_LOGON_VERSION_SERVER=8


SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8


修改参数




SQL> alter system set sec_case_sensitive_logon=false;


System altered.


创建用户




SQL> create user c##ccc identified by a;


User created.


SQL> grant connect to c##ccc;


Grant succeeded.


密码大小写不敏感




SQL> conn c##ccc/a


Connected.


SQL> conn c##ccc/A


Connected.