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 : access_credential_control.test

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

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

set @@session.old_passwords=2;

CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
SET PASSWORD for 'Tanjotuser3'@'localhost' = '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='Tanjotuser3' and Host='localhost')='sha256_password';
--echo 1 Expected

## Dropping the created users

Drop user 'Tanjotuser1'@'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 sha256_password plugin

set @@session.old_passwords=2;
GRANT usage on mysql.* to 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password' BY '123';
SET PASSWORD for 'Tanjotuser3'@'localhost' = '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='Tanjotuser3' and Host='localhost')='sha256_password';
--echo 1 Expected

## Dropping the created users

Drop user 'Tanjotuser1'@'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 sha256_password plugin

set @@session.old_passwords=2;

CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
SET PASSWORD for 'Tanjotuser3'@'localhost' = '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(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 '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(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 con3;
select user(), current_user();

disconnect con1;
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 sha256_password plugin

set @@session.old_passwords=2;

CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
SET PASSWORD for 'Tanjotuser3'@'localhost' = '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='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(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 '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='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 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(con12,localhost,Tanjotuser3,abc,,);
--enable_query_log

--disable_warnings
connect(con13,localhost,user1,abc,,);
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 con9;
disconnect con13;
disconnect con15;

connection default;

Drop user 'user1'@'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 sha256_password plugin

set @@session.old_passwords=2;
GRANT select on mysql.* to 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password' BY '123';
SET PASSWORD for 'Tanjotuser3'@'localhost' = '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(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 '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 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 '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 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 'Tanjotuser3'@'localhost';

connect(con50,localhost,Tanjotuser1,abc,,);
select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
--echo mysql_native_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 con18;

disconnect con50;
disconnect con52;

connection default;

Drop user 'Tanjotuser1'@'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
--disable_warnings
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
set @@session.old_passwords=0;
UPDATE mysql.user SET Authentication_string=PASSWORD('abc') WHERE User='Tanjotuser1' 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 IGNORE mysql.user SET Authentication_string=PASSWORD('abc') WHERE User='Tanjotuser3' AND Host='localhost';
FLUSH PRIVILEGES;
--enable_warnings

connect(con19,localhost,Tanjotuser1,abc,,);
select user(), current_user();
set password='abcd';

# Below section is hashed out till Bug #16050188 is fixed

#connect(con21,localhost,Tanjotuser3,abc,,);
#select user(), current_user();

connect(con22,localhost,Tanjotuser1,abcd,,);
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 con21;
disconnect con22;
#disconnect con24;

connection default;

Drop user 'Tanjotuser1'@'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 
--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

#### Checking the password plugin assignment with create user command

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

--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' = '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' = '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='Tanjotuser3' and Host='localhost')='sha256_password';
--echo 1 Expected

## Dropping the created users

Drop user 'Tanjotuser1'@'localhost';
Drop user 'Tanjotuser3'@'localhost';

#### 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' BY '123';
SET PASSWORD for 'Tanjotuser1'@'localhost' = 'abc';

--echo **** Creating user with sha256_password plugin

set @@session.old_passwords=2;
GRANT usage on mysql.* to 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password' BY '123';
SET PASSWORD for 'Tanjotuser3'@'localhost' = '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='Tanjotuser3' and Host='localhost')='sha256_password';
--echo 1 Expected

## Dropping the created users

Drop user 'Tanjotuser1'@'localhost';
Drop user 'Tanjotuser3'@'localhost';

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

set @@session.old_passwords=2;

CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
SET PASSWORD for 'Tanjotuser3'@'localhost' = '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(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 '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(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 con3;
select user(), current_user();

disconnect con1;
disconnect con3;

#### 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' = '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' = '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='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(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 '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='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 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(con12,localhost,Tanjotuser3,abc,,);
--enable_query_log

--disable_warnings
connect(con13,localhost,user1,abc,,);
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 con9;
disconnect con13;
disconnect con15;

connection default;

Drop user 'user1'@'localhost';
Drop user 'user3'@'localhost';

#### 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' BY '123';
SET PASSWORD for 'Tanjotuser1'@'localhost' = 'abc';

--echo **** Creating user with sha256_password plugin

set @@session.old_passwords=2;
GRANT select on mysql.* to 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password' BY '123';
SET PASSWORD for 'Tanjotuser3'@'localhost' = '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(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 '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 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 '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 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 'Tanjotuser3'@'localhost';

connect(con50,localhost,Tanjotuser1,abc,,);
select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
--echo mysql_native_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 con18;

disconnect con50;
disconnect con52;

connection default;

Drop user 'Tanjotuser1'@'localhost';
Drop user 'Tanjotuser3'@'localhost';

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

connect(con19,localhost,Tanjotuser1,abc,,);
select user(), current_user();
set password='abcd';

# Below section is hashed out till Bug #16050188 is fixed

#connect(con21,localhost,Tanjotuser3,abc,,);
#select user(), current_user();

connect(con22,localhost,Tanjotuser1,abcd,,);
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 con21;
disconnect con22;
#disconnect con24;

connection default;

Drop user 'Tanjotuser1'@'localhost';
Drop user 'Tanjotuser3'@'localhost';

## Final Cleanup
set @@session.old_passwords=default;

${this.title}

Code Editor : access_credential_control.test