1SET GLOBAL SQL_MODE=""; 2SET LOCAL SQL_MODE=""; 3SELECT PLUGIN_STATUS, PLUGIN_TYPE, PLUGIN_DESCRIPTION 4FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME='test_plugin_server'; 5PLUGIN_STATUS ACTIVE 6PLUGIN_TYPE AUTHENTICATION 7PLUGIN_DESCRIPTION plugin API test plugin 8CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 9CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 10SELECT plugin,authentication_string FROM mysql.user WHERE User='plug'; 11plugin authentication_string 12test_plugin_server plug_dest 13## test plugin auth 14connect(localhost,plug,plug_dest,test,MYSQL_PORT,MYSQL_SOCK); 15connect plug_con,localhost,plug,plug_dest; 16ERROR 28000: Access denied for user 'plug'@'localhost' (using password: YES) 17GRANT PROXY ON plug_dest TO plug; 18test proxies_priv columns 19SELECT * FROM mysql.proxies_priv WHERE user !='root'; 20Host User Proxied_host Proxied_user With_grant Grantor Timestamp 21xx plug % plug_dest 0 root@localhost xx 22test mysql.proxies_priv; 23SHOW CREATE TABLE mysql.proxies_priv; 24Table Create Table 25proxies_priv CREATE TABLE `proxies_priv` ( 26 `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', 27 `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '', 28 `Proxied_host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', 29 `Proxied_user` char(80) COLLATE utf8_bin NOT NULL DEFAULT '', 30 `With_grant` tinyint(1) NOT NULL DEFAULT 0, 31 `Grantor` char(141) COLLATE utf8_bin NOT NULL DEFAULT '', 32 `Timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 33 PRIMARY KEY (`Host`,`User`,`Proxied_host`,`Proxied_user`), 34 KEY `Grantor` (`Grantor`) 35) ENGINE=Aria DEFAULT CHARSET=utf8 COLLATE=utf8_bin PAGE_CHECKSUM=1 TRANSACTIONAL=1 COMMENT='User proxy privileges' 36connect plug_con,localhost,plug,plug_dest; 37select USER(),CURRENT_USER(); 38USER() CURRENT_USER() 39plug@localhost plug_dest@% 40## test SET PASSWORD 41SET PASSWORD = PASSWORD('plug_dest'); 42connection default; 43disconnect plug_con; 44## test bad credentials 45connect(localhost,plug,bad_credentials,test,MYSQL_PORT,MYSQL_SOCK); 46connect plug_con,localhost,plug,bad_credentials; 47ERROR 28000: Access denied for user 'plug'@'localhost' (using password: YES) 48## test bad default plugin : nothing bad happens, as that plugin was't required by the server 49connect plug_con_wrongp,localhost,plug,plug_dest,,,,,wrong_plugin_name; 50select USER(),CURRENT_USER(); 51USER() CURRENT_USER() 52plug@localhost plug_dest@% 53connection default; 54disconnect plug_con_wrongp; 55## test correct default plugin 56connect plug_con_rightp,localhost,plug,plug_dest,,,,,auth_test_plugin; 57select USER(),CURRENT_USER(); 58USER() CURRENT_USER() 59plug@localhost plug_dest@% 60connection default; 61disconnect plug_con_rightp; 62## test no_auto_create_user sql mode with plugin users 63SET @@sql_mode=no_auto_create_user; 64GRANT INSERT ON TEST.* TO grant_user IDENTIFIED WITH 'test_plugin_server'; 65SET @@sql_mode=""; 66DROP USER grant_user; 67## test utf-8 user name 68CREATE USER `Ÿ` IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 69GRANT PROXY ON plug_dest TO `Ÿ`; 70connect non_ascii,localhost,Ÿ,plug_dest; 71select USER(),CURRENT_USER(); 72USER() CURRENT_USER() 73Ÿ@localhost plug_dest@% 74connection default; 75disconnect non_ascii; 76DROP USER `Ÿ`; 77## test GRANT ... IDENTIFIED WITH/BY ... 78CREATE DATABASE test_grant_db; 79# create new user via GRANT WITH 80GRANT ALL PRIVILEGES ON test_grant_db.* TO new_grant_user 81IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 82GRANT PROXY ON plug_dest TO new_grant_user; 83connect plug_con_grant,localhost,new_grant_user,plug_dest; 84select USER(),CURRENT_USER(); 85USER() CURRENT_USER() 86new_grant_user@localhost plug_dest@% 87USE test_grant_db; 88CREATE TABLE t1 (a INT); 89DROP TABLE t1; 90connection default; 91disconnect plug_con_grant; 92REVOKE ALL PRIVILEGES ON test_grant_db.* FROM new_grant_user; 93# try re-create existing user via GRANT IDENTIFIED BY 94GRANT ALL PRIVILEGES ON test_grant_db.* TO new_grant_user 95IDENTIFIED BY 'new_password'; 96connect(localhost,new_grant_user,plug_dest,test,MYSQL_PORT,MYSQL_SOCK); 97connect plug_con_grant_deny,localhost,new_grant_user,plug_dest; 98ERROR 28000: Access denied for user 'new_grant_user'@'localhost' (using password: YES) 99connect plug_con_grant,localhost,new_grant_user,new_password; 100select USER(),CURRENT_USER(); 101USER() CURRENT_USER() 102new_grant_user@localhost new_grant_user@% 103USE test_grant_db; 104CREATE TABLE t1 (a INT); 105DROP TABLE t1; 106connection default; 107disconnect plug_con_grant; 108DROP USER new_grant_user; 109# try re-create existing user via GRANT IDENTIFIED WITH 110GRANT ALL PRIVILEGES ON test_grant_db.* TO plug 111IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 112GRANT ALL PRIVILEGES ON test_grant_db.* TO plug_dest 113IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 114REVOKE SELECT on test_grant_db.* FROM joro 115INDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 116ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'' at line 2 117REVOKE SELECT on test_grant_db.* FROM joro 118INDENTIFIED BY 'plug_dest_passwd'; 119ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INDENTIFIED BY 'plug_dest_passwd'' at line 2 120REVOKE SELECT on test_grant_db.* FROM joro 121INDENTIFIED BY PASSWORD 'plug_dest_passwd'; 122ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INDENTIFIED BY PASSWORD 'plug_dest_passwd'' at line 2 123DROP DATABASE test_grant_db; 124## GRANT PROXY tests 125CREATE USER grant_plug IDENTIFIED WITH 'test_plugin_server' 126AS 'grant_plug_dest'; 127CREATE USER grant_plug_dest IDENTIFIED BY 'grant_plug_dest_passwd'; 128CREATE USER grant_plug_dest2 IDENTIFIED BY 'grant_plug_dest_passwd2'; 129# ALL PRIVILEGES doesn't include PROXY 130GRANT ALL PRIVILEGES ON *.* TO grant_plug; 131ERROR 28000: Access denied for user 'grant_plug'@'localhost' (using password: YES) 132GRANT ALL PRIVILEGES,PROXY ON grant_plug_dest TO grant_plug; 133ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PROXY ON grant_plug_dest TO grant_plug' at line 1 134this should fail : can't combine PROXY 135GRANT ALL SELECT,PROXY ON grant_plug_dest TO grant_plug; 136ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT,PROXY ON grant_plug_dest TO grant_plug' at line 1 137# this should fail : no such grant 138REVOKE PROXY ON grant_plug_dest FROM grant_plug; 139ERROR 42000: There is no such grant defined for user 'grant_plug' on host '%' 140connect grant_plug_dest_con,localhost,grant_plug_dest,grant_plug_dest_passwd; 141## testing what an ordinary user can grant 142this should fail : no rights to grant all 143GRANT PROXY ON ''@'%%' TO grant_plug; 144ERROR 28000: Access denied for user 'grant_plug_dest'@'localhost' 145this should fail : not the same user 146GRANT PROXY ON grant_plug TO grant_plug_dest; 147ERROR 28000: Access denied for user 'grant_plug_dest'@'localhost' 148This is a valid grant 149GRANT PROXY ON grant_plug_dest TO grant_plug; 150REVOKE PROXY ON grant_plug_dest FROM grant_plug; 151this should work : same user 152GRANT PROXY ON grant_plug_dest TO grant_plug_dest2; 153REVOKE PROXY ON grant_plug_dest FROM grant_plug_dest2; 154this should fail : not the same user 155GRANT PROXY ON grant_plug_dest@localhost TO grant_plug WITH GRANT OPTION; 156ERROR 28000: Access denied for user 'grant_plug_dest'@'localhost' 157this should fail : not the same user 158REVOKE PROXY ON grant_plug_dest@localhost FROM grant_plug; 159ERROR 28000: Access denied for user 'grant_plug_dest'@'localhost' 160this should fail : can't create users 161GRANT PROXY ON grant_plug_dest TO grant_plug@localhost; 162ERROR 42000: You are not allowed to create a user with GRANT 163connection default; 164disconnect grant_plug_dest_con; 165# test what root can grant 166should work : root has PROXY to all users 167GRANT PROXY ON ''@'%%' TO grant_plug; 168REVOKE PROXY ON ''@'%%' FROM grant_plug; 169should work : root has PROXY to all users 170GRANT PROXY ON ''@'%%' TO proxy_admin IDENTIFIED BY 'test' 171WITH GRANT OPTION; 172need USAGE : PROXY doesn't contain it. 173GRANT USAGE on *.* TO proxy_admin; 174connect proxy_admin_con,localhost,proxy_admin,test; 175should work : proxy_admin has proxy to ''@'%%' 176GRANT PROXY ON future_user TO grant_plug; 177connection default; 178disconnect proxy_admin_con; 179SHOW GRANTS FOR grant_plug; 180Grants for grant_plug@% 181GRANT ALL PRIVILEGES ON *.* TO `grant_plug`@`%` IDENTIFIED VIA test_plugin_server USING 'grant_plug_dest' 182GRANT PROXY ON 'future_user'@'%' TO 'grant_plug'@'%' 183REVOKE PROXY ON future_user FROM grant_plug; 184SHOW GRANTS FOR grant_plug; 185Grants for grant_plug@% 186GRANT ALL PRIVILEGES ON *.* TO `grant_plug`@`%` IDENTIFIED VIA test_plugin_server USING 'grant_plug_dest' 187## testing drop user 188CREATE USER test_drop@localhost; 189GRANT PROXY ON future_user TO test_drop@localhost; 190SHOW GRANTS FOR test_drop@localhost; 191Grants for test_drop@localhost 192GRANT USAGE ON *.* TO `test_drop`@`localhost` 193GRANT PROXY ON 'future_user'@'%' TO 'test_drop'@'localhost' 194DROP USER test_drop@localhost; 195SELECT * FROM mysql.proxies_priv WHERE Host = 'test_drop' AND User = 'localhost'; 196Host User Proxied_host Proxied_user With_grant Grantor Timestamp 197DROP USER proxy_admin; 198DROP USER grant_plug,grant_plug_dest,grant_plug_dest2; 199## END GRANT PROXY tests 200## cleanup 201DROP USER plug; 202DROP USER plug_dest; 203## @@proxy_user tests 204CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 205CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 206GRANT PROXY ON plug_dest TO plug; 207SELECT USER(),CURRENT_USER(),@@LOCAL.proxy_user; 208USER() CURRENT_USER() @@LOCAL.proxy_user 209root@localhost root@localhost NULL 210SELECT @@GLOBAL.proxy_user; 211ERROR HY000: Variable 'proxy_user' is a SESSION variable 212SELECT @@LOCAL.proxy_user; 213@@LOCAL.proxy_user 214NULL 215SET GLOBAL proxy_user = 'test'; 216ERROR HY000: Variable 'proxy_user' is a read only variable 217SET LOCAL proxy_user = 'test'; 218ERROR HY000: Variable 'proxy_user' is a read only variable 219SELECT @@LOCAL.proxy_user; 220@@LOCAL.proxy_user 221NULL 222connect plug_con,localhost,plug,plug_dest; 223SELECT @@LOCAL.proxy_user; 224@@LOCAL.proxy_user 225'plug'@'%' 226connection default; 227disconnect plug_con; 228## cleanup 229DROP USER plug; 230DROP USER plug_dest; 231## END @@proxy_user tests 232## @@external_user tests 233CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; 234CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; 235GRANT PROXY ON plug_dest TO plug; 236SELECT USER(),CURRENT_USER(),@@LOCAL.external_user; 237USER() CURRENT_USER() @@LOCAL.external_user 238root@localhost root@localhost NULL 239SELECT @@GLOBAL.external_user; 240ERROR HY000: Variable 'external_user' is a SESSION variable 241SELECT @@LOCAL.external_user; 242@@LOCAL.external_user 243NULL 244SET GLOBAL external_user = 'test'; 245ERROR HY000: Variable 'external_user' is a read only variable 246SET LOCAL external_user = 'test'; 247ERROR HY000: Variable 'external_user' is a read only variable 248SELECT @@LOCAL.external_user; 249@@LOCAL.external_user 250NULL 251connect plug_con,localhost,plug,plug_dest; 252SELECT @@LOCAL.external_user; 253@@LOCAL.external_user 254plug_dest 255connection default; 256disconnect plug_con; 257## cleanup 258DROP USER plug; 259DROP USER plug_dest; 260## END @@external_user tests 261# 262# Bug #56798 : Wrong credentials assigned when using a proxy user. 263# 264GRANT ALL PRIVILEGES ON *.* TO power_user; 265GRANT USAGE ON anonymous_db.* TO ''@'%%' 266 IDENTIFIED WITH 'test_plugin_server' AS 'power_user'; 267GRANT PROXY ON power_user TO ''@'%%'; 268CREATE DATABASE confidential_db; 269connect plug_con,localhost, test_login_user, power_user, confidential_db; 270SELECT user(),current_user(),@@proxy_user; 271user() current_user() @@proxy_user 272test_login_user@localhost power_user@% ''@'%%' 273connection default; 274disconnect plug_con; 275DROP USER power_user; 276DROP USER ''@'%%'; 277DROP DATABASE confidential_db; 278# Test case #2 (crash with double grant proxy) 279CREATE USER ''@'%%' IDENTIFIED WITH 'test_plugin_server' AS 'standard_user'; 280CREATE USER standard_user; 281CREATE DATABASE shared; 282GRANT ALL PRIVILEGES ON shared.* TO standard_user; 283GRANT PROXY ON standard_user TO ''@'%%'; 284#should not crash 285GRANT PROXY ON standard_user TO ''@'%%'; 286DROP USER ''@'%%'; 287DROP USER standard_user; 288DROP DATABASE shared; 289# 290# Bug #57551 : Live upgrade fails between 5.1.52 -> 5.5.7-rc 291# 292CALL mtr.add_suppression("Missing system table mysql.proxies_priv."); 293DROP TABLE mysql.proxies_priv; 294# Must come back with mysql.proxies_priv absent. 295# restart 296SELECT * FROM mysql.proxies_priv; 297ERROR 42S02: Table 'mysql.proxies_priv' doesn't exist 298CREATE USER u1@localhost; 299GRANT ALL PRIVILEGES ON *.* TO u1@localhost; 300REVOKE ALL PRIVILEGES ON *.* FROM u1@localhost; 301GRANT ALL PRIVILEGES ON *.* TO u1@localhost; 302CREATE USER u2@localhost; 303GRANT ALL PRIVILEGES ON *.* TO u2@localhost; 304# access denied because of no privileges to root 305GRANT PROXY ON u2@localhost TO u1@localhost; 306ERROR 28000: Access denied for user 'root'@'localhost' 307# access denied because of no privileges to root 308REVOKE PROXY ON u2@localhost FROM u1@localhost; 309ERROR 28000: Access denied for user 'root'@'localhost' 310# go try graning proxy on itself, so that it will need the table 311connect proxy_granter_con,localhost,u2,; 312GRANT PROXY ON u2@localhost TO u1@localhost; 313ERROR 42S02: Table 'mysql.proxies_priv' doesn't exist 314REVOKE PROXY ON u2@localhost FROM u1@localhost; 315ERROR 42S02: Table 'mysql.proxies_priv' doesn't exist 316connection default; 317disconnect proxy_granter_con; 318# test if REVOKE works without the proxies_priv table 319REVOKE ALL PRIVILEGES ON *.* FROM u1@localhost, u2@localhost; 320# test if DROP USER work without the proxies_priv table 321DROP USER u1@localhost,u2@localhost; 322# test if FLUSH PRIVILEGES works without the proxies_priv table 323FLUSH PRIVILEGES; 324SELECT Host,User,Proxied_host,Proxied_user,With_grant FROM mysql.proxies_priv; 325Host localhost 326User root 327Proxied_host 328Proxied_user 329With_grant 1 330FLUSH PRIVILEGES; 331# 332# Bug#58139 : default-auth option not recognized in MySQL standard 333# command line clients 334# 335# Executing 'mysql' 3361 3371 338# Executing 'mysqladmin' 339mysqld is alive 340# Executing 'mysqldump' 341# Executing 'mysql_upgrade' 342# 343# Bug #59657: Move the client authentication_pam plugin into the 344# server repository 345# 346CREATE USER uplain@localhost IDENTIFIED WITH 'cleartext_plugin_server' 347 AS 'cleartext_test'; 348## test plugin auth 349ERROR 28000: Access denied for user 'uplain'@'localhost' (using password: YES) 350connect cleartext_con,localhost,uplain,cleartext_test; 351select USER(),CURRENT_USER(); 352USER() CURRENT_USER() 353uplain@localhost uplain@localhost 354connection default; 355disconnect cleartext_con; 356DROP USER uplain@localhost; 357# switching from mysql.global_priv to mysql.user 358drop view mysql.user_bak; 359# 360# Bug #59038 : mysql.user.authentication_string column 361# causes configuration wizard to fail 362INSERT IGNORE INTO mysql.user( 363Host, 364User, 365Password, 366Select_priv, 367Insert_priv, 368Update_priv, 369Delete_priv, 370Create_priv, 371Drop_priv, 372Reload_priv, 373Shutdown_priv, 374Process_priv, 375File_priv, 376Grant_priv, 377References_priv, 378Index_priv, 379Alter_priv, 380Show_db_priv, 381Super_priv, 382Create_tmp_table_priv, 383Lock_tables_priv, 384Execute_priv, 385Repl_slave_priv, 386Repl_client_priv, 387/*!50001 388Create_view_priv, 389Show_view_priv, 390Create_routine_priv, 391Alter_routine_priv, 392Create_user_priv, 393*/ 394ssl_type, 395ssl_cipher, 396x509_issuer, 397x509_subject, 398max_questions, 399max_updates, 400max_connections) 401VALUES ( 402'localhost', 403'inserttest', '', 404'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 405'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 406/*!50001 'Y', 'Y', 'Y', 'Y', 'Y', */'', '', '', '', '0', '0', '0'); 407Warnings: 408Warning 1364 Field 'authentication_string' doesn't have a default value 409FLUSH PRIVILEGES; 410DROP USER inserttest@localhost; 411SELECT IS_NULLABLE, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE 412COLUMN_NAME IN ('authentication_string', 'plugin') AND 413TABLE_NAME='user' AND 414TABLE_SCHEMA='mysql' 415ORDER BY COLUMN_NAME; 416IS_NULLABLE COLUMN_NAME 417NO authentication_string 418NO plugin 419# 420# Bug #11936829: diff. between mysql.user (authentication_string) 421# in fresh and upgraded 5.5.11 422# 423SELECT IS_NULLABLE, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 424WHERE TABLE_SCHEMA= 'mysql' AND TABLE_NAME= 'user' AND 425COLUMN_NAME IN ('plugin', 'authentication_string') 426ORDER BY COLUMN_NAME; 427IS_NULLABLE COLUMN_NAME 428NO authentication_string 429NO plugin 430ALTER TABLE mysql.user MODIFY plugin char(64) DEFAULT '' NOT NULL; 431ALTER TABLE mysql.user MODIFY authentication_string TEXT NOT NULL; 432Run mysql_upgrade on a 5.5.10 external authentication column layout 433SELECT IS_NULLABLE, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 434WHERE TABLE_SCHEMA= 'mysql' AND TABLE_NAME= 'user' AND 435COLUMN_NAME IN ('plugin', 'authentication_string') 436ORDER BY COLUMN_NAME; 437IS_NULLABLE COLUMN_NAME 438NO authentication_string 439NO plugin 440drop table mysql.global_priv; 441rename table mysql.global_priv_bak to mysql.global_priv; 442# 443# Bug # 11766641: 59792: BIN/MYSQL -UUNKNOWN -PUNKNOWN 444# .-> USING PASSWORD: NO 445# 446# shoud contain "using password=yes" 447ERROR 1045 (28000): Access denied for user 'unknown'@'localhost' (using password: YES) 448# shoud contain "using password=no" 449ERROR 1045 (28000): Access denied for user 'unknown'@'localhost' (using password: NO) 450# 451# Bug #12610784: SET PASSWORD INCORRECTLY KEEP AN OLD EMPTY PASSWORD 452# 453CREATE USER bug12610784@localhost; 454SET PASSWORD FOR bug12610784@localhost = PASSWORD('secret'); 455ERROR 28000: Access denied for user 'bug12610784'@'localhost' (using password: NO) 456connect b12610784,localhost,bug12610784,secret,test; 457connection default; 458disconnect b12610784; 459DROP USER bug12610784@localhost; 460# 461# Bug #12818542: PAM: ADDING PASSWORD FOR AN ACCOUNT DISABLES PAM 462# AUTHENTICATION SETTINGS 463# 464CREATE USER bug12818542@localhost 465IDENTIFIED WITH 'test_plugin_server' AS 'bug12818542_dest'; 466CREATE USER bug12818542_dest@localhost 467IDENTIFIED BY 'bug12818542_dest_passwd'; 468GRANT PROXY ON bug12818542_dest@localhost TO bug12818542@localhost; 469connect bug12818542_con,localhost,bug12818542,bug12818542_dest; 470SELECT USER(),CURRENT_USER(); 471USER() CURRENT_USER() 472bug12818542@localhost bug12818542_dest@localhost 473SET PASSWORD = PASSWORD('bruhaha'); 474connection default; 475disconnect bug12818542_con; 476connect bug12818542_con2,localhost,bug12818542,bug12818542_dest; 477SELECT USER(),CURRENT_USER(); 478USER() CURRENT_USER() 479bug12818542@localhost bug12818542_dest@localhost 480connection default; 481disconnect bug12818542_con2; 482DROP USER bug12818542@localhost; 483DROP USER bug12818542_dest@localhost; 484SET GLOBAL SQL_MODE=default; 485End of 5.5 tests 486