Case Sensitive Passwords in Oracle 11g

With the release of oracle 11g database, oracle has added new feature regarding case sensitivity of password in the database. In lower versions of oracle database passwords are not case sensitive and user can specify their password in uppercase, lowercase or mixed, no matter how it was created.

New parameter SEC_CASE_SENSITIVE_LOGON enables or disables password case sensitivity in the database. It's boolean parameter and can have TRUE or FALSE as parameter value.(by default it is true).

TRUE    - Database logon passwords are case sensitive.
FALSE   - Database logon passwords are not case sensitive.


Here is the Demo of Password Case-Sensitivity
Database name  =   noida

C:\>sqlplus sys/XXXX@noida as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Apr 12 14:36:04 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> sho parameter SEC_CASE_SENSITIVE_LOGON
NAME                                                TYPE             VALUE
---------------------------                       -----------     ---------------
sec_case_sensitive_logon              boolean            TRUE


SQL> create user pass identified by Test ;  (Here Test  is in mixed case )
User created.
SQL> grant connect to pass;
Grant succeeded.
SQL> conn pass/test@noida
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

SQL> conn sys/xxxx@noida as sysdba
Connected.
SQL> conn pass/Test@noida     
Connected.

SQL> conn sys/xxxx@noida as sysdba
Connected.
SQL> alter system set SEC_CASE_SENSITIVE_LOGON=false ; 
System altered.
SQL> conn pass/test@noida
Connected.

Hence, Oracle 11g implements a more secure SHA1 algorithm that supports mixed-case passwords and add salts to stored passwords. Multi-byte passwords are also supported in 11g. This functionality is controlled by a new initialization parameter, SEC_CASE_SENSITIVE_LOGON (default is TRUE).

Weaker password hashes are still being stored in the SYS.USER$ table for passwords created in prior releases. Hence, it is recommended to change all passwords after upgrading to 11g .

There is a new column in DBA_USERS view which shows the history of the user passwords.


SQL> select username,password_versions from dba_users  ;
HR                                10G 11G
PASS                            10G 11G
NEER                            10G
ABC                              10G 11G
SCOTT                          10G 11G

The users having password_version=10g 11g, means they are imported from 10g and they have modified their passwords after being imported to 11g .

The users having password_version=11g , means these users were created in 11g database.

The users having password_version=10g , means they were imported from 10g and haven't yet changed their password after being imported to 11g.




Enjoy      :-)