Bug #109229 older mysql client report different auth error msg info when connect to 8.0.22

• Submitted: 29 Nov 2022 8:48
• Modified: 30 Nov 2022 1:18
• Reporter: yuxiang jiang (OCA)
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[29 Nov 2022 8:48] yuxiang jiang

Description:
When connecting to instance with non-exist user, the client report different error message.

The reason is function decoy_user create a user with random auth plugin. If the plugin is 'caching_sha2_password' and the client is mysql 5.6 or more lower version, client can't find the plugin so. So they will report plugin 'sha256_password' cannot be loaded. If random to mysql_native_password, client report 'Access denied for user 'root31'@'localhost'' 

How to repeat:
make sure the lower version
jiangyx@cloud /d/r/t/normal-work> ./bin/mysql --version
./bin/mysql  Ver 14.14 Distrib 5.6.28, for Linux (x86_64) using  EditLine wrapper

repeat use non-exist user login
jiangyx@cloud /d/r/t/normal-work> ./bin/mysql -uroot31 -S /data2/run/txsql8/normal-work/mysql.sock
ERROR 1045 (28000): Access denied for user 'root31'@'localhost' (using password: NO)
jiangyx@cloud /d/r/t/normal-work> ./bin/mysql -uroot32 -S /data2/run/txsql8/normal-work/mysql.sock
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /data2/run/txsql5.6/normal-work/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

Suggested fix:
use mysql_native_password for default to compatible with previous version.

[29 Nov 2022 13:01] MySQL Verification Team

Hi Mr. jiang,

Thank you for your bug report.

However, we do not upgrade our clients from the obsolete versions to have the same features as the 8.0 clients. 

This is why our Reference Manual has so many references on the compatibility of features with older clients. Particularly with 5.6 or earlier, that are NOT maintained for a long time.

Not a bug.

[30 Nov 2022 1:18] yuxiang jiang

I think login with non-exist user should report the same error.

Check the code below. Here we use random plugin for non-exist user. So when client has no caching_sha2_password plugin, client report error.  This random action cause ambiguous auth report.

    if (unknown_accounts->find(key, value)) {
      user->plugin = Cached_authentication_plugins::cached_plugins_names[value];
    } else {
      const int DECIMAL_SHIFT = 1000;
      const int random_number = static_cast<int>(my_rnd(rand) * DECIMAL_SHIFT);
      uint plugin_num = (uint)(random_number % ((uint)PLUGIN_LAST));
      user->plugin =
          Cached_authentication_plugins::cached_plugins_names[plugin_num];
      unknown_accounts->clear_if_greater(MAX_UNKNOWN_ACCOUNTS);