If value set to
true: Database logon passwords are case sensitive.
false: Database logon passwords are not case sensitive.
Using database configuration assistant we can revert these settings back to the pre-11g functionality during database creation as well as after database is created.
If you want to revert the settings after database is created then invoke
- dbca on the command prompt,
- click next,
- select Configure Database Options Radio button,
- click next,
- select database that you want to configure and click next,
- You will see step 3 of 6- click next,
- Now in the step 4 of 6 by clicking Revert to pre-11g default security settings and then both checking the Revert Audit settings to pre-11g defaults
and Revert password profile settings to pre-11g settings check box will return you
to default no audit as well as password in case insensitive.
Here is the image of the window so that you can have a better idea.
In order to know the current value of the sec_case_sensitive_logon from sql*plus issue,
SQL> SHOW PARAMETER sec_case_sensitive_logon
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
If you don't use sql*plus you can query from v$parameter in order to see the value of it. Note that the name sec_case_sensitive_logon is in lower case. As it is string so you have to use lower case name within single quotes.
SQL> select value from v$parameter where name='sec_case_sensitive_logon';
VALUE
--------------------------------------------------------------------------------
TRUE
This parameter can be changed in session level. In order to make it FALSE issue,
SQL> alter system set sec_case_sensitive_logon=FALSE;
System altered.
Check value from data dictionary by,
SQL> select value from v$parameter where name='sec_case_sensitive_logon';
VALUE
--------------------------------------------------------------------------------
FALSE
Now let's play with this parameter. First make it TRUE.
SQL> alter system set sec_case_sensitive_logon=TRUE;
System altered.
Now create a user named test with password TeSt. Note T and S in uppercase.
SQL> create user test identified by TeSt;
User created.
Grant create session to test so that test can login to database.
SQL> grant create session to test;
Grant succeeded.
Try to connect as test with password TeSt. It will always succeed.
SQL> conn test/TeSt
Connected.
Now let's try to connect with password test. As we have sec_case_sensitive_logon to TRUE so this will fail because now case sensitivity of password will be checked.
SQL> conn test/test
ERROR:
ORA-01017: invalid username/password; logon denied
Connect as sysdba and alter the value of sec_case_sensitive_logon
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;
System altered.
Now we will see case of password is not important. Any case with work either. As sec_case_sensitive_logon is set to FALSE so while login password case will not be checked.
SQL> conn test/test
Connected.
SQL> conn test/TeSt
Connected.
Now here the important thing to remember is even when case sensitive passwords are not enabled, the original case of the password is retained. So if case sensitivity is enabled the original case will be checked. Let's disable the case sensitivity and creates a new user with a mixed case password.
SQL> conn / as sysdba
Connected.
SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON
NAME TYPE VALUE
------------------------------------ ----------- --------------------------
sec_case_sensitive_logon boolean FALSE
SQL> create user test2 identified by TeST2;
User created.
SQL> grant create session to test2;
Grant succeeded.
Any case will work as sec_case_sensitive_logon is set to false.
SQL> conn test2/test2
Connected.
SQL> conn test2/teST2
Connected.
Now if sec_case_sensitive_logon is set to TRUE then authentication is done against the mixed case password.
SQL> conn test2/test2
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn test2/TeST2
Connected.
The DBA_USERS view includes a PASSWORD_VERSIONS column that indicates the database release in which the password was created or last modified.
SQL> select username, password_versions from dba_users where username in ('TEST','TEST2');
USERNAME PASSWORD
------------------------------ --------
TEST2 10G 11G
TEST 10G 11G
Note that if users are imported from a 10g database then in the dba_users view the field of PASSWORD_VERSIONS value is "10G" and maintain case insensitive passwords independent of the SEC_CASE_SENSITIVE_LOGON parameter setting. Though their passwords become case sensitive as soon as they are changed, assuming the SEC_CASE_SENSITIVE_LOGON parameter is set to TRUE.
The ignorecase parameter of the orapwd utility allows control over case sensitivity of passwords in the password file. The default value is "n", meaning the passwords are case sensitive. If you set to ignorecase=y then password is case-insensitive within password file.
One things while creating database link it is good to remember that, the passwords associated with database links are also case sensitive.
Related Documents
http://arjudba.blogspot.com/2008/05/password-verification-or-complexity.html
http://arjudba.blogspot.com/2008/05/playing-with-oracle-password-identified.html
http://arjudba.blogspot.com/2008/07/solution-of-ora-28002-password-will.html
http://arjudba.blogspot.com/2008/07/ora-28003-ora-20002-while-changing.html
No comments:
Post a Comment