1# The numbers represent test cases of the test plan. 2 3--source include/have_plugin_auth.inc 4--source include/not_embedded.inc 5 6CREATE DATABASE test_user_db; 7 8--sorted_result 9SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 10 11--echo ========== test 1.1.3.2 ==================================== 12 13# CREATE...WITH/CREATE...BY/GRANT 14CREATE USER plug_user IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 15CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 16GRANT PROXY ON `plug%dest` TO plug_user; 17--error 1 18--exec $MYSQL -S $MASTER_MYSOCK -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1 19REVOKE PROXY ON `plug%dest` FROM plug_user; 20GRANT PROXY ON plug_dest TO plug_user; 21--replace_result $MASTER_MYSOCK MASTER_MYSOCK 22--exec $MYSQL -S $MASTER_MYSOCK -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1 23REVOKE PROXY ON plug_dest FROM plug_user; 24--error 1 25--exec $MYSQL -S $MASTER_MYSOCK -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1 26DROP USER plug_user,plug_dest; 27# 28# GRANT...WITH 29GRANT ALL PRIVILEGES ON test_user_db.* TO plug_user 30 IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 31GRANT ALL PRIVILEGES ON test_user_db.* TO plug_dest IDENTIFIED BY 'plug_dest_passwd'; 32GRANT PROXY ON plug_dest TO plug_user; 33 34--sorted_result 35SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 36--echo 1) 37--replace_result $MASTER_MYSOCK MASTER_MYSOCK 38--exec $MYSQL -S $MASTER_MYSOCK -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1 39REVOKE ALL PRIVILEGES ON test_user_db.* FROM 'plug_user'; 40--echo 2) 41--replace_result $MASTER_MYSOCK MASTER_MYSOCK 42--exec $MYSQL -S $MASTER_MYSOCK -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1 43REVOKE PROXY ON plug_dest FROM plug_user; 44--echo 3) 45--error 1 46--exec $MYSQL -S $MASTER_MYSOCK -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1 47DROP USER plug_user,plug_dest; 48# 49# GRANT...WITH/CREATE...BY 50GRANT ALL PRIVILEGES ON test_user_db.* TO plug_user 51 IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 52CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 53--echo 1) 54--error 1 55--exec $MYSQL -S $MASTER_MYSOCK -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1 56GRANT PROXY ON plug_dest TO plug_user; 57--echo 2) 58--replace_result $MASTER_MYSOCK MASTER_MYSOCK 59--exec $MYSQL -S $MASTER_MYSOCK -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();USE test_user_db;CREATE TABLE t1(a int);SHOW TABLES;DROP TABLE t1;" 2>&1 60REVOKE ALL PRIVILEGES ON test_user_db.* FROM 'plug_user'; 61#REVOKE ALL PRIVILEGES ON test_user_db.* FROM 'plug_dest''; 62DROP USER plug_user,plug_dest; 63 64--echo ========== test 1.2 ======================================== 65 66# GRANT...WITH/CREATE...BY 67GRANT ALL PRIVILEGES ON test_user_db.* TO plug_user 68 IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 69CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 70GRANT PROXY ON plug_dest TO plug_user; 71--replace_result $MASTER_MYSOCK MASTER_MYSOCK 72--exec $MYSQL -S $MASTER_MYSOCK -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();" 2>&1 73RENAME USER plug_dest TO new_dest; 74--error 1 75--exec $MYSQL -S $MASTER_MYSOCK -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();" 2>&1 76GRANT PROXY ON new_dest TO plug_user; 77--error 1 78--exec $MYSQL -S $MASTER_MYSOCK -u plug_user --password=new_dest -e "SELECT current_user();SELECT user();" 2>&1 79--sorted_result 80SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 81DROP USER plug_user,new_dest; 82 83# CREATE...WITH/CREATE...BY 84CREATE USER plug_user 85 IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 86CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 87--error 1 88--exec $MYSQL -S $MASTER_MYSOCK -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();" 2>&1 89GRANT PROXY ON plug_dest TO plug_user; 90--replace_result $MASTER_MYSOCK MASTER_MYSOCK 91--exec $MYSQL -S $MASTER_MYSOCK -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();" 2>&1 92RENAME USER plug_dest TO new_dest; 93--error 1 94--exec $MYSQL -S $MASTER_MYSOCK -u plug_user --password=plug_dest -e "SELECT current_user();SELECT user();" 2>&1 95GRANT PROXY ON new_dest TO plug_user; 96--error 1 97--exec $MYSQL -S $MASTER_MYSOCK -u plug_user --password=new_dest -e "SELECT current_user();SELECT user();" 2>&1 98--sorted_result 99SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 100DROP USER plug_user,new_dest; 101# CREATE...WITH 102CREATE USER plug_user 103 IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 104CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 105GRANT PROXY ON plug_dest TO plug_user; 106connect(plug_user,localhost,plug_user,plug_dest); 107select USER(),CURRENT_USER(); 108connection default; 109disconnect plug_user; 110RENAME USER plug_user TO new_user; 111connect(plug_user,localhost,new_user,plug_dest); 112select USER(),CURRENT_USER(); 113connection default; 114--sorted_result 115SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 116disconnect plug_user; 117UPDATE mysql.global_priv SET user='plug_user' WHERE user='new_user'; 118FLUSH PRIVILEGES; 119--sorted_result 120SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 121DROP USER plug_dest,plug_user; 122--echo ========== test 1.3 ======================================== 123 124# 125CREATE USER plug_user 126 IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 127CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 128GRANT PROXY ON plug_dest TO plug_user; 129connect(plug_user,localhost,plug_user,plug_dest); 130select USER(),CURRENT_USER(); 131connection default; 132disconnect plug_user; 133--sorted_result 134SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 135UPDATE mysql.global_priv SET user='new_user' WHERE user='plug_user'; 136FLUSH PRIVILEGES; 137--sorted_result 138SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 139UPDATE mysql.global_priv SET priv=JSON_SET(priv, '$.authentication_string', 'new_dest') WHERE user='new_user'; 140FLUSH PRIVILEGES; 141--sorted_result 142SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 143UPDATE mysql.global_priv SET priv=JSON_SET(priv, '$.plugin', 'new_plugin_server') WHERE user='new_user'; 144FLUSH PRIVILEGES; 145--sorted_result 146SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 147--disable_query_log 148--error ER_PLUGIN_IS_NOT_LOADED 149connect(plug_user,localhost,new_user,new_dest); 150--enable_query_log 151UPDATE mysql.global_priv SET priv=JSON_SET(priv, '$.plugin', 'test_plugin_server') WHERE user='new_user'; 152UPDATE mysql.global_priv SET user='new_dest' WHERE user='plug_dest'; 153FLUSH PRIVILEGES; 154GRANT PROXY ON new_dest TO new_user; 155--sorted_result 156SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 157connect(plug_user,localhost,new_user,new_dest); 158select USER(),CURRENT_USER(); 159connection default; 160disconnect plug_user; 161UPDATE mysql.global_priv SET user='plug_dest' WHERE user='new_dest'; 162FLUSH PRIVILEGES; 163CREATE USER new_dest IDENTIFIED BY 'new_dest_passwd'; 164--sorted_result 165SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 166GRANT ALL PRIVILEGES ON test.* TO new_user; 167connect(plug_user,localhost,new_dest,new_dest_passwd); 168select USER(),CURRENT_USER(); 169connection default; 170disconnect plug_user; 171DROP USER new_user,new_dest,plug_dest; 172 173--echo ========== test 2, 2.1, 2.2 ================================ 174 175CREATE USER ''@'%%' IDENTIFIED WITH test_plugin_server AS 'proxied_user'; 176CREATE USER proxied_user IDENTIFIED BY 'proxied_user_passwd'; 177--sorted_result 178SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 179connect(proxy_con,localhost,proxied_user,proxied_user_passwd); 180SELECT USER(),CURRENT_USER(); 181--echo ========== test 2.2.1 ====================================== 182SELECT @@proxy_user; 183connection default; 184disconnect proxy_con; 185--disable_query_log 186--error ER_ACCESS_DENIED_ERROR : this should fail : no grant 187connect(proxy_con,localhost,proxy_user,proxied_user); 188--enable_query_log 189GRANT PROXY ON proxied_user TO ''@'%%'; 190connect(proxy_con,localhost,proxied_user,proxied_user_passwd); 191SELECT USER(),CURRENT_USER(); 192connection default; 193disconnect proxy_con; 194connect(proxy_con,localhost,proxy_user,proxied_user); 195SELECT USER(),CURRENT_USER(); 196--echo ========== test 2.2.1 ====================================== 197SELECT @@proxy_user; 198connection default; 199disconnect proxy_con; 200DROP USER ''@'%%',proxied_user; 201# 202GRANT ALL PRIVILEGES ON test_user_db.* TO ''@'%%' 203 IDENTIFIED WITH test_plugin_server AS 'proxied_user'; 204CREATE USER proxied_user IDENTIFIED BY 'proxied_user_passwd'; 205--sorted_result 206SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 207connect(proxy_con,localhost,proxied_user,proxied_user_passwd); 208SELECT USER(),CURRENT_USER(); 209SELECT @@proxy_user; 210connection default; 211disconnect proxy_con; 212--disable_query_log 213--error ER_ACCESS_DENIED_ERROR : this should fail : no grant 214connect(proxy_con,localhost,proxy_user,proxied_user); 215--enable_query_log 216GRANT PROXY ON proxied_user TO ''@'%%'; 217connect(proxy_con,localhost,proxied_user,proxied_user_passwd); 218SELECT USER(),CURRENT_USER(); 219connection default; 220disconnect proxy_con; 221connect(proxy_con,localhost,proxy_user,proxied_user); 222SELECT USER(),CURRENT_USER(); 223SELECT @@proxy_user; 224connection default; 225disconnect proxy_con; 226DROP USER ''@'%%',proxied_user; 227# 228CREATE USER ''@'%%' IDENTIFIED WITH test_plugin_server AS 'proxied_user'; 229CREATE USER proxied_user_1 IDENTIFIED BY 'proxied_user_1_pwd'; 230CREATE USER proxied_user_2 IDENTIFIED BY 'proxied_user_2_pwd'; 231CREATE USER proxied_user_3 IDENTIFIED BY 'proxied_user_3_pwd'; 232CREATE USER proxied_user_4 IDENTIFIED BY 'proxied_user_4_pwd'; 233CREATE USER proxied_user_5 IDENTIFIED BY 'proxied_user_5_pwd'; 234GRANT PROXY ON proxied_user_1 TO ''@'%%'; 235GRANT PROXY ON proxied_user_2 TO ''@'%%'; 236GRANT PROXY ON proxied_user_3 TO ''@'%%'; 237GRANT PROXY ON proxied_user_4 TO ''@'%%'; 238GRANT PROXY ON proxied_user_5 TO ''@'%%'; 239--sorted_result 240SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 241connect(proxy_con_1,localhost,proxied_user_1,'proxied_user_1_pwd'); 242connect(proxy_con_2,localhost,proxied_user_2,proxied_user_2_pwd); 243connect(proxy_con_3,localhost,proxied_user_3,proxied_user_3_pwd); 244connect(proxy_con_4,localhost,proxied_user_4,proxied_user_4_pwd); 245connect(proxy_con_5,localhost,proxied_user_5,proxied_user_5_pwd); 246connection proxy_con_1; 247SELECT USER(),CURRENT_USER(); 248SELECT @@proxy_user; 249connection proxy_con_2; 250SELECT USER(),CURRENT_USER(); 251SELECT @@proxy_user; 252connection proxy_con_3; 253SELECT USER(),CURRENT_USER(); 254SELECT @@proxy_user; 255connection proxy_con_4; 256SELECT USER(),CURRENT_USER(); 257SELECT @@proxy_user; 258connection proxy_con_5; 259SELECT USER(),CURRENT_USER(); 260SELECT @@proxy_user; 261connection default; 262disconnect proxy_con_1; 263disconnect proxy_con_2; 264disconnect proxy_con_3; 265disconnect proxy_con_4; 266disconnect proxy_con_5; 267DROP USER ''@'%%',proxied_user_1,proxied_user_2,proxied_user_3,proxied_user_4,proxied_user_5; 268 269--echo ========== test 3 ========================================== 270 271GRANT ALL PRIVILEGES ON *.* TO plug_user 272 IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 273CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 274GRANT PROXY ON plug_dest TO plug_user; 275FLUSH PRIVILEGES; 276 277# Not working with the patch. 278 279#--replace_result $MYSQLADMIN MYSQLADMIN $MASTER_MYPORT MYPORT $MASTER_MYSOCK MYSOCK 280#--exec $MYSQLADMIN -h localhost -P $MASTER_MYPORT -S $MASTER_MYSOCK -u plug_user --password=plug_dest ping 2>&1 281#--replace_result $MYSQL_CHECK MYSQL_CHECK $MASTER_MYPORT MYPORT 282#--exec $MYSQL_CHECK -h localhost -P $MASTER_MYPORT -u plug_user --password=plug_dest test 283#--replace_result $MYSQL_DUMP MYSQL_DUMP $MASTER_MYPORT MYPORT 284#--exec $MYSQL_DUMP -h localhost -P $MASTER_MYPORT -u plug_user --password=plug_dest test 285#--replace_result $MYSQL_SHOW MYSQL_SHOW $MASTER_MYPORT MYPORT 286#--exec $MYSQL_SHOW -h localhost -P $MASTER_MYPORT --plugin_dir=../plugin/auth -u plug_user --password=plug_dest 2>&1 287DROP USER plug_user, plug_dest; 288DROP DATABASE test_user_db; 289--exit 290