21cXE: password case sensitive feature

SmithJohn45

VM: Windows 10 64bit.

yesterday downloaded fresh copy of Oracle 21cXE, today i installed and tried to remove password case sensitive but seems it is deprecated or changed.

if it is yet, how i can set it to FALSE? please help.

regards

L. Fernigrini

No, you cannot have non sensitives password since Oracle 18c

https://docs.oracle.com/en/database/oracle/oracle-database/18/spuss/understanding-password-case-sensitivity-and-upgrades.html#GUID-6428A98A-6621-4E49-89E5-FAFD51F04B60

Solomon Yakobson

@L. Fernigrini: No, you cannot have non sensitives password since Oracle 18c

Not true. You can still have it even in 19C (I didn't check 20)

SQL> show parameter sec_case_sensitive_logon

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE
SQL> select version_full from v$instance;

VERSION_FULL
-----------------
19.13.0.0.0

SQL> create user test_user identified by TeSt#UsEr123;

User created.

SQL> select password_versions from dba_users where username = 'TEST_USER';

PASSWORD_VERSIONS
-----------------
10G 11G 12C       -- see that 10G - that's because sec_case_sensitive_logon is FALSE

SQL> grant create session to test_user;

Grant succeeded.

SQL> connect test_user/TEST#[email protected]
Connected.
SQL> connect test_user/test#[email protected]
Connected.
SQL>

SY.

cormaco

According to the release notes the parameter was desupported in Oracle 21;

https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/changes-this-release-oracle-database-reference.html

The following features are desupported in Oracle Database 21c:

• Initialization parameters:
  • CLUSTER_DATABASE_INSTANCES
  • REMOTE_OS_AUTHENT
  • SEC_CASE_SENSITIVE_LOGON
  • UNIFIED_AUDIT_SGA_QUEUE_SIZE

Solomon Yakobson

Yes, it is desupported in 21C but I couldn't find anything in docs where it explains what happens to 10g password verions only IDs. Many companies keep upgrading Oracle version from 10G up and setting sec_case_sensitive_logon to true thinking that's all that is needed to enforce case sensitive logins - not true. ID with password versions 10G only stores nothing but old upper-cased password hash. I wish Oracle would raise login error when password versions is 10G only and sec_case_sensitive_logon is set to true, but it simply ignores sec_case_sensitive_logon and lets user in:

SQL> select version_full from v$instance;

VERSION_FULL
-----------------
19.13.0.0.0

SQL> show parameter sec_case_sensitive_logon

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE

SQL> select username,password_versions from dba_users where username = 'TEST_USER';


USERNAME   PASSWORD_VERSIONS
---------- -----------------
TEST_USER  10G

SQL> connect test_user/[email protected]
Connected.
SQL> connect test_user/[email protected]
Connected.
SQL> connect test_user/[email protected]
Connected.
SQL>

As you can see case sensitive login is ignored since ID is 10G only password ID. We MUST change password (even to itself) to force Oracle to save case sensitive password hash and change password version to include whatever our version password hashing is.

Anyway, I wonder what happens to 10G only password versions IDs when we upgrade to 21C.

SY.