Spy on Oracle's password_versions from a single case

1. Environmental description

ORACLE Client Version	11.2.0.1
ORACLE Server-side version	12.2.0.1

2. Abnormal phenomenon

The client (hereafter also known as CP) accesses the server side (Sp) and reported an error:

Figure 1

With the error code for the keyword in the online search for reasons, there are users suggest to the server Sqlnet.ora file in the Sqlnet.allowed_logon_version_server parameters and sqlnet. Change the allowed_logon_version_client parameter to 8 (the original value is 12). As shown in the following:

Figure 2

Reconnect after you've changed, bad, a new error has occurred:

Figure 3

But I have no problem connecting to the SP from other clients (which later found these client versions are higher than 11.2.0.1). I thought there was something going on. The daemon will force all lowercase input into uppercase, so I change the account password from SYS to SYS and then connect:

Figure 4

It worked. From the perspective of the phenomenon, it seems that the relationship between the case is really. But really, yes? I will now change the password from sys back to SYS and then connect:

Figure 5

This time with the SYS as password landing, also succeeded. It looks like the real reason, not what the daemon casts the case. What is the reason for that?

3. Explanation of principle

In fact, in terms of the phenomenon, the change was initially set

Sqlnet. Allowed_logon_version_client

and sqlnet.allowed_logon_version_server two parameters. So what do these two parameters really do?

Let's take a look at Sqlnet.allowed_logon_version_client's description in the official documentation:

Figure 6

This parameter is the newly introduced parameter of 12C. It represents the minimum version of the authentication protocol that CP uses when it sends an authentication (authentication) request to the SP. Note that the authentication protocol version here is not equivalent to the version of Oracle database. What are the main differences between the different versions of the authentication protocol? Apart from the semantics of the Protocol, the most important difference in my opinion is that the versions of the different authentication protocols correspond to different database version, and the different database version may use different hash algorithms to encrypt the password. Different hash algorithms are different password_version, which can be explained in the Password_versions field of the Dba_users dictionary table:

Figure 7

When Oracle stores the password for each account, it is not the plaintext store, but the plaintext is hashed and stored, and the hash encryption algorithm is the version of the password, which is password_version. As shown in Figure 7, Password_version actually represents the hash algorithm provided by database in the same version (not exactly the same as the last schedule), for example, Password_version 10g represents the database The hash algorithm provided by 10g. If all new versions of Oracle only use the new version of the hash algorithm, then some of the earlier clients because there is no such hash algorithm, the hash algorithm can not get the hash value, it is impossible for the server to verify the hash value. To solve the compatibility problem, Oracle uses multiple hash algorithms (that is, password_version) to operate on the password, preserving the results of multiple operations. When the lower version of the client accesses the high version of the server, the lower version of the client can communicate the authentication protocol used by the Protocol and the hash algorithm corresponding to the password hash value, the server according to the authentication protocol to see if the hash algorithm is stored the hash value of the protocol, if it exists, than the two hash value is consistent, if not present or two hash value inconsistent, error. The flowchart is roughly as follows (for your own understanding only):

In the above password Authentication flowchart, the red sub-process-"Judging whether the account Password_versions contains client version", there is a question: What is the password version, what is the decision? This leads to another parameter: Sqlnet.allowed_logon_version_server.

On the effect of this parameter, it describes the effect of setting different values for this parameter, mainly on Password_versions. Finally comes with a table, detailing the Sqlnet.allowed_logon_version_server set different values, the corresponding password_versions. It also shows that if you want to authenticate with a 12C database that is set to the current value, the supported client version. As you can see, even though the sqlnet.allowed_logon_version_server is set to 8, the lowest version of password versions generated is also 10G. As a result, it is also explained that the 8i,9i client cannot complete the login authentication because it cannot understand the 10G hash function. The following table is about the compatibility of client-to-server interactions:

4. Repeat the case

1. When sqlnet.allowed_logon_version_serve=12, a password was set for the SYS user, so the SYS account's password_versions=11g,12c

2. The client version 11.2.0.1 is then requested to log in because the client version The authentication protocol version of 11.2.0.1 is 11 (this version is less than 11.2.0.3, and the CPUOct2012 patch is not hit because the authentication protocol version is 11), so the process is shown in red Line 1:

3. Then manually change the Sqlnet.allowed_logon_version_serve to 8, but because only the parameter changes, and did not rebuild the password, so the actual password of the account is still 11g,12c, so the request to log in the process as shown in red Line 2:

4. Regenerate the password. Because the password is generated in the case of sqlnet.allowed_logon_version_serve=8, the account corresponds to the password_versions=10g,11g,12c, as shown in:

   The password_versions is supported by the client version 11.2.0.1 password authentication, so the process of requesting a login is shown in red Line 3:

5, the relationship between the Certification Agreement version, the database version and the password version

As the following table:

Spy on Oracle's password_versions from a single case