Debian Codegraph @

mysql-test/suite/auth_sec/t/atomic_create_user.test

raw
1 --source include/have_log_bin.inc
2 --source include/save_binlog_position.inc
3
4 # Save the initial number of concurrent sessions
5 --source include/count_sessions.inc
6
7 --echo # ----------------------------------------------------------------------
8
9 --echo # Begin : Tests for CREATE USER
10 CREATE USER userX;
11 SELECT user FROM mysql.user WHERE user LIKE 'user%';
12
13 --echo # Case 1 : Execute CREATE USER for existing user
14 --error ER_CANNOT_USER
15 CREATE USER userY, userZ, userX;
16 --echo # Must not show entries for userY and userZ
17 SELECT user FROM mysql.user WHERE user LIKE 'user%';
18
19 --let $event= !Q(CREATE USER .userX.*)
20 --source ../include/auth_sec_assert_binlog_events.inc
21 --let $invert= 1
22 --let $event= !Q(CREATE USER .*userY.*userZ.*)
23 --source ../include/auth_sec_assert_binlog_events.inc
24 --let $invert= 0
25 --source include/save_binlog_position.inc
26
27 --echo # Case 2 : Execute CREATE USER for non-existing users
28 CREATE USER userY, userZ;
29 --echo # Must show entries for userX, userY and userZ
30 SELECT user FROM mysql.user WHERE user LIKE 'user%';
31
32 --let $event= !Q(CREATE USER .userY.*userZ.*)
33 --source ../include/auth_sec_assert_binlog_events.inc
34 --source include/save_binlog_position.inc
35
36 --echo # Case 3 : Try creating anonymous user with expired password
37 --error ER_CANNOT_USER
38 CREATE USER ''@localhost,
39 userA IDENTIFIED BY 'abcd'
40 PASSWORD EXPIRE;
41
42 --error ER_CANNOT_USER
43 CREATE USER ''@'' PASSWORD EXPIRE;
44
45 --echo # Must not show ''@localhost or ''@''
46 SELECT user FROM mysql.user WHERE user LIKE 'user%';
47
48 --echo # Case 4 : Try creating user with invalid plugin
49 --error ER_PLUGIN_IS_NOT_LOADED
50 CREATE USER userA IDENTIFIED BY 'abcd', userB IDENTIFIED WITH 'blahblah';
51
52 --echo # Case 5 : Try creating user with invalid hash
53 --error ER_PASSWORD_FORMAT
54 CREATE USER userA IDENTIFIED BY 'abcd',
55 userB IDENTIFIED WITH 'mysql_native_password' AS 'hahaha';
56
57 --echo # Must not show userA, userB
58 SELECT user FROM mysql.user WHERE user LIKE 'user%';
59
60 --echo # Check binlog for Cases 3, 4 and 5 above
61 --let $invert= 1
62 --let $event= !Q(CREATE USER.*)
63 --source ../include/auth_sec_assert_binlog_events.inc
64 --let $invert= 0
65
66 --echo # Case 6 : Add a row in mysql.user without FLUSH PRIVILEGES and
67 --echo # execute CREATE USER for same user
68
69 --echo # Insert an entry in mysql.user table but do not execute FLUSH PRIVILEGES
70 CREATE TABLE mysql.tmp_user LIKE mysql.user;
71 INSERT INTO mysql.tmp_user SELECT * FROM mysql.user WHERE user LIKE 'userX';
72 UPDATE mysql.tmp_user SET user='userW' WHERE user LIKE 'userX';
73 INSERT INTO mysql.user SELECT * FROM mysql.tmp_user;
74 DROP TABLE mysql.tmp_user;
75
76 --source include/save_binlog_position.inc
77
78 --echo # Though userW is not present in cache, CREATE USER should fail
79 --echo # because we will encounter duplicate key error while inserting entry
80 --echo # in mysql.user table for userW.
81 --error ER_CANNOT_USER
82 CREATE USER userW IDENTIFIED BY 'abcd', userV IDENTIFIED BY 'haha';
83 --echo # Must not show userV
84 SELECT user FROM mysql.user WHERE user LIKE 'user%';
85
86 --let $invert= 1
87 --let $event= !Q(CREATE USER.*)
88 --source ../include/auth_sec_assert_binlog_events.inc
89 --let $invert= 0
90
91 FLUSH PRIVILEGES;
92
93 DROP USER userW, userX, userY, userZ;
94
95 --source include/save_binlog_position.inc
96
97 --echo # End : Tests for CREATE USER
98
99 --echo # ----------------------------------------------------------------------
100
101 --echo # BEGIN : Tests for CREATE USER IF NOT EXISTS
102
103 CALL mtr.add_suppression("Following users were specified in CREATE USER IF NOT EXISTS but they already exist.");
104
105 CREATE USER userX;
106 --source include/save_binlog_position.inc
107
108 --echo # Case 1 : Execute CREATE USER IF NOT EXISTS for existing user
109 CREATE USER IF NOT EXISTS userX, userY, userZ;
110
111 --echo # Must show entries for userX, userY, userZ
112 SELECT user FROM mysql.user WHERE user LIKE 'user%';
113
114 --let $event= !Q(CREATE USER IF NOT EXISTS.*userX.*userY.*userZ.*)
115 --source ../include/auth_sec_assert_binlog_events.inc
116 --source include/save_binlog_position.inc
117
118 --echo # Must show warnings about userX, userY and userZ
119 CREATE USER IF NOT EXISTS userX, userY, userZ;
120
121 --let $event= !Q(CREATE USER IF NOT EXISTS.*userX.*userY.*userZ.*)
122 --source ../include/auth_sec_assert_binlog_events.inc
123 --source include/save_binlog_position.inc
124
125 --echo # Case 2 : Try creating anonymous user with expired password
126 --echo # using CREATE USER IF NOT EXISTS
127 --error ER_CANNOT_USER
128 CREATE USER IF NOT EXISTS ''@'' PASSWORD EXPIRE;
129
130 --error ER_CANNOT_USER
131 CREATE USER IF NOT EXISTS ''@localhost PASSWORD EXPIRE;
132
133 --echo # Case 3 : Try creating user with invalid plugin
134 --error ER_PLUGIN_IS_NOT_LOADED
135 CREATE USER IF NOT EXISTS userA IDENTIFIED BY 'abcd', userB IDENTIFIED WITH 'blahblah', userX;
136
137 --echo # Case 4 : Try creating user with invalid hash
138 --error ER_PASSWORD_FORMAT
139 CREATE USER IF NOT EXISTS userA IDENTIFIED BY 'abcd',
140 userB IDENTIFIED WITH 'mysql_native_password' AS 'hahaha';
141
142 --echo # Must not show userA, userB
143 SELECT user FROM mysql.user WHERE user LIKE 'user%';
144
145 --let $invert= 1
146 --let $event= !Q(CREATE USER.*)
147 --source ../include/auth_sec_assert_binlog_events.inc
148 --let $invert= 0
149
150 --echo # Case 6 : Add a row in mysql.user without FLUSH PRIVILEGES and
151 --echo # execute CREATE USER IF NOT for same user
152
153 --echo # Insert an entry in mysql.user table but do not execute FLUSH PRIVILEGES
154 CREATE TABLE mysql.tmp_user LIKE mysql.user;
155 INSERT INTO mysql.tmp_user SELECT * FROM mysql.user WHERE user LIKE 'userX';
156 UPDATE mysql.tmp_user SET user='userW' WHERE user LIKE 'userX';
157 INSERT INTO mysql.user SELECT * FROM mysql.tmp_user;
158 DROP TABLE mysql.tmp_user;
159
160 --source include/save_binlog_position.inc
161
162 --echo # Though userW is not present in cache, CREATE USER IF NOT EXISTS
163 --echo # will skip it because we will encounter duplicate key error while
164 --echo # inserting entry in mysql.user table for userW.
165 --echo # Must show warning about userW@%.
166
167 CREATE USER IF NOT EXISTS userW IDENTIFIED BY 'abcd',
168 userV IDENTIFIED BY 'haha';
169
170 --echo # Must show userV, userW, userX, userY, userZ
171 SELECT user FROM mysql.user WHERE user LIKE 'user%';
172
173 --let $event= !Q(CREATE USER.*userW.*userV.*)
174 --source ../include/auth_sec_assert_binlog_events.inc
175
176 DROP USER userV, userW, userX, userY, userZ;
177
178 --source include/save_binlog_position.inc
179
180 --echo # END : Tests for CREATE USER IF NOT EXISTS
181
182 --echo # ----------------------------------------------------------------------
183
184 --echo # Begin : Tests for CREATE USER with DEFAULT ROLE
185 CREATE USER userX;
186 CREATE ROLE 'roleX', 'roleY', 'roleZ';
187 SELECT user FROM mysql.user WHERE user LIKE 'user%';
188
189 --echo # Case 1 : Execute CREATE USER for existing user
190 --error ER_CANNOT_USER
191 CREATE USER userY, userZ, userX DEFAULT ROLE 'roleX';
192 --echo # Must not show entries for userY and userZ
193 SELECT user FROM mysql.user WHERE user LIKE 'user%';
194 SELECT user FROM mysql.default_roles where user LIKE 'user%';
195
196 --let $invert= 1
197 --let $event= !Q(CREATE USER .*userY.*userZ.*.userX)
198 --source ../include/auth_sec_assert_binlog_events.inc
199 --let $invert= 0
200 --source include/save_binlog_position.inc
201
202 --echo # Case 2 : Execute CREATE USER for non-existing users
203 CREATE USER userY, userZ DEFAULT ROLE 'roleX', 'roleY';
204 --echo # Must show entries for userX, userY and userZ
205 SELECT user FROM mysql.user WHERE user LIKE 'user%';
206 SELECT user FROM mysql.default_roles where user LIKE 'user%';
207
208 --let $event= !Q(CREATE USER .userY.*userZ.*)
209 --source ../include/auth_sec_assert_binlog_events.inc
210 --source include/save_binlog_position.inc
211
212 --echo # Case 3 : Try creating user with invalid hash
213 --error ER_PASSWORD_FORMAT
214 CREATE USER userA IDENTIFIED BY 'abcd',
215 userB IDENTIFIED WITH 'mysql_native_password' AS 'hahaha'
216 DEFAULT ROLE roleX;
217
218 --echo # Must not show userA, userB
219 SELECT user FROM mysql.user WHERE user LIKE 'user%';
220 SELECT user FROM mysql.default_roles WHERE user LIKE 'user%';
221
222 --echo # Check binlog for Cases 3
223 --let $invert= 1
224 --let $event= !Q(CREATE USER.*)
225 --source ../include/auth_sec_assert_binlog_events.inc
226 --let $invert= 0
227
228 DROP USER userX, userY, userZ;
229 DROP ROLE roleX, roleY, roleZ;
230
231 --source include/save_binlog_position.inc
232
233 --echo # ----------------------------------------------------------------------
234
235 # Wait till we reached the initial number of concurrent sessions
236 --source include/wait_until_count_sessions.inc