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.
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.
No comments:
Post a Comment