MDEV-12321 authentication plugin: SET PASSWORD support

Support SET PASSWORD for authentication plugins.

Authentication plugin API is extended with two optional methods:
* hash_password() is used to compute a password hash (or digest)
  from the plain-text password. This digest will be stored in mysql.user
  table
* preprocess_hash() is used to convert this digest into some memory
  representation that can be later used to authenticate a user.
  Build-in plugins convert the hash from hexadecimal or base64 to binary,
  to avoid doing it on every authentication attempt.

Note a change in behavior: when loading privileges (on startup or on
FLUSH PRIVILEGES) an account with an unknown plugin was loaded with a
warning (e.g. "Plugin 'foo' is not loaded"). But such an account could
not be used for authentication until the plugin is installed. Now an
account like that will not be loaded at all (with a warning, still).
Indeed, without plugin's preprocess_hash() method the server cannot know
how to load an account. Thus, if a new authentication plugin is
installed run-time, one might need FLUSH PRIVILEGES to activate all
existing accounts that were using this new plugin.

15 files changed, 199 insertions, 43 deletions

diff --git a/mysql-test/main/alter_user.result b/mysql-test/main/alter_user.result
index ccc4f60a1bb..68720b6d6c7 100644
--- a/mysql-test/main/alter_user.result
+++ b/mysql-test/main/alter_user.result
@@ -7,11 +7,11 @@ localhost	root		Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y
 alter user CURRENT_USER;
 select * from mysql.user where user = 'root' and host = 'localhost';
 Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	Create_tablespace_priv	Delete_history_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections	plugin	authentication_string	password_expired	is_role	default_role	max_statement_time
-localhost	root		Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y					0	0	0	0			N	N		0.000000
+localhost	root		Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y					0	0	0	0	mysql_native_password		N	N		0.000000
 alter user CURRENT_USER();
 select * from mysql.user where user = 'root' and host = 'localhost';
 Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	Create_tablespace_priv	Delete_history_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections	plugin	authentication_string	password_expired	is_role	default_role	max_statement_time
-localhost	root		Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y					0	0	0	0			N	N		0.000000
+localhost	root		Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y	Y					0	0	0	0	mysql_native_password		N	N		0.000000
 create user foo;
 select * from mysql.user where user = 'foo';
 Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	Create_tablespace_priv	Delete_history_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections	plugin	authentication_string	password_expired	is_role	default_role	max_statement_time
@@ -61,13 +61,19 @@ select * from mysql.user where user = 'foo';
 Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	Create_tablespace_priv	Delete_history_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections	plugin	authentication_string	password_expired	is_role	default_role	max_statement_time
 %	foo		N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	Y	N	N	N	N	N	N	N	N	N	Y	N	N	N	N					0	0	0	0	mysql_native_password	*88C89BE093D4ECF72D039F62EBB7477EA1FD4D63	N	N		0.000000
 alter user foo identified with 'somecoolplugin';
+ERROR HY000: Operation ALTER USER failed for 'foo'@'%'
+show warnings;
+Level	Code	Message
+Warning	1524	Plugin 'somecoolplugin' is not loaded
+Error	1396	Operation ALTER USER failed for 'foo'@'%'
+alter user foo identified with 'mysql_old_password';
 select * from mysql.user where user = 'foo';
 Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	Create_tablespace_priv	Delete_history_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections	plugin	authentication_string	password_expired	is_role	default_role	max_statement_time
-%	foo		N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	Y	N	N	N	N	N	N	N	N	N	Y	N	N	N	N					0	0	0	0	somecoolplugin		N	N		0.000000
-alter user foo identified with 'somecoolplugin' using 'somecoolpassphrase';
+%	foo		N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	Y	N	N	N	N	N	N	N	N	N	Y	N	N	N	N					0	0	0	0	mysql_old_password		N	N		0.000000
+alter user foo identified with 'mysql_old_password' using '0123456789ABCDEF';
 select * from mysql.user where user = 'foo';
 Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	Create_tablespace_priv	Delete_history_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections	plugin	authentication_string	password_expired	is_role	default_role	max_statement_time
