1CREATE DATABASE test_user_db; 2SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 3User plugin authentication_string 4mariadb.sys mysql_native_password 5========== test 1.1.3.2 ==================================== 6CREATE USER plug_user IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 7CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 8GRANT PROXY ON `plug%dest` TO plug_user; 9ERROR 1045 (28000): Access denied for user 'plug_user'@'localhost' (using password: YES) 10REVOKE PROXY ON `plug%dest` FROM plug_user; 11GRANT PROXY ON plug_dest TO plug_user; 12current_user() 13plug_dest@% 14user() 15plug_user@localhost 16Tables_in_test_user_db 17t1 18REVOKE PROXY ON plug_dest FROM plug_user; 19ERROR 1045 (28000): Access denied for user 'plug_user'@'localhost' (using password: YES) 20DROP USER plug_user,plug_dest; 21GRANT ALL PRIVILEGES ON test_user_db.* TO plug_user 22IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 23GRANT ALL PRIVILEGES ON test_user_db.* TO plug_dest IDENTIFIED BY 'plug_dest_passwd'; 24GRANT PROXY ON plug_dest TO plug_user; 25SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 26User plugin authentication_string 27mariadb.sys mysql_native_password 28plug_dest mysql_native_password *939AEE68989794C0F408277411C26055CDF41119 29plug_user test_plugin_server plug_dest 301) 31current_user() 32plug_dest@% 33user() 34plug_user@localhost 35Tables_in_test_user_db 36t1 37REVOKE ALL PRIVILEGES ON test_user_db.* FROM 'plug_user'; 382) 39current_user() 40plug_dest@% 41user() 42plug_user@localhost 43Tables_in_test_user_db 44t1 45REVOKE PROXY ON plug_dest FROM plug_user; 463) 47ERROR 1045 (28000): Access denied for user 'plug_user'@'localhost' (using password: YES) 48DROP USER plug_user,plug_dest; 49GRANT ALL PRIVILEGES ON test_user_db.* TO plug_user 50IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 51CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 521) 53ERROR 1045 (28000): Access denied for user 'plug_user'@'localhost' (using password: YES) 54GRANT PROXY ON plug_dest TO plug_user; 552) 56current_user() 57plug_dest@% 58user() 59plug_user@localhost 60Tables_in_test_user_db 61t1 62REVOKE ALL PRIVILEGES ON test_user_db.* FROM 'plug_user'; 63DROP USER plug_user,plug_dest; 64========== test 1.2 ======================================== 65GRANT ALL PRIVILEGES ON test_user_db.* TO plug_user 66IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 67CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 68GRANT PROXY ON plug_dest TO plug_user; 69current_user() 70plug_dest@% 71user() 72plug_user@localhost 73RENAME USER plug_dest TO new_dest; 74ERROR 1045 (28000): Access denied for user 'plug_user'@'localhost' (using password: YES) 75GRANT PROXY ON new_dest TO plug_user; 76ERROR 1045 (28000): Access denied for user 'plug_user'@'localhost' (using password: YES) 77SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 78User plugin authentication_string 79mariadb.sys mysql_native_password 80new_dest mysql_native_password *939AEE68989794C0F408277411C26055CDF41119 81plug_user test_plugin_server plug_dest 82DROP USER plug_user,new_dest; 83CREATE USER plug_user 84IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 85CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 86ERROR 1045 (28000): Access denied for user 'plug_user'@'localhost' (using password: YES) 87GRANT PROXY ON plug_dest TO plug_user; 88current_user() 89plug_dest@% 90user() 91plug_user@localhost 92RENAME USER plug_dest TO new_dest; 93ERROR 1045 (28000): Access denied for user 'plug_user'@'localhost' (using password: YES) 94GRANT PROXY ON new_dest TO plug_user; 95ERROR 1045 (28000): Access denied for user 'plug_user'@'localhost' (using password: YES) 96SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 97User plugin authentication_string 98mariadb.sys mysql_native_password 99new_dest mysql_native_password *939AEE68989794C0F408277411C26055CDF41119 100plug_user test_plugin_server plug_dest 101DROP USER plug_user,new_dest; 102CREATE USER plug_user 103IDENTIFIED 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(); 108USER() CURRENT_USER() 109plug_user@localhost plug_dest@% 110connection default; 111disconnect plug_user; 112RENAME USER plug_user TO new_user; 113connect plug_user,localhost,new_user,plug_dest; 114select USER(),CURRENT_USER(); 115USER() CURRENT_USER() 116new_user@localhost plug_dest@% 117connection default; 118SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 119User plugin authentication_string 120mariadb.sys mysql_native_password 121new_user test_plugin_server plug_dest 122plug_dest mysql_native_password *939AEE68989794C0F408277411C26055CDF41119 123disconnect plug_user; 124UPDATE mysql.global_priv SET user='plug_user' WHERE user='new_user'; 125FLUSH PRIVILEGES; 126SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 127User plugin authentication_string 128mariadb.sys mysql_native_password 129plug_dest mysql_native_password *939AEE68989794C0F408277411C26055CDF41119 130plug_user test_plugin_server plug_dest 131DROP USER plug_dest,plug_user; 132========== test 1.3 ======================================== 133CREATE USER plug_user 134IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 135CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 136GRANT PROXY ON plug_dest TO plug_user; 137connect plug_user,localhost,plug_user,plug_dest; 138select USER(),CURRENT_USER(); 139USER() CURRENT_USER() 140plug_user@localhost plug_dest@% 141connection default; 142disconnect plug_user; 143SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 144User plugin authentication_string 145mariadb.sys mysql_native_password 146plug_dest mysql_native_password *939AEE68989794C0F408277411C26055CDF41119 147plug_user test_plugin_server plug_dest 148UPDATE mysql.global_priv SET user='new_user' WHERE user='plug_user'; 149FLUSH PRIVILEGES; 150SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 151User plugin authentication_string 152mariadb.sys mysql_native_password 153new_user test_plugin_server plug_dest 154plug_dest mysql_native_password *939AEE68989794C0F408277411C26055CDF41119 155UPDATE mysql.global_priv SET priv=JSON_SET(priv, '$.authentication_string', 'new_dest') WHERE user='new_user'; 156FLUSH PRIVILEGES; 157SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 158User plugin authentication_string 159mariadb.sys mysql_native_password 160new_user test_plugin_server new_dest 161plug_dest mysql_native_password *939AEE68989794C0F408277411C26055CDF41119 162UPDATE mysql.global_priv SET priv=JSON_SET(priv, '$.plugin', 'new_plugin_server') WHERE user='new_user'; 163FLUSH PRIVILEGES; 164SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 165User plugin authentication_string 166mariadb.sys mysql_native_password 167new_user new_plugin_server new_dest 168plug_dest mysql_native_password *939AEE68989794C0F408277411C26055CDF41119 169ERROR HY000: Plugin 'new_plugin_server' is not loaded 170UPDATE mysql.global_priv SET priv=JSON_SET(priv, '$.plugin', 'test_plugin_server') WHERE user='new_user'; 171UPDATE mysql.global_priv SET user='new_dest' WHERE user='plug_dest'; 172FLUSH PRIVILEGES; 173GRANT PROXY ON new_dest TO new_user; 174SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 175User plugin authentication_string 176mariadb.sys mysql_native_password 177new_dest mysql_native_password *939AEE68989794C0F408277411C26055CDF41119 178new_user test_plugin_server new_dest 179connect plug_user,localhost,new_user,new_dest; 180select USER(),CURRENT_USER(); 181USER() CURRENT_USER() 182new_user@localhost new_dest@% 183connection default; 184disconnect plug_user; 185UPDATE mysql.global_priv SET user='plug_dest' WHERE user='new_dest'; 186FLUSH PRIVILEGES; 187CREATE USER new_dest IDENTIFIED BY 'new_dest_passwd'; 188SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 189User plugin authentication_string 190mariadb.sys mysql_native_password 191new_dest mysql_native_password *01422E86A6FFF84618914AF149F9AEF64B84170A 192new_user test_plugin_server new_dest 193plug_dest mysql_native_password *939AEE68989794C0F408277411C26055CDF41119 194GRANT ALL PRIVILEGES ON test.* TO new_user; 195connect plug_user,localhost,new_dest,new_dest_passwd; 196select USER(),CURRENT_USER(); 197USER() CURRENT_USER() 198new_dest@localhost new_dest@% 199connection default; 200disconnect plug_user; 201DROP USER new_user,new_dest,plug_dest; 202========== test 2, 2.1, 2.2 ================================ 203CREATE USER ''@'%%' IDENTIFIED WITH test_plugin_server AS 'proxied_user'; 204CREATE USER proxied_user IDENTIFIED BY 'proxied_user_passwd'; 205SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 206User plugin authentication_string 207 test_plugin_server proxied_user 208mariadb.sys mysql_native_password 209proxied_user mysql_native_password *D7A51428CD38DB3C5293B9321DA1228BFB1611DD 210connect proxy_con,localhost,proxied_user,proxied_user_passwd; 211SELECT USER(),CURRENT_USER(); 212USER() CURRENT_USER() 213proxied_user@localhost proxied_user@% 214========== test 2.2.1 ====================================== 215SELECT @@proxy_user; 216@@proxy_user 217NULL 218connection default; 219disconnect proxy_con; 220ERROR 28000: Access denied for user 'proxy_user'@'localhost' (using password: YES) 221GRANT PROXY ON proxied_user TO ''@'%%'; 222connect proxy_con,localhost,proxied_user,proxied_user_passwd; 223SELECT USER(),CURRENT_USER(); 224USER() CURRENT_USER() 225proxied_user@localhost proxied_user@% 226connection default; 227disconnect proxy_con; 228connect proxy_con,localhost,proxy_user,proxied_user; 229SELECT USER(),CURRENT_USER(); 230USER() CURRENT_USER() 231proxy_user@localhost proxied_user@% 232========== test 2.2.1 ====================================== 233SELECT @@proxy_user; 234@@proxy_user 235''@'%%' 236connection default; 237disconnect proxy_con; 238DROP USER ''@'%%',proxied_user; 239GRANT ALL PRIVILEGES ON test_user_db.* TO ''@'%%' 240IDENTIFIED WITH test_plugin_server AS 'proxied_user'; 241CREATE USER proxied_user IDENTIFIED BY 'proxied_user_passwd'; 242SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 243User plugin authentication_string 244 test_plugin_server proxied_user 245mariadb.sys mysql_native_password 246proxied_user mysql_native_password *D7A51428CD38DB3C5293B9321DA1228BFB1611DD 247connect proxy_con,localhost,proxied_user,proxied_user_passwd; 248SELECT USER(),CURRENT_USER(); 249USER() CURRENT_USER() 250proxied_user@localhost proxied_user@% 251SELECT @@proxy_user; 252@@proxy_user 253NULL 254connection default; 255disconnect proxy_con; 256ERROR 28000: Access denied for user 'proxy_user'@'localhost' (using password: YES) 257GRANT PROXY ON proxied_user TO ''@'%%'; 258connect proxy_con,localhost,proxied_user,proxied_user_passwd; 259SELECT USER(),CURRENT_USER(); 260USER() CURRENT_USER() 261proxied_user@localhost proxied_user@% 262connection default; 263disconnect proxy_con; 264connect proxy_con,localhost,proxy_user,proxied_user; 265SELECT USER(),CURRENT_USER(); 266USER() CURRENT_USER() 267proxy_user@localhost proxied_user@% 268SELECT @@proxy_user; 269@@proxy_user 270''@'%%' 271connection default; 272disconnect proxy_con; 273DROP USER ''@'%%',proxied_user; 274CREATE USER ''@'%%' IDENTIFIED WITH test_plugin_server AS 'proxied_user'; 275CREATE USER proxied_user_1 IDENTIFIED BY 'proxied_user_1_pwd'; 276CREATE USER proxied_user_2 IDENTIFIED BY 'proxied_user_2_pwd'; 277CREATE USER proxied_user_3 IDENTIFIED BY 'proxied_user_3_pwd'; 278CREATE USER proxied_user_4 IDENTIFIED BY 'proxied_user_4_pwd'; 279CREATE USER proxied_user_5 IDENTIFIED BY 'proxied_user_5_pwd'; 280GRANT PROXY ON proxied_user_1 TO ''@'%%'; 281GRANT PROXY ON proxied_user_2 TO ''@'%%'; 282GRANT PROXY ON proxied_user_3 TO ''@'%%'; 283GRANT PROXY ON proxied_user_4 TO ''@'%%'; 284GRANT PROXY ON proxied_user_5 TO ''@'%%'; 285SELECT user,plugin,authentication_string FROM mysql.user WHERE user != 'root'; 286User plugin authentication_string 287 test_plugin_server proxied_user 288mariadb.sys mysql_native_password 289proxied_user_1 mysql_native_password *551D5A5177FCC3340F7D2FB0F4D8D1EEA7F7EF71 290proxied_user_2 mysql_native_password *3D948F77C6A988AFDCA9755AB2A6724362557220 291proxied_user_3 mysql_native_password *41A18925D237DEE738C76581153990B037F462E3 292proxied_user_4 mysql_native_password *F990073A9B96FF535C2D0721406042B8751E593F 293proxied_user_5 mysql_native_password *5AA915C5D0B5B1336336FD2BF7768BC09FD1F5B2 294connect proxy_con_1,localhost,proxied_user_1,'proxied_user_1_pwd'; 295connect proxy_con_2,localhost,proxied_user_2,proxied_user_2_pwd; 296connect proxy_con_3,localhost,proxied_user_3,proxied_user_3_pwd; 297connect proxy_con_4,localhost,proxied_user_4,proxied_user_4_pwd; 298connect proxy_con_5,localhost,proxied_user_5,proxied_user_5_pwd; 299connection proxy_con_1; 300SELECT USER(),CURRENT_USER(); 301USER() CURRENT_USER() 302proxied_user_1@localhost proxied_user_1@% 303SELECT @@proxy_user; 304@@proxy_user 305NULL 306connection proxy_con_2; 307SELECT USER(),CURRENT_USER(); 308USER() CURRENT_USER() 309proxied_user_2@localhost proxied_user_2@% 310SELECT @@proxy_user; 311@@proxy_user 312NULL 313connection proxy_con_3; 314SELECT USER(),CURRENT_USER(); 315USER() CURRENT_USER() 316proxied_user_3@localhost proxied_user_3@% 317SELECT @@proxy_user; 318@@proxy_user 319NULL 320connection proxy_con_4; 321SELECT USER(),CURRENT_USER(); 322USER() CURRENT_USER() 323proxied_user_4@localhost proxied_user_4@% 324SELECT @@proxy_user; 325@@proxy_user 326NULL 327connection proxy_con_5; 328SELECT USER(),CURRENT_USER(); 329USER() CURRENT_USER() 330proxied_user_5@localhost proxied_user_5@% 331SELECT @@proxy_user; 332@@proxy_user 333NULL 334connection default; 335disconnect proxy_con_1; 336disconnect proxy_con_2; 337disconnect proxy_con_3; 338disconnect proxy_con_4; 339disconnect proxy_con_5; 340DROP USER ''@'%%',proxied_user_1,proxied_user_2,proxied_user_3,proxied_user_4,proxied_user_5; 341========== test 3 ========================================== 342GRANT ALL PRIVILEGES ON *.* TO plug_user 343IDENTIFIED WITH test_plugin_server AS 'plug_dest'; 344CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 345GRANT PROXY ON plug_dest TO plug_user; 346FLUSH PRIVILEGES; 347DROP USER plug_user, plug_dest; 348DROP DATABASE test_user_db; 349