这篇文章主要介绍“怎么解决oracle 19c sec_case_sensitive_logon参数问题”,在日常操作中,相信很多人在怎么解决oracle 19c sec_case_sensitive_logon参数问题问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”怎么解决oracle 19c sec_case_sensitive_logon参数问题”的疑惑有所帮助!接下来,请跟着小编一起来学习吧!
11g前,密码不区分大小写,11g开始引入了sec_case_sensitive_logon参数控制密码敏感,默认true,大小写敏感。
安装完19C后,将sec_case_sensitive_logon调成false,设置密码大小写不敏感后,发现即使使用正确的密码也无法登录了。
原来19C中早已废弃了sec_case_sensitive_logon参数,重启数据库也可以看到提示:
MOS上相关的文章有很多,例如:
DBUA Removes SEC_CASE_SENSITIVE_LOGON Init Parameter After Upgrading to 12c (Doc ID 2107717.1) SEC_CASE_SENSITIVE_LOGON init parameter has been deprecated in 12c, hence ideally you should comment out this parameter before upgrade to 12c. But if you still set SEC_CASE_SENSITIVE_LOGON system parameter, the Database Upgrade Assistant (DBUA) removes the SEC_CASE_SENSITIVE_LOGON system parameter during the upgrade process if it exists in the parameter file (reference Bug 16238456 ). 18c: All user connections fail with ORA-01017 except SYS when SEC_CASE_SENSITIVE_LOGON=FALSE (Doc ID 2502204.1) What Is the SEC_CASE_SENSITIVE_LOGON Parameter and How Is It Used? (Doc ID 2378179.1) The SEC_CASE_SENSITIVE_LOGON parameter enables or disables password case sensitivity in the database. It is defaulted to a TRUE value. True - Database logon passwords are case sensitive False - Database logon passwords are not case sensitive For additional information please refer to the following Database Security Guide: https://docs.oracle.com/database/121/DBSEG/authentication.htm#DBSEG3225
测试过程如下:
查看数据库版本
SQL> select banner from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
启动所有PDB
SQL> alter pluggable database all open; SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 CJCPDB READ WRITE NO
查看当前参数值
SQL> show parameter sec_case_sensitive_logon NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sec_case_sensitive_logon boolean TRUE
创建新用户
SQL> create user c##chen identified by a; User created. SQL> grant connect to c##chen; Grant succeeded.
密码区分大小写
SQL> conn c##chen/a Connected. SQL> conn c##chen/A ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE.
更改参数
SQL> show parameter sec_case_sensitive_logon NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sec_case_sensitive_logon boolean TRUE
SQL> conn / as sysdba Connected. SQL> alter system set sec_case_sensitive_logon=false; System altered.
新用户无法登录(即使使用正确的密码)
SQL> conn c##chen/a ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL> conn c##chen/A ERROR: ORA-01017: invalid username/password; logon denied
改回参数
SQL> conn / as sysdba Connected. SQL> alter system set sec_case_sensitive_logon=true; System altered. SQL> conn c##chen/A ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE.
可以正常登录
SQL> conn c##chen/a Connected. SQL> show user USER is "C##CHEN"
也可以通过设置sqlnet.ora,实现大小写不敏感
该参数用来限制可以连接到数据库服务器上的最小客户端版本,比如设置值为10,即10g,11g等以上客户端版本可以连接到数据库服务器上。
[oracle@cjcos01 admin]$ pwd /u01/app/oracle/product/19.0.0/dbhome_1/network/admin [oracle@cjcos01 admin]$ cat sqlnet.ora SQLNET.ALLOWED_LOGON_VERSION_SERVER=8 SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
修改参数
SQL> alter system set sec_case_sensitive_logon=false; System altered.
创建用户
SQL> create user c##ccc identified by a; User created. SQL> grant connect to c##ccc; Grant succeeded.
密码大小写不敏感
SQL> conn c##ccc/a Connected. SQL> conn c##ccc/A Connected.
到此,关于“怎么解决oracle 19c sec_case_sensitive_logon参数问题”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注亿速云网站,小编会继续努力为大家带来更多实用的文章!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。