###############################################################################
# #
# User access, credential update, permission grant/revoke, alter user, #
# drop user, create user, grant user, set password and rename user commands #
# functional and integration test #
# #
# #
# Creation Date: 2012-12-27 #
# Author : Tanjot Singh Uppal #
# #
# #
# Description:Test Cases of creation, alteration and controlling of #
# the user access #
# #
###############################################################################
--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
--disable_query_log
call mtr.add_suppression(".*Tanjotuser3@localhost.*");
--enable_query_log
--echo
--echo
--echo =======================================================================
--echo Checking the password plugin assignment with create user command
--echo =======================================================================
--echo
#### Checking the password plugin assignment with create user command
## Creating a user with respect to all the 3 password authentication plugin
--disable_warnings
set @@global.secure_auth=0;
--enable_warnings
--echo **** Creating user with mysql_native_password plugin
select @@session.old_passwords;
--echo 0 Expected
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
--echo **** Creating user with mysql_old_password plugin
set @@session.old_passwords=1;
CREATE USER 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password';
SET PASSWORD for 'Tanjotuser2'@'localhost' = password('abc');
--echo **** Creating user with sha256_password plugin
set @@session.old_passwords=2;
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
SET PASSWORD for 'Tanjotuser3'@'localhost' = password('abc');
## Validating the plugin assigned for the above 3 users in the user table
--echo **** Validating the plugin names
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='localhost')='mysql_old_password';
--echo 1 Expected
select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
--echo 1 Expected
## Dropping the created users
Drop user 'Tanjotuser1'@'localhost';
Drop user 'Tanjotuser2'@'localhost';
Drop user 'Tanjotuser3'@'localhost';
--echo
--echo
--echo =======================================================================
--echo Checking the password plugin assignment with grant user command
--echo =======================================================================
--echo
#### Checking the password plugin assignment with grant user command
## Creating a user with respect to all the 3 password authentication plugin
--echo **** Creating user with mysql_native_password plugin
set @@session.old_passwords=0;
GRANT usage on mysql.* to 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
--echo **** Creating user with mysql_old_password plugin
set @@session.old_passwords=1;
GRANT usage on mysql.* to 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password';
SET PASSWORD for 'Tanjotuser2'@'localhost' = password('abc');
--echo **** Creating user with sha256_password plugin
set @@session.old_passwords=2;
GRANT usage on mysql.* to 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
SET PASSWORD for 'Tanjotuser3'@'localhost' = password('abc');
## Validating the plugin assigned for the above 3 users in the user table
--echo **** Validating the plugin names
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='localhost')='mysql_old_password';
--echo 1 Expected
select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
--echo 1 Expected
## Dropping the created users
Drop user 'Tanjotuser1'@'localhost';
Drop user 'Tanjotuser2'@'localhost';
Drop user 'Tanjotuser3'@'localhost';
--echo
--echo
--echo =======================================================================
--echo Checking the functionality and integrity of the DROP user command
--echo =======================================================================
--echo
#### Checking the functionality and integrity of the DROP user command
## Creating a user with respect to all the 3 password authentication plugin
--echo **** Creating user with mysql_native_password plugin
set @@session.old_passwords=0;
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
--echo **** Creating user with mysql_old_password plugin
set @@session.old_passwords=1;
CREATE USER 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password';
SET PASSWORD for 'Tanjotuser2'@'localhost' = password('abc');
--echo **** Creating user with sha256_password plugin
set @@session.old_passwords=2;
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
SET PASSWORD for 'Tanjotuser3'@'localhost' = password('abc');
## Creating a user with respect to all the 3 password authentication plugin
--echo Connecting a session each with the above 3 users
--disable_warnings
connect(con1,localhost,Tanjotuser1,abc,,);
connect(con2,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
connect(con3,localhost,Tanjotuser3,abc,,);
--enable_warnings
## Now dropping the above 3 users with each having a open session
--echo dropping the above 3 users with each having a open session
connection default;
Drop user 'Tanjotuser1'@'localhost';
Drop user 'Tanjotuser2'@'localhost';
Drop user 'Tanjotuser3'@'localhost';
## Now trying opening a new session for the above dropped user
--echo Now trying opening a new session for the above dropped user
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con4,localhost,Tanjotuser1,abc,,);
--enable_query_log
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con5,localhost,Tanjotuser2,abc,,);
--enable_query_log
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con6,localhost,Tanjotuser3,abc,,);
--enable_query_log
## Accessing the open sessions for the above dropped user
--echo Accessing the open sessions for the above dropped user
connection con1;
select user(), current_user();
connection con2;
select user(), current_user();
connection con3;
select user(), current_user();
disconnect con1;
disconnect con2;
disconnect con3;
--echo
--echo
--echo =======================================================================
--echo Checking the privellages post renaming the user
--echo =======================================================================
--echo
#### Checking the rename user command
## Creating a user with respect to all the 3 password authentication plugin
connection default;
--echo **** Creating user with mysql_native_password plugin
set @@session.old_passwords=0;
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
--echo **** Creating user with mysql_old_password plugin
set @@session.old_passwords=1;
CREATE USER 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password';
SET PASSWORD for 'Tanjotuser2'@'localhost' = password('abc');
--echo **** Creating user with sha256_password plugin
set @@session.old_passwords=2;
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
SET PASSWORD for 'Tanjotuser3'@'localhost' = password('abc');
## Validating the plugin assigned for the above 3 users in the user table
--echo **** Validating the plugin names
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='localhost')='mysql_old_password';
--echo 1 Expected
select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
--echo 1 Expected
## Connecting a session to each of the above created users
--disable_warnings
connect(con7,localhost,Tanjotuser1,abc,,);
select user(), current_user();
connect(con8,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
select user(), current_user();
connect(con9,localhost,Tanjotuser3,abc,,);
select user(), current_user();
--enable_warnings
## Validating the integrity of the rename user command
connection default;
--echo **** Validating the integrity of the rename user command
RENAME USER 'Tanjotuser1'@'localhost' to 'user1'@'localhost';
RENAME USER 'Tanjotuser2'@'localhost' to 'user2'@'localhost';
RENAME USER 'Tanjotuser3'@'localhost' to 'user3'@'localhost';
## Validating the plugin assigned for the renamed users in the user table
--echo **** Validating the plugin names
select (select plugin from mysql.user where User='user1' and Host='localhost')='mysql_native_password';
--echo 1 Expected
select (select plugin from mysql.user where User='user2' and Host='localhost')='mysql_old_password';
--echo 1 Expected
select (select plugin from mysql.user where User='user3' and Host='localhost')='sha256_password';
--echo 1 Expected
## Validating the users post renaming
--echo **** Validating the user connections post renaming
connection con7;
select user(), current_user();
connection con8;
select user(), current_user();
connection con9;
select user(), current_user();
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con10,localhost,Tanjotuser1,abc,,);
--enable_query_log
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con11,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
--enable_query_log
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con12,localhost,Tanjotuser3,abc,,);
--enable_query_log
--disable_warnings
connect(con13,localhost,user1,abc,,);
select user(), current_user();
connect(con14,localhost,user2,abc,,,,SKIPSECUREAUTH);
select user(), current_user();
connect(con15,localhost,user3,abc,,);
select user(), current_user();
--enable_warnings
## Disconnecting the open sessions and dropping the created users
--echo **** Disconnecting the open sessions and dropping the created users
connection default;
disconnect con7;
disconnect con8;
disconnect con9;
disconnect con13;
disconnect con14;
disconnect con15;
connection default;
Drop user 'user1'@'localhost';
Drop user 'user2'@'localhost';
Drop user 'user3'@'localhost';
--echo
--echo
--echo =======================================================================
--echo Checking the privillages with grant user command
--echo =======================================================================
--echo
#### Checking the password plugin assignment with grant user command
## Creating a user with respect to all the 3 password authentication plugin
--echo **** Creating user with mysql_native_password plugin
set @@session.old_passwords=0;
GRANT select on mysql.* to 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
--echo **** Creating user with mysql_old_password plugin
set @@session.old_passwords=1;
GRANT select on mysql.* to 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password';
SET PASSWORD for 'Tanjotuser2'@'localhost' = password('abc');
--echo **** Creating user with sha256_password plugin
set @@session.old_passwords=2;
GRANT select on mysql.* to 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
SET PASSWORD for 'Tanjotuser3'@'localhost' = password('abc');
## Granting and revoking the privellages from the above users
--echo **** Validating the granted privilleges
connect(con16,localhost,Tanjotuser1,abc,,);
select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
--echo mysql_native_password Expected
connect(con17,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
select plugin from mysql.user where user='Tanjotuser2' and host='localhost';
--echo mysql_old_password Expected
connect(con18,localhost,Tanjotuser3,abc,,);
select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
--echo sha256_password Expected
# Revoking select on mysql database
--echo **** Revoking select on mysql database
connection default;
Revoke select on mysql.* from 'Tanjotuser1'@'localhost';
Revoke select on mysql.* from 'Tanjotuser2'@'localhost';
Revoke select on mysql.* from 'Tanjotuser3'@'localhost';
--echo **** Validating the Revoked privilleges
connection con16;
--error ER_TABLEACCESS_DENIED_ERROR
select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
select user(), current_user();
connection con17;
--error ER_TABLEACCESS_DENIED_ERROR
select plugin from mysql.user where user='Tanjotuser2' and host='localhost';
select user(), current_user();
connection con18;
--error ER_TABLEACCESS_DENIED_ERROR
select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
select user(), current_user();
## Granting and revoking the privellages from the above users
connection default;
Grant usage on mysql.* to 'Tanjotuser1'@'localhost';
Grant usage on mysql.* to 'Tanjotuser2'@'localhost';
Grant usage on mysql.* to 'Tanjotuser3'@'localhost';
--echo **** Validating the granted privilleges
connection con16;
--error ER_TABLEACCESS_DENIED_ERROR
select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
select user(), current_user();
connection con17;
--error ER_TABLEACCESS_DENIED_ERROR
select plugin from mysql.user where user='Tanjotuser2' and host='localhost';
select user(), current_user();
connection con18;
--error ER_TABLEACCESS_DENIED_ERROR
select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
select user(), current_user();
# Granting All on mysql database
--echo **** Validating the granted privilleges
connection default;
Grant All on mysql.* to 'Tanjotuser1'@'localhost';
Grant All on mysql.* to 'Tanjotuser2'@'localhost';
Grant All on mysql.* to 'Tanjotuser3'@'localhost';
connect(con50,localhost,Tanjotuser1,abc,,);
select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
--echo mysql_native_password Expected
connect(con51,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
select plugin from mysql.user where user='Tanjotuser2' and host='localhost';
--echo mysql_old_password Expected
connect(con52,localhost,Tanjotuser3,abc,,);
select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
--echo sha256_password Expected
## Disconnecting the open sessions and dropping the created users
--echo **** Disconnecting the open sessions and dropping the created users
connection default;
disconnect con16;
disconnect con17;
disconnect con18;
disconnect con50;
disconnect con51;
disconnect con52;
connection default;
Drop user 'Tanjotuser1'@'localhost';
Drop user 'Tanjotuser2'@'localhost';
Drop user 'Tanjotuser3'@'localhost';
--echo
--echo
--echo =============================================================================
--echo Checking the password assignment using the update command on the user table
--echo =============================================================================
--echo
#### Checking the password plugin assignment and password hashing based on the update command
## Creating a user with respect to all the 3 password authentication plugin
--echo **** Creating user with mysql_native_password plugin
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
set @@session.old_passwords=0;
UPDATE mysql.user SET Password=PASSWORD('abc') WHERE User='Tanjotuser1' AND Host='localhost';
FLUSH PRIVILEGES;
--echo **** Creating user with mysql_old_password plugin
CREATE USER 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password';
set @@session.old_passwords=1;
UPDATE mysql.user SET Password=PASSWORD('abc') WHERE User='Tanjotuser2' AND Host='localhost';
FLUSH PRIVILEGES;
--echo **** Creating user with sha256_password plugin
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
set @@session.old_passwords=2;
UPDATE mysql.user SET Password=PASSWORD('abc') WHERE User='Tanjotuser3' AND Host='localhost';
FLUSH PRIVILEGES;
connect(con19,localhost,Tanjotuser1,abc,,);
select user(), current_user();
set password=password('abcd');
connect(con20,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
select user(), current_user();
set password=password('abcd');
# Below section is hashed out till Bug #16050188 is fixed
#connect(con21,localhost,Tanjotuser3,abc,,);
#select user(), current_user();
#set password=password('abcd');
connect(con22,localhost,Tanjotuser1,abcd,,);
select user(), current_user();
connect(con23,localhost,Tanjotuser2,abcd,,,,SKIPSECUREAUTH);
select user(), current_user();
#connect(con24,localhost,Tanjotuser3,abcd,,);
#select user(), current_user();
## Disconnecting the open sessions and dropping the created users
--echo **** Disconnecting the open sessions and dropping the created users
connection default;
disconnect con19;
disconnect con20;
#disconnect con21;
disconnect con22;
disconnect con23;
#disconnect con24;
connection default;
Drop user 'Tanjotuser1'@'localhost';
Drop user 'Tanjotuser2'@'localhost';
Drop user 'Tanjotuser3'@'localhost';
--echo
--echo
--echo ===================================================================================================
--echo Starting the server with default auth as sha256_password and checking the above validations again.
--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
--echo
--echo
--echo =======================================================================
--echo Checking the password plugin assignment with create user command
--echo =======================================================================
--echo
#### Checking the password plugin assignment with create user command
## Creating a user with respect to all the 3 password authentication plugin
--disable_warnings
set @@global.secure_auth=0;
--enable_warnings
--echo **** Creating user with mysql_native_password plugin
select @@session.old_passwords;
--echo 0 Expected
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
SET PASSWORD for 'Tanjotuser1'@'localhost' = password('abc');
--echo **** Creating user with mysql_old_password plugin
set @@session.old_passwords=1;
CREATE USER 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password';
SET PASSWORD for 'Tanjotuser2'@'localhost' = password('abc');
--echo **** Creating user with sha256_password plugin
set @@session.old_passwords=2;
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
SET PASSWORD for 'Tanjotuser3'@'localhost' = password('abc');
## Validating the plugin assigned for the above 3 users in the user table
--echo **** Validating the plugin names
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='localhost')='mysql_old_password';
--echo 1 Expected
select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
--echo 1 Expected
## Dropping the created users
Drop user 'Tanjotuser1'@'localhost';
Drop user 'Tanjotuser2'@'localhost';
Drop user 'Tanjotuser3'@'localhost';
--echo
--echo
--echo =======================================================================
--echo Checking the password plugin assignment with grant user command
--echo =======================================================================
--echo
#### Checking the password plugin assignment with grant user command
## Creating a user with respect to all the 3 password authentication plugin
--echo **** Creating user with mysql_native_password plugin
set @@session.old_passwords=0;
GRANT usage on mysql.* to 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
SET PASSWORD for 'Tanjotuser1'@'localhost' = password('abc');
--echo **** Creating user with mysql_old_password plugin
set @@session.old_passwords=1;
GRANT usage on mysql.* to 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password';
SET PASSWORD for 'Tanjotuser2'@'localhost' = password('abc');
--echo **** Creating user with sha256_password plugin
set @@session.old_passwords=2;
GRANT usage on mysql.* to 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
SET PASSWORD for 'Tanjotuser3'@'localhost' = password('abc');
## Validating the plugin assigned for the above 3 users in the user table
--echo **** Validating the plugin names
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='localhost')='mysql_old_password';
--echo 1 Expected
select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
--echo 1 Expected
## Dropping the created users
Drop user 'Tanjotuser1'@'localhost';
Drop user 'Tanjotuser2'@'localhost';
Drop user 'Tanjotuser3'@'localhost';
--echo
--echo
--echo =======================================================================
--echo Checking the functionality and integrity of the DROP user command
--echo =======================================================================
--echo
#### Checking the functionality and integrity of the DROP user command
## Creating a user with respect to all the 3 password authentication plugin
--echo **** Creating user with mysql_native_password plugin
set @@session.old_passwords=0;
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
--echo **** Creating user with mysql_old_password plugin
set @@session.old_passwords=1;
CREATE USER 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password';
SET PASSWORD for 'Tanjotuser2'@'localhost' = password('abc');
--echo **** Creating user with sha256_password plugin
set @@session.old_passwords=2;
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
SET PASSWORD for 'Tanjotuser3'@'localhost' = password('abc');
## Creating a user with respect to all the 3 password authentication plugin
--echo Connecting a session each with the above 3 users
--disable_warnings
connect(con1,localhost,Tanjotuser1,abc,,);
connect(con2,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
connect(con3,localhost,Tanjotuser3,abc,,);
--enable_warnings
## Now dropping the above 3 users with each having a open session
--echo dropping the above 3 users with each having a open session
connection default;
Drop user 'Tanjotuser1'@'localhost';
Drop user 'Tanjotuser2'@'localhost';
Drop user 'Tanjotuser3'@'localhost';
## Now trying opening a new session for the above dropped user
--echo Now trying opening a new session for the above dropped user
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con4,localhost,Tanjotuser1,abc,,);
--enable_query_log
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con5,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
--enable_query_log
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con6,localhost,Tanjotuser3,abc,,);
--enable_query_log
## Accessing the open sessions for the above dropped user
--echo Accessing the open sessions for the above dropped user
connection con1;
select user(), current_user();
connection con2;
select user(), current_user();
connection con3;
select user(), current_user();
disconnect con1;
disconnect con2;
disconnect con3;
--echo
--echo
--echo =======================================================================
--echo Checking the privellages post renaming the user
--echo =======================================================================
--echo
#### Checking the rename user command
## Creating a user with respect to all the 3 password authentication plugin
connection default;
--echo **** Creating user with mysql_native_password plugin
set @@session.old_passwords=0;
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
SET PASSWORD for 'Tanjotuser1'@'localhost' = password('abc');
--echo **** Creating user with mysql_old_password plugin
set @@session.old_passwords=1;
CREATE USER 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password';
SET PASSWORD for 'Tanjotuser2'@'localhost' = password('abc');
--echo **** Creating user with sha256_password plugin
set @@session.old_passwords=2;
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
SET PASSWORD for 'Tanjotuser3'@'localhost' = password('abc');
## Validating the plugin assigned for the above 3 users in the user table
--echo **** Validating the plugin names
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='localhost')='mysql_old_password';
--echo 1 Expected
select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
--echo 1 Expected
## Connecting a session to each of the above created users
--disable_warnings
connect(con7,localhost,Tanjotuser1,abc,,);
select user(), current_user();
connect(con8,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
select user(), current_user();
connect(con9,localhost,Tanjotuser3,abc,,);
select user(), current_user();
--enable_warnings
## Validating the integrity of the rename user command
connection default;
--echo **** Validating the integrity of the rename user command
RENAME USER 'Tanjotuser1'@'localhost' to 'user1'@'localhost';
RENAME USER 'Tanjotuser2'@'localhost' to 'user2'@'localhost';
RENAME USER 'Tanjotuser3'@'localhost' to 'user3'@'localhost';
## Validating the plugin assigned for the renamed users in the user table
--echo **** Validating the plugin names
select (select plugin from mysql.user where User='user1' and Host='localhost')='mysql_native_password';
--echo 1 Expected
select (select plugin from mysql.user where User='user2' and Host='localhost')='mysql_old_password';
--echo 1 Expected
select (select plugin from mysql.user where User='user3' and Host='localhost')='sha256_password';
--echo 1 Expected
## Validating the users post renaming
--echo **** Validating the user connections post renaming
connection con7;
select user(), current_user();
connection con8;
select user(), current_user();
connection con9;
select user(), current_user();
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con10,localhost,Tanjotuser1,abc,,);
--enable_query_log
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con11,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
--enable_query_log
--replace_result $MASTER_MYSOCK MASTER_MYSOCK
--disable_query_log
--error ER_ACCESS_DENIED_ERROR
connect(con12,localhost,Tanjotuser3,abc,,);
--enable_query_log
--disable_warnings
connect(con13,localhost,user1,abc,,);
select user(), current_user();
connect(con14,localhost,user2,abc,,,,SKIPSECUREAUTH);
select user(), current_user();
connect(con15,localhost,user3,abc,,);
select user(), current_user();
--enable_warnings
## Disconnecting the open sessions and dropping the created users
--echo **** Disconnecting the open sessions and dropping the created users
connection default;
disconnect con7;
disconnect con8;
disconnect con9;
disconnect con13;
disconnect con14;
disconnect con15;
connection default;
Drop user 'user1'@'localhost';
Drop user 'user2'@'localhost';
Drop user 'user3'@'localhost';
--echo
--echo
--echo =======================================================================
--echo Checking the privillages with grant user command
--echo =======================================================================
--echo
#### Checking the password plugin assignment with grant user command
## Creating a user with respect to all the 3 password authentication plugin
--echo **** Creating user with mysql_native_password plugin
set @@session.old_passwords=0;
GRANT select on mysql.* to 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
SET PASSWORD for 'Tanjotuser1'@'localhost' = password('abc');
--echo **** Creating user with mysql_old_password plugin
set @@session.old_passwords=1;
GRANT select on mysql.* to 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password';
SET PASSWORD for 'Tanjotuser2'@'localhost' = password('abc');
--echo **** Creating user with sha256_password plugin
set @@session.old_passwords=2;
GRANT select on mysql.* to 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
SET PASSWORD for 'Tanjotuser3'@'localhost' = password('abc');
## Granting and revoking the privellages from the above users
--echo **** Validating the granted privilleges
connect(con16,localhost,Tanjotuser1,abc,,);
select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
--echo mysql_native_password Expected
connect(con17,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
select plugin from mysql.user where user='Tanjotuser2' and host='localhost';
--echo mysql_old_password Expected
connect(con18,localhost,Tanjotuser3,abc,,);
select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
--echo sha256_password Expected
# Revoking select on mysql database
--echo **** Revoking select on mysql database
connection default;
Revoke select on mysql.* from 'Tanjotuser1'@'localhost';
Revoke select on mysql.* from 'Tanjotuser2'@'localhost';
Revoke select on mysql.* from 'Tanjotuser3'@'localhost';
--echo **** Validating the Revoked privilleges
connection con16;
--error ER_TABLEACCESS_DENIED_ERROR
select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
select user(), current_user();
connection con17;
--error ER_TABLEACCESS_DENIED_ERROR
select plugin from mysql.user where user='Tanjotuser2' and host='localhost';
select user(), current_user();
connection con18;
--error ER_TABLEACCESS_DENIED_ERROR
select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
select user(), current_user();
## Granting and revoking the privellages from the above users
connection default;
Grant usage on mysql.* to 'Tanjotuser1'@'localhost';
Grant usage on mysql.* to 'Tanjotuser2'@'localhost';
Grant usage on mysql.* to 'Tanjotuser3'@'localhost';
--echo **** Validating the granted privilleges
connection con16;
--error ER_TABLEACCESS_DENIED_ERROR
select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
select user(), current_user();
connection con17;
--error ER_TABLEACCESS_DENIED_ERROR
select plugin from mysql.user where user='Tanjotuser2' and host='localhost';
select user(), current_user();
connection con18;
--error ER_TABLEACCESS_DENIED_ERROR
select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
select user(), current_user();
# Granting All on mysql database
--echo **** Validating the granted privilleges
connection default;
Grant All on mysql.* to 'Tanjotuser1'@'localhost';
Grant All on mysql.* to 'Tanjotuser2'@'localhost';
Grant All on mysql.* to 'Tanjotuser3'@'localhost';
connect(con50,localhost,Tanjotuser1,abc,,);
select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
--echo mysql_native_password Expected
connect(con51,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
select plugin from mysql.user where user='Tanjotuser2' and host='localhost';
--echo mysql_old_password Expected
connect(con52,localhost,Tanjotuser3,abc,,);
select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
--echo sha256_password Expected
## Disconnecting the open sessions and dropping the created users
--echo **** Disconnecting the open sessions and dropping the created users
connection default;
disconnect con16;
disconnect con17;
disconnect con18;
disconnect con50;
disconnect con51;
disconnect con52;
connection default;
Drop user 'Tanjotuser1'@'localhost';
Drop user 'Tanjotuser2'@'localhost';
Drop user 'Tanjotuser3'@'localhost';
--echo
--echo
--echo =============================================================================
--echo Checking the password assignment using the update command on the user table
--echo =============================================================================
--echo
#### Checking the password plugin assignment and password hashing based on the update command
## Creating a user with respect to all the 3 password authentication plugin
--echo **** Creating user with mysql_native_password plugin
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
set @@session.old_passwords=0;
UPDATE mysql.user SET Password=PASSWORD('abc') WHERE User='Tanjotuser1' AND Host='localhost';
FLUSH PRIVILEGES;
--echo **** Creating user with mysql_old_password plugin
CREATE USER 'Tanjotuser2'@'localhost' IDENTIFIED WITH 'mysql_old_password';
set @@session.old_passwords=1;
UPDATE mysql.user SET Password=PASSWORD('abc') WHERE User='Tanjotuser2' AND Host='localhost';
FLUSH PRIVILEGES;
--echo **** Creating user with sha256_password plugin
CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
set @@session.old_passwords=2;
UPDATE mysql.user SET Password=PASSWORD('abc') WHERE User='Tanjotuser3' AND Host='localhost';
FLUSH PRIVILEGES;
connect(con19,localhost,Tanjotuser1,abc,,);
select user(), current_user();
set password=password('abcd');
connect(con20,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
select user(), current_user();
set password=password('abcd');
# Below section is hashed out till Bug #16050188 is fixed
#connect(con21,localhost,Tanjotuser3,abc,,);
#select user(), current_user();
#set password=password('abcd');
connect(con22,localhost,Tanjotuser1,abcd,,);
select user(), current_user();
connect(con23,localhost,Tanjotuser2,abcd,,,,SKIPSECUREAUTH);
select user(), current_user();
#connect(con24,localhost,Tanjotuser3,abcd,,);
#select user(), current_user();
## Disconnecting the open sessions and dropping the created users
--echo **** Disconnecting the open sessions and dropping the created users
connection default;
disconnect con19;
disconnect con20;
#disconnect con21;
disconnect con22;
disconnect con23;
#disconnect con24;
connection default;
Drop user 'Tanjotuser1'@'localhost';
Drop user 'Tanjotuser2'@'localhost';
Drop user 'Tanjotuser3'@'localhost';
## Final Cleanup
set @@session.old_passwords=default;