Oracle?19c的參數sec_case_sensitive_logon與ORA-01017錯誤問題分析

Oracle的參數sec_case_sensitive_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]的闡述如下所示：

(資料圖)

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.

下面我們來構造一個例子，看看這個參數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>

然后我們在另外一個窗口使用system賬號登陸數據庫

$?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

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

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

System?altered.

SQL>

然后驗證如下所示所示，一切正常，所以如果你遇到ORA-01017這個錯誤，而且數據庫版本為12c/19c,如果你確認你密碼是正確的，那么檢查一下這個參數。

$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>

下面我們再來測試一下，在參數sec_case_sensitive_logon為false的情況，我們控制密碼版本來解決ORA-01017這個錯誤

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這個參數文件，設置下面參數：

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

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

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>

此時驗證system賬號登陸，則不會報ORA-01017這個錯誤了。

$?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>

注意：最好使用其他賬號驗證測試，這里僅僅是為了偷懶，使用測試環境的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/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

到此這篇關于Oracle 19c的參數sec_case_sensitive_logon與ORA-01017錯誤的文章就介紹到這了,更多相關Oracle 19c sec_case_sensitive_logon與ORA-01017內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！

免責聲明：本文不構成任何商業建議，投資有風險，選擇需謹慎！本站發布的圖文一切為分享交流，傳播正能量，此文不保證數據的準確性，內容僅供參考

關鍵詞：