Bug #67449 Add support for ALTER USER ... IDENTIFIED WITH <new plugin>
Submitted: 1 Nov 2012 19:30 Modified: 10 Apr 2015 21:19
Reporter: Todd Farmer (OCA) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Security: Privileges Severity:S4 (Feature request)
Version:5.6.7 OS:Any
Assigned to: CPU Architecture:Any

[1 Nov 2012 19:30] Todd Farmer
MySQL 5.6 adds support for the ALTER USER command.  This could theoretically be leveraged to support changing authentication plugins for an existing user account, whereas the current implementation requires either direct manipulation of the mysql.user table, or for the user to be dropped and recreated with the new authentication plugin.

How to repeat:
Try to change authentication plugin without either:

1.  directly manipulating mysql.user table
2.  dropping/recreating the user account

Suggested fix:
Add support to change the authentication plugin with ALTER USER.
[6 Sep 2014 11:18] Daniƫl van Eeden
Related: Bug #29287

To change the root user to auth_socket authentication the easy way:
update mysql.user set password='', plugin='auth_socket' where user='root' and host='localhost';
flush privileges;

The more official way:
grant all on *.* to 'tempuser'@'localhost' identified by 'my_rnd_pwd' with grant option;
connect as the new user;
drop user 'root'@'localhost';
set session sql_mode='NO_AUTO_CREATE_USER'; -- just to be sure
create user 'root'@'localhost' identified with auth_socket;
grant all on *.* to 'root'@'localhost' with grant option;
connect as the new root user;
drop user 'tempuser'@'localhost';
[10 Apr 2015 21:19] Todd Farmer
Posted by developer:
This use case is supported by new ALTER USER syntax introduced in 5.7.6.