Debian Codegraph @

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

raw
1 #
2 # WL11772 CREATE USER IDENTIFIED BY RANDOM PASSWORD
3 #
4
5 --source include/have_log_bin.inc
6
7 SELECT @@GLOBAL.generated_random_password_length;
8 SELECT @@SESSION.generated_random_password_length;
9
10 --replace_column 3 random
11 CREATE USER u1 IDENTIFIED BY RANDOM PASSWORD, u2 IDENTIFIED BY RANDOM PASSWORD, u3 IDENTIFIED BY RANDOM PASSWORD;
12 --replace_column 3 random
13 CREATE USER u4 IDENTIFIED BY RANDOM PASSWORD;
14
15 --let $sql= 'CREATE USER foo IDENTIFIED BY RANDOM PASSWORD PASSWORD REQUIRE CURRENT'
16 --let $pass= query_get_value($sql,generated password,1)
17
18 # connect using the generated password.
19 --connect(con1,localhost,foo,$pass,test)
20 select current_user;
21 --echo ** Verify that REPLACE-syntax works
22 --disable_query_log
23 --replace_column 3 random
24 --eval SET PASSWORD TO RANDOM REPLACE '$pass';
25 --enable_query_log
26 --connection default
27 --disconnect con1
28
29 --echo ** Verify that RETAIN-syntax works
30 --disable_query_log
31 --replace_column 3 random
32 --eval SET PASSWORD FOR foo TO RANDOM RETAIN CURRENT PASSWORD;
33 --enable_query_log
34
35 --echo ** Verify that ALTER USER works as expected.
36 --let $sql= 'ALTER USER foo IDENTIFIED BY RANDOM PASSWORD'
37 --let $pass= query_get_value($sql,generated password,1)
38 --connect(con1,localhost,foo,$pass,test)
39 select current_user;
40 --echo ** Verify that REPLACE-syntax works
41 --disable_query_log
42 --replace_column 3 random
43 --eval ALTER USER foo IDENTIFIED BY RANDOM PASSWORD REPLACE '$pass';
44 --enable_query_log
45 --connection default
46 --disconnect con1
47 SET PASSWORD FOR u1 = 'foo';
48 --connect(con1,localhost,u1,foo,test)
49 --echo ** Verify that it works for current user.
50 --replace_column 3 random
51 SET PASSWORD TO RANDOM;
52 --replace_column 3 random
53 --let $sql ="SET PASSWORD FOR CURRENT_USER TO RANDOM";
54 --let $pass= query_get_value($sql,generated password,1)
55 --connection default
56 --disconnect con1
57 --connect(con1,localhost,u1,$pass,test)
58 SELECT CURRENT_USER();
59 --connection default
60 --disconnect con1
61
62 --echo ** Test password length
63 set session generated_random_password_length=6;
64 --let $sql= 'SET PASSWORD FOR foo TO RANDOM'
65 --let $pass= query_get_value($sql,generated password,1)
66 --disable_query_log
67 --eval SELECT LENGTH('$pass') AS "Password length";
68 --enable_query_log
69 set session generated_random_password_length=66;
70 --let $sql= 'SET PASSWORD FOR foo TO RANDOM'
71 --let $pass= query_get_value($sql,generated password,1)
72 --disable_query_log
73 --eval SELECT LENGTH('$pass') AS "Password length";
74 --enable_query_log
75
76 DROP USER u1,u2,u3,u4,foo;
77
78 --replace_column 3 random
79 CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
80 --let $qry="SET PASSWORD FOR usr TO RANDOM"
81 let $pswd = query_get_value ( $qry, "generated password", 1 );
82 connect(con1, localhost, usr, $pswd,test);
83 --echo Logged in successfully through usr
84 SELECT CURRENT_USER();
85 --echo "Disconnect from usr ..."
86 connection default;
87 disconnect con1;
88 DROP USER usr;
89
90 --let $sql= "CREATE USER usr1 IDENTIFIED WITH 'sha256_password' BY RANDOM PASSWORD"
91 let $pswd= query_get_value( $sql, "generated password", 1);
92 connect(con1, localhost, usr1, $pswd,test);
93 --echo Logged in successfully through usr1
94 SELECT CURRENT_USER();
95 --echo "Disconnect from usr1 ..."
96 connection default;
97 disconnect con1;
98 DROP USER usr1;
99
100 -- echo
101 -- echo #-- Testing Syntax of the new CREATE USER/ALTER USER/SET PASSWORD extension
102 -- echo
103 #
104 #-------------------------------------------------------------
105 # Tests should not throw any syntax errors
106 #-------------------------------------------------------------
107 --echo #--Valid combinations/ syntaxes of RANDOM PASSWORD
108
109 --replace_column 3 #####
110 CREATE USER 'usr'@'localhost' IDENTIFIED BY RANDOM PASSWORD;
111
112 --replace_column 3 #####
113 ALTER USER 'usr'@'localhost' IDENTIFIED BY RANDOM PASSWORD;
114
115 --replace_column 3 #####
116 SET PASSWORD for 'usr'@'localhost' TO RANDOM;
117
118 DROP USER 'usr'@'localhost';
119
120 --echo
121
122 --replace_column 3 #####
123 CREATE USER 'usr' IDENTIFIED BY RANDOM PASSWORD;
124
125 --replace_column 3 #####
126 ALTER USER 'usr' IDENTIFIED BY RANDOM PASSWORD;
127
128 --replace_column 3 #####
129 SET PASSWORD for 'usr' TO RANDOM;
130
131 DROP USER 'usr';
132
133 --echo
134
135 --replace_column 3 #####
136 CREATE USER 'usr'@'%' IDENTIFIED BY RANDOM PASSWORD;
137
138 --replace_column 3 #####
139 ALTER USER 'usr'@'%' IDENTIFIED BY RANDOM PASSWORD;
140
141 --replace_column 3 #####
142 SET PASSWORD for 'usr'@'%' TO RANDOM;
143
144 DROP USER 'usr'@'%';
145
146 --echo
147
148 --replace_column 3 #####
149 CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
150
151 --replace_column 3 #####
152 ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
153
154 --replace_column 3 #####
155 SET PASSWORD for usr TO RANDOM;
156
157 DROP USER usr;
158
159 --echo
160
161 --replace_column 3 #####
162 CREATE USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;
163
164 --replace_column 3 #####
165 ALTER USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;
166
167 --replace_column 3 #####
168 SET PASSWORD for usr@localhost TO RANDOM;
169
170 DROP USER usr@localhost;
171
172 --echo
173
174 --replace_column 3 #####
175 CREATE USER usr@'%' IDENTIFIED BY RANDOM PASSWORD;
176
177 --replace_column 3 #####
178 ALTER USER usr@'%' IDENTIFIED BY RANDOM PASSWORD;
179
180 --replace_column 3 #####
181 SET PASSWORD for usr@'%' TO RANDOM;
182
183 DROP USER usr@'%';
184
185 --echo
186
187 --replace_column 3 #####
188 CREATE USER 'usr'@localhost IDENTIFIED BY RANDOM PASSWORD;
189
190 --replace_column 3 #####
191 ALTER USER 'usr'@localhost IDENTIFIED BY RANDOM PASSWORD;
192
193 --replace_column 3 #####
194 SET PASSWORD for 'usr'@localhost TO RANDOM;
195
196 DROP USER 'usr'@localhost;
197
198 --echo
199
200 --replace_column 3 #####
201 CREATE USER usr@'localhost' IDENTIFIED BY RANDOM PASSWORD;
202
203 --replace_column 3 #####
204 ALTER USER usr@'localhost' IDENTIFIED BY RANDOM PASSWORD;
205
206 --replace_column 3 #####
207 SET PASSWORD for usr@'localhost' TO RANDOM;
208
209 DROP USER usr@'localhost';
210
211 --echo
212
213 --replace_column 3 #####
214 CREATE USER usr IDENTIFIED BY Random Password;
215 DROP USER usr;
216
217 --replace_column 3 #####
218 CREATE USER usr IDENTIFIED BY random password;
219 DROP USER usr;
220
221 --replace_column 3 #####
222 CREATE USER usr IDENTIFIED BY rANDOm PASSword;
223 DROP USER usr;
224
225 --echo
226
227 --replace_column 3 #####
228 CREATE USER usr1 IDENTIFIED BY RANDOM PASSWORD,
229 usr2 IDENTIFIED BY RANDOM PASSWORD,
230 usr3 IDENTIFIED BY RANDOM PASSWORD;
231
232 --replace_column 3 #####
233 ALTER USER usr1 IDENTIFIED BY RANDOM PASSWORD,
234 usr2 IDENTIFIED BY RANDOM PASSWORD,
235 usr3 IDENTIFIED BY RANDOM PASSWORD;
236
237 DROP USER usr1;
238 DROP USER usr2;
239 DROP USER usr3;
240
241
242 --echo
243
244 #-------------------------------------------------------------
245 # Invalid combinations/ syntaxes of RANDOM PASSWORD
246 #-------------------------------------------------------------
247 --echo #--Invalid combinations/ syntaxes of RANDOM PASSWORD
248
249 --error ER_PARSE_ERROR
250 CREATE USER usr IDENTIFIED BY RANDOM;
251
252 --echo
253 --error ER_PARSE_ERROR
254 CREATE USER usr IDENTIFIED BY RANDOM PASSWD;
255
256 --echo
257 --error ER_PARSE_ERROR
258 CREATE USER usr IDENTIFIED RANDOM PASSWORD;
259
260 --echo
261 --error ER_PARSE_ERROR
262 CREATE USER usr BY RANDOM PASSWORD;
263
264 --echo
265 --replace_column 3 ######
266 CREATE USER 'usr'@'%' IDENTIFIED BY RANDOM PASSWORD;
267
268 --echo
269 --error ER_PARSE_ERROR
270 ALTER USER usr IDENTIFIED BY RANDOM;
271
272 --echo
273 --error ER_PARSE_ERROR
274 ALTER USER usr IDENTIFIED BY RANDOM PASSWD;
275
276 --echo
277 --error ER_PARSE_ERROR
278 ALTER USER usr IDENTIFIED RANDOM PASSWORD;
279
280 --echo
281 --error ER_PARSE_ERROR
282 ALTER USER usr BY RANDOM PASSWORD;
283
284 --echo
285 --error ER_PARSE_ERROR
286 SET PASSWORD for usr RANDOM;
287
288 DROP USER usr;
289
290 --echo
291 --error ER_CANNOT_USER
292 ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
293
294 --echo
295 --replace_column 3 ######
296 CREATE USER 'usr'@'%' IDENTIFIED BY RANDOM PASSWORD;
297
298 --echo
299 --error ER_CANNOT_USER
300 ALTER USER 'usr'@'localhost' IDENTIFIED BY RANDOM PASSWORD;
301
302 DROP USER usr;
303
304 #------------
305
306
307 --echo
308 --echo #-------------------------------------------------------------
309 --echo # Privileges and Users
310 --echo #-------------------------------------------------------------
311 --echo
312
313 #-------------------------------------------------------------
314 #non privileged user
315 #-------------------------------------------------------------
316
317 --echo
318 --echo #--CREATE temp user with RANDOM PASSWORD
319 --replace_column 3 ######
320 CREATE USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
321 --echo #--CREATE user with RANDOM PASSWORD
322 let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
323 let $pswd = query_get_value ( $qry, "generated password", 1 );
324
325 #-------------------------------------------------------------
326 # non privileged user can set a RANDOM PASSWORD for itself
327 #-------------------------------------------------------------
328 --echo
329 --echo #--Login to usr with RANDOM PASSWORD
330 connect(con1, localhost, usr, $pswd,);
331 --echo #--Logged in successfully through usr
332 SELECT CURRENT_USER();
333 let $qry = SET PASSWORD TO RANDOM;
334 let $pswd = query_get_value ( "$qry", "generated password", 1 );
335 --echo #--Disconnect from usr ...
336 disconnect con1;
337
338
339 connect(con1, localhost, usr, $pswd,);
340 #-------------------------------------------------------------
341 # non privileged user cannot set a RANDOM PASSWORD for other user
342 #-------------------------------------------------------------
343 --error ER_DBACCESS_DENIED_ERROR
344 SET PASSWORD FOR usr_temp TO RANDOM;
345
346 #-------------------------------------------------------------
347 # non privileged user cannot alter a RANDOM PASSWORD for other users
348 #-------------------------------------------------------------
349 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
350 ALTER USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
351
352 #-------------------------------------------------------------
353 # non privileged user cannot create user with a RANDOM PASSWORD
354 #-------------------------------------------------------------
355 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
356 CREATE USER usr_ntemp IDENTIFIED BY RANDOM PASSWORD;
357
358 --echo #--Disconnect from usr ...
359 disconnect con1;
360
361 connection default;
362 DROP USER usr;
363 DROP USER usr_temp;
364
365 #-------------------------------------------------------------
366 #privileged user
367 #-------------------------------------------------------------
368 --echo
369 --echo #--CREATE temp user with RANDOM PASSWORD
370 --replace_column 3 #####
371 CREATE USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
372 --echo
373 --echo #--CREATE user with RANDOM PASSWORD
374 let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
375 let $pswd = query_get_value ( $qry, "generated password", 1 );
376 --echo
377 --echo #--GRANT ALL PRIVILEGES TO user
378 GRANT ALL ON *.* TO usr;
379 FLUSH PRIVILEGES;
380
381 --echo
382 --echo #--Login to usr with RANDOM PASSWORD
383 connect(con1, localhost, usr, $pswd,);
384 --echo #--Logged in successfully through usr
385 #-------------------------------------------------------------
386 # privileged user can set a RANDOM PASSWORD for itself
387 #-------------------------------------------------------------
388 SELECT CURRENT_USER();
389 --echo #--SET PASSWORD for user with RANDOM PASSWORD
390 let $qry = SET PASSWORD TO RANDOM;
391 let $pswd = query_get_value ( $qry, "generated password", 1 );
392 --echo #--Disconnect from usr ...
393 disconnect con1;
394 --echo
395
396 --echo #--Login to usr with RANDOM PASSWORD
397 connect(con1, localhost, usr, $pswd,);
398 #-------------------------------------------------------------
399 # privileged user can set a RANDOM PASSWORD for other user
400 #-------------------------------------------------------------
401 --echo #--SET PASSWORD for user with RANDOM PASSWORD
402 --replace_column 3 #####
403 SET PASSWORD FOR usr_temp TO RANDOM;
404
405 #-------------------------------------------------------------
406 # privileged user can alter a RANDOM PASSWORD for other users
407 #-------------------------------------------------------------
408 --echo
409 --echo #--ALTER user with RANDOM PASSWORD
410 --replace_column 3 #####
411 ALTER USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
412
413 #-------------------------------------------------------------
414 # privileged user can create user with a RANDOM PASSWORD
415 #-------------------------------------------------------------
416 --echo
417 --echo #--CREATE tempuser with RANDOM PASSWORD
418 --replace_column 3 #####
419 CREATE USER usr_ntemp IDENTIFIED BY RANDOM PASSWORD;
420 --echo #--Disconnect from usr ...
421 disconnect con1;
422
423 --echo
424 connection default;
425 --echo #--ALTER user with RANDOM PASSWORD
426 let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
427 let $pswd = query_get_value ( $qry, "generated password", 1 );
428 --echo
429
430 --echo #--Login to usr with RANDOM PASSWORD
431 connect(con1, localhost, usr, $pswd,);
432 #-------------------------------------------------------------
433 # privileged user can set the same RANDOM PASSWORD for other user
434 #-------------------------------------------------------------
435 --echo #--SET PASSWORD for user with RANDOM PASSWORD
436 --disable_query_log
437 SET PASSWORD for usr_temp = '$pswd';
438 --enable_query_log
439 --echo #--Disconnect from usr ...
440 disconnect con1;
441 --echo
442
443 --echo
444 --echo #--Login to usr with RANDOM PASSWORD
445 connect(con1, localhost, usr, $pswd,);
446 #-------------------------------------------------------------
447 # privileged user can set a RANDOM PASSWORD for the root user
448 #-------------------------------------------------------------
449
450 --echo
451 --echo #--SET PASSWORD for user with RANDOM PASSWORD
452 --error ER_PASSWORD_NO_MATCH
453 SET PASSWORD FOR root TO RANDOM;
454 --echo #--ALTER root with RANDOM PASSWORD
455 --error ER_CANNOT_USER
456 ALTER USER root IDENTIFIED BY RANDOM PASSWORD;
457
458 disconnect con1;
459 connection default;
460 DROP USER usr;
461 DROP USER usr_temp;
462 DROP USER usr_ntemp;
463 --echo
464 #-------------------------------------------------------------
465 # User with limited privileges
466 #-------------------------------------------------------------
467 connection default;
468 --replace_column 3 #####
469 CREATE USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
470 let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
471 let $pswd = query_get_value ( $qry, "generated password", 1 );
472 GRANT INSERT ON *.* TO usr_temp;
473 FLUSH PRIVILEGES;
474 --echo
475
476 connect(con1, localhost, usr, $pswd,);
477 --echo #--Logged in successfully through usr
478 SELECT CURRENT_USER();
479
480 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
481 CREATE USER usr_ntemp IDENTIFIED BY RANDOM PASSWORD;
482
483 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
484 ALTER USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
485 disconnect con1;
486 --echo
487
488 connection default;
489 DROP USER usr;
490 DROP USER usr_temp;
491
492 #-------------------------------------------------------------
493 # Manual password can be changed using ALTER USER
494 #-------------------------------------------------------------
495 #create user specifying the password
496 CREATE USER usr IDENTIFIED by 'foobar';
497 let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
498 let $pswd = query_get_value ( $qry, "generated password", 1 );
499 --echo
500
501 connect(con1, localhost, usr, $pswd,);
502 --echo #--Logged in successfully through usr
503 SELECT CURRENT_USER();
504 --echo #--Disconnect from usr ...
505 disconnect con1;
506 --echo
507
508 connection default;
509 DROP USER usr;
510
511 #-------------------------------------------------------------
512 # Manual password can be changed using SET USER
513 #-------------------------------------------------------------
514 #user created by specifying required password
515 CREATE USER usr IDENTIFIED by 'foobar';
516 let $qry = SET PASSWORD FOR usr TO RANDOM;
517 let $pswd = query_get_value ( $qry, "generated password", 1 );
518 --echo
519
520 connect(con1, localhost, usr, $pswd,);
521 --echo #--Logged in successfully through usr
522 SELECT CURRENT_USER();
523 --echo #--Disconnect from usr ...
524 disconnect con1;
525 --echo
526
527 connection default;
528 DROP USER usr;
529
530 #-------------------------------------------------------------
531 # Empty password can be changed using ALTER USER
532 #-------------------------------------------------------------
533 CREATE USER usr;
534 let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
535 let $pswd = query_get_value ( $qry, "generated password", 1 );
536
537 connect(con1, localhost, usr, $pswd,);
538 --echo #--Logged in successfully through usr
539 SELECT CURRENT_USER();
540 --echo #--Disconnect from usr ...
541 disconnect con1;
542
543 connection default;
544 DROP USER usr;
545
546 #-------------------------------------------------------------
547 # Empty password can be changed using SET USER
548 #-------------------------------------------------------------
549 CREATE USER usr;
550 let $qry = SET PASSWORD FOR usr TO RANDOM;
551 let $pswd = query_get_value ( $qry, "generated password", 1 );
552
553 connect(con1, localhost, usr, $pswd,);
554 --echo #--Logged in successfully through usr
555 SELECT CURRENT_USER();
556 --echo #--Disconnect from usr ...
557 disconnect con1;
558
559 connection default;
560 DROP USER usr;
561 --connection default
562
563 --replace_column 3 ######
564 CREATE USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;
565
566 --echo #-----------------------------------------------------------------------
567 --echo # RETAIN CURRENT PASSWORD with ALTER USER and SET PASSWORD
568 --echo #-----------------------------------------------------------------------
569 --echo
570 # No entry in mysql.user for usr@localhost
571 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
572 AS ADDITIONAL_PASSWORD_FOR_USR FROM mysql.user WHERE user LIKE 'usr';
573 --echo
574 # Change password
575 let $qry = ALTER USER usr@localhost IDENTIFIED BY RANDOM PASSWORD RETAIN CURRENT PASSWORD;
576 let $pswd1 = query_get_value ( $qry, "generated password", 1);
577 --echo
578 # Entry should be present in mysql.user for usr@localhost
579 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
580 AS ADDITIONAL_PASSWORD_FOR_USR FROM mysql.user WHERE user LIKE 'usr';
581 --echo
582 SET @pswd1=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
583 '$.additional_password') FROM mysql.user WHERE user LIKE 'usr');
584 --echo
585 # Change password
586 let $qry = SET PASSWORD for usr@localhost TO RANDOM RETAIN CURRENT PASSWORD;
587 let $pswd2 = query_get_value ( $qry, "generated password", 1);
588 --echo
589 # Entry should be present in mysql.user for usr@localhost
590 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
591 AS ADDITIONAL_PASSWORD_FOR_USR FROM mysql.user WHERE user LIKE 'usr';
592 --echo
593 SET @pswd2=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
594 '$.additional_password') FROM mysql.user WHERE user LIKE 'usr');
595 --echo
596 # Assert that password stored in mysql.user.user_attributes changed
597 SELECT @pswd1 <> @pswd2 AS ADDITIONAL_PASSWORD_CHANGED;
598 --echo
599 connect(con1, localhost, usr, '$pswd1',);
600 --echo #--Logged in successfully through usr with secondary password
601 SELECT CURRENT_USER();
602 --echo #--Disconnect from usr ...
603 disconnect con1;
604 --echo
605 connect(con2, localhost, usr, '$pswd2',);
606 --echo #--Logged in successfully through usr with primary password
607 SELECT CURRENT_USER();
608 --echo #--Disconnect from usr ...
609 disconnect con2;
610 --echo
611 connection default;
612 --echo
613 # Change password without RETAIN CURRENT PASSWORD
614 # Primary password will be changed and secondary password will be intact
615 let $qry = ALTER USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;
616 let $pswd3 = query_get_value ( $qry, "generated password", 1);
617 --echo
618 # Entry should still be present int mysql.user for usr@localhost
619 SELECT COUNT(*) AS ADDITIONAL_PASSWORD_FOR_USR FROM mysql.user
620 WHERE user LIKE 'usr';
621 --echo
622 SET @pswd3=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
623 '$.additional_password') FROM mysql.user WHERE user LIKE 'usr');
624 --echo
625 # Assert that password stored in mysql.user did not change
626 SELECT @pswd2 = @pswd3 AS ADDITIONAL_PASSWORD_NOT_CHANGED;
627 --echo
628 connect(con1, localhost, usr, '$pswd1',);
629 --echo #--Logged in successfully through usr with secondary password
630 SELECT CURRENT_USER();
631 --echo #--Disconnect from usr ...
632 disconnect con1;
633 --echo
634 connect(con3, localhost, usr, '$pswd3',);
635 --echo #--Logged in successfully through usr with primary password2
636 SELECT CURRENT_USER();
637 --echo #--Disconnect from usr ...
638 disconnect con3;
639 --echo
640 --disable_query_log
641 --error ER_ACCESS_DENIED_ERROR
642 connect(conn, localhost, usr, '$pswd2',);
643 --enable_query_log
644
645 --echo
646 connection default;
647
648 #secondary password will be discarded
649 ALTER USER usr@localhost DISCARD OLD PASSWORD;
650 --echo
651 connect(con1, localhost, usr, '$pswd3',);
652 --echo #--Logged in successfully through usr with primary password
653 SELECT CURRENT_USER();
654 --echo #--Disconnect from usr ...
655 disconnect con1;
656 --echo
657 connection default;
658 --echo
659 --disable_query_log
660 --error ER_ACCESS_DENIED_ERROR
661 connect(conn, localhost, usr, '$pswd1',);
662
663 --error ER_ACCESS_DENIED_ERROR
664 connect(conn, localhost, usr, '$pswd2',);
665
666 --enable_query_log
667 --echo
668 connection default;
669
670 DROP USER usr@localhost;
671
672
673 --echo #-----------------------------------------------------------------------
674 --echo # REQUIRE CURRENT
675 --echo #-----------------------------------------------------------------------
676 --echo
677
678 --connection default
679 # create user with random password
680 --replace_column 3 #####
681 CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
682
683 # alter user with random password PASSWORD REQUIRE CURRENT
684 let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD PASSWORD REQUIRE CURRENT;
685 let $pswd = query_get_value ( $qry, "generated password", 1 );
686
687 --echo
688 connect(con1, localhost, usr, '$pswd',);
689 --echo #--Logged in successfully through usr
690 select CURRENT_USER();
691 #--Execute alter user with replace
692 let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD REPLACE '$pswd' ;
693 let $pswd = query_get_value ( "$qry", "generated password", 1);
694 disconnect con1;
695
696 --echo
697 connect(con1, localhost, usr, '$pswd',);
698 --echo #--Logged in successfully through usr
699 select CURRENT_USER();
700 #--Execute alter user with replace
701 let $qry = SET PASSWORD TO RANDOM REPLACE '$pswd';
702 let $pswd = query_get_value ( "$qry", "generated password", 1);
703 disconnect con1;
704
705 --echo
706 connect(con1, localhost, usr, '$pswd',);
707 --echo #--Logged in successfully through usr
708 select CURRENT_USER();
709 --echo #--Execute set password
710 let $qry = SET PASSWORD FOR usr TO RANDOM REPLACE '$pswd';
711 let $pswd = query_get_value ( "$qry", "generated password", 1);
712 disconnect con1;
713 --echo
714
715
716 connect(conn, localhost, usr, '$pswd',);
717 # should not work
718 --error ER_INCORRECT_CURRENT_PASSWORD
719 ALTER USER usr IDENTIFIED BY RANDOM PASSWORD REPLACE 'qrst';
720 --error ER_INCORRECT_CURRENT_PASSWORD
721 SET PASSWORD TO RANDOM REPLACE 'qrst';
722 --error ER_INCORRECT_CURRENT_PASSWORD
723 SET PASSWORD FOR usr TO RANDOM REPLACE 'qrst';
724 disconnect conn;
725
726 -- connection default
727 DROP USER usr;
728
729 --echo #-----------------------------------------------------------------------
730 --echo # Plugin change
731 --echo #-----------------------------------------------------------------------
732 --echo
733 --connection default
734 --replace_column 3 ######
735 CREATE USER usr1@localhost IDENTIFIED BY RANDOM PASSWORD, usr2@localhost IDENTIFIED BY RANDOM PASSWORD, usr3@localhost IDENTIFIED BY RANDOM PASSWORD ;
736
737 --replace_column 3 ######
738 let $qry = ALTER USER usr1@localhost IDENTIFIED WITH 'mysql_native_password' BY RANDOM PASSWORD;
739 let $pswd1 = query_get_value ( $qry, "generated password", 1);
740
741 --echo
742 connect(con1, localhost, usr1, '$pswd1',);
743 --echo #--Logged in successfully through usr1
744 SELECT CURRENT_USER();
745 --echo "Disconnect from usr1 ..."
746 disconnect con1;
747 --echo
748
749 connection default;
750
751 --replace_column 3 ######
752 let $qry = ALTER USER usr2@localhost IDENTIFIED WITH 'sha256_password' BY RANDOM PASSWORD;
753 let $pswd2 = query_get_value ( $qry, "generated password", 1);
754
755 --echo
756 connect(con2, localhost, usr2, '$pswd2',);
757 --echo #--Logged in successfully through usr2
758 SELECT CURRENT_USER();
759 --echo #--Disconnect from usr2 ...
760 disconnect con2;
761 --echo
762
763 connection default;
764
765 --replace_column 3 ######
766 let $qry = ALTER USER usr3@localhost IDENTIFIED WITH 'caching_sha2_password' BY RANDOM PASSWORD;
767 let $pswd3 = query_get_value ( $qry, "generated password", 1);
768
769 --echo
770 connect(con3, localhost, usr3, '$pswd3',);
771 --echo #--Logged in successfully through usr3
772 SELECT CURRENT_USER();
773 --echo --echo #--Disconnect from usr3 ...
774 disconnect con3;
775 --echo
776 --echo Plugin change for usr1, usr2, usr3
777 --echo
778 connection default;
779
780 --replace_column 3 ######
781 let $qry = ALTER USER usr1@localhost IDENTIFIED WITH 'sha256_password' BY RANDOM PASSWORD;
782 let $pswd1 = query_get_value ( $qry, "generated password", 1);
783
784 --echo
785 connect(con1, localhost, usr1, '$pswd1',);
786 --echo #--Logged in successfully through usr1
787 SELECT CURRENT_USER();
788 --echo "Disconnect from usr1 ..."
789 disconnect con1;
790 --echo
791
792 connection default;
793
794 --replace_column 3 ######
795 let $qry = ALTER USER usr2@localhost IDENTIFIED WITH 'caching_sha2_password' BY RANDOM PASSWORD;
796 let $pswd2 = query_get_value ( $qry, "generated password", 1);
797
798 --echo
799 connect(con2, localhost, usr2, '$pswd2',);
800 --echo #--Logged in successfully through usr2
801 SELECT CURRENT_USER();
802 --echo #--Disconnect from usr2 ...
803 disconnect con2;
804 --echo
805
806 connection default;
807
808 --replace_column 3 ######
809 let $qry = ALTER USER usr3@localhost IDENTIFIED WITH 'mysql_native_password' BY RANDOM PASSWORD;
810 let $pswd3 = query_get_value ( $qry, "generated password", 1);
811
812 --echo
813 connect(con3, localhost, usr3, '$pswd3',);
814 --echo #--Logged in successfully through usr3
815 SELECT CURRENT_USER();
816 --echo --echo #--Disconnect from usr3 ...
817 disconnect con3;
818 --echo
819
820 connection default;
821
822 DROP USER usr1@localhost;
823 DROP USER usr2@localhost;
824 DROP USER usr3@localhost;
825
826 --echo #-----------------------------------------------------------------------
827 --echo # General log
828 --echo #-----------------------------------------------------------------------
829
830 # Keep this test at the end as it modifies many user properities
831
832 --connection default
833 # Clear the general_log file
834 TRUNCATE TABLE mysql.general_log;
835
836 SET @old_log_output = @@global.log_output;
837 -- echo @old_log_output
838 SET @old_general_log = @@global.general_log;
839 -- echo @old_general_log
840 SET @old_general_log_file = @@global.general_log_file;
841 --echo @old_general_log_file
842
843 let $general_file_off = $MYSQLTEST_VARDIR/log/random_password_general.log;
844 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
845 eval SET GLOBAL general_log_file = '$general_file_off';
846 SET GLOBAL log_output = 'FILE,TABLE';
847 SET GLOBAL general_log= 'ON';
848
849 --replace_column 3 ######
850 CREATE USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;
851 --replace_column 3 ######
852 ALTER USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;
853
854 let $qry = SET PASSWORD FOR usr@localhost to RANDOM;
855 let $pswd = query_get_value ( $qry, "generated password", 1);
856
857 --echo
858 connect(con1, localhost, usr, '$pswd',);
859 --echo #--Logged in successfully through usr
860 SELECT CURRENT_USER();
861 --replace_column 3 ######
862
863 SET PASSWORD TO RANDOM;
864 --echo #--Disconnect from usr ...
865 disconnect con1;
866
867 --echo
868 --connection default
869 --echo
870
871 --echo Display general_log content
872 --echo ------ rewrite ------
873 SELECT argument FROM mysql.general_log WHERE argument LIKE 'CREATE USER %' AND command_type NOT LIKE 'Prepare';
874 SELECT argument FROM mysql.general_log WHERE argument LIKE 'ALTER USER %' AND command_type NOT LIKE 'Prepare';
875 SELECT argument FROM mysql.general_log WHERE argument LIKE 'SET PASSWORD %';
876 --echo ------ done ------
877
878 DROP USER usr@localhost;
879
880 SET GLOBAL general_log_file = @old_general_log_file;
881 SET GLOBAL log_output = @old_log_output;
882 SET GLOBAL general_log= @old_general_log;
883 TRUNCATE TABLE mysql.general_log;
884
885 --echo #-----------------------------------------------------------------------
886 --echo # Binary log
887 --echo #-----------------------------------------------------------------------
888
889 --connection default
890 --source include/save_binlog_position.inc
891 --replace_column 3 #####
892 CREATE USER usr1 IDENTIFIED BY RANDOM PASSWORD, usr2 IDENTIFIED BY RANDOM PASSWORD;
893 --let $event= !Q(CREATE USER.*usr1.*usr2.*)
894 --source ../include/auth_sec_assert_binlog_events.inc
895 --source include/save_binlog_position.inc
896
897 --replace_column 3 #####
898 ALTER USER usr1 IDENTIFIED BY RANDOM PASSWORD, usr2 IDENTIFIED BY RANDOM PASSWORD;
899 --let $event= !Q(ALTER USER.*usr1.*usr2.*)
900 --source ../include/auth_sec_assert_binlog_events.inc
901 --source include/save_binlog_position.inc
902
903 --replace_column 3 #####
904 SET PASSWORD FOR usr1 TO RANDOM;
905 --let $event= !Q(ALTER USER.*usr1.*)
906 --source ../include/auth_sec_assert_binlog_events.inc
907 --source include/save_binlog_position.inc
908
909 DROP USER usr1, usr2;
910
911 --echo #-----------------------------------------------------------------------
912 --echo # Test NEW variable generated_random_password_length
913 --echo #-----------------------------------------------------------------------
914
915 --echo #--Print default values of the variable
916 SHOW VARIABLES LIKE 'generated_random_password_length';
917 SELECT @@GLOBAL.generated_random_password_length;
918 SELECT @@LOCAL.generated_random_password_length;
919 SELECT @@SESSION.generated_random_password_length;
920 SELECT @@generated_random_password_length;
921 --echo
922 --echo #--set global value to 10
923 SET GLOBAL generated_random_password_length = 10;
924 SELECT @@GLOBAL.generated_random_password_length;
925 --echo
926 --echo #--set session value to 30
927 SET SESSION generated_random_password_length = 30;
928 SELECT @@SESSION.generated_random_password_length;
929 --echo
930 --echo #--set session value to 40
931 SET @@GLOBAL.generated_random_password_length = 40;
932 SELECT @@GLOBAL.generated_random_password_length;
933 --echo
934 --echo #--set session value to 50
935 SET @@SESSION.generated_random_password_length = 50;
936 SELECT @@SESSION.generated_random_password_length;
937 #--echo
938 #--echo #--set persist value to 70
939 #SET @@PERSIST.generated_random_password_length = 70;
940 #SELECT @@GLOBAL.generated_random_password_length;
941 #--echo
942 #RESET PERSIST generated_random_password_length;
943 --echo
944 --echo #--set session value to 80
945 SET @@generated_random_password_length = 80;
946 SELECT @@SESSION.generated_random_password_length;
947 --echo
948 --echo #--set session value to 90
949 SET generated_random_password_length = 90;
950 SELECT @@SESSION.generated_random_password_length;
951 --echo
952 --echo #--set session value to DEFAULT
953 SET SESSION generated_random_password_length = DEFAULT;
954 SELECT @@SESSION.generated_random_password_length;
955 --echo
956 --echo #--set global value to DEFAULT
957 SET GLOBAL generated_random_password_length = DEFAULT;
958 SELECT @@GLOBAL.generated_random_password_length;
959
960 #-------------------------------
961 # Min value of the variable
962 #-------------------------------
963 --echo
964 --echo #--Print min values of the variable
965 --echo #--set global value to 5
966 SET @@GLOBAL.generated_random_password_length = 5;
967 SELECT @@GLOBAL.generated_random_password_length;
968 --echo
969 --echo #--set global value to 4
970 SET @@GLOBAL.generated_random_password_length = 4;
971 SELECT @@GLOBAL.generated_random_password_length;
972 --echo
973 --echo #--set session value to 5
974 SET @@SESSION.generated_random_password_length = 5;
975 SELECT @@SESSION.generated_random_password_length;
976 --echo
977 --echo #--set session value to 4
978 SET @@SESSION.generated_random_password_length = 4;
979 SELECT @@SESSION.generated_random_password_length;
980
981 --echo
982 --echo #--Test invalid values
983 --error ER_PARSE_ERROR
984 SET @@GLOBAL.generated_random_password_length =;
985
986 --error ER_WRONG_TYPE_FOR_VAR
987 SET @@GLOBAL.generated_random_password_length = b;
988
989 --error ER_WRONG_TYPE_FOR_VAR
990 SET GLOBAL generated_random_password_length = b;
991
992 --error ER_WRONG_TYPE_FOR_VAR
993 SET @@LOCAL.generated_random_password_length = '4';
994
995 --error ER_WRONG_TYPE_FOR_VAR
996 SET LOCAL generated_random_password_length = '10';
997
998 --error ER_WRONG_TYPE_FOR_VAR
999 SET @@GLOBAL.generated_random_password_length = 800000000000000000000000000000000000;
1000
1001 --error ER_WRONG_TYPE_FOR_VAR
1002 SET SESSION generated_random_password_length = 800000000000000000000000000000000000;
1003
1004 --error ER_WRONG_TYPE_FOR_VAR
1005 SET @@GLOBAL.generated_random_password_length = 3.5;
1006
1007 SET @@GLOBAL.generated_random_password_length = -4;
1008 SELECT @@GLOBAL.generated_random_password_length;
1009
1010 SET LOCAL generated_random_password_length = -10;
1011 SELECT @@LOCAL.generated_random_password_length;
1012
1013 #-------------------------------
1014 # Max value of the variable
1015 #-------------------------------
1016 --echo
1017 --echo #--Print Max values of the variable
1018 --echo #--set global value to 255
1019 SET @@GLOBAL.generated_random_password_length = 255;
1020 SELECT @@GLOBAL.generated_random_password_length;
1021 --echo
1022 --echo #--set global value to 256
1023 SET @@GLOBAL.generated_random_password_length = 256;
1024 SELECT @@GLOBAL.generated_random_password_length;
1025 --echo
1026 --echo #--set session value to 255
1027 SET @@SESSION.generated_random_password_length = 255;
1028 SELECT @@SESSION.generated_random_password_length;
1029 --echo
1030 --echo #--set session value to 256
1031 SET @@SESSION.generated_random_password_length = 256;
1032 SELECT @@SESSION.generated_random_password_length;
1033 --echo
1034 --echo #--set global and session to DEFAULT
1035 SET GLOBAL generated_random_password_length = DEFAULT;
1036 SET SESSION generated_random_password_length = DEFAULT;
1037
1038 #---------------------------------------------------------------------
1039 # create user/alter user/set password with various password lengths
1040 #---------------------------------------------------------------------
1041 --echo #--Test GLOBAL setting
1042 --echo #-- 1) Test with a lower value
1043 --echo #--set global to value 5
1044 SET @@GLOBAL.generated_random_password_length = 5;
1045 SELECT @@GLOBAL.generated_random_password_length;
1046
1047 --echo
1048 --echo #--create user usr
1049 let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
1050 let $pswd = query_get_value ( $qry, "generated password", 1 );
1051 --echo
1052 --echo #--login to user usr
1053 connect(con1, localhost, usr, $pswd,);
1054 --echo Logged in successfully through usr
1055 SELECT CURRENT_USER();
1056 SELECT @@GLOBAL.generated_random_password_length;
1057 SELECT @@SESSION.generated_random_password_length;
1058 --echo "Disconnect from usr ..."
1059 disconnect con1;
1060
1061 --echo
1062 --echo #--alter user usr
1063 --connection default
1064 let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
1065 let $pswd = query_get_value ( $qry, "generated password", 1 );
1066 --echo
1067 --echo #--login to user usr
1068 connect(con1, localhost, usr, $pswd,);
1069 --echo Logged in successfully through usr
1070 SELECT CURRENT_USER();
1071 SELECT @@GLOBAL.generated_random_password_length;
1072 SELECT @@SESSION.generated_random_password_length;
1073 --echo "Disconnect from usr ..."
1074 disconnect con1;
1075
1076 --connection default
1077 --echo
1078 --echo #--set password for usr
1079 let $qry = SET PASSWORD FOR usr TO RANDOM;
1080 let $pswd = query_get_value ( $qry, "generated password", 1 );
1081 --echo
1082 --echo #--login to user usr
1083 connect(con1, localhost, usr, $pswd,);
1084 --echo Logged in successfully through usr
1085 SELECT CURRENT_USER();
1086 SELECT @@GLOBAL.generated_random_password_length;
1087 SELECT @@SESSION.generated_random_password_length;
1088 --echo "Disconnect from usr ..."
1089 disconnect con1;
1090
1091 --connection default
1092 DROP USER usr;
1093 SET @@GLOBAL.generated_random_password_length = DEFAULT;
1094
1095 --echo
1096 --echo #-- 2) Test with the max value
1097 --echo #--set global to value 255
1098 SET @@GLOBAL.generated_random_password_length = 255;
1099 SELECT @@GLOBAL.generated_random_password_length;
1100 --echo
1101 --echo #-- create user usr
1102 let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
1103 let $pswd = query_get_value ( $qry, "generated password", 1 );
1104
1105 --echo
1106 --echo #--login to user usr
1107 connect(con1, localhost, usr, $pswd,);
1108 --echo Logged in successfully through usr
1109 SELECT CURRENT_USER();
1110 SELECT @@GLOBAL.generated_random_password_length;
1111 SELECT @@SESSION.generated_random_password_length;
1112 --echo "Disconnect from usr ..."
1113 disconnect con1;
1114
1115 --connection default
1116 --echo
1117 --echo #--alter user usr
1118 let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
1119 let $pswd = query_get_value ( $qry, "generated password", 1 );
1120
1121 --echo
1122 --echo #--login to user usr
1123 connect(con1, localhost, usr, $pswd,);
1124 --echo Logged in successfully through usr
1125 SELECT CURRENT_USER();
1126 SELECT @@GLOBAL.generated_random_password_length;
1127 SELECT @@SESSION.generated_random_password_length;
1128 --echo "Disconnect from usr ..."
1129 disconnect con1;
1130
1131 --connection default
1132 --echo
1133 --echo #--set password user usr
1134 let $qry = SET PASSWORD FOR usr TO RANDOM;
1135 let $pswd = query_get_value ( $qry, "generated password", 1 );
1136 --echo
1137 --echo #--login to user usr
1138 connect(con1, localhost, usr, $pswd,);
1139 --echo Logged in successfully through usr
1140 SELECT CURRENT_USER();
1141 SELECT @@GLOBAL.generated_random_password_length;
1142 SELECT @@SESSION.generated_random_password_length;
1143 --echo "Disconnect from usr ..."
1144 disconnect con1;
1145
1146 --connection default
1147 DROP USER usr;
1148
1149 SET @@GLOBAL.generated_random_password_length = DEFAULT;
1150
1151 --echo
1152 --echo #-- 3) Test with lower than min value
1153 --echo #--set session to value 0
1154 SET @@GLOBAL.generated_random_password_length = 0;
1155 SELECT @@GLOBAL.generated_random_password_length;
1156 --echo
1157 --echo #--create user usr
1158 let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
1159 let $pswd = query_get_value ( $qry, "generated password", 1 );
1160 --echo
1161 --echo #--login to user usr
1162 connect(con1, localhost, usr, $pswd,);
1163 --echo Logged in successfully through usr
1164 SELECT CURRENT_USER();
1165 SELECT @@GLOBAL.generated_random_password_length;
1166 SELECT @@SESSION.generated_random_password_length;
1167 --echo "Disconnect from usr ..."
1168 disconnect con1;
1169
1170 --connection default
1171 --echo
1172 --echo #--create user usr
1173 let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
1174 let $pswd = query_get_value ( $qry, "generated password", 1 );
1175 --echo
1176 --echo #--login to usr
1177 connect(con1, localhost, usr, $pswd,);
1178 --echo Logged in successfully through usr
1179 SELECT CURRENT_USER();
1180 SELECT @@GLOBAL.generated_random_password_length;
1181 SELECT @@SESSION.generated_random_password_length;
1182 --echo "Disconnect from usr ..."
1183 disconnect con1;
1184
1185 --echo
1186 --echo #--set password for usr
1187 --connection default
1188 let $qry = SET PASSWORD FOR usr TO RANDOM;
1189 let $pswd = query_get_value ( $qry, "generated password", 1 );
1190
1191 --echo
1192 --echo #--login to usr
1193 connect(con1, localhost, usr, $pswd,);
1194 --echo Logged in successfully through usr
1195 SELECT CURRENT_USER();
1196 SELECT @@GLOBAL.generated_random_password_length;
1197 SELECT @@SESSION.generated_random_password_length;
1198 --echo "Disconnect from usr ..."
1199 disconnect con1;
1200
1201 --connection default
1202
1203 DROP USER usr;
1204
1205 SET @@GLOBAL.generated_random_password_length = DEFAULT;
1206
1207 --echo
1208 --echo #-- 3) Test with a value which has crossed its max value
1209 --echo #--set global to value 256
1210
1211 SET @@GLOBAL.generated_random_password_length = 256;
1212 SELECT @@GLOBAL.generated_random_password_length;
1213 --echo
1214 --echo #--create user usr
1215 let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
1216 let $pswd = query_get_value ( $qry, "generated password", 1 );
1217 --echo
1218 --echo #--connect to usr
1219 connect(con1, localhost, usr, $pswd,);
1220 --echo Logged in successfully through usr
1221 SELECT CURRENT_USER();
1222 SELECT @@GLOBAL.generated_random_password_length;
1223 SELECT @@SESSION.generated_random_password_length;
1224 --echo "Disconnect from usr ..."
1225 disconnect con1;
1226
1227 --connection default
1228 --echo
1229 --echo #--alter user usr
1230 let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
1231 let $pswd = query_get_value ( $qry, "generated password", 1 );
1232 --echo
1233 --echo #--connect to usr
1234 connect(con1, localhost, usr, $pswd,);
1235 --echo Logged in successfully through usr
1236 SELECT CURRENT_USER();
1237 SELECT @@GLOBAL.generated_random_password_length;
1238 SELECT @@SESSION.generated_random_password_length;
1239 --echo "Disconnect from usr ..."
1240 disconnect con1;
1241 --echo
1242 --echo #--set password
1243 --connection default
1244 let $qry = SET PASSWORD FOR usr TO RANDOM;
1245 let $pswd = query_get_value ( $qry, "generated password", 1 );
1246 --echo
1247 --echo #--connect to usr
1248 connect(con1, localhost, usr, $pswd,);
1249 --echo Logged in successfully through usr
1250 SELECT CURRENT_USER();
1251 SELECT @@GLOBAL.generated_random_password_length;
1252 SELECT @@SESSION.generated_random_password_length;
1253 --echo "Disconnect from usr ..."
1254 disconnect con1;
1255
1256 --connection default
1257 DROP USER usr;
1258
1259 --echo #--Test SESSION setting
1260 --echo #-- 1) Test with a lower value
1261 --echo #--set session to value 5
1262 SET @@SESSION.generated_random_password_length = 5;
1263 SELECT @@SESSION.generated_random_password_length;
1264 --echo
1265 --echo #--create user usr
1266 let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
1267 let $pswd = query_get_value ( $qry, "generated password", 1 );
1268 --echo
1269 --echo #--connect to usr
1270 connect(con1, localhost, usr, $pswd,);
1271 --echo Logged in successfully through usr
1272 SELECT CURRENT_USER();
1273 SELECT @@GLOBAL.generated_random_password_length;
1274 SELECT @@SESSION.generated_random_password_length;
1275 --echo "Disconnect from usr ..."
1276 disconnect con1;
1277 --echo
1278 --echo #--alter user usr
1279 --connection default
1280 let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
1281 let $pswd = query_get_value ( $qry, "generated password", 1 );
1282 --echo
1283 --echo #--connect to usr
1284 connect(con1, localhost, usr, $pswd,);
1285 --echo Logged in successfully through usr
1286 SELECT CURRENT_USER();
1287 SELECT @@GLOBAL.generated_random_password_length;
1288 SELECT @@SESSION.generated_random_password_length;
1289 --echo "Disconnect from usr ..."
1290 disconnect con1;
1291 --echo
1292 --echo #--set password
1293 --connection default
1294 let $qry = SET PASSWORD FOR usr TO RANDOM;
1295 let $pswd = query_get_value ( $qry, "generated password", 1 );
1296 --echo
1297 --echo #--connect to usr
1298 connect(con1, localhost, usr, $pswd,);
1299 --echo Logged in successfully through usr
1300 SELECT CURRENT_USER();
1301 SELECT @@GLOBAL.generated_random_password_length;
1302 SELECT @@SESSION.generated_random_password_length;
1303 --echo "Disconnect from usr ..."
1304 disconnect con1;
1305
1306 --connection default
1307 DROP USER usr;
1308
1309 --echo #-- 2) Test with the max value
1310 --echo #--set session to value 255
1311 SET @@SESSION.generated_random_password_length = 255;
1312 SELECT @@SESSION.generated_random_password_length;
1313 --echo
1314 --echo #--create user usr
1315 let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
1316 let $pswd = query_get_value ( $qry, "generated password", 1 );
1317 --echo
1318 --echo #--connect to usr
1319 connect(con1, localhost, usr, $pswd,);
1320 --echo Logged in successfully through usr
1321 SELECT CURRENT_USER();
1322 SELECT @@GLOBAL.generated_random_password_length;
1323 SELECT @@SESSION.generated_random_password_length;
1324 --echo "Disconnect from usr ..."
1325 disconnect con1;
1326
1327 --connection default
1328 --echo
1329 --echo #--alter user usr
1330 let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
1331 let $pswd = query_get_value ( $qry, "generated password", 1 );
1332 --echo
1333 --echo #--connect to usr
1334 connect(con1, localhost, usr, $pswd,);
1335 --echo Logged in successfully through usr
1336 SELECT CURRENT_USER();
1337 SELECT @@GLOBAL.generated_random_password_length;
1338 SELECT @@SESSION.generated_random_password_length;
1339 --echo "Disconnect from usr ..."
1340 disconnect con1;
1341 --echo
1342 --echo #--set password
1343 --connection default
1344 let $qry = SET PASSWORD FOR usr TO RANDOM;
1345 let $pswd = query_get_value ( $qry, "generated password", 1 );
1346 --echo
1347 --echo #--connect to usr
1348 connect(con1, localhost, usr, $pswd,);
1349 --echo Logged in successfully through usr
1350 SELECT CURRENT_USER();
1351 SELECT @@GLOBAL.generated_random_password_length;
1352 SELECT @@SESSION.generated_random_password_length;
1353 --echo "Disconnect from usr ..."
1354 disconnect con1;
1355
1356 --connection default
1357 DROP USER usr;
1358
1359 --echo #-- 3) Test will value less than min value
1360 --echo #--set session to value 0
1361 SET @@SESSION.generated_random_password_length = 0;
1362 SELECT @@SESSION.generated_random_password_length;
1363 --echo
1364 --echo #--create user usr
1365 let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
1366 let $pswd = query_get_value ( $qry, "generated password", 1 );
1367 --echo
1368 --echo #--connect to usr
1369 connect(con1, localhost, usr, $pswd,);
1370 --echo Logged in successfully through usr
1371 SELECT CURRENT_USER();
1372 SELECT @@GLOBAL.generated_random_password_length;
1373 SELECT @@SESSION.generated_random_password_length;
1374 --echo "Disconnect from usr ..."
1375 disconnect con1;
1376 --echo
1377 --echo #--alter user usr
1378 --connection default
1379 let $qry = ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
1380 let $pswd = query_get_value ( $qry, "generated password", 1 );
1381 --echo
1382 --echo #--connect to usr
1383 connect(con1, localhost, usr, $pswd,);
1384 --echo Logged in successfully through usr
1385 SELECT CURRENT_USER();
1386 SELECT @@GLOBAL.generated_random_password_length;
1387 SELECT @@SESSION.generated_random_password_length;
1388 --echo "Disconnect from usr ..."
1389 disconnect con1;
1390 --echo
1391 --echo #--set password
1392 --connection default
1393 let $qry = SET PASSWORD FOR usr TO RANDOM;
1394 let $pswd = query_get_value ( $qry, "generated password", 1 );
1395 --echo
1396 --echo #--connect to usr
1397 connect(con1, localhost, usr, $pswd,);
1398 --echo Logged in successfully through usr
1399 SELECT CURRENT_USER();
1400 SELECT @@GLOBAL.generated_random_password_length;
1401 SELECT @@SESSION.generated_random_password_length;
1402 --echo "Disconnect from usr ..."
1403 disconnect con1;
1404
1405 --connection default
1406 DROP USER usr;
1407
1408 --echo
1409 #------------------------------
1410 # User without privileges
1411 #------------------------------
1412 --echo #--Test variable value setting with/without user privileges
1413 --echo
1414 let $qry = CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
1415 let $pswd = query_get_value ( $qry, "generated password", 1);
1416
1417 connect(con1, localhost, usr, '$pswd',);
1418 --echo #--Non-privileged user unable to set global value
1419 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
1420 SET @@GLOBAL.generated_random_password_length = 48;
1421 SELECT @@GLOBAL.generated_random_password_length;
1422 --echo
1423 --echo #--Non-privileged user able to set session value
1424 SET @@SESSION.generated_random_password_length = 48;
1425 SELECT @@SESSION.generated_random_password_length;
1426 disconnect con1;
1427 --echo
1428 connection default;
1429 GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO usr;
1430 FLUSH PRIVILEGES;
1431
1432 # User with privilege
1433 connect(con1, localhost, usr, '$pswd',);
1434 --echo
1435 --echo #--Privileged user able to set global value
1436 SET @@GLOBAL.generated_random_password_length = 48;
1437 SELECT @@GLOBAL.generated_random_password_length;
1438 --echo
1439 --echo #--Privileged user able to set session value
1440 SET @@SESSION.generated_random_password_length = 48;
1441 SELECT @@SESSION.generated_random_password_length;
1442
1443 disconnect con1;
1444
1445 # Cleanup
1446 connection default;
1447 REVOKE SYSTEM_VARIABLES_ADMIN ON *.* FROM usr;
1448 FLUSH PRIVILEGES;
1449 drop user usr;
1450 SET GLOBAL generated_random_password_length = DEFAULT;