Managing Password Case Sensitivity

You can manage the password case sensitivity for passwords from user accounts from previous releases.

Management of Case Sensitivity for Secure Role Passwords

Oracle Database ensures that the passwords for secure roles are case sensitive.

If before upgrading to the current release, you created secure roles by using the IDENTIFIED BY clause of the CREATE ROLE statement, and if upon upgrading to Oracle Database 12c release 12.2, you set the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter to one of the Exclusive Modes 12 or 12a, then you must change the password for these secure roles in order for them to remain usable. Because Exclusive Mode is now the default, secure roles that were created in earlier releases (such as Oracle Database 10g, in which the 10G password version was the default) will need to have their passwords changed. These passwords will automatically be case sensitive.

You can query the PASSWORD_REQUIRED and AUTHENTICATION_TYPE columns of the DBA_ROLES data dictionary view to find any secure roles that must have their password changed after upgrading to the current release, in order to become usable again.

Management of Password Versions of Users

By default, Oracle Database uses Exclusive Mode, which does not permit case-insensitive passwords, to manage password versions.

In a default installation, the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter is set to 12 to enable Exclusive Mode. Exclusive Mode requires that the password-based authentication protocol use one of the case-sensitive password versions (11G or 12C) for the account that is being authenticated. Exclusive Mode excludes the use of the 10G password version that was used in earlier releases. After you upgrade to Oracle Database 12c release 2 (12.2) or later, accounts that use the 10G password version become inaccessible. (As of Oracle AI Database 26ai, the 10G password version is no longer supported.) This occurs because the server runs in Exclusive Mode by default, and Exclusive Mode cannot use the old 10G password version to authenticate the client. The server is left with no password version with which to authenticate the client.

The user accounts from Release 10g use the 10G password version. Therefore, you should find the user accounts that use the 10G password version, and then reset the passwords for these accounts. This generates the appropriate password version based on the setting of the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter, as follows:

After the user accounts from an Oracle Database release 10g (or earlier) have been imported into the current database release, if a user had only the 10G password version, then a database administrator must alter the user’s password. This sets the user’s password version to be 11G and 12C, so that the password automatically becomes case sensitive.

Finding and Resetting User Passwords That Use the 10G Password Version

For better security, find and reset passwords for user accounts that use the 10G password version so that they use later, more secure password versions.

Starting in Oracle AI Database 26ai, the 10G password version is no longer supported.

Finding All Password Versions of Current Users

You can query the DBA_USERS data dictionary view to find a list of all the password versions configured for user accounts.

For example:

SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;

USERNAME                       PASSWORD_VERSIONS
------------------------------ -----------------
JONES                          10G 11G 12C
ADAMS                          10G 11G
CLARK                          10G 11G
PRESTON                        11G
BLAKE                          10G

The PASSWORD_VERSIONS column shows the list of password versions that exist for the account. 10G refers to the desupported case-insensitive Oracle password version, 11G refers to the SHA-1-based password version, and 12C refers to the SHA-2-based SHA-512 password version.

Note: Starting with Oracle AI Database 26ai, the SHA-1 verifier introduced with Oracle Database 11g is deprecated.The salted multi-round SHA-512 password hash (also known as “verifier”) introduced with Oracle Database 12c provides enhanced security for your password. If 11g verifiers (11G) are still being used in your database, then Oracle recommends resetting them so they can be upgraded to the 12c (12C) de-optimized PBKDF2-based verifier.

Resetting User Passwords That Use Only the 10G Password Version

You should remove the 10G password version from the accounts of all users and then ensure that users are using the 11G or later verifiers. If you have already upgraded to release 26ai or later, a user who has only the 10G password version cannot log in to the database, because the 10G password version is no longer supported. An administrator will need to manually reset this user’s password.

  1. Ensure that all clients have the O5L_NP capability by making ensuring that they have the CPUOct2012 patch.

    See Oracle AI Database Net Services Reference for more information about O5L_NP.

  2. Query the DBA_USERS data dictionary view to find user accounts that have only the 10G verifier.

    SELECT USERNAME FROM DBA_USERS
