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內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！
推薦閱讀：
- ORACLE查看當前賬號的相關信息
- oracle創建用戶過程詳解
- Oracle查看表空間使用率以及爆滿解決方案詳解
- Oracle查詢表空間大小及每個表所占空間的大小語句示例
- springdata jpa使用Example快速實現動態查詢功能