Debian Codegraph @

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

raw
1 # Save the initial number of concurrent sessions
2 --source include/count_sessions.inc
3 # For non built-in plugin tests
4 --source include/have_plugin_auth.inc
5
6 --source include/have_log_bin.inc
7
8 --echo #-----------------------------------------------------------------------
9 --echo # Setup : Create users, grant privileges, create connections
10
11 # Create test users
12 CREATE USER arthurdent@localhost IDENTIFIED BY 'abcd';
13 CREATE USER marvintheparanoidandroid@localhost IDENTIFIED BY 'efghi';
14 CREATE USER zaphodbeeblebrox@localhost IDENTIFIED BY 'xyz';
15
16 # Grant privileges to test users
17 GRANT CREATE USER ON *.* TO arthurdent@localhost;
18 GRANT UPDATE ON mysql.* TO zaphodbeeblebrox@localhost;
19 GRANT APPLICATION_PASSWORD_ADMIN ON *.* TO marvintheparanoidandroid@localhost;
20
21 # Create connections
22
23 --connect(arthur_conn, localhost, arthurdent, abcd,,,,)
24 --connect(marvin_conn, localhost, marvintheparanoidandroid, efghi,,,,)
25 --connect(zaphod_conn, localhost, zaphodbeeblebrox, xyz,,,,)
26
27 --connection default
28
29 --echo #-----------------------------------------------------------------------
30 --echo # Test 1: Use RETAIN CURRENT PASSWORD with ALTER USER
31
32 # No entry in mysql.user for arthurdent@localhost
33 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
34 AS ADDITIONAL_PASSWORD_FOR_ARTHURDENT FROM mysql.user
35 WHERE user LIKE 'arthurdent';
36
37 # Change password
38 ALTER USER arthurdent@localhost IDENTIFIED BY 'efghi' RETAIN CURRENT PASSWORD;
39
40 # Entry should be present in mysql.user for arthurdent@localhost
41 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
42 AS ADDITIONAL_PASSWORD_FOR_ARTHURDENT FROM mysql.user
43 WHERE user LIKE 'arthurdent';
44
45 SET @additional_password_1=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
46 '$.additional_password') FROM mysql.user WHERE user LIKE 'arthurdent');
47
48 # Change password
49 ALTER USER arthurdent@localhost IDENTIFIED by 'xyz' RETAIN CURRENT PASSWORD;
50
51 # Entry should be present in mysql.user for arthurdent@localhost
52 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
53 AS ADDITIONAL_PASSWORD_FOR_ARTHURDENT FROM mysql.user
54 WHERE user LIKE 'arthurdent';
55
56 SET @additional_password_2=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
57 '$.additional_password') FROM mysql.user WHERE user LIKE 'arthurdent');
58
59 # Assert that password stored in mysql.user.user_attributes changed
60 SELECT @additional_password_1 <> @additional_password_2 AS
61 ADDITIONAL_PASSWORD_CHANGED;
62
63 # Change password without RETAIN CURRENT PASSWORD
64 ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd';
65
66 # Entry should still be present int mysql.user for arthurdent@localhost
67 SELECT COUNT(*) AS ADDITIONAL_PASSWORD_FOR_ARTHURDENT FROM mysql.user
68 WHERE user LIKE 'arthurdent';
69
70 SET @additional_password_3=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
71 '$.additional_password') FROM mysql.user WHERE user LIKE 'arthurdent');
72
73 # Assert that password stored in mysql.user did not change
74 SELECT @additional_password_2 = @additional_password_3 AS
75 ADDITIONAL_PASSWORD_NOT_CHANGED;
76
77 --echo #-----------------------------------------------------------------------
78 --echo # Test 2: Use DISCARD OLD PASSWORD with ALTER USER
79
80 # No entry in mysql.user for marvintheparanoidandroid@localhost
81 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
82 AS ADDITONAL_PASSWORD_FOR_MARVINTHEPARANOIDANDROID FROM
83 mysql.user WHERE user LIKE 'marvintheparanoidandroid';
84
85 # Change password
86 ALTER USER marvintheparanoidandroid@localhost IDENTIFIED BY 'abcd'
87 RETAIN CURRENT PASSWORD;
88
89 # Entry should be present in mysql.user for marvintheparanoidandroid@localhost
90 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
91 AS ADDITONAL_PASSWORD_FOR_MARVINTHEPARANOIDANDROID FROM
92 mysql.user WHERE user LIKE 'marvintheparanoidandroid';
93
94 # Discard old password
95 ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
96
97 # Entry should not be present in mysql.user for marvintheparanoidandroid@localhost
98 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
99 AS ADDITONAL_PASSWORD_FOR_MARVINTHEPARANOIDANDROID FROM
100 mysql.user WHERE user LIKE 'marvintheparanoidandroid';
101
102 --echo #-----------------------------------------------------------------------
103 --echo # Test 3: Use RETAIN CURRENT PASSWORD with SET PASSWORD
104
105 # No entry in mysql.user for zaphodbeeblebrox@localhost
106 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
107 AS ADDITIONAL_PASSWORD_FOR_ZAPHODBEEBLEBROX FROM
108 mysql.user WHERE user LIKE 'zaphodbeeblebrox';
109
110 # Change password
111 SET PASSWORD FOR zaphodbeeblebrox@localhost = 'abcd' RETAIN CURRENT PASSWORD;
112
113 # Entry should be present in mysql.user for zaphodbeeblebrox@localhost
114 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
115 AS ADDITIONAL_PASSWORD_FOR_ZAPHODBEEBLEBROX FROM
116 mysql.user WHERE user LIKE 'zaphodbeeblebrox';
117
118 SET @additional_password_1=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
119 '$.additional_password') FROM mysql.user WHERE user LIKE 'zaphodbeeblebrox');
120
121 # Change password
122 SET PASSWORD FOR zaphodbeeblebrox@localhost = 'efghi' RETAIN CURRENT PASSWORD;
123
124 # Entry should be present in mysql.user for zaphodbeeblebrox@localhost
125 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
126 AS ADDITIONAL_PASSWORD_FOR_ZAPHODBEEBLEBROX FROM
127 mysql.user WHERE user LIKE 'zaphodbeeblebrox';
128
129 SET @additional_password_2=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
130 '$.additional_password') FROM mysql.user WHERE user LIKE 'zaphodbeeblebrox');
131
132 # Assert that password stored in mysql.user changed
133 SELECT @additional_password_1 <> @additional_password_2 AS
134 ADDITIONAL_PASSWORD_CHANGED;
135
136 # Change password without RETAIN CURRENT PASSWORD
137 SET PASSWORD FOR zaphodbeeblebrox@localhost='xyz';
138
139 # Entry should be present in mysql.user for zaphodbeeblebrox@localhost
140 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
141 AS ADDITIONAL_PASSWORD_FOR_ZAPHODBEEBLEBROX FROM
142 mysql.user WHERE user LIKE 'zaphodbeeblebrox';
143
144 SET @additional_password_3=(SELECT JSON_EXTRACT(mysql.user.user_attributes,
145 '$.additional_password') FROM mysql.user WHERE user LIKE 'zaphodbeeblebrox');
146
147 # Assert that password stored in mysql.user did not change
148 SELECT @additional_password_2 = @additional_password_3 AS
149 ADDITIONAL_PASSWORD_NOT_CHANGED;
150
151 --echo #-----------------------------------------------------------------------
152 --echo # Test 4: RENAME USER
153
154 # Rename a user
155 RENAME USER arthurdent@localhost TO arthurdent1@localhost;
156
157 # Check effect on mysql.user
158 SELECT COUNT(JSON_KEYS(mysql.user.user_attributes)) AS
159 ADDITIONAL_PASSWORD_FOR_ARTHURDENT FROM mysql.user
160 WHERE user LIKE 'arthurdent';
161
162 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
163 AS ADDITIONAL_PASSWORD_FOR_ARTHURDENT1 FROM mysql.user
164 WHERE user LIKE 'arthurdent1';
165
166 # Rename once again
167 RENAME USER arthurdent1@localhost TO arthurdent@localhost;
168
169 # Check effect on mysql.user
170 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
171 AS ADDITIONAL_PASSWORD_FOR_ARTHURDENT FROM mysql.user
172 WHERE user LIKE 'arthurdent';
173
174 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
175 AS ADDITIONAL_PASSWORD_FOR_ARTHURDENT1 FROM mysql.user
176 WHERE user LIKE 'arthurdent1';
177
178 --echo #-----------------------------------------------------------------------
179 --echo # Test 5: DROP USER
180
181 CREATE USER u1@localhost IDENTIFIED BY 'abcd';
182 CREATE USER u2@localhost IDENTIFIED BY 'abcd';
183
184 # Change passwords
185 ALTER USER u1@localhost IDENTIFIED BY 'efghi' RETAIN CURRENT PASSWORD,
186 u2@localhost IDENTIFIED BY 'efghi' RETAIN CURRENT PASSWORD;
187
188 # Check that additional passwords are present in mysql.user
189 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
190 AS ADDITIONAL_PASSWORD_FOR_U1 FROM mysql.user WHERE user LIKE 'u1';
191
192 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
193 AS ADDITIONAL_PASSWORD_FOR_U2 FROM mysql.user WHERE user LIKE 'u2';
194
195 # Drop users
196 DROP USER u1@localhost, u2@localhost;
197
198 # Check that passwords are removed from mysql.user
199 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
200 AS ADDITIONAL_PASSWORD_FOR_U1 FROM mysql.user WHERE user LIKE 'u1';
201
202 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
203 AS ADDITIONAL_PASSWORD_FOR_U2 FROM mysql.user WHERE user LIKE 'u2';
204
205 --echo #-----------------------------------------------------------------------
206 --echo # Test 6: Syntax
207
208 # Clean slate
209 ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
210 ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
211 ALTER USER zaphodbeeblebrox@localhost DISCARD OLD PASSWORD;
212
213 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
214 AS NO_ADDITIONAL_PASSWORDS FROM mysql.user;
215
216 # RETAIN CURRENT PASSWORD for multiple users
217 ALTER USER arthurdent@localhost IDENTIFIED by '1234' RETAIN CURRENT PASSWORD,
218 marvintheparanoidandroid@localhost IDENTIFIED BY '5678',
219 zaphodbeeblebrox@localhost IDENTIFIED BY '9012' RETAIN CURRENT PASSWORD;
220
221 # 2 additional passwords
222 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
223 AS TWO_ADDITIONAL_PASSWORDS FROM mysql.user;
224
225 # MIX-N-MATCH
226 ALTER USER arthurdent@localhost DISCARD OLD PASSWORD,
227 marvintheparanoidandroid@localhost IDENTIFIED BY '5678'
228 RETAIN CURRENT PASSWORD,
229 zaphodbeeblebrox@localhost IDENTIFIED BY '9012';
230
231 # 2 additional passwords
232 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
233 AS TWO_ADDITIONAL_PASSWORDS FROM mysql.user;
234
235 # DISCARD CURRENT PASSWORD for multiple users
236 ALTER USER arthurdent@localhost IDENTIFIED BY '5678',
237 marvintheparanoidandroid@localhost DISCARD OLD PASSWORD,
238 zaphodbeeblebrox@localhost DISCARD OLD PASSWORD;
239
240 # No additional passwords
241 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
242 AS NO_ADDITIONAL_PASSWORDS FROM mysql.user;
243
244 --echo #-----------------------------------------------------------------------
245 --echo # Test 7: Permission
246
247 --connection arthur_conn
248 SET PASSWORD FOR arthurdent@localhost = 'abcd' RETAIN CURRENT PASSWORD;
249 ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
250
251 --connection zaphod_conn
252 ALTER USER arthurdent@localhost IDENTIFIED BY 'efghi' RETAIN CURRENT PASSWORD;
253 SET PASSWORD = 'abcd' RETAIN CURRENT PASSWORD;
254 ALTER USER arthurdent@localhost DISCARD OLD PASSWORD,
255 zaphodbeeblebrox@localhost DISCARD OLD PASSWORD;
256
257 --connection marvin_conn
258 SET PASSWORD = 'xyz' RETAIN CURRENT PASSWORD;
259 ALTER USER marvintheparanoidandroid@localhost
260 IDENTIFIED BY 'abcd' RETAIN CURRENT PASSWORD;
261 ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
262 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
263 ALTER USER marvintheparanoidandroid@localhost
264 IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD,
265 arthurdent@localhost DISCARD OLD PASSWORD;
266
267 --echo #-----------------------------------------------------------------------
268 --echo # Test 8: Non built-in plugin
269
270 --connection default
271 CREATE USER plug IDENTIFIED WITH test_plugin_server BY '123';
272 ALTER USER plug IDENTIFIED BY '123' RETAIN CURRENT PASSWORD;
273 ALTER USER plug DISCARD OLD PASSWORD;
274 DROP USER plug;
275
276 --echo #-----------------------------------------------------------------------
277 --echo # Test 9: Plugin change
278
279 --connection default
280 ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
281 ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
282 ALTER USER zaphodbeeblebrox@localhost DISCARD OLD PASSWORD;
283
284 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
285 AS ZERO_ADDITIONAL_PASSWORDS FROM mysql.user;
286
287 ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd' RETAIN CURRENT PASSWORD;
288 ALTER USER marvintheparanoidandroid@localhost IDENTIFIED BY 'efgh'
289 RETAIN CURRENT PASSWORD;
290 ALTER USER zaphodbeeblebrox@localhost IDENTIFIED BY 'ijkl'
291 RETAIN CURRENT PASSWORD;
292
293 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
294 AS THREE_ADDITIONAL_PASSWORDS FROM mysql.user;
295
296 ALTER USER arthurdent@localhost IDENTIFIED WITH 'mysql_native_password'
297 BY 'abcd';
298 ALTER USER marvintheparanoidandroid@localhost IDENTIFIED WITH 'sha256_password'
299 BY 'efgh';
300 ALTER USER zaphodbeeblebrox@localhost IDENTIFIED WITH 'mysql_native_password'
301 BY 'ijkl';
302
303 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
304 AS ZERO_ADDITIONAL_PASSWORDS FROM mysql.user;
305
306 --echo #-----------------------------------------------------------------------
307 --echo # Test 10: Empty first password
308
309 --connection default
310 SET PASSWORD FOR arthurdent@localhost = '';
311 --error ER_SECOND_PASSWORD_CANNOT_BE_EMPTY
312 ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd' RETAIN CURRENT PASSWORD;
313
314 --echo #-----------------------------------------------------------------------
315 --echo # Test 11: Binary log
316
317 --connection default
318 CREATE USER userX IDENTIFIED BY 'abcd', userY IDENTIFIED BY 'efgh';
319 --source include/save_binlog_position.inc
320 ALTER USER userX IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD,
321 userY IDENTIFIED BY 'ijkl' RETAIN CURRENT PASSWORD;
322 --let $event= !Q(ALTER USER.*userX.*RETAIN CURRENT PASSWORD.*userY.*RETAIN CURRENT PASSWORD.*)
323 --source ../include/auth_sec_assert_binlog_events.inc
324 --source include/save_binlog_position.inc
325 ALTER USER userX DISCARD OLD PASSWORD,
326 userY IDENTIFIED BY 'mnop' RETAIN CURRENT PASSWORD;
327 --let $event= !Q(ALTER USER.*userX.*DISCARD OLD PASSWORD.*userY.*RETAIN CURRENT PASSWORD.*)
328 --source ../include/auth_sec_assert_binlog_events.inc
329 --source include/save_binlog_position.inc
330 ALTER USER userX IDENTIFIED BY 'qrst' RETAIN CURRENT PASSWORD,
331 userY DISCARD OLD PASSWORD PASSWORD REQUIRE CURRENT;
332 --let $event= !Q(ALTER USER.*userX.*RETAIN CURRENT PASSWORD.*userY.*DISCARD OLD PASSWORD.*PASSWORD REQUIRE CURRENT.*)
333 --source ../include/auth_sec_assert_binlog_events.inc
334 --source include/save_binlog_position.inc
335 DROP USER userX;
336
337 --echo #-----------------------------------------------------------------------
338 --echo # Test 12: With REPLACE clause
339
340 --connection default
341 ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
342
343 # Force user to provide current password while changing the password
344 ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd' PASSWORD REQUIRE CURRENT;
345
346 # Now change passwords and retain existing ones
347 ALTER USER arthurdent@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD;
348
349 --connection arthur_conn
350 # should work
351 ALTER USER arthurdent@localhost IDENTIFIED BY 'ijkl' REPLACE 'efgh';
352 SET PASSWORD = 'mnop' REPLACE 'ijkl';
353 SET PASSWORD = 'qrst' REPLACE 'mnop' RETAIN CURRENT PASSWORD;
354 ALTER USER arthurdent@localhost IDENTIFIED BY 'uvwx' REPLACE 'qrst' RETAIN CURRENT PASSWORD;
355 # should not work
356 --error ER_INCORRECT_CURRENT_PASSWORD
357 ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd' REPLACE 'qrst';
358 --error ER_INCORRECT_CURRENT_PASSWORD
359 SET PASSWORD = 'efgh' REPLACE 'qrst';
360
361 --echo #-----------------------------------------------------------------------
362 --echo # Test 13: ALTER USER with USER() function
363
364 --connection default
365 ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
366 ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
367 ALTER USER zaphodbeeblebrox@localhost DISCARD OLD PASSWORD;
368 ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd';
369 ALTER USER arthurdent@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD;
370
371 --connection arthur_conn
372 # should work
373 ALTER USER USER() IDENTIFIED BY 'ijkl' RETAIN CURRENT PASSWORD;
374 ALTER USER USER() DISCARD OLD PASSWORD;
375
376 --echo #-----------------------------------------------------------------------
377 --echo # Test 14: Plugin change and retained password
378
379 --connection default
380 ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
381 ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
382 ALTER USER zaphodbeeblebrox@localhost DISCARD OLD PASSWORD;
383 ALTER USER arthurdent@localhost IDENTIFIED WITH 'caching_sha2_password' BY 'abcd';
384 ALTER USER arthurdent@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD;
385
386 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
387 AS ONE_ADDITIONAL_PASSWORDS FROM mysql.user;
388
389 # Retained password is not cleared if plugin is not changed
390 ALTER USER arthurdent@localhost IDENTIFIED WITH caching_sha2_password BY 'ijkl';
391
392 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
393 AS ONE_ADDITIONAL_PASSWORDS FROM mysql.user;
394
395 # Retained password is cleared if plugin is changed
396 ALTER USER arthurdent@localhost IDENTIFIED WITH 'mysql_native_password' BY 'mnop';
397
398 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
399 AS ZERO_ADDITIONAL_PASSWORDS FROM mysql.user;
400
401 --error ER_PASSWORD_CANNOT_BE_RETAINED_ON_PLUGIN_CHANGE
402 ALTER USER arthurdent@localhost IDENTIFIED WITH 'sha256_password' BY 'qrst' RETAIN CURRENT PASSWORD;
403 --echo #-----------------------------------------------------------------------
404 --echo # Test 15: Setting empty password
405 --connection default
406 ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
407 ALTER USER marvintheparanoidandroid@localhost DISCARD OLD PASSWORD;
408 ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd';
409 ALTER USER arthurdent@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD;
410 ALTER USER marvintheparanoidandroid@localhost IDENTIFIED BY 'abcd';
411 ALTER USER marvintheparanoidandroid@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD;
412
413 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
414 AS TWO_ADDITIONAL_PASSWORDS FROM mysql.user;
415
416 ALTER USER arthurdent@localhost IDENTIFIED BY '';
417
418 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
419 AS ONE_ADDITIONAL_PASSWORDS FROM mysql.user;
420
421 SET PASSWORD FOR marvintheparanoidandroid@localhost = '';
422
423 SELECT count(JSON_EXTRACT(mysql.user.user_attributes, '$.additional_password'))
424 AS ZERO_ADDITIONAL_PASSWORDS FROM mysql.user;
425
426 ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd';
427 --error ER_CURRENT_PASSWORD_CANNOT_BE_RETAINED
428 ALTER USER arthurdent@localhost IDENTIFIED BY '' RETAIN CURRENT PASSWORD;
429 --error ER_CURRENT_PASSWORD_CANNOT_BE_RETAINED
430 SET PASSWORD FOR arthurdent@localhost = '' RETAIN CURRENT PASSWORD;
431
432 CREATE USER userX;
433 --error ER_SECOND_PASSWORD_CANNOT_BE_EMPTY
434 SET PASSWORD FOR userX = 'abcd' RETAIN CURRENT PASSWORD;
435 DROP USER userX;
436
437 --echo #-----------------------------------------------------------------------
438 --echo # Test 16: General log
439
440 # Keep this test at the end as it modifies many user properities
441
442 --connection default
443 ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
444 ALTER USER arthurdent@localhost IDENTIFIED BY 'abcd';
445
446 # Make sure we start with a clean slate. log_tables.test says this is OK.
447 TRUNCATE TABLE mysql.general_log;
448
449 SET @old_log_output = @@global.log_output;
450 SET @old_general_log = @@global.general_log;
451 SET @old_general_log_file = @@global.general_log_file;
452
453 let $general_file_off = $MYSQLTEST_VARDIR/log/multiple_passwords_general.log;
454 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
455 eval SET GLOBAL general_log_file = '$general_file_off';
456 SET GLOBAL log_output = 'FILE,TABLE';
457 SET GLOBAL general_log= 'ON';
458
459 ALTER USER arthurdent@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD;
460 ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
461 SET PASSWORD FOR arthurdent@localhost = 'abcd' RETAIN CURRENT PASSWORD;
462 --connection arthur_conn
463 SET PASSWORD = 'efgh' REPLACE 'abcd' RETAIN CURRENT PASSWORD;
464 --connection default
465 ALTER USER arthurdent@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD,
466 marvintheparanoidandroid@localhost DISCARD OLD PASSWORD,
467 zaphodbeeblebrox@localhost IDENTIFIED BY 'abcd'
468 REQUIRE SSL WITH MAX_QUERIES_PER_HOUR 22 PASSWORD EXPIRE DEFAULT
469 PASSWORD HISTORY 10 PASSWORD REUSE INTERVAL 10 DAY
470 PASSWORD REQUIRE CURRENT OPTIONAL ACCOUNT UNLOCK;
471
472 --echo Show what is logged:
473 --echo ------ rewrite ------
474 SELECT argument FROM mysql.general_log WHERE argument LIKE 'ALTER USER %' AND
475 command_type NOT LIKE 'Prepare';
476 SELECT argument FROM mysql.general_log WHERE argument LIKE 'SET PASSWORD %';
477 --echo ------ done ------
478
479
480 --echo #-----------------------------------------------------------------------
481 --echo # Cleanup : Destroy connections, Drop users
482
483 # Destroy connections
484 --connection default
485 --disconnect arthur_conn
486 --disconnect marvin_conn
487 --disconnect zaphod_conn
488
489 # Drop test users
490 DROP USER arthurdent@localhost;
491 DROP USER marvintheparanoidandroid@localhost;
492 DROP USER zaphodbeeblebrox@localhost;
493
494 # Wait till all disconnects are completed
495 --source include/wait_until_count_sessions.inc
496 --source include/force_restart.inc
497
498 --echo #-----------------------------------------------------------------------