# The numbers represent test cases of the test plan. --source include/have_plugin_auth.inc --source include/not_embedded.inc set sql_mode=""; CREATE DATABASE test_user_db; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; --echo ========== test 1.1 ====================================================== # without '', without AS part CREATE USER plug IDENTIFIED WITH test_plugin_server; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER plug; GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH test_plugin_server; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; REVOKE ALL PRIVILEGES ON test_user_db.* FROM plug; DROP USER plug; # with '', without AS part CREATE USER plug IDENTIFIED WITH 'test_plugin_server'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER plug; GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH 'test_plugin_server'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; REVOKE ALL PRIVILEGES ON test_user_db.* FROM plug; DROP USER plug; # without '', AS part empty CREATE USER plug IDENTIFIED WITH test_plugin_server AS ''; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER plug; GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH test_plugin_server AS ''; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; REVOKE ALL PRIVILEGES ON test_user_db.* FROM plug; DROP USER plug; # with '', AS part empty without '' --error ER_PARSE_ERROR CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS ; --error ER_PARSE_ERROR GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH 'test_plugin_server' AS; # without '', AS part without '' --error ER_PARSE_ERROR CREATE USER plug IDENTIFIED WITH test_plugin_server AS plug_dest; --error ER_PARSE_ERROR GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH test_plugin_server AS plug_dest; --echo ========== test 1.1 syntax errors ======================================== # without auth_name --error ER_PARSE_ERROR CREATE USER plug IDENTIFIED WITH AS plug_dest; --error ER_PARSE_ERROR GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH AS plug_dest; # without auth_name and AS part --error ER_PARSE_ERROR CREATE USER plug IDENTIFIED WITH; --error ER_PARSE_ERROR GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH; # without auth_name but AS part --error ER_PARSE_ERROR CREATE USER plug IDENTIFIED AS ''; --error ER_PARSE_ERROR GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED AS ''; # with 2 auth_name parts --error ER_PARSE_ERROR CREATE USER plug IDENTIFIED WITH 'test_plugin_server' IDENTIFIED WITH 'test_plugin_server'; --error ER_PARSE_ERROR GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH 'test_plugin_server' IDENTIFIED WITH 'test_plugin_server'; # with 2 AS parts --error ER_PARSE_ERROR CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS '' AS 'plug_dest'; --error ER_PARSE_ERROR GRANT ALL PRIVILEGES ON test_user_db.* TO plug AS '' AS 'plug_dest'; # with 2 complete WITH parts --error ER_PARSE_ERROR CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS '' IDENTIFIED WITH test_plugin_server AS 'plug_dest'; --error ER_PARSE_ERROR GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH 'test_plugin_server' AS '' IDENTIFIED WITH test_plugin_server AS 'plug_dest'; # with BY and WITH part --error ER_PARSE_ERROR CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd' IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; --error ER_PARSE_ERROR GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED BY 'plug_dest_passwd' IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; # with WITH part and BY part --error ER_PARSE_ERROR CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest' USER plug_dest IDENTIFIED by 'plug_dest_pwd'; --error ER_PARSE_ERROR GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest' USER plug_dest IDENTIFIED by 'plug_dest_pwd'; # with WITH part and BY part --error ER_PARSE_ERROR CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest' plug_dest IDENTIFIED by 'plug_dest_pwd'; --error ER_PARSE_ERROR GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest' plug_dest IDENTIFIED by 'plug_dest_pwd'; # with WITH part and BY part --error ER_PARSE_ERROR CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest' IDENTIFIED by 'plug_dest_pwd'; --error ER_PARSE_ERROR GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest' IDENTIFIED by 'plug_dest_pwd'; --echo ========== test 1.1 combinations ========================== # CREATE...WITH/CREATE...BY CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; --echo ========== test 1.1.1.6/1.1.2.5 ============================ --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER plug, plug_dest; # CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER plug; CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER plug_dest; # GRANT...WITH/CREATE...BY GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER plug, plug_dest; # GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH test_plugin_server AS 'plug_dest'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER plug; CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER plug_dest; # CREATE...WITH/GRANT...BY CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; GRANT ALL PRIVILEGES ON test_user_db.* TO plug_dest IDENTIFIED BY 'plug_dest_passwd'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER plug, plug_dest; # CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER plug; GRANT ALL PRIVILEGES ON test_user_db.* TO plug_dest IDENTIFIED BY 'plug_dest_passwd'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER plug_dest; # CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH 'test_plugin_server'; DROP USER plug; # GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH test_plugin_server AS 'plug_dest'; --error ER_CANNOT_USER CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; --error ER_CANNOT_USER CREATE USER plug IDENTIFIED WITH 'test_plugin_server'; DROP USER plug; # CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; --sorted_result SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root'; GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED BY 'plug_dest_passwd'; --sorted_result SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root'; DROP USER plug; # GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH test_plugin_server AS 'plug_dest'; --error ER_CANNOT_USER CREATE USER plug IDENTIFIED BY 'plug_dest_passwd'; DROP USER plug; # CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; CREATE USER plug_dest IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; --sorted_result SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root'; DROP USER plug,plug_dest; # CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; --sorted_result SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root'; GRANT ALL PRIVILEGES ON test_user_db.* TO plug_dest IDENTIFIED WITH test_plugin_server AS 'plug_dest'; --sorted_result SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root'; DROP USER plug,plug_dest; # --echo ========== test 1.1.1.1/1.1.2.1/1.1.1.5 ==================== SET NAMES utf8; # CREATE USER plüg IDENTIFIED WITH 'test_plugin_server' AS 'plüg_dest'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER plüg; CREATE USER plüg_dest IDENTIFIED BY 'plug_dest_passwd'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER plüg_dest; SET NAMES ascii; # CREATE USER 'plüg' IDENTIFIED WITH 'test_plugin_server' AS 'plüg_dest'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER 'plüg'; CREATE USER 'plüg_dest' IDENTIFIED BY 'plug_dest_passwd'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER 'plüg_dest'; SET NAMES latin1; # --echo ========== test 1.1.1.5 ==================================== --error ER_PLUGIN_IS_NOT_LOADED CREATE USER 'plüg' IDENTIFIED WITH 'test_plügin_server' AS 'plüg_dest'; CREATE USER 'plug' IDENTIFIED WITH 'test_plugin_server' AS 'plüg_dest'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER 'plug'; CREATE USER 'plüg_dest' IDENTIFIED BY 'plug_dest_passwd'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER 'plüg_dest'; SET NAMES utf8; # --error ER_PLUGIN_IS_NOT_LOADED CREATE USER plüg IDENTIFIED WITH 'test_plügin_server' AS 'plüg_dest'; CREATE USER 'plüg' IDENTIFIED WITH 'test_plugin_server' AS 'plüg_dest'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER 'plüg'; CREATE USER 'plüg_dest' IDENTIFIED BY 'plug_dest_passwd'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER 'plüg_dest'; CREATE USER plüg IDENTIFIED WITH test_plugin_server AS 'plüg_dest'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER plüg; CREATE USER plüg_dest IDENTIFIED BY 'plug_dest_passwd'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER plüg_dest; --echo ========== test 1.1.1.2/1.1.2.2============================= SET @auth_name= 'test_plugin_server'; --error ER_PARSE_ERROR CREATE USER plug IDENTIFIED WITH @auth_name AS 'plug_dest'; SET @auth_string= 'plug_dest'; --error ER_PARSE_ERROR CREATE USER plug IDENTIFIED WITH test_plugin_server AS @auth_string; --echo ========== test 1.1.1.3/1.1.2.3============================= --error ER_PLUGIN_IS_NOT_LOADED CREATE USER plug IDENTIFIED WITH 'hh''s_test_plugin_server' AS 'plug_dest'; CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'hh''s_plug_dest'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER plug; CREATE USER 'hh''s_plug_dest' IDENTIFIED BY 'plug_dest_passwd'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER 'hh''s_plug_dest'; --echo ========== test 1.1.1.4 ==================================== --error ER_PARSE_ERROR CREATE USER plug IDENTIFIED WITH hh''s_test_plugin_server AS 'plug_dest'; --echo ========== test 1.1.3.1 ==================================== GRANT INSERT ON test_user_db.* TO grant_user IDENTIFIED WITH test_plugin_server AS 'plug_dest'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; CREATE USER plug_dest; DROP USER plug_dest; GRANT ALL PRIVILEGES ON test_user_db.* TO plug_dest; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; DROP USER grant_user,plug_dest; # set @save_sql_mode= @@sql_mode; SET @@sql_mode=no_auto_create_user; GRANT INSERT ON test_user_db.* TO grant_user IDENTIFIED WITH test_plugin_server AS 'plug_dest'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; CREATE USER plug_dest; DROP USER plug_dest; --error ER_PASSWORD_NO_MATCH GRANT ALL PRIVILEGES ON test_user_db.* TO plug_dest; DROP USER grant_user; # GRANT INSERT ON test_user_db.* TO grant_user IDENTIFIED WITH test_plugin_server AS 'plug_dest'; --sorted_result SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; --sorted_result SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root'; DROP USER plug_dest; GRANT ALL PRIVILEGES ON test_user_db.* TO plug_dest IDENTIFIED BY 'plug_user_passwd'; --sorted_result SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root'; DROP USER grant_user,plug_dest; set @@sql_mode= @save_sql_mode; # DROP DATABASE test_user_db; --exit