1 files changed, 147 insertions, 0 deletions
diff --git a/mysql-test/main/set_password.test b/mysql-test/main/set_password.test
new file mode 100644
index 00000000000..fc1ecb5ef5c
--- /dev/null
+++ b/mysql-test/main/set_password.test
@@ -0,0 +1,147 @@
+#
+# MDEV-9835 Valid password is not working after server restart.
+#
+# Various combinations of SET PASSWORD and not-empty mysql.user.plugin field
+#
+--source include/not_embedded.inc
+
+--enable_connect_log
+
+set global secure_auth=0;
+
+# The hash (old and new) is for 'test'
+create user natauth@localhost identified via 'mysql_native_password' using '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
+
+create user newpass@localhost identified by password '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
+
+create user newpassnat@localhost identified via 'mysql_native_password';
+set password for newpassnat@localhost = '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';
+
+create user oldauth@localhost identified with 'mysql_old_password' using '378b243e220ca493';
+
+create user oldpass@localhost identified by password '378b243e220ca493';
+
+create user oldpassold@localhost identified with 'mysql_old_password';
+set password for oldpassold@localhost = '378b243e220ca493';
+
+--sorted_result
+select user, host, password, plugin, authentication_string from mysql.user where user != 'root';
+
+--connect(con,localhost,natauth,test,)
+select current_user();
+--disconnect con
+--connect(con,localhost,newpass,test,)
+select current_user();
+--disconnect con
+--connect(con,localhost,newpassnat,test,)
+select current_user();
+--disconnect con
+--connect(con,localhost,oldauth,test,)
+select current_user();
+--disconnect con
+--connect(con,localhost,oldpass,test,)
+select current_user();
+--disconnect con
+--connect(con,localhost,oldpassold,test,)
+select current_user();
+--disconnect con
+
+--connection default
+
+flush privileges;
+
+--connect(con,localhost,natauth,test,)
+select current_user();
+--disconnect con
+--connect(con,localhost,newpass,test,)
+select current_user();
+--disconnect con
+--connect(con,localhost,newpassnat,test,)
+select current_user();
+--disconnect con
+--connect(con,localhost,oldauth,test,)
+select current_user();
+--disconnect con
+--connect(con,localhost,oldpass,test,)
+select current_user();
+--disconnect con
+--connect(con,localhost,oldpassold,test,)
+select current_user();
+--disconnect con
+
+--connection default
+
+# changing to the NEW password hash
+set password for natauth@localhost = PASSWORD('test2');
+set password for newpass@localhost = PASSWORD('test2');
+set password for newpassnat@localhost = PASSWORD('test2');
+set password for oldauth@localhost = PASSWORD('test2');
+set password for oldpass@localhost = PASSWORD('test2');
+set password for oldpassold@localhost = PASSWORD('test2');
+
+--sorted_result
+select user, host, password, plugin, authentication_string from mysql.user where user != 'root';
+
+--connect(con,localhost,natauth,test2,)
+select current_user();
+--disconnect con
+--connect(con,localhost,newpass,test2,)
+select current_user();
+--disconnect con
+--connect(con,localhost,newpassnat,test2,)
+select current_user();
+--disconnect con
+--connect(con,localhost,oldauth,test2,)
+select current_user();
+--disconnect con
+--connect(con,localhost,oldpass,test2,)
+select current_user();
+--disconnect con
+--connect(con,localhost,oldpassold,test2,)
+select current_user();
+--disconnect con
+
+--connection default
+
+flush privileges;
+
+--connect(con,localhost,natauth,test2,)
+select current_user();
+--disconnect con
+--connect(con,localhost,newpass,test2,)
+select current_user();
+--disconnect con
+--connect(con,localhost,newpassnat,test2,)
+select current_user();
+--disconnect con
+--connect(con,localhost,oldauth,test2,)
+select current_user();
+--disconnect con
+--connect(con,localhost,oldpass,test2,)
+select current_user();
+--disconnect con
+--connect(con,localhost,oldpassold,test2,)
+select current_user();
+--disconnect con
+
+--connection default
+drop user natauth@localhost, newpass@localhost, newpassnat@localhost;
+drop user oldauth@localhost, oldpass@localhost, oldpassold@localhost;
+set global secure_auth=default;
+
+#
+# MDEV-16238 root/localhost authn prioritizes authentication_string over Password
+#
+create user foo@localhost identified with mysql_native_password;
+update mysql.user set authentication_string=password('foo'), plugin='mysql_native_password' where user='foo' and host='localhost';
+set password for 'foo'@'localhost' = password('bar');
+flush privileges;
+--connect foo, localhost, foo, bar
+select user(), current_user();
+show grants;
+--disconnect foo
+--connection default
+select user,host,password,plugin,authentication_string from mysql.user where user='foo';
+set password for 'foo'@'localhost' = '';
+select user,host,password,plugin,authentication_string from mysql.user where user='foo';
+drop user foo@localhost;