--

Wednesday, March 26, 2008

11g New Features - Case-sensitive passwords

Probably a long overdue feature...though one could have implemented the same using password verify function in earlier releases but it was necessitated to be in compliance with industry wide Data security standards. Starting 11g case sensitive passwords automatically enforced.

Here is how to implement case-sensitive passwords feature :-

SIPRAS@11glab> create user TESTUSR identified by TESTUSR;

User created.

SIPRAS@11glab> grant create session to TESTUSR;

Grant succeeded.

SIPRAS@11glab> conn testusr/testusr@11glab
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
@> conn testusr/TESTUSR@11glab
Connected.
TESTUSR@11glab>


See the difference - since the user was created with an upper case password, it did not allow lower case password while connecting to "TESTUSR". Had it been 10g, you would easily got connected. So now, "TESTUSR", "testusr" and "TestUsr" are different passwords.

However, Oracle has also provided an initialization parameter to disable case-sensitive passwords i.e. going back to old way of 10g and prior versions.

SIPRAS@11glab> show parameter SEC_CASE_SENSITIVE_LOGON

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

SIPRAS@11glab> ALTER SYSTEM set SEC_CASE_SENSITIVE_LOGON=FALSE scope=both;

System altered.

SIPRAS@11glab> show parameter SEC_CASE_SENSITIVE_LOGON

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean FALSE

And now see the difference...

SIPRAS@11glab> conn testusr/testusr@11glab
Connected.

TESTUSR@11glab> conn testusr/TESTUSR@11glab
Connected.
TESTUSR@11glab>


So it would connect irrespective of case. A new column "PASSWORD_VERSIONS" has been added to "DBA_USERS" view to indicate database version in which the password was created or changed.

SIPRAS@11glab> select username,PASSWORD_VERSIONS from dba_users;

USERNAME PASSWORD
------------------------------ --------
.....
SCOTT 10G 11G
TESTUSR 10G 11G


However, I am not able to find answer as to why a user created in 11g has both "10G" and "11G" in PASSWORD_VERSIONS column. According to the documentation if a database was migrated from 10g then it would have both "10G", "11G" in it which is not true in my case..

One can also enforce case-sensitive passwords for SYSDBA users. Use "ignorecase" argument while creating password files using "ORAPWD" utility. Default values for "ignorecase" is "n", and you can set it to "y" to enable case-sensitive passwords.

e.g. $orapwd file=orapw entries=5 ignorecase=y

So if you plan to upgrade to 11g then make sure you change passwords to adhere to case-sensitivity and ensure that you change your scripts which have inconsistent password cases too.

Reference : Oracle® Database Security Guide 11g Release 1 (11.1) Part Number B28531-04

No comments: