1############################################################################### 2# # 3# Authorization and authentication of the key-value pair shared between # 4# the server and the client # 5# # 6# # 7# Creation Date: 2012-12-1 # 8# Author : Tanjot Singh Uppal # 9# # 10# # 11# Description:Test Cases of authenticating the key value pair shared # 12# between the client and server during the handshake # 13# # 14############################################################################### 15 16--source include/not_embedded.inc 17--source include/have_ssl.inc 18--source include/have_sha256_rsa_auth.inc 19--source include/mysql_upgrade_preparation.inc 20 21# This test will intentionally generate errors in the server error log 22# when a broken password is inserted into the mysql.user table. 23# The below suppression is to clear those errors. 24--disable_query_log 25call mtr.add_suppression(".*Password salt for user.*"); 26--enable_query_log 27 28--echo 29--echo 30--echo ======================================================================= 31--echo Checking the password authentication with mysql_native_password plugin 32--echo ======================================================================= 33--echo 34 35# Checking the password authentication with mysql_native_password plugin 36# Creating 2 users and encrypting the server side password with the default mysql_native_password plugin 37 38CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc'; 39 40CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED BY 'abc'; 41 42# Verifying the default server side plugin used to be mysql_native_password 43 44select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password'; 45--echo 1 Expected 46 47select (select plugin from mysql.user where User='Tanjotuser2' and Host='127.0.0.1')='mysql_native_password'; 48--echo 1 Expected 49 50# Verifying the encrypted password string using the password function 51 52SET @@global.old_passwords=0; 53 54SET @@session.old_passwords=0; 55 56select (select authentication_string from mysql.user where User='Tanjotuser1' and Host='localhost')=password('abc'); 57--echo 1 Expected 58 59select (select authentication_string from mysql.user where User='Tanjotuser2' and Host='127.0.0.1')=password('abc'); 60--echo 1 Expected 61 62# Verifying the client connection using the same mysql_native_password 63 64connect(con1,localhost,Tanjotuser1,abc,,); 65connect(con2,127.0.0.1,Tanjotuser2,abc,,); 66 67 68# Verifying the client access denied passing null password 69 70--replace_result $MASTER_MYSOCK MASTER_MYSOCK 71--disable_query_log 72--error ER_ACCESS_DENIED_ERROR 73connect(con3,localhost,Tanjotuser1,,,); 74--enable_query_log 75 76--replace_result $MASTER_MYSOCK MASTER_MYSOCK 77--disable_query_log 78--error ER_ACCESS_DENIED_ERROR 79connect(con4,127.0.0.1,Tanjotuser2,,,); 80--enable_query_log 81 82# Verifying the client access denied passing blank password 83 84--replace_result $MASTER_MYSOCK MASTER_MYSOCK 85--disable_query_log 86--error ER_ACCESS_DENIED_ERROR 87connect(con5,localhost,Tanjotuser1, ,,); 88--enable_query_log 89 90--replace_result $MASTER_MYSOCK MASTER_MYSOCK 91--disable_query_log 92--error ER_ACCESS_DENIED_ERROR 93connect(con6,127.0.0.1,Tanjotuser2, ,,); 94--enable_query_log 95 96# Dropping the created users 97 98connection default; 99 100DROP USER 'Tanjotuser1'@'localhost'; 101DROP USER 'Tanjotuser2'@'127.0.0.1'; 102 103--echo 104--echo 105--echo ======================================================================= 106--echo Checking the password authentication with sha256_password plugin 107--echo ======================================================================= 108--echo 109 110# Checking the password authentication with sha256_password plugin 111# Creating 2 users and encrypting the server side password with the sha256_password plugin 112 113 114CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password'; 115 116CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password'; 117 118# Verifying the server side plugin used to be sha256_password 119 120select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='sha256_password'; 121--echo 1 Expected 122 123select (select plugin from mysql.user where User='Tanjotuser2' and Host='127.0.0.1')='sha256_password'; 124--echo 1 Expected 125 126# Verifying the client connection using the same sha256_password plugin and NULL password 127 128 129connect(con19,localhost,Tanjotuser1,,,); 130connect(con20,127.0.0.1,Tanjotuser2,,,); 131 132# Verifying the client connection using the same sha256_password plugin and junk password 133 134--replace_result $MASTER_MYSOCK MASTER_MYSOCK 135--disable_query_log 136--error ER_ACCESS_DENIED_ERROR 137connect(con21,localhost,Tanjotuser1," ",,); 138--enable_query_log 139 140--replace_result $MASTER_MYSOCK MASTER_MYSOCK 141--disable_query_log 142--error ER_ACCESS_DENIED_ERROR 143connect(con22,127.0.0.1,Tanjotuser2," ",,); 144--enable_query_log 145 146# setting the password for the 2 users using the sha256_password plugin 147 148connection default; 149 150SET @@global.old_passwords=2; 151 152SET @@session.old_passwords=2; 153 154SET PASSWORD FOR 'Tanjotuser1'@'localhost'='abc'; 155SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'='abc'; 156 157# Verifying the client connection using the same sha256_password plugin and NULL password 158 159--replace_result $MASTER_MYSOCK MASTER_MYSOCK 160--disable_query_log 161--error ER_ACCESS_DENIED_ERROR 162connect(con23,localhost,Tanjotuser1,,,); 163--enable_query_log 164 165--replace_result $MASTER_MYSOCK MASTER_MYSOCK 166--disable_query_log 167--error ER_ACCESS_DENIED_ERROR 168connect(con24,127.0.0.1,Tanjotuser2,,,); 169--enable_query_log 170 171# Verifying the client connection using the same sha256_password plugin and junk password 172 173--replace_result $MASTER_MYSOCK MASTER_MYSOCK 174--disable_query_log 175--error ER_ACCESS_DENIED_ERROR 176connect(con25,localhost,Tanjotuser1," ",,); 177--enable_query_log 178 179--replace_result $MASTER_MYSOCK MASTER_MYSOCK 180--disable_query_log 181--error ER_ACCESS_DENIED_ERROR 182connect(con26,127.0.0.1,Tanjotuser2," ",,); 183--enable_query_log 184 185# Verifying the client connection using the same sha256_password plugin and correct password 186 187--disable_warnings 188connect(con27,localhost,Tanjotuser1,abc,,); 189connect(con28,127.0.0.1,Tanjotuser2,abc,,); 190--enable_warnings 191 192# Dropping the created users 193 194connection default; 195 196DROP USER 'Tanjotuser1'@'localhost'; 197DROP USER 'Tanjotuser2'@'127.0.0.1'; 198 199# Disconnecting the above connections 200 201--disable_query_log 202 203disconnect con1; 204disconnect con2; 205#disconnect con3; 206#disconnect con4; 207#disconnect con5; 208#disconnect con6; 209disconnect con19; 210disconnect con20; 211#disconnect con21; 212#disconnect con22; 213#disconnect con23; 214#disconnect con24; 215#disconnect con25; 216#disconnect con26; 217disconnect con27; 218disconnect con28; 219 220--enable_query_log 221 222 223--echo 224--echo 225--echo ==================================================================================================== 226--echo checking the integrity of long password (more than 40 chars) using mysql_native_password plugin 227--echo ==================================================================================================== 228--echo 229 230# Checking the password authentication with mysql_native_password plugin 231# Creating 2 users and encrypting the server side password with the default mysql_native_password plugin 232 233CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222'; 234 235CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED BY 'aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222'; 236 237# Verifying the client connection using the same mysql_native_password 238 239--disable_warnings 240connect(con29,localhost,Tanjotuser1,aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222,,); 241connect(con30,127.0.0.1,Tanjotuser2,aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222,,); 242--enable_warnings 243 244# Verifying the client access denied passing null password 245 246--replace_result $MASTER_MYSOCK MASTER_MYSOCK 247--disable_query_log 248--error ER_ACCESS_DENIED_ERROR 249connect(con31,localhost,Tanjotuser1,,,); 250--enable_query_log 251 252--replace_result $MASTER_MYSOCK MASTER_MYSOCK 253--disable_query_log 254--error ER_ACCESS_DENIED_ERROR 255connect(con32,127.0.0.1,Tanjotuser2,,,); 256--enable_query_log 257 258 259# Dropping the created users 260 261connection default; 262 263DROP USER 'Tanjotuser1'@'localhost'; 264DROP USER 'Tanjotuser2'@'127.0.0.1'; 265 266--echo 267--echo 268--echo ==================================================================================================== 269--echo checking the integrity of long password (more than 40 chars) using sha256_password plugin 270--echo ==================================================================================================== 271--echo 272 273# Checking the password authentication with sha256_password plugin 274# Creating 2 users and encrypting the server side password with the sha256_password plugin 275 276 277CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password'; 278 279CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password'; 280 281 282# setting the password for the 2 users using the sha256_password plugin 283 284SET @@global.old_passwords=2; 285 286SET @@session.old_passwords=2; 287 288SET PASSWORD FOR 'Tanjotuser1'@'localhost'='aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222'; 289SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'='aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222'; 290 291 292# Verifying the client connection using the same sha256_password plugin and correct password 293 294--disable_warnings 295connect(con37,localhost,Tanjotuser1,aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222,,); 296connect(con38,127.0.0.1,Tanjotuser2,aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222,,); 297--enable_warnings 298 299# Verifying the client connection using the same sha256_password plugin and NULL password 300 301--replace_result $MASTER_MYSOCK MASTER_MYSOCK 302--disable_query_log 303--error ER_ACCESS_DENIED_ERROR 304connect(con39,localhost,Tanjotuser1,,,); 305--enable_query_log 306 307--replace_result $MASTER_MYSOCK MASTER_MYSOCK 308--disable_query_log 309--error ER_ACCESS_DENIED_ERROR 310connect(con40,127.0.0.1,Tanjotuser2,,,); 311--enable_query_log 312 313# Dropping the created users 314 315connection default; 316 317DROP USER 'Tanjotuser1'@'localhost'; 318DROP USER 'Tanjotuser2'@'127.0.0.1'; 319 320 321--echo 322--echo 323--echo ==================================================================================================== 324--echo checking the integrity of NULL password using mysql_native_password plugin 325--echo ==================================================================================================== 326--echo 327 328# Checking the password authentication with mysql_native_password plugin 329# Creating 2 users and encrypting the server side password with the default mysql_native_password plugin 330 331CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY ''; 332 333CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED BY ''; 334 335# Verifying the client connection using the null password 336 337connect(con41,localhost,Tanjotuser1,,,); 338connect(con42,127.0.0.1,Tanjotuser2,,,); 339 340# Verifying the client access denied passing not null password 341 342--replace_result $MASTER_MYSOCK MASTER_MYSOCK 343--disable_query_log 344--error ER_ACCESS_DENIED_ERROR 345connect(con43,localhost,Tanjotuser1," ",,); 346--enable_query_log 347 348--replace_result $MASTER_MYSOCK MASTER_MYSOCK 349--disable_query_log 350--error ER_ACCESS_DENIED_ERROR 351connect(con44,127.0.0.1,Tanjotuser2," ",,); 352--enable_query_log 353 354 355# Verifying the client access denied passing not null password 356 357--replace_result $MASTER_MYSOCK MASTER_MYSOCK 358--disable_query_log 359--error ER_ACCESS_DENIED_ERROR 360connect(con45,localhost,Tanjotuser1,NULL,,); 361--enable_query_log 362 363--replace_result $MASTER_MYSOCK MASTER_MYSOCK 364--disable_query_log 365--error ER_ACCESS_DENIED_ERROR 366connect(con46,127.0.0.1,Tanjotuser2,NULL,,); 367--enable_query_log 368 369 370# Verifying the client access denied passing not null password 371 372--replace_result $MASTER_MYSOCK MASTER_MYSOCK 373--disable_query_log 374--error ER_ACCESS_DENIED_ERROR 375connect(con47,localhost,Tanjotuser1,0,,); 376--enable_query_log 377 378--replace_result $MASTER_MYSOCK MASTER_MYSOCK 379--disable_query_log 380--error ER_ACCESS_DENIED_ERROR 381connect(con48,127.0.0.1,Tanjotuser2,0,,); 382--enable_query_log 383 384 385# Dropping the created users 386 387connection default; 388 389DROP USER 'Tanjotuser1'@'localhost'; 390DROP USER 'Tanjotuser2'@'127.0.0.1'; 391 392 393--echo 394--echo 395--echo ==================================================================================================== 396--echo checking the integrity of NULL password using sha256_password plugin 397--echo ==================================================================================================== 398--echo 399 400# Checking the password authentication with sha256_password plugin 401# Creating 2 users and encrypting the server side password with the sha256_password plugin 402 403 404CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password'; 405 406CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password'; 407 408# setting the NULL password for the 2 users using the sha256_password plugin 409 410 411SET @@global.old_passwords=2; 412 413SET @@session.old_passwords=2; 414 415SET PASSWORD FOR 'Tanjotuser1'@'localhost'=''; 416SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=''; 417 418 419# Verifying the client connection using the null password 420 421connect(con57,localhost,Tanjotuser1,,,); 422connect(con58,127.0.0.1,Tanjotuser2,,,); 423 424 425# Verifying the client connection using the sha256_password plugin and not NULL password 426 427--replace_result $MASTER_MYSOCK MASTER_MYSOCK 428--disable_query_log 429--error ER_ACCESS_DENIED_ERROR 430connect(con59,localhost,Tanjotuser1," ",,); 431--enable_query_log 432 433--replace_result $MASTER_MYSOCK MASTER_MYSOCK 434--disable_query_log 435--error ER_ACCESS_DENIED_ERROR 436connect(con60,127.0.0.1,Tanjotuser2," ",,); 437--enable_query_log 438 439# Verifying the client connection using the sha256_password plugin and not NULL password 440 441--replace_result $MASTER_MYSOCK MASTER_MYSOCK 442--disable_query_log 443--error ER_ACCESS_DENIED_ERROR 444connect(con61,localhost,Tanjotuser1,0,,); 445--enable_query_log 446 447--replace_result $MASTER_MYSOCK MASTER_MYSOCK 448--disable_query_log 449--error ER_ACCESS_DENIED_ERROR 450connect(con62,127.0.0.1,Tanjotuser2,0,,); 451--enable_query_log 452 453 454# Dropping the created users 455 456connection default; 457 458DROP USER 'Tanjotuser1'@'localhost'; 459DROP USER 'Tanjotuser2'@'127.0.0.1'; 460 461 462# Disconnecting the above connections 463 464--disable_query_log 465 466disconnect con29; 467disconnect con30; 468#disconnect con31; 469#disconnect con32; 470disconnect con37; 471disconnect con38; 472#disconnect con39; 473#disconnect con40; 474disconnect con41; 475disconnect con42; 476#disconnect con43; 477#disconnect con44; 478#disconnect con45; 479#disconnect con46; 480#disconnect con47; 481#disconnect con48; 482disconnect con57; 483disconnect con58; 484#disconnect con59; 485#disconnect con60; 486#disconnect con61; 487#disconnect con62; 488 489--enable_query_log 490 491##The below section is hashed out until the bug#16022007 is fixed 492 493#--echo 494#--echo 495#--echo ==================================================================================================== 496#--echo checking the combination of Client default_auth=mysql_native_password and server default_auth=native 497#--echo ==================================================================================================== 498#--echo 499 500#CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc'; 501#CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED BY 'abc'; 502 503#GRANT ALL ON *.* TO 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password'; 504#GRANT ALL ON *.* TO 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'mysql_native_password'; 505 506#--echo **** Client default_auth=mysql_native_password and server default_auth=native 507 508#--exec $MYSQL -uTanjotuser1 -hlocalhost -pabc --default_auth=mysql_native_password -e "select user(), current_user()" 509#--exec $MYSQL -uTanjotuser2 -h127.0.0.1 -pabc --default_auth=mysql_native_password -e "select user(), current_user()" 510 511# Dropping the created users 512 513#DROP USER 'Tanjotuser1'@'localhost'; 514#DROP USER 'Tanjotuser2'@'127.0.0.1'; 515 516--echo 517--echo 518--echo ================================================================================================= 519--echo checking the combination of Client default_auth=sha256_password and server default_auth=native 520--echo ================================================================================================= 521--echo 522 523CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password'; 524CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password'; 525 526SET @@global.old_passwords=2; 527SET @@session.old_passwords=2; 528 529SET PASSWORD FOR 'Tanjotuser1'@'localhost'='abc'; 530SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'='abc'; 531 532GRANT ALL ON *.* TO 'Tanjotuser1'@'localhost'; 533GRANT ALL ON *.* TO 'Tanjotuser2'@'127.0.0.1'; 534 535--echo **** Client default_auth=sha_256_password and server default auth=native 536--exec $MYSQL -uTanjotuser1 -hlocalhost -pabc --default_auth=sha256_password -e "select user(), current_user()" 537--echo **** Client default_auth=native and server default auth=native 538--exec $MYSQL -uTanjotuser2 -h127.0.0.1 -pabc --default_auth=mysql_native_password -e "select user(), current_user()" 539--echo **** Client default_auth=sha_256_password + public key on file. 540--exec $MYSQL -uTanjotuser1 -hlocalhost -pabc --default_auth=sha256_password --server_public_key_path=$MYSQL_TEST_DIR/std_data/rsa_public_key.pem -e "select user(), current_user()" 541 542# Dropping the created users 543 544DROP USER 'Tanjotuser1'@'localhost'; 545DROP USER 'Tanjotuser2'@'127.0.0.1'; 546 547 548--echo 549--echo 550--echo ================================================================================================= 551--echo Starting the server with the default authentication mysql_native_password 552--echo ================================================================================================= 553--echo 554 555--echo # Restart server with default-authentication-plugin=mysql_native_password; 556 557let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect; 558--exec echo "wait" > $restart_file 559--shutdown_server 560--source include/wait_until_disconnected.inc 561-- exec echo "restart:--default-authentication-plugin=mysql_native_password " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect 562-- enable_reconnect 563-- source include/wait_until_connected_again.inc 564 565 566# Creating 3 users with mysql_native_password, mysql_old_password and sha256_password plugins respectively 567 568--echo Creating a user with mysql_native_password plugin 569CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc'; 570 571Grant all on *.* to 'Tanjotuser1'@'localhost'; 572 573--echo Creating a user with SHA256_password plugin 574CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password'; 575 576set @@session.old_passwords=2; 577 578set password for 'Tanjotuser3'@'localhost' ='abc'; 579 580Grant all on *.* to 'Tanjotuser3'@'localhost'; 581 582# Verifying the Authentication plugin for the above created users 583 584select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password'; 585--echo 1 Expected 586 587select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password'; 588--echo 1 Expected 589 590# Verifying the old_passwords variable Global value 591 592select @@global.old_passwords; 593--echo 0 Expected 594 595# Verifying the old_passwords variable session value 596 597--echo **** connecting Tanjotuser1 with Client default_auth=mysql_native_password 598--exec $MYSQL -uTanjotuser1 -pabc --default_auth=mysql_native_password -e "select @@session.old_passwords" 599--echo 0 Expected 600 601--echo **** connecting Tanjotuser1 with Client default_auth=sha256_password 602--exec $MYSQL -uTanjotuser1 -pabc --default_auth=sha256_password -e "select @@session.old_passwords" 603--echo 0 Expected 604 605 606--echo **** connecting Tanjotuser3 with Client default_auth=mysql_native_password 607--exec $MYSQL -uTanjotuser3 -pabc --default_auth=mysql_native_password -e "select @@session.old_passwords" 608--echo 2 Expected 609 610--echo **** connecting Tanjotuser3 with Client default_auth=sha256_password 611--exec $MYSQL -uTanjotuser3 -pabc --default_auth=sha256_password -e "select @@session.old_passwords" 612--echo 2 Expected 613 614 615# Dropping the created users 616 617DROP USER 'Tanjotuser1'@'localhost'; 618DROP USER 'Tanjotuser3'@'localhost'; 619 620 621--echo 622--echo 623--echo ================================================================================================= 624--echo Starting the server with the default authentication sha256_password 625--echo ================================================================================================= 626--echo 627 628--echo # Restart server with default-authentication-plugin=sha256_password; 629 630let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect; 631--exec echo "wait" > $restart_file 632--shutdown_server 633--source include/wait_until_disconnected.inc 634-- exec echo "restart:--default-authentication-plugin=sha256_password " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect 635-- enable_reconnect 636-- source include/wait_until_connected_again.inc 637 638# Creating 3 users with mysql_native_password, mysql_old_password and sha256_password plugins respectively 639 640--echo Creating a user with mysql_native_password plugin 641CREATE USER 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_native_password'; 642 643set @@session.old_passwords=0; 644 645set password for 'Tanjotuser2'@'localhost' ='abc'; 646 647Grant all on *.* to 'Tanjotuser2'@'localhost'; 648 649--echo Creating a user with SHA256_password plugin 650CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED by 'abc'; 651 652Grant all on *.* to 'Tanjotuser3'@'localhost'; 653 654# Verifying the Authentication plugin for the above created users 655 656select (select plugin from mysql.user where User='Tanjotuser2' and Host='localhost')='mysql_native_password'; 657--echo 1 Expected 658 659select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password'; 660--echo 1 Expected 661 662# Verifying the old_passwords variable Global value 663 664select @@global.old_passwords; 665--echo 2 Expected 666 667# Verifying the old_passwords variable session value 668 669--echo **** connecting Tanjotuser2 with Client default_auth=mysql_native_password 670--exec $MYSQL -uTanjotuser2 -pabc --default_auth=mysql_native_password -e "select @@session.old_passwords" 671--echo 0 Expected 672 673--echo **** connecting Tanjotuser2 with Client default_auth=sha256_password 674--exec $MYSQL -uTanjotuser2 -pabc --default_auth=sha256_password -e "select @@session.old_passwords" 675--echo 0 Expected 676 677--echo **** connecting Tanjotuser3 with Client default_auth=mysql_native_password 678--exec $MYSQL -uTanjotuser3 -pabc --default_auth=mysql_native_password -e "select @@session.old_passwords" 679--echo 2 Expected 680 681--echo **** connecting Tanjotuser3 with Client default_auth=sha256_password 682--exec $MYSQL -uTanjotuser3 -pabc --default_auth=sha256_password -e "select @@session.old_passwords" 683--echo 2 Expected 684 685 686# Dropping the created users 687 688DROP USER 'Tanjotuser2'@'localhost'; 689DROP USER 'Tanjotuser3'@'localhost'; 690 691# Restore default value for old_passwords 692SET GLOBAL old_passwords= default; 693 694