Debian Codegraph @

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

raw
1 ###############################################################################
2 # #
3 # User access, credential update, permission grant/revoke, alter user, #
4 # drop user, create user, grant user, set password and rename user commands #
5 # functional and integration test #
6 # #
7 # #
8 # Creation Date: 2012-12-27 #
9 # Author : Tanjot Singh Uppal #
10 # #
11 # #
12 # Description:Test Cases of creation, alteration and controlling of #
13 # the user access #
14 # #
15 ###############################################################################
16
17 --source include/mysql_upgrade_preparation.inc
18
19 # This test will intentionally generate errors in the server error log
20 # when a broken password is inserted into the mysql.user table.
21 # The below suppression is to clear those errors.
22 --disable_query_log
23 call mtr.add_suppression(".*Password salt for user.*");
24 --enable_query_log
25
26 --disable_query_log
27 call mtr.add_suppression(".*Tanjotuser3@localhost.*");
28 --enable_query_log
29
30
31 --echo
32 --echo
33 --echo =======================================================================
34 --echo Checking the password plugin assignment with create user command
35 --echo =======================================================================
36 --echo
37
38 #### Checking the password plugin assignment with create user command
39
40 ## Creating a user with respect to all the 3 password authentication plugin
41
42 set @default_plugin=@@global.default_authentication_plugin;
43 let DEFAULT_PLUGIN= `SELECT @default_plugin`;
44
45 --echo **** Creating user with default_plugin
46
47 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
48
49
50 --echo **** Creating user with sha256_password plugin
51
52 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
53 SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
54
55
56 ## Validating the plugin assigned for the above 3 users in the user table
57
58 --echo **** Validating the plugin names
59
60 select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')=@default_plugin;
61 --echo 1 Expected
62
63 select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
64 --echo 1 Expected
65
66
67 ## Dropping the created users
68
69 Drop user 'Tanjotuser1'@'localhost';
70 Drop user 'Tanjotuser3'@'localhost';
71
72
73
74 --echo
75 --echo
76 --echo =======================================================================
77 --echo Checking the functionality and integrity of the DROP user command
78 --echo =======================================================================
79 --echo
80
81 #### Checking the functionality and integrity of the DROP user command
82
83 ## Creating a user with respect to all the 3 password authentication plugin
84
85
86 --echo **** Creating user with default_plugin
87
88 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
89
90
91 --echo **** Creating user with sha256_password plugin
92
93 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
94 SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
95
96
97 ## Creating a user with respect to all the 3 password authentication plugin
98
99 --echo Connecting a session each with the above 3 users
100
101 --disable_warnings
102 connect(con1,localhost,Tanjotuser1,abc,,);
103 connect(con3,localhost,Tanjotuser3,abc,,);
104 --enable_warnings
105
106 ## Now dropping the above 3 users with each having a open session
107
108 --echo dropping the above 3 users with each having a open session
109
110 connection default;
111
112 Drop user 'Tanjotuser1'@'localhost';
113 Drop user 'Tanjotuser3'@'localhost';
114
115 ## Now trying opening a new session for the above dropped user
116
117 --echo Now trying opening a new session for the above dropped user
118
119 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
120 --disable_query_log
121 --error ER_ACCESS_DENIED_ERROR
122 connect(con4,localhost,Tanjotuser1,abc,,);
123 --enable_query_log
124
125 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
126 --disable_query_log
127 --error ER_ACCESS_DENIED_ERROR
128 connect(con6,localhost,Tanjotuser3,abc,,);
129 --enable_query_log
130
131
132 ## Accessing the open sessions for the above dropped user
133
134 --echo Accessing the open sessions for the above dropped user
135
136
137 connection con1;
138 select user(), current_user();
139
140
141 connection con3;
142 select user(), current_user();
143
144
145 disconnect con1;
146 disconnect con3;
147
148
149 --echo
150 --echo
151 --echo =======================================================================
152 --echo Checking the privellages post renaming the user
153 --echo =======================================================================
154 --echo
155
156 #### Checking the rename user command
157
158 ## Creating a user with respect to all the 3 password authentication plugin
159
160 connection default;
161
162 --echo **** Creating user with default_plugin
163
164 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
165
166
167 --echo **** Creating user with sha256_password plugin
168
169 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
170 SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
171
172
173 ## Validating the plugin assigned for the above 3 users in the user table
174
175 --echo **** Validating the plugin names
176
177 select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')=@default_plugin;
178 --echo 1 Expected
179
180 select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
181 --echo 1 Expected
182
183 ## Connecting a session to each of the above created users
184
185
186 --disable_warnings
187 connect(con7,localhost,Tanjotuser1,abc,,);
188 select user(), current_user();
189
190 connect(con9,localhost,Tanjotuser3,abc,,);
191 select user(), current_user();
192 --enable_warnings
193
194
195
196 ## Validating the integrity of the rename user command
197
198 connection default;
199
200 --echo **** Validating the integrity of the rename user command
201
202 RENAME USER 'Tanjotuser1'@'localhost' to 'user1'@'localhost';
203 RENAME USER 'Tanjotuser3'@'localhost' to 'user3'@'localhost';
204
205 ## Validating the plugin assigned for the renamed users in the user table
206
207 --echo **** Validating the plugin names
208
209 select (select plugin from mysql.user where User='user1' and Host='localhost')=@default_plugin;
210 --echo 1 Expected
211
212 select (select plugin from mysql.user where User='user3' and Host='localhost')='sha256_password';
213 --echo 1 Expected
214
215
216 ## Validating the users post renaming
217
218 --echo **** Validating the user connections post renaming
219
220 connection con7;
221 select user(), current_user();
222
223 connection con9;
224 select user(), current_user();
225
226
227 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
228 --disable_query_log
229 --error ER_ACCESS_DENIED_ERROR
230 connect(con10,localhost,Tanjotuser1,abc,,);
231 --enable_query_log
232
233 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
234 --disable_query_log
235 --error ER_ACCESS_DENIED_ERROR
236 connect(con12,localhost,Tanjotuser3,abc,,);
237 --enable_query_log
238
239
240 --disable_warnings
241 connect(con13,localhost,user1,abc,,);
242 select user(), current_user();
243
244 connect(con15,localhost,user3,abc,,);
245 select user(), current_user();
246 --enable_warnings
247
248
249 ## Disconnecting the open sessions and dropping the created users
250
251 --echo **** Disconnecting the open sessions and dropping the created users
252
253 connection default;
254
255 disconnect con7;
256 disconnect con9;
257 disconnect con13;
258 disconnect con15;
259
260 connection default;
261
262 Drop user 'user1'@'localhost';
263 Drop user 'user3'@'localhost';
264
265
266 --echo
267 --echo
268 --echo =======================================================================
269 --echo Checking the privillages with grant user command
270 --echo =======================================================================
271 --echo
272
273 #### Checking the password plugin assignment with grant user command
274
275 ## Creating a user with respect to all the 3 password authentication plugin
276
277
278 --echo **** Creating user with default_plugin
279 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
280 GRANT select on mysql.* to 'Tanjotuser1'@'localhost';
281
282
283 --echo **** Creating user with sha256_password plugin
284
285 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password' BY '123';
286 GRANT select on mysql.* to 'Tanjotuser3'@'localhost';
287 SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
288
289
290 ## Granting and revoking the privellages from the above users
291
292 --echo **** Validating the granted privilleges
293 connect(con16,localhost,Tanjotuser1,abc,,);
294 --replace_result $DEFAULT_PLUGIN default_plugin
295 select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
296 --echo default_plugin Expected
297
298
299 connect(con18,localhost,Tanjotuser3,abc,,);
300 select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
301 --echo sha256_password Expected
302
303 # Revoking select on mysql database
304 --echo **** Revoking select on mysql database
305
306 connection default;
307
308 Revoke select on mysql.* from 'Tanjotuser1'@'localhost';
309 Revoke select on mysql.* from 'Tanjotuser3'@'localhost';
310
311 --echo **** Validating the Revoked privilleges
312
313 connection con16;
314 --error ER_TABLEACCESS_DENIED_ERROR
315 select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
316 select user(), current_user();
317
318 connection con18;
319 --error ER_TABLEACCESS_DENIED_ERROR
320 select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
321 select user(), current_user();
322
323
324 ## Granting and revoking the privellages from the above users
325
326
327 connection default;
328
329 Grant usage on mysql.* to 'Tanjotuser1'@'localhost';
330 Grant usage on mysql.* to 'Tanjotuser3'@'localhost';
331
332
333 --echo **** Validating the granted privilleges
334
335 connection con16;
336 --error ER_TABLEACCESS_DENIED_ERROR
337 select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
338 select user(), current_user();
339
340 connection con18;
341 --error ER_TABLEACCESS_DENIED_ERROR
342 select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
343 select user(), current_user();
344
345
346 # Granting All on mysql database
347
348 --echo **** Validating the granted privilleges
349
350 connection default;
351
352 Grant All on mysql.* to 'Tanjotuser1'@'localhost';
353 Grant All on mysql.* to 'Tanjotuser3'@'localhost';
354
355 connect(con50,localhost,Tanjotuser1,abc,,);
356 --replace_result $DEFAULT_PLUGIN default_plugin
357 select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
358 --echo default_plugin Expected
359
360 connect(con52,localhost,Tanjotuser3,abc,,);
361 select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
362 --echo sha256_password Expected
363
364
365
366 ## Disconnecting the open sessions and dropping the created users
367
368 --echo **** Disconnecting the open sessions and dropping the created users
369
370 connection default;
371
372 disconnect con16;
373 disconnect con18;
374
375 disconnect con50;
376 disconnect con52;
377
378 connection default;
379
380 Drop user 'Tanjotuser1'@'localhost';
381 Drop user 'Tanjotuser3'@'localhost';
382
383
384
385 --echo
386 --echo
387 --echo =============================================================================
388 --echo Checking the password assignment using the update command on the user table
389 --echo =============================================================================
390 --echo
391
392 #### Checking the password plugin assignment and password hashing based on the update command
393
394 ## Creating a user with respect to all the 3 password authentication plugin
395
396
397 --echo **** Creating user with mysql_native_password plugin
398 --disable_warnings
399 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
400 UPDATE mysql.user SET Authentication_string='*0D3CED9BEC10A777AEC23CCC353A8C08A633045E' WHERE User='Tanjotuser1' AND Host='localhost';
401 FLUSH PRIVILEGES;
402
403
404
405 --echo **** Creating user with sha256_password plugin
406
407 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
408 UPDATE IGNORE mysql.user SET Authentication_string='*0D3CED9BEC10A777AEC23CCC353A8C08A633045E' WHERE User='Tanjotuser3' AND Host='localhost';
409 FLUSH PRIVILEGES;
410 --enable_warnings
411
412
413 connect(con19,localhost,Tanjotuser1,abc,,);
414 select user(), current_user();
415 set password='abcd';
416
417 # Below section is hashed out till Bug #16050188 is fixed
418
419 #connect(con21,localhost,Tanjotuser3,abc,,);
420 #select user(), current_user();
421
422
423 connect(con22,localhost,Tanjotuser1,abcd,,);
424 select user(), current_user();
425
426
427 #connect(con24,localhost,Tanjotuser3,abcd,,);
428 #select user(), current_user();
429
430
431
432 ## Disconnecting the open sessions and dropping the created users
433
434 --echo **** Disconnecting the open sessions and dropping the created users
435
436 connection default;
437
438 disconnect con19;
439 #disconnect con21;
440 disconnect con22;
441 #disconnect con24;
442
443 connection default;
444
445 Drop user 'Tanjotuser1'@'localhost';
446 Drop user 'Tanjotuser3'@'localhost';
447
448
449
450 --echo
451 --echo
452 --echo ===================================================================================================
453 --echo Starting the server with default auth as sha256_password and checking the above validations again.
454 --echo ===================================================================================================
455 --echo
456
457 --echo # Restart server with default-authentication-plugin=sha256_password;
458
459 let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect;
460 --exec echo "wait" > $restart_file
461 --shutdown_server
462 --source include/wait_until_disconnected.inc
463 -- exec echo "restart:--default-authentication-plugin=sha256_password " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
464 -- enable_reconnect
465 -- source include/wait_until_connected_again.inc
466
467
468 --echo
469 --echo
470 --echo =======================================================================
471 --echo Checking the password plugin assignment with create user command
472 --echo =======================================================================
473 --echo
474
475 #### Checking the password plugin assignment with create user command
476
477 ## Creating a user with respect to all the 3 password authentication plugin
478
479
480 --echo **** Creating user with mysql_native_password plugin
481
482 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
483 SET PASSWORD for 'Tanjotuser1'@'localhost' = 'abc';
484
485
486 --echo **** Creating user with sha256_password plugin
487
488 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
489 SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
490
491
492 ## Validating the plugin assigned for the above 3 users in the user table
493
494 --echo **** Validating the plugin names
495
496 select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password';
497 --echo 1 Expected
498
499 select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
500 --echo 1 Expected
501
502
503 ## Dropping the created users
504
505 Drop user 'Tanjotuser1'@'localhost';
506 Drop user 'Tanjotuser3'@'localhost';
507
508
509 --echo
510 --echo
511 --echo =======================================================================
512 --echo Checking the functionality and integrity of the DROP user command
513 --echo =======================================================================
514 --echo
515
516 #### Checking the functionality and integrity of the DROP user command
517
518 ## Creating a user with respect to all the 3 password authentication plugin
519
520
521 --echo **** Creating user with default_plugin
522
523 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
524
525
526 --echo **** Creating user with sha256_password plugin
527
528 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
529 SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
530
531
532 ## Creating a user with respect to all the 3 password authentication plugin
533
534 --echo Connecting a session each with the above 3 users
535
536 --disable_warnings
537 connect(con1,localhost,Tanjotuser1,abc,,);
538 connect(con3,localhost,Tanjotuser3,abc,,);
539 --enable_warnings
540
541 ## Now dropping the above 3 users with each having a open session
542
543 --echo dropping the above 3 users with each having a open session
544
545 connection default;
546
547 Drop user 'Tanjotuser1'@'localhost';
548 Drop user 'Tanjotuser3'@'localhost';
549
550 ## Now trying opening a new session for the above dropped user
551
552 --echo Now trying opening a new session for the above dropped user
553
554 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
555 --disable_query_log
556 --error ER_ACCESS_DENIED_ERROR
557 connect(con4,localhost,Tanjotuser1,abc,,);
558 --enable_query_log
559
560 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
561 --disable_query_log
562 --error ER_ACCESS_DENIED_ERROR
563 connect(con6,localhost,Tanjotuser3,abc,,);
564 --enable_query_log
565
566
567 ## Accessing the open sessions for the above dropped user
568
569 --echo Accessing the open sessions for the above dropped user
570
571
572 connection con1;
573 select user(), current_user();
574
575
576 connection con3;
577 select user(), current_user();
578
579
580 disconnect con1;
581 disconnect con3;
582
583
584 --echo
585 --echo
586 --echo =======================================================================
587 --echo Checking the privellages post renaming the user
588 --echo =======================================================================
589 --echo
590
591 #### Checking the rename user command
592
593 ## Creating a user with respect to all the 3 password authentication plugin
594
595 connection default;
596
597 --echo **** Creating user with mysql_native_password plugin
598
599 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
600 SET PASSWORD for 'Tanjotuser1'@'localhost' = 'abc';
601
602
603 --echo **** Creating user with sha256_password plugin
604
605 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
606 SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
607
608
609 ## Validating the plugin assigned for the above 3 users in the user table
610
611 --echo **** Validating the plugin names
612
613 select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password';
614 --echo 1 Expected
615
616 select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
617 --echo 1 Expected
618
619 ## Connecting a session to each of the above created users
620
621
622 --disable_warnings
623 connect(con7,localhost,Tanjotuser1,abc,,);
624 select user(), current_user();
625
626 connect(con9,localhost,Tanjotuser3,abc,,);
627 select user(), current_user();
628 --enable_warnings
629
630
631
632 ## Validating the integrity of the rename user command
633
634 connection default;
635
636 --echo **** Validating the integrity of the rename user command
637
638 RENAME USER 'Tanjotuser1'@'localhost' to 'user1'@'localhost';
639 RENAME USER 'Tanjotuser3'@'localhost' to 'user3'@'localhost';
640
641 ## Validating the plugin assigned for the renamed users in the user table
642
643 --echo **** Validating the plugin names
644
645 select (select plugin from mysql.user where User='user1' and Host='localhost')='mysql_native_password';
646 --echo 1 Expected
647
648 select (select plugin from mysql.user where User='user3' and Host='localhost')='sha256_password';
649 --echo 1 Expected
650
651
652 ## Validating the users post renaming
653
654 --echo **** Validating the user connections post renaming
655
656 connection con7;
657 select user(), current_user();
658
659 connection con9;
660 select user(), current_user();
661
662 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
663 --disable_query_log
664 --error ER_ACCESS_DENIED_ERROR
665 connect(con10,localhost,Tanjotuser1,abc,,);
666 --enable_query_log
667
668 --replace_result $MASTER_MYSOCK MASTER_MYSOCK
669 --disable_query_log
670 --error ER_ACCESS_DENIED_ERROR
671 connect(con12,localhost,Tanjotuser3,abc,,);
672 --enable_query_log
673
674
675 --disable_warnings
676 connect(con13,localhost,user1,abc,,);
677 select user(), current_user();
678
679 connect(con15,localhost,user3,abc,,);
680 select user(), current_user();
681 --enable_warnings
682
683
684 ## Disconnecting the open sessions and dropping the created users
685
686 --echo **** Disconnecting the open sessions and dropping the created users
687
688 connection default;
689
690 disconnect con7;
691 disconnect con9;
692 disconnect con13;
693 disconnect con15;
694
695 connection default;
696
697 Drop user 'user1'@'localhost';
698 Drop user 'user3'@'localhost';
699
700
701 --echo
702 --echo
703 --echo =======================================================================
704 --echo Checking the privillages with grant user command
705 --echo =======================================================================
706 --echo
707
708 #### Checking the password plugin assignment with grant user command
709
710 ## Creating a user with respect to all the 3 password authentication plugin
711
712
713 --echo **** Creating user with mysql_native_password plugin
714 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '123';
715 GRANT select on mysql.* to 'Tanjotuser1'@'localhost';
716 SET PASSWORD for 'Tanjotuser1'@'localhost' = 'abc';
717
718
719 --echo **** Creating user with sha256_password plugin
720 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password' BY '123';
721 GRANT select on mysql.* to 'Tanjotuser3'@'localhost';
722 SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
723
724
725 ## Granting and revoking the privellages from the above users
726
727 --echo **** Validating the granted privilleges
728
729 connect(con16,localhost,Tanjotuser1,abc,,);
730 select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
731 --echo mysql_native_password Expected
732
733 connect(con18,localhost,Tanjotuser3,abc,,);
734 select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
735 --echo sha256_password Expected
736
737 # Revoking select on mysql database
738 --echo **** Revoking select on mysql database
739
740 connection default;
741
742 Revoke select on mysql.* from 'Tanjotuser1'@'localhost';
743 Revoke select on mysql.* from 'Tanjotuser3'@'localhost';
744
745 --echo **** Validating the Revoked privilleges
746
747 connection con16;
748 --error ER_TABLEACCESS_DENIED_ERROR
749 select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
750 select user(), current_user();
751
752 connection con18;
753 --error ER_TABLEACCESS_DENIED_ERROR
754 select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
755 select user(), current_user();
756
757
758 ## Granting and revoking the privellages from the above users
759
760
761 connection default;
762
763 Grant usage on mysql.* to 'Tanjotuser1'@'localhost';
764 Grant usage on mysql.* to 'Tanjotuser3'@'localhost';
765
766
767 --echo **** Validating the granted privilleges
768
769 connection con16;
770 --error ER_TABLEACCESS_DENIED_ERROR
771 select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
772 select user(), current_user();
773
774 connection con18;
775 --error ER_TABLEACCESS_DENIED_ERROR
776 select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
777 select user(), current_user();
778
779
780 # Granting All on mysql database
781
782 --echo **** Validating the granted privilleges
783
784 connection default;
785
786 Grant All on mysql.* to 'Tanjotuser1'@'localhost';
787 Grant All on mysql.* to 'Tanjotuser3'@'localhost';
788
789 connect(con50,localhost,Tanjotuser1,abc,,);
790 select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
791 --echo mysql_native_password Expected
792
793 connect(con52,localhost,Tanjotuser3,abc,,);
794 select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
795 --echo sha256_password Expected
796
797
798
799 ## Disconnecting the open sessions and dropping the created users
800
801 --echo **** Disconnecting the open sessions and dropping the created users
802
803 connection default;
804
805 disconnect con16;
806 disconnect con18;
807
808 disconnect con50;
809 disconnect con52;
810
811 connection default;
812
813 Drop user 'Tanjotuser1'@'localhost';
814 Drop user 'Tanjotuser3'@'localhost';
815
816
817
818 --echo
819 --echo
820 --echo =============================================================================
821 --echo Checking the password assignment using the update command on the user table
822 --echo =============================================================================
823 --echo
824
825 #### Checking the password plugin assignment and password hashing based on the update command
826
827 ## Creating a user with respect to all the 3 password authentication plugin
828
829
830 --echo **** Creating user with mysql_native_password plugin
831 --disable_warnings
832 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
833 UPDATE mysql.user SET Authentication_string='*0D3CED9BEC10A777AEC23CCC353A8C08A633045E' WHERE User='Tanjotuser1' AND Host='localhost';
834 FLUSH PRIVILEGES;
835
836
837
838 --echo **** Creating user with sha256_password plugin
839
840 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
841 UPDATE IGNORE mysql.user SET Authentication_string='*0D3CED9BEC10A777AEC23CCC353A8C08A633045E' WHERE User='Tanjotuser3' AND Host='localhost';
842 FLUSH PRIVILEGES;
843 --enable_warnings
844
845
846 connect(con19,localhost,Tanjotuser1,abc,,);
847 select user(), current_user();
848 set password='abcd';
849
850 # Below section is hashed out till Bug #16050188 is fixed
851
852 #connect(con21,localhost,Tanjotuser3,abc,,);
853 #select user(), current_user();
854
855
856 connect(con22,localhost,Tanjotuser1,abcd,,);
857 select user(), current_user();
858
859
860 #connect(con24,localhost,Tanjotuser3,abcd,,);
861 #select user(), current_user();
862
863
864
865 ## Disconnecting the open sessions and dropping the created users
866
867 --echo **** Disconnecting the open sessions and dropping the created users
868
869 connection default;
870
871 disconnect con19;
872 #disconnect con21;
873 disconnect con22;
874 #disconnect con24;
875
876 connection default;
877
878 Drop user 'Tanjotuser1'@'localhost';
879 Drop user 'Tanjotuser3'@'localhost';
880
881 # Restore default settings in opt file
882 --let $restart_parameters = restart:
883 --source include/restart_mysqld.inc