Debian Codegraph @

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

raw
1 ###############################################################################
2 # #
3 # Authorization and authentication of the key-value pair shared between #
4 # the server and the client #
5 # #
6 # #
7 # Creation Date: 2012-12-1 #
8 # Author : Tanjot Singh Uppal #
9 # #
10 # #
11 # Description:Test Cases of authenticating the key value pair shared #
12 # between the client and server during the handshake #
13 # #
14 ###############################################################################
15
16 --source include/mysql_upgrade_preparation.inc
17
18 # This test will intentionally generate errors in the server error log
19 # when a broken password is inserted into the mysql.user table.
20 # The below suppression is to clear those errors.
21 --disable_query_log
22 call mtr.add_suppression(".*Password salt for user.*");
23 --enable_query_log
24
25 --echo
26 --echo
27 --echo =======================================================================
28 --echo Checking the password authentication with @default_plugin plugin
29 --echo =======================================================================
30 --echo
31
32 # Checking the password authentication with @default_plugin plugin
33 # Creating 2 users and encrypting the server side password with the default @default_plugin plugin
34
35 set @default_plugin= @@global.default_authentication_plugin;
36
37 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
38
39 CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED BY 'abc';
40
41 # Verifying the default server side plugin used to be @default_plugin
42
43 select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')=@default_plugin;
44 --echo 1 Expected
45
46 select (select plugin from mysql.user where User='Tanjotuser2' and Host='127.0.0.1')=@default_plugin;
47 --echo 1 Expected
48
49 # Verifying the client connection using the same @default_plugin
50
51 connect(con1,localhost,Tanjotuser1,abc,,,,SSL);
52 connect(con2,127.0.0.1,Tanjotuser2,abc,,,,SSL);
53
54
55 # Verifying the client access denied passing null password
56
57 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
58 --disable_query_log
59 --error ER_ACCESS_DENIED_ERROR
60 connect(con3,localhost,Tanjotuser1,,,);
61 --enable_query_log
62
63 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
64 --disable_query_log
65 --error ER_ACCESS_DENIED_ERROR
66 connect(con4,127.0.0.1,Tanjotuser2,,,);
67 --enable_query_log
68
69 # Verifying the client access denied passing blank password
70
71 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
72 --disable_query_log
73 --error ER_ACCESS_DENIED_ERROR
74 connect(con5,localhost,Tanjotuser1, ,,);
75 --enable_query_log
76
77 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
78 --disable_query_log
79 --error ER_ACCESS_DENIED_ERROR
80 connect(con6,127.0.0.1,Tanjotuser2, ,,);
81 --enable_query_log
82
83 # Dropping the created users
84
85 connection default;
86
87 DROP USER 'Tanjotuser1'@'localhost';
88 DROP USER 'Tanjotuser2'@'127.0.0.1';
89
90 --echo
91 --echo
92 --echo =======================================================================
93 --echo Checking the password authentication with sha256_password plugin
94 --echo =======================================================================
95 --echo
96
97 # Checking the password authentication with sha256_password plugin
98 # Creating 2 users and encrypting the server side password with the sha256_password plugin
99
100
101 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password';
102
103 CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password';
104
105 # Verifying the server side plugin used to be sha256_password
106
107 select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='sha256_password';
108 --echo 1 Expected
109
110 select (select plugin from mysql.user where User='Tanjotuser2' and Host='127.0.0.1')='sha256_password';
111 --echo 1 Expected
112
113 # Verifying the client connection using the same sha256_password plugin and NULL password
114
115
116 connect(con19,localhost,Tanjotuser1,,,);
117 connect(con20,127.0.0.1,Tanjotuser2,,,);
118
119 # Verifying the client connection using the same sha256_password plugin and junk password
120
121 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
122 --disable_query_log
123 --error ER_ACCESS_DENIED_ERROR
124 connect(con21,localhost,Tanjotuser1," ",,);
125 --enable_query_log
126
127 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
128 --disable_query_log
129 --error ER_ACCESS_DENIED_ERROR
130 connect(con22,127.0.0.1,Tanjotuser2," ",,);
131 --enable_query_log
132
133 # setting the password for the 2 users using the sha256_password plugin
134
135 connection default;
136
137 SET PASSWORD FOR 'Tanjotuser1'@'localhost'='abc';
138 SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'='abc';
139
140 # Verifying the client connection using the same sha256_password plugin and NULL password
141
142 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
143 --disable_query_log
144 --error ER_ACCESS_DENIED_ERROR
145 connect(con23,localhost,Tanjotuser1,,,);
146 --enable_query_log
147
148 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
149 --disable_query_log
150 --error ER_ACCESS_DENIED_ERROR
151 connect(con24,127.0.0.1,Tanjotuser2,,,);
152 --enable_query_log
153
154 # Verifying the client connection using the same sha256_password plugin and junk password
155
156 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
157 --disable_query_log
158 --error ER_ACCESS_DENIED_ERROR
159 connect(con25,localhost,Tanjotuser1," ",,);
160 --enable_query_log
161
162 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
163 --disable_query_log
164 --error ER_ACCESS_DENIED_ERROR
165 connect(con26,127.0.0.1,Tanjotuser2," ",,);
166 --enable_query_log
167
168 # Verifying the client connection using the same sha256_password plugin and correct password
169
170 --disable_warnings
171 connect(con27,localhost,Tanjotuser1,abc,,);
172 connect(con28,127.0.0.1,Tanjotuser2,abc,,);
173 --enable_warnings
174
175 # Dropping the created users
176
177 connection default;
178
179 DROP USER 'Tanjotuser1'@'localhost';
180 DROP USER 'Tanjotuser2'@'127.0.0.1';
181
182 # Disconnecting the above connections
183
184 --disable_query_log
185
186 disconnect con1;
187 disconnect con2;
188 #disconnect con3;
189 #disconnect con4;
190 #disconnect con5;
191 #disconnect con6;
192 disconnect con19;
193 disconnect con20;
194 #disconnect con21;
195 #disconnect con22;
196 #disconnect con23;
197 #disconnect con24;
198 #disconnect con25;
199 #disconnect con26;
200 disconnect con27;
201 disconnect con28;
202
203 --enable_query_log
204
205
206 --echo
207 --echo
208 --echo ====================================================================================================
209 --echo checking the integrity of long password (more than 40 chars) using @default_plugin plugin
210 --echo ====================================================================================================
211 --echo
212
213 # Checking the password authentication with @default_plugin plugin
214 # Creating 2 users and encrypting the server side password with the default @default_plugin plugin
215
216 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222';
217
218 CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED BY 'aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222';
219
220 # Verifying the client connection using the same @default_plugin
221
222 --disable_warnings
223 connect(con29,localhost,Tanjotuser1,aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222,,,,SSL);
224 connect(con30,127.0.0.1,Tanjotuser2,aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222,,,,SSL);
225 --enable_warnings
226
227 # Verifying the client access denied passing null password
228
229 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
230 --disable_query_log
231 --error ER_ACCESS_DENIED_ERROR
232 connect(con31,localhost,Tanjotuser1,,,);
233 --enable_query_log
234
235 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
236 --disable_query_log
237 --error ER_ACCESS_DENIED_ERROR
238 connect(con32,127.0.0.1,Tanjotuser2,,,);
239 --enable_query_log
240
241
242 # Dropping the created users
243
244 connection default;
245
246 DROP USER 'Tanjotuser1'@'localhost';
247 DROP USER 'Tanjotuser2'@'127.0.0.1';
248
249 --echo
250 --echo
251 --echo ====================================================================================================
252 --echo checking the integrity of long password (more than 40 chars) using sha256_password plugin
253 --echo ====================================================================================================
254 --echo
255
256 # Checking the password authentication with sha256_password plugin
257 # Creating 2 users and encrypting the server side password with the sha256_password plugin
258
259
260 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password';
261
262 CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password';
263
264
265 # setting the password for the 2 users using the sha256_password plugin
266
267 SET PASSWORD FOR 'Tanjotuser1'@'localhost'='aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222';
268 SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'='aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222';
269
270
271 # Verifying the client connection using the same sha256_password plugin and correct password
272
273 --disable_warnings
274 connect(con37,localhost,Tanjotuser1,aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222,,);
275 connect(con38,127.0.0.1,Tanjotuser2,aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222,,);
276 --enable_warnings
277
278 # Verifying the client connection using the same sha256_password plugin and NULL password
279
280 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
281 --disable_query_log
282 --error ER_ACCESS_DENIED_ERROR
283 connect(con39,localhost,Tanjotuser1,,,);
284 --enable_query_log
285
286 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
287 --disable_query_log
288 --error ER_ACCESS_DENIED_ERROR
289 connect(con40,127.0.0.1,Tanjotuser2,,,);
290 --enable_query_log
291
292 # Dropping the created users
293
294 connection default;
295
296 DROP USER 'Tanjotuser1'@'localhost';
297 DROP USER 'Tanjotuser2'@'127.0.0.1';
298
299
300 --echo
301 --echo
302 --echo ====================================================================================================
303 --echo checking the integrity of NULL password using @default_plugin plugin
304 --echo ====================================================================================================
305 --echo
306
307 # Checking the password authentication with @default_plugin plugin
308 # Creating 2 users and encrypting the server side password with the default @default_plugin plugin
309
310 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY '';
311
312 CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED BY '';
313
314 # Verifying the client connection using the null password
315
316 connect(con41,localhost,Tanjotuser1,,,);
317 connect(con42,127.0.0.1,Tanjotuser2,,,);
318
319 # Verifying the client access denied passing not null password
320
321 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
322 --disable_query_log
323 --error ER_ACCESS_DENIED_ERROR
324 connect(con43,localhost,Tanjotuser1," ",,,,SSL);
325 --enable_query_log
326
327 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
328 --disable_query_log
329 --error ER_ACCESS_DENIED_ERROR
330 connect(con44,127.0.0.1,Tanjotuser2," ",,,,SSL);
331 --enable_query_log
332
333
334 # Verifying the client access denied passing not null password
335
336 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
337 --disable_query_log
338 --error ER_ACCESS_DENIED_ERROR
339 connect(con45,localhost,Tanjotuser1,NULL,,,,SSL);
340 --enable_query_log
341
342 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
343 --disable_query_log
344 --error ER_ACCESS_DENIED_ERROR
345 connect(con46,127.0.0.1,Tanjotuser2,NULL,,,,SSL);
346 --enable_query_log
347
348
349 # Verifying the client access denied passing not null password
350
351 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
352 --disable_query_log
353 --error ER_ACCESS_DENIED_ERROR
354 connect(con47,localhost,Tanjotuser1,0,,,,SSL);
355 --enable_query_log
356
357 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
358 --disable_query_log
359 --error ER_ACCESS_DENIED_ERROR
360 connect(con48,127.0.0.1,Tanjotuser2,0,,,,SSL);
361 --enable_query_log
362
363
364 # Dropping the created users
365
366 connection default;
367
368 DROP USER 'Tanjotuser1'@'localhost';
369 DROP USER 'Tanjotuser2'@'127.0.0.1';
370
371
372 --echo
373 --echo
374 --echo ====================================================================================================
375 --echo checking the integrity of NULL password using sha256_password plugin
376 --echo ====================================================================================================
377 --echo
378
379 # Checking the password authentication with sha256_password plugin
380 # Creating 2 users and encrypting the server side password with the sha256_password plugin
381
382
383 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password';
384
385 CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password';
386
387 # setting the NULL password for the 2 users using the sha256_password plugin
388
389
390 SET PASSWORD FOR 'Tanjotuser1'@'localhost'='';
391 SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'='';
392
393
394 # Verifying the client connection using the null password
395
396 connect(con57,localhost,Tanjotuser1,,,);
397 connect(con58,127.0.0.1,Tanjotuser2,,,);
398
399
400 # Verifying the client connection using the sha256_password plugin and not NULL password
401
402 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
403 --disable_query_log
404 --error ER_ACCESS_DENIED_ERROR
405 connect(con59,localhost,Tanjotuser1," ",,);
406 --enable_query_log
407
408 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
409 --disable_query_log
410 --error ER_ACCESS_DENIED_ERROR
411 connect(con60,127.0.0.1,Tanjotuser2," ",,);
412 --enable_query_log
413
414 # Verifying the client connection using the sha256_password plugin and not NULL password
415
416 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
417 --disable_query_log
418 --error ER_ACCESS_DENIED_ERROR
419 connect(con61,localhost,Tanjotuser1,0,,);
420 --enable_query_log
421
422 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
423 --disable_query_log
424 --error ER_ACCESS_DENIED_ERROR
425 connect(con62,127.0.0.1,Tanjotuser2,0,,);
426 --enable_query_log
427
428
429 # Dropping the created users
430
431 connection default;
432
433 DROP USER 'Tanjotuser1'@'localhost';
434 DROP USER 'Tanjotuser2'@'127.0.0.1';
435
436
437 # Disconnecting the above connections
438
439 --disable_query_log
440
441 disconnect con29;
442 disconnect con30;
443 #disconnect con31;
444 #disconnect con32;
445 disconnect con37;
446 disconnect con38;
447 #disconnect con39;
448 #disconnect con40;
449 disconnect con41;
450 disconnect con42;
451 #disconnect con43;
452 #disconnect con44;
453 #disconnect con45;
454 #disconnect con46;
455 #disconnect con47;
456 #disconnect con48;
457 disconnect con57;
458 disconnect con58;
459 #disconnect con59;
460 #disconnect con60;
461 #disconnect con61;
462 #disconnect con62;
463
464 --enable_query_log
465
466 --echo
467 --echo
468 --echo =================================================================================================
469 --echo checking the combination of Client default_auth=sha256_password and server default_auth=native
470 --echo =================================================================================================
471 --echo
472
473 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password';
474 CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password';
475
476 SET PASSWORD FOR 'Tanjotuser1'@'localhost'='abc';
477 SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'='abc';
478
479 GRANT ALL ON *.* TO 'Tanjotuser1'@'localhost';
480 GRANT ALL ON *.* TO 'Tanjotuser2'@'127.0.0.1';
481
482 --echo **** Client default_auth=sha_256_password and server default auth=native
483 --exec $MYSQL -uTanjotuser1 -hlocalhost -pabc --default_auth=sha256_password -e "select user(), current_user()"
484 --echo **** Client default_auth=native and server default auth=native
485 --exec $MYSQL -uTanjotuser2 -h127.0.0.1 -pabc --default_auth=mysql_native_password -e "select user(), current_user()"
486 --echo **** Client default_auth=sha_256_password + public key on file.
487 --exec $MYSQL -uTanjotuser1 -hlocalhost -pabc --default_auth=sha256_password --server_public_key_path=$MYSQL_TEST_DIR/std_data/rsa_public_key.pem -e "select user(), current_user()"
488
489 # Dropping the created users
490
491 DROP USER 'Tanjotuser1'@'localhost';
492 DROP USER 'Tanjotuser2'@'127.0.0.1';
493
494
495 --echo
496 --echo
497 --echo =================================================================================================
498 --echo Starting the server with the default authentication mysql_native_password
499 --echo =================================================================================================
500 --echo
501
502 --echo # Restart server with default-authentication-plugin=mysql_native_password;
503
504 let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect;
505 --exec echo "wait" > $restart_file
506 --shutdown_server
507 --source include/wait_until_disconnected.inc
508 -- exec echo "restart:--default-authentication-plugin=mysql_native_password " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
509 -- enable_reconnect
510 -- source include/wait_until_connected_again.inc
511
512
513 # Creating 3 users with mysql_native_password, mysql_old_password and sha256_password plugins respectively
514
515 --echo Creating a user with mysql_native_password plugin
516 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
517
518 Grant all on *.* to 'Tanjotuser1'@'localhost';
519
520 --echo Creating a user with SHA256_password plugin
521 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
522
523 set password for 'Tanjotuser3'@'localhost' ='abc';
524
525 Grant all on *.* to 'Tanjotuser3'@'localhost';
526
527 # Verifying the Authentication plugin for the above created users
528
529 select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password';
530 --echo 1 Expected
531
532 select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
533 --echo 1 Expected
534
535 # Dropping the created users
536
537 DROP USER 'Tanjotuser1'@'localhost';
538 DROP USER 'Tanjotuser3'@'localhost';
539
540
541 --echo
542 --echo
543 --echo =================================================================================================
544 --echo Starting the server with the default authentication sha256_password
545 --echo =================================================================================================
546 --echo
547
548 --echo # Restart server with default-authentication-plugin=sha256_password;
549
550 let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect;
551 --exec echo "wait" > $restart_file
552 --shutdown_server
553 --source include/wait_until_disconnected.inc
554 -- exec echo "restart:--default-authentication-plugin=sha256_password " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
555 -- enable_reconnect
556 -- source include/wait_until_connected_again.inc
557
558 # Creating 3 users with mysql_native_password, mysql_old_password and sha256_password plugins respectively
559
560 --echo Creating a user with mysql_native_password plugin
561 CREATE USER 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_native_password';
562
563 set password for 'Tanjotuser2'@'localhost' ='abc';
564
565 Grant all on *.* to 'Tanjotuser2'@'localhost';
566
567 --echo Creating a user with SHA256_password plugin
568 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED by 'abc';
569
570 Grant all on *.* to 'Tanjotuser3'@'localhost';
571
572 # Verifying the Authentication plugin for the above created users
573
574 select (select plugin from mysql.user where User='Tanjotuser2' and Host='localhost')='mysql_native_password';
575 --echo 1 Expected
576
577 select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
578 --echo 1 Expected
579
580 # Dropping the created users
581
582 DROP USER 'Tanjotuser2'@'localhost';
583 DROP USER 'Tanjotuser3'@'localhost';
584
585 # Restore default settings in opt file and clean up
586 --let $restart_parameters = restart:
587 --source include/restart_mysqld.inc