-%	foo		N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	Y	N	N	N	N	N	N	N	N	N	Y	N	N	N	N					0	0	0	0	somecoolplugin	somecoolpassphrase	N	N		0.000000
+%	foo		N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	Y	N	N	N	N	N	N	N	N	N	Y	N	N	N	N					0	0	0	0	mysql_old_password	0123456789ABCDEF	N	N		0.000000
 # Test ssl related altering.
 alter user foo identified by 'something' require SSL;
 select * from mysql.user where user = 'foo';
@@ -91,3 +97,4 @@ select * from mysql.user where user = 'foo';
 Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	Create_tablespace_priv	Delete_history_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections	plugin	authentication_string	password_expired	is_role	default_role	max_statement_time
 %	foo		N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	Y	N	N	N	N	N	N	N	N	N	Y	N	N	N	N	SPECIFIED	text	foo_issuer	foo_subject	10	20	30	40	mysql_native_password	*88C89BE093D4ECF72D039F62EBB7477EA1FD4D63	N	N		0.000000
 drop user foo;
+update mysql.user set plugin='';
diff --git a/mysql-test/main/alter_user.test b/mysql-test/main/alter_user.test
index ca444f70a70..ef20f79a554 100644
--- a/mysql-test/main/alter_user.test
+++ b/mysql-test/main/alter_user.test
@@ -53,10 +53,14 @@ select * from mysql.user where user = 'foo';
 alter user foo identified by password '*88C89BE093D4ECF72D039F62EBB7477EA1FD4D63';
 select * from mysql.user where user = 'foo';
 
+--error ER_CANNOT_USER
 alter user foo identified with 'somecoolplugin';
+show warnings;
+
+alter user foo identified with 'mysql_old_password';
 select * from mysql.user where user = 'foo';
 
-alter user foo identified with 'somecoolplugin' using 'somecoolpassphrase';
+alter user foo identified with 'mysql_old_password' using '0123456789ABCDEF';
 select * from mysql.user where user = 'foo';
 
 --echo # Test ssl related altering.
@@ -77,3 +81,5 @@ alter user foo with MAX_QUERIES_PER_HOUR 10
                     MAX_USER_CONNECTIONS 40;
 select * from mysql.user where user = 'foo';
 drop user foo;
+
+update mysql.user set plugin='';
diff --git a/mysql-test/main/failed_auth_3909.result b/mysql-test/main/failed_auth_3909.result
index d0fd2c41221..4c3c0aba9df 100644
--- a/mysql-test/main/failed_auth_3909.result
+++ b/mysql-test/main/failed_auth_3909.result
@@ -1,7 +1,7 @@
 optimize table mysql.user;
 Table	Op	Msg_type	Msg_text
 mysql.user	optimize	status	OK
-insert ignore mysql.user (user,plugin) values ('foo','bar'),('bar','bar'),('baz','bar');
+insert ignore mysql.user (user,plugin) values ('foo','mysql_old_password'),('bar','mysql_old_password'),('baz','mysql_old_password');
 Warnings:
 Warning	1364	Field 'ssl_cipher' doesn't have a default value
 Warning	1364	Field 'x509_issuer' doesn't have a default value
@@ -10,15 +10,15 @@ Warning	1364	Field 'authentication_string' doesn't have a default value
 flush privileges;
 connect(localhost,u1,,test,MASTER_PORT,MASTER_SOCKET);
 connect  fail,localhost,u1;
-ERROR HY000: Plugin 'bar' is not loaded
+ERROR HY000: Server is running in --secure-auth mode, but 'u1'@'localhost' has a password in the old format; please change the password to the new format
 connect(localhost,u2,,test,MASTER_PORT,MASTER_SOCKET);
 connect  fail,localhost,u2;
 ERROR 28000: Access denied for user 'u2'@'localhost' (using password: NO)
 connect(localhost,u2,password,test,MASTER_PORT,MASTER_SOCKET);
 connect  fail,localhost,u2,password;
 ERROR 28000: Access denied for user 'u2'@'localhost' (using password: YES)
