oracle?19c的參數(shù)sec_case_sensitive_logon與ora-01017錯誤問題分析

oracle的參數(shù)sec_case_sensitive_logon是oracle 11g開始被引入。這個參數(shù)主要是為了控制密碼的大小寫敏感問題。
sec_case_sensitive_logon=true表示密碼區(qū)分大小寫。
sec_case_sensitive_logon=false表示密碼不區(qū)分大小寫。
從oracle 12c開始，參數(shù)sec_case_sensitive_logon被棄用了。但是為了向下兼容，即使在oracle 19c中,這個參數(shù)依然保留了。這個參數(shù)在oracle 12c(確切的說是12.2以及后續(xù)版本)和19c中不能設(shè)置為false，因為它和sqlnet.allowed_logon_version_server=12或者sqlnet.allowed_logon_version_server=12a不兼容。這是因為用于此模式的更安全的密碼版本僅支持區(qū)分大小寫的密碼檢查。簡單點來說，就是這種環(huán)境下，這種設(shè)置會沖突。官方文檔[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.

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

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

$?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(這個參數(shù)調(diào)整后可以立即生效，不用重啟)，

sql>?show?user;
user?is?"sys"
sql>?alter?system?set?sec_case_sensitive_logon=true?scope=both;

system?altered.

sql>

然后驗證如下所示所示，一切正常，所以如果你遇到ora-01017這個錯誤，而且數(shù)據(jù)庫版本為12c/19c,如果你確認你密碼是正確的，那么檢查一下這個參數(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>

下面我們再來測試一下，在參數(shù)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這個參數(shù)文件，設(shè)置下面參數(shù)：

sqlnet.allowed_logon_version_client?=?10
sqlnet.allowed_logon_version_server?=?10

修改sqlnet.ora參數(shù)文件后，必須重新登陸sqlplus后（如果使用之前的sqlplus連接也不會生效），執(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>

此時驗證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>

注意：最好使用其他賬號驗證測試，這里僅僅是為了偷懶，使用測試環(huán)境的system賬號測試驗證。更多相關(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錯誤的文章就介紹至此，更多相關(guān)oracle 19c sec_case_sensitive_logon與ora-01017內(nèi)容請搜索碩編程以前的文章，希望以后支持碩編程！