Oracle的參數(shù)sec_case_sensitive_logon是Oracle 11g開(kāi)始被引入。這個(gè)參數(shù)主要是為了控制密碼的大小寫(xiě)敏感問(wèn)題。
sec_case_sensitive_logon=true表示密碼區(qū)分大小寫(xiě)。
sec_case_sensitive_logon=false表示密碼不區(qū)分大小寫(xiě)。
從Oracle 12c開(kāi)始，參數(shù)sec_case_sensitive_logon被棄用了。但是為了向下兼容，即使在Oracle 19c中,這個(gè)參數(shù)依然保留了。這個(gè)參數(shù)在Oracle 12c(確切的說(shuō)是12.2以及后續(xù)版本)和19c中不能設(shè)置為false，因?yàn)樗蚐QLNET.ALLOWED_LOGON_VERSION_SERVER=12或者SQLNET.ALLOWED_LOGON_VERSION_SERVER=12a不兼容。這是因?yàn)橛糜诖四Ｊ降母踩?strong>密碼版本僅支持區(qū)分大小寫(xiě)的密碼檢查。簡(jiǎn)單點(diǎn)來(lái)說(shuō)，就是這種環(huán)境下，這種設(shè)置會(huì)沖突。官方文檔[1]的闡述如下所示：

Note?the?following?implications?of?setting?the?value?to?12?or?12a:
??A?value?of?FALSE?for?the?SEC_CASE_SENSITIVE_LOGON?Oracle?instance?initialization?parameter?must?not?be?used?because?password?case?insensitivity?requires?the?use?of?the?10G?password?version.?If?the?SEC_CASE_SENSITIVE_LOGON?Oracle?instance?initialization?parameter?is?set?to?FALSE,?then?user?accounts?and?secure?roles?become?unusable?because?Exclusive?Mode?excludes?the?use?of?the?10G?password?version.?The?SEC_CASE_SENSITIVE_LOGON?Oracle?instance?initialization?parameter?enables?or?disables?password?case?sensitivity.?However,?since?Exclusive?mode?is?enabled?by?default?in?this?release,?disabling?the?password?case?sensitivity?is?not?supported.
Note:
??The?use?of?the?Oracle?instance?initialization?parameter?SEC_CASE_SENSITIVE_LOGON?is?deprecated?in?favor?of?setting?the?SQLNET.ALLOWED_LOGON_VERSION_SERVER?parameter?to?12?to?ensure?that?passwords?are?treated?in?a?case-sensitive?fashion.
??Disabling?password?case?sensitivity?is?not?supported?in?Exclusive?mode?(when?SQLNET.ALLOWED_LOGON_VERSION_SERVER?is?set?to?12?or?12a.)
??Releases?of?OCI?clients?earlier?than?Oracle?Database?10g?cannot?authenticate?to?the?Oracle?database?using?password-based?authentication.
??If?the?client?uses?Oracle?Database?10g,?then?the?client?will?receive?an?ORA-03134:?Connections?to?this?server?version?are?no?longer?supported?error?message.?To?allow?the?connection,?set?the?SQLNET.ALLOWED_LOGON_VERSION_SERVER?value?to?8.?Ensure?the?DBA_USERS.PASSWORD_VERSIONS?value?for?the?account?contains?the?value?10G.?It?may?be?necessary?to?reset?the?password?for?that?account.

下面我們來(lái)構(gòu)造一個(gè)例子，看看這個(gè)參數(shù)sec_case_sensitive_logon的影響

SQL>?select?banner_full?from?v$version;

BANNER_FULL
----------------------------------------------------------------------------------------------------
Oracle?Database?19c?Enterprise?Edition?Release?19.0.0.0.0?-?Production
Version?19.3.0.0.0


1?row?selected.

SQL>?show?parameter?sec_case_sensitive_logon;

NAME?????????????????????????????????TYPE????????VALUE
--------------------------?-----------?------------------------------
sec_case_sensitive_logon????????boolean?????TRUE
SQL>?alter?user?system?identified?by?"system#1245";

