How do I change the Authentication Plugin for users mysql.sys, mysql.session, etc?

Question

I use MySQL version 8.0.34 on a Debian server. With the latest update I get the message that mysql_native_password is deprecated and I should rather use caching_sha2_password instead. With my own users this is no problem, here I can simply change the authentication plugin.

ALTER USER user
IDENTIFIED WITH caching_sha2_password
BY 'password';

But what about the integrated users mysql.sys, mysql.session and debian-sys-maint? I did not create these, they are not intended for a user login and I accordingly do not have a password for them. How can I change to caching_sha2_password here?

ALTER USER user
IDENTIFIED WITH caching_sha2_password;

Omitting the password in the SQL statement only results in errors.

Thanks

Answer 1

Most likely you've found this error message in your logs:

[MY-013360] [Server] Plugin mysql_native_password reported: 
''mysql_native_password' is deprecated and will be removed in a future release. 
Please use caching_sha2_password instead'

If you've changed the auth method for your existing users, then the message should have been already gone (built-in users are not reported for using the old method).

The method for built-in users - mysql.session, mysql.sys or mysql.infoschema - will be changed by the installer when the feature is finally removed. You shouldn't change the password of these users.

If you want, you can easily update the auth method for debian-sys-maint however:

• Find the password in /etc/mysql/debian.cnf (look for password = ...).
• Then use ALTER USER 'debian-sys-maint'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'your-password-here';

(It may also be handled by MySQL installer later too, but as the password is already known, there's no risk to do it manually).