^^&&^^

Edit File: mysql_old_passwords.test

###############################################################################
#                                                                             #
# Authorization and authentication of the hashed password based on the        #
# plugin selection by OLD_PASSWORDS variable both at the server side          #
# and the clienbt side.                                                       #
#                                                                             #
#                                                                             #
#                                                                             #
# Creation Date: 2012-12-12                                                   #
# Author : Tanjot Singh Uppal                                                 #
#                                                                             #
#                                                                             #
# Description:Test Cases of authenticating the hashed password using the      #
# Plugin selected by the value of the old_passwords variable at the server    #
# side as well as the client side                                             #
#                                                                             #
#                                                                             #
###############################################################################

--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 global and session value of old_passwords when server default plugin is mysql_native_password.
--echo =============================================================================================================
--echo

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

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

# Checking the old_passwords values when user created with mysql_old_password plugin
# Creating 2 users and encrypting the server side password with the mysql_old_password plugin

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

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

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

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

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

connection default;

## setting password when old_password points to mysql_native_password plugin

--error ER_PASSWD_LENGTH
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
--error ER_PASSWD_LENGTH
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');

## setting password when old_password points to sha256_password plugin

set @@session.old_passwords=2;

--error ER_PASSWD_LENGTH
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
--error ER_PASSWD_LENGTH
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');

## setting password when old_password points to mysql_old_password plugin

set @@session.old_passwords=1;

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

## Checking connection with the above created users and valid password shipment from client to server and vice versa

--disable_warnings
set @@global.secure_auth=0;
--enable_warnings

connect(con3,localhost,Tanjotuser1,abc,,,,SKIPSECUREAUTH);
connect(con4,127.0.0.1,Tanjotuser2,abc,,,,SKIPSECUREAUTH);

# Validating the old_passwords value from the created users with secure_auth disabled.

connection default;

--disable_warnings
set @@global.secure_auth=0;
--enable_warnings

--echo **** connecting Tanjotuser1 with server 
--exec $MYSQL -uTanjotuser1 -pabc --skip-secure-auth -hlocalhost --default_auth=mysql_native_password -e "select @@Global.old_passwords;select @@session.old_passwords"
--echo 0 for GLOBAL and 1 for SESSION Expected

--echo **** connecting Tanjotuser2 with server 
--exec $MYSQL -uTanjotuser2 -pabc --skip-secure-auth -h127.0.0.1 --default_auth=mysql_native_password -e "select @@Global.old_passwords;select @@session.old_passwords"
--echo 0 for GLOBAL and 1 for SESSION Expected

--echo **** connecting Tanjotuser1 with server 
--exec $MYSQL -uTanjotuser1 -pabc --skip-secure-auth -hlocalhost --default_auth=mysql_old_password -e "select @@Global.old_passwords;select @@session.old_passwords"
--echo 0 for GLOBAL and 1 for SESSION Expected

--echo **** connecting Tanjotuser2 with server 
--exec $MYSQL -uTanjotuser2 -pabc --skip-secure-auth -h127.0.0.1 --default_auth=mysql_old_password -e "select @@Global.old_passwords;select @@session.old_passwords"
--echo 0 for GLOBAL and 1 for SESSION Expected

--echo **** connecting Tanjotuser1 with server 
--exec $MYSQL -uTanjotuser1 -pabc --skip-secure-auth -hlocalhost --default_auth=sha256_password -e "select @@Global.old_passwords;select @@session.old_passwords"
--echo 0 for GLOBAL and 1 for SESSION Expected

--echo **** connecting Tanjotuser2 with server 
--exec $MYSQL -uTanjotuser2 -pabc --skip-secure-auth -h127.0.0.1 --default_auth=sha256_password -e "select @@Global.old_passwords;select @@session.old_passwords"
--echo 0 for GLOBAL and 1 for SESSION Expected

--echo **** connecting Tanjotuser1 with server 
--exec $MYSQL -uTanjotuser1 -pabc --skip-secure-auth -hlocalhost -e "select @@Global.old_passwords;select @@session.old_passwords"
--echo 0 for GLOBAL and 1 for SESSION Expected

