Bug #115675 Enable mysql_no_login authentication plugin
Submitted: 23 Jul 2024 23:18 Modified: 24 Jul 2024 7:00
Reporter: Mike Griffin Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S4 (Feature request)
Version:8.0.39 OS:Any
Assigned to: CPU Architecture:Any
Tags: mysql security, mysql_secure_installation

[23 Jul 2024 23:18] Mike Griffin 
Description:
This plugin would be useful in the ways described in the manual, ie "Accounts that must be able to execute stored programs and views with elevated privileges without exposing those privileges to ordinary users"

I think it would also be less confusing to use this plugin for default accounts mysql.infoschema, mysql.session, and mysql.sys.

Currently these accounts ship with a hopefully impossible to use authentication_string via caching_sha2_password

How to repeat:
select user, host, plugin, authentication_string from mysql.user
where authentication_string = '$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED';

Suggested fix:
Make this plugin available by default and possibly backport this via "mysql_secure_installation -D":

INSTALL PLUGIN mysql_no_login SONAME 'mysql_no_login.so';
ALTER USER `mysql.infoschema`@`localhost` IDENTIFIED WITH mysql_no_login;
ALTER USER `mysql.session`@`localhost` IDENTIFIED WITH mysql_no_login;
ALTER USER `mysql.sys`@`localhost` IDENTIFIED WITH mysql_no_login;

Ideally @mysql_mandatory_plugins@ would have builtin_mysql_no_login
[23 Jul 2024 23:21] Mike Griffin 
The mysql_secure_installation backport idea would work very similarly to install_password_validation_component()
[24 Jul 2024 7:00] MySQL Verification Team 
Hello Mike Griffin,

Thank you for the  feature request!!

regards,
Umesh