Gecko [ www.jdcpatala.org ]

Name	Size	Permission
access_credential_control-mast...	170 B	-rw-r--r--
access_credential_control.test	27.33 KB	-rw-r--r--
cert_verify.test	2.17 KB	-rw-r--r--
early_plugin_load-master.opt	23 B	-rw-r--r--
early_plugin_load.test	1.17 KB	-rw-r--r--
install_keyring_file-master.op...	73 B	-rw-r--r--
install_keyring_file.test	1.21 KB	-rw-r--r--
key_rotation.test	622 B	-rw-r--r--
key_rotation_qa-master.opt	20 B	-rw-r--r--
key_rotation_qa.test	9.02 KB	-rw-r--r--
key_value_auth-master.opt	170 B	-rw-r--r--
key_value_auth.test	23.04 KB	-rw-r--r--
keyring_file_data-master.opt	93 B	-rw-r--r--
keyring_file_data.test	1.18 KB	-rw-r--r--
keyring_file_data_qa-master.op...	73 B	-rw-r--r--
keyring_file_data_qa.test	2.84 KB	-rw-r--r--
keyring_udf-master.opt	37 B	-rw-r--r--
keyring_udf.test	5.2 KB	-rw-r--r--
multiple_plugins-master.opt	225 B	-rw-r--r--
multiple_plugins.test	505 B	-rw-r--r--
mysql_native_plugin.test	24.09 KB	-rw-r--r--
mysql_no_login-master.opt	20 B	-rw-r--r--
mysql_no_login.test	4.56 KB	-rw-r--r--
mysql_protocol_types.test	6.42 KB	-rw-r--r--
mysql_protocol_types_windows-m...	50 B	-rw-r--r--
mysql_protocol_types_windows.t...	3.38 KB	-rw-r--r--
mysql_sha256_plugin-master.opt	170 B	-rw-r--r--
mysql_sha256_plugin.test	21.21 KB	-rw-r--r--
mysql_ssl_connection-master.op...	35 B	-rw-r--r--
mysql_ssl_connection.test	418 B	-rw-r--r--
mysql_ssl_rsa_setup.test	12.05 KB	-rw-r--r--
named_pipe_ssl-master.opt	26 B	-rw-r--r--
named_pipe_ssl.test	959 B	-rw-r--r--
openssl_cert_generation-master...	39 B	-rw-r--r--
openssl_cert_generation.test	18.35 KB	-rw-r--r--
openssl_cert_generation_debug....	3.91 KB	-rw-r--r--
openssl_cert_generation_subjec...	39 B	-rw-r--r--
openssl_cert_generation_subjec...	3.37 KB	-rw-r--r--
openssl_cert_validity-master.o...	67 B	-rw-r--r--
openssl_cert_validity.test	590 B	-rw-r--r--
openssl_skip_cert_generation-m...	67 B	-rw-r--r--
openssl_skip_cert_generation.t...	2.46 KB	-rw-r--r--
password_expired-master.opt	170 B	-rw-r--r--
password_expired.test	22.89 KB	-rw-r--r--
password_expiry.test	7.69 KB	-rw-r--r--
require_secure_transport.test	3.15 KB	-rw-r--r--
require_secure_transport_windo...	50 B	-rw-r--r--
require_secure_transport_windo...	5.81 KB	-rw-r--r--
secure_file_priv_error.test	1.61 KB	-rw-r--r--
secure_file_priv_null-master.o...	24 B	-rw-r--r--
secure_file_priv_null.test	1.47 KB	-rw-r--r--
secure_file_priv_warnings-mast...	22 B	-rw-r--r--
secure_file_priv_warnings.test	2.18 KB	-rw-r--r--
secure_file_priv_warnings_not_...	1.22 KB	-rw-r--r--
secure_file_priv_warnings_win....	1.45 KB	-rw-r--r--
server_withssl_client_withouts...	405 B	-rw-r--r--
server_withssl_client_withouts...	11.95 KB	-rw-r--r--
server_withssl_client_withssl-...	405 B	-rw-r--r--
server_withssl_client_withssl....	15.89 KB	-rw-r--r--
set_password_deprecated.test	2.67 KB	-rw-r--r--
ssl_auto_detect-master.opt	124 B	-rw-r--r--
ssl_auto_detect.test	5.22 KB	-rw-r--r--
ssl_auto_detect_openssl-master...	118 B	-rw-r--r--
ssl_auto_detect_openssl.test	2.62 KB	-rw-r--r--
ssl_mode.test	9.47 KB	-rw-r--r--
tls.test	4.45 KB	-rw-r--r--
tls12_tls1.test	2.33 KB	-rw-r--r--
wrong_keyring_file_data-master...	90 B	-rw-r--r--
wrong_keyring_file_data.test	806 B	-rw-r--r--