--echo **** connecting Tanjotuser2 with server 
--exec $MYSQL -uTanjotuser2 -pabc --skip-secure-auth -h127.0.0.1 -e "select @@Global.old_passwords;select @@session.old_passwords"
--echo 0 for GLOBAL and 1 for SESSION Expected

# Validating the old_passwords value from the created users with secure_auth enabled.

connection default;

--disable_warnings
set @@global.secure_auth=1;
--enable_warnings

--echo **** connecting Tanjotuser1 with server
--error 1
--exec $MYSQL -uTanjotuser1 -pabc --skip-secure-auth -hlocalhost --default_auth=mysql_native_password -e "select @@Global.old_passwords;select @@session.old_passwords"

--echo **** connecting Tanjotuser2 with server
--error 1
--exec $MYSQL -uTanjotuser2 -pabc --skip-secure-auth -h127.0.0.1 --default_auth=mysql_native_password -e "select @@Global.old_passwords;select @@session.old_passwords"

--echo **** connecting Tanjotuser1 with server
--error 1
--exec $MYSQL -uTanjotuser1 -pabc --skip-secure-auth -hlocalhost --default_auth=mysql_old_password -e "select @@Global.old_passwords;select @@session.old_passwords"

--echo **** connecting Tanjotuser2 with server
--error 1
--exec $MYSQL -uTanjotuser2 -pabc --skip-secure-auth -h127.0.0.1 --default_auth=mysql_old_password -e "select @@Global.old_passwords;select @@session.old_passwords"

--echo **** connecting Tanjotuser1 with server
--error 1
--exec $MYSQL -uTanjotuser1 -pabc --skip-secure-auth -hlocalhost --default_auth=sha256_password -e "select @@Global.old_passwords;select @@session.old_passwords"

--echo **** connecting Tanjotuser2 with server
--error 1
--exec $MYSQL -uTanjotuser2 -pabc --skip-secure-auth -h127.0.0.1 --default_auth=sha256_password -e "select @@Global.old_passwords;select @@session.old_passwords"

--echo **** connecting Tanjotuser1 with server
--error 1
--exec $MYSQL -uTanjotuser1 -pabc --skip-secure-auth -hlocalhost -e "select @@Global.old_passwords;select @@session.old_passwords"

--echo **** connecting Tanjotuser2 with server
--error 1
--exec $MYSQL -uTanjotuser2 -pabc --skip-secure-auth -h127.0.0.1 -e "select @@Global.old_passwords;select @@session.old_passwords"

# Dropping the created users

connection default;

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

# Checking the old_passwords values when user created with mysql_native_password plugin

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

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

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

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

connect(con5,localhost,Tanjotuser1,,,);
connect(con6,127.0.0.1,Tanjotuser2,,,);

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

connection default;

## setting password when old_password points to mysql_old_password plugin

set @@session.old_passwords=1;

--error ER_PASSWD_LENGTH
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
--error ER_PASSWD_LENGTH
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');

## setting password when old_password points to sha256_password plugin

set @@session.old_passwords=2;

--error ER_PASSWD_LENGTH
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
--error ER_PASSWD_LENGTH
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');

## setting password when old_password points to mysql_native_password plugin

set @@session.old_passwords=0;

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

## Checking connection with the above created users and valid password shipment from client to server and vice versa

connect(con7,localhost,Tanjotuser1,abc,,);
connect(con8,127.0.0.1,Tanjotuser2,abc,,);

# Validating the old_passwords value from the created users with secure_auth disabled.

connection default;

--disable_warnings
set @@global.secure_auth=0;
--enable_warnings

--echo **** connecting Tanjotuser1 with server 
--exec $MYSQL -uTanjotuser1 -pabc -hlocalhost --default_auth=mysql_native_password -e "select @@Global.old_passwords;select @@session.old_passwords"
--echo 0 for GLOBAL and 0 for SESSION Expected

--echo **** connecting Tanjotuser2 with server 
--exec $MYSQL -uTanjotuser2 -pabc -h127.0.0.1 --default_auth=mysql_native_password -e "select @@Global.old_passwords;select @@session.old_passwords"
--echo 0 for GLOBAL and 0 for SESSION Expected

--echo **** connecting Tanjotuser1 with server 
--exec $MYSQL -uTanjotuser1 -pabc -hlocalhost --default_auth=mysql_old_password -e "select @@Global.old_passwords;select @@session.old_passwords"
--echo 0 for GLOBAL and 0 for SESSION Expected