User?altered.
SQL>?SET?LINESIZE?1080;
SQL>?SET?PAGESIZE?36;
SQL>?COL?USERNAME?FOR?A24;
SQL>?COL?ACCOUNT_STATUS?FOR?A16;?
SQL>?COL?DEFAULT_TABLESPACE?FOR?A16;
SQL>?COL?TEMPORARY_TABLESPACE?FOR?A10;
SQL>?COL?PROFILE?FOR?A10;
SQL>?COL?LOCK_DATE?FOR?A20;
SQL>?COL?EXPIRY_DATE?FOR?A20;
SQL>?COL?PASSWORD_VERSIONS?FOR?A12;
SQL>?SELECT?USERNAME?
??2???????,?ACCOUNT_STATUS
??3???????,?DEFAULT_TABLESPACE
??4???????,?TEMPORARY_TABLESPACE
??5???????,?PROFILE
??6???????,?TO_CHAR(LOCK_DATE,"YYYY-MM-DD?HH24:MI:SS")????AS?LOCK_DATE
??7???????,?TO_CHAR(EXPIRY_DATE,"YYYY-MM-DD?HH24:MI:SS")??AS?EXPIRY_DATE?
??8??????,?PASSWORD_VERSIONS
??9??FROM?DBA_USERS?
?10??WHERE?USERNAME=UPPER("&USERNAME")
?11??ORDER?BY?EXPIRY_DATE;
Enter?value?for?username:?system
old??10:?WHERE?USERNAME=UPPER("&USERNAME")
new??10:?WHERE?USERNAME=UPPER("system")

USERNAME??????ACCOUNT_STATUS???DEFAULT_TABLESPA?TEMPORARY_?PROFILE????LOCK_DATE???????????EXPIRY_DATE??????????PASSWORD_VER
----------?----------------?----------------?----------?----------?---------------?--------------------?------------
SYSTEM?????????????OPEN?????????????SYSTEM???????????TEMP???????DEFAULT????????????????????????2023-10-22?17:25:09????11G?12C

SQL>?alter?system?set?sec_case_sensitive_logon=false?scope=both;

System?altered.

SQL>

然后我們?cè)诹硗庖粋€(gè)窗口使用system賬號(hào)登陸數(shù)據(jù)庫(kù)

$?sqlplus?system/system#1245

SQL*Plus:?Release?19.0.0.0.0?-?Production?on?Tue?Apr?25?17:16:28?2023
Version?19.3.0.0.0

Copyright?(c)?1982,?2019,?Oracle.??All?rights?reserved.

ERROR:
ORA-01017:?invalid?username/password;?logon?denied

如果我們將參數(shù)設(shè)置sec_case_sensitive_logon為true(這個(gè)參數(shù)調(diào)整后可以立即生效，不用重啟)，

SQL>?show?user;
USER?is?"SYS"
SQL>?alter?system?set?sec_case_sensitive_logon=true?scope=both;

System?altered.

SQL>

然后驗(yàn)證如下所示所示，一切正常，所以如果你遇到ORA-01017這個(gè)錯(cuò)誤，而且數(shù)據(jù)庫(kù)版本為12c/19c,如果你確認(rèn)你密碼是正確的，那么檢查一下這個(gè)參數(shù)。

$sqlplus?system/system#1245

SQL*Plus:?Release?19.0.0.0.0?-?Production?on?Tue?Apr?25?17:20:28?2023
Version?19.3.0.0.0

Copyright?(c)?1982,?2019,?Oracle.??All?rights?reserved.

Last?Successful?login?time:?Tue?Apr?25?2023?09:54:37?+08:00

Connected?to:
Oracle?Database?19c?Enterprise?Edition?Release?19.0.0.0.0?-?Production
Version?19.3.0.0.0

SQL>

下面我們?cè)賮?lái)測(cè)試一下，在參數(shù)sec_case_sensitive_logon為false的情況，我們控制密碼版本來(lái)解決ORA-01017這個(gè)錯(cuò)誤

SQL>?show?user;??
USER?is?"SYS"
SQL>?show?parameter?sec_case_sensitive_logon;

