Debian Codegraph @

mysql-test/suite/binlog/t/binlog_grant_alter_user.test

raw
1 ################### suite/binlog/t/binlog_grant_alter_user.test ###################
2 # Replication Testing CREATE/ALTER/GRANT USER new syntax with New variable #
3 # "log-builtin-as-identified-by-password" default value "OFF" #
4 # #
5 # Test for WL#6409 #
6 # created :2015-01-02 #
7 # Modified:2015-02-04 WL#6054 #
8 # Author : Lalit Choudhary #
9 # #
10 ###################################################################################
11
12 # Save the initial number of concurrent sessions
13 --source include/count_sessions.inc
14 --source include/master-slave.inc
15 --connection master
16
17 --echo #
18 --echo # WL#6409: CREATE/ALTER USER
19 --echo #
20
21 --echo # CREATE USER
22 --echo
23 CREATE USER user1;
24 query_vertical SELECT host,user,plugin
25 FROM mysql.user WHERE user = 'user1';
26
27 --echo
28 --error ER_PARSE_ERROR
29 CREATE USER user3@%;
30
31 # Sequence of the attributes. provide password first and auth_plugin later on
32 --error ER_PARSE_ERROR
33 CREATE USER user3@localhost BY 'auth_string' WITH 'mysql_native_password';
34
35 CREATE USER "user2"@'%';
36 query_vertical SELECT host,user,plugin
37 FROM mysql.user WHERE user='user2';
38
39 --echo
40 CREATE USER u1@localhost;
41 query_vertical SELECT user,plugin FROM mysql.user WHERE USER='u1';
42
43 CREATE USER u2@localhost IDENTIFIED BY 'auth_string' ACCOUNT LOCK;
44 query_vertical SELECT user,plugin FROM mysql.user WHERE USER='u2';
45
46 --echo
47 --error ER_PARSE_ERROR
48 CREATE USER user5@localhost IDENTIFIED AS 'auth_string';
49
50 CREATE USER u3@localhost IDENTIFIED WITH 'mysql_native_password';
51 query_vertical SELECT user,plugin FROM mysql.user WHERE USER='u3';
52
53 CREATE USER u4@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
54 query_vertical SELECT user,plugin FROM mysql.user WHERE USER='u4';
55
56 # Testing connection
57 --connect(con1, localhost, u4,'auth_string')
58 SELECT USER();
59 connection default;
60 disconnect con1;
61
62 --echo
63 CREATE USER user4@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
64 query_vertical SELECT user,plugin FROM mysql.user WHERE USER='user4';
65 --echo
66 CREATE USER user6@localhost IDENTIFIED WITH 'mysql_native_password';
67 query_vertical SELECT user,plugin FROM mysql.user WHERE USER='user6';
68 --echo
69 CREATE USER user7@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y'
70 PASSWORD EXPIRE NEVER ACCOUNT UNLOCK;
71 query_vertical SELECT user,plugin FROM mysql.user WHERE USER='user7';
72 --echo
73 # Testing connection
74 --connect(con1, localhost, user7, 'auth_string#%y')
75 SELECT USER();
76 connection default;
77 disconnect con1;
78
79 CREATE USER user8@localhost IDENTIFIED WITH 'mysql_native_password'
80 AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF';
81 query_vertical SELECT user,plugin FROM mysql.user WHERE USER='user8';
82
83 # Testing connection
84 --connect(con1, localhost, user8,'auth_string')
85 SELECT USER();
86 connection default;
87 disconnect con1;
88
89 # Tesing with mysql_native_password plugin.
90 CREATE USER tu1@localhost IDENTIFIED WITH 'mysql_native_password';
91 query_vertical SELECT user,plugin,password_expired,
92 password_expired FROM mysql.user WHERE USER='tu1';
93
94 CREATE USER tu2@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_@13*';
95 query_vertical SELECT user,plugin,
96 password_expired FROM mysql.user WHERE USER='tu2';
97
98 --echo
99 CREATE USER tu3@localhost IDENTIFIED WITH 'mysql_native_password' BY '%auth_O0s-tring';
100 query_vertical SELECT user,plugin,password_expired
101 FROM mysql.user WHERE USER='tu3';
102
103 --echo
104
105 CREATE USER u5@localhost REQUIRE SSL;
106 query_vertical SELECT user,plugin,ssl_type
107 FROM mysql.user WHERE USER='u5';
108
109 CREATE USER u6@localhost IDENTIFIED BY 'auth_string' REQUIRE X509;
110 query_vertical SELECT user,plugin,ssl_type
111 FROM mysql.user WHERE USER='u6';
112
113 CREATE USER tu4@localhost IDENTIFIED WITH 'mysql_native_password' BY 'djgsj743$' REQUIRE SSL;
114 query_vertical SELECT user,plugin,ssl_type FROM mysql.user WHERE USER='tu4';
115
116 CREATE USER tu5@localhost IDENTIFIED WITH 'mysql_native_password' BY 'dwh@#ghd$!' REQUIRE X509;
117 query_vertical SELECT user,plugin,ssl_type FROM mysql.user WHERE USER='tu5';
118
119 CREATE USER u7@localhost IDENTIFIED WITH 'mysql_native_password'
120 REQUIRE CIPHER 'cipher';
121 query_vertical SELECT user,plugin,ssl_type,ssl_cipher,
122 x509_issuer,x509_subject FROM mysql.user WHERE USER='u7';
123
124 CREATE USER u8@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
125 REQUIRE ISSUER '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA';
126 query_vertical SELECT user,plugin,ssl_type,ssl_cipher,
127 x509_issuer,x509_subject FROM mysql.user WHERE USER='u8';
128
129
130 CREATE USER u9@localhost REQUIRE SUBJECT 'sub';
131 query_vertical SELECT user,plugin,ssl_type,ssl_cipher,
132 x509_issuer,x509_subject FROM mysql.user WHERE USER='u9';
133
134 CREATE USER u10@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#y'
135 REQUIRE CIPHER "ECDHE-RSA-AES128-GCM-SHA256" AND
136 SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
137 ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA";
138 query_vertical SELECT user,plugin,ssl_type,ssl_cipher,
139 x509_issuer,x509_subject FROM mysql.user WHERE USER='u10';
140
141 # Testing connection
142 --connect(con1, localhost, u10,'auth_string#y',,,,SSL)
143 SELECT USER();
144 connection default;
145 disconnect con1;
146
147 CREATE USER tu6@localhost IDENTIFIED WITH 'mysql_native_password' BY '#hGrt0O6'
148 REQUIRE CIPHER "ECDHE-RSA-AES128-GCM-SHA256" AND
149 SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
150 ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
151 WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2;
152 query_vertical SELECT user,plugin,ssl_type,ssl_cipher,x509_issuer,
153 x509_subject,max_questions,max_user_connections FROM mysql.user WHERE USER='tu6';
154
155
156 CREATE USER u11@localhost WITH MAX_QUERIES_PER_HOUR 2;
157 query_vertical SELECT user,plugin,max_questions
158 FROM mysql.user WHERE USER='u11';
159
160 CREATE USER u12@localhost IDENTIFIED BY 'auth_string' WITH MAX_QUERIES_PER_HOUR 2;
161 query_vertical SELECT user,plugin,max_questions
162 FROM mysql.user WHERE USER='u12';
163
164 CREATE USER u13@localhost IDENTIFIED WITH 'mysql_native_password'
165 WITH MAX_CONNECTIONS_PER_HOUR 2;
166 query_vertical SELECT user,plugin,max_connections
167 FROM mysql.user WHERE USER='u13';
168
169 CREATE USER u14@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
170 WITH MAX_USER_CONNECTIONS 2 ACCOUNT LOCK;
171 query_vertical SELECT user,plugin,max_user_connections
172 FROM mysql.user WHERE USER='u14';
173
174 CREATE USER u15@localhost,
175 u16@localhost IDENTIFIED BY 'auth_string',
176 u17@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
177 query_vertical SELECT user,plugin FROM mysql.user WHERE USER='u15';
178 query_vertical SELECT user,plugin FROM mysql.user WHERE USER='u16';
179 query_vertical SELECT user,plugin FROM mysql.user WHERE USER='u17';
180 CREATE USER u18@localhost,
181 u19@localhost IDENTIFIED BY 'auth_string',
182 u20@localhost IDENTIFIED WITH 'mysql_native_password',
183 u21@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string',
184 u22@localhost IDENTIFIED WITH 'mysql_native_password',
185 u23@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_&string'
186 REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
187 WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2 ACCOUNT UNLOCK;
188 query_vertical SELECT user,plugin,ssl_type,ssl_cipher,
189 x509_issuer,x509_subject,max_questions,max_user_connections FROM mysql.user WHERE USER='u18';
190 query_vertical SELECT user,plugin,ssl_type,ssl_cipher,
191 x509_issuer,x509_subject,max_questions,max_user_connections FROM mysql.user WHERE USER='u19';
192 query_vertical SELECT user,plugin,ssl_type,ssl_cipher,
193 x509_issuer,x509_subject,max_questions,max_user_connections FROM mysql.user WHERE USER='u20';
194 query_vertical SELECT user,plugin,ssl_type,ssl_cipher,
195 x509_issuer,x509_subject,max_questions,max_user_connections FROM mysql.user WHERE USER='u22';
196 query_vertical SELECT user,plugin,ssl_type,ssl_cipher,
197 x509_issuer,x509_subject,max_questions,max_user_connections FROM mysql.user WHERE USER='u23';
198
199 DROP USER tu1@localhost,tu2@localhost,tu3@localhost,tu4@localhost,tu5@localhost,
200 tu6@localhost,user1@'%',user2@'%',user4@localhost,user6@localhost,
201 user7@localhost,user8@localhost, u1@localhost,u2@localhost,u3@localhost,
202 u4@localhost,u5@localhost,u6@localhost, u7@localhost, u8@localhost,
203 u9@localhost, u10@localhost,u11@localhost, u12@localhost,u13@localhost,
204 u14@localhost,u15@localhost,u16@localhost,u17@localhost,u18@localhost,
205 u19@localhost, u20@localhost,u21@localhost,u22@localhost,u23@localhost;
206
207 --echo # ALTER USER
208
209 CREATE USER u1@localhost;
210 --replace_column 8 #
211 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
212 plugin,password_expired,
213 password_last_changed,password_lifetime
214 FROM mysql.user WHERE USER='u1';
215
216 --echo # Its a no op
217 ALTER USER u1@localhost;
218 --replace_column 8 #
219 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
220 plugin,password_expired,
221 password_last_changed,password_lifetime
222 FROM mysql.user WHERE USER='u1';
223
224 CREATE USER u2@localhost IDENTIFIED BY 'password_string';
225 --replace_column 8 #
226 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
227 plugin,password_expired,
228 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u2';
229 ALTER USER u2@localhost IDENTIFIED BY 'new_auth_string';
230 # Look for auth_string and password last changed field
231 --replace_column 8 #
232 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
233 plugin,password_expired,
234 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u2';
235 # Testing connection
236 --connect(con1, localhost, u2, new_auth_string)
237 SELECT USER();
238 connection default;
239 disconnect con1;
240 --echo
241 CREATE USER u3@localhost IDENTIFIED WITH 'mysql_native_password' ACCOUNT LOCK;
242 --replace_column 8 #
243 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
244 plugin,password_expired,
245 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u3';
246
247 ALTER USER u3@localhost IDENTIFIED WITH 'mysql_native_password'
248 AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
249 PASSWORD EXPIRE NEVER ACCOUNT UNLOCK;
250 # Look for plugin,auth_string and password expired field
251 --replace_column 8 #
252 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
253 plugin,password_expired,
254 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u3';
255
256 # Testing connection
257 --connect(con1, localhost, u3, 'auth_string')
258 SELECT USER();
259 connection default;
260 disconnect con1;
261
262 ALTER USER u3@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
263 --replace_column 8 #
264 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
265 plugin,password_expired,
266 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u3';
267
268 ALTER USER u3@localhost
269 REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
270 WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2
271 PASSWORD EXPIRE NEVER;
272
273 --replace_column 8 #
274 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
275 plugin,password_expired,
276 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u3';
277
278 CREATE USER u4@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
279 ACCOUNT LOCK PASSWORD EXPIRE DEFAULT;
280 --replace_column 8 #
281 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
282 plugin,password_expired,
283 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u4';
284 ALTER USER u4@localhost IDENTIFIED WITH 'mysql_native_password'
285 BY 'auth_string' ACCOUNT UNLOCK;
286 # Look for plugin,auth_string field
287 --replace_column 8 #
288 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
289 plugin,password_expired,
290 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u4';
291
292 CREATE USER u5@localhost REQUIRE SSL;
293 --replace_column 8 #
294 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
295 plugin,password_expired,
296 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u5';
297
298 ALTER USER u5@localhost IDENTIFIED WITH 'mysql_native_password';
299 # Look for plugin,auth_string, password expired field
300 --replace_column 8 #
301 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
302 plugin,password_expired,
303 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u5';
304
305 # Testing connection
306 --connect(con1, localhost, u5,,,,,SSL)
307 SET PASSWORD='new_auth_string';
308 SELECT USER();
309 connection default;
310 disconnect con1;
311
312 CREATE USER u6@localhost IDENTIFIED BY 'auth_string' REQUIRE X509 ACCOUNT UNLOCK ACCOUNT LOCK PASSWORD EXPIRE INTERVAL 80 DAY;
313 --replace_column 8 #
314 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
315 plugin,password_expired,
316 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u6';
317 ALTER USER u6@localhost IDENTIFIED WITH 'mysql_native_password' BY 'new_auth_string' REQUIRE SSL ACCOUNT UNLOCK;
318 --replace_column 8 #
319 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
320 plugin,password_expired,
321 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u6';
322
323 CREATE USER u7@localhost IDENTIFIED WITH 'mysql_native_password'
324 BY 'auth_string' REQUIRE CIPHER 'ECDHE-RSA-AES128-GCM-SHA256';
325 --replace_column 8 #
326 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
327 plugin,password_expired,
328 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u7';
329 ALTER USER u7@localhost IDENTIFIED WITH 'mysql_native_password' BY 'new_auth_string'
330 REQUIRE ISSUER '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA' ACCOUNT UNLOCK;
331 # Look for plugin,auth_string, password expired, SSL type field
332 --replace_column 8 #
333 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
334 plugin,password_expired,
335 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u7';
336
337 # Testing connection
338 --connect(con1, localhost, u7,'new_auth_string',,,,SSL)
339 SELECT USER();
340 connection default;
341 disconnect con1;
342
343 CREATE USER u8@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
344 REQUIRE ISSUER '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA';
345 --replace_column 8 #
346 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
347 plugin,password_expired,
348 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u8';
349 ALTER USER u8@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
350 REQUIRE CIPHER "ECDHE-RSA-AES128-GCM-SHA256";
351 # Look for plugin,auth_string, password expired, SSL fields
352 --replace_column 8 #
353 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
354 plugin,password_expired,
355 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u8';
356 # Testing connection
357 --connect(con1, localhost, u8,'auth_string',,,,SSL)
358 SELECT USER();
359 connection default;
360 disconnect con1;
361
362 CREATE USER tu1@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
363 REQUIRE ISSUER 'issuer';
364 --replace_column 8 #
365 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
366 plugin,password_expired,
367 password_last_changed,password_lifetime FROM mysql.user WHERE USER='tu1';
368 ALTER USER tu1@localhost IDENTIFIED WITH 'mysql_native_password'
369 REQUIRE CIPHER "ECDHE-RSA-AES128-GCM-SHA256";
370 # Look for plugin,auth_string, password expired, SSL fields
371 --replace_column 8 #
372 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
373 plugin,password_expired,
374 password_last_changed,password_lifetime FROM mysql.user WHERE USER='tu1';
375
376 CREATE USER u9@localhost REQUIRE SUBJECT 'sub';
377 --replace_column 8 #
378 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
379 plugin,password_expired,
380 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u9';
381 ALTER USER u9@localhost REQUIRE ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA";
382 --replace_column 8 #
383 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
384 plugin,password_expired,
385 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u9';
386
387 ALTER USER u9@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string77hg'
388 REQUIRE ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA";
389 --replace_column 8 #
390 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
391 plugin,password_expired,
392 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u9';
393
394 CREATE USER u10@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
395 REQUIRE CIPHER "ECDHE-RSA-AES128-GCM-SHA256" AND
396 SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
397 ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA";
398 --replace_column 8 #
399 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
400 plugin,password_expired,
401 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u10';
402 ALTER USER u10@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
403 REQUIRE SSL;
404 --replace_column 8 #
405 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
406 plugin,password_expired,
407 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u10';
408
409 # Testing connection
410 --connect(con1, localhost, u10,'auth_string',,,,SSL)
411 SELECT USER();
412 connection default;
413 disconnect con1;
414
415 CREATE USER u11@localhost WITH MAX_QUERIES_PER_HOUR 2;
416 --replace_column 8 #
417 query_vertical SELECT user,max_questions FROM mysql.user WHERE USER='u11';
418 ALTER USER u11@localhost WITH MAX_QUERIES_PER_HOUR 6;
419 --replace_column 8 #
420 query_vertical SELECT user,max_questions FROM mysql.user WHERE USER='u11';
421
422 CREATE USER u12@localhost IDENTIFIED BY 'auth_string'
423 WITH MAX_QUERIES_PER_HOUR 2;
424 --replace_column 8 #
425 query_vertical SELECT user,max_questions FROM mysql.user WHERE USER='u12';
426 ALTER USER u12@localhost IDENTIFIED WITH 'mysql_native_password'
427 WITH MAX_QUERIES_PER_HOUR 8;
428 --replace_column 8 #
429 query_vertical SELECT user,max_questions FROM mysql.user WHERE USER='u12';
430
431 ALTER USER u12@localhost IDENTIFIED WITH 'mysql_native_password'
432 WITH MAX_QUERIES_PER_HOUR 1000;
433 --replace_column 8 #
434 query_vertical SELECT user,max_questions FROM mysql.user WHERE USER='u12';
435
436 CREATE USER u13@localhost IDENTIFIED WITH 'mysql_native_password'
437 WITH MAX_CONNECTIONS_PER_HOUR 2;
438 --replace_column 8 #
439 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
440 plugin,password_expired,
441 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u13';
442 ALTER USER u13@localhost PASSWORD EXPIRE;
443 --replace_column 8 #
444 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
445 plugin,password_expired,
446 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u13';
447
448 CREATE USER u14@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
449 WITH MAX_USER_CONNECTIONS 2;
450 --replace_column 9 #
451 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
452 plugin,password_expired,max_user_connections,
453 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u14';
454 ALTER USER u14@localhost WITH MAX_USER_CONNECTIONS 12
455 PASSWORD EXPIRE INTERVAL 365 DAY;
456 --replace_column 9 #
457 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
458 plugin,password_expired,max_user_connections,
459 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u14';
460
461 CREATE USER tu2@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
462 WITH MAX_USER_CONNECTIONS 2 ;
463 --replace_column 10 #
464 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
465 plugin,password_expired,max_user_connections,max_questions,
466 password_last_changed,password_lifetime FROM mysql.user WHERE USER='tu2';
467 ALTER USER tu2@localhost WITH MAX_USER_CONNECTIONS 12 MAX_QUERIES_PER_HOUR 543
468 PASSWORD EXPIRE INTERVAL 365 DAY;
469 --replace_column 10 #
470 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
471 plugin,password_expired,max_user_connections,max_questions,
472 password_last_changed,password_lifetime FROM mysql.user WHERE USER='tu2';
473
474 # Testing connection
475 --connect(con1, localhost, tu2,'auth_string')
476 SELECT USER();
477 connection default;
478 disconnect con1;
479
480 CREATE USER u15@localhost,
481 u16@localhost IDENTIFIED WITH 'mysql_native_password',
482 u17@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
483 --replace_column 8 #
484 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
485 plugin,password_expired,
486 password_last_changed,password_lifetime FROM mysql.user
487 WHERE USER='u15';
488 --replace_column 8 #
489 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
490 plugin,password_expired,
491 password_last_changed,password_lifetime FROM mysql.user
492 WHERE USER='u16';
493 --replace_column 8 #
494 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
495 plugin,password_expired,
496 password_last_changed,password_lifetime FROM mysql.user
497 WHERE USER='u17';
498 ALTER USER u15@localhost IDENTIFIED WITH 'mysql_native_password',
499 u16@localhost,
500 u17@localhost IDENTIFIED BY 'new_auth_string'
501 PASSWORD EXPIRE DEFAULT;
502 --replace_column 8 #
503 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
504 plugin,password_expired,
505 password_last_changed,password_lifetime FROM mysql.user
506 WHERE USER='u15';
507 --replace_column 8 #
508 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
509 plugin,password_expired,
510 password_last_changed,password_lifetime FROM mysql.user
511 WHERE USER='u16';
512 --replace_column 8 #
513 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
514 plugin,password_expired,
515 password_last_changed,password_lifetime FROM mysql.user
516 WHERE USER='u17';
517
518 CREATE USER u18@localhost,
519 u19@localhost IDENTIFIED BY 'auth_string',
520 u20@localhost IDENTIFIED WITH 'mysql_native_password',
521 u21@localhost IDENTIFIED WITH 'mysql_native_password' BY '!Y_TOdh6)',
522 u22@localhost IDENTIFIED WITH 'mysql_native_password',
523 u23@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_&string'
524 REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
525 WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2;
526 --replace_column 10 #
527 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
528 plugin,password_expired,max_user_connections,
529 max_questions,password_last_changed,password_lifetime FROM mysql.user
530 WHERE USER='u18';
531 --replace_column 10 #
532 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
533 plugin,password_expired,max_user_connections,
534 max_questions,password_last_changed,password_lifetime FROM mysql.user
535 WHERE USER='u19';
536 --replace_column 10 #
537 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
538 plugin,password_expired,max_user_connections,
539 max_questions,password_last_changed,password_lifetime FROM mysql.user
540 WHERE USER='u20';
541 --replace_column 10 #
542 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
543 plugin,password_expired,max_user_connections,
544 max_questions,password_last_changed,password_lifetime FROM mysql.user
545 WHERE USER='u21';
546 --replace_column 10 #
547 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
548 plugin,password_expired,max_user_connections,
549 max_questions,password_last_changed,password_lifetime FROM mysql.user
550 WHERE USER='u22';
551 --replace_column 10 #
552 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
553 plugin,password_expired,max_user_connections,
554 max_questions,password_last_changed,password_lifetime FROM mysql.user
555 WHERE USER='u23';
556
557 ALTER USER u18@localhost, u19@localhost,
558 u20@localhost, u21@localhost,
559 u22@localhost, u23@localhost
560 REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
561 WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2
562 PASSWORD EXPIRE NEVER;
563 --replace_column 10 #
564 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
565 plugin,password_expired,max_user_connections,
566 max_questions,password_last_changed,password_lifetime FROM mysql.user
567 WHERE USER='u18';
568 --replace_column 10 #
569 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
570 plugin,password_expired,max_user_connections,
571 max_questions,password_last_changed,password_lifetime FROM mysql.user
572 WHERE USER='u19';
573 --replace_column 10 #
574 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
575 plugin,password_expired,max_user_connections,
576 max_questions,password_last_changed,password_lifetime FROM mysql.user
577 WHERE USER='u20';
578 --replace_column 10 #
579 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
580 plugin,password_expired,max_user_connections,
581 max_questions,password_last_changed,password_lifetime FROM mysql.user
582 WHERE USER='u21';
583 --replace_column 10 #
584 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
585 plugin,password_expired,max_user_connections,
586 max_questions,password_last_changed,password_lifetime FROM mysql.user
587 WHERE USER='u22';
588 --replace_column 10 #
589 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
590 plugin,password_expired,max_user_connections,
591 max_questions,password_last_changed,password_lifetime FROM mysql.user
592 WHERE USER='u23';
593
594 DROP USER tu1@localhost,tu2@localhost,u1@localhost, u2@localhost, u3@localhost,
595 u4@localhost,u5@localhost,u6@localhost, u7@localhost, u8@localhost,
596 u9@localhost,u10@localhost,u11@localhost,u12@localhost,u13@localhost,
597 u14@localhost,u15@localhost,u16@localhost,u17@localhost,u18@localhost,
598 u19@localhost,u20@localhost,u21@localhost,u22@localhost,u23@localhost;
599
600 --echo # ALTER USER with current user
601
602 CREATE USER u1@localhost IDENTIFIED BY 'abc';
603 --connect(con1, localhost, u1, abc)
604 SELECT USER();
605 connection default;
606 ALTER USER u1@localhost PASSWORD EXPIRE;
607 disconnect con1;
608
609 --connect(con1, localhost, u1, abc)
610 --error ER_MUST_CHANGE_PASSWORD
611 SELECT USER();
612 SET PASSWORD = 'def';
613 --disable_warnings
614 disconnect con1;
615 --enable_warnings
616
617 --connect(con1, localhost, u1, def)
618 SELECT USER();
619 connection default;
620 ALTER USER u1@localhost PASSWORD EXPIRE;
621 disconnect con1;
622
623 --connect(con1, localhost, u1, def)
624 --error ER_MUST_CHANGE_PASSWORD
625 SELECT USER();
626 # Password set to current user
627 --disable_ps_protocol
628 --error ER_PARSE_ERROR
629 ALTER USER IDENTIFIED BY 'npwd';
630 ALTER USER USER() IDENTIFIED BY 'abc';
631 --enable_ps_protocol
632 disconnect con1;
633
634 --connect(con1, localhost, u1, abc)
635 SELECT USER();
636 connection default;
637 ALTER USER u1@localhost PASSWORD EXPIRE;
638 disconnect con1;
639
640 --connect(con1, localhost, u1, abc)
641 --error ER_MUST_CHANGE_PASSWORD
642 SELECT USER();
643 connection default;
644 ALTER USER u1@localhost IDENTIFIED BY 'def';
645 disconnect con1;
646
647 --connect(con1, localhost, u1, def)
648 SELECT USER();
649 connection default;
650 DROP USER u1@localhost;
651 disconnect con1;
652
653 --echo # ALTER USER with current user is allowed to set only credential information
654
655 CREATE USER u1@localhost, u2@localhost IDENTIFIED BY 'abc';
656 GRANT ALL ON *.* TO u2@localhost;
657
658 --connect(con1, localhost, u2, abc)
659 --error ER_PARSE_ERROR
660 ALTER USER USER() IDENTIFIED WITH 'sha256_password';
661 --error ER_PARSE_ERROR
662 ALTER USER USER() IDENTIFIED BY 'def', u2@localhost PASSWORD EXPIRE;
663 --error ER_PARSE_ERROR
664 ALTER USER USER() IDENTIFIED BY 'def' PASSWORD EXPIRE;
665 --error ER_PARSE_ERROR
666 ALTER USER ;
667 ALTER USER u2@localhost IDENTIFIED BY 'auth_string' PASSWORD EXPIRE INTERVAL 45 DAY;
668 --replace_column 8 #
669 query_vertical SELECT user,ssl_type,ssl_cipher,x509_issuer,x509_subject,
670 plugin,password_expired,
671 password_last_changed,password_lifetime FROM mysql.user WHERE USER='u2';
672 connection default;
673 disconnect con1;
674 DROP USER u1@localhost, u2@localhost;
675
676 --echo # SHOW CREATE USER
677
678 CREATE USER u1@localhost;
679 SHOW CREATE USER u1@localhost;
680
681 CREATE USER u2@localhost IDENTIFIED BY 'auth_string';
682 --replace_regex /AS '(.*)' REQUIRE/AS '<non-deterministic-password-hash>' REQUIRE/
683 SHOW CREATE USER u2@localhost;
684 --echo # DDL output for SHOW CREATE USER u2@localhost and recreating user
685 DROP USER u2@localhost;
686 CREATE USER 'u2'@'localhost' IDENTIFIED WITH 'mysql_native_password'
687 AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF' REQUIRE NONE;
688
689 --connect(con1,localhost,u2,auth_string)
690 SELECT USER();
691 connection default;
692 disconnect con1;
693 --echo
694
695 CREATE USER u3@localhost IDENTIFIED WITH 'mysql_native_password';
696 SHOW CREATE USER u3@localhost;
697
698 CREATE USER u4@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
699 --replace_regex /AS '(.*)' REQUIRE/AS '<non-deterministic-password-hash>' REQUIRE/
700 SHOW CREATE USER u4@localhost;
701
702 CREATE USER user1@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
703 --replace_regex /AS '(.*)' REQUIRE/AS '<non-deterministic-password-hash>' REQUIRE/
704 SHOW CREATE USER user1@localhost;
705
706 --echo
707
708 CREATE USER user2@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
709 --replace_regex /AS '(.*)' REQUIRE/AS '<non-deterministic-password-hash>' REQUIRE/
710 SHOW CREATE USER user2@localhost;
711 --echo
712 CREATE USER u5@localhost REQUIRE SSL;
713 SHOW CREATE USER u5@localhost;
714
715 CREATE USER u6@localhost IDENTIFIED BY 'auth_string' REQUIRE X509;
716 --replace_regex /AS '(.*)' REQUIRE/AS '<non-deterministic-password-hash>' REQUIRE/
717 SHOW CREATE USER u6@localhost;
718
719 CREATE USER u7@localhost IDENTIFIED WITH 'mysql_native_password'
720 REQUIRE CIPHER 'cipher';
721 SHOW CREATE USER u7@localhost;
722
723 CREATE USER u8@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
724 REQUIRE ISSUER 'issuer';
725 --replace_regex /AS '(.*)' REQUIRE/AS '<non-deterministic-password-hash>' REQUIRE/
726 SHOW CREATE USER u8@localhost;
727
728 CREATE USER u9@localhost REQUIRE SUBJECT 'sub';
729 SHOW CREATE USER u9@localhost;
730
731 CREATE USER u10@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
732 REQUIRE CIPHER "ECDHE-RSA-AES128-GCM-SHA256" AND
733 SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client"
734 ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA";
735 --replace_regex /AS '(.*)' REQUIRE/AS '<non-deterministic-password-hash>' REQUIRE/
736 SHOW CREATE USER u10@localhost;
737
738 CREATE USER u11@localhost WITH MAX_QUERIES_PER_HOUR 2;
739 SHOW CREATE USER u11@localhost;
740
741 CREATE USER u12@localhost IDENTIFIED BY 'auth_string' WITH MAX_QUERIES_PER_HOUR 2;
742 --replace_regex /AS '(.*)' REQUIRE/AS '<non-deterministic-password-hash>' REQUIRE/
743 SHOW CREATE USER u12@localhost;
744
745 CREATE USER u13@localhost IDENTIFIED WITH 'mysql_native_password'
746 WITH MAX_CONNECTIONS_PER_HOUR 2;
747 SHOW CREATE USER u13@localhost;
748
749 CREATE USER u14@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
750 WITH MAX_USER_CONNECTIONS 2;
751 --replace_regex /AS '(.*)' REQUIRE/AS '<non-deterministic-password-hash>' REQUIRE/
752 SHOW CREATE USER u14@localhost;
753
754 CREATE USER u15@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string'
755 REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
756 ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
757 CIPHER 'cipher' WITH MAX_QUERIES_PER_HOUR 2 MAX_USER_CONNECTIONS 2;
758 --replace_regex /AS '(.*)' REQUIRE/AS '<non-deterministic-password-hash>' REQUIRE/
759 SHOW CREATE USER u15@localhost;
760
761 DROP USER user1@localhost,u1@localhost,u2@localhost,u3@localhost,u4@localhost,
762 u5@localhost,u6@localhost, u7@localhost, u8@localhost,u9@localhost,
763 u10@localhost,u11@localhost,u12@localhost,u13@localhost,u14@localhost,
764 u15@localhost;
765 --echo
766 --echo # GRANT
767 --echo
768 CREATE USER user1@localhost IDENTIFIED WITH 'mysql_native_password'
769 AS '*67092806AE91BFB6BE72DE6C7BE2B7CCA8CFA9DF'
770 REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
771 ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
772 WITH MAX_CONNECTIONS_PER_HOUR 1000 MAX_USER_CONNECTIONS 20
773 MAX_QUERIES_PER_HOUR 60 MAX_UPDATES_PER_HOUR 100;
774
775 query_vertical SELECT user,plugin,ssl_type,ssl_cipher,
776 x509_issuer,x509_subject,max_user_connections,
777 max_questions,max_updates,max_connections FROM mysql.user
778 WHERE USER='user1';
779
780 --echo
781 GRANT SELECT ON *.* TO user1@localhost;
782 GRANT SELECT,INSERT,UPDATE ON mysql.* TO user1@localhost;
783
784 --echo
785 SHOW GRANTS FOR user1@localhost;
786 --echo
787 REVOKE INSERT,UPDATE ON mysql.* FROM user1@localhost;
788
789 SHOW GRANTS FOR user1@localhost;
790 --echo
791
792 GRANT SELECT,INSERT,UPDATE ON *.* TO user2@localhost;
793 ALTER USER user2@localhost IDENTIFIED BY 'admin'
794 REQUIRE SUBJECT '/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client'
795 ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA"
796 WITH MAX_CONNECTIONS_PER_HOUR 1000 MAX_USER_CONNECTIONS 20
797 MAX_QUERIES_PER_HOUR 60 MAX_UPDATES_PER_HOUR 100;
798
799 query_vertical SELECT user,plugin,ssl_type,ssl_cipher,
800 x509_issuer,x509_subject,max_user_connections,
801 max_questions,max_updates,max_connections FROM mysql.user WHERE USER='user2';
802
803 CREATE USER user3@localhost IDENTIFIED BY 'auth_string';
804
805 --echo # Cleanup
806 DROP USER user1@localhost,user2@localhost,user3@localhost;
807
808 --echo
809 --echo # SET PASSWORD Tests
810 --echo
811 CREATE USER user1@localhost IDENTIFIED BY 'auth_string';
812 CREATE USER user2@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string1';
813
814 SET PASSWORD FOR user1@localhost='auth_xyz@';
815 SET PASSWORD FOR user2@localhost='gd636@gj';
816
817 --echo
818 CREATE USER user4@localhost;
819 ALTER USER user4@localhost PASSWORD EXPIRE;
820 SET PASSWORD FOR user4@localhost='';
821
822 CREATE USER user3@localhost IDENTIFIED BY 'auth_string';
823 CREATE USER user5@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string1';
824
825 SET PASSWORD FOR user3@localhost='auth_abc';
826 GRANT SELECT ON *.* TO user3@localhost;
827
828 --connect(con1, localhost, user3, auth_abc)
829 SELECT USER();
830 SET PASSWORD='auth_xyz';
831 query_vertical SELECT user,plugin,ssl_type,password_expired,
832 password_lifetime FROM mysql.user WHERE user='user3';
833 connection default;
834 disconnect con1;
835
836 --echo
837 SET PASSWORD FOR user4@localhost='auth_xyz';
838 query_vertical SELECT user,plugin,ssl_type,password_expired,
839 password_lifetime FROM mysql.user WHERE user='user4';
840 --echo
841 SET PASSWORD FOR user5@localhost='auth_dhsga5';
842 query_vertical SELECT user,plugin,ssl_type,password_expired,
843 password_lifetime FROM mysql.user WHERE user='user5';
844
845 --echo
846 CREATE USER user6@localhost IDENTIFIED BY 'auth_string';
847 query_vertical SELECT user,plugin,password_expired
848 FROM mysql.user WHERE USER='user6';
849
850 ALTER USER user6@localhost IDENTIFIED WITH 'mysql_native_password';
851 query_vertical SELECT user,plugin,password_expired
852 FROM mysql.user WHERE USER='user6';
853
854 SET PASSWORD FOR user6@localhost='plaintext_password';
855 query_vertical SELECT user,plugin
856 FROM mysql.user WHERE USER='user6';
857
858 # Testing connection
859 --connect(con1, localhost, user6, 'plaintext_password')
860 SELECT USER();
861 connection default;
862 disconnect con1;
863
864 --echo
865 --echo # Cleanup
866 DROP USER user1@localhost,user2@localhost,user3@localhost,
867 user4@localhost,user5@localhost,user6@localhost;
868
869 --source include/sync_slave_sql_with_master.inc
870 --echo [On Slave]
871 --let $mask_user_password_events=1
872 --let $mask_grant_as_events=1
873 --source include/show_binlog_events.inc
874 --let $mask_user_password_events=0
875 --source include/rpl_end.inc
876
877 --echo
878 --echo End of 5.7 tests!
879 --echo
880