[ Avaa Bypassed ]

Upload:

Command:

hmhc3928@3.144.28.90: ~ $ 
###############################################################################
#                                                                             #
# Password expiry scenarios catering to all the authentication plugin         #
# and handled by the flag "password_expired" in the mysql.user table          #
#                                                                             #
#                                                                             #
#                                                                             #
# Creation Date: 2012-12-28                                                   #
# Author : Tanjot Singh Uppal                                                 #
#                                                                             #
#                                                                             #
# Description:Test Cases of password expiry validated the access to the       # 
# users under scenarios with expired password.                                #
#                                                                             #
###############################################################################

--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

## By default the server is started with the mysql_native_password plugin.

--echo
--echo 
--echo =======================================================================================
--echo Checking the password expiry with the users created with all 3 plugable authentication
--echo =======================================================================================
--echo

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

## Creating a user with respect to all the 3 password authentication plugin

--echo Creating a user with respect to all the 3 password authentication plugin


# User with mysql_native_password plugin
--echo **** Creating user with mysql_native_password plugin

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

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


# User with mysql_old_password plugin
--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');

# User with sha256_password plugin
--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


## Validating the password expiry flag in the mysql.user table

--echo **** Validating the password expiry flag in the mysql.user table


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

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

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


## Expirying password from user login for the 3 created users

--echo **** Expirying password from root login for the 3 created users


--disable_warnings
connect(con1,localhost,Tanjotuser1,abc,,);
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
Alter user 'Tanjotuser1'@'localhost' password expire;
select 1;
--echo 1 Expected
--enable_warnings


