############ mysql-test\t\sha256_password_proxy_users_func.test#################
#                                                                              #
#Variable Name: sha256_password_proxy_users                                    #
#Scope: SESSION                                                                #
#Access Type: Dynamic                                                          #
#Data Type: BOOLEAN                                                            #
#Default Value: OFF                                                            #
#Values:      ON, OFF                                                          #
#                                                                              #
#                                                                              #
#Creation Date: 2015-01-20                                                     #
#Author:  Todd Farmer                                                          #
#                                                                              #
#Description: Test Cases of Dynamic System Variable                            #
#             "sha256_password_proxy_users"                                    #
#             that checks behavior of this variable in the following ways      #
#             * Functionality based on different values                        #
#                                                                              #
#Reference: http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#
# option_mysqld_sha256_password_proxy_users                                    #
#                                                                              #
################################################################################

--echo ** Setup **
--echo
#
# Setup
#

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

SET @default_sha256_password_proxy_users = @@global.sha256_password_proxy_users;
SET @default_check_proxy_users = @@global.check_proxy_users;

--echo '#----- 3.2.4 With super privileges --------------------------#'
###################################
#Setting value OFF                #
###################################

SET Global sha256_password_proxy_users=OFF;

--echo '#----- 3.2.4 Without super privileges ------------------------#'
#########################################
#Creating user without Super privilege  #
#########################################

--echo ** Creating new user with out super privilege**
CREATE USER sameea;
--echo ** Connecting connn using username 'sameea' **
CONNECT (conna,localhost,sameea,,);

--Error ER_SPECIFIC_ACCESS_DENIED_ERROR
SET GLOBAL sha256_password_proxy_users=ON;
--Error ER_SPECIFIC_ACCESS_DENIED_ERROR
SET @@global.sha256_password_proxy_users=ON;

--echo ** Connection default **
connection default;
--echo ** Closing connection **
DISCONNECT conna;

--echo '#----- 3.2.2.2 check OFF and sha256 OFF -------------------#'
###################################
#Setting value OFF                #
###################################

SET Global sha256_password_proxy_users=OFF;
SELECT @@global.check_proxy_users;

###################################
#Setting up proxy accounts        #
###################################

--echo ** Creating new base user **
CREATE USER ''@localhost;
--echo '#----- 4.4) Test to ensure users not defined with any PROXY privileges log in normally.-#';
CREATE USER proxy_base@localhost;
GRANT ALTER ON *.* TO proxy_base@localhost;
--echo ** Creating new proxy user **
CREATE USER proxy_sha256@localhost IDENTIFIED WITH sha256_password;
GRANT CREATE ON *.* TO proxy_base@localhost;
GRANT PROXY ON proxy_base@localhost TO proxy_sha256@localhost;

--echo ** Creating new proxy user with missing base user **
CREATE USER proxy_none@localhost IDENTIFIED WITH sha256_password;
GRANT PROXY ON proxy_blah@localhost TO proxy_none@localhost;

--echo '#----- 4.3)Test to ensure proxy privileges are not chained. ----------------------------#';
CREATE USER proxy_base_multi@localhost;
GRANT SELECT ON *.* TO proxy_base_multi@localhost;
--echo ** Creating new proxy user **
CREATE USER proxy_sha256_1@localhost IDENTIFIED WITH sha256_password;
GRANT DELETE ON *.* TO proxy_sha256_1@localhost;
CREATE USER proxy_sha256_2@localhost IDENTIFIED WITH sha256_password;
GRANT INSERT,DELETE ON *.* TO proxy_sha256_2@localhost;
CREATE USER proxy_sha256_3@localhost IDENTIFIED WITH sha256_password;
GRANT SELECT,DELETE ON *.* TO proxy_sha256_3@localhost;
GRANT PROXY ON proxy_base_multi@localhost TO proxy_sha256_1@localhost;
GRANT PROXY ON proxy_base_multi@localhost TO proxy_sha256_2@localhost;
GRANT PROXY ON proxy_base_multi@localhost TO proxy_sha256_3@localhost;

--echo ** Creating new proxy user **
CREATE USER proxy_sha256_4@localhost IDENTIFIED WITH sha256_password;
CREATE USER proxy_sha256_41@localhost IDENTIFIED WITH sha256_password;
GRANT PROXY ON proxy_base@localhost TO proxy_sha256_4@localhost;
GRANT PROXY ON proxy_base_multi@localhost TO proxy_sha256_4@localhost;
GRANT INSERT ON *.* TO proxy_sha256_4@localhost;
GRANT PROXY ON proxy_sha256_4@localhost TO proxy_sha256_41@localhost;
GRANT UPDATE ON *.* TO proxy_sha256_41@localhost;