NAME?????????????????????????????????TYPE????????VALUE
------------------------------------?-----------?------------------------------
sec_case_sensitive_logon?????????????boolean?????FALSE
SQL>

修改sqlnet.ora這個(gè)參數(shù)文件，設(shè)置下面參數(shù)：

SQLNET.ALLOWED_LOGON_VERSION_CLIENT?=?10
SQLNET.ALLOWED_LOGON_VERSION_SERVER?=?10

修改sqlnet.ora參數(shù)文件后，必須重新登陸SQLPlus后（如果使用之前的SQLPlus連接也不會(huì)生效），執(zhí)行下面腳本

SQL>?alter?user?system?identified?by?"system#1245";

User?altered.

SQL>?SET?LINESIZE?1080;
SQL>?SET?PAGESIZE?36;
SQL>?COL?USERNAME?FOR?A16;
SQL>?COL?ACCOUNT_STATUS?FOR?A16;?
SQL>?COL?DEFAULT_TABLESPACE?FOR?A16;
SQL>?COL?TEMPORARY_TABLESPACE?FOR?A10;
SQL>?COL?PROFILE?FOR?A10;
SQL>?COL?LOCK_DATE?FOR?A20;
SQL>?COL?EXPIRY_DATE?FOR?A20;
SQL>?COL?PASSWORD_VERSIONS?FOR?A12;
SQL>?SELECT?USERNAME?
??2???????,?ACCOUNT_STATUS
??3???????,?DEFAULT_TABLESPACE
??4???????,?TEMPORARY_TABLESPACE
??5???????,?PROFILE
??6???????,?TO_CHAR(LOCK_DATE,"YYYY-MM-DD?HH24:MI:SS")????AS?LOCK_DATE
??7???????,?TO_CHAR(EXPIRY_DATE,"YYYY-MM-DD?HH24:MI:SS")??AS?EXPIRY_DATE?
??8??????,?PASSWORD_VERSIONS
??9??FROM?DBA_USERS?
?10??WHERE?USERNAME=UPPER("&USERNAME")
?11??ORDER?BY?EXPIRY_DATE;
Enter?value?for?username:?system
old??10:?WHERE?USERNAME=UPPER("&USERNAME")
new??10:?WHERE?USERNAME=UPPER("system")

USERNAME?????????ACCOUNT_STATUS???DEFAULT_TABLESPA?TEMPORARY_?PROFILE????LOCK_DATE????????????EXPIRY_DATE??????????PASSWORD_VER
----------------?----------------?----------------?----------?----------?--------------------?--------------------?------------
SYSTEM???????????OPEN?????????????SYSTEM???????????TEMP???????DEFAULT?????????????????????????2023-10-23?09:21:27??10G?11G?12C

1?row?selected.

SQL>

此時(shí)驗(yàn)證system賬號(hào)登陸，則不會(huì)報(bào)ORA-01017這個(gè)錯(cuò)誤了。

$?sqlplus?system/system#1245

SQL*Plus:?Release?19.0.0.0.0?-?Production?on?Wed?Apr?26?09:22:18?2023
Version?19.3.0.0.0

Copyright?(c)?1982,?2019,?Oracle.??All?rights?reserved.

Last?Successful?login?time:?Tue?Apr?25?2023?17:20:29?+08:00

Connected?to:
Oracle?Database?19c?Enterprise?Edition?Release?19.0.0.0.0?-?Production
Version?19.3.0.0.0

SQL>

注意：最好使用其他賬號(hào)驗(yàn)證測(cè)試，這里僅僅是為了偷懶，使用測(cè)試環(huán)境的system賬號(hào)測(cè)試驗(yàn)證。更多相關(guān)信息也可以參考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/oracle-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

到此這篇關(guān)于Oracle 19c的參數(shù)sec_case_sensitive_logon與ORA-01017錯(cuò)誤的文章就介紹到這了,更多相關(guān)Oracle 19c sec_case_sensitive_logon與ORA-01017內(nèi)容請(qǐng)搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！