1# The numbers represent test cases of the test plan. 2 3--source include/have_plugin_auth.inc 4--source include/not_embedded.inc 5 6set sql_mode=""; 7 8CREATE DATABASE test_user_db; 9 10--sorted_result 11SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 12--echo ========== test 1.1 ====================================================== 13# without '', without AS part 14CREATE USER plug IDENTIFIED WITH test_plugin_server; 15--sorted_result 16SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 17DROP USER plug; 18GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH test_plugin_server; 19--sorted_result 20SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 21REVOKE ALL PRIVILEGES ON test_user_db.* FROM plug; 22DROP USER plug; 23# with '', without AS part 24CREATE USER plug IDENTIFIED WITH 'test_plugin_server'; 25--sorted_result 26SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 27DROP USER plug; 28GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH 'test_plugin_server'; 29--sorted_result 30SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 31REVOKE ALL PRIVILEGES ON test_user_db.* FROM plug; 32DROP USER plug; 33# without '', AS part empty 34CREATE USER plug IDENTIFIED WITH test_plugin_server AS ''; 35--sorted_result 36SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 37DROP USER plug; 38GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH test_plugin_server AS ''; 39--sorted_result 40SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 41REVOKE ALL PRIVILEGES ON test_user_db.* FROM plug; 42DROP USER plug; 43# with '', AS part empty without '' 44--error ER_PARSE_ERROR 45CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS ; 46--error ER_PARSE_ERROR 47GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH 'test_plugin_server' AS; 48# without '', AS part without '' 49--error ER_PARSE_ERROR 50CREATE USER plug IDENTIFIED WITH test_plugin_server AS plug_dest; 51--error ER_PARSE_ERROR 52GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH test_plugin_server AS plug_dest; 53--echo ========== test 1.1 syntax errors ======================================== 54# without auth_name 55--error ER_PARSE_ERROR 56CREATE USER plug IDENTIFIED WITH AS plug_dest; 57--error ER_PARSE_ERROR 58GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH AS plug_dest; 59# without auth_name and AS part 60--error ER_PARSE_ERROR 61CREATE USER plug IDENTIFIED WITH; 62--error ER_PARSE_ERROR 63GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH; 64# without auth_name but AS part 65--error ER_PARSE_ERROR 66CREATE USER plug IDENTIFIED AS ''; 67--error ER_PARSE_ERROR 68GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED AS ''; 69# with 2 auth_name parts 70--error ER_PARSE_ERROR 71CREATE USER plug IDENTIFIED WITH 'test_plugin_server' IDENTIFIED WITH 'test_plugin_server'; 72--error ER_PARSE_ERROR 73GRANT ALL PRIVILEGES ON test_user_db.* TO plug 74 IDENTIFIED WITH 'test_plugin_server' IDENTIFIED WITH 'test_plugin_server'; 75# with 2 AS parts 76--error ER_PARSE_ERROR 77CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS '' AS 'plug_dest'; 78--error ER_PARSE_ERROR 79GRANT ALL PRIVILEGES ON test_user_db.* TO plug AS '' AS 'plug_dest'; 80# with 2 complete WITH parts 81--error ER_PARSE_ERROR 82CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS '' 83 IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 84--error ER_PARSE_ERROR 85GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH 'test_plugin_server' AS '' 86 IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 87# with BY and WITH part 88--error ER_PARSE_ERROR 89CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd' 90 IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 91--error ER_PARSE_ERROR 92GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED BY 'plug_dest_passwd' 93 IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 94# with WITH part and BY part 95--error ER_PARSE_ERROR 96CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest' 97 USER plug_dest IDENTIFIED by 'plug_dest_pwd'; 98--error ER_PARSE_ERROR 99GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest' 100 USER plug_dest IDENTIFIED by 'plug_dest_pwd'; 101# with WITH part and BY part 102--error ER_PARSE_ERROR 103CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest' 104 plug_dest IDENTIFIED by 'plug_dest_pwd'; 105--error ER_PARSE_ERROR 106GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest' 107 plug_dest IDENTIFIED by 'plug_dest_pwd'; 108# with WITH part and BY part 109--error ER_PARSE_ERROR 110CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest' 111 IDENTIFIED by 'plug_dest_pwd'; 112--error ER_PARSE_ERROR 113GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest' 114 IDENTIFIED by 'plug_dest_pwd'; 115 116--echo ========== test 1.1 combinations ========================== 117# CREATE...WITH/CREATE...BY 118CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 119--echo ========== test 1.1.1.6/1.1.2.5 ============================ 120--sorted_result 121SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 122CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 123--sorted_result 124SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 125DROP USER plug, plug_dest; 126# 127CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 128--sorted_result 129SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 130DROP USER plug; 131CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 132--sorted_result 133SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 134DROP USER plug_dest; 135# GRANT...WITH/CREATE...BY 136GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 137--sorted_result 138SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 139CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 140--sorted_result 141SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 142DROP USER plug, plug_dest; 143# 144GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 145--sorted_result 146SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 147DROP USER plug; 148CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 149--sorted_result 150SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 151DROP USER plug_dest; 152# CREATE...WITH/GRANT...BY 153CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 154--sorted_result 155SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 156GRANT ALL PRIVILEGES ON test_user_db.* TO plug_dest IDENTIFIED BY 'plug_dest_passwd'; 157--sorted_result 158SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 159DROP USER plug, plug_dest; 160# 161CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 162--sorted_result 163SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 164DROP USER plug; 165GRANT ALL PRIVILEGES ON test_user_db.* TO plug_dest IDENTIFIED BY 'plug_dest_passwd'; 166--sorted_result 167SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 168DROP USER plug_dest; 169# 170CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 171GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 172GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH 'test_plugin_server'; 173DROP USER plug; 174# 175GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 176--error ER_CANNOT_USER 177CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 178--error ER_CANNOT_USER 179CREATE USER plug IDENTIFIED WITH 'test_plugin_server'; 180DROP USER plug; 181# 182CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 183--sorted_result 184SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root'; 185GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED BY 'plug_dest_passwd'; 186--sorted_result 187SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root'; 188DROP USER plug; 189# 190GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 191--error ER_CANNOT_USER 192CREATE USER plug IDENTIFIED BY 'plug_dest_passwd'; 193DROP USER plug; 194# 195CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 196CREATE USER plug_dest IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 197--sorted_result 198SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root'; 199DROP USER plug,plug_dest; 200# 201CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 202--sorted_result 203SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root'; 204GRANT ALL PRIVILEGES ON test_user_db.* TO plug_dest 205 IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 206--sorted_result 207SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root'; 208DROP USER plug,plug_dest; 209# 210 211--echo ========== test 1.1.1.1/1.1.2.1/1.1.1.5 ==================== 212 213SET NAMES utf8; 214# 215CREATE USER plüg IDENTIFIED WITH 'test_plugin_server' AS 'plüg_dest'; 216--sorted_result 217SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 218DROP USER plüg; 219CREATE USER plüg_dest IDENTIFIED BY 'plug_dest_passwd'; 220--sorted_result 221SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 222DROP USER plüg_dest; 223 224SET NAMES ascii; 225# 226CREATE USER 'plüg' IDENTIFIED WITH 'test_plugin_server' AS 'plüg_dest'; 227--sorted_result 228SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 229DROP USER 'plüg'; 230CREATE USER 'plüg_dest' IDENTIFIED BY 'plug_dest_passwd'; 231--sorted_result 232SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 233DROP USER 'plüg_dest'; 234 235SET NAMES latin1; 236# 237--echo ========== test 1.1.1.5 ==================================== 238--error ER_PLUGIN_IS_NOT_LOADED 239CREATE USER 'plüg' IDENTIFIED WITH 'test_plügin_server' AS 'plüg_dest'; 240CREATE USER 'plug' IDENTIFIED WITH 'test_plugin_server' AS 'plüg_dest'; 241--sorted_result 242SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 243DROP USER 'plug'; 244CREATE USER 'plüg_dest' IDENTIFIED BY 'plug_dest_passwd'; 245--sorted_result 246SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 247DROP USER 'plüg_dest'; 248 249SET NAMES utf8; 250# 251--error ER_PLUGIN_IS_NOT_LOADED 252CREATE USER plüg IDENTIFIED WITH 'test_plügin_server' AS 'plüg_dest'; 253CREATE USER 'plüg' IDENTIFIED WITH 'test_plugin_server' AS 'plüg_dest'; 254--sorted_result 255SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 256DROP USER 'plüg'; 257CREATE USER 'plüg_dest' IDENTIFIED BY 'plug_dest_passwd'; 258--sorted_result 259SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 260DROP USER 'plüg_dest'; 261 262CREATE USER plüg IDENTIFIED WITH test_plugin_server AS 'plüg_dest'; 263--sorted_result 264SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 265DROP USER plüg; 266CREATE USER plüg_dest IDENTIFIED BY 'plug_dest_passwd'; 267--sorted_result 268SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 269DROP USER plüg_dest; 270 271--echo ========== test 1.1.1.2/1.1.2.2============================= 272 273SET @auth_name= 'test_plugin_server'; 274--error ER_PARSE_ERROR 275CREATE USER plug IDENTIFIED WITH @auth_name AS 'plug_dest'; 276 277SET @auth_string= 'plug_dest'; 278--error ER_PARSE_ERROR 279CREATE USER plug IDENTIFIED WITH test_plugin_server AS @auth_string; 280 281--echo ========== test 1.1.1.3/1.1.2.3============================= 282 283--error ER_PLUGIN_IS_NOT_LOADED 284CREATE USER plug IDENTIFIED WITH 'hh''s_test_plugin_server' AS 'plug_dest'; 285 286CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'hh''s_plug_dest'; 287--sorted_result 288SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 289DROP USER plug; 290CREATE USER 'hh''s_plug_dest' IDENTIFIED BY 'plug_dest_passwd'; 291--sorted_result 292SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 293DROP USER 'hh''s_plug_dest'; 294 295--echo ========== test 1.1.1.4 ==================================== 296 297--error ER_PARSE_ERROR 298CREATE USER plug IDENTIFIED WITH hh''s_test_plugin_server AS 'plug_dest'; 299 300--echo ========== test 1.1.3.1 ==================================== 301 302GRANT INSERT ON test_user_db.* TO grant_user IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 303--sorted_result 304SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 305CREATE USER plug_dest; 306DROP USER plug_dest; 307GRANT ALL PRIVILEGES ON test_user_db.* TO plug_dest; 308--sorted_result 309SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 310DROP USER grant_user,plug_dest; 311# 312set @save_sql_mode= @@sql_mode; 313SET @@sql_mode=no_auto_create_user; 314GRANT INSERT ON test_user_db.* TO grant_user IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 315--sorted_result 316SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 317CREATE USER plug_dest; 318DROP USER plug_dest; 319--error ER_PASSWORD_NO_MATCH 320GRANT ALL PRIVILEGES ON test_user_db.* TO plug_dest; 321DROP USER grant_user; 322# 323GRANT INSERT ON test_user_db.* TO grant_user IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 324--sorted_result 325SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 326CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 327--sorted_result 328SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root'; 329DROP USER plug_dest; 330GRANT ALL PRIVILEGES ON test_user_db.* TO plug_dest IDENTIFIED BY 'plug_user_passwd'; 331--sorted_result 332SELECT user,plugin,authentication_string,password FROM mysql.user WHERE user != 'root'; 333DROP USER grant_user,plug_dest; 334set @@sql_mode= @save_sql_mode; 335# 336DROP DATABASE test_user_db; 337 338--exit 339 340