In 11g Oracle supports password to be case sensitive. But the password can be case insensitive depending on value of “PASSWORD_VERSIONS” and “sec_case_sensitive_logon”.
In 11g dba_users view has a new column called “PASSWORD_VERSIONS”, it can have 3 possible values “10G”, “10G 11G”, “11G”. Below shows what they mean along with parameter “sec_case_sensitive_logon”.
Scenario when the value of password_versions in DBA_USERS is “10G 11G”, when the value “10G 11G” the column “PASSWORD” and “SPARE4” has a value
SQL> select password_versions from dba_users where username = 'SCOTT';
PASSWORD
——–
10G 11G
SQL> select password, spare4 from sys.user$ where name = 'SCOTT';
PASSWORD
——————————
SPARE4
————————————————————————
F894844C34402B67
S:AEAEF0006791D6D6C90F9067BDDD37A475B4087625AA14F8BFF612A0145B
-- When the value of PASSWORD_VERSIONS is 10G 11G and sec_case_sensitive_login is TRUE, Oracle matches the case of the password.
SQL> show parameter sec_case_sensitive_logon;
NAME TYPE VALUE
———————————— ———– ——————-
sec_case_sensitive_logon boolean TRUE
SQL> connect scott/TIGER
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect scott/tiger
Connected.
SQL>
-- When the value of PASSWORD_VERSIONS is 10G 11G and sec_case_sensitive_login is FALSE the password entered does not have to be case-sensitive.
SQL> alter system set sec_case_sensitive_logon=FALSE;
System altered.
SQL> connect scott/TIGER
Connected.
Scenario when password_versions is 11G – In this scenario the password is case-sensitive irrespective what the value of sec_case_sensitive_logon so the password is always case-sensitive. In this case the column “PASSWORD” is NULL and SPARE4 is not NULL
SQL> select password_versions from dba_users where username = 'SCOTT';
PASSWORD
——–
11G
SQL> select password, spare4 from sys.user$ where name = 'SCOTT';
PASSWORD
——————————
SPARE4
——————————————————————-
S:CAED10CB7E2A275ACCCFCFB597530005310CFD9555FC5773802B4129B346
-- Shows example when sec_case_sensitive_logon is TRUE and password versions is 11G the password is case-sensitive irrespective the value of “sec_case_sensitive_logon”.
SQL> alter system set sec_case_sensitive_logon=TRUE;
System altered.
SQL> connect scott/TIGER
ERROR:
ORA-01017: invalid username/password; logon denied
-- Shows example when set sec_case_sensitive_logon is FALSE when password versions is 11G that it only works when password is case-sensitive
SQL> alter system set sec_case_sensitive_logon=FALSE;
System altered.
SQL> connect scott/TIGER
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect scott/tiger
Connected.
Scenario when password_versions is 10G – In this scenario the password is not case-sensitive. In this case in sys.user$ “PASSWORD” is NOT NULL and SPARE4 is NULL.
SQL> select password_versions from dba_users where username = 'SCOTT';
PASSWORD
——–
10G
SQL> select password, spare4 from sys.user$ where name = 'SCOTT';
PASSWORD
——————————
SPARE4
—————————————————————–
F894844C34402B67
-- Shows the password is not case sensitive so changing the parameter sec_case_sensitive_logon has no effect when password is entered for SCOTT
SQL> alter system set sec_case_sensitive_logon=true;
System altered.
SQL> connect scott/TIGER
Connected.
SQL> show parameter sec_case_sensitive_logon;
NAME TYPE VALUE
———————————— ———– ——-
sec_case_sensitive_logon boolean FALSE
SQL> connect scott/TIGER
Connected.
alexlima.com 