-ERROR HY000: Plugin 'bar' is not loaded
+ERROR HY000: Server is running in --secure-auth mode, but 'u1'@'localhost' has a password in the old format; please change the password to the new format
 ERROR 28000: Access denied for user 'u2'@'localhost' (using password: NO)
 ERROR 28000: Access denied for user 'u2'@'localhost' (using password: YES)
-delete from mysql.user where plugin = 'bar';
+delete from mysql.user where plugin = 'mysql_old_password';
 flush privileges;
diff --git a/mysql-test/main/failed_auth_3909.test b/mysql-test/main/failed_auth_3909.test
index f72460691ea..fb104cf4b81 100644
--- a/mysql-test/main/failed_auth_3909.test
+++ b/mysql-test/main/failed_auth_3909.test
@@ -7,11 +7,11 @@ source include/not_embedded.inc;
 # the server requests a plugin
 #
 optimize table mysql.user;
-insert ignore mysql.user (user,plugin) values ('foo','bar'),('bar','bar'),('baz','bar');
+insert ignore mysql.user (user,plugin) values ('foo','mysql_old_password'),('bar','mysql_old_password'),('baz','mysql_old_password');
 flush privileges;
 
 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
---error ER_PLUGIN_IS_NOT_LOADED
+--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
 connect (fail,localhost,u1);
 
 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
@@ -22,7 +22,7 @@ connect (fail,localhost,u2);
 --error ER_ACCESS_DENIED_ERROR
 connect (fail,localhost,u2,password);
 
---error ER_PLUGIN_IS_NOT_LOADED
+--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
 change_user u1;
 
 --error ER_ACCESS_DENIED_ERROR
@@ -31,7 +31,7 @@ change_user u2;
 --error ER_ACCESS_DENIED_ERROR
 change_user u2,password;
 
-delete from mysql.user where plugin = 'bar';
+delete from mysql.user where plugin = 'mysql_old_password';
 flush privileges;
 
 
diff --git a/mysql-test/main/failed_auth_unixsocket.result b/mysql-test/main/failed_auth_unixsocket.result
index 680d3b48a33..3b2cff7e845 100644
--- a/mysql-test/main/failed_auth_unixsocket.result
+++ b/mysql-test/main/failed_auth_unixsocket.result
@@ -1,10 +1,15 @@
 update mysql.user set plugin='unix_socket';
 flush privileges;
+Warnings:
+Warning	1524	Plugin 'unix_socket' is not loaded
+Warning	1524	Plugin 'unix_socket' is not loaded
+Warning	1524	Plugin 'unix_socket' is not loaded
+Warning	1524	Plugin 'unix_socket' is not loaded
 connect(localhost,USER,,test,MASTER_PORT,MASTER_SOCKET);
-connect  fail,localhost,$USER;
-ERROR HY000: Plugin 'unix_socket' is not loaded
-ERROR HY000: Plugin 'unix_socket' is not loaded
+ERROR 28000: Access denied for user 'USER'@'localhost' (using password: NO)
+ERROR 28000: Access denied for user 'USER'@'localhost' (using password: NO)
 install plugin unix_socket soname 'auth_socket.so';
+flush privileges;
 connect(localhost,USER,,test,MASTER_PORT,MASTER_SOCKET);
 ERROR 28000: Access denied for user 'USER'@'localhost'
 ERROR 28000: Access denied for user 'USER'@'localhost'
diff --git a/mysql-test/main/failed_auth_unixsocket.test b/mysql-test/main/failed_auth_unixsocket.test
index f7345f44698..5dfd9585882 100644
--- a/mysql-test/main/failed_auth_unixsocket.test
+++ b/mysql-test/main/failed_auth_unixsocket.test
@@ -7,20 +7,26 @@
 update mysql.user set plugin='unix_socket';
 flush privileges;
 
---replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT $USER USER
---error ER_PLUGIN_IS_NOT_LOADED
+# Make sure that the replace works, even if $USER is 'user' or something else
+# that matches other parts of the error message.
+let $replace=Access denied for user '$USER';
+
+--echo connect(localhost,USER,,test,MASTER_PORT,MASTER_SOCKET);
+--replace_result $replace "Access denied for user 'USER'"
+--disable_query_log
+--error ER_ACCESS_DENIED_ERROR
 connect (fail,localhost,$USER);
+--enable_query_log
 
---error ER_PLUGIN_IS_NOT_LOADED
+--replace_result $replace "Access denied for user 'USER'"
+--error ER_ACCESS_DENIED_ERROR
 change_user $USER;
 
 eval install plugin unix_socket soname '$AUTH_SOCKET_SO';
+flush privileges;
 
-# Make sure that the replace works, even if $USER is 'user' or something else
-# that matches other parts of the error message.
 --echo connect(localhost,USER,,test,MASTER_PORT,MASTER_SOCKET);
---let $replace=Access denied for user '$USER'
---replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT $replace "Access denied for user 'USER'"
+--replace_result $replace "Access denied for user 'USER'"
 --disable_query_log
 --error ER_ACCESS_DENIED_NO_PASSWORD_ERROR
 connect (fail,localhost,$USER);
diff --git a/mysql-test/main/grant2.result b/mysql-test/main/grant2.result
index 7f9af44b7e0..4fd1a6c3fe7 100644
--- a/mysql-test/main/grant2.result
+++ b/mysql-test/main/grant2.result
@@ -565,7 +565,7 @@ root	localhost				N
 GRANT INSERT ON *.* TO CURRENT_USER();
 SELECT user,host,password,plugin,authentication_string,insert_priv FROM user WHERE user=@u AND host=@h;
 user	host	password	plugin	authentication_string	insert_priv
-root	localhost				Y
+root	localhost		mysql_native_password		Y
 UPDATE user SET insert_priv='N' WHERE user=@u AND host=@h;
 GRANT INSERT ON *.* TO CURRENT_USER() IDENTIFIED BY 'keksdose';
 SELECT user,host,password,plugin,authentication_string,insert_priv FROM user WHERE user=@u AND host=@h;
diff --git a/mysql-test/main/grant5.result b/mysql-test/main/grant5.result
index 24abc61a348..af7b75277b8 100644
--- a/mysql-test/main/grant5.result
+++ b/mysql-test/main/grant5.result
@@ -18,3 +18,84 @@ ERROR 42000: Access denied for user 'test'@'%' to database 'mysql'
 connection default;
 drop user test, foo;
 drop role foo;