--echo **** connecting Tanjotuser2 with server 
--exec $MYSQL -uTanjotuser2 -pabc -h127.0.0.1 --default_auth=mysql_old_password -e "select @@Global.old_passwords;select @@session.old_passwords"
--echo 0 for GLOBAL and 0 for SESSION Expected

--echo **** connecting Tanjotuser1 with server 
--exec $MYSQL -uTanjotuser1 -pabc -hlocalhost --default_auth=sha256_password -e "select @@Global.old_passwords;select @@session.old_passwords"
--echo 0 for GLOBAL and 0 for SESSION Expected

--echo **** connecting Tanjotuser2 with server 
--exec $MYSQL -uTanjotuser2 -pabc -h127.0.0.1 --default_auth=sha256_password -e "select @@Global.old_passwords;select @@session.old_passwords"
--echo 0 for GLOBAL and 0 for SESSION Expected

--echo **** connecting Tanjotuser1 with server 
--exec $MYSQL -uTanjotuser1 -pabc -hlocalhost -e "select @@Global.old_passwords;select @@session.old_passwords"
--echo 0 for GLOBAL and 0 for SESSION Expected

--echo **** connecting Tanjotuser2 with server 
--exec $MYSQL -uTanjotuser2 -pabc -h127.0.0.1 -e "select @@Global.old_passwords;select @@session.old_passwords"
--echo 0 for GLOBAL and 0 for SESSION Expected

# Validating the old_passwords value from the created users with secure_auth enabled.

--disable_warnings
set @@global.secure_auth=1;
--enable_warnings

# Dropping the created users

connection default;

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

# Checking the old_passwords values when user created 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 client connection using the same sha256_password plugin and NULL password

connect(con9,localhost,Tanjotuser1,,,);
connect(con10,127.0.0.1,Tanjotuser2,,,);

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

connection default;

## setting password when old_password points to mysql_old_password plugin

set @@session.old_passwords=1;

--error ER_PASSWORD_FORMAT
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
--error ER_PASSWORD_FORMAT
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');

## setting password when old_password points to mysql_native_password plugin

set @@session.old_passwords=0;

--error ER_PASSWORD_FORMAT
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
--error ER_PASSWORD_FORMAT
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');

## setting password when old_password points to sha256_password plugin

set @@session.old_passwords=2;

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

## Checking connection with the above created users and valid password shipment from client to server and vice versa

connect(con11,localhost,Tanjotuser1,abc,,);
connect(con12,127.0.0.1,Tanjotuser2,abc,,);

# Validating the old_passwords value from the created users with secure_auth disabled.

connection default;

--disable_warnings
set @@global.secure_auth=0;
--enable_warnings

# Validating the old_passwords value from the created users with secure_auth enabled.

--disable_warnings
set @@global.secure_auth=1;
--enable_warnings

# Dropping the created users

connection default;

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

--echo
--echo 
--echo ============================================================================================================
--echo Checking the global and session value of old_passwords when server default plugin is 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 10
--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

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

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

# Checking the old_passwords values when user created with mysql_old_password plugin

# Creating 2 users and encrypting the server side password with the mysql_old_password plugin

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

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

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

connect(con25,localhost,Tanjotuser1,,,,,SKIPSECUREAUTH);
connect(con26,127.0.0.1,Tanjotuser2,,,,,SKIPSECUREAUTH);

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

connection default;

## setting password when old_password points to mysql_native_password plugin

--error ER_PASSWD_LENGTH
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
--error ER_PASSWD_LENGTH
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');

## setting password when old_password points to sha256_password plugin

set @@session.old_passwords=2;

--error ER_PASSWD_LENGTH
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
--error ER_PASSWD_LENGTH
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');

## setting password when old_password points to mysql_old_password plugin

set @@session.old_passwords=1;

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

## Checking connection with the above created users and valid password shipment from client to server and vice versa

--disable_warnings
set @@global.secure_auth=0;
--enable_warnings

connect(con27,localhost,Tanjotuser1,abc,,,,SKIPSECUREAUTH);
connect(con28,127.0.0.1,Tanjotuser2,abc,,,,SKIPSECUREAUTH);

