1** Setup ** 2 3SET @default_mysql_native_password_proxy_users = @@global.mysql_native_password_proxy_users; 4SET @default_check_proxy_users = @@global.check_proxy_users; 5'#----- 2.2.4 With super privilege ------------------------------#' 6SET Global mysql_native_password_proxy_users=OFF; 7'#----- 2.2.4 Without super privilege ---------------------------#' 8** Creating new user with out super privilege** 9CREATE USER sameea; 10** Connecting connn using username 'sameea' ** 11SET GLOBAL mysql_native_password_proxy_users=ON; 12ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation 13SET @@global.mysql_native_password_proxy_users=ON; 14ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation 15** Connection default ** 16** Closing connection ** 17'#----- 2.2.2.2 OFF no assignment of proxy user -----------------#' 18SET Global mysql_native_password_proxy_users=OFF; 19SELECT @@global.check_proxy_users; 20@@global.check_proxy_users 210 22** Creating new base user ** 23CREATE USER ''@localhost; 24'#----- 4.4) Test to ensure users not defined with any PROXY privileges log in normally.-#'; 25CREATE USER proxy_base@localhost; 26GRANT ALTER ON *.* TO proxy_base@localhost; 27** Creating new proxy user ** 28CREATE USER proxy_native@localhost IDENTIFIED WITH mysql_native_password; 29GRANT CREATE ON *.* TO proxy_native@localhost; 30GRANT PROXY ON proxy_base@localhost TO proxy_native@localhost; 31** Creating new proxy user with missing base user ** 32CREATE USER proxy_none@localhost IDENTIFIED WITH mysql_native_password; 33GRANT PROXY ON proxy_blah@localhost TO proxy_none@localhost; 34'#----- 4.3)Test to ensure proxy privileges are not chained. ----------------------------#'; 35CREATE USER proxy_base_multi@localhost; 36GRANT SELECT ON *.* TO proxy_base_multi@localhost; 37** Creating new proxy user ** 38CREATE USER proxy_native_1@localhost IDENTIFIED WITH mysql_native_password; 39GRANT DELETE ON *.* TO proxy_native_1@localhost; 40CREATE USER proxy_native_2@localhost IDENTIFIED WITH mysql_native_password; 41GRANT INSERT,DELETE ON *.* TO proxy_native_2@localhost; 42CREATE USER proxy_native_3@localhost IDENTIFIED WITH mysql_native_password; 43GRANT SELECT,DELETE ON *.* TO proxy_native_3@localhost; 44GRANT PROXY ON proxy_base_multi@localhost TO proxy_native_1@localhost; 45GRANT PROXY ON proxy_base_multi@localhost TO proxy_native_2@localhost; 46GRANT PROXY ON proxy_base_multi@localhost TO proxy_native_3@localhost; 47** Creating new proxy user ** 48CREATE USER proxy_native_4@localhost IDENTIFIED WITH mysql_native_password; 49CREATE USER proxy_native_41@localhost IDENTIFIED WITH mysql_native_password; 50GRANT PROXY ON proxy_base@localhost TO proxy_native_4@localhost; 51GRANT PROXY ON proxy_base_multi@localhost TO proxy_native_4@localhost; 52GRANT INSERT ON *.* TO proxy_native_4@localhost; 53GRANT PROXY ON proxy_native_4@localhost TO proxy_native_41@localhost; 54GRANT UPDATE ON *.* TO proxy_native_41@localhost; 55'#----- 4.2.check_proxy_users=OFF: mysql_native_password_proxy_users=OFF -----#'; 56** Connecting as proxy_native with proxy mapping disabled ** 57SELECT CURRENT_USER(), USER(), @@session.proxy_user; 58CURRENT_USER() USER() @@session.proxy_user 59proxy_native@localhost proxy_native@localhost NULL 60SHOW GRANTS; 61Grants for proxy_native@localhost 62GRANT CREATE ON *.* TO 'proxy_native'@'localhost' 63GRANT PROXY ON 'proxy_base'@'localhost' TO 'proxy_native'@'localhost' 64** Connection default ** 65SET PASSWORD FOR 'proxy_native'@'localhost' = 'testpw'; 66** Connecting as proxy_native,password with proxy mapping disabled ** 67SELECT CURRENT_USER(), USER(), @@session.proxy_user; 68CURRENT_USER() USER() @@session.proxy_user 69proxy_native@localhost proxy_native@localhost NULL 70SHOW GRANTS; 71Grants for proxy_native@localhost 72GRANT CREATE ON *.* TO 'proxy_native'@'localhost' 73GRANT PROXY ON 'proxy_base'@'localhost' TO 'proxy_native'@'localhost' 74** Connection default ** 75SET PASSWORD FOR 'proxy_native'@'localhost' = ''; 76** Connecting as proxy_none with proxy mapping disabled ** 77SELECT CURRENT_USER(), USER(), @@session.proxy_user; 78CURRENT_USER() USER() @@session.proxy_user 79proxy_none@localhost proxy_none@localhost NULL 80SHOW GRANTS; 81Grants for proxy_none@localhost 82GRANT USAGE ON *.* TO 'proxy_none'@'localhost' 83GRANT PROXY ON 'proxy_blah'@'localhost' TO 'proxy_none'@'localhost' 84** Connecting as proxy_native_1 with proxy mapping disabled ** 85SELECT CURRENT_USER(), USER(), @@session.proxy_user; 86CURRENT_USER() USER() @@session.proxy_user 87proxy_native_1@localhost proxy_native_1@localhost NULL 88SHOW GRANTS; 89Grants for proxy_native_1@localhost 90GRANT DELETE ON *.* TO 'proxy_native_1'@'localhost' 91GRANT PROXY ON 'proxy_base_multi'@'localhost' TO 'proxy_native_1'@'localhost' 92** Connecting as proxy_native_2 with proxy mapping disabled ** 93SELECT CURRENT_USER(), USER(), @@session.proxy_user; 94CURRENT_USER() USER() @@session.proxy_user 95proxy_native_2@localhost proxy_native_2@localhost NULL 96SHOW GRANTS; 97Grants for proxy_native_2@localhost 98GRANT INSERT, DELETE ON *.* TO 'proxy_native_2'@'localhost' 99GRANT PROXY ON 'proxy_base_multi'@'localhost' TO 'proxy_native_2'@'localhost' 100** Connecting as proxy_native_4 with proxy mapping disabled ** 101SELECT CURRENT_USER(), USER(), @@session.proxy_user; 102CURRENT_USER() USER() @@session.proxy_user 103proxy_native_4@localhost proxy_native_4@localhost NULL 104SHOW GRANTS; 105Grants for proxy_native_4@localhost 106GRANT INSERT ON *.* TO 'proxy_native_4'@'localhost' 107GRANT PROXY ON 'proxy_base'@'localhost' TO 'proxy_native_4'@'localhost' 108GRANT PROXY ON 'proxy_base_multi'@'localhost' TO 'proxy_native_4'@'localhost' 109** Connection default ** 110** Disconnecting connections ** 111'#----- 2.2.2.1 ON assignment of proxy user ------------------#' 112'#----- 4.2.check_proxy_users=OFF: mysql_native_password_proxy_users=ON ------#'; 113SET Global mysql_native_password_proxy_users=ON; 114** Connecting as proxy_native with proxy mapping disabled (native mapping on) ** 115SELECT CURRENT_USER(), USER(), @@session.proxy_user; 116CURRENT_USER() USER() @@session.proxy_user 117proxy_native@localhost proxy_native@localhost NULL 118SHOW GRANTS; 119Grants for proxy_native@localhost 120GRANT CREATE ON *.* TO 'proxy_native'@'localhost' 121GRANT PROXY ON 'proxy_base'@'localhost' TO 'proxy_native'@'localhost' 122** Connection default ** 123SET PASSWORD FOR 'proxy_native'@'localhost' = 'testpw'; 124** Connecting as proxy_native,password with proxy mapping disabled (native mapping on) ** 125SELECT CURRENT_USER(), USER(), @@session.proxy_user; 126CURRENT_USER() USER() @@session.proxy_user 127proxy_native@localhost proxy_native@localhost NULL 128SHOW GRANTS; 129Grants for proxy_native@localhost 130GRANT CREATE ON *.* TO 'proxy_native'@'localhost' 131GRANT PROXY ON 'proxy_base'@'localhost' TO 'proxy_native'@'localhost' 132** Connection default ** 133SET PASSWORD FOR 'proxy_native'@'localhost' = ''; 134** Connecting as proxy_none with proxy mapping disabled (native mapping on) ** 135SELECT CURRENT_USER(), USER(), @@session.proxy_user; 136CURRENT_USER() USER() @@session.proxy_user 137proxy_none@localhost proxy_none@localhost NULL 138SHOW GRANTS; 139Grants for proxy_none@localhost 140GRANT USAGE ON *.* TO 'proxy_none'@'localhost' 141GRANT PROXY ON 'proxy_blah'@'localhost' TO 'proxy_none'@'localhost' 142** Connecting as proxy_native_1 with proxy mapping disabled (native mapping on)** 143SELECT CURRENT_USER(), USER(), @@session.proxy_user; 144CURRENT_USER() USER() @@session.proxy_user 145proxy_native_1@localhost proxy_native_1@localhost NULL 146SHOW GRANTS; 147Grants for proxy_native_1@localhost 148GRANT DELETE ON *.* TO 'proxy_native_1'@'localhost' 149GRANT PROXY ON 'proxy_base_multi'@'localhost' TO 'proxy_native_1'@'localhost' 150** Connecting as proxy_native_2 with proxy mapping disabled (native mapping on) ** 151SELECT CURRENT_USER(), USER(), @@session.proxy_user; 152CURRENT_USER() USER() @@session.proxy_user 153proxy_native_2@localhost proxy_native_2@localhost NULL 154SHOW GRANTS; 155Grants for proxy_native_2@localhost 156GRANT INSERT, DELETE ON *.* TO 'proxy_native_2'@'localhost' 157GRANT PROXY ON 'proxy_base_multi'@'localhost' TO 'proxy_native_2'@'localhost' 158** Connecting as proxy_native_4 with proxy mapping disabled (native mapping on) ** 159SELECT CURRENT_USER(), USER(), @@session.proxy_user; 160CURRENT_USER() USER() @@session.proxy_user 161proxy_native_4@localhost proxy_native_4@localhost NULL 162SHOW GRANTS; 163Grants for proxy_native_4@localhost 164GRANT INSERT ON *.* TO 'proxy_native_4'@'localhost' 165GRANT PROXY ON 'proxy_base'@'localhost' TO 'proxy_native_4'@'localhost' 166GRANT PROXY ON 'proxy_base_multi'@'localhost' TO 'proxy_native_4'@'localhost' 167** Connecting as chained proxy_native_41 with proxy mapping disabled (native mapping on) ** 168SELECT CURRENT_USER(), USER(), @@session.proxy_user; 169CURRENT_USER() USER() @@session.proxy_user 170proxy_native_41@localhost proxy_native_41@localhost NULL 171SHOW GRANTS; 172Grants for proxy_native_41@localhost 173GRANT UPDATE ON *.* TO 'proxy_native_41'@'localhost' 174GRANT PROXY ON 'proxy_native_4'@'localhost' TO 'proxy_native_41'@'localhost' 175** Connection default ** 176GRANT PROXY ON ''@localhost TO proxy_native_1@localhost; 177** Connecting as Anonymus with proxy mapping disabled (native mapping on) ** 178SELECT @@session.proxy_user IS NOT NULL; 179@@session.proxy_user IS NOT NULL 1800 181** Connection default ** 182REVOKE PROXY ON ''@localhost FROM proxy_native_1@localhost; 183** Connection default ** 184** Disconnecting connections ** 185'#----- 1.2.2.1 check and native ON --------------------------#' 186'#----- 4.2.check_proxy_users=ON: mysql_native_password_proxy_users=ON ------#'; 187SET Global check_proxy_users=ON; 188** Connecting as proxy_native with proxy mapping enabled ** 189SELECT CURRENT_USER(), USER(), @@session.proxy_user; 190CURRENT_USER() USER() @@session.proxy_user 191proxy_base@localhost proxy_native@localhost 'proxy_native'@'localhost' 192SHOW GRANTS; 193Grants for proxy_base@localhost 194GRANT ALTER ON *.* TO 'proxy_base'@'localhost' 195** Connection default ** 196SET PASSWORD FOR 'proxy_native'@'localhost' = 'testpw'; 197** Connecting as proxy_native,password with proxy mapping enabled ** 198SELECT CURRENT_USER(), USER(), @@session.proxy_user; 199CURRENT_USER() USER() @@session.proxy_user 200proxy_base@localhost proxy_native@localhost 'proxy_native'@'localhost' 201SHOW GRANTS; 202Grants for proxy_base@localhost 203GRANT ALTER ON *.* TO 'proxy_base'@'localhost' 204** Connection default ** 205SET PASSWORD FOR 'proxy_native'@'localhost' = ''; 206** Connecting as proxy_none with proxy mapping enabled ** 207SELECT CURRENT_USER(), USER(), @@session.proxy_user; 208CURRENT_USER() USER() @@session.proxy_user 209proxy_none@localhost proxy_none@localhost NULL 210SHOW GRANTS; 211Grants for proxy_none@localhost 212GRANT USAGE ON *.* TO 'proxy_none'@'localhost' 213GRANT PROXY ON 'proxy_blah'@'localhost' TO 'proxy_none'@'localhost' 214** Connecting as proxy_native_1 with proxy mapping enabled ** 215SELECT CURRENT_USER(), USER(), @@session.proxy_user; 216CURRENT_USER() USER() @@session.proxy_user 217proxy_base_multi@localhost proxy_native_1@localhost 'proxy_native_1'@'localhost' 218SHOW GRANTS; 219Grants for proxy_base_multi@localhost 220GRANT SELECT ON *.* TO 'proxy_base_multi'@'localhost' 221** Connecting as proxy_native_2 with proxy mapping enabled ** 222SELECT CURRENT_USER(), USER(), @@session.proxy_user; 223CURRENT_USER() USER() @@session.proxy_user 224proxy_base_multi@localhost proxy_native_2@localhost 'proxy_native_2'@'localhost' 225SHOW GRANTS; 226Grants for proxy_base_multi@localhost 227GRANT SELECT ON *.* TO 'proxy_base_multi'@'localhost' 228** Connecting as proxy_native_4 with proxy mapping enabled ** 229SELECT CURRENT_USER() IN ('proxy_base@localhost','proxy_base_multi@localhost'); 230CURRENT_USER() IN ('proxy_base@localhost','proxy_base_multi@localhost') 2311 232SELECT USER(), @@session.proxy_user; 233USER() @@session.proxy_user 234proxy_native_4@localhost 'proxy_native_4'@'localhost' 235SHOW GRANTS; 236Grants for proxy_base@localhost 237GRANT ALTER ON *.* TO 'proxy_base'@'localhost' 238** Connecting as chained proxy_native_41 with proxy mapping enabled ** 239SELECT CURRENT_USER() IN ('proxy_base@localhost','proxy_base_multi@localhost'); 240CURRENT_USER() IN ('proxy_base@localhost','proxy_base_multi@localhost') 2410 242SELECT CURRENT_USER() IN ('proxy_native_4@localhost'); 243CURRENT_USER() IN ('proxy_native_4@localhost') 2441 245SELECT USER(), @@session.proxy_user; 246USER() @@session.proxy_user 247proxy_native_41@localhost 'proxy_native_41'@'localhost' 248SHOW GRANTS; 249Grants for proxy_native_4@localhost 250GRANT INSERT ON *.* TO 'proxy_native_4'@'localhost' 251GRANT PROXY ON 'proxy_base'@'localhost' TO 'proxy_native_4'@'localhost' 252GRANT PROXY ON 'proxy_base_multi'@'localhost' TO 'proxy_native_4'@'localhost' 253** Connection default ** 254GRANT PROXY ON ''@localhost TO proxy_native_1@localhost; 255** Connecting as Anonymus with proxy mapping enabled ** 256SELECT @@session.proxy_user IS NOT NULL; 257@@session.proxy_user IS NOT NULL 2580 259** Connection default ** 260REVOKE PROXY ON ''@localhost FROM proxy_native_1@localhost; 261** Disconnecting connections ** 262DROP USER ''@localhost; 263DROP USER proxy_base@localhost; 264DROP USER proxy_base_multi@localhost; 265DROP USER proxy_native@localhost; 266DROP USER proxy_none@localhost; 267DROP USER proxy_native_1@localhost; 268DROP USER proxy_native_2@localhost; 269DROP USER proxy_native_3@localhost; 270DROP USER proxy_native_4@localhost; 271DROP USER proxy_native_41@localhost; 272DROP USER sameea; 273SET @@global.mysql_native_password_proxy_users = @default_mysql_native_password_proxy_users; 274SET @@global.check_proxy_users = @default_check_proxy_users; 275