+create user u1@h identified with 'mysql_native_password' using 'pwd';
+ERROR HY000: Password hash should be a 41-digit hexadecimal number
+create user u1@h identified with 'mysql_native_password' using password('pwd');
+create user u2@h identified with 'mysql_native_password' using '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD';
+create user u3@h identified with 'mysql_native_password';
+set password for u3@h = 'pwd';
+ERROR HY000: Password hash should be a 41-digit hexadecimal number
+set password for u3@h = password('pwd');
+create user u4@h identified with 'mysql_native_password';
+set password for u4@h = '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD';
+create user u5@h identified with 'mysql_old_password' using 'pwd';
+ERROR HY000: Password hash should be a 16-digit hexadecimal number
+create user u5@h identified with 'mysql_old_password' using password('pwd');
+create user u6@h identified with 'mysql_old_password' using '78a302dd267f6044';
+create user u7@h identified with 'mysql_old_password';
+set password for u7@h = 'pwd';
+ERROR HY000: Password hash should be a 41-digit hexadecimal number
+set password for u7@h = old_password('pwd');
+create user u8@h identified with 'mysql_old_password';
+set password for u8@h = '78a302dd267f6044';
+select user,host,password,plugin,authentication_string from mysql.user where host='h';
+user	host	password	plugin	authentication_string
+u1	h		mysql_native_password	*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD
+u2	h		mysql_native_password	*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD
+u3	h		mysql_native_password	*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD
+u4	h		mysql_native_password	*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD
+u5	h		mysql_old_password	78a302dd267f6044
+u6	h		mysql_old_password	78a302dd267f6044
+u7	h		mysql_old_password	78a302dd267f6044
+u8	h		mysql_old_password	78a302dd267f6044
+update mysql.user set authentication_string='bad' where user='u1';
+update mysql.user set authentication_string='bad' where user='u5';
+update mysql.user set plugin='nonexistent' where user='u8';
+flush privileges;
+Warnings:
+Error	1372	Password hash should be a 41-digit hexadecimal number
+Error	1372	Password hash should be a 16-digit hexadecimal number
+Warning	1524	Plugin 'nonexistent' is not loaded
+show create user u1@h;
+ERROR 28000: Can't find any matching row in the user table
+show create user u2@h;
+CREATE USER for u2@h
+CREATE USER 'u2'@'h' IDENTIFIED BY PASSWORD '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD'
+show create user u3@h;
+CREATE USER for u3@h
+CREATE USER 'u3'@'h' IDENTIFIED BY PASSWORD '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD'
+show create user u4@h;
+CREATE USER for u4@h
+CREATE USER 'u4'@'h' IDENTIFIED BY PASSWORD '*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD'
+show create user u5@h;
+ERROR 28000: Can't find any matching row in the user table
+show create user u6@h;
+CREATE USER for u6@h
+CREATE USER 'u6'@'h' IDENTIFIED BY PASSWORD '78a302dd267f6044'
+show create user u7@h;
+CREATE USER for u7@h
+CREATE USER 'u7'@'h' IDENTIFIED BY PASSWORD '78a302dd267f6044'
+show create user u8@h;
+ERROR 28000: Can't find any matching row in the user table
+grant select on *.* to u1@h;
+ERROR 28000: Can't find any matching row in the user table
+grant select on *.* to u2@h;
+grant select on *.* to u3@h;
+grant select on *.* to u4@h;
+grant select on *.* to u5@h;
+ERROR 28000: Can't find any matching row in the user table
+grant select on *.* to u6@h;
+grant select on *.* to u7@h;
+grant select on *.* to u8@h;
+ERROR 28000: Can't find any matching row in the user table
+select user,select_priv,plugin,authentication_string from mysql.user where user like 'u_';
+user	select_priv	plugin	authentication_string
+u1	N	mysql_native_password	bad
+u2	Y	mysql_native_password	*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD
+u3	Y	mysql_native_password	*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD
+u4	Y	mysql_native_password	*975B2CD4FF9AE554FE8AD33168FBFC326D2021DD
+u5	N	mysql_old_password	bad
+u6	Y	mysql_old_password	78a302dd267f6044
+u7	Y	mysql_old_password	78a302dd267f6044
+u8	N	nonexistent	78a302dd267f6044
+drop user u1@h, u2@h, u3@h, u4@h, u5@h, u6@h, u7@h, u8@h;
diff --git a/mysql-test/main/grant5.test b/mysql-test/main/grant5.test
index 14f2fd65020..1ab68b82066 100644
--- a/mysql-test/main/grant5.test
+++ b/mysql-test/main/grant5.test
@@ -23,3 +23,63 @@ show grants for foo@'%'; # user
 drop user test, foo;
 drop role foo;
 
