Case sensitive passwords (and auditing) are a default feature of newly created Oracle 11g databases. The Database Configuration Assistant (DBCA) allows you to revert these settings back to the pre-11g functionality during database creation.
The SEC_CASE_SENSITIVE_LOGON initialization parameter gives control over case sensitive passwords. If existing applications struggle to authenticate against 11g, you can use the ALTER SYSTEM command to turn off this functionality.
SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGONNAME TYPE VALUE------------------------------------ ----------- ------------------------------sec_case_sensitive_logon boolean TRUESQL> SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;System altered.SQL>
The following code demonstrates the case sensitive password functionality. First, it resets the SEC_CASE_SENSITIVE_LOGON initialization parameter to TRUE and creates a new user with a mixed case password.
CONN / AS SYSDBAALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE;CREATE USER test2 IDENTIFIED BY Test2;GRANT CONNECT TO test2;
We can see the case sensitive password functionality in operation if we attempt to connect to the new user with both the correct and incorrect case password.
SQL> CONN test2/Test2Connected.SQL> CONN test2/test2ERROR:ORA-01017: invalid username/password; logon deniedWarning: You are no longer connected to ORACLE.SQL>
By switching the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE we are able to connect using both variations of the password.
CONN / AS SYSDBAALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;SQL> CONN test2/Test2Connected.SQL> CONN test2/test2Connected.SQL>
The important thing to remember here is even when case sensitive passwords are not enabled, the original case of the password is retained so it can be used if case sensitivity is subsequently enabled. The following code disables case sensitivity and creates a new user with a mixed case password.
CONN / AS SYSDBAALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;CREATE USER test3 IDENTIFIED BY Test3;GRANT CONNECT TO test3;
As you would expect, connection to the user is possible regardless of the case of the password.
SQL> CONN test3/Test3Connected.SQL> CONN test3/test3Connected.SQL>
If we enable case sensitivity, authentication is done against the mixed case password.
CONN / AS SYSDBAALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE;SQL> CONN test3/Test3Connected.SQL> CONN test3/test3ERROR:ORA-01017: invalid username/password; logon deniedWarning: You are no longer connected to ORACLE.SQL>
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;USERNAME PASSWORD------------------------------ --------TEST 10G 11GSPATIAL_WFS_ADMIN_USR 10G 11GSPATIAL_CSW_ADMIN_USR 10G 11GAPEX_PUBLIC_USER 10G 11G...SYSTEM 10G 11GSYS 10G 11GMGMT_VIEW 10G 11GOUTLN 10G 11G32 rows selected.SQL>
Users imported from a 10g database have a PASSWORD_VERSIONS value of "10G" and maintain case insensitive passwords independent of the SEC_CASE_SENSITIVE_LOGON parameter setting. 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. When privileged users (SYSDBA & SYSOPER) are imported from a previous release their passwords are included in the password file. These users will retain case insensitive passwords until the password is modified.
To create case insensitive passwords in the password file, recreate the password file using the ignorecase=y option.
$ orapwd file=orapwDB11Gb entries=100 ignorecase=y password=mypassword
The passwords associated with database links are also case sensitive, which presents some issues when connecting between different releases:
For more information see:
Hope this helps. Regards Tim...
联系客服
