Debian Codegraph @

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

raw
1
2
3 =======================================================================================
4 Checking the password expiry with the users created with all 3 plugable authentication
5 =======================================================================================
6
7 set @default_plugin= @@global.default_authentication_plugin;
8 Warnings:
9 Warning 1287 '@@default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
10 Creating a user with respect to all the 2 password authentication plugin
11 **** Creating user with @default_plugin plugin
12 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
13 **** Creating user with sha256_password plugin
14 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
15 SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
16 **** Validating the plugin names
17 select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')=@default_plugin;
18 (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')=@default_plugin
19 1
20 1 Expected
21 select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
22 (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password'
23 1
24 1 Expected
25 **** Validating the password expiry flag in the mysql.user table
26 select (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='N';
27 (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='N'
28 1
29 1 Expected
30 select (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='N';
31 (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='N'
32 1
33 1 Expected
34 **** Expirying password from root login for the 2 created users
35 Alter user 'Tanjotuser1'@'localhost' password expire;
36 ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
37 select 1;
38 1
39 1
40 1 Expected
41 Alter user 'Tanjotuser3'@'localhost' password expire;
42 ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
43 select 1;
44 1
45 1
46 1 Expected
47 **** Validating the password expiry flag in the mysql.user table should not be altered
48 select (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='N';
49 (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='N'
50 1
51 1 Expected
52 select (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='N';
53 (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='N'
54 1
55 1 Expected
56 **** These 2 users still should be able to login and work properly
57 select 1;
58 1
59 1
60 1 Expected
61 select 1;
62 1
63 1
64 1 Expected
65 **** Expirying password from root login for the 2 created users using Alter user
66 Alter user 'Tanjotuser1'@'localhost' password expire;
67 Alter user 'Tanjotuser3'@'localhost' password expire;
68 **** Validating the password expiry flag in the mysql.user table
69 select (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='Y';
70 (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='Y'
71 1
72 1 Expected
73 select (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='Y';
74 (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='Y'
75 1
76 1 Expected
77 **** checking user access after password expiry
78 select 1;
79 ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
80 select 1;
81 ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
82 At the same time the open sessions for these users should able to work properly.
83 select 1;
84 1
85 1
86 1 Expected
87 select 1;
88 1
89 1
90 1 Expected
91 setting passwords from the new connections.
92 select 1;
93 ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
94 set password='abcd';
95 select 1;
96 1
97 1
98 1 Expected
99 select (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='N';
100 (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='N'
101 1
102 1 Expected
103 select 1;
104 ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
105 set password='abcd';
106 select 1;
107 1
108 1
109 1 Expected
110 select (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='N';
111 (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='N'
112 1
113 1 Expected
114 **** logging the 2 users with the new passwords
115 select 1;
116 1
117 1
118 1 Expected
119 select 1;
120 1
121 1
122 1 Expected
123 Disconnecting the open sessions and dropping the created users
124 drop user 'Tanjotuser1'@'localhost';
125 drop user 'Tanjotuser3'@'localhost';
126
127
128 =======================================================================================
129 Checking the password expiry using the update command on mysql.user table
130 =======================================================================================
131
132 Creating a user with respect to all the 2 password authentication plugin
133 **** Creating user with @default_plugin plugin
134 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
135 **** Creating user with sha256_password plugin
136 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
137 SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
138 **** Validating the plugin names
139 select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')=@default_plugin;
140 (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')=@default_plugin
141 1
142 1 Expected
143 select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
144 (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password'
145 1
146 1 Expected
147 **** Validating the password expiry flag in the mysql.user table
148 select (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='N';
149 (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='N'
150 1
151 1 Expected
152 select (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='N';
153 (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='N'
154 1
155 1 Expected
156 **** Making connections from each of these created users
157 select 1;
158 1
159 1
160 1 Expected
161 select 1;
162 1
163 1
164 1 Expected
165 **** Expirying password from root login for the 2 created users using update command
166 update mysql.user set password_expired='Y' where User='Tanjotuser1' and Host='localhost';
167 update mysql.user set password_expired='Y' where User='Tanjotuser3' and Host='localhost';
168 **** connecting client before flush privileges
169 select 1;
170 1
171 1
172 1 Expected
173 select 1;
174 1
175 1
176 1 Expected
177 **** flush privileges
178 flush privileges;
179 **** connecting client after flush privileges
180 select 1;
181 ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
182 select 1;
183 ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
184 **** checking the previous open connections
185 select 1;
186 1
187 1
188 1 Expected
189 select 1;
190 1
191 1
192 1 Expected
193 **** Resetting the password
194 select 1;
195 1
196 1
197 1 Expected
198 set password='abcd';
199 select 1;
200 1
201 1
202 1 Expected
203 select (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='N';
204 (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='N'
205 1
206 1 Expected
207 select 1;
208 1
209 1
210 1 Expected
211 set password='abcd';
212 select 1;
213 1
214 1
215 1 Expected
216 select (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='N';
217 (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='N'
218 1
219 1 Expected
220 **** Logging with the new password
221 select 1;
222 1
223 1
224 1 Expected
225 select 1;
226 1
227 1
228 1 Expected
229 **** connecting client after resetting the password
230 Disconnecting the open sessions and dropping the created users
231 drop user 'Tanjotuser1'@'localhost';
232 drop user 'Tanjotuser3'@'localhost';
233
234
235 =================================================================================================
236 Starting the server with the default authentication sha256_password
237 =================================================================================================
238
239 # Restart server with default-authentication-plugin=sha256_password;
240
241
242 =======================================================================================
243 Checking the password expiry with the users created with all 2 plugable authentication
244 =======================================================================================
245
246 Creating a user with respect to all the 2 password authentication plugin
247 **** Creating user with mysql_native_password plugin
248 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
249 SET PASSWORD for 'Tanjotuser1'@'localhost' = 'abc';
250 **** Creating user with sha256_password plugin
251 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
252 SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
253 **** Validating the plugin names
254 select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password';
255 (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password'
256 1
257 1 Expected
258 select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
259 (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password'
260 1
261 1 Expected
262 **** Validating the password expiry flag in the mysql.user table
263 select (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='N';
264 (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='N'
265 1
266 1 Expected
267 select (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='N';
268 (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='N'
269 1
270 1 Expected
271 **** Expirying password from root login for the 2 created users
272 Alter user 'Tanjotuser1'@'localhost' password expire;
273 ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
274 select 1;
275 1
276 1
277 1 Expected
278 Alter user 'Tanjotuser3'@'localhost' password expire;
279 ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
280 select 1;
281 1
282 1
283 1 Expected
284 **** Validating the password expiry flag in the mysql.user table should not be altered
285 select (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='N';
286 (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='N'
287 1
288 1 Expected
289 select (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='N';
290 (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='N'
291 1
292 1 Expected
293 **** These 2 users still should be able to login and work properly
294 select 1;
295 1
296 1
297 1 Expected
298 select 1;
299 1
300 1
301 1 Expected
302 **** Expirying password from root login for the 2 created users using Alter user
303 Alter user 'Tanjotuser1'@'localhost' password expire;
304 Alter user 'Tanjotuser3'@'localhost' password expire;
305 **** Validating the password expiry flag in the mysql.user table
306 select (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='Y';
307 (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='Y'
308 1
309 1 Expected
310 select (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='Y';
311 (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='Y'
312 1
313 1 Expected
314 **** checking user access after password expiry
315 select 1;
316 ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
317 select 1;
318 ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
319 At the same time the open sessions for these users should able to work properly.
320 select 1;
321 1
322 1
323 1 Expected
324 select 1;
325 1
326 1
327 1 Expected
328 setting passwords from the new connections.
329 select 1;
330 ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
331 set password='abcd';
332 select 1;
333 1
334 1
335 1 Expected
336 select (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='N';
337 (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='N'
338 1
339 1 Expected
340 select 1;
341 ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
342 set password='abcd';
343 select 1;
344 1
345 1
346 1 Expected
347 select (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='N';
348 (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='N'
349 1
350 1 Expected
351 **** logging the 2 users with the new passwords
352 select 1;
353 1
354 1
355 1 Expected
356 select 1;
357 1
358 1
359 1 Expected
360 Disconnecting the open sessions and dropping the created users
361 drop user 'Tanjotuser1'@'localhost';
362 drop user 'Tanjotuser3'@'localhost';
363
364
365 =======================================================================================
366 Checking the password expiry using the update command on mysql.user table
367 =======================================================================================
368
369 Creating a user with respect to all the 2 password authentication plugin
370 **** Creating user with mysql_native_password plugin
371 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
372 SET PASSWORD for 'Tanjotuser1'@'localhost' = 'abc';
373 **** Creating user with sha256_password plugin
374 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
375 SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
376 **** Validating the plugin names
377 select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password';
378 (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password'
379 1
380 1 Expected
381 select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
382 (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password'
383 1
384 1 Expected
385 **** Validating the password expiry flag in the mysql.user table
386 select (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='N';
387 (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='N'
388 1
389 1 Expected
390 select (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='N';
391 (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='N'
392 1
393 1 Expected
394 **** Making connections from each of these created users
395 select 1;
396 1
397 1
398 1 Expected
399 select 1;
400 1
401 1
402 1 Expected
403 **** Expirying password from root login for the 2 created users using update command
404 update mysql.user set password_expired='Y' where User='Tanjotuser1' and Host='localhost';
405 update mysql.user set password_expired='Y' where User='Tanjotuser3' and Host='localhost';
406 **** connecting client before flush privileges
407 select 1;
408 1
409 1
410 1 Expected
411 select 1;
412 1
413 1
414 1 Expected
415 **** flush privileges
416 flush privileges;
417 **** connecting client after flush privileges
418 select 1;
419 ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
420 select 1;
421 ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
422 **** checking the previous open connections
423 select 1;
424 1
425 1
426 1 Expected
427 select 1;
428 1
429 1
430 1 Expected
431 **** Resetting the password
432 select 1;
433 1
434 1
435 1 Expected
436 set password='abcd';
437 select 1;
438 1
439 1
440 1 Expected
441 select (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='N';
442 (select password_expired from mysql.user where User='Tanjotuser1' and Host='localhost')='N'
443 1
444 1 Expected
445 select 1;
446 1
447 1
448 1 Expected
449 set password='abcd';
450 select 1;
451 1
452 1
453 1 Expected
454 select (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='N';
455 (select password_expired from mysql.user where User='Tanjotuser3' and Host='localhost')='N'
456 1
457 1 Expected
458 **** Logging with the new password
459 select 1;
460 1
461 1
462 1 Expected
463 select 1;
464 1
465 1
466 1 Expected
467 **** connecting client after resetting the password
468 #
469 # WL#2284: Increase the length of a user name
470 #
471 CREATE USER user_name_len_25_01234567@localhost IDENTIFIED BY 'password' PASSWORD EXPIRE;
472 SELECT 1;
473 ERROR HY000: You must reset your password using ALTER USER statement before executing this statement.
474 SET PASSWORD FOR user_name_len_25_01234567@localhost = 'abc';
475 SELECT 1;
476 1
477 1
478 Disconnecting the open sessions and dropping the created users
479 DROP USER user_name_len_25_01234567@localhost;
480 drop user 'Tanjotuser1'@'localhost';
481 drop user 'Tanjotuser3'@'localhost';
482 # restart: