Case Sensitive Passwords in Oracle Database 11g Release 1.

I've been reading about new features and one of them are the "Case Sensitive Passwords" as a default feature of newly oracle 11g version.

The SEC_CASE_SENSITIVE_LOGON initialization parameter gives us the control over case sensitive passwords. We can use the ALTER SYSTEM command to turn off this functionality.

SQL> show parameter sec_case_sensitive_logon

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
SQL>
SQL>

Now lets try it using some examples and creating a new user with a mixed case password.

SQL> create user demo1 identified by demo1;

User created.

SQL> conn demo1/demo1
Connected.
SQL>
SQL> show user
USER is "DEMO1"
SQL> SQL> conn demo1/Demo1
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> conn demo1/deMo1
ERROR:
ORA-01017: invalid username/password; logon denied


SQL>

Ok, now let me switch the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE and we are able to connect using both variations of the password. Lets try.

SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

System altered.

SQL> conn demo1/demo1

Connected.
SQL> conn demo1/Demo1

Connected.
SQL> conn demo1/DEMO1

Connected.
SQL>

Just keep in mind that the original case sensitive password is retained, so it can be used if case sensitivity is subsequently enabled.

The DBA_USERS view includes a PASSWORD_VERSIONS column that indicates the database release in which the password was created or last modified.

SQL> desc DBA_USERS
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(8)
EDITIONS_ENABLED VARCHAR2(1)

SQL>

SQL> select username, password, password_versions from dba_users;

USERNAME PASSWORD PASSWORD
------------------------------ ------------------------------ --------
SYS 10G 11G
DBSNMP 10G 11G
DEMO1 10G 11G
SYSTEM 10G 11G
OUTLN 10G 11G
WKSYS 10G 11G
OLAPSYS 10G 11G
SI_INFORMTN_SCHEMA 10G 11G
OWBSYS 10G 11G
FLOWS_030000 10G 11G
ORDPLUGINS 10G 11G
WKPROXY 10G 11G
XDB 10G 11G
ANONYMOUS
.
.
.
.

SQL>

In this case it means both old and new-style hash values ar available for user DEMO1, the new hash value is stored in the USER$.SPARE4 column, as long as this remains NULL it means the password has not been changed since the migration and the user will have the old case insensitive password.

We can note above that the hash value is not visible anymore like in old Oracle versions. But we still can check this value using dictionary table named SYS.USER$.

SQL> select name,password from sys.user$ where name='DEMO1';

NAME PASSWORD
------------------------------ ------------------------------
DEMO1 DD9FD1922A0AFC29

SQL>

In addition one more interesting thing that we have in Oracle 11g is a new dictionary view named DBA_USERS_WITH_DEFPWD. This view help us to find users using "default" passwords and we must change them as soon as possible.

SQL> select * from dba_users_with_defpwd;

USERNAME
------------------------------
DIP
MDSYS
SPATIAL_WFS_ADMIN_USR
TSMSYS
WK_TEST
CTXSYS
OUTLN
OWBSYS
SPATIAL_CSW_ADMIN_USR
EXFSYS
ORACLE_OCM
.
.
.
.
SQL>