WHERE ( PASSWORD_VERSIONS = '10G '
OR PASSWORD_VERSIONS = '10G HTTP ')
AND USERNAME <> 'ANONYMOUS';

  3. After logging in as an account administrator, change the passwords for these accounts so that both the 11G and 12C verifiers can be provisioned for these accounts. (Because the 10G verifier is desupported, users having only this verifier cannot perform this password-change operation themselves, and an administrative user must reset their password.)

  4. Send the new password to the users using a secure, out-of-band form of communication, and then ask the user to change the password on their own.

How Case Sensitivity Affects Password Files

The password file version and whether the password file contains accounts from previous releases affects the case sensitivity of administrative authentication.

Any password file account from a previous release that has only the 10G verifier can only perform case-insensitive administrative authentication. The 10G verifier is no longer supported as of Oracle AI Database 26ai.

After a password file has been created (using the orapwd utility), the Oracle AI Database updates it when an administrative privilege is granted to or revoked from the user, or when the password of a user who has an administrative privilege is updated.

The password file is external to the database, allowing the Oracle AI Database to authenticate administrative connections (using the AS administrative_privilege_name clause, for example, AS SYSKM) even when the database is in the CLOSED state.

When an administrative connection is attempted, the Oracle AI Database searches for the user in the password file to verify their password and to ensure that the user has been granted the requested administrative privilege. The Oracle AI Database can use the password file to authenticate an administrative connection even when the database is in the CLOSED state.

The version of the password file and the type of verifier that it contains for the administrative user affects whether the authentication of that administrative user can be done in a case-sensitive fashion.

However, password files from earlier Oracle AI Database releases will by default retain their original case-insensitive verifiers. Oracle recommends that you force case sensitivity in these older password files by migrating the password file from one format to another and changing the password of any account that has only a 10G verifier, using the following syntax:

orapwd FILE=new_pwd_file_name INPUT_FILE=old_pwd_file_name [FORMAT=12.2]

The FORMAT and FORCE options are not mandatory and can be omitted. If you omit FORMAT, then it defaults to 12.2. If the FILE and INPUT_FILE options are set to the same file, then the FORCE option would be required.

For example:

orapwd FILE='/u01/oracle/dbs/old_pwd_file_name' INPUT_FILE='/u01/oracle/dbs/new_pwd_file_name' FORMAT=12.2 FORCE=y
Enter password for SYS: password

Assuming that the user accounts in the password file have the newer verifiers (11G and 12C), this command creates a case-sensitive password file called new_pwd_file_name that will authenticate administrative connections in a case-sensitive fashion. If any user account in the password file uses only the older 10G verifier, then the password of this account must be changed to enable case-sensitive authentication of administrative connections to that account. Afterward, if you connect using this password, it succeeds-as long as you enter it using the exact case in which it was created. If you enter the same password but with a different case, then the authentication attempt that uses the password fails.

If you imported user accounts from a previous release and these accounts were created with SYSDBA or SYSOPER administrative privilege, then they will be included in the password file. The passwords for these accounts are case insensitive. The next time these users change their passwords, the passwords become case sensitive. For greater security, have these users change their passwords. You can use the ALTER USER PASSWORD EXPIRE statement to expire a user’s password. Afterward, ask the user log in again, so that the user will be prompted to change their password.

Related Topics

How Case Sensitivity Affects Passwords Used in Database Link Connections

When you create a database link connection, you must define a user name and password for the connection.

When you create the database link connection, the password is case sensitive. How a user enters their password for the database link depends on the release to which the database link was created:

You can find the user accounts for existing database links by querying the V$DBLINK view. For example:

SELECT DB_LINK, OWNER_ID FROM V$DBLINK;

Related Topics