Debian Codegraph @

mysql-test/suite/roles/grant_role_auto_create_user.test

raw
1 #
2 # MDEV-5221 User auto-creation does not work upon GRANT <role>
3 #
4 --source include/not_embedded.inc
5
6 create database db;
7 create role auto_create;
8 create user auto_create;
9 grant all on db.* to auto_create;
10 create user foo@localhost;
11 grant auto_create to foo@localhost;
12 create user bar@localhost identified by 'baz';
13 grant auto_create to bar@localhost;
14
15 # Test if the users have been created and the role has been granted to them
16 --connect (con1,localhost,foo,,)
17 set role 'auto_create';
18 use db;
19 create table t1 (i int);
20 --disconnect con1
21
22 --connect (con1,localhost,bar,baz,)
23 set role auto_create;
24 use db;
25 insert into t1 values (1);
26 --disconnect con1
27
28 --connection default
29 drop user foo@localhost, bar@localhost;
30
31 set sql_mode = 'no_auto_create_user';
32 --error ER_PASSWORD_NO_MATCH
33 grant auto_create to foo@localhost;
34 grant auto_create to bar@localhost identified by 'baz';
35 select user, host from mysql.user where user = 'bar';
36 set sql_mode = '';
37
38 --connect (con1,localhost,bar,baz,)
39 set role auto_create;
40 use db;
41 drop table t1;
42 --disconnect con1
43
44 --connection default
45
46 create user foo@localhost;
47
48 # test all possible cases with a user who has no rights to grant the role
49 --connect (con1, localhost, foo,,)
50
51 set sql_mode = '';
52 #try and grant roles, no rights however
53 --error ER_ACCESS_DENIED_NO_PASSWORD_ERROR
54 grant auto_create to bar2@localhost;
55 --error ER_ACCESS_DENIED_NO_PASSWORD_ERROR
56 grant auto_create to foo2@localhost;
57
58 set sql_mode = 'no_auto_create_user';
59 #try and grant roles, no rights however
60 --error ER_ACCESS_DENIED_NO_PASSWORD_ERROR
61 grant auto_create to bar2@localhost;
62 --error ER_ACCESS_DENIED_NO_PASSWORD_ERROR
63 grant auto_create to foo2@localhost identified by 'pass';
64 --disconnect con1
65 --connection default
66 grant auto_create to foo@localhost;
67
68 --connect (con1, localhost, foo,,)
69
70 #we now have the role granted to us, but we don't have insert privileges,
71 #we should not be able to create a new user
72
73 set sql_mode = '';
74 #also test that we can not grant a role without admin option
75 --error ER_ACCESS_DENIED_NO_PASSWORD_ERROR
76 grant auto_create to bar@localhost;
77
78 --error ER_ACCESS_DENIED_NO_PASSWORD_ERROR
79 grant auto_create to bar2@localhost;
80 --error ER_ACCESS_DENIED_NO_PASSWORD_ERROR
81 grant auto_create to foo2@localhost identified by 'pass';
82
83 set sql_mode = 'no_auto_create_user';
84 --error ER_ACCESS_DENIED_NO_PASSWORD_ERROR
85 grant auto_create to bar2@localhost;
86 --error ER_ACCESS_DENIED_NO_PASSWORD_ERROR
87 grant auto_create to foo2@localhost identified by 'pass';
88
89 #test that we can grant a role with admin option to an existing user, but not
90 #create one
91
92 --connection default
93 grant auto_create to foo@localhost with admin option;
94
95 --disconnect con1
96 --connect (con1, localhost, foo,,)
97
98 #we now have the role granted to us, but we don't have insert privileges,
99 #we should not be able to create a new user
100
101 set sql_mode = '';
102 #also test that we can grant a role with admin option
103 grant auto_create to bar@localhost;
104
105 #test that we don't create users if we don't have insert privileges
106 --error ER_CANT_CREATE_USER_WITH_GRANT
107 grant auto_create to bar2@localhost;
108 --error ER_CANT_CREATE_USER_WITH_GRANT
109 grant auto_create to foo2@localhost identified by 'pass';
110
111 set sql_mode = 'no_auto_create_user';
112 --error ER_PASSWORD_NO_MATCH
113 grant auto_create to bar2@localhost;
114 --error ER_CANT_CREATE_USER_WITH_GRANT
115 grant auto_create to foo2@localhost identified by 'pass';
116
117
118 --connection default
119 drop user foo@localhost;
120 drop user bar@localhost;
121 drop role auto_create;
122 drop user auto_create;
123 drop database db;