--echo '#----- 4.2.check_proxy_users=OFF: sha256_password_proxy_users=OFF -----#';
###################################
#Testing proxy accounts           #
###################################

--echo ** Connecting as proxy_sha256 with proxy mapping disabled **
CONNECT (connb,localhost,proxy_sha256,,);
SELECT CURRENT_USER(), USER(), @@session.proxy_user;
SHOW GRANTS;

--echo ** Connection default **
connection default;
ALTER USER 'proxy_sha256'@'localhost' IDENTIFIED BY 'testpw';
--echo ** Connecting as proxy_sha256, password with proxy mapping disabled **
CONNECT (connbp,localhost,proxy_sha256,testpw,);
SELECT CURRENT_USER(), USER(), @@session.proxy_user;
SHOW GRANTS;
--echo ** Connection default **
connection default;
ALTER USER 'proxy_sha256'@'localhost' IDENTIFIED BY '';

--echo ** Connecting as proxy_none with proxy mapping disabled **
CONNECT (connc,localhost,proxy_none,,);
SELECT CURRENT_USER(), USER(), @@session.proxy_user;
SHOW GRANTS;

--echo ** Connecting as proxy_sha256_1 with proxy mapping disabled **
CONNECT (connn1,localhost,proxy_sha256_1,,);
SELECT CURRENT_USER(), USER(), @@session.proxy_user;
SHOW GRANTS;
--echo ** Connecting as proxy_sha256_2 with proxy mapping disabled **
CONNECT (connn2,localhost,proxy_sha256_2,,);
SELECT CURRENT_USER(), USER(), @@session.proxy_user;
SHOW GRANTS;

--echo ** Connecting as proxy_sha256_4 with proxy mapping disabled **
CONNECT (connm1,localhost,proxy_sha256_4,,);
SELECT CURRENT_USER(), USER(), @@session.proxy_user;
SHOW GRANTS;

--echo ** Connection default **
connection default;

--echo ** Disconnecting connections **
DISCONNECT connb;
DISCONNECT connbp;
DISCONNECT connc;
DISCONNECT connn1;
DISCONNECT connn2;
DISCONNECT connm1;

--echo '#----- 3.2.2.1 check OFF and sha256 ON ----------------------#'
--echo '#----- 4.2.check_proxy_users=OFF: sha256_password_proxy_users=ON ------#';
###################################
#Setting value ON                 #
###################################

SET Global sha256_password_proxy_users=ON;

###################################
#Testing proxy accounts           #
###################################

--echo ** Connecting as proxy_sha256 with proxy mapping disabled (sha256 mapping on) **
CONNECT (connd,localhost,proxy_sha256,,);
SELECT CURRENT_USER(), USER(), @@session.proxy_user;
SHOW GRANTS;

--echo ** Connection default **
connection default;
ALTER USER 'proxy_sha256'@'localhost' IDENTIFIED BY 'testpw';
--echo ** Connecting as proxy_sha256, password with proxy mapping disabled (sha256 mapping on) **
CONNECT (conndp,localhost,proxy_sha256,testpw,);
SELECT CURRENT_USER(), USER(), @@session.proxy_user;
SHOW GRANTS;
--echo ** Connection default **
connection default;
ALTER USER 'proxy_sha256'@'localhost' IDENTIFIED BY '';

--echo ** Connecting as proxy_none with proxy mapping disabled (sha256 mapping on) **
CONNECT (conne,localhost,proxy_none,,);
SELECT CURRENT_USER(), USER(), @@session.proxy_user;
SHOW GRANTS;

--echo ** Connecting as proxy_sha256_1 with proxy mapping disabled (sha256 mapping on)**
CONNECT (connn3,localhost,proxy_sha256_1,,);
SELECT CURRENT_USER(), USER(), @@session.proxy_user;
SHOW GRANTS;
--echo ** Connecting as proxy_sha256_2 with proxy mapping disabled (sha256 mapping on) **
CONNECT (connn4,localhost,proxy_sha256_2,,);
SELECT CURRENT_USER(), USER(), @@session.proxy_user;
SHOW GRANTS;

--echo ** Connecting as proxy_sha256_4 with proxy mapping disabled (sha256 mapping on) **
CONNECT (connm2,localhost,proxy_sha256_4,,);
SELECT CURRENT_USER(), USER(), @@session.proxy_user;
SHOW GRANTS;

--echo ** Connecting as chained proxy_sha256_41 with proxy mapping disabled (sha256 mapping on) **
CONNECT (connm21,localhost,proxy_sha256_41,,);
SELECT CURRENT_USER(), USER(), @@session.proxy_user;
SHOW GRANTS;