Code Editor : key_value_auth.test

###############################################################################
#                                                                             #
# Authorization and authentication of the key-value pair shared between       #
# the server and the client                                                   #
#                                                                             #
#                                                                             #
# Creation Date: 2012-12-1                                                    #
# Author : Tanjot Singh Uppal                                                 #
#                                                                             #
#                                                                             #
# Description:Test Cases of authenticating the key value pair shared          #
# between the client and server during the handshake                          #
#                                                                             #
###############################################################################

--source include/not_embedded.inc
--source include/have_ssl.inc
--source include/have_sha256_rsa_auth.inc
--source include/mysql_upgrade_preparation.inc

# This test will intentionally generate errors in the server error log
# when a broken password is inserted into the mysql.user table.
# The below suppression is to clear those errors.
--disable_query_log
call mtr.add_suppression(".*Password salt for user.*");
--enable_query_log

--echo
--echo 
--echo =======================================================================
--echo Checking the password authentication with mysql_native_password plugin
--echo =======================================================================
--echo

# Checking the password authentication with mysql_native_password plugin
# Creating 2 users and encrypting the server side password with the default mysql_native_password plugin

CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';

CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED BY 'abc';

# Verifying the default server side plugin used to be  mysql_native_password

select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password';
--echo 1 Expected

select (select plugin from mysql.user where User='Tanjotuser2' and Host='127.0.0.1')='mysql_native_password';
--echo 1 Expected

# Verifying the encrypted password string using the password function

SET @@global.old_passwords=0;

SET @@session.old_passwords=0;

select (select authentication_string from mysql.user where User='Tanjotuser1' and Host='localhost')=password('abc');
--echo 1 Expected

select (select authentication_string from mysql.user where User='Tanjotuser2' and Host='127.0.0.1')=password('abc');
--echo 1 Expected

# Verifying the client connection using the same mysql_native_password

connect(con1,localhost,Tanjotuser1,abc,,);
connect(con2,127.0.0.1,Tanjotuser2,abc,,);

# Verifying the client access denied passing null password

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con3,localhost,Tanjotuser1,,,);
--enable_query_log

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con4,127.0.0.1,Tanjotuser2,,,);
--enable_query_log

# Verifying the client access denied passing blank password

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con5,localhost,Tanjotuser1, ,,);
--enable_query_log

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con6,127.0.0.1,Tanjotuser2, ,,);
--enable_query_log

# Dropping the created users

connection default;

DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser2'@'127.0.0.1';

--echo
--echo 
--echo =======================================================================
--echo Checking the password authentication with sha256_password plugin
--echo =======================================================================
--echo

# Checking the password authentication with sha256_password plugin
# Creating 2 users and encrypting the server side password with the sha256_password plugin

CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password';

CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password';

# Verifying the server side plugin used to be sha256_password

select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='sha256_password';
--echo 1 Expected

select (select plugin from mysql.user where User='Tanjotuser2' and Host='127.0.0.1')='sha256_password';
--echo 1 Expected

# Verifying the client connection using the same sha256_password plugin and NULL password

connect(con19,localhost,Tanjotuser1,,,);
connect(con20,127.0.0.1,Tanjotuser2,,,);

# Verifying the client connection using the same sha256_password plugin and junk password

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con21,localhost,Tanjotuser1," ",,);
--enable_query_log

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con22,127.0.0.1,Tanjotuser2," ",,);
--enable_query_log

# setting the password for the 2 users using the sha256_password plugin

connection default;

SET @@global.old_passwords=2;

SET @@session.old_passwords=2;

SET PASSWORD FOR 'Tanjotuser1'@'localhost'='abc';
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'='abc';

