Oracle 19c的参数sec_case_sensitive_logon与ORA-01017错误问题分析

oracle的参数sec_case_sensitivejavascript_logon是Oracle 11g开始被引入。这个参数主要是为了控制密码的大小写敏感问题。

sec_case_sensitive_logon=true表示密码区分大小写。

sec_case_sensitive_logon=false表示密码不区分大小写。

从Oracle 12c开始，参数sec_case_sensitive_logon被弃用了。但是为了向下兼容，即使在Oracle 19c中,这个参数依然保留了。这个参数在Oracle 12c(确切的说是12.2以及后续版本)和19c中不能设置为false，因为它和SQLNET.ALLOWED_LOGON_VERSION_SERVER=12或者SQLNET.ALLOWED_LOGON_VERSION_SERVER=12a不兼容。这是因为用于此模式的更安全的密码版本仅支持区分大小写的密码检查。简单点来说，就是这种环境下，这种设置会冲突。官方文档[1]的阐述如下所示：

Notethefollowingimplicationsofsettingthevalueto12or12a:
•AvalueofFALSEfortheSEC_CASE_SENSITIVE_LOGONOracleinstanceinitializationparametermustnotbeusedbecausepasswordcaseinsensitivityrequirestheuseofthe10Gpasswordversion.IftheSEC_CASE_SENSITIVE_LOGONOracleinstanceinitializationparameterissettoFALSE,thenuseraccountsandsecurerolesbecomeunusablebecauseExclusiveModeexcludestheuseofthe10Gpasswordversion.TheSEC_CASE_SENSITIVE_LOGONOracleinstanceinitializationparameterenablesordisablespasswordcasesensitivity.However,sinceExclusivemodeisenabledbydefaultinthisrelease,disablingthepasswordcasesensitivityisnotsupported.
Note:
•TheuseoftheOracleinstanceinitializationparameterSEC_CASE_SENSITIVE_LOGONisdeprecatedinfavorofsettingtheSQLNET.ALLOWED_LOGON_VERSION_SERVERparameterto12toensurethatpasswordsaretreatedinacase-sensitivefashion.
•DisablingpasswordcasesensitivityisnotsupportedinExclusivemode(whenSQLNET.ALLOWED_LOGON_VERSION_SERVERissetto12or12a.)
•ReleasesofOCIclientsearlierthanOracleDatabase10gcannotauthenticatetotheOracledatabaseusingpassword-basedauthentication.
•IftheclientusesOracleDatabase10g,thentheclientwillreceiveanORA-03134:Connectionstothisserverversionarenolongersupportederrormessage.Toallowtheconnection,settheSQLNET.ALLOWED_LOGON_VERSION_SERVERvalueto8.EnsuretheDBA_USERS.PASSWORD_VERSIONSvaluefortheaccountcontainsthevalue10G.Itmaybenecessarytoresetthepasswordforthataccount.

下面我们来构造一个例子，看看这个参数sec_case_sensitive_logon的影响

SQL>selectbanner_fullfromv$version;

BANNER_FULL
----------------------------------------------------------------------------------------------------
OracleDatabase19cEnterpriseEditionRelease19.0.0.0.0-Production
Version19.3.0.0.0


1rowselected.

SQL>showparametersec_case_sensitive_logon;

NAMETYPEVALUE
-----------------------------编程客栈--------------------------------------
sec_case_sensitive_logonbooleanTRUE
SQL>alterusersystemidentifiedby"system#1245";
编程客栈
Useraltered.
SQL>SETLINESIZE1080;
SQL>SETPAGESIZE36;
SQL>COLUSERNAMEFORA24;
SQL>COLACCOUNT_STATUSFORA16;
SQL>COLDEFAULT_TABLESPACEFORA16;
SQL>COLTEMPORARY_TABLESPACEFORA10;
SQL>COLPROFILEFORA10;
SQL>COLLOCK_DATEFORA20;
SQL>COLEXPIRY_DATEFORA20;
SQL>COLPASSWORD_VERSIONSFORA12;
SQL>SELECTUSERNAME
2,ACCOUNT_STATUS
3,DEFAULT_TABLESPACE
4,TEMPORARY_TABLESPACE
5,PROFILE
6,TO_CHAR(LOCK_DATE,'YYYY-MM-DDHH24:MI:SS')ASLOCK_DATE
7,TO_CHAR(EXPIRY_DATE,'YYYY-MM-DDHH24:MI:SS')ASEXPIRY_DATE
8,PASSWORD_VERSIONS
9FROMDBA_USERS
10WHEREUSERNAME=UPPER('&USERNAME')
11ORDERBYEXPIRY_DATE;
Entervalueforusername:system
old10:WHEREUSERNAME=UPPER('&USERNAME')
new10:WHEREUSERNAME=UPPER('system')

USERNAMEACCOUNT_STATUSDEFAULT_TABLESPATEMPORARY_PROFILELOCK_DATEEXPIRY_DATEPASSWORD_VER
-------------------------------------------------------------------------------------------------------------
SYSTEMOPENSYSTEMTEMPDEFAULT2023-10-2217:25:0911G12C

SQL>altersystemsetsec_case_sensitive_logonlRyub=falsescope=both;

