Use these procedures to identify if the Oracle Database that you want to upgrade has accounts or configuration parameters that are using a case-insensitive password version.
By default, in Oracle Database 12c release 2 (12.2) and later releases, the
10G password version is not generated or allowed.
If you do not set
SQLNET.ALLOWED_LOGON_VERSION_SERVER to a permissive authentication protocol that permits case-insensitive versions, and you do not want user accounts authenticated with case-insensitive password versions to be locked out of the database, then you must identify affected accounts, and ensure that they are using case-sensitive password versions.
Example 2-1 Finding User Accounts That Use Case-Insensitive (10G) Version
Log in to SQL*Plus as an administrative user, and enter the following SQL query:
SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;
The following result shows password versions for the accounts:
USERNAME PASSWORD_VERSIONS ------------------------------ ----------------- JONES 10G 11G 12C ADAMS 10G 11G CLARK 10G 11G PRESTON 11G BLAKE 10G
In this example, the backgrounds for each user account password verification version in use are different:
JONESwas created in Oracle Database
10G, and the password for
JONESwas reset in Oracle Database
12Cwhen the setting for the
SQLNET.ALLOWED_LOGON_VERSION_SERVERparameter was set to
8. As a result, this password reset created all three versions.
12Cuse case-sensitive passwords.
CLARKwere originally created with the
10Gversion, and then
11G, after they were imported from an earlier release. These account passwords were then reset in
11G, with the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to TRUE.
The password for
BLAKEwas created with the
10Gversion, and the password has not been reset. As a result, user BLAKE continues to use the
10Gpassword version, which uses a case-insensitive password.
BLAKE has only the
10G password version before upgrade:
SQL> SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS; USERNAME PASSWORD_VERSIONS ------------------------------ ----------------- BLAKE 10G
If you upgrade to a new Oracle Database release without taking any further action, then this account becomes inaccessible. Ensure that the system is not configured in Exclusive Mode (by setting the
SQLNET.ALLOWED_LOGON_VERSION_SERVER to a more permissive authentication mode) before the upgrade.
Example 2-2 Fixing Accounts with Case-Insensitive Passwords
Complete the following procedure:
Use the following SQL query to find the accounts that only have the
select USERNAME from DBA_USERS where ( PASSWORD_VERSIONS = '10G ' or PASSWORD_VERSIONS = '10G HTTP ') and USERNAME <> 'ANONYMOUS';
Configure the system so that it is not running in Exclusive Mode by editing the setting of the
SQLNET.ALLOWED_LOGON_VERSION_SERVERto a level appropriate for affected accounts. For example:
After you make this change, proceed with the upgrade.
After the upgrade completes, use the following command syntax to expire the accounts you found in step 1, where
usernameis the name of a user returned from the query in step 1:
ALTER USER username PASSWORD EXPIRE;
Ask the users for whom you have expired the passwords to log in.
When these users log in, they are prompted to reset their passwords. The system internally generates the missing
12Cpassword versions for their account, in addition to the
10Gpassword version. The
10Gpassword version is still present, because the system is running in the permissive mode.
Ensure that the client software with which users are connecting has the
All Oracle Database release 220.127.116.11 and later clients, and all Oracle Database release 12.1 and later clients have the
O5L_NPcapability. Other clients require the
CPUOct2012patch to acquire the
O5L_NPcapability flag is documented in Oracle Database Net Services Reference, in the section on the parameter
After all clients have the
O5L_NPcapability, raise the server security back to Exclusive Mode by using the following procedure:
SEC_CASE_SENSITIVE_LOGONsetting from the instance initialization file, or set the
SEC_CASE_SENSITIVE_LOGONinstance initialization parameter to
TRUE. For example:
SEC_CASE_SENSITIVE_LOGON = TRUE
SQLNET.ALLOWED_LOGON_VERSION_SERVERparameter from the server
SQLNET.ORAfile, or set it back to Exclusive Mode by changing the value of
SQLNET.ALLOWED_LOGON_VERSION_SERVERin the server
SQLNET.ORAfile back to
12. For example:
SQLNET.ALLOWED_LOGON_VERSION_SERVER = 12
Use the following SQL query to find the accounts that still have the
select USERNAME from DBA_USERS where PASSWORD_VERSIONS like '%10G%' and USERNAME <> 'ANONYMOUS';
Use the list of accounts returned from the query in step 8 to expire all the accounts that still have the
10Gpassword version. Expire the accounts using the following syntax, where
usernameis a name on the list returned by the query:
ALTER USER username PASSWORD EXPIRE;
Request the users whose accounts you expired to log in to their accounts.
When the users log in, they are prompted to reset their password. The system internally generates only the
12Cpassword versions for their account. Because the system is running in Exclusive Mode, the
10Gpassword version is no longer generated.
Check that the system is running in a secure mode by rerunning the query from step 1. Ensure that no users are found. When the query finds no users, this result means that no
10Gpassword version remains present in the system.
Example 2-3 Checking for the Presence of SEC_CASE_SENSITIVE_LOGON Set to FALSE
Oracle Database does not prevent the use of the
FALSE setting for
SEC_CASE_SENSITIVE_LOGON when the
SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter is set to
12a. This setting can result in all accounts in the upgraded database becoming inaccessible.
SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sec_case_sensitive_logon boolean FALSE
SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE; System altered.
Unless the value for the parameter
SQLNET.ALLOWED_LOGON_VERSION_SERVER is changed to a version that is more permissive than
12, such as
11, do not set the
SEC_CASE_SENSITIVE_LOGON parameter to FALSE.