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