--echo ** Connection default **
connection default;
GRANT PROXY ON ''@localhost TO proxy_sha256_1@localhost;
--echo ** Connecting as Anonymus with proxy mapping disabled (sha256 mapping on) **
CONNECT (connan, localhost, '',,);
SELECT @@session.proxy_user IS NOT NULL;

--echo ** Connection default **
connection default;
DISCONNECT connan;
REVOKE PROXY ON ''@localhost FROM proxy_sha256_1@localhost;

--echo ** Connection default **
connection default;

--echo ** Disconnecting connections **
DISCONNECT connd;
DISCONNECT conndp;
DISCONNECT conne;
DISCONNECT connn3;
DISCONNECT connn4;
DISCONNECT connm2;
DISCONNECT connm21;

--echo '#----- 3.2.2.1 check and sha256 ON --------------------------#'
--echo '#----- 4.2.check_proxy_users=ON: sha256_password_proxy_users=ON ------#';
###################################
#Setting check_proxy value ON     #
###################################

SET Global check_proxy_users=ON;

###################################
#Testing proxy accounts           #
###################################

--echo ** Connecting as proxy_sha256 with proxy mapping enabled **
CONNECT (connf,localhost,proxy_sha256,,);
SELECT CURRENT_USER(), USER(), @@session.proxy_user;
SHOW GRANTS;

--echo ** Connection default **
connection default;
ALTER USER 'proxy_sha256'@'localhost' IDENTIFIED BY 'testpw';
--echo ** Connecting as proxy_sha256, password with proxy mapping enabled **
CONNECT (connfp,localhost,proxy_sha256,testpw,);
SELECT CURRENT_USER(), USER(), @@session.proxy_user;
SHOW GRANTS;
--echo ** Connection default **
connection default;
ALTER USER 'proxy_sha256'@'localhost' IDENTIFIED BY '';

--echo ** Connecting as proxy_sha256 with proxy mapping enabled **
CONNECT (conng,localhost,proxy_none,,);
SELECT CURRENT_USER(), USER(), @@session.proxy_user;
SHOW GRANTS;

--echo ** Connecting as proxy_sha256_1 with proxy mapping enabled **
CONNECT (connn5,localhost,proxy_sha256_1,,);
SELECT CURRENT_USER(), USER(), @@session.proxy_user;
SHOW GRANTS;
--echo ** Connecting as proxy_sha256_2 with proxy mapping enabled **
CONNECT (connn6,localhost,proxy_sha256_2,,);
SELECT CURRENT_USER(), USER(), @@session.proxy_user;
SHOW GRANTS;

--echo ** Connecting as proxy_sha256_4 with proxy mapping enabled **
CONNECT (connm3,localhost,proxy_sha256_4,,);
SELECT CURRENT_USER() IN ('proxy_base@localhost','proxy_base_multi@localhost');
SELECT USER(), @@session.proxy_user;
SHOW GRANTS;

--echo ** Connecting as chained proxy_sha256_41 with proxy mapping enabled **
CONNECT (connm31,localhost,proxy_sha256_41,,);
SELECT CURRENT_USER() IN ('proxy_base@localhost','proxy_base_multi@localhost');
SELECT CURRENT_USER() IN ('proxy_sha256_4@localhost');
SELECT USER(), @@session.proxy_user;
SHOW GRANTS;

--echo ** Connection default **
connection default;
GRANT PROXY ON ''@localhost TO proxy_sha256_1@localhost;
--echo ** Connecting as Anonymus with proxy mapping enabled **
CONNECT (connan, localhost, '',,);
SELECT @@session.proxy_user IS NOT NULL;

--echo ** Connection default **
connection default;
DISCONNECT connan;
REVOKE PROXY ON ''@localhost FROM proxy_sha256_1@localhost;

--echo ** Disconnecting connections **
DISCONNECT connf;
DISCONNECT connfp;
DISCONNECT conng;
DISCONNECT connn5;
DISCONNECT connn6;
DISCONNECT connm3;
DISCONNECT connm31;

###################################
#Cleaning up                      #
###################################

--echo ** Connection default **
connection default;

DROP USER ''@localhost;
DROP USER proxy_base@localhost;
DROP USER proxy_base_multi@localhost;
DROP USER proxy_sha256@localhost;
DROP USER proxy_none@localhost;
DROP USER proxy_sha256_1@localhost;
DROP USER proxy_sha256_2@localhost;
DROP USER proxy_sha256_3@localhost;
DROP USER proxy_sha256_4@localhost;
DROP USER proxy_sha256_41@localhost;
DROP USER sameea;

SET @@global.sha256_password_proxy_users = @default_sha256_password_proxy_users;
SET @@global.check_proxy_users = @default_check_proxy_users;