Zahid Karim
Recent Posts
1.Write fast and efficient PLSQL - DBMS_PROFILER
2.Fix physically corrupt data blocks using PLSQL - DBMS_REPAIR
3.Encrypt or Decrypt sensitive data using PLSQL - DBMS_CRYPTO
4.ORA-24248 XMLDB extensible security not installed
5.Upgrade Oracle 10g Release 2 to Oracle 11g Release 1
Find me on
Oracle 11g Access Control List for External Network Services Oracle 11g Release 1 Pre Upgrade tool utlu111i sql

Oracle 11g Case Sensitive Passwords

Posted by Zahid on July 20, 2010.

Oracle 11g by default force case sensitivity of user passwords. The users have to provide passwords in the same case (upper, lower or mixed) they created the password with. Lets observe this behavior.

Case sensitive user passwords in Oracle 11g

SQL> create user u1 identified by U1;

User created.

SQL> grant create session to u1;

Grant succeeded.

SQL> conn u1/u1
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

We have created a user named u1 with a password U1. Now this user has to provide the password as U1 but not u1. This behavior is controlled with an initialization parameter SEC_CASE_SENSITIVE_LOGON. By default it has a value TRUE.

SQL> conn / as sysdba
Connected.
SQL> show parameter sec_case_sensitive_logon;

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

SQL> alter system set sec_case_sensitive_logon=false scope=both;

System altered.

SQL> show parameter sec_case_sensitive_logon

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

SQL> conn u1/u1
Connected.

Now that sec_case_sensitive_logon is set to false, the case of the password doesn't matter.

Remember this password "U1" is stored in the case it was created with, so as soon as you switch the case sensitive password to on again, it will start forcing the case of the password.

SQL> alter system set sec_case_sensitive_logon=true scope=both;

System altered.

SQL> conn u1/u1
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

SQL> conn u1/U1
Connected.
SQL> 

As oracle releases before 11g didn't have case sensitivity on password, so when you import your users from a pre-11g database to 11g database their password is still case insensitive. The passwords for these users will become case sensitive when they change their passwords after they are imported to 11g database, if the password case sensitivity is enabled. So after importing users to 11g database pro pre 11g releases e.g. 10g ask the users to change their passwords immediately for better password security.

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;

USERNAME                       PASSWORD_VERSIONS
------------------------------ -----------------
SCOTT                          10G 11G
HR                             10G 11G
ZAHID                          10G 11G
U1                             11G
KAREEM                         10G

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.

Case sensitive password in Password File in Oracle 11g

$ cd $ORACLE_HOME/dbs

$ orapwd file=orapwora11g entries=30 password=System  ignorecase=n

-- This will create a password file with a password "System" and ignorecase=n
-- ignorecase=n is the default.

$ sqlplus sys/system@ora11g as sysdba

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: 

-- When I provide the password as "system" the logon was denied.

$ sqlplus sys/System@ora11g as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 21 06:11:41 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 

-- I am successfully logged in when I used the password "System".

ignorecase=n is the default with the orapwd command in oracle 11g i.e. you mention it or not it will force the password to be case sensitive when users log in as SYSDBA remotely.

To turn off password case sensitivity in password file we need to explicitly mention ignorecase=y while creating the password file.

$ cd $ORACLE_HOME/dbs

$ rm orapwora11g

$ orapwd file=orapwora11g entries=30 password=System ignorecase=y

$ sqlplus sys/system@ora11g as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 21 06:19:24 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

This time I created a password file with ignorecase=y and I can login with a password "system".

If password file was created with ignorecase=n and you have imported a SYSDBA or SYSOPER user from previous oracle release, their passwords will be included in the password file but they will be able to use case insensitive passwords unless they modify their passwords. So ask users to modify their passwords once they are shifted to 11g for better security.

Db Links Passwords Case Sensitivity in Oracle 11g

There could be three scenarios of db link connections.

1). A user connecting from a pre-11g database to 11g database

If the password case sensitivity is switched on in the 11g database then recreate the db link with the password in uppercase before the user can connect to 11g database. As db link passwords are always created in uppercase no matter what case was used at the time of db link creation.

2). A user connecting from 11g database to 11g database.

If the user is connecting from a 11g database to 11g database and password case sensitivity is enabled, the user must enter the password using the case in which it was created.

3). A user connecting from 11g database to a pre-11g database.

If the user is connecting from a 11g database to pre-11g database then the case of the password doesn't matter. As there is no password case sensitivity in the pre-11g database.