Bug #109973 Contribution: Fix for errors: Plugin sha256_password reported
Submitted: 7 Feb 2023 20:58 Modified: 8 Feb 2023 5:54
Reporter: OCA Admin (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Connection Handling Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[7 Feb 2023 20:58] OCA Admin
Description:
This bug tracks a contribution by xichao li (Github user: lxc-19092808) as described in http://github.com/mysql/mysql-server/pull/439

How to repeat:
See description

Suggested fix:
See contribution code attached
[7 Feb 2023 20:58] OCA Admin
Contribution submitted via Github - Fix for errors: Plugin sha256_password reported 
(*) Contribution by xichao li (Github lxc-19092808, mysql-server/pull/439#issuecomment-1420923629): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_1197652927.txt (text/plain), 2.94 KiB.

[8 Feb 2023 5:54] MySQL Verification Team
Hello xichao li,

Thank you for the report and contribution.

regards,
Umesh
[14 Feb 2023 9:02] Georgi Kodinov
Posted by developer:
 
I understand the inconvenience caused, but I do not think we should limit the choice of plugins used for the non-existent user.
The whole point of the decoy user is to make it harder for an attacker to guess if a user account exists or not. If there was no decoy user the server would just disconnect in mid-authentication for non-existent users and thus would make it possible to distinguish non-existent users from existing users with bad credentials.
If we turn off the sha256 plugin as you are suggesting this would mean that if by any chance the server says "use sha256" to the client this is a very clear sign that this is an existing user account. Hence we need to make the decoy user's authentication methods as random as possible. 

IMHO you need to review your security model and think long and hard if you want to expose the MySQL database port to non-trusted connections. I would suggest considering a VPN or a SSH tunnel instead. 

If you can't do any of that I believe that your best workaround is to turn off the deprecated message by filtering it out: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_log_error_supp...
[14 Feb 2023 9:50] xichao li
I roughly understand what you mean, avoiding the use of sha256 plugin will reduce the original 3 select 1 to 2 select 1.
But I don't quite understand:
"If we turn off the sha256 plugin as you are suggesting this would mean that if by any chance the server says "use sha256" to the client this is a very clear sign that this is an existing user account "SHA256" To the client this is a very clear sign that this is an existing user account"
Now that you've chosen not to use it, based on the code and debug results, you should be able to select CACHING_SHA2 / MYSQL_NATIVE, which is actually random.

But overall, it's really a decision:
Accepting changes will, as you say, reduce randomness (but whether it will reduce security I don't know yet)
Failure to accept the change will result in:
1. When non-existent users access the database, similar logs are stored in the database logs. This log, in my experience, can lead to confusion among users (who may think that an existing user is actually using the wrong plugin) and can waste a lot of time trying to analyze it in other ways.
2. Since the current algorithm, such as one that does not exist, is selected to use sha256, it will always use this plug-in. This alarm is generated for each subsequent access. There could be a problem with disk space being overconsumed (we actually found the problem after the disk was overconsumed).