MySQL Bugs: #67449: Add support for ALTER USER ... IDENTIFIED WITH <new plugin>

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:	None
Category:	MySQL Server: Security: Privileges	Severity:	S4 (Feature request)
Version:	5.6.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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.

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';

Posted by developer:
 
This use case is supported by new ALTER USER syntax introduced in 5.7.6.