热门
立即入驻

Oracle？19c的参数sec_case_sensitive_logon与ORA-01017错误问题分析（oracle数据库参数）一看就会

随心笔谈1年前发布 编辑
199 0
🌐 经济型：买域名、轻量云服务器、用途：游戏 网站等 《腾讯云》特点：特价机便宜 适合初学者用 点我优惠购买
🚀 拓展型：买域名、轻量云服务器、用途：游戏 网站等 《阿里云》特点：中档服务器便宜 域名备案事多 点我优惠购买
🛡️ 稳定型：买域名、轻量云服务器、用途：游戏 网站等 《西部数码》 特点：比上两家略贵但是稳定性超好事也少 点我优惠购买



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?TYPEVALUE
————————–?———–?——————————
sec_case_sensitive_logonboolean?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
9FROM?DBA_USERS?
?10WHERE?USERNAME=UPPER(‘&USERNAME’)
?11ORDER?BY?EXPIRY_DATE;
Enter?value?for?username:?system
old10:?WHERE?USERNAME=UPPER(‘&USERNAME’)
new10:?WHERE?USERNAME=UPPER(‘system’)

USERNAMEACCOUNT_STATUS?DEFAULT_TABLESPA?TEMPORARY_?PROFILELOCK_DATE?EXPIRY_DATEPASSWORD_VER
———-?—————-?—————-?———-?———-?—————?——————–?————
SYSTEM?OPEN?SYSTEM?TEMP?DEFAULT2023-10-22?17:25:0911G?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?TYPEVALUE
————————————?———–?——————————
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
9FROM?DBA_USERS?
?10WHERE?USERNAME=UPPER(‘&USERNAME’)
?11ORDER?BY?EXPIRY_DATE;
Enter?value?for?username:?system
old10:?WHERE?USERNAME=UPPER(‘&USERNAME’)
new10:?WHERE?USERNAME=UPPER(‘system’)

USERNAME?ACCOUNT_STATUS?DEFAULT_TABLESPA?TEMPORARY_?PROFILELOCK_DATEEXPIRY_DATEPASSWORD_VER
—————-?—————-?—————-?———-?———-?——————–?——————–?————
SYSTEM?OPEN?SYSTEM?TEMP?DEFAULT?2023-10-23?09:21:2710G?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内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

您可能感兴趣的文章:Oracle数据库19C的安装与配置指南(2022年最新版)CenterOs7 安装oracle19c的方法详解Windows10安装Oracle19c数据库详细记录(图文详解)

© 版权声明
文章版权归作者所有，未经允许请勿转载。

相关文章

添加小工具
点此为“正文侧边栏”添加小工具