# Verifying the client connection using the same sha256_password plugin and NULL password

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con23,localhost,Tanjotuser1,,,);
--enable_query_log

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con24,127.0.0.1,Tanjotuser2,,,);
--enable_query_log

# Verifying the client connection using the same sha256_password plugin and junk password

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con25,localhost,Tanjotuser1," ",,);
--enable_query_log

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con26,127.0.0.1,Tanjotuser2," ",,);
--enable_query_log

# Verifying the client connection using the same sha256_password plugin and correct password

--disable_warnings
connect(con27,localhost,Tanjotuser1,abc,,);
connect(con28,127.0.0.1,Tanjotuser2,abc,,);
--enable_warnings

# Dropping the created users

connection default;

DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser2'@'127.0.0.1';

# Disconnecting the above connections

--disable_query_log

disconnect con1;
disconnect con2;
#disconnect con3;
#disconnect con4;
#disconnect con5;
#disconnect con6;
disconnect con19;
disconnect con20;
#disconnect con21;
#disconnect con22;
#disconnect con23;
#disconnect con24;
#disconnect con25;
#disconnect con26;
disconnect con27;
disconnect con28;

--enable_query_log

--echo
--echo 
--echo ====================================================================================================
--echo checking the integrity of long password (more than 40 chars) using mysql_native_password plugin
--echo ====================================================================================================
--echo

# Checking the password authentication with mysql_native_password plugin
# Creating 2 users and encrypting the server side password with the default mysql_native_password plugin

CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222';

CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED BY 'aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222';

# Verifying the client connection using the same mysql_native_password

--disable_warnings
connect(con29,localhost,Tanjotuser1,aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222,,);
connect(con30,127.0.0.1,Tanjotuser2,aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222,,);
--enable_warnings

# Verifying the client access denied passing null password

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con31,localhost,Tanjotuser1,,,);
--enable_query_log

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con32,127.0.0.1,Tanjotuser2,,,);
--enable_query_log

# Dropping the created users

connection default;

DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser2'@'127.0.0.1';

--echo
--echo 
--echo ====================================================================================================
--echo checking the integrity of long password (more than 40 chars) using sha256_password plugin
--echo ====================================================================================================
--echo

# Checking the password authentication with sha256_password plugin
# Creating 2 users and encrypting the server side password with the sha256_password plugin

CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password';

CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password';

# setting the password for the 2 users using the sha256_password plugin

SET @@global.old_passwords=2;

SET @@session.old_passwords=2;

SET PASSWORD FOR 'Tanjotuser1'@'localhost'='aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222';
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'='aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222';

# Verifying the client connection using the same sha256_password plugin and correct password

--disable_warnings
connect(con37,localhost,Tanjotuser1,aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222,,);
connect(con38,127.0.0.1,Tanjotuser2,aaaaaaaaaabbbbbbbbbbccccccccccddddddddddeeeeeeeeeeaaaaaaaaaannnnnnnnnnaaaaaaaaaahhhhhhhhhh1111111111gggggggggg2222222222,,);
--enable_warnings

# Verifying the client connection using the same sha256_password plugin and NULL password

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con39,localhost,Tanjotuser1,,,);
--enable_query_log

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con40,127.0.0.1,Tanjotuser2,,,);
--enable_query_log

# Dropping the created users

connection default;

DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser2'@'127.0.0.1';

--echo
--echo 
--echo ====================================================================================================
--echo checking the integrity of NULL password using mysql_native_password plugin
--echo ====================================================================================================
--echo

# Checking the password authentication with mysql_native_password plugin
# Creating 2 users and encrypting the server side password with the default mysql_native_password plugin

CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY '';

CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED BY '';

# Verifying the client connection using the null password

connect(con41,localhost,Tanjotuser1,,,);
connect(con42,127.0.0.1,Tanjotuser2,,,);

# Verifying the client access denied passing not null password

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con43,localhost,Tanjotuser1," ",,);
--enable_query_log

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con44,127.0.0.1,Tanjotuser2," ",,);
--enable_query_log

# Verifying the client access denied passing not null password

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con45,localhost,Tanjotuser1,NULL,,);
--enable_query_log

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con46,127.0.0.1,Tanjotuser2,NULL,,);
--enable_query_log