Systemaltered.

SQL>

然后我们在另外一个窗口使用system账号登陆数据库

$sqlplussystem/system#1245

SQL*Plus:Release19.0.0.0.0-ProductiononTueApr2517:16:282023
Version19.3.0.0.0

Copyright(c)1982,2019,Oracle.Allrightsreserved.

ERROR:
ORA-01017:invalidusername/password;logondenied

如果我们将参数设置sec_case_sensitive_logon为true(这个参数调整后可以立即生效，不用重启)，

SQL>showuser;
USERis"SYS"
SQL>altersystemsetsec_case_sensitive_logon=truescope=both;

Systemaltered.

SQL>

然后验证如下所示所示，一切正常，所以如果你遇到ORA-01017这个错误，而且数据库版本为12c/19c,如果你确认你密码是正确的，那么检查一下这个参数。

$sqlplussystem/system#1245

SQL*Plus:Release19.0.0.0.0-ProductiononTueApr2517:20:282023
Version19.3.0.0.0

Copyright(c)1982,2019,Oracle.Allrightsreserved.

LastSuccessfullogintime:TueApr25202309:54:37+08:00

Connectedto:
OracleDatabase19cEnterpriseEditionRelease19.0.0.0.0-Production
Version19.3.0.0.0

SQL>

下面我们再来测试一下，在参数sec_case_sensitive_logon为false的情况，我们控制密码版本来解决ORA-01017这个错误

SQL>showuser;
USERis"SYS"
SQL>showparametersec_case_sensitive_logon;

NAMETYPEVALUE
-----------------------------------------------------------------------------
sec_case_sensitive_logonbooleanFALSE
SQL>

修改sqlnet.ora这个参数文件，设置下面参数：

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
SQLNET.ALLOWED_LOGON_VERSION_SERVER=10

修改sqlnet.ora参数文件后，必须重新登陆SQLPlus后（如果使用之前的SQLPlus连接也不会生效），执行下面脚本

SQL>alterusersystemidentifiedby"system#1245";

Useraltered.

SQL>SETLINESIZE1080;
SQL>SETPAGESIZE36;
SQL>COLUSERNAMEFORA16;
SQL>COLACCOUNT_STATUSFORA16;
SQL>COLDEFAULT_TABLESPACEFORA16;
SQL>COLTEMPORARY_TABLESPACEFORA10;
SQL>COLPROFILEFORA10;
SQL>COLLOCK_DATEFORA20;
SQL>COLEXPIRY_DATEFORA20;
SQL>COLPASSWORD_VERSIONSFORA12;
SQL>SELECTUSERNAME
2,ACCOUNT_STATUS
3,DEFAULT_TABLESPACE
4,TEMPORARY_TABLESPACE
5,PROFILE
6,TO_CHAR(LOCK_DATE,'YYYY-MM-DDHH24:MI:SS')ASLOCK_DATE
7,TO_CHAR(EXPIRY_DATE,'YYYY-MM-DDHH24:MI:SS')ASEXPIRY_DATE
8,PASSWORD_VERSIONS
9FROMDBA_USERS
10WHEREUSERNAME=UPPER('&USERNAME')
11ORDERBYEXPIRY_DATE;
Entervalueforusername:system
old10:WHEREUSERNAME=UPPER('&USERNAME')
new10:WHEREUSERNAME=UPPER('system')

USERNAMEACCOUNT_STATUSDEFAULT_TABLESPATEMPORARY_PROFILELOCK_DATEEXPIRY_DATEPASSWORD_VER
------------------------------------------------------------------------------------------------------------------------
SYSTEMOPENSYSTEMTEMPDEFAULT2023-10-2309:21:2710G11G12C

1rowselected.

SQL>

此时验证system账号登陆，则不会报ORA-01017这个错误了。

$sqlplussystem/system#1245

SQL*Plus:Release19.0.0.0.0-ProductiononWedApr2609:22:182023
Version19.3.0.0.0

编程Copyright(c)1982,2019,Oracle.Allrightsreserved.

LastSuccessfullogintime:TueApr25202317:20:29+08:00

Connectedto:
OracleDatabase19cEnterpriseEditionRelease19.0.0.0.0-Production
Version19.3.0.0.0

SQL>

注意：最好使用其他账号验证测试，这里仅仅是为了偷懒，使用测试环境的system账号测试验证。更多相关信息也可以参考The new Exclusive Mode default for password-based authentication in Oracle 12.2 conflicts with case-insensitive password configurations. All user login fails with ORA-1017 after upgrade to 12.2 (Doc ID 2075401.1)[2]

参考资料

[1]

官方文档1: https://docs.oracle.com/en/database/oracle/or开发者_大数据acle-database/19/netrf/parameters-for-the-sqlnet.ora.html#GUID-1FA9D26C-4D97-4D1C-AB47-1EC234D924AA

[2]

Doc ID 2075401.1: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=249715360691380&id=2075401.1&_afrWindowMode=0&_adf.ctrl-state=1agoeyy4f0_80

到此这篇关于Oracle 19c的参数sec_case_sensitive_logon与ORA-01017错误的文章就介绍到这了,更多相关Oracle 19c sec_case_sensitive_logon与ORA-01017内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！