Debian Codegraph @

mysql-test/suite/auth_sec/r/random_password.result

raw
1 SELECT @@GLOBAL.generated_random_password_length;
2 @@GLOBAL.generated_random_password_length
3 20
4 SELECT @@SESSION.generated_random_password_length;
5 @@SESSION.generated_random_password_length
6 20
7 CREATE USER u1 IDENTIFIED BY RANDOM PASSWORD, u2 IDENTIFIED BY RANDOM PASSWORD, u3 IDENTIFIED BY RANDOM PASSWORD;
8 user host generated password auth_factor
9 u1 % random 1
10 u2 % random 1
11 u3 % random 1
12 CREATE USER u4 IDENTIFIED BY RANDOM PASSWORD;
13 user host generated password auth_factor
14 u4 % random 1
15 select current_user;
16 current_user
17 foo@%
18 ** Verify that REPLACE-syntax works
19 user host generated password auth_factor
20 foo % random 1
21 ** Verify that RETAIN-syntax works
22 user host generated password auth_factor
23 foo % random 1
24 ** Verify that ALTER USER works as expected.
25 select current_user;
26 current_user
27 foo@%
28 ** Verify that REPLACE-syntax works
29 user host generated password auth_factor
30 foo % random 1
31 SET PASSWORD FOR u1 = 'foo';
32 ** Verify that it works for current user.
33 SET PASSWORD TO RANDOM;
34 user host generated password auth_factor
35 u1 % random 1
36 SELECT CURRENT_USER();
37 CURRENT_USER()
38 u1@%
39 ** Test password length
40 set session generated_random_password_length=6;
41 Password length
42 6
43 set session generated_random_password_length=66;
44 Password length
45 66
46 DROP USER u1,u2,u3,u4,foo;
47 CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
48 user host generated password auth_factor
49 usr % random 1
50 Logged in successfully through usr
51 SELECT CURRENT_USER();
52 CURRENT_USER()
53 usr@%
54 "Disconnect from usr ..."
55 DROP USER usr;
56 Logged in successfully through usr1
57 SELECT CURRENT_USER();
58 CURRENT_USER()
59 usr1@%
60 "Disconnect from usr1 ..."
61 DROP USER usr1;
62
63 #-- Testing Syntax of the new CREATE USER/ALTER USER/SET PASSWORD extension
64
65 #--Valid combinations/ syntaxes of RANDOM PASSWORD
66 CREATE USER 'usr'@'localhost' IDENTIFIED BY RANDOM PASSWORD;
67 user host generated password auth_factor
68 usr localhost ##### 1
69 ALTER USER 'usr'@'localhost' IDENTIFIED BY RANDOM PASSWORD;
70 user host generated password auth_factor
71 usr localhost ##### 1
72 SET PASSWORD for 'usr'@'localhost' TO RANDOM;
73 user host generated password auth_factor
74 usr localhost ##### 1
75 DROP USER 'usr'@'localhost';
76
77 CREATE USER 'usr' IDENTIFIED BY RANDOM PASSWORD;
78 user host generated password auth_factor
79 usr % ##### 1
80 ALTER USER 'usr' IDENTIFIED BY RANDOM PASSWORD;
81 user host generated password auth_factor
82 usr % ##### 1
83 SET PASSWORD for 'usr' TO RANDOM;
84 user host generated password auth_factor
85 usr % ##### 1
86 DROP USER 'usr';
87
88 CREATE USER 'usr'@'%' IDENTIFIED BY RANDOM PASSWORD;
89 user host generated password auth_factor
90 usr % ##### 1
91 ALTER USER 'usr'@'%' IDENTIFIED BY RANDOM PASSWORD;
92 user host generated password auth_factor
93 usr % ##### 1
94 SET PASSWORD for 'usr'@'%' TO RANDOM;
95 user host generated password auth_factor
96 usr % ##### 1
97 DROP USER 'usr'@'%';
98
99 CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
100 user host generated password auth_factor
101 usr % ##### 1
102 ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
103 user host generated password auth_factor
104 usr % ##### 1
105 SET PASSWORD for usr TO RANDOM;
106 user host generated password auth_factor
107 usr % ##### 1
108 DROP USER usr;
109
110 CREATE USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;
111 user host generated password auth_factor
112 usr localhost ##### 1
113 ALTER USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;
114 user host generated password auth_factor
115 usr localhost ##### 1
116 SET PASSWORD for usr@localhost TO RANDOM;
117 user host generated password auth_factor
118 usr localhost ##### 1
119 DROP USER usr@localhost;
120
121 CREATE USER usr@'%' IDENTIFIED BY RANDOM PASSWORD;
122 user host generated password auth_factor
123 usr % ##### 1
124 ALTER USER usr@'%' IDENTIFIED BY RANDOM PASSWORD;
125 user host generated password auth_factor
126 usr % ##### 1
127 SET PASSWORD for usr@'%' TO RANDOM;
128 user host generated password auth_factor
129 usr % ##### 1
130 DROP USER usr@'%';
131
132 CREATE USER 'usr'@localhost IDENTIFIED BY RANDOM PASSWORD;
133 user host generated password auth_factor
134 usr localhost ##### 1
135 ALTER USER 'usr'@localhost IDENTIFIED BY RANDOM PASSWORD;
136 user host generated password auth_factor
137 usr localhost ##### 1
138 SET PASSWORD for 'usr'@localhost TO RANDOM;
139 user host generated password auth_factor
140 usr localhost ##### 1
141 DROP USER 'usr'@localhost;
142
143 CREATE USER usr@'localhost' IDENTIFIED BY RANDOM PASSWORD;
144 user host generated password auth_factor
145 usr localhost ##### 1
146 ALTER USER usr@'localhost' IDENTIFIED BY RANDOM PASSWORD;
147 user host generated password auth_factor
148 usr localhost ##### 1
149 SET PASSWORD for usr@'localhost' TO RANDOM;
150 user host generated password auth_factor
151 usr localhost ##### 1
152 DROP USER usr@'localhost';
153
154 CREATE USER usr IDENTIFIED BY Random Password;
155 user host generated password auth_factor
156 usr % ##### 1
157 DROP USER usr;
158 CREATE USER usr IDENTIFIED BY random password;
159 user host generated password auth_factor
160 usr % ##### 1
161 DROP USER usr;
162 CREATE USER usr IDENTIFIED BY rANDOm PASSword;
163 user host generated password auth_factor
164 usr % ##### 1
165 DROP USER usr;
166
167 CREATE USER usr1 IDENTIFIED BY RANDOM PASSWORD,
168 usr2 IDENTIFIED BY RANDOM PASSWORD,
169 usr3 IDENTIFIED BY RANDOM PASSWORD;
170 user host generated password auth_factor
171 usr1 % ##### 1
172 usr2 % ##### 1
173 usr3 % ##### 1
174 ALTER USER usr1 IDENTIFIED BY RANDOM PASSWORD,
175 usr2 IDENTIFIED BY RANDOM PASSWORD,
176 usr3 IDENTIFIED BY RANDOM PASSWORD;
177 user host generated password auth_factor
178 usr1 % ##### 1
179 usr2 % ##### 1
180 usr3 % ##### 1
181 DROP USER usr1;
182 DROP USER usr2;
183 DROP USER usr3;
184
185 #--Invalid combinations/ syntaxes of RANDOM PASSWORD
186 CREATE USER usr IDENTIFIED BY RANDOM;
187 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
188
189 CREATE USER usr IDENTIFIED BY RANDOM PASSWD;
190 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWD' at line 1
191
192 CREATE USER usr IDENTIFIED RANDOM PASSWORD;
193 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RANDOM PASSWORD' at line 1
194
195 CREATE USER usr BY RANDOM PASSWORD;
196 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BY RANDOM PASSWORD' at line 1
197
198 CREATE USER 'usr'@'%' IDENTIFIED BY RANDOM PASSWORD;
199 user host generated password auth_factor
200 usr % ###### 1
201
202 ALTER USER usr IDENTIFIED BY RANDOM;
203 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
204
205 ALTER USER usr IDENTIFIED BY RANDOM PASSWD;
206 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWD' at line 1
207
208 ALTER USER usr IDENTIFIED RANDOM PASSWORD;
209 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RANDOM PASSWORD' at line 1
210
211 ALTER USER usr BY RANDOM PASSWORD;
212 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BY RANDOM PASSWORD' at line 1
213
214 SET PASSWORD for usr RANDOM;
215 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RANDOM' at line 1
216 DROP USER usr;
217
218 ALTER USER usr IDENTIFIED BY RANDOM PASSWORD;
219 ERROR HY000: Operation ALTER USER failed for 'usr'@'%'
220
221 CREATE USER 'usr'@'%' IDENTIFIED BY RANDOM PASSWORD;
222 user host generated password auth_factor
223 usr % ###### 1
224
225 ALTER USER 'usr'@'localhost' IDENTIFIED BY RANDOM PASSWORD;
226 ERROR HY000: Operation ALTER USER failed for 'usr'@'localhost'
227 DROP USER usr;
228
229 #-------------------------------------------------------------
230 # Privileges and Users
231 #-------------------------------------------------------------
232
233
234 #--CREATE temp user with RANDOM PASSWORD
235 CREATE USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
236 user host generated password auth_factor
237 usr_temp % ###### 1
238 #--CREATE user with RANDOM PASSWORD
239
240 #--Login to usr with RANDOM PASSWORD
241 #--Logged in successfully through usr
242 SELECT CURRENT_USER();
243 CURRENT_USER()
244 usr@%
245 #--Disconnect from usr ...
246 SET PASSWORD FOR usr_temp TO RANDOM;
247 ERROR 42000: Access denied for user 'usr'@'%' to database 'mysql'
248 ALTER USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
249 ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
250 CREATE USER usr_ntemp IDENTIFIED BY RANDOM PASSWORD;
251 ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
252 #--Disconnect from usr ...
253 DROP USER usr;
254 DROP USER usr_temp;
255
256 #--CREATE temp user with RANDOM PASSWORD
257 CREATE USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
258 user host generated password auth_factor
259 usr_temp % ##### 1
260
261 #--CREATE user with RANDOM PASSWORD
262
263 #--GRANT ALL PRIVILEGES TO user
264 GRANT ALL ON *.* TO usr;
265 FLUSH PRIVILEGES;
266
267 #--Login to usr with RANDOM PASSWORD
268 #--Logged in successfully through usr
269 SELECT CURRENT_USER();
270 CURRENT_USER()
271 usr@%
272 #--SET PASSWORD for user with RANDOM PASSWORD
273 #--Disconnect from usr ...
274
275 #--Login to usr with RANDOM PASSWORD
276 #--SET PASSWORD for user with RANDOM PASSWORD
277 SET PASSWORD FOR usr_temp TO RANDOM;
278 user host generated password auth_factor
279 usr_temp % ##### 1
280
281 #--ALTER user with RANDOM PASSWORD
282 ALTER USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
283 user host generated password auth_factor
284 usr_temp % ##### 1
285
286 #--CREATE tempuser with RANDOM PASSWORD
287 CREATE USER usr_ntemp IDENTIFIED BY RANDOM PASSWORD;
288 user host generated password auth_factor
289 usr_ntemp % ##### 1
290 #--Disconnect from usr ...
291
292 #--ALTER user with RANDOM PASSWORD
293
294 #--Login to usr with RANDOM PASSWORD
295 #--SET PASSWORD for user with RANDOM PASSWORD
296 #--Disconnect from usr ...
297
298
299 #--Login to usr with RANDOM PASSWORD
300
301 #--SET PASSWORD for user with RANDOM PASSWORD
302 SET PASSWORD FOR root TO RANDOM;
303 ERROR 42000: Can't find any matching row in the user table
304 #--ALTER root with RANDOM PASSWORD
305 ALTER USER root IDENTIFIED BY RANDOM PASSWORD;
306 ERROR HY000: Operation ALTER USER failed for 'root'@'%'
307 DROP USER usr;
308 DROP USER usr_temp;
309 DROP USER usr_ntemp;
310
311 CREATE USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
312 user host generated password auth_factor
313 usr_temp % ##### 1
314 GRANT INSERT ON *.* TO usr_temp;
315 FLUSH PRIVILEGES;
316
317 #--Logged in successfully through usr
318 SELECT CURRENT_USER();
319 CURRENT_USER()
320 usr@%
321 CREATE USER usr_ntemp IDENTIFIED BY RANDOM PASSWORD;
322 ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
323 ALTER USER usr_temp IDENTIFIED BY RANDOM PASSWORD;
324 ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
325
326 DROP USER usr;
327 DROP USER usr_temp;
328 CREATE USER usr IDENTIFIED by 'foobar';
329
330 #--Logged in successfully through usr
331 SELECT CURRENT_USER();
332 CURRENT_USER()
333 usr@%
334 #--Disconnect from usr ...
335
336 DROP USER usr;
337 CREATE USER usr IDENTIFIED by 'foobar';
338
339 #--Logged in successfully through usr
340 SELECT CURRENT_USER();
341 CURRENT_USER()
342 usr@%
343 #--Disconnect from usr ...
344
345 DROP USER usr;
346 CREATE USER usr;
347 #--Logged in successfully through usr
348 SELECT CURRENT_USER();
349 CURRENT_USER()
350 usr@%
351 #--Disconnect from usr ...
352 DROP USER usr;
353 CREATE USER usr;
354 #--Logged in successfully through usr
355 SELECT CURRENT_USER();
356 CURRENT_USER()
357 usr@%
358 #--Disconnect from usr ...
359 DROP USER usr;
360 CREATE USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;
361 user host generated password auth_factor
362 usr localhost ###### 1
363 #-----------------------------------------------------------------------
364 # RETAIN CURRENT PASSWORD with ALTER USER and SET PASSWORD
365 #-----------------------------------------------------------------------
366
367 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
368 AS ADDITIONAL_PASSWORD_FOR_USR FROM mysql.user WHERE user LIKE 'usr';
369 ADDITIONAL_PASSWORD_FOR_USR
370 0
371
372
373 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
374 AS ADDITIONAL_PASSWORD_FOR_USR FROM mysql.user WHERE user LIKE 'usr';
375 ADDITIONAL_PASSWORD_FOR_USR
376 1
377
378 SET @pswd1=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
379 '$.additional_password') FROM mysql.user WHERE user LIKE 'usr');
380
381
382 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
383 AS ADDITIONAL_PASSWORD_FOR_USR FROM mysql.user WHERE user LIKE 'usr';
384 ADDITIONAL_PASSWORD_FOR_USR
385 1
386
387 SET @pswd2=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
388 '$.additional_password') FROM mysql.user WHERE user LIKE 'usr');
389
390 SELECT @pswd1 <> @pswd2 AS ADDITIONAL_PASSWORD_CHANGED;
391 ADDITIONAL_PASSWORD_CHANGED
392 1
393
394 #--Logged in successfully through usr with secondary password
395 SELECT CURRENT_USER();
396 CURRENT_USER()
397 usr@localhost
398 #--Disconnect from usr ...
399
400 #--Logged in successfully through usr with primary password
401 SELECT CURRENT_USER();
402 CURRENT_USER()
403 usr@localhost
404 #--Disconnect from usr ...
405
406
407
408 SELECT COUNT(*) AS ADDITIONAL_PASSWORD_FOR_USR FROM mysql.user
409 WHERE user LIKE 'usr';
410 ADDITIONAL_PASSWORD_FOR_USR
411 1
412
413 SET @pswd3=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
414 '$.additional_password') FROM mysql.user WHERE user LIKE 'usr');
415
416 SELECT @pswd2 = @pswd3 AS ADDITIONAL_PASSWORD_NOT_CHANGED;
417 ADDITIONAL_PASSWORD_NOT_CHANGED
418 1
419
420 #--Logged in successfully through usr with secondary password
421 SELECT CURRENT_USER();
422 CURRENT_USER()
423 usr@localhost
424 #--Disconnect from usr ...
425
426 #--Logged in successfully through usr with primary password2
427 SELECT CURRENT_USER();
428 CURRENT_USER()
429 usr@localhost
430 #--Disconnect from usr ...
431
432 ERROR 28000: Access denied for user 'usr'@'localhost' (using password: YES)
433
434 ALTER USER usr@localhost DISCARD OLD PASSWORD;
435
436 #--Logged in successfully through usr with primary password
437 SELECT CURRENT_USER();
438 CURRENT_USER()
439 usr@localhost
440 #--Disconnect from usr ...
441
442
443 ERROR 28000: Access denied for user 'usr'@'localhost' (using password: YES)
444 ERROR 28000: Access denied for user 'usr'@'localhost' (using password: YES)
445
446 DROP USER usr@localhost;
447 #-----------------------------------------------------------------------
448 # REQUIRE CURRENT
449 #-----------------------------------------------------------------------
450
451 CREATE USER usr IDENTIFIED BY RANDOM PASSWORD;
452 user host generated password auth_factor
453 usr % ##### 1
454
455 #--Logged in successfully through usr
456 select CURRENT_USER();
457 CURRENT_USER()
458 usr@%
459
460 #--Logged in successfully through usr
461 select CURRENT_USER();
462 CURRENT_USER()
463 usr@%
464
465 #--Logged in successfully through usr
466 select CURRENT_USER();
467 CURRENT_USER()
468 usr@%
469 #--Execute set password
470
471 ALTER USER usr IDENTIFIED BY RANDOM PASSWORD REPLACE 'qrst';
472 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
473 SET PASSWORD TO RANDOM REPLACE 'qrst';
474 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
475 SET PASSWORD FOR usr TO RANDOM REPLACE 'qrst';
476 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
477 DROP USER usr;
478 #-----------------------------------------------------------------------
479 # Plugin change
480 #-----------------------------------------------------------------------
481
482 CREATE USER usr1@localhost IDENTIFIED BY RANDOM PASSWORD, usr2@localhost IDENTIFIED BY RANDOM PASSWORD, usr3@localhost IDENTIFIED BY RANDOM PASSWORD ;
483 user host generated password auth_factor
484 usr1 localhost ###### 1
485 usr2 localhost ###### 1
486 usr3 localhost ###### 1
487
488 #--Logged in successfully through usr1
489 SELECT CURRENT_USER();
490 CURRENT_USER()
491 usr1@localhost
492 "Disconnect from usr1 ..."
493
494
495 #--Logged in successfully through usr2
496 SELECT CURRENT_USER();
497 CURRENT_USER()
498 usr2@localhost
499 #--Disconnect from usr2 ...
500
501
502 #--Logged in successfully through usr3
503 SELECT CURRENT_USER();
504 CURRENT_USER()
505 usr3@localhost
506 --echo #--Disconnect from usr3 ...
507
508 Plugin change for usr1, usr2, usr3
509
510
511 #--Logged in successfully through usr1
512 SELECT CURRENT_USER();
513 CURRENT_USER()
514 usr1@localhost
515 "Disconnect from usr1 ..."
516
517
518 #--Logged in successfully through usr2
519 SELECT CURRENT_USER();
520 CURRENT_USER()
521 usr2@localhost
522 #--Disconnect from usr2 ...
523
524
525 #--Logged in successfully through usr3
526 SELECT CURRENT_USER();
527 CURRENT_USER()
528 usr3@localhost
529 --echo #--Disconnect from usr3 ...
530
531 DROP USER usr1@localhost;
532 DROP USER usr2@localhost;
533 DROP USER usr3@localhost;
534 #-----------------------------------------------------------------------
535 # General log
536 #-----------------------------------------------------------------------
537 TRUNCATE TABLE mysql.general_log;
538 SET @old_log_output = @@global.log_output;
539 @old_log_output
540 SET @old_general_log = @@global.general_log;
541 @old_general_log
542 SET @old_general_log_file = @@global.general_log_file;
543 @old_general_log_file
544 SET GLOBAL general_log_file = 'MYSQLTEST_VARDIR/log/random_password_general.log';
545 SET GLOBAL log_output = 'FILE,TABLE';
546 SET GLOBAL general_log= 'ON';
547 CREATE USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;
548 user host generated password auth_factor
549 usr localhost ###### 1
550 ALTER USER usr@localhost IDENTIFIED BY RANDOM PASSWORD;
551 user host generated password auth_factor
552 usr localhost ###### 1
553
554 #--Logged in successfully through usr
555 SELECT CURRENT_USER();
556 CURRENT_USER()
557 usr@localhost
558 SET PASSWORD TO RANDOM;
559 user host generated password auth_factor
560 usr localhost ###### 1
561 #--Disconnect from usr ...
562
563
564 Display general_log content
565 ------ rewrite ------
566 SELECT argument FROM mysql.general_log WHERE argument LIKE 'CREATE USER %' AND command_type NOT LIKE 'Prepare';
567 argument
568 CREATE USER 'usr'@'localhost' IDENTIFIED BY <secret>
569 SELECT argument FROM mysql.general_log WHERE argument LIKE 'ALTER USER %' AND command_type NOT LIKE 'Prepare';
570 argument
571 ALTER USER 'usr'@'localhost' IDENTIFIED BY <secret>
572 SELECT argument FROM mysql.general_log WHERE argument LIKE 'SET PASSWORD %';
573 argument
574 SET PASSWORD FOR `usr`@`localhost`=<secret>
575 SET PASSWORD FOR `usr`@`localhost`=<secret>
576 ------ done ------
577 DROP USER usr@localhost;
578 SET GLOBAL general_log_file = @old_general_log_file;
579 SET GLOBAL log_output = @old_log_output;
580 SET GLOBAL general_log= @old_general_log;
581 TRUNCATE TABLE mysql.general_log;
582 #-----------------------------------------------------------------------
583 # Binary log
584 #-----------------------------------------------------------------------
585 include/save_binlog_position.inc
586 CREATE USER usr1 IDENTIFIED BY RANDOM PASSWORD, usr2 IDENTIFIED BY RANDOM PASSWORD;
587 user host generated password auth_factor
588 usr1 % ##### 1
589 usr2 % ##### 1
590 # This event sequence pattern MUST be present in binlog: !Q(CREATE USER.*usr1.*usr2.*)
591 include/assert_binlog_events.inc
592 include/save_binlog_position.inc
593 ALTER USER usr1 IDENTIFIED BY RANDOM PASSWORD, usr2 IDENTIFIED BY RANDOM PASSWORD;
594 user host generated password auth_factor
595 usr1 % ##### 1
596 usr2 % ##### 1
597 # This event sequence pattern MUST be present in binlog: !Q(ALTER USER.*usr1.*usr2.*)
598 include/assert_binlog_events.inc
599 include/save_binlog_position.inc
600 SET PASSWORD FOR usr1 TO RANDOM;
601 user host generated password auth_factor
602 usr1 % ##### 1
603 # This event sequence pattern MUST be present in binlog: !Q(ALTER USER.*usr1.*)
604 include/assert_binlog_events.inc
605 include/save_binlog_position.inc
606 DROP USER usr1, usr2;
607 #-----------------------------------------------------------------------
608 # Test NEW variable generated_random_password_length
609 #-----------------------------------------------------------------------
610 #--Print default values of the variable
611 SHOW VARIABLES LIKE 'generated_random_password_length';
612 Variable_name Value
613 generated_random_password_length 66
614 SELECT @@GLOBAL.generated_random_password_length;
615 @@GLOBAL.generated_random_password_length
616 20
617 SELECT @@LOCAL.generated_random_password_length;
618 @@LOCAL.generated_random_password_length
619 66
620 SELECT @@SESSION.generated_random_password_length;
621 @@SESSION.generated_random_password_length
622 66
623 SELECT @@generated_random_password_length;
624 @@generated_random_password_length
625 66
626
627 #--set global value to 10
628 SET GLOBAL generated_random_password_length = 10;
629 SELECT @@GLOBAL.generated_random_password_length;
630 @@GLOBAL.generated_random_password_length
631 10
632
633 #--set session value to 30
634 SET SESSION generated_random_password_length = 30;
635 SELECT @@SESSION.generated_random_password_length;
636 @@SESSION.generated_random_password_length
637 30
638
639 #--set session value to 40
640 SET @@GLOBAL.generated_random_password_length = 40;
641 SELECT @@GLOBAL.generated_random_password_length;
642 @@GLOBAL.generated_random_password_length
643 40
644
645 #--set session value to 50
646 SET @@SESSION.generated_random_password_length = 50;
647 SELECT @@SESSION.generated_random_password_length;
648 @@SESSION.generated_random_password_length
649 50
650
651 #--set session value to 80
652 SET @@generated_random_password_length = 80;
653 SELECT @@SESSION.generated_random_password_length;
654 @@SESSION.generated_random_password_length
655 80
656
657 #--set session value to 90
658 SET generated_random_password_length = 90;
659 SELECT @@SESSION.generated_random_password_length;
660 @@SESSION.generated_random_password_length
661 90
662
663 #--set session value to DEFAULT
664 SET SESSION generated_random_password_length = DEFAULT;
665 SELECT @@SESSION.generated_random_password_length;
666 @@SESSION.generated_random_password_length
667 40
668
669 #--set global value to DEFAULT
670 SET GLOBAL generated_random_password_length = DEFAULT;
671 SELECT @@GLOBAL.generated_random_password_length;
672 @@GLOBAL.generated_random_password_length
673 20
674
675 #--Print min values of the variable
676 #--set global value to 5
677 SET @@GLOBAL.generated_random_password_length = 5;
678 SELECT @@GLOBAL.generated_random_password_length;
679 @@GLOBAL.generated_random_password_length
680 5
681
682 #--set global value to 4
683 SET @@GLOBAL.generated_random_password_length = 4;
684 Warnings:
685 Warning 1292 Truncated incorrect generated_random_password_length value: '4'
686 SELECT @@GLOBAL.generated_random_password_length;
687 @@GLOBAL.generated_random_password_length
688 5
689
690 #--set session value to 5
691 SET @@SESSION.generated_random_password_length = 5;
692 SELECT @@SESSION.generated_random_password_length;
693 @@SESSION.generated_random_password_length
694 5
695
696 #--set session value to 4
697 SET @@SESSION.generated_random_password_length = 4;
698 Warnings:
699 Warning 1292 Truncated incorrect generated_random_password_length value: '4'
700 SELECT @@SESSION.generated_random_password_length;
701 @@SESSION.generated_random_password_length
702 5
703
704 #--Test invalid values
705 SET @@GLOBAL.generated_random_password_length =;
706 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
707 SET @@GLOBAL.generated_random_password_length = b;
708 ERROR 42000: Incorrect argument type to variable 'generated_random_password_length'
709 SET GLOBAL generated_random_password_length = b;
710 ERROR 42000: Incorrect argument type to variable 'generated_random_password_length'
711 SET @@LOCAL.generated_random_password_length = '4';
712 ERROR 42000: Incorrect argument type to variable 'generated_random_password_length'
713 SET LOCAL generated_random_password_length = '10';
714 ERROR 42000: Incorrect argument type to variable 'generated_random_password_length'
715 SET @@GLOBAL.generated_random_password_length = 800000000000000000000000000000000000;
716 ERROR 42000: Incorrect argument type to variable 'generated_random_password_length'
717 SET SESSION generated_random_password_length = 800000000000000000000000000000000000;
718 ERROR 42000: Incorrect argument type to variable 'generated_random_password_length'
719 SET @@GLOBAL.generated_random_password_length = 3.5;
720 ERROR 42000: Incorrect argument type to variable 'generated_random_password_length'
721 SET @@GLOBAL.generated_random_password_length = -4;
722 Warnings:
723 Warning 1292 Truncated incorrect generated_random_password_length value: '-4'
724 SELECT @@GLOBAL.generated_random_password_length;
725 @@GLOBAL.generated_random_password_length
726 5
727 SET LOCAL generated_random_password_length = -10;
728 Warnings:
729 Warning 1292 Truncated incorrect generated_random_password_length value: '-10'
730 SELECT @@LOCAL.generated_random_password_length;
731 @@LOCAL.generated_random_password_length
732 5
733
734 #--Print Max values of the variable
735 #--set global value to 255
736 SET @@GLOBAL.generated_random_password_length = 255;
737 SELECT @@GLOBAL.generated_random_password_length;
738 @@GLOBAL.generated_random_password_length
739 255
740
741 #--set global value to 256
742 SET @@GLOBAL.generated_random_password_length = 256;
743 Warnings:
744 Warning 1292 Truncated incorrect generated_random_password_length value: '256'
745 SELECT @@GLOBAL.generated_random_password_length;
746 @@GLOBAL.generated_random_password_length
747 255
748
749 #--set session value to 255
750 SET @@SESSION.generated_random_password_length = 255;
751 SELECT @@SESSION.generated_random_password_length;
752 @@SESSION.generated_random_password_length
753 255
754
755 #--set session value to 256
756 SET @@SESSION.generated_random_password_length = 256;
757 Warnings:
758 Warning 1292 Truncated incorrect generated_random_password_length value: '256'
759 SELECT @@SESSION.generated_random_password_length;
760 @@SESSION.generated_random_password_length
761 255
762
763 #--set global and session to DEFAULT
764 SET GLOBAL generated_random_password_length = DEFAULT;
765 SET SESSION generated_random_password_length = DEFAULT;
766 #--Test GLOBAL setting
767 #-- 1) Test with a lower value
768 #--set global to value 5
769 SET @@GLOBAL.generated_random_password_length = 5;
770 SELECT @@GLOBAL.generated_random_password_length;
771 @@GLOBAL.generated_random_password_length
772 5
773
774 #--create user usr
775
776 #--login to user usr
777 Logged in successfully through usr
778 SELECT CURRENT_USER();
779 CURRENT_USER()
780 usr@%
781 SELECT @@GLOBAL.generated_random_password_length;
782 @@GLOBAL.generated_random_password_length
783 5
784 SELECT @@SESSION.generated_random_password_length;
785 @@SESSION.generated_random_password_length
786 5
787 "Disconnect from usr ..."
788
789 #--alter user usr
790
791 #--login to user usr
792 Logged in successfully through usr
793 SELECT CURRENT_USER();
794 CURRENT_USER()
795 usr@%
796 SELECT @@GLOBAL.generated_random_password_length;
797 @@GLOBAL.generated_random_password_length
798 5
799 SELECT @@SESSION.generated_random_password_length;
800 @@SESSION.generated_random_password_length
801 5
802 "Disconnect from usr ..."
803
804 #--set password for usr
805
806 #--login to user usr
807 Logged in successfully through usr
808 SELECT CURRENT_USER();
809 CURRENT_USER()
810 usr@%
811 SELECT @@GLOBAL.generated_random_password_length;
812 @@GLOBAL.generated_random_password_length
813 5
814 SELECT @@SESSION.generated_random_password_length;
815 @@SESSION.generated_random_password_length
816 5
817 "Disconnect from usr ..."
818 DROP USER usr;
819 SET @@GLOBAL.generated_random_password_length = DEFAULT;
820
821 #-- 2) Test with the max value
822 #--set global to value 255
823 SET @@GLOBAL.generated_random_password_length = 255;
824 SELECT @@GLOBAL.generated_random_password_length;
825 @@GLOBAL.generated_random_password_length
826 255
827
828 #-- create user usr
829
830 #--login to user usr
831 Logged in successfully through usr
832 SELECT CURRENT_USER();
833 CURRENT_USER()
834 usr@%
835 SELECT @@GLOBAL.generated_random_password_length;
836 @@GLOBAL.generated_random_password_length
837 255
838 SELECT @@SESSION.generated_random_password_length;
839 @@SESSION.generated_random_password_length
840 255
841 "Disconnect from usr ..."
842
843 #--alter user usr
844
845 #--login to user usr
846 Logged in successfully through usr
847 SELECT CURRENT_USER();
848 CURRENT_USER()
849 usr@%
850 SELECT @@GLOBAL.generated_random_password_length;
851 @@GLOBAL.generated_random_password_length
852 255
853 SELECT @@SESSION.generated_random_password_length;
854 @@SESSION.generated_random_password_length
855 255
856 "Disconnect from usr ..."
857
858 #--set password user usr
859
860 #--login to user usr
861 Logged in successfully through usr
862 SELECT CURRENT_USER();
863 CURRENT_USER()
864 usr@%
865 SELECT @@GLOBAL.generated_random_password_length;
866 @@GLOBAL.generated_random_password_length
867 255
868 SELECT @@SESSION.generated_random_password_length;
869 @@SESSION.generated_random_password_length
870 255
871 "Disconnect from usr ..."
872 DROP USER usr;
873 SET @@GLOBAL.generated_random_password_length = DEFAULT;
874
875 #-- 3) Test with lower than min value
876 #--set session to value 0
877 SET @@GLOBAL.generated_random_password_length = 0;
878 Warnings:
879 Warning 1292 Truncated incorrect generated_random_password_length value: '0'
880 SELECT @@GLOBAL.generated_random_password_length;
881 @@GLOBAL.generated_random_password_length
882 5
883
884 #--create user usr
885
886 #--login to user usr
887 Logged in successfully through usr
888 SELECT CURRENT_USER();
889 CURRENT_USER()
890 usr@%
891 SELECT @@GLOBAL.generated_random_password_length;
892 @@GLOBAL.generated_random_password_length
893 5
894 SELECT @@SESSION.generated_random_password_length;
895 @@SESSION.generated_random_password_length
896 5
897 "Disconnect from usr ..."
898
899 #--create user usr
900
901 #--login to usr
902 Logged in successfully through usr
903 SELECT CURRENT_USER();
904 CURRENT_USER()
905 usr@%
906 SELECT @@GLOBAL.generated_random_password_length;
907 @@GLOBAL.generated_random_password_length
908 5
909 SELECT @@SESSION.generated_random_password_length;
910 @@SESSION.generated_random_password_length
911 5
912 "Disconnect from usr ..."
913
914 #--set password for usr
915
916 #--login to usr
917 Logged in successfully through usr
918 SELECT CURRENT_USER();
919 CURRENT_USER()
920 usr@%
921 SELECT @@GLOBAL.generated_random_password_length;
922 @@GLOBAL.generated_random_password_length
923 5
924 SELECT @@SESSION.generated_random_password_length;
925 @@SESSION.generated_random_password_length
926 5
927 "Disconnect from usr ..."
928 DROP USER usr;
929 SET @@GLOBAL.generated_random_password_length = DEFAULT;
930
931 #-- 3) Test with a value which has crossed its max value
932 #--set global to value 256
933 SET @@GLOBAL.generated_random_password_length = 256;
934 Warnings:
935 Warning 1292 Truncated incorrect generated_random_password_length value: '256'
936 SELECT @@GLOBAL.generated_random_password_length;
937 @@GLOBAL.generated_random_password_length
938 255
939
940 #--create user usr
941
942 #--connect to usr
943 Logged in successfully through usr
944 SELECT CURRENT_USER();
945 CURRENT_USER()
946 usr@%
947 SELECT @@GLOBAL.generated_random_password_length;
948 @@GLOBAL.generated_random_password_length
949 255
950 SELECT @@SESSION.generated_random_password_length;
951 @@SESSION.generated_random_password_length
952 255
953 "Disconnect from usr ..."
954
955 #--alter user usr
956
957 #--connect to usr
958 Logged in successfully through usr
959 SELECT CURRENT_USER();
960 CURRENT_USER()
961 usr@%
962 SELECT @@GLOBAL.generated_random_password_length;
963 @@GLOBAL.generated_random_password_length
964 255
965 SELECT @@SESSION.generated_random_password_length;
966 @@SESSION.generated_random_password_length
967 255
968 "Disconnect from usr ..."
969
970 #--set password
971
972 #--connect to usr
973 Logged in successfully through usr
974 SELECT CURRENT_USER();
975 CURRENT_USER()
976 usr@%
977 SELECT @@GLOBAL.generated_random_password_length;
978 @@GLOBAL.generated_random_password_length
979 255
980 SELECT @@SESSION.generated_random_password_length;
981 @@SESSION.generated_random_password_length
982 255
983 "Disconnect from usr ..."
984 DROP USER usr;
985 #--Test SESSION setting
986 #-- 1) Test with a lower value
987 #--set session to value 5
988 SET @@SESSION.generated_random_password_length = 5;
989 SELECT @@SESSION.generated_random_password_length;
990 @@SESSION.generated_random_password_length
991 5
992
993 #--create user usr
994
995 #--connect to usr
996 Logged in successfully through usr
997 SELECT CURRENT_USER();
998 CURRENT_USER()
999 usr@%
1000 SELECT @@GLOBAL.generated_random_password_length;
1001 @@GLOBAL.generated_random_password_length
1002 255
1003 SELECT @@SESSION.generated_random_password_length;
1004 @@SESSION.generated_random_password_length
1005 255
1006 "Disconnect from usr ..."
1007
1008 #--alter user usr
1009
1010 #--connect to usr
1011 Logged in successfully through usr
1012 SELECT CURRENT_USER();
1013 CURRENT_USER()
1014 usr@%
1015 SELECT @@GLOBAL.generated_random_password_length;
1016 @@GLOBAL.generated_random_password_length
1017 255
1018 SELECT @@SESSION.generated_random_password_length;
1019 @@SESSION.generated_random_password_length
1020 255
1021 "Disconnect from usr ..."
1022
1023 #--set password
1024
1025 #--connect to usr
1026 Logged in successfully through usr
1027 SELECT CURRENT_USER();
1028 CURRENT_USER()
1029 usr@%
1030 SELECT @@GLOBAL.generated_random_password_length;
1031 @@GLOBAL.generated_random_password_length
1032 255
1033 SELECT @@SESSION.generated_random_password_length;
1034 @@SESSION.generated_random_password_length
1035 255
1036 "Disconnect from usr ..."
1037 DROP USER usr;
1038 #-- 2) Test with the max value
1039 #--set session to value 255
1040 SET @@SESSION.generated_random_password_length = 255;
1041 SELECT @@SESSION.generated_random_password_length;
1042 @@SESSION.generated_random_password_length
1043 255
1044
1045 #--create user usr
1046
1047 #--connect to usr
1048 Logged in successfully through usr
1049 SELECT CURRENT_USER();
1050 CURRENT_USER()
1051 usr@%
1052 SELECT @@GLOBAL.generated_random_password_length;
1053 @@GLOBAL.generated_random_password_length
1054 255
1055 SELECT @@SESSION.generated_random_password_length;
1056 @@SESSION.generated_random_password_length
1057 255
1058 "Disconnect from usr ..."
1059
1060 #--alter user usr
1061
1062 #--connect to usr
1063 Logged in successfully through usr
1064 SELECT CURRENT_USER();
1065 CURRENT_USER()
1066 usr@%
1067 SELECT @@GLOBAL.generated_random_password_length;
1068 @@GLOBAL.generated_random_password_length
1069 255
1070 SELECT @@SESSION.generated_random_password_length;
1071 @@SESSION.generated_random_password_length
1072 255
1073 "Disconnect from usr ..."
1074
1075 #--set password
1076
1077 #--connect to usr
1078 Logged in successfully through usr
1079 SELECT CURRENT_USER();
1080 CURRENT_USER()
1081 usr@%
1082 SELECT @@GLOBAL.generated_random_password_length;
1083 @@GLOBAL.generated_random_password_length
1084 255
1085 SELECT @@SESSION.generated_random_password_length;
1086 @@SESSION.generated_random_password_length
1087 255
1088 "Disconnect from usr ..."
1089 DROP USER usr;
1090 #-- 3) Test will value less than min value
1091 #--set session to value 0
1092 SET @@SESSION.generated_random_password_length = 0;
1093 Warnings:
1094 Warning 1292 Truncated incorrect generated_random_password_length value: '0'
1095 SELECT @@SESSION.generated_random_password_length;
1096 @@SESSION.generated_random_password_length
1097 5
1098
1099 #--create user usr
1100
1101 #--connect to usr
1102 Logged in successfully through usr
1103 SELECT CURRENT_USER();
1104 CURRENT_USER()
1105 usr@%
1106 SELECT @@GLOBAL.generated_random_password_length;
1107 @@GLOBAL.generated_random_password_length
1108 255
1109 SELECT @@SESSION.generated_random_password_length;
1110 @@SESSION.generated_random_password_length
1111 255
1112 "Disconnect from usr ..."
1113
1114 #--alter user usr
1115
1116 #--connect to usr
1117 Logged in successfully through usr
1118 SELECT CURRENT_USER();
1119 CURRENT_USER()
1120 usr@%
1121 SELECT @@GLOBAL.generated_random_password_length;
1122 @@GLOBAL.generated_random_password_length
1123 255
1124 SELECT @@SESSION.generated_random_password_length;
1125 @@SESSION.generated_random_password_length
1126 255
1127 "Disconnect from usr ..."
1128
1129 #--set password
1130
1131 #--connect to usr
1132 Logged in successfully through usr
1133 SELECT CURRENT_USER();
1134 CURRENT_USER()
1135 usr@%
1136 SELECT @@GLOBAL.generated_random_password_length;
1137 @@GLOBAL.generated_random_password_length
1138 255
1139 SELECT @@SESSION.generated_random_password_length;
1140 @@SESSION.generated_random_password_length
1141 255
1142 "Disconnect from usr ..."
1143 DROP USER usr;
1144
1145 #--Test variable value setting with/without user privileges
1146
1147 #--Non-privileged user unable to set global value
1148 SET @@GLOBAL.generated_random_password_length = 48;
1149 ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
1150 SELECT @@GLOBAL.generated_random_password_length;
1151 @@GLOBAL.generated_random_password_length
1152 255
1153
1154 #--Non-privileged user able to set session value
1155 SET @@SESSION.generated_random_password_length = 48;
1156 SELECT @@SESSION.generated_random_password_length;
1157 @@SESSION.generated_random_password_length
1158 48
1159
1160 GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO usr;
1161 FLUSH PRIVILEGES;
1162
1163 #--Privileged user able to set global value
1164 SET @@GLOBAL.generated_random_password_length = 48;
1165 SELECT @@GLOBAL.generated_random_password_length;
1166 @@GLOBAL.generated_random_password_length
1167 48
1168
1169 #--Privileged user able to set session value
1170 SET @@SESSION.generated_random_password_length = 48;
1171 SELECT @@SESSION.generated_random_password_length;
1172 @@SESSION.generated_random_password_length
1173 48
1174 REVOKE SYSTEM_VARIABLES_ADMIN ON *.* FROM usr;
1175 FLUSH PRIVILEGES;
1176 drop user usr;
1177 SET GLOBAL generated_random_password_length = DEFAULT;