--disable_warnings
connect(con2,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
Alter user 'Tanjotuser2'@'localhost' password expire;
select 1;
--echo 1 Expected
--enable_warnings


--disable_warnings
connect(con3,localhost,Tanjotuser3,abc,,);
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
Alter user 'Tanjotuser3'@'localhost' password expire;
select 1;
--echo 1 Expected
--enable_warnings



## The password expiry flag in the mysql.user table should not be altered

--echo **** Validating the password expiry flag in the mysql.user table should not be altered

connection default;

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

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

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



## These 3 users still should be able to login and work properly

--echo **** These 3 users still should be able to login and work properly

--disable_warnings
connect(con4,localhost,Tanjotuser1,abc,,);
select 1;
--echo 1 Expected

connect(con5,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
select 1;
--echo 1 Expected

connect(con6,localhost,Tanjotuser3,abc,,);
select 1;
--echo 1 Expected

--enable_warnings

## Disconnecting the last 3 connections

disconnect con4;
disconnect con5;
disconnect con6;

## Expirying password from root login for the 3 created users using Alter user

--echo **** Expirying password from root login for the 3 created users using Alter user

connection default;

Alter user 'Tanjotuser1'@'localhost' password expire;

Alter user 'Tanjotuser2'@'localhost' password expire;

Alter user 'Tanjotuser3'@'localhost' password expire;


## Validating the password expiry flag in the mysql.user table

--echo **** Validating the password expiry flag in the mysql.user table


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

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

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


## checking user access after password expiry

--echo **** checking user access after password expiry

--disable_warnings

connect(con7,localhost,Tanjotuser1,abc,,);
--error ER_MUST_CHANGE_PASSWORD
select 1;


connect(con8,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
--error ER_MUST_CHANGE_PASSWORD
select 1;


connect(con9,localhost,Tanjotuser3,abc,,);
--error ER_MUST_CHANGE_PASSWORD
select 1;

--enable_warnings


## At the same time the open sessions for these users should able to work properly 

--echo At the same time the open sessions for these users should able to work properly.

connection con1;

select 1;
--echo 1 Expected


connection con2;

select 1;
--echo 1 Expected


connection con3;

select 1;
--echo 1 Expected


## setting passwords from the new connections.

--echo setting passwords from the new connections.

connection con7;

set @@session.old_passwords=1;
--error ER_PASSWD_LENGTH
set password=password('abcd');

--error ER_MUST_CHANGE_PASSWORD
select 1;

set @@session.old_passwords=2;
--error ER_PASSWD_LENGTH
set password=password('abcd');

--error ER_MUST_CHANGE_PASSWORD
select 1;

set @@session.old_passwords=0;
set password=password('abcd');

select 1;
--echo 1 Expected

connection default;

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


connection con8;

set @@session.old_passwords=0;
--error ER_PASSWD_LENGTH
set password=password('abcd');

--error ER_MUST_CHANGE_PASSWORD
select 1;

set @@session.old_passwords=2;
--error ER_PASSWD_LENGTH
set password=password('abcd');

--error ER_MUST_CHANGE_PASSWORD
select 1;

set @@session.old_passwords=1;
set password=password('abcd');

select 1;
--echo 1 Expected

connection default;

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


connection con9;

set @@session.old_passwords=0;
--error ER_PASSWORD_FORMAT
set password=password('abcd');

--error ER_MUST_CHANGE_PASSWORD
select 1;

set @@session.old_passwords=1;
--error ER_PASSWORD_FORMAT
set password=password('abcd');

--error ER_MUST_CHANGE_PASSWORD
select 1;

set @@session.old_passwords=2;
set password=password('abcd');

select 1;
--echo 1 Expected

connection default;

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



## logging the 3 users with the new passwords

--echo **** logging the 3 users with the new passwords


--disable_warnings

connect(con10,localhost,Tanjotuser1,abcd,,);
select 1;
--echo 1 Expected

connect(con11,localhost,Tanjotuser2,abcd,,,,SKIPSECUREAUTH);
select 1;
--echo 1 Expected

connect(con12,localhost,Tanjotuser3,abcd,,);
select 1;
--echo 1 Expected

--enable_warnings


## Disconnecting the open sessions and dropping the created users.

connection default;

--echo Disconnecting the open sessions and dropping the created users

disconnect con1;
disconnect con2;
disconnect con3;

disconnect con7;
disconnect con8;
disconnect con9;

disconnect con10;
disconnect con11;
disconnect con12;

drop user 'Tanjotuser1'@'localhost';
drop user 'Tanjotuser2'@'localhost';
drop user 'Tanjotuser3'@'localhost';


--echo
--echo 
--echo =======================================================================================
--echo Checking the password expiry using the update command on mysql.user table
--echo =======================================================================================
--echo


## Creating a user with respect to all the 3 password authentication plugin

--echo Creating a user with respect to all the 3 password authentication plugin

connection default;

# User with mysql_native_password plugin
--echo **** Creating user with mysql_native_password plugin

set @@session.old_passwords=0;
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';


# User with mysql_old_password plugin
--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');

# User with sha256_password plugin
--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


## Validating the password expiry flag in the mysql.user table

--echo **** Validating the password expiry flag in the mysql.user table


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

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

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


## Making connections from each of these created users

--echo **** Making connections from each of these created users

--disable_warnings

connect(con13,localhost,Tanjotuser1,abc,,);
select 1;
--echo 1 Expected

connect(con14,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
select 1;
--echo 1 Expected

connect(con15,localhost,Tanjotuser3,abc,,);
select 1;
--echo 1 Expected

--enable_warnings



## Expirying password from user login for the 3 created users using update command

--echo **** Expirying password from root login for the 3 created users using update command

connection default;

update mysql.user set password_expired='Y' where User='Tanjotuser1' and Host='localhost';

update mysql.user set password_expired='Y' where User='Tanjotuser2' and Host='localhost';

update mysql.user set password_expired='Y' where User='Tanjotuser3' and Host='localhost';


## connecting client before flush privileges

--echo **** connecting client before flush privileges

--disable_warnings

connect(con16,localhost,Tanjotuser1,abc,,);
select 1;
--echo 1 Expected

connect(con17,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
select 1;
--echo 1 Expected

connect(con18,localhost,Tanjotuser3,abc,,);
select 1;
--echo 1 Expected

--enable_warnings


## flush privileges

--echo **** flush privileges

connection default;
flush privileges;


## connecting client after flush privileges

--echo **** connecting client after flush privileges

--disable_warnings

connect(con19,localhost,Tanjotuser1,abc,,);
--error ER_MUST_CHANGE_PASSWORD
select 1;

connect(con20,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
--error ER_MUST_CHANGE_PASSWORD
select 1;

connect(con21,localhost,Tanjotuser3,abc,,);
--error ER_MUST_CHANGE_PASSWORD
select 1;

--enable_warnings


## checking the previous open connections

--echo **** checking the previous open connections


connection con13;
select 1;
--echo 1 Expected

connection con14;
select 1;
--echo 1 Expected

connection con15;
select 1;
--echo 1 Expected


## Resetting the password

--echo **** Resetting the password

connection con16;

set @@session.old_passwords=1;
--error ER_PASSWD_LENGTH
set password=password('abcd');

select 1;
--echo 1 Expected

set @@session.old_passwords=2;
--error ER_PASSWD_LENGTH
set password=password('abcd');

select 1;
--echo 1 Expected

set @@session.old_passwords=0;
set password=password('abcd');

select 1;
--echo 1 Expected

connection default;

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


connection con17;


set @@session.old_passwords=0;
--error ER_PASSWD_LENGTH
set password=password('abcd');

select 1;
--echo 1 Expected

set @@session.old_passwords=2;
--error ER_PASSWD_LENGTH
set password=password('abcd');

select 1;
--echo 1 Expected

set @@session.old_passwords=1;
set password=password('abcd');

select 1;
--echo 1 Expected

connection default;

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

 
connection con18;


set @@session.old_passwords=0;
--error ER_PASSWORD_FORMAT
set password=password('abcd');

select 1;
--echo 1 Expected

set @@session.old_passwords=1;
--error ER_PASSWORD_FORMAT
set password=password('abcd');

select 1;
--echo 1 Expected

set @@session.old_passwords=2;
set password=password('abcd');

select 1;
--echo 1 Expected

connection default;

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


## Logging with the new password

--echo **** Logging with the new password


--disable_warnings

connect(con22,localhost,Tanjotuser1,abcd,,);

select 1;
--echo 1 Expected

connect(con23,localhost,Tanjotuser2,abcd,,,,SKIPSECUREAUTH);

select 1;
--echo 1 Expected

connect(con24,localhost,Tanjotuser3,abcd,,);

select 1;
--echo 1 Expected

--enable_warnings

## Below section is hashed till Bug #16054065 is fixed

## connecting client after resetting the password

--echo **** connecting client after resetting the password

#--disable_warnings

#connection con19;
#select 1;
#--echo 1 Expected

#connection con20;
#select 1;
#--echo 1 Expected

#connection con21;
#select 1;
#--echo 1 Expected

#--enable_warnings


## Disconnecting the open sessions and dropping the created users.

connection default;

--echo Disconnecting the open sessions and dropping the created users

disconnect con13;
disconnect con14;
disconnect con15;

disconnect con16;
disconnect con17;
disconnect con18;

disconnect con19;
disconnect con20;
disconnect con21;

disconnect con22;
disconnect con23;
disconnect con24;

drop user 'Tanjotuser1'@'localhost';
drop user 'Tanjotuser2'@'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 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 expiry with the users created with all 3 plugable authentication
--echo =======================================================================================
--echo

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

## Creating a user with respect to all the 3 password authentication plugin

--echo Creating a user with respect to all the 3 password authentication plugin


# User with mysql_native_password plugin
--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');

# User with mysql_old_password plugin
--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');

# User with sha256_password plugin
--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


## Validating the password expiry flag in the mysql.user table

--echo **** Validating the password expiry flag in the mysql.user table


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

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

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


## Expirying password from user login for the 3 created users

--echo **** Expirying password from root login for the 3 created users


--disable_warnings
connect(con1,localhost,Tanjotuser1,abc,,);
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
Alter user 'Tanjotuser1'@'localhost' password expire;
select 1;
--echo 1 Expected
--enable_warnings


--disable_warnings
connect(con2,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
Alter user 'Tanjotuser2'@'localhost' password expire;
select 1;
--echo 1 Expected
--enable_warnings


--disable_warnings
connect(con3,localhost,Tanjotuser3,abc,,);
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
Alter user 'Tanjotuser3'@'localhost' password expire;
select 1;
--echo 1 Expected
--enable_warnings



## The password expiry flag in the mysql.user table should not be altered

--echo **** Validating the password expiry flag in the mysql.user table should not be altered

connection default;

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

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

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



## These 3 users still should be able to login and work properly

--echo **** These 3 users still should be able to login and work properly

--disable_warnings
connect(con4,localhost,Tanjotuser1,abc,,);
select 1;
--echo 1 Expected

connect(con5,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
select 1;
--echo 1 Expected

connect(con6,localhost,Tanjotuser3,abc,,);
select 1;
--echo 1 Expected

--enable_warnings

## Disconnecting the last 3 connections

disconnect con4;
disconnect con5;
disconnect con6;

## Expirying password from root login for the 3 created users using Alter user

--echo **** Expirying password from root login for the 3 created users using Alter user

connection default;

Alter user 'Tanjotuser1'@'localhost' password expire;

Alter user 'Tanjotuser2'@'localhost' password expire;

Alter user 'Tanjotuser3'@'localhost' password expire;


## Validating the password expiry flag in the mysql.user table

--echo **** Validating the password expiry flag in the mysql.user table


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

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

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


## checking user access after password expiry

--echo **** checking user access after password expiry

--disable_warnings

connect(con7,localhost,Tanjotuser1,abc,,);
--error ER_MUST_CHANGE_PASSWORD
select 1;


connect(con8,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
--error ER_MUST_CHANGE_PASSWORD
select 1;


connect(con9,localhost,Tanjotuser3,abc,,);
--error ER_MUST_CHANGE_PASSWORD
select 1;

--enable_warnings


## At the same time the open sessions for these users should able to work properly 

--echo At the same time the open sessions for these users should able to work properly.

connection con1;

select 1;
--echo 1 Expected


connection con2;

select 1;
--echo 1 Expected


connection con3;

select 1;
--echo 1 Expected


## setting passwords from the new connections.

--echo setting passwords from the new connections.

connection con7;

set @@session.old_passwords=1;
--error ER_PASSWD_LENGTH
set password=password('abcd');

--error ER_MUST_CHANGE_PASSWORD
select 1;

set @@session.old_passwords=2;
--error ER_PASSWD_LENGTH
set password=password('abcd');

--error ER_MUST_CHANGE_PASSWORD
select 1;

set @@session.old_passwords=0;
set password=password('abcd');

select 1;
--echo 1 Expected

connection default;

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


connection con8;

set @@session.old_passwords=0;
--error ER_PASSWD_LENGTH
set password=password('abcd');

--error ER_MUST_CHANGE_PASSWORD
select 1;

set @@session.old_passwords=2;
--error ER_PASSWD_LENGTH
set password=password('abcd');

--error ER_MUST_CHANGE_PASSWORD
select 1;

set @@session.old_passwords=1;
set password=password('abcd');

select 1;
--echo 1 Expected

connection default;

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


connection con9;

set @@session.old_passwords=0;
--error ER_PASSWORD_FORMAT
set password=password('abcd');

--error ER_MUST_CHANGE_PASSWORD
select 1;

set @@session.old_passwords=1;
--error ER_PASSWORD_FORMAT
set password=password('abcd');

--error ER_MUST_CHANGE_PASSWORD
select 1;

set @@session.old_passwords=2;
set password=password('abcd');

select 1;
--echo 1 Expected

connection default;

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



## logging the 3 users with the new passwords

--echo **** logging the 3 users with the new passwords


--disable_warnings

connect(con10,localhost,Tanjotuser1,abcd,,);
select 1;
--echo 1 Expected

connect(con11,localhost,Tanjotuser2,abcd,,,,SKIPSECUREAUTH);
select 1;
--echo 1 Expected

connect(con12,localhost,Tanjotuser3,abcd,,);
select 1;
--echo 1 Expected

--enable_warnings


## Disconnecting the open sessions and dropping the created users.

connection default;

--echo Disconnecting the open sessions and dropping the created users

disconnect con1;
disconnect con2;
disconnect con3;

disconnect con7;
disconnect con8;
disconnect con9;

disconnect con10;
disconnect con11;
disconnect con12;

drop user 'Tanjotuser1'@'localhost';
drop user 'Tanjotuser2'@'localhost';
drop user 'Tanjotuser3'@'localhost';


--echo
--echo 
--echo =======================================================================================
--echo Checking the password expiry using the update command on mysql.user table
--echo =======================================================================================
--echo


## Creating a user with respect to all the 3 password authentication plugin

--echo Creating a user with respect to all the 3 password authentication plugin

connection default;

# User with mysql_native_password plugin
--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');


# User with mysql_old_password plugin
--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');

# User with sha256_password plugin
--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


## Validating the password expiry flag in the mysql.user table

--echo **** Validating the password expiry flag in the mysql.user table


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

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

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


## Making connections from each of these created users

--echo **** Making connections from each of these created users

--disable_warnings

connect(con13,localhost,Tanjotuser1,abc,,);
select 1;
--echo 1 Expected

connect(con14,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
select 1;
--echo 1 Expected

connect(con15,localhost,Tanjotuser3,abc,,);
select 1;
--echo 1 Expected

--enable_warnings



## Expirying password from user login for the 3 created users using update command

--echo **** Expirying password from root login for the 3 created users using update command

connection default;

update mysql.user set password_expired='Y' where User='Tanjotuser1' and Host='localhost';

update mysql.user set password_expired='Y' where User='Tanjotuser2' and Host='localhost';

update mysql.user set password_expired='Y' where User='Tanjotuser3' and Host='localhost';


## connecting client before flush privileges

--echo **** connecting client before flush privileges

--disable_warnings

connect(con16,localhost,Tanjotuser1,abc,,);
select 1;
--echo 1 Expected

connect(con17,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
select 1;
--echo 1 Expected

connect(con18,localhost,Tanjotuser3,abc,,);
select 1;
--echo 1 Expected

--enable_warnings


## flush privileges

--echo **** flush privileges

connection default;
flush privileges;


## connecting client after flush privileges

--echo **** connecting client after flush privileges

--disable_warnings

connect(con19,localhost,Tanjotuser1,abc,,);
--error ER_MUST_CHANGE_PASSWORD
select 1;

connect(con20,localhost,Tanjotuser2,abc,,,,SKIPSECUREAUTH);
--error ER_MUST_CHANGE_PASSWORD
select 1;

connect(con21,localhost,Tanjotuser3,abc,,);
--error ER_MUST_CHANGE_PASSWORD
select 1;

--enable_warnings


## checking the previous open connections

--echo **** checking the previous open connections


connection con13;
select 1;
--echo 1 Expected

connection con14;
select 1;
--echo 1 Expected

connection con15;
select 1;
--echo 1 Expected


## Resetting the password

--echo **** Resetting the password

connection con16;

set @@session.old_passwords=1;
--error ER_PASSWD_LENGTH
set password=password('abcd');

select 1;
--echo 1 Expected

set @@session.old_passwords=2;
--error ER_PASSWD_LENGTH
set password=password('abcd');

select 1;
--echo 1 Expected

set @@session.old_passwords=0;
set password=password('abcd');

select 1;
--echo 1 Expected

connection default;

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


connection con17;


set @@session.old_passwords=0;
--error ER_PASSWD_LENGTH
set password=password('abcd');

select 1;
--echo 1 Expected

set @@session.old_passwords=2;
--error ER_PASSWD_LENGTH
set password=password('abcd');

select 1;
--echo 1 Expected

set @@session.old_passwords=1;
set password=password('abcd');

select 1;
--echo 1 Expected

connection default;

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

 
connection con18;


set @@session.old_passwords=0;
--error ER_PASSWORD_FORMAT
set password=password('abcd');

select 1;
--echo 1 Expected

set @@session.old_passwords=1;
--error ER_PASSWORD_FORMAT
set password=password('abcd');

select 1;
--echo 1 Expected

set @@session.old_passwords=2;
set password=password('abcd');

select 1;
--echo 1 Expected

connection default;

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


## Logging with the new password

--echo **** Logging with the new password


--disable_warnings

connect(con22,localhost,Tanjotuser1,abcd,,);

select 1;
--echo 1 Expected

connect(con23,localhost,Tanjotuser2,abcd,,,,SKIPSECUREAUTH);

select 1;
--echo 1 Expected

connect(con24,localhost,Tanjotuser3,abcd,,);

select 1;
--echo 1 Expected

--enable_warnings

## Below section is hashed till Bug #16054065 is fixed

## connecting client after resetting the password

--echo **** connecting client after resetting the password

#--disable_warnings

#connection con19;
#select 1;
#--echo 1 Expected

#connection con20;
#select 1;
#--echo 1 Expected

#connection con21;
#select 1;
#--echo 1 Expected

#--enable_warnings


## Disconnecting the open sessions and dropping the created users.

connection default;

--echo Disconnecting the open sessions and dropping the created users

disconnect con13;
disconnect con14;
disconnect con15;

disconnect con16;
disconnect con17;
disconnect con18;

disconnect con19;
disconnect con20;
disconnect con21;

disconnect con22;
disconnect con23;
disconnect con24;

drop user 'Tanjotuser1'@'localhost';
drop user 'Tanjotuser2'@'localhost';
drop user 'Tanjotuser3'@'localhost';


set @@session.old_passwords=default;

Filemanager

DIR : / usr/ share/ mysql-test/ suite/ auth_sec/ t/
Name Type Size Permission Actions
access_credential_control-master.opt File 170 B 0644
access_credential_control.test File 35.48 KB 0644
cert_verify.test File 2.17 KB 0644
key_value_auth-master.opt File 170 B 0644
key_value_auth.test File 37.92 KB 0644
key_value_auth.test_old File 42.09 KB 0644
mysql_native_plugin.test File 31.23 KB 0644
mysql_no_login-master.opt File 20 B 0644
mysql_no_login.test File 4.62 KB 0644
mysql_old_passwords-master.opt File 170 B 0644
mysql_old_passwords.test File 38.19 KB 0644
mysql_old_plugin.test File 31.96 KB 0644
mysql_old_plugin.test_old File 32.12 KB 0644
mysql_sha256_plugin-master.opt File 170 B 0644
mysql_sha256_plugin.test File 30.39 KB 0644
password_expired-master.opt File 170 B 0644
password_expired.test File 31.95 KB 0644
secure_file_priv_error.test File 1.4 KB 0644
secure_file_priv_null.test File 1.82 KB 0644
secure_file_priv_warnings.test File 2.41 KB 0644
secure_file_priv_warnings_not_win.test File 1.22 KB 0644
secure_file_priv_warnings_win.test File 1.35 KB 0644
server_withssl_client_withoutssl-master.opt File 405 B 0644
server_withssl_client_withoutssl.test File 28.92 KB 0644
server_withssl_client_withssl-master.opt File 405 B 0644
server_withssl_client_withssl.test File 40.52 KB 0644