+#
+# MDEV-12321 authentication plugin: SET PASSWORD support
+#
+error ER_PASSWD_LENGTH;
+create user u1@h identified with 'mysql_native_password' using 'pwd';
+create user u1@h identified with 'mysql_native_password' using password('pwd');
+let p=`select password('pwd')`;
+eval create user u2@h identified with 'mysql_native_password' using '$p';
+create user u3@h identified with 'mysql_native_password';
+error ER_PASSWD_LENGTH;
+set password for u3@h = 'pwd';
+set password for u3@h = password('pwd');
+create user u4@h identified with 'mysql_native_password';
+eval set password for u4@h = '$p';
+error ER_PASSWD_LENGTH;
+create user u5@h identified with 'mysql_old_password' using 'pwd';
+create user u5@h identified with 'mysql_old_password' using password('pwd');
+let p=`select old_password('pwd')`;
+eval create user u6@h identified with 'mysql_old_password' using '$p';
+create user u7@h identified with 'mysql_old_password';
+error ER_PASSWD_LENGTH;
+set password for u7@h = 'pwd';
+set password for u7@h = old_password('pwd');
+create user u8@h identified with 'mysql_old_password';
+eval set password for u8@h = '$p';
+sorted_result;
+select user,host,password,plugin,authentication_string from mysql.user where host='h';
+# test with invalid entries
+update mysql.user set authentication_string='bad' where user='u1';
+update mysql.user set authentication_string='bad' where user='u5';
+update mysql.user set plugin='nonexistent' where user='u8';
+flush privileges;
+# invalid entries are skipped, users don't exist
+error ER_PASSWORD_NO_MATCH;
+show create user u1@h;
+show create user u2@h;
+show create user u3@h;
+show create user u4@h;
+error ER_PASSWORD_NO_MATCH;
+show create user u5@h;
+show create user u6@h;
+show create user u7@h;
+error ER_PASSWORD_NO_MATCH;
+show create user u8@h;
+#grants don't work either
+error ER_PASSWORD_NO_MATCH;
+grant select on *.* to u1@h;
+grant select on *.* to u2@h;
+grant select on *.* to u3@h;
+grant select on *.* to u4@h;
+error ER_PASSWORD_NO_MATCH;
+grant select on *.* to u5@h;
+grant select on *.* to u6@h;
+grant select on *.* to u7@h;
+error ER_PASSWORD_NO_MATCH;
+grant select on *.* to u8@h;
+select user,select_priv,plugin,authentication_string from mysql.user where user like 'u_';
+
+# but they still can be dropped
+drop user u1@h, u2@h, u3@h, u4@h, u5@h, u6@h, u7@h, u8@h;
diff --git a/mysql-test/main/plugin_auth_qa_1.result b/mysql-test/main/plugin_auth_qa_1.result
index 25e859557b5..b04483722b8 100644
--- a/mysql-test/main/plugin_auth_qa_1.result
+++ b/mysql-test/main/plugin_auth_qa_1.result
@@ -149,11 +149,13 @@ new_user	test_plugin_server	new_dest
 plug_dest	mysql_native_password	*939AEE68989794C0F408277411C26055CDF41119
 UPDATE mysql.user SET plugin='new_plugin_server' WHERE user='new_user';
 FLUSH PRIVILEGES;
+Warnings:
+Warning	1524	Plugin 'new_plugin_server' is not loaded
 SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
 user	plugin	authentication_string
 new_user	new_plugin_server	new_dest
 plug_dest	mysql_native_password	*939AEE68989794C0F408277411C26055CDF41119
-ERROR HY000: Plugin 'new_plugin_server' is not loaded
+ERROR 28000: Access denied for user 'new_user'@'localhost' (using password: YES)
 UPDATE mysql.user SET plugin='test_plugin_server' WHERE user='new_user';
 UPDATE mysql.user SET USER='new_dest' WHERE user='plug_dest';
 FLUSH PRIVILEGES;
diff --git a/mysql-test/main/plugin_auth_qa_1.test b/mysql-test/main/plugin_auth_qa_1.test
index b0b8ffb3544..4f45a8aced6 100644
--- a/mysql-test/main/plugin_auth_qa_1.test
+++ b/mysql-test/main/plugin_auth_qa_1.test
@@ -141,7 +141,7 @@ FLUSH PRIVILEGES;
 --sorted_result
 SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root';
 --disable_query_log
