智能AI导航网 智能AI导航网
热门
立即入驻

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

随心笔谈11个月前发布 admin
114 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数据库详细记录(图文详解)

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

相关文章

Erlang实现的百度云推送Android服务端实例（二郎系拉面 杭州）学到了
admin
90 0
powershell远程管理服务器磁盘空间的实现代码（powershell远程连接服务器）学会了吗
admin
98 0
sqlserver2005利用临时表和@@RowCount提高分页查询存储过程性能示例分享（sql server临时表的生命周期）一篇读懂
admin
84 0
大学生代刷网课系统源码 刷网课平台系统源码
admin
110 0
Win32 下病毒设计入门详细说明（电脑病毒win32）学会了吗
admin
69 0
Ruby操作CSV格式数据方法详解（ruby-position）没想到
admin
92 0
添加小工具
点此为“正文侧边栏”添加小工具
一个神奇的网站导航工具站-提供最权威高质量的在线网站导引服务-让您第一时间到达所需要的网站地址

友链申请 免责声明 广告合作 关于我们

关注我们智能AI导航网

关注我们
Copyright © 2024 智能AI导航网