Forum Stats

  • 3,837,263 Users
  • 2,262,245 Discussions
  • 7,900,240 Comments

Discussions

21cXE: password case sensitive feature

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

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,580 Red Diamond

    @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
    cormaco Member Posts: 1,950 Silver Crown

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

    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
    Solomon Yakobson Member Posts: 19,580 Red Diamond
    edited Jul 2, 2022 12:19PM

    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.