Debian Codegraph @

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

raw
1 # Default server variable value must be OFF
2 SELECT @@global.password_require_current;
3 @@global.password_require_current
4 0
5
6 ########################################################################
7 # 1. Create user without any PASSWPRD REQUIRE CURRENT clause which
8 # must be equivalent to DEFAULT clause
9 ########################################################################
10 CREATE USER non_priv_usr IDENTIFIED BY 'pwd1',
11 priv_usr IDENTIFIED BY 'pwd2';
12
13 # Show how the users were created
14
15 SHOW CREATE USER non_priv_usr;
16 CREATE USER for non_priv_usr@%
17 CREATE USER `non_priv_usr`@`%` IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
18 SHOW CREATE USER priv_usr;
19 CREATE USER for priv_usr@%
20 CREATE USER `priv_usr`@`%` IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
21 SELECT password_require_current from mysql.user
22 where user like '%priv_usr' OR user like '%non_priv_usr';
23 password_require_current
24 NULL
25 NULL
26
27 # Grant the update privileges to the user
28 GRANT UPDATE ON mysql.* to 'priv_usr'@'%' WITH GRANT OPTION;
29
30 #------------------------------------------------------------------------
31 # Priv user changing his own password
32 #------------------------------------------------------------------------
33
34 # Without REPLACE clause
35 SET PASSWORD='pwd1';
36 SET PASSWORD for priv_usr='pwd2';
37 ALTER USER user() IDENTIFIED BY 'pwd1';
38 ALTER USER priv_usr IDENTIFIED BY 'pwd2';
39
40 # With invalid current password in the REPLACE clause
41 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D';
42 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
43 SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D';
44 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
45 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
46 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
47 ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
48 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
49
50 # With valid current password in the REPLACE clause
51 SET PASSWORD='pwd1' REPLACE 'pwd2';
52 SET PASSWORD for priv_usr='pwd2' REPLACE 'pwd1';
53 ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
54 ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
55
56 #------------------------------------------------------------------------
57 # Priv user changing the password of non_priv_usr
58 #------------------------------------------------------------------------
59
60 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'pwd1';
61 ERROR HY000: Do not specify the current password while changing it for other users.
62 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE '';
63 ERROR HY000: Do not specify the current password while changing it for other users.
64
65 SET PASSWORD for non_priv_usr='pwd2';
66 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2';
67
68 #------------------------------------------------------------------------
69 # One priv user(root) changing password for other priv user (priv_usr)
70 #------------------------------------------------------------------------
71
72 SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2';
73 ERROR HY000: Do not specify the current password while changing it for other users.
74 ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
75 ERROR HY000: Do not specify the current password while changing it for other users.
76
77 ALTER USER priv_usr IDENTIFIED BY 'pwd2';
78 SET PASSWORD for priv_usr='pwd1';
79
80 # Now test with Global server variable ON
81 SET @@global.password_require_current=ON;
82
83 #------------------------------------------------------------------------
84 # Priv user changing his own password
85 #------------------------------------------------------------------------
86
87 # Can change without REPLACE clause
88 SET PASSWORD='pwd2';
89 SET PASSWORD for priv_usr='pwd1';
90 ALTER USER user() IDENTIFIED BY 'pwd2';
91 ALTER USER priv_usr IDENTIFIED BY 'pwd1';
92
93 # Incorrect password specified in the REPLACE clause
94 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D';
95 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
96 SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D';
97 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
98 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
99 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
100 ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
101 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
102
103 # Correct password specified in the REPLACE clause
104 SET PASSWORD='pwd2' REPLACE 'pwd1';
105 SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2';
106 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
107 ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'
108 WITH MAX_QUERIES_PER_HOUR 100 PASSWORD EXPIRE DEFAULT;
109
110 #------------------------------------------------------------------------
111 # Priv user changing the password of non_priv_usr
112 #------------------------------------------------------------------------
113
114 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D';
115 ERROR HY000: Do not specify the current password while changing it for other users.
116 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
117 ERROR HY000: Do not specify the current password while changing it for other users.
118
119 SET PASSWORD for non_priv_usr='pwd2' ;
120 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1';
121
122 #------------------------------------------------------------------------
123 # One priv user(root) changing password for other priv user (priv_usr)
124 #------------------------------------------------------------------------
125
126 SET PASSWORD for priv_usr='pwd1' REPLACE '';
127 ERROR HY000: Do not specify the current password while changing it for other users.
128 ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
129 ERROR HY000: Do not specify the current password while changing it for other users.
130 SET PASSWORD for priv_usr='pwd1';
131 ALTER USER priv_usr IDENTIFIED BY 'pwd2';
132
133 # Reset the privileges and global variable
134
135 REVOKE UPDATE ON mysql.* FROM 'priv_usr'@'%';
136 SET @@global.password_require_current=OFF;
137
138 #------------------------------------------------------------------------
139 # Non priv user changing his own password
140 #------------------------------------------------------------------------
141
142 # REPLACE clause is not specified
143 SET PASSWORD='pwd2';
144 SET PASSWORD for non_priv_usr='pwd2';
145 ALTER USER user() IDENTIFIED BY 'pwd1';
146 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1';
147
148 # Incorrect password specified in the REPLACE clause
149 SET PASSWORD='pwd2' REPLACE '' ;
150 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
151 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D';
152 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
153 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
154 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
155 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
156 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
157
158 # Correct password specified in the REPLACE clause
159 SET PASSWORD='pwd2' REPLACE 'pwd1';
160 SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2';
161 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
162 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
163
164 # Now test with Global server variable ON
165 SET @@global.password_require_current=ON;
166
167 #------------------------------------------------------------------------
168 # Non priv user changing his own password
169 #------------------------------------------------------------------------
170
171 # REPLACE clause is not specified
172 SET PASSWORD='pwd2';
173 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
174 SET PASSWORD for non_priv_usr='pwd2';
175 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
176 ALTER USER user() IDENTIFIED BY 'pwd1';
177 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
178 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1';
179 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
180
181 # Incorrect password specified in the REPLACE clause
182 SET PASSWORD='pwd2' REPLACE '' ;
183 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
184 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D';
185 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
186 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
187 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
188 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
189 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
190
191 # Correct password specified in the REPLACE clause
192 SET PASSWORD='pwd2' REPLACE 'pwd1';
193 SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2';
194 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
195 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
196
197 # Reset the Global server variable to OFF
198 SET @@global.password_require_current=OFF;
199
200 DROP USER non_priv_usr, priv_usr;
201
202 ########################################################################
203 # 2. Create user with PASSWPRD REQUIRE CURRENT DEFAULT clause
204 ########################################################################
205 CREATE USER non_priv_usr IDENTIFIED BY 'pwd1',
206 priv_usr IDENTIFIED BY 'pwd2'
207 PASSWORD REQUIRE CURRENT DEFAULT;
208
209 # Show how the users were created
210
211 SHOW CREATE USER non_priv_usr;
212 CREATE USER for non_priv_usr@%
213 CREATE USER `non_priv_usr`@`%` IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
214 SHOW CREATE USER priv_usr;
215 CREATE USER for priv_usr@%
216 CREATE USER `priv_usr`@`%` IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
217 SELECT password_require_current from mysql.user
218 where user like '%priv_usr' OR user like '%non_priv_usr';
219 password_require_current
220 NULL
221 NULL
222
223 # Grant the update privileges to the user
224 GRANT UPDATE ON mysql.* to 'priv_usr'@'%' WITH GRANT OPTION;
225
226 #------------------------------------------------------------------------
227 # Priv user changing his own password
228 #------------------------------------------------------------------------
229
230 # Without REPLACE clause
231 SET PASSWORD='pwd1';
232 SET PASSWORD for priv_usr='pwd2';
233 ALTER USER user() IDENTIFIED BY 'pwd1';
234 ALTER USER priv_usr IDENTIFIED BY 'pwd2';
235
236 # With invalid current password in the REPLACE clause
237 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D';
238 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
239 SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D';
240 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
241 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
242 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
243 ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
244 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
245
246 # With valid current password in the REPLACE clause
247 SET PASSWORD='pwd1' REPLACE 'pwd2';
248 SET PASSWORD for priv_usr='pwd2' REPLACE 'pwd1';
249 ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
250 ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
251
252 #------------------------------------------------------------------------
253 # Priv user changing the password of non_priv_usr
254 #------------------------------------------------------------------------
255
256 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'pwd1';
257 ERROR HY000: Do not specify the current password while changing it for other users.
258 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE '';
259 ERROR HY000: Do not specify the current password while changing it for other users.
260
261 SET PASSWORD for non_priv_usr='pwd2';
262 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2';
263
264 #------------------------------------------------------------------------
265 # One priv user(root) changing password for other priv user (priv_usr)
266 #------------------------------------------------------------------------
267
268 SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2';
269 ERROR HY000: Do not specify the current password while changing it for other users.
270 ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
271 ERROR HY000: Do not specify the current password while changing it for other users.
272
273 ALTER USER priv_usr IDENTIFIED BY 'pwd2';
274 SET PASSWORD for priv_usr='pwd1';
275
276 # Now test with Global server variable ON
277 SET @@global.password_require_current=ON;
278
279 #------------------------------------------------------------------------
280 # Priv user changing his own password
281 #------------------------------------------------------------------------
282
283 # Can change without REPLACE clause
284 SET PASSWORD='pwd2';
285 SET PASSWORD for priv_usr='pwd1';
286 ALTER USER user() IDENTIFIED BY 'pwd2';
287 ALTER USER priv_usr IDENTIFIED BY 'pwd1';
288
289 # Incorrect password specified in the REPLACE clause
290 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D';
291 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
292 SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D';
293 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
294 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
295 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
296 ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
297 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
298
299 # Correct password specified in the REPLACE clause
300 SET PASSWORD='pwd2' REPLACE 'pwd1';
301 SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2';
302 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
303 ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'
304 WITH MAX_QUERIES_PER_HOUR 100 PASSWORD EXPIRE DEFAULT;
305
306 #------------------------------------------------------------------------
307 # Priv user changing the password of non_priv_usr
308 #------------------------------------------------------------------------
309
310 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D';
311 ERROR HY000: Do not specify the current password while changing it for other users.
312 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
313 ERROR HY000: Do not specify the current password while changing it for other users.
314
315 SET PASSWORD for non_priv_usr='pwd2' ;
316 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1';
317
318 #------------------------------------------------------------------------
319 # One priv user(root) changing password for other priv user (priv_usr)
320 #------------------------------------------------------------------------
321
322 SET PASSWORD for priv_usr='pwd1' REPLACE '';
323 ERROR HY000: Do not specify the current password while changing it for other users.
324 ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
325 ERROR HY000: Do not specify the current password while changing it for other users.
326 SET PASSWORD for priv_usr='pwd1';
327 ALTER USER priv_usr IDENTIFIED BY 'pwd2';
328
329 # Reset the privileges and global variable
330
331 REVOKE UPDATE ON mysql.* FROM 'priv_usr'@'%';
332 SET @@global.password_require_current=OFF;
333
334 #------------------------------------------------------------------------
335 # Non priv user changing his own password
336 #------------------------------------------------------------------------
337
338 # REPLACE clause is not specified
339 SET PASSWORD='pwd2';
340 SET PASSWORD for non_priv_usr='pwd2';
341 ALTER USER user() IDENTIFIED BY 'pwd1';
342 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1';
343
344 # Incorrect password specified in the REPLACE clause
345 SET PASSWORD='pwd2' REPLACE '' ;
346 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
347 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D';
348 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
349 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
350 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
351 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
352 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
353
354 # Correct password specified in the REPLACE clause
355 SET PASSWORD='pwd2' REPLACE 'pwd1';
356 SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2';
357 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
358 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
359
360 # Now test with Global server variable ON
361 SET @@global.password_require_current=ON;
362
363 #------------------------------------------------------------------------
364 # Non priv user changing his own password
365 #------------------------------------------------------------------------
366
367 # REPLACE clause is not specified
368 SET PASSWORD='pwd2';
369 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
370 SET PASSWORD for non_priv_usr='pwd2';
371 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
372 ALTER USER user() IDENTIFIED BY 'pwd1';
373 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
374 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1';
375 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
376
377 # Incorrect password specified in the REPLACE clause
378 SET PASSWORD='pwd2' REPLACE '' ;
379 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
380 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D';
381 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
382 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
383 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
384 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
385 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
386
387 # Correct password specified in the REPLACE clause
388 SET PASSWORD='pwd2' REPLACE 'pwd1';
389 SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2';
390 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
391 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
392
393 # Reset the Global server variable to OFF
394 SET @@global.password_require_current=OFF;
395
396 DROP USER non_priv_usr, priv_usr;
397
398 ########################################################################
399 # 3. Create user with CURRENT clause
400 ########################################################################
401 CREATE USER IF NOT EXISTS non_priv_usr IDENTIFIED BY 'pwd1',
402 priv_usr IDENTIFIED BY 'pwd2' PASSWORD REQUIRE CURRENT;
403
404 # Show how the users were created
405
406 SHOW CREATE USER non_priv_usr;
407 CREATE USER for non_priv_usr@%
408 CREATE USER `non_priv_usr`@`%` IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT
409 SHOW CREATE USER priv_usr;
410 CREATE USER for priv_usr@%
411 CREATE USER `priv_usr`@`%` IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT
412 SELECT password_require_current from mysql.user
413 where user like '%priv_usr' OR user like '%non_priv_usr';
414 password_require_current
415 Y
416 Y
417
418 # Grant the update privileges to the user
419 GRANT UPDATE ON mysql.* to 'priv_usr'@'%' WITH GRANT OPTION;
420
421 #------------------------------------------------------------------------
422 # Priv user changing his own password
423 #------------------------------------------------------------------------
424
425 # Without REPLACE clause
426 SET PASSWORD='pwd1';
427 SET PASSWORD for priv_usr='pwd2';
428 ALTER USER user() IDENTIFIED BY 'pwd1';
429 ALTER USER priv_usr IDENTIFIED BY 'pwd2';
430
431 # With invalid current password in the REPLACE clause
432 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D';
433 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
434 SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D';
435 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
436 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
437 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
438 ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
439 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
440
441 # With valid current password in the REPLACE clause
442 SET PASSWORD='pwd1' REPLACE 'pwd2';
443 SET PASSWORD for priv_usr='pwd2' REPLACE 'pwd1';
444 ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
445 ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
446
447 #------------------------------------------------------------------------
448 # Priv user changing the password of non_priv_usr
449 #------------------------------------------------------------------------
450
451 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'pwd1';
452 ERROR HY000: Do not specify the current password while changing it for other users.
453 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE '';
454 ERROR HY000: Do not specify the current password while changing it for other users.
455
456 SET PASSWORD for non_priv_usr='pwd2';
457 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2';
458
459 #------------------------------------------------------------------------
460 # One priv user(root) changing password for other priv user (priv_usr)
461 #------------------------------------------------------------------------
462
463 SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2';
464 ERROR HY000: Do not specify the current password while changing it for other users.
465 ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
466 ERROR HY000: Do not specify the current password while changing it for other users.
467
468 ALTER USER priv_usr IDENTIFIED BY 'pwd2';
469 SET PASSWORD for priv_usr='pwd1';
470
471 # Now test with Global server variable ON
472 SET @@global.password_require_current=ON;
473
474 #------------------------------------------------------------------------
475 # Priv user changing his own password
476 #------------------------------------------------------------------------
477
478 # Can change without REPLACE clause
479 SET PASSWORD='pwd2';
480 SET PASSWORD for priv_usr='pwd1';
481 ALTER USER user() IDENTIFIED BY 'pwd2';
482 ALTER USER priv_usr IDENTIFIED BY 'pwd1';
483
484 # Incorrect password specified in the REPLACE clause
485 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D';
486 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
487 SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D';
488 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
489 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
490 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
491 ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
492 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
493
494 # Correct password specified in the REPLACE clause
495 SET PASSWORD='pwd2' REPLACE 'pwd1';
496 SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2';
497 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
498 ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'
499 WITH MAX_QUERIES_PER_HOUR 100 PASSWORD EXPIRE DEFAULT;
500
501 #------------------------------------------------------------------------
502 # Priv user changing the password of non_priv_usr
503 #------------------------------------------------------------------------
504
505 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D';
506 ERROR HY000: Do not specify the current password while changing it for other users.
507 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
508 ERROR HY000: Do not specify the current password while changing it for other users.
509
510 SET PASSWORD for non_priv_usr='pwd2' ;
511 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1';
512
513 #------------------------------------------------------------------------
514 # One priv user(root) changing password for other priv user (priv_usr)
515 #------------------------------------------------------------------------
516
517 SET PASSWORD for priv_usr='pwd1' REPLACE '';
518 ERROR HY000: Do not specify the current password while changing it for other users.
519 ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
520 ERROR HY000: Do not specify the current password while changing it for other users.
521 SET PASSWORD for priv_usr='pwd1';
522 ALTER USER priv_usr IDENTIFIED BY 'pwd2';
523
524 # Reset the privileges and global variable
525
526 REVOKE UPDATE ON mysql.* FROM 'priv_usr'@'%';
527 SET @@global.password_require_current=OFF;
528
529 #------------------------------------------------------------------------
530 # Non priv user changing his own password
531 #------------------------------------------------------------------------
532
533 # REPLACE clause is not specified
534 SET PASSWORD='pwd2';
535 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
536 SET PASSWORD for non_priv_usr='pwd2';
537 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
538 ALTER USER user() IDENTIFIED BY 'pwd1';
539 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
540 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1';
541 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
542
543 # Incorrect password specified in the REPLACE clause
544 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ;
545 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
546 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D';
547 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
548 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
549 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
550 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
551 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
552
553 # Correct password specified in the REPLACE clause
554 SET PASSWORD='pwd2' REPLACE 'pwd1';
555 SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2';
556 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
557 ALTER USER IF EXISTS non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
558
559 # Now test with Global server variable ON
560 SET @@global.password_require_current=ON;
561
562 #------------------------------------------------------------------------
563 # Non priv user changing his own password
564 #------------------------------------------------------------------------
565
566 # REPLACE clause is not specified
567 SET PASSWORD='pwd2';
568 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
569 SET PASSWORD for non_priv_usr='pwd2';
570 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
571 ALTER USER user() IDENTIFIED BY 'pwd2';
572 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
573 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2';
574 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
575
576 # Incorrect password specified in the REPLACE clause
577 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ;
578 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
579 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D';
580 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
581 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
582 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
583 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
584 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
585
586 # Correct password specified in the REPLACE clause
587 SET PASSWORD='pwd2' REPLACE 'pwd1';
588 SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2';
589 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
590 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
591
592 # Reset the Global server variable to OFF
593 SET @@global.password_require_current=OFF;
594
595 DROP USER non_priv_usr, priv_usr;
596
597 ########################################################################
598 # 4. Create user with OPTIONAL clause
599 ########################################################################
600 CREATE USER non_priv_usr IDENTIFIED BY 'pwd1',
601 priv_usr IDENTIFIED BY 'pwd2'
602 PASSWORD REQUIRE CURRENT OPTIONAL;
603
604 # Show how the users were created
605
606 SHOW CREATE USER non_priv_usr;
607 CREATE USER for non_priv_usr@%
608 CREATE USER `non_priv_usr`@`%` IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT OPTIONAL
609 SHOW CREATE USER priv_usr;
610 CREATE USER for priv_usr@%
611 CREATE USER `priv_usr`@`%` IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT OPTIONAL
612 SELECT password_require_current from mysql.user
613 where user like '%priv_usr' OR user like '%non_priv_usr';
614 password_require_current
615 N
616 N
617
618 # Grant the update privileges to the user
619 GRANT UPDATE ON mysql.* to 'priv_usr'@'%' WITH GRANT OPTION;
620
621 #------------------------------------------------------------------------
622 # Priv user changing his own password
623 #------------------------------------------------------------------------
624
625 # Without REPLACE clause
626 SET PASSWORD='pwd1';
627 SET PASSWORD for priv_usr='pwd2';
628 ALTER USER user() IDENTIFIED BY 'pwd1';
629 ALTER USER priv_usr IDENTIFIED BY 'pwd2';
630
631 # With invalid current password in the REPLACE clause
632 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D';
633 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
634 SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D';
635 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
636 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
637 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
638 ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
639 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
640
641 # With valid current password in the REPLACE clause
642 SET PASSWORD='pwd1' REPLACE 'pwd2';
643 SET PASSWORD for priv_usr='pwd2' REPLACE 'pwd1';
644 ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
645 ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
646
647 #------------------------------------------------------------------------
648 # Priv user changing the password of non_priv_usr
649 #------------------------------------------------------------------------
650
651 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'pwd1';
652 ERROR HY000: Do not specify the current password while changing it for other users.
653 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE '';
654 ERROR HY000: Do not specify the current password while changing it for other users.
655
656 SET PASSWORD for non_priv_usr='pwd2';
657 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2';
658
659 #------------------------------------------------------------------------
660 # One priv user(root) changing password for other priv user (priv_usr)
661 #------------------------------------------------------------------------
662
663 SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2';
664 ERROR HY000: Do not specify the current password while changing it for other users.
665 ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
666 ERROR HY000: Do not specify the current password while changing it for other users.
667
668 ALTER USER priv_usr IDENTIFIED BY 'pwd2';
669 SET PASSWORD for priv_usr='pwd1';
670
671 # Now test with Global server variable ON
672 SET @@global.password_require_current=ON;
673
674 #------------------------------------------------------------------------
675 # Priv user changing his own password
676 #------------------------------------------------------------------------
677
678 # Can change without REPLACE clause
679 SET PASSWORD='pwd2';
680 SET PASSWORD for priv_usr='pwd1';
681 ALTER USER user() IDENTIFIED BY 'pwd2';
682 ALTER USER priv_usr IDENTIFIED BY 'pwd1';
683
684 # Incorrect password specified in the REPLACE clause
685 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D';
686 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
687 SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D';
688 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
689 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
690 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
691 ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
692 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
693
694 # Correct password specified in the REPLACE clause
695 SET PASSWORD='pwd2' REPLACE 'pwd1';
696 SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2';
697 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
698 ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'
699 WITH MAX_QUERIES_PER_HOUR 100 PASSWORD EXPIRE DEFAULT;
700
701 #------------------------------------------------------------------------
702 # Priv user changing the password of non_priv_usr
703 #------------------------------------------------------------------------
704
705 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D';
706 ERROR HY000: Do not specify the current password while changing it for other users.
707 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
708 ERROR HY000: Do not specify the current password while changing it for other users.
709
710 SET PASSWORD for non_priv_usr='pwd2' ;
711 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1';
712
713 #------------------------------------------------------------------------
714 # One priv user(root) changing password for other priv user (priv_usr)
715 #------------------------------------------------------------------------
716
717 SET PASSWORD for priv_usr='pwd1' REPLACE '';
718 ERROR HY000: Do not specify the current password while changing it for other users.
719 ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
720 ERROR HY000: Do not specify the current password while changing it for other users.
721 SET PASSWORD for priv_usr='pwd1';
722 ALTER USER priv_usr IDENTIFIED BY 'pwd2';
723
724 # Reset the privileges and global variable
725
726 REVOKE UPDATE ON mysql.* FROM 'priv_usr'@'%';
727 SET @@global.password_require_current=OFF;
728
729 #------------------------------------------------------------------------
730 # Non priv user changing his own password
731 #------------------------------------------------------------------------
732
733 # REPLACE clause is not specified
734 SET PASSWORD='pwd2';
735 SET PASSWORD for non_priv_usr='pwd2';
736 ALTER USER user() IDENTIFIED BY 'pwd1';
737 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1';
738
739 # Incorrect password specified in the REPLACE clause
740 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ;
741 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
742 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D';
743 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
744 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
745 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
746 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
747 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
748
749 # Correct password specified in the REPLACE clause
750 SET PASSWORD='pwd2' REPLACE 'pwd1';
751 SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2';
752 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
753 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
754
755 # Now test with Global server variable ON
756 SET @@global.password_require_current=ON;
757
758 #------------------------------------------------------------------------
759 # Non priv user changing his own password
760 #------------------------------------------------------------------------
761
762 # REPLACE clause is not specified
763 SET PASSWORD='pwd2';
764 SET PASSWORD for non_priv_usr='pwd1';
765 ALTER USER user() IDENTIFIED BY 'pwd2';
766 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1';
767
768 # Incorrect password specified in the REPLACE clause
769 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ;
770 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
771 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D';
772 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
773 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
774 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
775 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
776 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
777
778 # Correct password specified in the REPLACE clause
779 SET PASSWORD='pwd2' REPLACE 'pwd1';
780 SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2';
781 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
782 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
783
784 # Reset the Global server variable to OFF
785 SET @@global.password_require_current=OFF;
786
787 DROP USER non_priv_usr, priv_usr;
788
789 ########################################################################
790 # 5. Verify the ALTER USER scenarios
791 ########################################################################
792 CREATE USER non_priv_usr IDENTIFIED BY 'pwd1',
793 priv_usr IDENTIFIED BY 'pwd2'
794 PASSWORD REQUIRE CURRENT;
795 #
796 # Change password_require_current --> password_require_current_optional
797 #
798 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1',
799 priv_usr IDENTIFIED BY 'pwd2'
800 WITH MAX_USER_CONNECTIONS 2
801 PASSWORD REQUIRE CURRENT OPTIONAL
802 PASSWORD EXPIRE NEVER;
803
804 # Show how the users were created
805
806 SHOW CREATE USER non_priv_usr;
807 CREATE USER for non_priv_usr@%
808 CREATE USER `non_priv_usr`@`%` IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE WITH MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE NEVER ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT OPTIONAL
809 SHOW CREATE USER priv_usr;
810 CREATE USER for priv_usr@%
811 CREATE USER `priv_usr`@`%` IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE WITH MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE NEVER ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT OPTIONAL
812 SELECT password_require_current from mysql.user
813 where user like '%priv_usr' OR user like '%non_priv_usr';
814 password_require_current
815 N
816 N
817
818 # Grant the update privileges to the user
819 GRANT UPDATE ON mysql.* to 'priv_usr'@'%' WITH GRANT OPTION;
820
821 #------------------------------------------------------------------------
822 # Priv user changing his own password
823 #------------------------------------------------------------------------
824
825 # Without REPLACE clause
826 SET PASSWORD='pwd1';
827 SET PASSWORD for priv_usr='pwd2';
828 ALTER USER user() IDENTIFIED BY 'pwd1';
829 ALTER USER priv_usr IDENTIFIED BY 'pwd2';
830
831 # With invalid current password in the REPLACE clause
832 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D';
833 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
834 SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D';
835 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
836 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
837 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
838 ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
839 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
840
841 # With valid current password in the REPLACE clause
842 SET PASSWORD='pwd1' REPLACE 'pwd2';
843 SET PASSWORD for priv_usr='pwd2' REPLACE 'pwd1';
844 ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
845 ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
846
847 #------------------------------------------------------------------------
848 # Priv user changing the password of non_priv_usr
849 #------------------------------------------------------------------------
850
851 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'pwd1';
852 ERROR HY000: Do not specify the current password while changing it for other users.
853 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE '';
854 ERROR HY000: Do not specify the current password while changing it for other users.
855
856 SET PASSWORD for non_priv_usr='pwd2';
857 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2';
858
859 #------------------------------------------------------------------------
860 # One priv user(root) changing password for other priv user (priv_usr)
861 #------------------------------------------------------------------------
862
863 SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2';
864 ERROR HY000: Do not specify the current password while changing it for other users.
865 ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
866 ERROR HY000: Do not specify the current password while changing it for other users.
867
868 ALTER USER priv_usr IDENTIFIED BY 'pwd2';
869 SET PASSWORD for priv_usr='pwd1';
870
871 # Now test with Global server variable ON
872 SET @@global.password_require_current=ON;
873
874 #------------------------------------------------------------------------
875 # Priv user changing his own password
876 #------------------------------------------------------------------------
877
878 # Can change without REPLACE clause
879 SET PASSWORD='pwd2';
880 SET PASSWORD for priv_usr='pwd1';
881 ALTER USER user() IDENTIFIED BY 'pwd2';
882 ALTER USER priv_usr IDENTIFIED BY 'pwd1';
883
884 # Incorrect password specified in the REPLACE clause
885 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D';
886 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
887 SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D';
888 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
889 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
890 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
891 ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
892 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
893
894 # Correct password specified in the REPLACE clause
895 SET PASSWORD='pwd2' REPLACE 'pwd1';
896 SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2';
897 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
898 ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'
899 WITH MAX_QUERIES_PER_HOUR 100 PASSWORD EXPIRE DEFAULT;
900
901 #------------------------------------------------------------------------
902 # Priv user changing the password of non_priv_usr
903 #------------------------------------------------------------------------
904
905 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D';
906 ERROR HY000: Do not specify the current password while changing it for other users.
907 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
908 ERROR HY000: Do not specify the current password while changing it for other users.
909
910 SET PASSWORD for non_priv_usr='pwd2' ;
911 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1';
912
913 #------------------------------------------------------------------------
914 # One priv user(root) changing password for other priv user (priv_usr)
915 #------------------------------------------------------------------------
916
917 SET PASSWORD for priv_usr='pwd1' REPLACE '';
918 ERROR HY000: Do not specify the current password while changing it for other users.
919 ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
920 ERROR HY000: Do not specify the current password while changing it for other users.
921 SET PASSWORD for priv_usr='pwd1';
922 ALTER USER priv_usr IDENTIFIED BY 'pwd2';
923
924 # Reset the privileges and global variable
925
926 REVOKE UPDATE ON mysql.* FROM 'priv_usr'@'%';
927 SET @@global.password_require_current=OFF;
928
929 #------------------------------------------------------------------------
930 # Non priv user changing his own password
931 #------------------------------------------------------------------------
932
933 # REPLACE clause is not specified
934 SET PASSWORD='pwd2';
935 SET PASSWORD for non_priv_usr='pwd2';
936 ALTER USER user() IDENTIFIED BY 'pwd1';
937 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1';
938
939 # Incorrect password specified in the REPLACE clause
940 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ;
941 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
942 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D';
943 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
944 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
945 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
946 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
947 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
948
949 # Correct password specified in the REPLACE clause
950 SET PASSWORD='pwd2' REPLACE 'pwd1';
951 SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2';
952 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
953 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
954
955 # Now test with Global server variable ON
956 SET @@global.password_require_current=ON;
957
958 #------------------------------------------------------------------------
959 # Non priv user changing his own password
960 #------------------------------------------------------------------------
961
962 # REPLACE clause is not specified
963 SET PASSWORD='pwd2';
964 SET PASSWORD for non_priv_usr='pwd1';
965 ALTER USER user() IDENTIFIED BY 'pwd2';
966 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1';
967
968 # Incorrect password specified in the REPLACE clause
969 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ;
970 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
971 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D';
972 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
973 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
974 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
975 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
976 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
977
978 # Correct password specified in the REPLACE clause
979 SET PASSWORD='pwd2' REPLACE 'pwd1';
980 SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2';
981 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
982 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
983
984 # Reset the Global server variable to OFF
985 SET @@global.password_require_current=OFF;
986
987 #
988 # Change password_require_optional --> password_require_current_default
989 #
990 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1',
991 priv_usr IDENTIFIED BY 'pwd2'
992 PASSWORD EXPIRE DEFAULT
993 PASSWORD REUSE INTERVAL 0 DAY
994 PASSWORD REQUIRE CURRENT DEFAULT;
995
996 # Show how the users were created
997
998 SHOW CREATE USER non_priv_usr;
999 CREATE USER for non_priv_usr@%
1000 CREATE USER `non_priv_usr`@`%` IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE WITH MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL 0 DAY PASSWORD REQUIRE CURRENT DEFAULT
1001 SHOW CREATE USER priv_usr;
1002 CREATE USER for priv_usr@%
1003 CREATE USER `priv_usr`@`%` IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 100 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL 0 DAY PASSWORD REQUIRE CURRENT DEFAULT
1004 SELECT password_require_current from mysql.user
1005 where user like '%priv_usr' OR user like '%non_priv_usr';
1006 password_require_current
1007 NULL
1008 NULL
1009
1010 # Grant the update privileges to the user
1011 GRANT UPDATE ON mysql.* to 'priv_usr'@'%' WITH GRANT OPTION;
1012
1013 #------------------------------------------------------------------------
1014 # Priv user changing his own password
1015 #------------------------------------------------------------------------
1016
1017 # Without REPLACE clause
1018 SET PASSWORD='pwd1';
1019 SET PASSWORD for priv_usr='pwd2';
1020 ALTER USER user() IDENTIFIED BY 'pwd1';
1021 ALTER USER priv_usr IDENTIFIED BY 'pwd2';
1022
1023 # With invalid current password in the REPLACE clause
1024 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D';
1025 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1026 SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D';
1027 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1028 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1029 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1030 ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1031 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1032
1033 # With valid current password in the REPLACE clause
1034 SET PASSWORD='pwd1' REPLACE 'pwd2';
1035 SET PASSWORD for priv_usr='pwd2' REPLACE 'pwd1';
1036 ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
1037 ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
1038
1039 #------------------------------------------------------------------------
1040 # Priv user changing the password of non_priv_usr
1041 #------------------------------------------------------------------------
1042
1043 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'pwd1';
1044 ERROR HY000: Do not specify the current password while changing it for other users.
1045 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE '';
1046 ERROR HY000: Do not specify the current password while changing it for other users.
1047
1048 SET PASSWORD for non_priv_usr='pwd2';
1049 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2';
1050
1051 #------------------------------------------------------------------------
1052 # One priv user(root) changing password for other priv user (priv_usr)
1053 #------------------------------------------------------------------------
1054
1055 SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2';
1056 ERROR HY000: Do not specify the current password while changing it for other users.
1057 ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
1058 ERROR HY000: Do not specify the current password while changing it for other users.
1059
1060 ALTER USER priv_usr IDENTIFIED BY 'pwd2';
1061 SET PASSWORD for priv_usr='pwd1';
1062
1063 # Now test with Global server variable ON
1064 SET @@global.password_require_current=ON;
1065
1066 #------------------------------------------------------------------------
1067 # Priv user changing his own password
1068 #------------------------------------------------------------------------
1069
1070 # Can change without REPLACE clause
1071 SET PASSWORD='pwd2';
1072 SET PASSWORD for priv_usr='pwd1';
1073 ALTER USER user() IDENTIFIED BY 'pwd2';
1074 ALTER USER priv_usr IDENTIFIED BY 'pwd1';
1075
1076 # Incorrect password specified in the REPLACE clause
1077 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D';
1078 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1079 SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D';
1080 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1081 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1082 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1083 ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1084 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1085
1086 # Correct password specified in the REPLACE clause
1087 SET PASSWORD='pwd2' REPLACE 'pwd1';
1088 SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2';
1089 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
1090 ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'
1091 WITH MAX_QUERIES_PER_HOUR 100 PASSWORD EXPIRE DEFAULT;
1092
1093 #------------------------------------------------------------------------
1094 # Priv user changing the password of non_priv_usr
1095 #------------------------------------------------------------------------
1096
1097 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D';
1098 ERROR HY000: Do not specify the current password while changing it for other users.
1099 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1100 ERROR HY000: Do not specify the current password while changing it for other users.
1101
1102 SET PASSWORD for non_priv_usr='pwd2' ;
1103 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1';
1104
1105 #------------------------------------------------------------------------
1106 # One priv user(root) changing password for other priv user (priv_usr)
1107 #------------------------------------------------------------------------
1108
1109 SET PASSWORD for priv_usr='pwd1' REPLACE '';
1110 ERROR HY000: Do not specify the current password while changing it for other users.
1111 ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
1112 ERROR HY000: Do not specify the current password while changing it for other users.
1113 SET PASSWORD for priv_usr='pwd1';
1114 ALTER USER priv_usr IDENTIFIED BY 'pwd2';
1115
1116 # Reset the privileges and global variable
1117
1118 REVOKE UPDATE ON mysql.* FROM 'priv_usr'@'%';
1119 SET @@global.password_require_current=OFF;
1120
1121 #------------------------------------------------------------------------
1122 # Non priv user changing his own password
1123 #------------------------------------------------------------------------
1124
1125 # REPLACE clause is not specified
1126 SET PASSWORD='pwd2';
1127 SET PASSWORD for non_priv_usr='pwd2';
1128 ALTER USER user() IDENTIFIED BY 'pwd1';
1129 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1';
1130
1131 # Incorrect password specified in the REPLACE clause
1132 SET PASSWORD='pwd2' REPLACE '' ;
1133 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1134 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D';
1135 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1136 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1137 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1138 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
1139 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1140
1141 # Correct password specified in the REPLACE clause
1142 SET PASSWORD='pwd2' REPLACE 'pwd1';
1143 SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2';
1144 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
1145 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
1146
1147 # Now test with Global server variable ON
1148 SET @@global.password_require_current=ON;
1149
1150 #------------------------------------------------------------------------
1151 # Non priv user changing his own password
1152 #------------------------------------------------------------------------
1153
1154 # REPLACE clause is not specified
1155 SET PASSWORD='pwd2';
1156 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1157 SET PASSWORD for non_priv_usr='pwd2';
1158 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1159 ALTER USER user() IDENTIFIED BY 'pwd1';
1160 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1161 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1';
1162 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1163
1164 # Incorrect password specified in the REPLACE clause
1165 SET PASSWORD='pwd2' REPLACE '' ;
1166 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1167 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D';
1168 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1169 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1170 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1171 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
1172 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1173
1174 # Correct password specified in the REPLACE clause
1175 SET PASSWORD='pwd2' REPLACE 'pwd1';
1176 SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2';
1177 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
1178 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
1179
1180 # Reset the Global server variable to OFF
1181 SET @@global.password_require_current=OFF;
1182
1183 #
1184 # Change password_require_default --> password_require_current
1185 #
1186 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1',
1187 priv_usr IDENTIFIED BY 'pwd2'
1188 WITH MAX_QUERIES_PER_HOUR 100
1189 PASSWORD HISTORY 0
1190 PASSWORD REQUIRE CURRENT;
1191
1192 # Show how the users were created
1193
1194 SHOW CREATE USER non_priv_usr;
1195 CREATE USER for non_priv_usr@%
1196 CREATE USER `non_priv_usr`@`%` IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 100 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY 0 PASSWORD REUSE INTERVAL 0 DAY PASSWORD REQUIRE CURRENT
1197 SHOW CREATE USER priv_usr;
1198 CREATE USER for priv_usr@%
1199 CREATE USER `priv_usr`@`%` IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 100 MAX_USER_CONNECTIONS 2 PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY 0 PASSWORD REUSE INTERVAL 0 DAY PASSWORD REQUIRE CURRENT
1200 SELECT password_require_current from mysql.user
1201 where user like '%priv_usr' OR user like '%non_priv_usr';
1202 password_require_current
1203 Y
1204 Y
1205
1206 # Grant the update privileges to the user
1207 GRANT UPDATE ON mysql.* to 'priv_usr'@'%' WITH GRANT OPTION;
1208
1209 #------------------------------------------------------------------------
1210 # Priv user changing his own password
1211 #------------------------------------------------------------------------
1212
1213 # Without REPLACE clause
1214 SET PASSWORD='pwd1';
1215 SET PASSWORD for priv_usr='pwd2';
1216 ALTER USER user() IDENTIFIED BY 'pwd1';
1217 ALTER USER priv_usr IDENTIFIED BY 'pwd2';
1218
1219 # With invalid current password in the REPLACE clause
1220 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D';
1221 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1222 SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D';
1223 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1224 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1225 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1226 ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1227 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1228
1229 # With valid current password in the REPLACE clause
1230 SET PASSWORD='pwd1' REPLACE 'pwd2';
1231 SET PASSWORD for priv_usr='pwd2' REPLACE 'pwd1';
1232 ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
1233 ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
1234
1235 #------------------------------------------------------------------------
1236 # Priv user changing the password of non_priv_usr
1237 #------------------------------------------------------------------------
1238
1239 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'pwd1';
1240 ERROR HY000: Do not specify the current password while changing it for other users.
1241 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE '';
1242 ERROR HY000: Do not specify the current password while changing it for other users.
1243
1244 SET PASSWORD for non_priv_usr='pwd2';
1245 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2';
1246
1247 #------------------------------------------------------------------------
1248 # One priv user(root) changing password for other priv user (priv_usr)
1249 #------------------------------------------------------------------------
1250
1251 SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2';
1252 ERROR HY000: Do not specify the current password while changing it for other users.
1253 ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
1254 ERROR HY000: Do not specify the current password while changing it for other users.
1255
1256 ALTER USER priv_usr IDENTIFIED BY 'pwd2';
1257 SET PASSWORD for priv_usr='pwd1';
1258
1259 # Now test with Global server variable ON
1260 SET @@global.password_require_current=ON;
1261
1262 #------------------------------------------------------------------------
1263 # Priv user changing his own password
1264 #------------------------------------------------------------------------
1265
1266 # Can change without REPLACE clause
1267 SET PASSWORD='pwd2';
1268 SET PASSWORD for priv_usr='pwd1';
1269 ALTER USER user() IDENTIFIED BY 'pwd2';
1270 ALTER USER priv_usr IDENTIFIED BY 'pwd1';
1271
1272 # Incorrect password specified in the REPLACE clause
1273 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D';
1274 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1275 SET PASSWORD for priv_usr='pwd2' REPLACE 'P&(*^_)D';
1276 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1277 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1278 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1279 ALTER USER priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1280 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1281
1282 # Correct password specified in the REPLACE clause
1283 SET PASSWORD='pwd2' REPLACE 'pwd1';
1284 SET PASSWORD for priv_usr='pwd1' REPLACE 'pwd2';
1285 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
1286 ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2'
1287 WITH MAX_QUERIES_PER_HOUR 100 PASSWORD EXPIRE DEFAULT;
1288
1289 #------------------------------------------------------------------------
1290 # Priv user changing the password of non_priv_usr
1291 #------------------------------------------------------------------------
1292
1293 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D';
1294 ERROR HY000: Do not specify the current password while changing it for other users.
1295 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1296 ERROR HY000: Do not specify the current password while changing it for other users.
1297
1298 SET PASSWORD for non_priv_usr='pwd2' ;
1299 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1';
1300
1301 #------------------------------------------------------------------------
1302 # One priv user(root) changing password for other priv user (priv_usr)
1303 #------------------------------------------------------------------------
1304
1305 SET PASSWORD for priv_usr='pwd1' REPLACE '';
1306 ERROR HY000: Do not specify the current password while changing it for other users.
1307 ALTER USER priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
1308 ERROR HY000: Do not specify the current password while changing it for other users.
1309 SET PASSWORD for priv_usr='pwd1';
1310 ALTER USER priv_usr IDENTIFIED BY 'pwd2';
1311
1312 # Reset the privileges and global variable
1313
1314 REVOKE UPDATE ON mysql.* FROM 'priv_usr'@'%';
1315 SET @@global.password_require_current=OFF;
1316
1317 #------------------------------------------------------------------------
1318 # Non priv user changing his own password
1319 #------------------------------------------------------------------------
1320
1321 # REPLACE clause is not specified
1322 SET PASSWORD='pwd2';
1323 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1324 SET PASSWORD for non_priv_usr='pwd2';
1325 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1326 ALTER USER user() IDENTIFIED BY 'pwd1';
1327 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1328 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1';
1329 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1330
1331 # Incorrect password specified in the REPLACE clause
1332 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ;
1333 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1334 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D';
1335 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1336 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1337 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1338 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1339 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1340
1341 # Correct password specified in the REPLACE clause
1342 SET PASSWORD='pwd2' REPLACE 'pwd1';
1343 SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2';
1344 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
1345 ALTER USER IF EXISTS non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
1346
1347 # Now test with Global server variable ON
1348 SET @@global.password_require_current=ON;
1349
1350 #------------------------------------------------------------------------
1351 # Non priv user changing his own password
1352 #------------------------------------------------------------------------
1353
1354 # REPLACE clause is not specified
1355 SET PASSWORD='pwd2';
1356 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1357 SET PASSWORD for non_priv_usr='pwd2';
1358 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1359 ALTER USER user() IDENTIFIED BY 'pwd2';
1360 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1361 ALTER USER non_priv_usr IDENTIFIED BY 'pwd2';
1362 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1363
1364 # Incorrect password specified in the REPLACE clause
1365 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ;
1366 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1367 SET PASSWORD for non_priv_usr='pwd2' REPLACE 'P&(*^_)D';
1368 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1369 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1370 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1371 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
1372 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1373
1374 # Correct password specified in the REPLACE clause
1375 SET PASSWORD='pwd2' REPLACE 'pwd1';
1376 SET PASSWORD for non_priv_usr='pwd1' REPLACE 'pwd2';
1377 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
1378 ALTER USER non_priv_usr IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
1379
1380 # Reset the Global server variable to OFF
1381 SET @@global.password_require_current=OFF;
1382
1383 DROP USER non_priv_usr, priv_usr;
1384
1385 ########################################################################
1386 # 6. Change password after privileges are granted/revoked from the user
1387 ########################################################################
1388 CREATE USER usr1 IDENTIFIED BY 'pwd1' PASSWORD REQUIRE CURRENT;
1389
1390 #------------------------------------------------------------------------
1391 # Non priv user changing his own password
1392 #------------------------------------------------------------------------
1393
1394 # REPLACE clause is not specified
1395 SET PASSWORD='pwd2';
1396 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1397 SET PASSWORD for usr1='pwd2';
1398 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1399 ALTER USER user() IDENTIFIED BY 'pwd1';
1400 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1401 ALTER USER usr1 IDENTIFIED BY 'pwd1';
1402 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1403
1404 # Incorrect password specified in the REPLACE clause
1405 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ;
1406 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1407 SET PASSWORD for usr1='pwd2' REPLACE 'P&(*^_)D';
1408 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1409 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1410 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1411 ALTER USER usr1 IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1412 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1413
1414 # Correct password specified in the REPLACE clause
1415 SET PASSWORD='pwd2' REPLACE 'pwd1';
1416 SET PASSWORD for usr1='pwd1' REPLACE 'pwd2';
1417 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
1418 ALTER USER IF EXISTS usr1 IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
1419
1420 # Now test with Global server variable ON
1421 SET @@global.password_require_current=ON;
1422
1423 #------------------------------------------------------------------------
1424 # Non priv user changing his own password
1425 #------------------------------------------------------------------------
1426
1427 # REPLACE clause is not specified
1428 SET PASSWORD='pwd2';
1429 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1430 SET PASSWORD for usr1='pwd2';
1431 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1432 ALTER USER user() IDENTIFIED BY 'pwd2';
1433 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1434 ALTER USER usr1 IDENTIFIED BY 'pwd2';
1435 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1436
1437 # Incorrect password specified in the REPLACE clause
1438 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ;
1439 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1440 SET PASSWORD for usr1='pwd2' REPLACE 'P&(*^_)D';
1441 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1442 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1443 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1444 ALTER USER usr1 IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
1445 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1446
1447 # Correct password specified in the REPLACE clause
1448 SET PASSWORD='pwd2' REPLACE 'pwd1';
1449 SET PASSWORD for usr1='pwd1' REPLACE 'pwd2';
1450 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
1451 ALTER USER usr1 IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
1452
1453 # Reset the Global server variable to OFF
1454 SET @@global.password_require_current=OFF;
1455
1456 CREATE USER usr2 IDENTIFIED BY 'pwd1' PASSWORD REQUIRE CURRENT;
1457
1458 # Show how the users were created
1459
1460 SHOW CREATE USER usr2;
1461 CREATE USER for usr2@%
1462 CREATE USER `usr2`@`%` IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT
1463 SHOW CREATE USER usr1;
1464 CREATE USER for usr1@%
1465 CREATE USER `usr1`@`%` IDENTIFIED WITH '<default_auth_plugin>' AS '<password_hash>' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT
1466 SELECT password_require_current from mysql.user
1467 where user like '%usr1' OR user like '%usr2';
1468 password_require_current
1469 Y
1470 Y
1471
1472 # Grant the update privileges to the user
1473 GRANT UPDATE ON mysql.* to 'usr1'@'%' WITH GRANT OPTION;
1474
1475 #------------------------------------------------------------------------
1476 # Priv user changing his own password
1477 #------------------------------------------------------------------------
1478
1479 # Without REPLACE clause
1480 SET PASSWORD='pwd1';
1481 SET PASSWORD for usr1='pwd1';
1482 ALTER USER user() IDENTIFIED BY 'pwd1';
1483 ALTER USER usr1 IDENTIFIED BY 'pwd1';
1484
1485 # With invalid current password in the REPLACE clause
1486 SET PASSWORD='pwd1' REPLACE 'P&(*^_)D';
1487 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1488 SET PASSWORD for usr1='pwd1' REPLACE 'P&(*^_)D';
1489 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1490 ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
1491 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1492 ALTER USER usr1 IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
1493 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1494
1495 # With valid current password in the REPLACE clause
1496 SET PASSWORD='pwd1' REPLACE 'pwd1';
1497 SET PASSWORD for usr1='pwd1' REPLACE 'pwd1';
1498 ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'pwd1';
1499 ALTER USER usr1 IDENTIFIED BY 'pwd1' REPLACE 'pwd1';
1500
1501 #------------------------------------------------------------------------
1502 # Priv user changing the password of usr2
1503 #------------------------------------------------------------------------
1504
1505 SET PASSWORD for usr2='pwd1' REPLACE 'pwd1';
1506 ERROR HY000: Do not specify the current password while changing it for other users.
1507 ALTER USER usr2 IDENTIFIED BY 'pwd1' REPLACE '';
1508 ERROR HY000: Do not specify the current password while changing it for other users.
1509
1510 SET PASSWORD for usr2='pwd1';
1511 ALTER USER usr2 IDENTIFIED BY 'pwd1';
1512
1513 #------------------------------------------------------------------------
1514 # One priv user(root) changing password for other priv user (usr1)
1515 #------------------------------------------------------------------------
1516
1517 SET PASSWORD for usr1='pwd1' REPLACE 'pwd1';
1518 ERROR HY000: Do not specify the current password while changing it for other users.
1519 ALTER USER usr1 IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
1520 ERROR HY000: Do not specify the current password while changing it for other users.
1521
1522 ALTER USER usr1 IDENTIFIED BY 'pwd1';
1523 SET PASSWORD for usr1='pwd1';
1524
1525 # Now test with Global server variable ON
1526 SET @@global.password_require_current=ON;
1527
1528 #------------------------------------------------------------------------
1529 # Priv user changing his own password
1530 #------------------------------------------------------------------------
1531
1532 # Can change without REPLACE clause
1533 SET PASSWORD='pwd1';
1534 SET PASSWORD for usr1='pwd1';
1535 ALTER USER user() IDENTIFIED BY 'pwd1';
1536 ALTER USER usr1 IDENTIFIED BY 'pwd1';
1537
1538 # Incorrect password specified in the REPLACE clause
1539 SET PASSWORD='pwd1' REPLACE 'P&(*^_)D';
1540 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1541 SET PASSWORD for usr1='pwd1' REPLACE 'P&(*^_)D';
1542 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1543 ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
1544 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1545 ALTER USER usr1 IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
1546 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1547
1548 # Correct password specified in the REPLACE clause
1549 SET PASSWORD='pwd1' REPLACE 'pwd1';
1550 SET PASSWORD for usr1='pwd1' REPLACE 'pwd1';
1551 ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE 'pwd1';
1552 ALTER USER usr1 IDENTIFIED BY 'pwd1' REPLACE 'pwd1'
1553 WITH MAX_QUERIES_PER_HOUR 100 PASSWORD EXPIRE DEFAULT;
1554
1555 #------------------------------------------------------------------------
1556 # Priv user changing the password of usr2
1557 #------------------------------------------------------------------------
1558
1559 SET PASSWORD for usr2='pwd1' REPLACE 'P&(*^_)D';
1560 ERROR HY000: Do not specify the current password while changing it for other users.
1561 ALTER USER usr2 IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
1562 ERROR HY000: Do not specify the current password while changing it for other users.
1563
1564 SET PASSWORD for usr2='pwd1' ;
1565 ALTER USER usr2 IDENTIFIED BY 'pwd1';
1566
1567 #------------------------------------------------------------------------
1568 # One priv user(root) changing password for other priv user (usr1)
1569 #------------------------------------------------------------------------
1570
1571 SET PASSWORD for usr1='pwd1' REPLACE '';
1572 ERROR HY000: Do not specify the current password while changing it for other users.
1573 ALTER USER usr1 IDENTIFIED BY 'pwd1' REPLACE 'pwd1';
1574 ERROR HY000: Do not specify the current password while changing it for other users.
1575 SET PASSWORD for usr1='pwd1';
1576 ALTER USER usr1 IDENTIFIED BY 'pwd1';
1577
1578 # Reset the privileges and global variable
1579
1580 REVOKE UPDATE ON mysql.* FROM 'usr1'@'%';
1581 SET @@global.password_require_current=OFF;
1582 DROP USER usr2;
1583
1584 #------------------------------------------------------------------------
1585 # Non priv user changing his own password
1586 #------------------------------------------------------------------------
1587
1588 # REPLACE clause is not specified
1589 SET PASSWORD='pwd2';
1590 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1591 SET PASSWORD for usr1='pwd2';
1592 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1593 ALTER USER user() IDENTIFIED BY 'pwd1';
1594 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1595 ALTER USER usr1 IDENTIFIED BY 'pwd1';
1596 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1597
1598 # Incorrect password specified in the REPLACE clause
1599 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ;
1600 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1601 SET PASSWORD for usr1='pwd2' REPLACE 'P&(*^_)D';
1602 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1603 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1604 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1605 ALTER USER usr1 IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1606 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1607
1608 # Correct password specified in the REPLACE clause
1609 SET PASSWORD='pwd2' REPLACE 'pwd1';
1610 SET PASSWORD for usr1='pwd1' REPLACE 'pwd2';
1611 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
1612 ALTER USER IF EXISTS usr1 IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
1613
1614 # Now test with Global server variable ON
1615 SET @@global.password_require_current=ON;
1616
1617 #------------------------------------------------------------------------
1618 # Non priv user changing his own password
1619 #------------------------------------------------------------------------
1620
1621 # REPLACE clause is not specified
1622 SET PASSWORD='pwd2';
1623 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1624 SET PASSWORD for usr1='pwd2';
1625 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1626 ALTER USER user() IDENTIFIED BY 'pwd2';
1627 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1628 ALTER USER usr1 IDENTIFIED BY 'pwd2';
1629 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1630
1631 # Incorrect password specified in the REPLACE clause
1632 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ;
1633 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1634 SET PASSWORD for usr1='pwd2' REPLACE 'P&(*^_)D';
1635 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1636 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1637 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1638 ALTER USER usr1 IDENTIFIED BY 'pwd1' REPLACE 'P&(*^_)D';
1639 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1640
1641 # Correct password specified in the REPLACE clause
1642 SET PASSWORD='pwd2' REPLACE 'pwd1';
1643 SET PASSWORD for usr1='pwd1' REPLACE 'pwd2';
1644 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'pwd1';
1645 ALTER USER usr1 IDENTIFIED BY 'pwd1' REPLACE 'pwd2';
1646
1647 # Reset the Global server variable to OFF
1648 SET @@global.password_require_current=OFF;
1649
1650 DROP USER usr1;
1651
1652 ########################################################################
1653 # 7. Change password of an user using old auth plugin with empty password
1654 ########################################################################
1655 CREATE USER usr1 IDENTIFIED WITH 'mysql_native_password'
1656 PASSWORD REQUIRE CURRENT;
1657
1658 #------------------------------------------------------------------------
1659 # Non priv user changing his own password
1660 #------------------------------------------------------------------------
1661
1662 # REPLACE clause is not specified
1663 SET PASSWORD='pwd2';
1664 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1665 SET PASSWORD for usr1='pwd2';
1666 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1667 ALTER USER user() IDENTIFIED BY '';
1668 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1669 ALTER USER usr1 IDENTIFIED BY '';
1670 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1671
1672 # Incorrect password specified in the REPLACE clause
1673 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ;
1674 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1675 SET PASSWORD for usr1='pwd2' REPLACE 'P&(*^_)D';
1676 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1677 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1678 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1679 ALTER USER usr1 IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1680 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1681
1682 # Correct password specified in the REPLACE clause
1683 SET PASSWORD='pwd2' REPLACE '';
1684 SET PASSWORD for usr1='' REPLACE 'pwd2';
1685 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE '';
1686 ALTER USER IF EXISTS usr1 IDENTIFIED BY '' REPLACE 'pwd2';
1687
1688 # Now test with Global server variable ON
1689 SET @@global.password_require_current=ON;
1690
1691 #------------------------------------------------------------------------
1692 # Non priv user changing his own password
1693 #------------------------------------------------------------------------
1694
1695 # REPLACE clause is not specified
1696 SET PASSWORD='pwd2';
1697 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1698 SET PASSWORD for usr1='pwd2';
1699 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1700 ALTER USER user() IDENTIFIED BY 'pwd2';
1701 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1702 ALTER USER usr1 IDENTIFIED BY 'pwd2';
1703 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1704
1705 # Incorrect password specified in the REPLACE clause
1706 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ;
1707 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1708 SET PASSWORD for usr1='pwd2' REPLACE 'P&(*^_)D';
1709 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1710 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1711 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1712 ALTER USER usr1 IDENTIFIED BY '' REPLACE 'P&(*^_)D';
1713 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1714
1715 # Correct password specified in the REPLACE clause
1716 SET PASSWORD='pwd2' REPLACE '';
1717 SET PASSWORD for usr1='' REPLACE 'pwd2';
1718 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE '';
1719 ALTER USER usr1 IDENTIFIED BY '' REPLACE 'pwd2';
1720
1721 # Reset the Global server variable to OFF
1722 SET @@global.password_require_current=OFF;
1723
1724 DROP USER usr1;
1725 CREATE USER usr1 IDENTIFIED WITH 'sha256_password'
1726 PASSWORD REQUIRE CURRENT;
1727
1728 #------------------------------------------------------------------------
1729 # Non priv user changing his own password
1730 #------------------------------------------------------------------------
1731
1732 # REPLACE clause is not specified
1733 SET PASSWORD='pwd2';
1734 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1735 SET PASSWORD for usr1='pwd2';
1736 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1737 ALTER USER user() IDENTIFIED BY '';
1738 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1739 ALTER USER usr1 IDENTIFIED BY '';
1740 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1741
1742 # Incorrect password specified in the REPLACE clause
1743 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ;
1744 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1745 SET PASSWORD for usr1='pwd2' REPLACE 'P&(*^_)D';
1746 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1747 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1748 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1749 ALTER USER usr1 IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1750 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1751
1752 # Correct password specified in the REPLACE clause
1753 SET PASSWORD='pwd2' REPLACE '';
1754 SET PASSWORD for usr1='' REPLACE 'pwd2';
1755 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE '';
1756 ALTER USER IF EXISTS usr1 IDENTIFIED BY '' REPLACE 'pwd2';
1757
1758 # Now test with Global server variable ON
1759 SET @@global.password_require_current=ON;
1760
1761 #------------------------------------------------------------------------
1762 # Non priv user changing his own password
1763 #------------------------------------------------------------------------
1764
1765 # REPLACE clause is not specified
1766 SET PASSWORD='pwd2';
1767 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1768 SET PASSWORD for usr1='pwd2';
1769 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1770 ALTER USER user() IDENTIFIED BY 'pwd2';
1771 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1772 ALTER USER usr1 IDENTIFIED BY 'pwd2';
1773 ERROR HY000: Current password needs to be specified in the REPLACE clause in order to change it.
1774
1775 # Incorrect password specified in the REPLACE clause
1776 SET PASSWORD='pwd2' REPLACE 'P&(*^_)D' ;
1777 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1778 SET PASSWORD for usr1='pwd2' REPLACE 'P&(*^_)D';
1779 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1780 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE 'P&(*^_)D';
1781 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1782 ALTER USER usr1 IDENTIFIED BY '' REPLACE 'P&(*^_)D';
1783 ERROR HY000: Incorrect current password. Specify the correct password which has to be replaced.
1784
1785 # Correct password specified in the REPLACE clause
1786 SET PASSWORD='pwd2' REPLACE '';
1787 SET PASSWORD for usr1='' REPLACE 'pwd2';
1788 ALTER USER user() IDENTIFIED BY 'pwd2' REPLACE '';
1789 ALTER USER usr1 IDENTIFIED BY '' REPLACE 'pwd2';
1790
1791 # Reset the Global server variable to OFF
1792 SET @@global.password_require_current=OFF;
1793
1794 DROP USER usr1;
1795
1796 ########################################################################
1797 # 8. Change the password of multiple users at once.
1798 ########################################################################
1799 CREATE USER u1, u2, u3;
1800 GRANT SELECT, CREATE USER ON *.* to 'u1' WITH GRANT OPTION;
1801 SELECT user, password_require_current FROM mysql.user WHERE USER LIKE 'u%';
1802 user password_require_current
1803 u1 NULL
1804 u2 NULL
1805 u3 NULL
1806 ALTER USER u1 IDENTIFIED BY '' REPLACE '', u2 IDENTIFIED BY '',
1807 u3 IDENTIFIED BY '' PASSWORD REQUIRE CURRENT OPTIONAL;
1808 SELECT user, password_require_current FROM mysql.user WHERE USER LIKE 'u%';
1809 user password_require_current
1810 u1 N
1811 u2 N
1812 u3 N
1813 ALTER USER u2 IDENTIFIED BY '', u1 IDENTIFIED BY '' REPLACE '',
1814 u3 IDENTIFIED BY '';
1815 ALTER USER u3 IDENTIFIED BY '' , u2 IDENTIFIED BY '',
1816 u1 IDENTIFIED BY '' REPLACE '';
1817 ALTER USER u1 IDENTIFIED BY '' REPLACE '', u2 IDENTIFIED BY '' REPLACE '';
1818 ERROR HY000: Do not specify the current password while changing it for other users.
1819 ALTER USER u2 IDENTIFIED BY '' REPLACE '', u1 IDENTIFIED BY '';
1820 ERROR HY000: Do not specify the current password while changing it for other users.
1821 ALTER USER u1 IDENTIFIED BY '123',
1822 u2 IDENTIFIED BY '123' REPLACE '',
1823 u3 IDENTIFIED BY '';
1824 ERROR HY000: Do not specify the current password while changing it for other users.
1825 DROP USER u1, u2, u3;
1826
1827 ########################################################################
1828 # 9. Change the password as well as password require current clause
1829 # of an user
1830 ########################################################################
1831 CREATE USER u1, u2;
1832 SELECT user, password_require_current FROM mysql.user WHERE USER LIKE 'u%';
1833 user password_require_current
1834 u1 NULL
1835 u2 NULL
1836 ALTER USER u1 IDENTIFIED BY '' REPLACE '' PASSWORD REQUIRE CURRENT;
1837 ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
1838 GRANT SELECT, CREATE USER ON *.* to 'u1' WITH GRANT OPTION;
1839 ALTER USER u1 IDENTIFIED BY '' REPLACE '' PASSWORD REQUIRE CURRENT;
1840 SELECT user, password_require_current FROM mysql.user WHERE USER LIKE 'u%';
1841 user password_require_current
1842 u1 Y
1843 u2 NULL
1844 ALTER USER u1 IDENTIFIED BY '' PASSWORD REQUIRE CURRENT OPTIONAL;
1845 SELECT user, password_require_current FROM mysql.user WHERE USER LIKE 'u%';
1846 user password_require_current
1847 u1 N
1848 u2 NULL
1849 DROP USER u1, u2;
1850
1851 ########################################################################
1852 # 10. Change the password of proxy user and proxied user
1853 ########################################################################
1854 CREATE USER 'u1' IDENTIFIED with 'mysql_native_password';
1855 CREATE USER 'u2' IDENTIFIED with 'mysql_native_password';
1856 GRANT PROXY ON 'u1' TO 'u2';
1857 SET @@global.check_proxy_users = ON;
1858 SET @@global.mysql_native_password_proxy_users = ON;
1859 SET @@global.password_require_current = ON;
1860 SET PASSWORD='abc' REPLACE '';
1861 ALTER USER u1 IDENTIFIED BY '' REPLACE 'abc';
1862 ALTER USER u2 IDENTIFIED BY '' REPLACE '';
1863 ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
1864 SET PASSWORD='abc' REPLACE '';
1865 ALTER USER USER() IDENTIFIED BY '' REPLACE 'abc';
1866 ALTER USER u1 IDENTIFIED BY 'abc' REPLACE '';
1867 ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
1868 GRANT CREATE USER ON *.* to 'u1' WITH GRANT OPTION;
1869 SELECT USER();
1870 USER()
1871 u1@localhost
1872 SELECT CURRENT_USER();
1873 CURRENT_USER()
1874 u1@%
1875 SET PASSWORD='abc' REPLACE '';
1876 ALTER USER u2 IDENTIFIED BY 'abc' REPLACE '';
1877 ERROR HY000: Do not specify the current password while changing it for other users.
1878 SELECT USER();
1879 USER()
1880 u2@localhost
1881 SELECT CURRENT_USER();
1882 CURRENT_USER()
1883 u1@%
1884 SET PASSWORD='abc' REPLACE '';
1885 ALTER USER u1 IDENTIFIED BY '' REPLACE 'abc';
1886 ERROR HY000: Do not specify the current password while changing it for other users.
1887 DROP USER u1, u2;
1888 SET @@global.check_proxy_users = OFF;
1889 SET @@global.mysql_native_password_proxy_users = OFF;
1890 SET @@global.password_require_current = OFF;
1891
1892 ########################################################################
1893 # 11. Negative Tests
1894 ########################################################################
1895 CREATE USER non_priv_usr PASSWORD REQUIRE;
1896 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
1897 CREATE USER non_priv_usr PASSWORD REQUIRE PASSWORD EXPIRE DEFAULT;
1898 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 'PASSWORD EXPIRE DEFAULT' at line 1
1899 CREATE USER non_priv_usr IDENTIFIED BY '123' REPLACE '133';
1900 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 'REPLACE '133'' at line 1
1901 ALTER USER user() IDENTIFIED BY 'pwd1' REPLACE;
1902 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
1903 ALTER USER root IDENTIFIED BY 'pwd1' REPLACE;
1904 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
1905 SET PASSWORD='' REPLACE;
1906 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
1907 SET PASSWORD FOR root='' REPLACE;
1908 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
1909 ALTER USER user() IDENTIFIED AS '$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED' replace '' ;
1910 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 'AS '$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED' repl' at line 1