# Verifying the client access denied passing not null password

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con47,localhost,Tanjotuser1,0,,);
--enable_query_log

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con48,127.0.0.1,Tanjotuser2,0,,);
--enable_query_log

# Dropping the created users

connection default;

DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser2'@'127.0.0.1';

--echo
--echo 
--echo ====================================================================================================
--echo checking the integrity of NULL password using sha256_password plugin
--echo ====================================================================================================
--echo

# Checking the password authentication with sha256_password plugin
# Creating 2 users and encrypting the server side password with the sha256_password plugin

CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password';

CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password';

# setting the NULL password for the 2 users using the sha256_password plugin

SET @@global.old_passwords=2;

SET @@session.old_passwords=2;

SET PASSWORD FOR 'Tanjotuser1'@'localhost'='';
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'='';

# Verifying the client connection using the null password

connect(con57,localhost,Tanjotuser1,,,);
connect(con58,127.0.0.1,Tanjotuser2,,,);

# Verifying the client connection using the sha256_password plugin and not NULL password

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con59,localhost,Tanjotuser1," ",,);
--enable_query_log

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con60,127.0.0.1,Tanjotuser2," ",,);
--enable_query_log

# Verifying the client connection using the sha256_password plugin and not NULL password

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con61,localhost,Tanjotuser1,0,,);
--enable_query_log

--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con62,127.0.0.1,Tanjotuser2,0,,);
--enable_query_log

# Dropping the created users

connection default;

DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser2'@'127.0.0.1';

# Disconnecting the above connections

--disable_query_log

disconnect con29;
disconnect con30;
#disconnect con31;
#disconnect con32;
disconnect con37;
disconnect con38;
#disconnect con39;
#disconnect con40;
disconnect con41;
disconnect con42;
#disconnect con43;
#disconnect con44;
#disconnect con45;
#disconnect con46;
#disconnect con47;
#disconnect con48;
disconnect con57;
disconnect con58;
#disconnect con59;
#disconnect con60;
#disconnect con61;
#disconnect con62;

--enable_query_log

##The below section is hashed out until the bug#16022007  is fixed

#--echo
#--echo 
#--echo ====================================================================================================
#--echo checking the combination of Client default_auth=mysql_native_password and server default_auth=native
#--echo ====================================================================================================
#--echo

#CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
#CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED BY 'abc';

#GRANT ALL ON *.* TO 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
#GRANT ALL ON *.* TO 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'mysql_native_password';

#--echo **** Client default_auth=mysql_native_password and server default_auth=native

#--exec $MYSQL -uTanjotuser1 -hlocalhost -pabc --default_auth=mysql_native_password -e "select user(), current_user()"
#--exec $MYSQL -uTanjotuser2 -h127.0.0.1 -pabc --default_auth=mysql_native_password -e "select user(), current_user()"

# Dropping the created users

#DROP USER 'Tanjotuser1'@'localhost';
#DROP USER 'Tanjotuser2'@'127.0.0.1';

--echo
--echo 
--echo =================================================================================================
--echo checking the combination of Client default_auth=sha256_password and server default_auth=native
--echo =================================================================================================
--echo

CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password';
CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password';

SET @@global.old_passwords=2;
SET @@session.old_passwords=2;

SET PASSWORD FOR 'Tanjotuser1'@'localhost'='abc';
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'='abc';

GRANT ALL ON *.* TO 'Tanjotuser1'@'localhost';
GRANT ALL ON *.* TO 'Tanjotuser2'@'127.0.0.1';

--echo **** Client default_auth=sha_256_password and server default auth=native
--exec $MYSQL -uTanjotuser1 -hlocalhost -pabc --default_auth=sha256_password -e "select user(), current_user()"
--echo **** Client default_auth=native and server default auth=native
--exec $MYSQL -uTanjotuser2 -h127.0.0.1 -pabc --default_auth=mysql_native_password -e "select user(), current_user()"
--echo **** Client default_auth=sha_256_password + public key on file.
--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()"

# Dropping the created users

DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser2'@'127.0.0.1';

--echo
--echo 
--echo =================================================================================================
--echo Starting the server with the default authentication mysql_native_password
--echo =================================================================================================
--echo

--echo # Restart server with default-authentication-plugin=mysql_native_password;

