1--source include/have_plugin_auth.inc 2--source include/not_embedded.inc 3--source include/mysql_upgrade_preparation.inc 4--source include/have_innodb.inc 5 6SET GLOBAL SQL_MODE=""; 7SET LOCAL SQL_MODE=""; 8 9query_vertical SELECT PLUGIN_STATUS, PLUGIN_TYPE, PLUGIN_DESCRIPTION 10 FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='test_plugin_server'; 11 12CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 13CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 14 15SELECT plugin,authentication_string FROM mysql.user WHERE User='plug'; 16 17--echo ## test plugin auth 18--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 19--error ER_ACCESS_DENIED_ERROR : this should fail : no grant 20connect(plug_con,localhost,plug,plug_dest); 21 22GRANT PROXY ON plug_dest TO plug; 23--echo test proxies_priv columns 24--replace_column 1 xx 7 xx 25SELECT * FROM mysql.proxies_priv WHERE user !='root'; 26--echo test mysql.proxies_priv; 27SHOW CREATE TABLE mysql.proxies_priv; 28 29connect(plug_con,localhost,plug,plug_dest); 30select USER(),CURRENT_USER(); 31 32--echo ## test SET PASSWORD 33#--error ER_SET_PASSWORD_AUTH_PLUGIN 34SET PASSWORD = PASSWORD('plug_dest'); 35 36connection default; 37disconnect plug_con; 38 39--echo ## test bad credentials 40--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 41--error ER_ACCESS_DENIED_ERROR 42connect(plug_con,localhost,plug,bad_credentials); 43 44--echo ## test bad default plugin : nothing bad happens, as that plugin was't required by the server 45connect(plug_con_wrongp,localhost,plug,plug_dest,,,,,wrong_plugin_name); 46select USER(),CURRENT_USER(); 47connection default; 48disconnect plug_con_wrongp; 49 50--echo ## test correct default plugin 51connect(plug_con_rightp,localhost,plug,plug_dest,,,,,auth_test_plugin); 52select USER(),CURRENT_USER(); 53connection default; 54disconnect plug_con_rightp; 55 56--echo ## test no_auto_create_user sql mode with plugin users 57SET @@sql_mode=no_auto_create_user; 58GRANT INSERT ON TEST.* TO grant_user IDENTIFIED WITH 'test_plugin_server'; 59SET @@sql_mode=""; 60DROP USER grant_user; 61 62--echo ## test utf-8 user name 63CREATE USER `Ÿ` IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 64 65GRANT PROXY ON plug_dest TO `Ÿ`; 66 67connect(non_ascii,localhost,Ÿ,plug_dest); 68select USER(),CURRENT_USER(); 69 70connection default; 71disconnect non_ascii; 72DROP USER `Ÿ`; 73 74--echo ## test GRANT ... IDENTIFIED WITH/BY ... 75 76CREATE DATABASE test_grant_db; 77 78--echo # create new user via GRANT WITH 79GRANT ALL PRIVILEGES ON test_grant_db.* TO new_grant_user 80 IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 81 82GRANT PROXY ON plug_dest TO new_grant_user; 83 84connect(plug_con_grant,localhost,new_grant_user,plug_dest); 85select USER(),CURRENT_USER(); 86USE test_grant_db; 87CREATE TABLE t1 (a INT); 88DROP TABLE t1; 89connection default; 90disconnect plug_con_grant; 91REVOKE ALL PRIVILEGES ON test_grant_db.* FROM new_grant_user; 92 93--echo # try re-create existing user via GRANT IDENTIFIED BY 94GRANT ALL PRIVILEGES ON test_grant_db.* TO new_grant_user 95 IDENTIFIED BY 'new_password'; 96 97--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 98--error ER_ACCESS_DENIED_ERROR 99connect(plug_con_grant_deny,localhost,new_grant_user,plug_dest); 100 101connect(plug_con_grant,localhost,new_grant_user,new_password); 102select USER(),CURRENT_USER(); 103USE test_grant_db; 104CREATE TABLE t1 (a INT); 105DROP TABLE t1; 106connection default; 107disconnect plug_con_grant; 108 109DROP USER new_grant_user; 110 111--echo # try re-create existing user via GRANT IDENTIFIED WITH 112 113GRANT ALL PRIVILEGES ON test_grant_db.* TO plug 114 IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 115 116GRANT ALL PRIVILEGES ON test_grant_db.* TO plug_dest 117 IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 118 119--error ER_PARSE_ERROR 120REVOKE SELECT on test_grant_db.* FROM joro 121 INDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 122 123--error ER_PARSE_ERROR 124REVOKE SELECT on test_grant_db.* FROM joro 125 INDENTIFIED BY 'plug_dest_passwd'; 126 127--error ER_PARSE_ERROR 128REVOKE SELECT on test_grant_db.* FROM joro 129 INDENTIFIED BY PASSWORD 'plug_dest_passwd'; 130 131DROP DATABASE test_grant_db; 132 133--echo ## GRANT PROXY tests 134 135CREATE USER grant_plug IDENTIFIED WITH 'test_plugin_server' 136 AS 'grant_plug_dest'; 137CREATE USER grant_plug_dest IDENTIFIED BY 'grant_plug_dest_passwd'; 138CREATE USER grant_plug_dest2 IDENTIFIED BY 'grant_plug_dest_passwd2'; 139 140--echo # ALL PRIVILEGES doesn't include PROXY 141GRANT ALL PRIVILEGES ON *.* TO grant_plug; 142--disable_query_log 143--error ER_ACCESS_DENIED_ERROR : this should fail : no grant 144connect(grant_plug_con,localhost,grant_plug,grant_plug_dest); 145--enable_query_log 146 147--error ER_PARSE_ERROR : this should fail : can't combine PROXY 148GRANT ALL PRIVILEGES,PROXY ON grant_plug_dest TO grant_plug; 149 150--echo this should fail : can't combine PROXY 151--error ER_PARSE_ERROR 152GRANT ALL SELECT,PROXY ON grant_plug_dest TO grant_plug; 153 154--echo # this should fail : no such grant 155--error ER_NONEXISTING_GRANT 156REVOKE PROXY ON grant_plug_dest FROM grant_plug; 157 158connect(grant_plug_dest_con,localhost,grant_plug_dest,grant_plug_dest_passwd); 159 160--echo ## testing what an ordinary user can grant 161--echo this should fail : no rights to grant all 162--error ER_ACCESS_DENIED_NO_PASSWORD_ERROR 163GRANT PROXY ON ''@'%%' TO grant_plug; 164 165--echo this should fail : not the same user 166--error ER_ACCESS_DENIED_NO_PASSWORD_ERROR 167GRANT PROXY ON grant_plug TO grant_plug_dest; 168 169# Security context in THD contains two pairs of (user,host) 170# 1. (user,host) pair referring to inbound connection 171# 2. (priv_user,priv_host) pair obtained from mysql.user table after doing 172# authnetication of incoming connection. 173# Granting/revoking proxy privileges, privileges should be checked wrt 174# (priv_user, priv_host) tuple that is obtained from mysql.user table 175# Following is a valid grant because effective user of connection is 176# grant_plug_dest@% and statement is trying to grant proxy on the same 177# user. 178--echo This is a valid grant 179GRANT PROXY ON grant_plug_dest TO grant_plug; 180REVOKE PROXY ON grant_plug_dest FROM grant_plug; 181 182--echo this should work : same user 183GRANT PROXY ON grant_plug_dest TO grant_plug_dest2; 184REVOKE PROXY ON grant_plug_dest FROM grant_plug_dest2; 185 186# grant_plug_dest@localhost is not the same as grant_plug_dest@% 187# so following grant/revoke should fail 188--echo this should fail : not the same user 189--error ER_ACCESS_DENIED_NO_PASSWORD_ERROR 190GRANT PROXY ON grant_plug_dest@localhost TO grant_plug WITH GRANT OPTION; 191--echo this should fail : not the same user 192--error ER_ACCESS_DENIED_NO_PASSWORD_ERROR 193REVOKE PROXY ON grant_plug_dest@localhost FROM grant_plug; 194 195--echo this should fail : can't create users 196--error ER_CANT_CREATE_USER_WITH_GRANT 197GRANT PROXY ON grant_plug_dest TO grant_plug@localhost; 198 199connection default; 200disconnect grant_plug_dest_con; 201 202--echo # test what root can grant 203 204--echo should work : root has PROXY to all users 205GRANT PROXY ON ''@'%%' TO grant_plug; 206REVOKE PROXY ON ''@'%%' FROM grant_plug; 207 208--echo should work : root has PROXY to all users 209GRANT PROXY ON ''@'%%' TO proxy_admin IDENTIFIED BY 'test' 210 WITH GRANT OPTION; 211 212--echo need USAGE : PROXY doesn't contain it. 213GRANT USAGE on *.* TO proxy_admin; 214 215connect (proxy_admin_con,localhost,proxy_admin,test); 216 217--echo should work : proxy_admin has proxy to ''@'%%' 218GRANT PROXY ON future_user TO grant_plug; 219 220connection default; 221disconnect proxy_admin_con; 222 223SHOW GRANTS FOR grant_plug; 224REVOKE PROXY ON future_user FROM grant_plug; 225SHOW GRANTS FOR grant_plug; 226 227--echo ## testing drop user 228CREATE USER test_drop@localhost; 229GRANT PROXY ON future_user TO test_drop@localhost; 230SHOW GRANTS FOR test_drop@localhost; 231DROP USER test_drop@localhost; 232SELECT * FROM mysql.proxies_priv WHERE Host = 'test_drop' AND User = 'localhost'; 233 234DROP USER proxy_admin; 235 236DROP USER grant_plug,grant_plug_dest,grant_plug_dest2; 237 238--echo ## END GRANT PROXY tests 239 240--echo ## cleanup 241DROP USER plug; 242DROP USER plug_dest; 243 244--echo ## @@proxy_user tests 245CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 246CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 247GRANT PROXY ON plug_dest TO plug; 248 249SELECT USER(),CURRENT_USER(),@@LOCAL.proxy_user; 250 251--error ER_INCORRECT_GLOBAL_LOCAL_VAR 252SELECT @@GLOBAL.proxy_user; 253SELECT @@LOCAL.proxy_user; 254 255--error ER_INCORRECT_GLOBAL_LOCAL_VAR 256SET GLOBAL proxy_user = 'test'; 257--error ER_INCORRECT_GLOBAL_LOCAL_VAR 258SET LOCAL proxy_user = 'test'; 259SELECT @@LOCAL.proxy_user; 260 261connect(plug_con,localhost,plug,plug_dest); 262SELECT @@LOCAL.proxy_user; 263connection default; 264disconnect plug_con; 265 266--echo ## cleanup 267DROP USER plug; 268DROP USER plug_dest; 269--echo ## END @@proxy_user tests 270 271--echo ## @@external_user tests 272CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 273CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 274GRANT PROXY ON plug_dest TO plug; 275SELECT USER(),CURRENT_USER(),@@LOCAL.external_user; 276 277--error ER_INCORRECT_GLOBAL_LOCAL_VAR 278SELECT @@GLOBAL.external_user; 279SELECT @@LOCAL.external_user; 280 281--error ER_INCORRECT_GLOBAL_LOCAL_VAR 282SET GLOBAL external_user = 'test'; 283--error ER_INCORRECT_GLOBAL_LOCAL_VAR 284SET LOCAL external_user = 'test'; 285SELECT @@LOCAL.external_user; 286 287connect(plug_con,localhost,plug,plug_dest); 288SELECT @@LOCAL.external_user; 289connection default; 290disconnect plug_con; 291 292--echo ## cleanup 293DROP USER plug; 294DROP USER plug_dest; 295--echo ## END @@external_user tests 296 297--echo # 298--echo # Bug #56798 : Wrong credentials assigned when using a proxy user. 299--echo # 300 301GRANT ALL PRIVILEGES ON *.* TO power_user; 302GRANT USAGE ON anonymous_db.* TO ''@'%%' 303 IDENTIFIED WITH 'test_plugin_server' AS 'power_user'; 304GRANT PROXY ON power_user TO ''@'%%'; 305CREATE DATABASE confidential_db; 306 307connect(plug_con,localhost, test_login_user, power_user, confidential_db); 308SELECT user(),current_user(),@@proxy_user; 309 310connection default; 311disconnect plug_con; 312 313DROP USER power_user; 314DROP USER ''@'%%'; 315DROP DATABASE confidential_db; 316 317--echo # Test case #2 (crash with double grant proxy) 318 319CREATE USER ''@'%%' IDENTIFIED WITH 'test_plugin_server' AS 'standard_user'; 320CREATE USER standard_user; 321CREATE DATABASE shared; 322GRANT ALL PRIVILEGES ON shared.* TO standard_user; 323GRANT PROXY ON standard_user TO ''@'%%'; 324--echo #should not crash 325GRANT PROXY ON standard_user TO ''@'%%'; 326 327DROP USER ''@'%%'; 328DROP USER standard_user; 329DROP DATABASE shared; 330 331--echo # 332--echo # Bug #57551 : Live upgrade fails between 5.1.52 -> 5.5.7-rc 333--echo # 334 335CALL mtr.add_suppression("Missing system table mysql.proxies_priv."); 336 337DROP TABLE mysql.proxies_priv; 338 339--echo # Must come back with mysql.proxies_priv absent. 340--source include/restart_mysqld.inc 341 342--error ER_NO_SUCH_TABLE 343SELECT * FROM mysql.proxies_priv; 344 345CREATE USER u1@localhost; 346GRANT ALL PRIVILEGES ON *.* TO u1@localhost; 347REVOKE ALL PRIVILEGES ON *.* FROM u1@localhost; 348GRANT ALL PRIVILEGES ON *.* TO u1@localhost; 349 350CREATE USER u2@localhost; 351GRANT ALL PRIVILEGES ON *.* TO u2@localhost; 352 353--echo # access denied because of no privileges to root 354--error ER_ACCESS_DENIED_NO_PASSWORD_ERROR 355GRANT PROXY ON u2@localhost TO u1@localhost; 356 357--echo # access denied because of no privileges to root 358--error ER_ACCESS_DENIED_NO_PASSWORD_ERROR 359REVOKE PROXY ON u2@localhost FROM u1@localhost; 360 361--echo # go try graning proxy on itself, so that it will need the table 362connect(proxy_granter_con,localhost,u2,); 363 364--error ER_NO_SUCH_TABLE 365GRANT PROXY ON u2@localhost TO u1@localhost; 366--error ER_NO_SUCH_TABLE 367REVOKE PROXY ON u2@localhost FROM u1@localhost; 368 369connection default; 370disconnect proxy_granter_con; 371 372--echo # test if REVOKE works without the proxies_priv table 373REVOKE ALL PRIVILEGES ON *.* FROM u1@localhost, u2@localhost; 374 375--echo # test if DROP USER work without the proxies_priv table 376DROP USER u1@localhost,u2@localhost; 377 378--echo # test if FLUSH PRIVILEGES works without the proxies_priv table 379FLUSH PRIVILEGES; 380 381--exec $MYSQL_UPGRADE --skip-verbose --force 2>&1 382--query_vertical SELECT Host,User,Proxied_host,Proxied_user,With_grant FROM mysql.proxies_priv 383 384FLUSH PRIVILEGES; 385 386--echo # 387--echo # Bug#58139 : default-auth option not recognized in MySQL standard 388--echo # command line clients 389--echo # 390 391--echo # Executing 'mysql' 392--exec $MYSQL -u root -S $MASTER_MYSOCK -P $MASTER_MYPORT --default-auth=auth_test_plugin -e "SELECT 1" 393 394--echo # Executing 'mysqladmin' 395--exec $MYSQLADMIN -u root -S $MASTER_MYSOCK -P $MASTER_MYPORT --default-auth=auth_test_plugin ping 396 397--echo # Executing 'mysqldump' 398--exec $MYSQL_DUMP -u root -S $MASTER_MYSOCK -P $MASTER_MYPORT --compact --default-auth=auth_test_plugin test 399 400--echo # Executing 'mysql_upgrade' 401--exec $MYSQL_UPGRADE -u root -S $MASTER_MYSOCK -P $MASTER_MYPORT --default-auth=auth_test_plugin --skip-verbose --force --upgrade-system-tables 402 403--echo # 404--echo # Bug #59657: Move the client authentication_pam plugin into the 405--echo # server repository 406--echo # 407 408CREATE USER uplain@localhost IDENTIFIED WITH 'cleartext_plugin_server' 409 AS 'cleartext_test'; 410 411--echo ## test plugin auth 412--disable_query_log 413--error ER_ACCESS_DENIED_ERROR : this should fail : no grant 414connect(cleartext_fail_con,localhost,uplain,cleartext_test2); 415--enable_query_log 416 417connect(cleartext_con,localhost,uplain,cleartext_test); 418select USER(),CURRENT_USER(); 419 420connection default; 421disconnect cleartext_con; 422DROP USER uplain@localhost; 423 424# prepare for two tests that use mysql.user table 425source include/switch_to_mysql_user.inc; 426drop view mysql.user_bak; 427 428--echo # 429--echo # Bug #59038 : mysql.user.authentication_string column 430--echo # causes configuration wizard to fail 431 432INSERT IGNORE INTO mysql.user( 433 Host, 434 User, 435 Password, 436 Select_priv, 437 Insert_priv, 438 Update_priv, 439 Delete_priv, 440 Create_priv, 441 Drop_priv, 442 Reload_priv, 443 Shutdown_priv, 444 Process_priv, 445 File_priv, 446 Grant_priv, 447 References_priv, 448 Index_priv, 449 Alter_priv, 450 Show_db_priv, 451 Super_priv, 452 Create_tmp_table_priv, 453 Lock_tables_priv, 454 Execute_priv, 455 Repl_slave_priv, 456 Repl_client_priv, 457 /*!50001 458 Create_view_priv, 459 Show_view_priv, 460 Create_routine_priv, 461 Alter_routine_priv, 462 Create_user_priv, 463 */ 464 ssl_type, 465 ssl_cipher, 466 x509_issuer, 467 x509_subject, 468 max_questions, 469 max_updates, 470 max_connections) 471VALUES ( 472 'localhost', 473 'inserttest', '', 474 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 475 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 476 /*!50001 'Y', 'Y', 'Y', 'Y', 'Y', */'', '', '', '', '0', '0', '0'); 477FLUSH PRIVILEGES; 478DROP USER inserttest@localhost; 479SELECT IS_NULLABLE, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE 480 COLUMN_NAME IN ('authentication_string', 'plugin') AND 481 TABLE_NAME='user' AND 482 TABLE_SCHEMA='mysql' 483ORDER BY COLUMN_NAME; 484 485--echo # 486--echo # Bug #11936829: diff. between mysql.user (authentication_string) 487--echo # in fresh and upgraded 5.5.11 488--echo # 489 490SELECT IS_NULLABLE, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 491 WHERE TABLE_SCHEMA= 'mysql' AND TABLE_NAME= 'user' AND 492 COLUMN_NAME IN ('plugin', 'authentication_string') 493 ORDER BY COLUMN_NAME; 494ALTER TABLE mysql.user MODIFY plugin char(64) DEFAULT '' NOT NULL; 495ALTER TABLE mysql.user MODIFY authentication_string TEXT NOT NULL; 496 497--echo Run mysql_upgrade on a 5.5.10 external authentication column layout 498--exec $MYSQL_UPGRADE --skip-verbose --force 2>&1 499 500SELECT IS_NULLABLE, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 501 WHERE TABLE_SCHEMA= 'mysql' AND TABLE_NAME= 'user' AND 502 COLUMN_NAME IN ('plugin', 'authentication_string') 503 ORDER BY COLUMN_NAME; 504let $datadir= `select @@datadir`; 505remove_file $datadir/mysql_upgrade_info; 506drop table mysql.global_priv; 507rename table mysql.global_priv_bak to mysql.global_priv; 508 509--echo # 510--echo # Bug # 11766641: 59792: BIN/MYSQL -UUNKNOWN -PUNKNOWN 511--echo # .-> USING PASSWORD: NO 512--echo # 513 514--echo # shoud contain "using password=yes" 515--error 1 516--exec $MYSQL -uunknown -punknown 2>&1 517 518--echo # shoud contain "using password=no" 519--error 1 520--exec $MYSQL -uunknown 2>&1 521 522--echo # 523--echo # Bug #12610784: SET PASSWORD INCORRECTLY KEEP AN OLD EMPTY PASSWORD 524--echo # 525 526CREATE USER bug12610784@localhost; 527SET PASSWORD FOR bug12610784@localhost = PASSWORD('secret'); 528--disable_query_log 529--error ER_ACCESS_DENIED_ERROR 530connect(b12610784,localhost,bug12610784,,test); 531--enable_query_log 532connect(b12610784,localhost,bug12610784,secret,test); 533connection default; 534disconnect b12610784; 535DROP USER bug12610784@localhost; 536 537 538--echo # 539--echo # Bug #12818542: PAM: ADDING PASSWORD FOR AN ACCOUNT DISABLES PAM 540--echo # AUTHENTICATION SETTINGS 541--echo # 542 543CREATE USER bug12818542@localhost 544 IDENTIFIED WITH 'test_plugin_server' AS 'bug12818542_dest'; 545CREATE USER bug12818542_dest@localhost 546 IDENTIFIED BY 'bug12818542_dest_passwd'; 547GRANT PROXY ON bug12818542_dest@localhost TO bug12818542@localhost; 548 549connect(bug12818542_con,localhost,bug12818542,bug12818542_dest); 550SELECT USER(),CURRENT_USER(); 551 552SET PASSWORD = PASSWORD('bruhaha'); 553 554connection default; 555disconnect bug12818542_con; 556 557connect(bug12818542_con2,localhost,bug12818542,bug12818542_dest); 558SELECT USER(),CURRENT_USER(); 559 560connection default; 561disconnect bug12818542_con2; 562 563DROP USER bug12818542@localhost; 564DROP USER bug12818542_dest@localhost; 565 566SET GLOBAL SQL_MODE=default; 567--echo End of 5.5 tests 568