1create database db; 2create role auto_create; 3create user auto_create; 4grant all on db.* to auto_create; 5create user foo@localhost; 6grant auto_create to foo@localhost; 7create user bar@localhost identified by 'baz'; 8grant auto_create to bar@localhost; 9connect con1,localhost,foo,,; 10set role 'auto_create'; 11use db; 12create table t1 (i int); 13disconnect con1; 14connect con1,localhost,bar,baz,; 15set role auto_create; 16use db; 17insert into t1 values (1); 18disconnect con1; 19connection default; 20drop user foo@localhost, bar@localhost; 21set sql_mode = 'no_auto_create_user'; 22grant auto_create to foo@localhost; 23ERROR 28000: Can't find any matching row in the user table 24grant auto_create to bar@localhost identified by 'baz'; 25select user, host from mysql.user where user = 'bar'; 26user host 27bar localhost 28set sql_mode = ''; 29connect con1,localhost,bar,baz,; 30set role auto_create; 31use db; 32drop table t1; 33disconnect con1; 34connection default; 35create user foo@localhost; 36connect con1, localhost, foo,,; 37set sql_mode = ''; 38grant auto_create to bar2@localhost; 39ERROR 28000: Access denied for user 'foo'@'localhost' 40grant auto_create to foo2@localhost; 41ERROR 28000: Access denied for user 'foo'@'localhost' 42set sql_mode = 'no_auto_create_user'; 43grant auto_create to bar2@localhost; 44ERROR 28000: Access denied for user 'foo'@'localhost' 45grant auto_create to foo2@localhost identified by 'pass'; 46ERROR 28000: Access denied for user 'foo'@'localhost' 47disconnect con1; 48connection default; 49grant auto_create to foo@localhost; 50connect con1, localhost, foo,,; 51set sql_mode = ''; 52grant auto_create to bar@localhost; 53ERROR 28000: Access denied for user 'foo'@'localhost' 54grant auto_create to bar2@localhost; 55ERROR 28000: Access denied for user 'foo'@'localhost' 56grant auto_create to foo2@localhost identified by 'pass'; 57ERROR 28000: Access denied for user 'foo'@'localhost' 58set sql_mode = 'no_auto_create_user'; 59grant auto_create to bar2@localhost; 60ERROR 28000: Access denied for user 'foo'@'localhost' 61grant auto_create to foo2@localhost identified by 'pass'; 62ERROR 28000: Access denied for user 'foo'@'localhost' 63connection default; 64grant auto_create to foo@localhost with admin option; 65disconnect con1; 66connect con1, localhost, foo,,; 67set sql_mode = ''; 68grant auto_create to bar@localhost; 69grant auto_create to bar2@localhost; 70ERROR 42000: You are not allowed to create a user with GRANT 71grant auto_create to foo2@localhost identified by 'pass'; 72ERROR 42000: You are not allowed to create a user with GRANT 73set sql_mode = 'no_auto_create_user'; 74grant auto_create to bar2@localhost; 75ERROR 28000: Can't find any matching row in the user table 76grant auto_create to foo2@localhost identified by 'pass'; 77ERROR 42000: You are not allowed to create a user with GRANT 78connection default; 79drop user foo@localhost; 80drop user bar@localhost; 81drop role auto_create; 82drop user auto_create; 83drop database db; 84