let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect;
--exec echo "wait" > $restart_file
--shutdown_server 
--source include/wait_until_disconnected.inc
-- exec echo "restart:--default-authentication-plugin=mysql_native_password  " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
-- enable_reconnect
-- source include/wait_until_connected_again.inc

# Creating 3 users with mysql_native_password, mysql_old_password and sha256_password plugins respectively

--echo Creating a user with mysql_native_password plugin
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';

Grant all on *.* to 'Tanjotuser1'@'localhost';

--echo Creating a user with SHA256_password plugin
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';

set @@session.old_passwords=2;

set password for 'Tanjotuser3'@'localhost' ='abc';

Grant all on *.* to 'Tanjotuser3'@'localhost';

# Verifying the Authentication plugin for the above created users

select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password';
--echo 1 Expected

select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
--echo 1 Expected

# Verifying the old_passwords variable Global value

select @@global.old_passwords;
--echo 0 Expected

# Verifying the old_passwords variable session value

--echo **** connecting Tanjotuser1 with Client default_auth=mysql_native_password 
--exec $MYSQL -uTanjotuser1 -pabc --default_auth=mysql_native_password -e "select @@session.old_passwords"
--echo 0 Expected

--echo **** connecting Tanjotuser1 with Client default_auth=sha256_password 
--exec $MYSQL -uTanjotuser1 -pabc --default_auth=sha256_password -e "select @@session.old_passwords"
--echo 0 Expected

--echo **** connecting Tanjotuser3 with Client default_auth=mysql_native_password 
--exec $MYSQL -uTanjotuser3 -pabc --default_auth=mysql_native_password -e "select @@session.old_passwords"
--echo 2 Expected

--echo **** connecting Tanjotuser3 with Client default_auth=sha256_password 
--exec $MYSQL -uTanjotuser3 -pabc --default_auth=sha256_password -e "select @@session.old_passwords"
--echo 2 Expected

# Dropping the created users

DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser3'@'localhost';

--echo
--echo 
--echo =================================================================================================
--echo Starting the server with the default authentication sha256_password
--echo =================================================================================================
--echo

--echo # Restart server with default-authentication-plugin=sha256_password;

let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect;
--exec echo "wait" > $restart_file
--shutdown_server 
--source include/wait_until_disconnected.inc
-- exec echo "restart:--default-authentication-plugin=sha256_password  " > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
-- enable_reconnect
-- source include/wait_until_connected_again.inc

# Creating 3 users with mysql_native_password, mysql_old_password and sha256_password plugins respectively

--echo Creating a user with mysql_native_password plugin
CREATE USER 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_native_password';

set @@session.old_passwords=0;

set password for 'Tanjotuser2'@'localhost' ='abc';

Grant all on *.* to 'Tanjotuser2'@'localhost';

--echo Creating a user with SHA256_password plugin
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED by 'abc';

Grant all on *.* to 'Tanjotuser3'@'localhost';

# Verifying the Authentication plugin for the above created users

select (select plugin from mysql.user where User='Tanjotuser2' and Host='localhost')='mysql_native_password';
--echo 1 Expected

select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
--echo 1 Expected

# Verifying the old_passwords variable Global value

select @@global.old_passwords;
--echo 2 Expected

# Verifying the old_passwords variable session value

--echo **** connecting Tanjotuser2 with Client default_auth=mysql_native_password 
--exec $MYSQL -uTanjotuser2 -pabc --default_auth=mysql_native_password -e "select @@session.old_passwords"
--echo 0 Expected

--echo **** connecting Tanjotuser2 with Client default_auth=sha256_password 
--exec $MYSQL -uTanjotuser2 -pabc --default_auth=sha256_password -e "select @@session.old_passwords"
--echo 0 Expected

--echo **** connecting Tanjotuser3 with Client default_auth=sha256_password 
--exec $MYSQL -uTanjotuser3 -pabc --default_auth=sha256_password -e "select @@session.old_passwords"
--echo 2 Expected

# Dropping the created users

DROP USER 'Tanjotuser2'@'localhost';
DROP USER 'Tanjotuser3'@'localhost';

# Restore default value for old_passwords
SET GLOBAL old_passwords= default;

${this.title}

Code Editor : key_value_auth.test