---error ER_PLUGIN_IS_NOT_LOADED
+--error ER_ACCESS_DENIED_ERROR
 connect(plug_user,localhost,new_user,new_dest);
 --enable_query_log
 UPDATE mysql.user SET plugin='test_plugin_server' WHERE user='new_user';
diff --git a/mysql-test/main/show_create_user.result b/mysql-test/main/show_create_user.result
index 63013eca074..1205b658b6e 100644
--- a/mysql-test/main/show_create_user.result
+++ b/mysql-test/main/show_create_user.result
@@ -10,10 +10,10 @@ create user foo2@test identified by 'password';
 show create user foo2@test;
 CREATE USER for foo2@test
 CREATE USER 'foo2'@'test' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
-alter user foo2@test identified with 'someplugin' as 'somepassword';
+alter user foo2@test identified with 'mysql_old_password' as '0123456789ABCDEF';
 show create user foo2@test;
 CREATE USER for foo2@test
-CREATE USER 'foo2'@'test' IDENTIFIED VIA someplugin USING 'somepassword'
+CREATE USER 'foo2'@'test' IDENTIFIED BY PASSWORD '0123456789ABCDEF'
 create user foo3@test require SSL;
 show create user foo3@test;
 CREATE USER for foo3@test
diff --git a/mysql-test/main/show_create_user.test b/mysql-test/main/show_create_user.test
index a10c8aeeda6..03852b5abbc 100644
--- a/mysql-test/main/show_create_user.test
+++ b/mysql-test/main/show_create_user.test
@@ -9,7 +9,7 @@ show create user foo@test;
 create user foo2@test identified by 'password';
 show create user foo2@test;
 
-alter user foo2@test identified with 'someplugin' as 'somepassword';
+alter user foo2@test identified with 'mysql_old_password' as '0123456789ABCDEF';
 show create user foo2@test;
 
 create user foo3@test require SSL;
diff --git a/mysql-test/main/sp-security.result b/mysql-test/main/sp-security.result
index 3b1ec1f49e1..051fbf3a7e8 100644
--- a/mysql-test/main/sp-security.result
+++ b/mysql-test/main/sp-security.result
@@ -711,9 +711,7 @@ disconnect con2;
 DROP USER user2@localhost;
 DROP DATABASE db1;
 create user foo@local_ost;
-create user foo@`local\_ost`;
-update mysql.user set plugin='foobar' where host='local\\_ost';
-flush privileges;
+create user foo@`local\_ost` identified via mysql_old_password using '0123456789ABCDEF';
 create database foodb;
 grant create routine on foodb.* to foo@local_ost;
 connect con1,localhost,foo;
diff --git a/mysql-test/main/sp-security.test b/mysql-test/main/sp-security.test
index df915bf84cf..dd917eed671 100644
--- a/mysql-test/main/sp-security.test
+++ b/mysql-test/main/sp-security.test
@@ -977,16 +977,7 @@ DROP DATABASE db1;
 # Bug#27407480: AUTOMATIC_SP_PRIVILEGES REQUIRES NEED THE INSERT PRIVILEGES FOR MYSQL.USER TABLE
 #
 create user foo@local_ost;
-#
-# Create a user with an authentification plugin 'foobar'.
-# Instead of using a normal "CREATE USER <user> IDENTIFIED VIA <plugin>"
-# we do CREATE (without VIA) followed by UPDATE and FLUSH.
-# This is to avoid installing a real plugin and thus avoid the test dependency.
-# We won't login under this user in the below test, so this is fine.
-#
-create user foo@`local\_ost`;
-update mysql.user set plugin='foobar' where host='local\\_ost';
-flush privileges;
+create user foo@`local\_ost` identified via mysql_old_password using '0123456789ABCDEF';
 create database foodb;
 grant create routine on foodb.* to foo@local_ost;
 connect con1,localhost,foo;