# Validating the old_passwords value from the created users with secure_auth disabled.

connection default;

--disable_warnings
set @@global.secure_auth=0;
--enable_warnings

# Validating the old_passwords value from the created users with secure_auth enabled.

connection default;

--disable_warnings
set @@global.secure_auth=1;
--enable_warnings

--echo **** connecting Tanjotuser1 with server
--error 1
--exec $MYSQL -uTanjotuser1 -pabc --skip-secure-auth -hlocalhost -e "select @@Global.old_passwords;select @@session.old_passwords"

--echo **** connecting Tanjotuser2 with server
--error 1
--exec $MYSQL -uTanjotuser2 -pabc --skip-secure-auth -h127.0.0.1 -e "select @@Global.old_passwords;select @@session.old_passwords"

# Dropping the created users

connection default;

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

# Checking the old_passwords values when user created with mysql_native_password plugin

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

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

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

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

connect(con29,localhost,Tanjotuser1,,,);
connect(con30,127.0.0.1,Tanjotuser2,,,);

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

connection default;

## setting password when old_password points to mysql_old_password plugin

set @@session.old_passwords=1;

--error ER_PASSWD_LENGTH
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
--error ER_PASSWD_LENGTH
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');

## setting password when old_password points to sha256_password plugin

set @@session.old_passwords=2;

--error ER_PASSWD_LENGTH
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
--error ER_PASSWD_LENGTH
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');

## setting password when old_password points to mysql_native_password plugin

set @@session.old_passwords=0;

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

## Checking connection with the above created users and valid password shipment from client to server and vice versa

connect(con31,localhost,Tanjotuser1,abc,,);
connect(con32,127.0.0.1,Tanjotuser2,abc,,);

# Validating the old_passwords value from the created users with secure_auth disabled.

connection default;

--disable_warnings
set @@global.secure_auth=0;
--enable_warnings

# Validating the old_passwords value from the created users with secure_auth enabled.

--disable_warnings
set @@global.secure_auth=1;
--enable_warnings

# Dropping the created users

connection default;

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

# Checking the old_passwords values when user created 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 client connection using the same sha256_password plugin and NULL password

connect(con33,localhost,Tanjotuser1,,,);
connect(con34,127.0.0.1,Tanjotuser2,,,);

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

connection default;

## setting password when old_password points to mysql_old_password plugin

set @@session.old_passwords=1;

--error ER_PASSWORD_FORMAT
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
--error ER_PASSWORD_FORMAT
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');

## setting password when old_password points to mysql_native_password plugin

set @@session.old_passwords=0;

--error ER_PASSWORD_FORMAT
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
--error ER_PASSWORD_FORMAT
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');

## setting password when old_password points to sha256_password plugin

set @@session.old_passwords=2;

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

## Checking connection with the above created users and valid password shipment from client to server and vice versa

connect(con35,localhost,Tanjotuser1,abc,,);
connect(con36,127.0.0.1,Tanjotuser2,abc,,);

# Validating the old_passwords value from the created users with secure_auth disabled.

connection default;

--disable_warnings
set @@global.secure_auth=0;
--enable_warnings

# Validating the old_passwords value from the created users with secure_auth enabled.

--disable_warnings
set @@global.secure_auth=1;
--enable_warnings

# Disconnection the open sessions

disconnect con1;
disconnect con2;
disconnect con3;
disconnect con4;
disconnect con5;
disconnect con6;
disconnect con7;
disconnect con8;
disconnect con9;
disconnect con10;
disconnect con11;
disconnect con12;
#disconnect con13;
#disconnect con14;
#disconnect con15;
#disconnect con16;
#disconnect con17;
#disconnect con18;
#disconnect con19;
#disconnect con20;
#disconnect con21;
#disconnect con22;
#disconnect con23;
#disconnect con24;
disconnect con25;
disconnect con26;
disconnect con27;
disconnect con28;
disconnect con29;
disconnect con30;
disconnect con31;
disconnect con32;
disconnect con33;
disconnect con34;
disconnect con35;
disconnect con36;

# Dropping the created users

connection default;

DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser2'@'127.0.0.1';
set @@global.old_passwords=default;
set @@session.old_passwords=default;
set @@global.secure_auth=default;