Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to solve the oracle 19c sec_case_sensitive_logon parameter problem". In daily operation, I believe many people have doubts about how to solve the oracle 19c sec_case_sensitive_logon parameter problem. Xiaobian consulted all kinds of information and sorted out simple and easy to use operation methods. I hope to help you answer the doubts about "how to solve the oracle 19c sec_case_sensitive_logon parameter problem"! Next, please follow the small series to learn together!

Before 11g, passwords were case-insensitive. In 11g, sec_case_sensitive_logon parameter was introduced to control password sensitivity. By default, it is true and case-sensitive.

After installing 19C, set sec_case_sensitive_logon to false, set password case insensitive, and find that even if you use the correct password, you cannot log in.

The sec_case_sensitive_logon parameter has long been abandoned in 19C. You can also see the prompt after restarting the database:

There are many articles related to MOS, such as:

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 sensitiveFalse - Database logon passwords are not case sensitiveFor additional information please refer to the following Database Security Guide: https://docs.oracle.com/database/121/DBSEG/authentication.htm#DBSEG3225

The test process is as follows:

View Database Version

SQL> select banner from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Start all PDBs

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

View current parameter values

SQL> show parameter sec_case_sensitive_logonNAME TYPE VALUE------------------------------------ ----------- ------------------------------sec_case_sensitive_logon boolean TRUE

create a new user

SQL> create user c##chen identified by a;User created.SQL> grant connect to c##chen;Grant succeeded.

passwords are case-sensitive

SQL> conn c##chen/aConnected.SQL> conn c##chen/AERROR:ORA-01017: invalid username/password; logon deniedWarning: You are no longer connected to ORACLE.

change parameters

SQL> show parameter sec_case_sensitive_logonNAME TYPE VALUE------------------------------------ ----------- ------------------------------sec_case_sensitive_logon boolean TRUESQL> conn / as sysdbaConnected.SQL> alter system set sec_case_sensitive_logon=false;System altered.

New users cannot log in (even with the correct password)

SQL> conn c##chen/aERROR:ORA-01017: invalid username/password; logon deniedWarning: You are no longer connected to ORACLE.SQL> conn c##chen/AERROR:ORA-01017: invalid username/password; logon denied

Change back parameters

SQL> conn / as sysdbaConnected.SQL> alter system set sec_case_sensitive_logon=true;System altered.SQL> conn c##chen/AERROR:ORA-01017: invalid username/password; logon deniedWarning: You are no longer connected to ORACLE.

Can log in normally

SQL> conn c##chen/aConnected.SQL> show userUSER is "C##CHEN"

Case insensitivity can also be achieved by setting sqlnet.ora

This parameter is used to limit the minimum client version that can connect to the database server. For example, if the setting value is 10, that is, client versions above 10g and 11g can connect to the database server.

[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

modify the parameters

SQL> alter system set sec_case_sensitive_logon=false;System altered.

create user

SQL> create user c##ccc identified by a;User created.SQL> grant connect to c##ccc;Grant succeeded.

Password case-insensitive

SQL> conn c##ccc/aConnected.SQL> conn c##ccc/AConnected. At this point, the study of "how to solve the oracle 19c sec_case_sensitive_logon parameter problem" is over, hoping to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.