Description:
Since `mysql_native_password` is deprecated and will be removed from future releases of MySQL, we need to discourage users from creating users with this authentication plugin.
However, when attempting to create a user with `mysql_native_password` on MySQL 8.4, no warning is displayed. We observe similar behavior with other deprecated plugins, such as `sha256_password`.
The lack of a warning or advisory message during user creation with deprecated authentication plugins can lead users to inadvertently create accounts using insecure or soon-to-be-unsupported methods, potentially introducing security risks or compatibility issues in the future.
As a follow-up task item, we can also display a warning message when a user created with the `mysql_native_password` authentication plugin is authenticated using a MySQL client. This warning would inform the user that the authentication method used for their account is deprecated and will be removed in future releases of MySQL, encouraging them to migrate to a more secure and supported authentication plugin.
How to repeat:
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.4.0 |
+-----------+
1 row in set (0.00 sec)
mysql> create user user1@'%' identified with 'mysql_native_password' by '<password>';
Query OK, 0 rows affected (0.01 sec)
mysql> show warnings;
Empty set (0.00 sec)
mysql> select plugin from mysql.user where user = 'user1';
+-----------------------+
| plugin |
+-----------------------+
| mysql_native_password |
+-----------------------+
1 row in set (0.00 sec)
mysql> create user user2@'%' identified with 'sha256_password' by '<password>';
Query OK, 0 rows affected (0.01 sec)
mysql> show warnings;
Empty set (0.00 sec)
Suggested fix:
We should display warning similar to what we get for other deprecated features. For example:
mysql> show warnings \G
*************************** 1. row ***************************
Level: Warning
Code: xxxx
Message: Authentication plugin 'mysql_native_password' is deprecated and will be removed in a future release. Please use 'caching_sha2_password' or an alternative secure authentication plugin instead.
1 row in set (0.00 sec)
