1
###############################################################################
3
# Authorization and authentication using the MYSQL_OLD_PASSWORD pligin at #
4
# server and client side #
8
# Creation Date: 2012-12-10 #
9
# Author : Tanjot Singh Uppal #
12
# Description:Test Cases of authenticating the hashed password using the #
13
# mysql_old_password plugin at the server side as well as the client side #
16
###############################################################################
18
--source include/not_embedded.inc
19
--source include/have_ssl.inc
20
--source include/have_sha256_rsa_auth.inc
21
--source include/mysql_upgrade_preparation.inc
23
# This test will intentionally generate errors in the server error log
24
# when a broken password is inserted into the mysql.user table.
25
# The below suppression is to clear those errors.
29
call mtr.add_suppression(".*Password salt for user.*");
34
--echo ===========================================================================================================================================
35
--echo Checking the password authentication using the mysql_old_password plugin at the client and mysql_native_password plugin at the server side
36
--echo ===========================================================================================================================================
39
select @@Global.old_passwords;
42
select @@session.old_passwords;
45
# Checking the password authentication with mysql_old_password plugin
46
# Creating 2 users and encrypting the server side password with the mysql_old_password plugin
48
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_old_password';
50
CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'mysql_old_password';
52
# Verifying the server side plugin used to be mysql_old_password
54
select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_old_password';
57
select (select plugin from mysql.user where User='Tanjotuser2' and Host='127.0.0.1')='mysql_old_password';
60
# Verifying the client connection using the same mysql_old_password plugin and NULL password
62
connect(con1,localhost,Tanjotuser1,,,);
63
connect(con2,127.0.0.1,Tanjotuser2,,,);
65
# Verifying the client connection using the same mysql_old_password plugin and junk password
67
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
68
connect(con3,localhost,Tanjotuser1," ",,);
70
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
71
connect(con4,127.0.0.1,Tanjotuser2," ",,);
73
# setting the password for the 2 users using the mysql_old_password plugin
77
SET @@session.old_passwords=1;
79
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
80
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');
82
# Validating the old_passwords value from the created users.
85
set @@global.secure_auth=0;
89
--echo **** connecting Tanjotuser1 with server
90
--exec $MYSQL -uTanjotuser1 -pabc -hlocalhost --default_auth=mysql_native_password -e "select @@session.old_passwords"
93
--echo **** connecting Tanjotuser2 with server
94
--exec $MYSQL -uTanjotuser2 -pabc -h127.0.0.1 --default_auth=mysql_native_password -e "select @@session.old_passwords"
99
--echo **** connecting Tanjotuser1 with server
100
--exec $MYSQL -uTanjotuser1 -pabc -hlocalhost --default_auth=mysql_old_password -e "select @@session.old_passwords"
103
--echo **** connecting Tanjotuser2 with server
104
--exec $MYSQL -uTanjotuser2 -pabc -h127.0.0.1 --default_auth=mysql_old_password -e "select @@session.old_passwords"
109
--echo **** connecting Tanjotuser1 with server
110
--exec $MYSQL -uTanjotuser1 -pabc -hlocalhost --default_auth=sha256_password -e "select @@session.old_passwords"
113
--echo **** connecting Tanjotuser2 with server
114
--exec $MYSQL -uTanjotuser2 -pabc -h127.0.0.1 --default_auth=sha256_password -e "select @@session.old_passwords"
119
--echo **** connecting Tanjotuser1 with server
120
--exec $MYSQL -uTanjotuser1 -pabc -hlocalhost -e "select @@session.old_passwords"
123
--echo **** connecting Tanjotuser2 with server
124
--exec $MYSQL -uTanjotuser2 -pabc -h127.0.0.1 -e "select @@session.old_passwords"
128
set @@global.secure_auth=1;
132
# Verifying the client connection using the same mysql_old_password plugin and NULL password
134
--error ER_ACCESS_DENIED_ERROR
135
connect(con5,localhost,Tanjotuser1,,,);
136
--error ER_ACCESS_DENIED_ERROR
137
connect(con6,127.0.0.1,Tanjotuser2,,,);
139
# Verifying the client connection using the same mysql_old_password plugin and junk password
141
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
142
connect(con7,localhost,Tanjotuser1," ",,);
143
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
144
connect(con8,127.0.0.1,Tanjotuser2," ",,);
146
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
147
connect(con9,localhost,Tanjotuser1,"%",,);
148
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
149
connect(con10,127.0.0.1,Tanjotuser2,"%",,);
151
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
152
connect(con11,localhost,Tanjotuser1,"_",,);
153
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
154
connect(con12,127.0.0.1,Tanjotuser2,"_",,);
156
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
157
connect(con13,localhost,Tanjotuser1,"0",,);
158
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
159
connect(con14,127.0.0.1,Tanjotuser2,"0",,);
161
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
162
connect(con15,localhost,Tanjotuser1,"^",,);
163
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
164
connect(con16,127.0.0.1,Tanjotuser2,"^",,);
166
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
167
connect(con17,localhost,Tanjotuser1,'\$',,);
168
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
169
connect(con18,127.0.0.1,Tanjotuser2,'\$',,);
171
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
172
connect(con19,localhost,Tanjotuser1,"&",,);
173
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
174
connect(con20,127.0.0.1,Tanjotuser2,"&",,);
177
# Verifying the client connection using the same mysql_old_password plugin and correct password with secure_auth enabled
181
select @@global.secure_auth;
184
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
185
connect(con21,localhost,Tanjotuser1,abc,,);
186
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
187
connect(con22,127.0.0.1,Tanjotuser2,abc,,);
189
# Verifying the client connection using the same mysql_old_password plugin and correct password with secure_auth disabled
194
set @@global.secure_auth=0;
198
connect(con23,localhost,Tanjotuser1,abc,,);
199
connect(con24,127.0.0.1,Tanjotuser2,abc,,);
202
# Below section is hashed till the bug16040690 is fixed.
204
#--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
205
#connect(con25,localhost,Tanjotuser1,"abc ",,);
206
#--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
207
#connect(con26,127.0.0.1,Tanjotuser2,"abc ",,);
209
#--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
210
#connect(con27,localhost,Tanjotuser1," abc",,);
211
#--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
212
#connect(con28,127.0.0.1,Tanjotuser2," abc",,);
214
--error ER_ACCESS_DENIED_ERROR
215
connect(con29,localhost,Tanjotuser1,"0abc",,);
216
--error ER_ACCESS_DENIED_ERROR
217
connect(con30,127.0.0.1,Tanjotuser2,"0abc",,);
219
--error ER_ACCESS_DENIED_ERROR
220
connect(con31,localhost,Tanjotuser1,"abc0",,);
221
--error ER_ACCESS_DENIED_ERROR
222
connect(con32,127.0.0.1,Tanjotuser2,"abc0",,);
224
--error ER_ACCESS_DENIED_ERROR
225
connect(con33,localhost,Tanjotuser1,"@abc",,);
226
--error ER_ACCESS_DENIED_ERROR
227
connect(con34,127.0.0.1,Tanjotuser2,"@abc",,);
229
# Dropping the created users
233
DROP USER 'Tanjotuser1'@'localhost';
234
DROP USER 'Tanjotuser2'@'127.0.0.1';
238
--echo ====================================================================================================
239
--echo checking the integrity of long password (more than 40 chars) using mysql_old_password plugin
240
--echo ====================================================================================================
244
# Checking the password authentication with mysql_old_password plugin
245
# Creating 2 users and encrypting the server side password with the mysql_old_password plugin
247
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_old_password';
249
CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'mysql_old_password';
251
# setting the password for the 2 users using the mysql_old_password plugin
253
SET @@session.old_passwords=1;
255
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('$$$$$$$$$$^^^^^^^^^^&&&&&&&&&&**********((((((((((##########!!!!!!!!!!@@@@@@@@@@ @@@@@@@@@@__________');
256
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('$$$$$$$$$$^^^^^^^^^^&&&&&&&&&&**********((((((((((##########!!!!!!!!!!@@@@@@@@@@ @@@@@@@@@@__________');
259
# Verifying the client connection using the same mysql_old_password plugin and correct password with secure_auth disabled
262
set @@global.secure_auth=0;
266
connect(con35,localhost,Tanjotuser1,"\$\$\$\$\$\$\$\$\$\$^^^^^^^^^^&&&&&&&&&&**********((((((((((##########!!!!!!!!!!@@@@@@@@@@ @@@@@@@@@@__________",,);
267
connect(con36,127.0.0.1,Tanjotuser2,"\$\$\$\$\$\$\$\$\$\$^^^^^^^^^^&&&&&&&&&&**********((((((((((##########!!!!!!!!!!@@@@@@@@@@ @@@@@@@@@@__________",,);
270
# Verifying the client access denied passing null password
272
--error ER_ACCESS_DENIED_ERROR
273
connect(con37,localhost,Tanjotuser1," ",,);
274
--error ER_ACCESS_DENIED_ERROR
275
connect(con38,127.0.0.1,Tanjotuser2," ",,);
277
--error ER_ACCESS_DENIED_ERROR
278
connect(con39,localhost,Tanjotuser1,,,);
279
--error ER_ACCESS_DENIED_ERROR
280
connect(con40,127.0.0.1,Tanjotuser2,,,);
282
--error ER_ACCESS_DENIED_ERROR
283
connect(con41,localhost,Tanjotuser1,"@_",,);
284
--error ER_ACCESS_DENIED_ERROR
285
connect(con42,127.0.0.1,Tanjotuser2,"@_",,);
287
--error ER_ACCESS_DENIED_ERROR
288
connect(con43,localhost,Tanjotuser1,"$_",,);
289
--error ER_ACCESS_DENIED_ERROR
290
connect(con44,127.0.0.1,Tanjotuser2,"$_",,);
293
# Dropping the created users
297
DROP USER 'Tanjotuser1'@'localhost';
298
DROP USER 'Tanjotuser2'@'127.0.0.1';
302
--echo ============================================================================================================================================
303
--echo Checking the password authentication using the mysql_old_password plugin at the client and mysql_old_password plugin at the server side
304
--echo ============================================================================================================================================
307
--echo # Restart server with default-authentication-plugin=mysql_old_password;
309
let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect;
310
--exec echo "wait" > $restart_file
312
--source include/wait_until_disconnected.inc
313
-- exec echo "restart:--default-authentication-plugin=mysql_old_password " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
315
-- source include/wait_until_connected_again.inc
317
# Below section is hased till bug 16014394 is fixed
319
#select @@Global.old_passwords;
322
select @@session.old_passwords;
325
# Checking the password authentication with mysql_old_password plugin
326
# Creating 2 users and encrypting the server side password with the mysql_old_password plugin
328
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_old_password';
330
CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'mysql_old_password';
332
# Verifying the server side plugin used to be mysql_old_password
334
select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_old_password';
337
select (select plugin from mysql.user where User='Tanjotuser2' and Host='127.0.0.1')='mysql_old_password';
340
# Verifying the client connection using the same mysql_old_password plugin and NULL password
342
connect(con45,localhost,Tanjotuser1,,,);
343
connect(con46,127.0.0.1,Tanjotuser2,,,);
345
# Verifying the client connection using the same mysql_old_password plugin and junk password
347
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
348
connect(con47,localhost,Tanjotuser1," ",,);
350
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
351
connect(con48,127.0.0.1,Tanjotuser2," ",,);
353
# setting the password for the 2 users using the mysql_old_password plugin
357
SET @@session.old_passwords=1;
359
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
360
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');
363
set @@global.secure_auth=0;
366
## The parts of the below section has been hashed out till the bug https://clustra.no.oracle.com/orabugs/bug.php?id=16014394 is fixed
369
# Validating the session old_passwords value from the created users.
371
#--echo **** connecting Tanjotuser1 with server
372
#--exec $MYSQL -uTanjotuser1 -pabc -hlocalhost --default_auth=mysql_native_password -e "select @@session.old_passwords"
375
#--echo **** connecting Tanjotuser2 with server
376
#--exec $MYSQL -uTanjotuser2 -pabc -h127.0.0.1 --default_auth=mysql_native_password -e "select @@session.old_passwords"
381
#--echo **** connecting Tanjotuser1 with server
382
#--exec $MYSQL -uTanjotuser1 -pabc -hlocalhost --default_auth=mysql_old_password -e "select @@session.old_passwords"
385
#--echo **** connecting Tanjotuser2 with server
386
#--exec $MYSQL -uTanjotuser2 -pabc -h127.0.0.1 --default_auth=mysql_old_password -e "select @@session.old_passwords"
391
#--echo **** connecting Tanjotuser1 with server
392
#--exec $MYSQL -uTanjotuser1 -pabc -hlocalhost --default_auth=sha256_password -e "select @@session.old_passwords"
395
#--echo **** connecting Tanjotuser2 with server
396
#--exec $MYSQL -uTanjotuser2 -pabc -h127.0.0.1 --default_auth=sha256_password -e "select @@session.old_passwords"
401
#--echo **** connecting Tanjotuser1 with server
402
#--exec $MYSQL -uTanjotuser1 -pabc -hlocalhost -e "select @@session.old_passwords"
405
#--echo **** connecting Tanjotuser2 with server
406
#--exec $MYSQL -uTanjotuser2 -pabc -h127.0.0.1 -e "select @@session.old_passwords"
411
set @@global.secure_auth=1;
415
# Verifying the client connection using the same mysql_old_password plugin and NULL password
417
--error ER_ACCESS_DENIED_ERROR
418
connect(con49,localhost,Tanjotuser1,,,);
419
--error ER_ACCESS_DENIED_ERROR
420
connect(con50,127.0.0.1,Tanjotuser2,,,);
422
# Verifying the client connection using the same mysql_old_password plugin and junk password
424
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
425
connect(con51,localhost,Tanjotuser1," ",,);
426
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
427
connect(con52,127.0.0.1,Tanjotuser2," ",,);
429
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
430
connect(con53,localhost,Tanjotuser1,"%",,);
431
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
432
connect(con54,127.0.0.1,Tanjotuser2,"%",,);
434
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
435
connect(con55,localhost,Tanjotuser1,"_",,);
436
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
437
connect(con56,127.0.0.1,Tanjotuser2,"_",,);
439
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
440
connect(con57,localhost,Tanjotuser1,"0",,);
441
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
442
connect(con58,127.0.0.1,Tanjotuser2,"0",,);
444
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
445
connect(con59,localhost,Tanjotuser1,"^",,);
446
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
447
connect(con60,127.0.0.1,Tanjotuser2,"^",,);
449
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
450
connect(con61,localhost,Tanjotuser1,"\$",,);
451
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
452
connect(con62,127.0.0.1,Tanjotuser2,"\$",,);
454
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
455
connect(con63,localhost,Tanjotuser1,"&",,);
456
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
457
connect(con64,127.0.0.1,Tanjotuser2,"&",,);
460
# Verifying the client connection using the same mysql_old_password plugin and correct password with secure_auth enabled
464
select @@global.secure_auth;
467
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
468
connect(con65,localhost,Tanjotuser1,abc,,);
469
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
470
connect(con66,127.0.0.1,Tanjotuser2,abc,,);
472
# Verifying the client connection using the same mysql_old_password plugin and correct password with secure_auth disabled
477
set @@global.secure_auth=0;
481
connect(con67,localhost,Tanjotuser1,abc,,);
482
connect(con68,127.0.0.1,Tanjotuser2,abc,,);
485
# Below section is hashed till the bug16040690 is fixed.
487
#--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
488
#connect(con69,localhost,Tanjotuser1,"abc ",,);
489
#--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
490
#connect(con70,127.0.0.1,Tanjotuser2,"abc ",,);
492
#--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
493
#connect(con71,localhost,Tanjotuser1," abc",,);
494
#--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
495
#connect(con72,127.0.0.1,Tanjotuser2," abc",,);
497
--error ER_ACCESS_DENIED_ERROR
498
connect(con73,localhost,Tanjotuser1,"0abc",,);
499
--error ER_ACCESS_DENIED_ERROR
500
connect(con74,127.0.0.1,Tanjotuser2,"0abc",,);
502
--error ER_ACCESS_DENIED_ERROR
503
connect(con75,localhost,Tanjotuser1,"abc0",,);
504
--error ER_ACCESS_DENIED_ERROR
505
connect(con76,127.0.0.1,Tanjotuser2,"abc0",,);
507
--error ER_ACCESS_DENIED_ERROR
508
connect(con77,localhost,Tanjotuser1,"@abc",,);
509
--error ER_ACCESS_DENIED_ERROR
510
connect(con78,127.0.0.1,Tanjotuser2,"@abc",,);
512
# Dropping the created users
516
DROP USER 'Tanjotuser1'@'localhost';
517
DROP USER 'Tanjotuser2'@'127.0.0.1';
521
--echo ====================================================================================================
522
--echo checking the integrity of long password (more than 40 chars) using mysql_old_password plugin
523
--echo ====================================================================================================
527
# Checking the password authentication with mysql_old_password plugin
528
# Creating 2 users and encrypting the server side password with the mysql_old_password plugin
530
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_old_password';
532
CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'mysql_old_password';
534
# setting the password for the 2 users using the mysql_old_password plugin
536
SET @@session.old_passwords=1;
538
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('$$$$$$$$$$^^^^^^^^^^&&&&&&&&&&**********((((((((((##########!!!!!!!!!!@@@@@@@@@@ @@@@@@@@@@__________');
539
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('$$$$$$$$$$^^^^^^^^^^&&&&&&&&&&**********((((((((((##########!!!!!!!!!!@@@@@@@@@@ @@@@@@@@@@__________');
542
# Verifying the client connection using the same mysql_old_password plugin and correct password with secure_auth disabled
545
set @@global.secure_auth=0;
549
connect(con79,localhost,Tanjotuser1,"\$\$\$\$\$\$\$\$\$\$^^^^^^^^^^&&&&&&&&&&**********((((((((((##########!!!!!!!!!!@@@@@@@@@@ @@@@@@@@@@__________",,);
550
connect(con80,127.0.0.1,Tanjotuser2,"\$\$\$\$\$\$\$\$\$\$^^^^^^^^^^&&&&&&&&&&**********((((((((((##########!!!!!!!!!!@@@@@@@@@@ @@@@@@@@@@__________",,);
553
# Verifying the client access denied passing null password
555
--error ER_ACCESS_DENIED_ERROR
556
connect(con81,localhost,Tanjotuser1," ",,);
557
--error ER_ACCESS_DENIED_ERROR
558
connect(con82,127.0.0.1,Tanjotuser2," ",,);
560
--error ER_ACCESS_DENIED_ERROR
561
connect(con83,localhost,Tanjotuser1,,,);
562
--error ER_ACCESS_DENIED_ERROR
563
connect(con84,127.0.0.1,Tanjotuser2,,,);
565
--error ER_ACCESS_DENIED_ERROR
566
connect(con85,localhost,Tanjotuser1,"@_",,);
567
--error ER_ACCESS_DENIED_ERROR
568
connect(con86,127.0.0.1,Tanjotuser2,"@_",,);
570
--error ER_ACCESS_DENIED_ERROR
571
connect(con87,localhost,Tanjotuser1,"$_",,);
572
--error ER_ACCESS_DENIED_ERROR
573
connect(con88,127.0.0.1,Tanjotuser2,"$_",,);
576
# Dropping the created users
580
DROP USER 'Tanjotuser1'@'localhost';
581
DROP USER 'Tanjotuser2'@'127.0.0.1';
586
--echo ============================================================================================================================================
587
--echo Checking the password authentication using the mysql_old_password plugin at the client and sha256_password plugin at the server side
588
--echo ============================================================================================================================================
591
--echo # Restart server with default-authentication-plugin=sha256_password;
593
let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect;
594
--exec echo "wait" > $restart_file
596
--source include/wait_until_disconnected.inc
597
-- exec echo "restart:--default-authentication-plugin=sha256_password " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
599
-- source include/wait_until_connected_again.inc
603
select @@Global.old_passwords;
606
select @@session.old_passwords;
609
# Checking the password authentication with mysql_old_password plugin
610
# Creating 2 users and encrypting the server side password with the mysql_old_password plugin
612
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_old_password';
614
CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'mysql_old_password';
616
# Verifying the server side plugin used to be mysql_old_password
618
select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_old_password';
621
select (select plugin from mysql.user where User='Tanjotuser2' and Host='127.0.0.1')='mysql_old_password';
624
# Verifying the client connection using the same mysql_old_password plugin and NULL password
626
connect(con89,localhost,Tanjotuser1,,,);
627
connect(con90,127.0.0.1,Tanjotuser2,,,);
629
# Verifying the client connection using the same mysql_old_password plugin and junk password
631
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
632
connect(con91,localhost,Tanjotuser1," ",,);
634
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
635
connect(con92,127.0.0.1,Tanjotuser2," ",,);
637
# setting the password for the 2 users using the mysql_old_password plugin
641
SET @@session.old_passwords=1;
643
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
644
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');
647
# Validating the old_passwords value from the created users.
650
set @@global.secure_auth=0;
654
--echo **** connecting Tanjotuser1 with server
655
--exec $MYSQL -uTanjotuser1 -pabc -hlocalhost --default_auth=mysql_native_password -e "select @@session.old_passwords"
658
--echo **** connecting Tanjotuser2 with server
659
--exec $MYSQL -uTanjotuser2 -pabc -h127.0.0.1 --default_auth=mysql_native_password -e "select @@session.old_passwords"
664
--echo **** connecting Tanjotuser1 with server
665
--exec $MYSQL -uTanjotuser1 -pabc -hlocalhost --default_auth=mysql_old_password -e "select @@session.old_passwords"
668
--echo **** connecting Tanjotuser2 with server
669
--exec $MYSQL -uTanjotuser2 -pabc -h127.0.0.1 --default_auth=mysql_old_password -e "select @@session.old_passwords"
674
--echo **** connecting Tanjotuser1 with server
675
--exec $MYSQL -uTanjotuser1 -pabc -hlocalhost --default_auth=sha256_password -e "select @@session.old_passwords"
678
--echo **** connecting Tanjotuser2 with server
679
--exec $MYSQL -uTanjotuser2 -pabc -h127.0.0.1 --default_auth=sha256_password -e "select @@session.old_passwords"
684
--echo **** connecting Tanjotuser1 with server
685
--exec $MYSQL -uTanjotuser1 -pabc -hlocalhost -e "select @@session.old_passwords"
688
--echo **** connecting Tanjotuser2 with server
689
--exec $MYSQL -uTanjotuser2 -pabc -h127.0.0.1 -e "select @@session.old_passwords"
693
set @@global.secure_auth=1;
697
# Verifying the client connection using the same mysql_old_password plugin and NULL password
699
--error ER_ACCESS_DENIED_ERROR
700
connect(con93,localhost,Tanjotuser1,,,);
701
--error ER_ACCESS_DENIED_ERROR
702
connect(con94,127.0.0.1,Tanjotuser2,,,);
704
# Verifying the client connection using the same mysql_old_password plugin and junk password
706
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
707
connect(con95,localhost,Tanjotuser1," ",,);
708
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
709
connect(con96,127.0.0.1,Tanjotuser2," ",,);
711
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
712
connect(con97,localhost,Tanjotuser1,"%",,);
713
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
714
connect(con98,127.0.0.1,Tanjotuser2,"%",,);
716
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
717
connect(con99,localhost,Tanjotuser1,"_",,);
718
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
719
connect(con100,127.0.0.1,Tanjotuser2,"_",,);
721
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
722
connect(con101,localhost,Tanjotuser1,"0",,);
723
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
724
connect(con102,127.0.0.1,Tanjotuser2,"0",,);
726
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
727
connect(con103,localhost,Tanjotuser1,"^",,);
728
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
729
connect(con104,127.0.0.1,Tanjotuser2,"^",,);
731
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
732
connect(con105,localhost,Tanjotuser1,"\$",,);
733
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
734
connect(con106,127.0.0.1,Tanjotuser2,"\$",,);
736
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
737
connect(con107,localhost,Tanjotuser1,"&",,);
738
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
739
connect(con108,127.0.0.1,Tanjotuser2,"&",,);
742
# Verifying the client connection using the same mysql_old_password plugin and correct password with secure_auth enabled
746
select @@global.secure_auth;
749
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
750
connect(con109,localhost,Tanjotuser1,abc,,);
751
--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
752
connect(con110,127.0.0.1,Tanjotuser2,abc,,);
754
# Verifying the client connection using the same mysql_old_password plugin and correct password with secure_auth disabled
759
set @@global.secure_auth=0;
763
connect(con111,localhost,Tanjotuser1,abc,,);
764
connect(con112,127.0.0.1,Tanjotuser2,abc,,);
767
# Below section is hashed till the bug16040690 is fixed.
769
#--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
770
#connect(con113,localhost,Tanjotuser1,"abc ",,);
771
#--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
772
#connect(con114,127.0.0.1,Tanjotuser2,"abc ",,);
774
#--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
775
#connect(con115,localhost,Tanjotuser1," abc",,);
776
#--error ER_SERVER_IS_IN_SECURE_AUTH_MODE
777
#connect(con116,127.0.0.1,Tanjotuser2," abc",,);
779
--error ER_ACCESS_DENIED_ERROR
780
connect(con117,localhost,Tanjotuser1,"0abc",,);
781
--error ER_ACCESS_DENIED_ERROR
782
connect(con118,127.0.0.1,Tanjotuser2,"0abc",,);
784
--error ER_ACCESS_DENIED_ERROR
785
connect(con119,localhost,Tanjotuser1,"abc0",,);
786
--error ER_ACCESS_DENIED_ERROR
787
connect(con120,127.0.0.1,Tanjotuser2,"abc0",,);
789
--error ER_ACCESS_DENIED_ERROR
790
connect(con121,localhost,Tanjotuser1,"@abc",,);
791
--error ER_ACCESS_DENIED_ERROR
792
connect(con122,127.0.0.1,Tanjotuser2,"@abc",,);
794
# Dropping the created users
798
DROP USER 'Tanjotuser1'@'localhost';
799
DROP USER 'Tanjotuser2'@'127.0.0.1';
803
--echo ====================================================================================================
804
--echo checking the integrity of long password (more than 40 chars) using mysql_old_password plugin
805
--echo ====================================================================================================
809
# Checking the password authentication with mysql_old_password plugin
810
# Creating 2 users and encrypting the server side password with the mysql_old_password plugin
812
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_old_password';
814
CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'mysql_old_password';
816
# setting the password for the 2 users using the mysql_old_password plugin
818
SET @@session.old_passwords=1;
820
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('$$$$$$$$$$^^^^^^^^^^&&&&&&&&&&**********((((((((((##########!!!!!!!!!!@@@@@@@@@@ @@@@@@@@@@__________');
821
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('$$$$$$$$$$^^^^^^^^^^&&&&&&&&&&**********((((((((((##########!!!!!!!!!!@@@@@@@@@@ @@@@@@@@@@__________');
824
# Verifying the client connection using the same mysql_old_password plugin and correct password with secure_auth disabled
827
set @@global.secure_auth=0;
831
connect(con123,localhost,Tanjotuser1,"\$\$\$\$\$\$\$\$\$\$^^^^^^^^^^&&&&&&&&&&**********((((((((((##########!!!!!!!!!!@@@@@@@@@@ @@@@@@@@@@__________",,);
832
connect(con124,127.0.0.1,Tanjotuser2,"\$\$\$\$\$\$\$\$\$\$^^^^^^^^^^&&&&&&&&&&**********((((((((((##########!!!!!!!!!!@@@@@@@@@@ @@@@@@@@@@__________",,);
835
# Verifying the client access denied passing null password
837
--error ER_ACCESS_DENIED_ERROR
838
connect(con125,localhost,Tanjotuser1," ",,);
839
--error ER_ACCESS_DENIED_ERROR
840
connect(con126,127.0.0.1,Tanjotuser2," ",,);
842
--error ER_ACCESS_DENIED_ERROR
843
connect(con127,localhost,Tanjotuser1,,,);
844
--error ER_ACCESS_DENIED_ERROR
845
connect(con128,127.0.0.1,Tanjotuser2,,,);
847
--error ER_ACCESS_DENIED_ERROR
848
connect(con129,localhost,Tanjotuser1,"@_",,);
849
--error ER_ACCESS_DENIED_ERROR
850
connect(con130,127.0.0.1,Tanjotuser2,"@_",,);
852
--error ER_ACCESS_DENIED_ERROR
853
connect(con131,localhost,Tanjotuser1,"$_",,);
854
--error ER_ACCESS_DENIED_ERROR
855
connect(con132,127.0.0.1,Tanjotuser2,"$_",,);
858
# Dropping the created users
996
DROP USER 'Tanjotuser1'@'localhost';
997
DROP USER 'Tanjotuser2'@'127.0.0.1';