1select priv into @root_priv from mysql.global_priv where user='root' and host='localhost'; 2set GLOBAL sql_mode=""; 3set LOCAL sql_mode=""; 4SET NAMES binary; 5drop database if exists mysqltest; 6drop database if exists mysqltest_1; 7delete from mysql.user where user like 'mysqltest\_%'; 8delete from mysql.db where user like 'mysqltest\_%'; 9delete from mysql.tables_priv where user like 'mysqltest\_%'; 10delete from mysql.columns_priv where user like 'mysqltest\_%'; 11flush privileges; 12grant all privileges on `my\_1`.* to mysqltest_1@localhost with grant option; 13grant create user on *.* to mysqltest_1@localhost; 14create user mysqltest_2@localhost; 15connect user_a,localhost,mysqltest_1,,; 16connection user_a; 17grant select on `my\_1`.* to mysqltest_2@localhost; 18grant select on `my\_1`.* to mysqltest_2@localhost identified by 'pass'; 19ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysql' 20disconnect user_a; 21connection default; 22grant update on mysql.* to mysqltest_1@localhost; 23connect user_b,localhost,mysqltest_1,,; 24connection user_b; 25grant select on `my\_1`.* to mysqltest_2@localhost identified by 'pass'; 26grant select on `my\_1`.* to mysqltest_3@localhost; 27disconnect user_b; 28connection default; 29grant insert on mysql.* to mysqltest_1@localhost; 30connect user_c,localhost,mysqltest_1,,; 31connection user_c; 32grant select on `my\_1`.* to mysqltest_3@localhost; 33grant select on `my\_1`.* to mysqltest_4@localhost identified by 'pass'; 34disconnect user_c; 35connection default; 36delete from mysql.user where user like 'mysqltest\_%'; 37delete from mysql.db where user like 'mysqltest\_%'; 38delete from mysql.tables_priv where user like 'mysqltest\_%'; 39delete from mysql.columns_priv where user like 'mysqltest\_%'; 40flush privileges; 41grant all privileges on `my\_%`.* to mysqltest_1@localhost with grant option; 42grant create user on *.* to mysqltest_1@localhost; 43connect user1,localhost,mysqltest_1,,; 44connection user1; 45select current_user(); 46current_user() 47mysqltest_1@localhost 48grant all privileges on `my\_1`.* to mysqltest_2@localhost with grant option; 49grant all privileges on `my_%`.* to mysqltest_3@localhost with grant option; 50ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'my_%' 51set @@sql_mode='NO_AUTO_CREATE_USER'; 52select @@sql_mode; 53@@sql_mode 54NO_AUTO_CREATE_USER 55grant select on `my\_1`.* to mysqltest_4@localhost with grant option; 56ERROR 28000: Can't find any matching row in the user table 57grant select on `my\_1`.* to mysqltest_4@localhost identified by 'mypass' 58with grant option; 59disconnect user1; 60connection default; 61show grants for mysqltest_1@localhost; 62Grants for mysqltest_1@localhost 63GRANT CREATE USER ON *.* TO `mysqltest_1`@`localhost` 64GRANT ALL PRIVILEGES ON `my\_%`.* TO `mysqltest_1`@`localhost` WITH GRANT OPTION 65show grants for mysqltest_2@localhost; 66Grants for mysqltest_2@localhost 67GRANT USAGE ON *.* TO `mysqltest_2`@`localhost` 68GRANT ALL PRIVILEGES ON `my\_1`.* TO `mysqltest_2`@`localhost` WITH GRANT OPTION 69show grants for mysqltest_3@localhost; 70ERROR 42000: There is no such grant defined for user 'mysqltest_3' on host 'localhost' 71delete from mysql.user where user like 'mysqltest\_%'; 72delete from mysql.db where user like 'mysqltest\_%'; 73flush privileges; 74create database mysqltest_1; 75grant all privileges on `mysqltest\_1`.* to mysqltest_1@localhost with grant option; 76connect user2,localhost,mysqltest_1,,; 77connection user2; 78select current_user(); 79current_user() 80mysqltest_1@localhost 81show databases; 82Database 83information_schema 84mysqltest_1 85test 86grant all privileges on `mysqltest_1`.* to mysqltest_1@localhost with grant option; 87ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest_1' 88disconnect user2; 89connection default; 90show grants for mysqltest_1@localhost; 91Grants for mysqltest_1@localhost 92GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` 93GRANT ALL PRIVILEGES ON `mysqltest\_1`.* TO `mysqltest_1`@`localhost` WITH GRANT OPTION 94delete from mysql.user where user like 'mysqltest\_%'; 95delete from mysql.db where user like 'mysqltest\_%'; 96drop database mysqltest_1; 97flush privileges; 98create database mysqltest; 99grant INSERT, SELECT on mysqltest.* to mysqltest_1@localhost; 100flush privileges; 101use mysqltest; 102create table t1 (id int primary key, data varchar(255)); 103connect mrbad, localhost, mysqltest_1,,mysqltest; 104connection mrbad; 105show grants for current_user(); 106Grants for mysqltest_1@localhost 107GRANT USAGE ON *.* TO `mysqltest_1`@`localhost` 108GRANT SELECT, INSERT ON `mysqltest`.* TO `mysqltest_1`@`localhost` 109insert into t1 values (1, 'I can''t change it!'); 110update t1 set data='I can change it!' where id = 1; 111ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 't1' 112insert into t1 values (1, 'XXX') on duplicate key update data= 'I can change it!'; 113ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 't1' 114select * from t1; 115id data 1161 I can't change it! 117disconnect mrbad; 118connection default; 119drop table t1; 120delete from mysql.user where user like 'mysqltest\_%'; 121delete from mysql.db where user like 'mysqltest\_%'; 122flush privileges; 123create table t1 (a int, b int); 124grant select (a) on t1 to mysqltest_1@localhost with grant option; 125connect mrugly, localhost, mysqltest_1,,mysqltest; 126connection mrugly; 127grant select (a,b) on t1 to mysqltest_2@localhost; 128ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'b' in table 't1' 129grant select on t1 to mysqltest_3@localhost; 130ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 't1' 131disconnect mrugly; 132connection default; 133drop table t1; 134delete from mysql.user where user like 'mysqltest\_%'; 135delete from mysql.db where user like 'mysqltest\_%'; 136delete from mysql.tables_priv where user like 'mysqltest\_%'; 137delete from mysql.columns_priv where user like 'mysqltest\_%'; 138flush privileges; 139drop database mysqltest; 140use test; 141create user mysqltest_1@host1; 142create user mysqltest_2@host2; 143create user mysqltest_3@host3; 144create user mysqltest_4@host4; 145create user mysqltest_5@host5; 146create user mysqltest_6@host6; 147create user mysqltest_7@host7; 148flush privileges; 149drop user mysqltest_3@host3; 150connect con8,127.0.0.1,root,,test,$MASTER_MYPORT,; 151disconnect con8; 152connection default; 153drop user mysqltest_1@host1, mysqltest_2@host2, mysqltest_4@host4, 154mysqltest_5@host5, mysqltest_6@host6, mysqltest_7@host7; 155connect con9,127.0.0.1,root,,test,$MASTER_MYPORT,; 156disconnect con9; 157connection default; 158create database mysqltest_1; 159grant select, insert, update on `mysqltest\_1`.* to mysqltest_1@localhost; 160connect con10,localhost,mysqltest_1,,; 161connection con10; 162set sql_log_off = 1; 163ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation 164set sql_log_bin = 0; 165ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation 166disconnect con10; 167connection default; 168delete from mysql.user where user like 'mysqltest\_1'; 169delete from mysql.db where user like 'mysqltest\_1'; 170drop database mysqltest_1; 171flush privileges; 172set sql_mode='maxdb'; 173drop table if exists t1, t2; 174create table t1(c1 int); 175create table t2(c1 int, c2 int); 176create user 'mysqltest_1'; 177create user 'mysqltest_1'; 178ERROR HY000: Operation CREATE USER failed for 'mysqltest_1'@'%' 179create user 'mysqltest_2' identified by 'Mysqltest-2'; 180create user 'mysqltest_3' identified by password 'fffffffffffffffffffffffffffffffffffffffff'; 181grant select on *.* to 'mysqltest_2'; 182grant insert on test.* to 'mysqltest_2'; 183grant update on test.t1 to 'mysqltest_2'; 184grant update (c2) on test.t2 to 'mysqltest_2'; 185select host,user,password,plugin,authentication_string from mysql.user where user like 'mysqltest_%'; 186Host User Password plugin authentication_string 187% mysqltest_1 mysql_native_password 188% mysqltest_2 *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1 mysql_native_password *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1 189% mysqltest_3 fffffffffffffffffffffffffffffffffffffffff mysql_native_password fffffffffffffffffffffffffffffffffffffffff 190select host,db,user from mysql.db where user like 'mysqltest_%'; 191host db user 192% test mysqltest_2 193select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%'; 194host db user table_name 195% test mysqltest_2 t1 196% test mysqltest_2 t2 197select host,db,user,table_name,column_name from mysql.columns_priv where user like 'mysqltest_%'; 198host db user table_name column_name 199% test mysqltest_2 t2 c2 200show grants for 'mysqltest_1'; 201Grants for mysqltest_1@% 202GRANT USAGE ON *.* TO "mysqltest_1"@"%" 203show grants for 'mysqltest_2'; 204Grants for mysqltest_2@% 205GRANT SELECT ON *.* TO "mysqltest_2"@"%" IDENTIFIED BY PASSWORD '*BD447CBA355AF58578D3AE33BA2E2CD388BA08D1' 206GRANT INSERT ON "test".* TO "mysqltest_2"@"%" 207GRANT UPDATE (c2) ON "test"."t2" TO "mysqltest_2"@"%" 208GRANT UPDATE ON "test"."t1" TO "mysqltest_2"@"%" 209drop user 'mysqltest_1'; 210select host,user,password,plugin,authentication_string from mysql.user where user like 'mysqltest_%'; 211Host User Password plugin authentication_string 212% mysqltest_2 *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1 mysql_native_password *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1 213% mysqltest_3 fffffffffffffffffffffffffffffffffffffffff mysql_native_password fffffffffffffffffffffffffffffffffffffffff 214select host,db,user from mysql.db where user like 'mysqltest_%'; 215host db user 216% test mysqltest_2 217select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%'; 218host db user table_name 219% test mysqltest_2 t1 220% test mysqltest_2 t2 221select host,db,user,table_name,column_name from mysql.columns_priv where user like 'mysqltest_%'; 222host db user table_name column_name 223% test mysqltest_2 t2 c2 224show grants for 'mysqltest_1'; 225ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%' 226rename user 'mysqltest_2' to 'mysqltest_1'; 227select host,user,password,plugin,authentication_string from mysql.user where user like 'mysqltest_%' ; 228Host User Password plugin authentication_string 229% mysqltest_1 *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1 mysql_native_password *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1 230% mysqltest_3 fffffffffffffffffffffffffffffffffffffffff mysql_native_password fffffffffffffffffffffffffffffffffffffffff 231select host,db,user from mysql.db where user like 'mysqltest_%' ; 232host db user 233% test mysqltest_1 234select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' ; 235host db user table_name 236% test mysqltest_1 t1 237% test mysqltest_1 t2 238select host,db,user,table_name,column_name from mysql.columns_priv where user like 'mysqltest_%' ; 239host db user table_name column_name 240% test mysqltest_1 t2 c2 241show grants for 'mysqltest_1'; 242Grants for mysqltest_1@% 243GRANT SELECT ON *.* TO "mysqltest_1"@"%" IDENTIFIED BY PASSWORD '*BD447CBA355AF58578D3AE33BA2E2CD388BA08D1' 244GRANT INSERT ON "test".* TO "mysqltest_1"@"%" 245GRANT UPDATE (c2) ON "test"."t2" TO "mysqltest_1"@"%" 246GRANT UPDATE ON "test"."t1" TO "mysqltest_1"@"%" 247drop user 'mysqltest_1', 'mysqltest_3'; 248drop user 'mysqltest_1'; 249ERROR HY000: Operation DROP USER failed for 'mysqltest_1'@'%' 250drop table t1, t2; 251insert into mysql.db set user='mysqltest_1', db='%', host='%'; 252flush privileges; 253show grants for 'mysqltest_1'; 254ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%' 255revoke all privileges, grant option from 'mysqltest_1'; 256ERROR HY000: Can't revoke all privileges for one or more of the requested users 257drop user 'mysqltest_1'; 258select host,db,user from mysql.db where user = 'mysqltest_1' ; 259host db user 260insert into mysql.tables_priv set host='%', db='test', user='mysqltest_1', table_name='t1'; 261flush privileges; 262show grants for 'mysqltest_1'; 263ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%' 264drop user 'mysqltest_1'; 265select host,db,user,table_name from mysql.tables_priv where user = 'mysqltest_1' ; 266host db user table_name 267insert into mysql.columns_priv set host='%', db='test', user='mysqltest_1', table_name='t1', column_name='c1'; 268flush privileges; 269show grants for 'mysqltest_1'; 270ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%' 271drop user 'mysqltest_1'; 272select host,db,user,table_name,column_name from mysql.columns_priv where user = 'mysqltest_1' ; 273host db user table_name column_name 274create user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; 275drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; 276create user 'mysqltest_1', 'mysqltest_2' identified by 'Mysqltest-2', 'mysqltest_3' identified by password 'fffffffffffffffffffffffffffffffffffffffff'; 277rename user 'mysqltest_1' to 'mysqltest_1a', 'mysqltest_2' TO 'mysqltest_2a', 'mysqltest_3' TO 'mysqltest_3a'; 278drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; 279ERROR HY000: Operation DROP USER failed for 'mysqltest_1'@'%','mysqltest_2'@'%','mysqltest_3'@'%' 280drop user 'mysqltest_1a', 'mysqltest_2a', 'mysqltest_3a'; 281create user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; 282create user 'mysqltest_1a', 'mysqltest_2', 'mysqltest_3a'; 283ERROR HY000: Operation CREATE USER failed for 'mysqltest_2'@'%' 284rename user 'mysqltest_1a' to 'mysqltest_1b', 'mysqltest_2a' TO 'mysqltest_2b', 'mysqltest_3a' TO 'mysqltest_3b'; 285ERROR HY000: Operation RENAME USER failed for 'mysqltest_2a'@'%' 286drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3'; 287drop user 'mysqltest_1b', 'mysqltest_2b', 'mysqltest_3b'; 288ERROR HY000: Operation DROP USER failed for 'mysqltest_2b'@'%' 289create user 'mysqltest_2' identified by 'Mysqltest-2'; 290drop user 'mysqltest_2' identified by 'Mysqltest-2'; 291ERROR 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 'identified by 'Mysqltest-2'' at line 1 292drop user 'mysqltest_2'; 293create user '%@b'@'b'; 294show grants for '%@b'@'b'; 295Grants for %@b@b 296GRANT USAGE ON *.* TO "%@b"@"b" 297grant select on mysql.* to '%@b'@'b'; 298show grants for '%@b'@'b'; 299Grants for %@b@b 300GRANT USAGE ON *.* TO "%@b"@"b" 301GRANT SELECT ON "mysql".* TO "%@b"@"b" 302rename user '%@b'@'b' to '%@a'@'a'; 303show grants for '%@b'@'b'; 304ERROR 42000: There is no such grant defined for user '%@b' on host 'b' 305show grants for '%@a'@'a'; 306Grants for %@a@a 307GRANT USAGE ON *.* TO "%@a"@"a" 308GRANT SELECT ON "mysql".* TO "%@a"@"a" 309drop user '%@a'@'a'; 310create user mysqltest_2@localhost; 311grant create user on *.* to mysqltest_2@localhost; 312connect user3,localhost,mysqltest_2,,; 313connection user3; 314select host,user,password,plugin,authentication_string from mysql.user where user like 'mysqltest_%' ; 315ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table 'user' 316create user mysqltest_A@'%'; 317rename user mysqltest_A@'%' to mysqltest_B@'%'; 318drop user mysqltest_B@'%'; 319disconnect user3; 320connection default; 321drop user mysqltest_2@localhost; 322create user mysqltest_3@localhost; 323grant INSERT,DELETE,UPDATE on mysql.* to mysqltest_3@localhost; 324connect user4,localhost,mysqltest_3,,; 325connection user4; 326show grants; 327Grants for mysqltest_3@localhost 328GRANT USAGE ON *.* TO `mysqltest_3`@`localhost` 329GRANT INSERT, UPDATE, DELETE ON `mysql`.* TO `mysqltest_3`@`localhost` 330select host,user,password,plugin,authentication_string from mysql.user where user like 'mysqltest_%' ; 331ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 'user' 332insert into mysql.global_priv set host='%', user='mysqltest_B'; 333create user mysqltest_A@'%'; 334rename user mysqltest_B@'%' to mysqltest_C@'%'; 335drop user mysqltest_C@'%'; 336drop user mysqltest_A@'%'; 337disconnect user4; 338connection default; 339drop user mysqltest_3@localhost; 340set @@sql_mode=''; 341create database mysqltest_1; 342create table mysqltest_1.t1 (i int); 343insert into mysqltest_1.t1 values (1),(2),(3); 344GRANT ALL ON mysqltest_1.t1 TO mysqltest_1@'127.0.0.0/255.0.0.0'; 345connect n1,127.0.0.1,mysqltest_1,,mysqltest_1,$MASTER_MYPORT,$MASTER_MYSOCK; 346connection n1; 347show grants for current_user(); 348Grants for mysqltest_1@127.0.0.0/255.0.0.0 349GRANT USAGE ON *.* TO `mysqltest_1`@`127.0.0.0/255.0.0.0` 350GRANT ALL PRIVILEGES ON `mysqltest_1`.`t1` TO `mysqltest_1`@`127.0.0.0/255.0.0.0` 351select * from t1; 352i 3531 3542 3553 356disconnect n1; 357connection default; 358REVOKE ALL ON mysqltest_1.t1 FROM mysqltest_1@'127.0.0.0/255.0.0.0'; 359delete from mysql.user where user like 'mysqltest\_1'; 360flush privileges; 361drop table mysqltest_1.t1; 362grant all on mysqltest_1.* to mysqltest_1@'127.0.0.1'; 363connect b12302,127.0.0.1,mysqltest_1,,mysqltest_1,$MASTER_MYPORT,; 364connection b12302; 365select current_user(); 366current_user() 367mysqltest_1@127.0.0.1 368set password = password('changed'); 369disconnect b12302; 370connection default; 371select host, length(authentication_string) from mysql.user where user like 'mysqltest\_1'; 372Host length(authentication_string) 373127.0.0.1 41 374revoke all on mysqltest_1.* from mysqltest_1@'127.0.0.1'; 375delete from mysql.user where user like 'mysqltest\_1'; 376flush privileges; 377grant all on mysqltest_1.* to mysqltest_1@'127.0.0.0/255.0.0.0'; 378connect b12302_2,127.0.0.1,mysqltest_1,,mysqltest_1,$MASTER_MYPORT,; 379connection b12302_2; 380select current_user(); 381current_user() 382mysqltest_1@127.0.0.0/255.0.0.0 383set password = password('changed'); 384disconnect b12302_2; 385connection default; 386select host, length(authentication_string) from mysql.user where user like 'mysqltest\_1'; 387Host length(authentication_string) 388127.0.0.0/255.0.0.0 41 389revoke all on mysqltest_1.* from mysqltest_1@'127.0.0.0/255.0.0.0'; 390delete from mysql.user where user like 'mysqltest\_1'; 391flush privileges; 392drop database mysqltest_1; 393connect n5,localhost,test,,test,$MASTER_MYPORT,$MASTER_MYSOCK; 394connection n5; 395set password = password("changed"); 396ERROR 42000: You are using MariaDB as an anonymous user and anonymous users are not allowed to modify user settings 397disconnect n5; 398connection default; 399connect con2root,localhost,root,,; 400connect con3root,localhost,root,,; 401connection default; 402lock table mysql.user write; 403connection con2root; 404flush privileges; 405connection con3root; 406grant all on *.* to 'mysqltest_1'@'localhost'; 407connection default; 408unlock tables; 409connection con2root; 410connection con3root; 411connection default; 412lock table mysql.user write; 413connection con2root; 414set password for 'mysqltest_1'@'localhost' = password(''); 415connection con3root; 416revoke all on *.* from 'mysqltest_1'@'localhost'; 417connection default; 418unlock tables; 419connection con2root; 420connection con3root; 421connection default; 422drop user 'mysqltest_1'@'localhost'; 423disconnect con2root; 424disconnect con3root; 425create database TESTDB; 426create table t2(a int); 427create temporary table t1 as select * from mysql.global_priv; 428delete from mysql.global_priv where host='localhost'; 429INSERT INTO mysql.global_priv (host, user, priv) VALUES 430('%','mysqltest_1',json_object('authentication_string', password('password'))); 431INSERT INTO mysql.db (host, db, user, select_priv) VALUES 432('%','TESTDB','mysqltest_1','Y'); 433FLUSH PRIVILEGES; 434connect con1,localhost,mysqltest_1,password,TESTDB; 435create database TEStdb; 436Got one of the listed errors 437connection default; 438disconnect con1; 439delete from mysql.global_priv; 440delete from mysql.db where host='%' and user='mysqltest_1' and db='TESTDB'; 441insert into mysql.global_priv select * from t1; 442drop table t1, t2; 443drop database TESTDB; 444flush privileges; 445SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; 446SET GLOBAL log_bin_trust_function_creators = 1; 447GRANT ALL PRIVILEGES ON test.* TO `a@`@localhost; 448GRANT EXECUTE ON * TO `a@`@localhost; 449connect bug13310,localhost,'a@',,test; 450connection bug13310; 451CREATE TABLE t2 (s1 INT); 452INSERT INTO t2 VALUES (1); 453DROP FUNCTION IF EXISTS f2; 454CREATE FUNCTION f2 () RETURNS INT 455BEGIN DECLARE v INT; SELECT s1 FROM t2 INTO v; RETURN v; END// 456Warnings: 457Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 458SELECT f2(); 459f2() 4601 461DROP FUNCTION f2; 462DROP TABLE t2; 463disconnect bug13310; 464connection default; 465REVOKE ALL PRIVILEGES, GRANT OPTION FROM `a@`@localhost; 466DROP USER `a@`@localhost; 467SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators; 468drop database if exists mysqltest_1; 469drop database if exists mysqltest_2; 470drop user mysqltest_u1@localhost; 471create database mysqltest_1; 472create database mysqltest_2; 473grant all on mysqltest_1.* to mysqltest_u1@localhost; 474use mysqltest_2; 475create table t1 (i int); 476connect user1,localhost,mysqltest_u1,,mysqltest_1; 477connection user1; 478show create table mysqltest_2.t1; 479ERROR 42000: SHOW command denied to user 'mysqltest_u1'@'localhost' for table 't1' 480create table t1 like mysqltest_2.t1; 481ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' 482connection default; 483grant select on mysqltest_2.t1 to mysqltest_u1@localhost; 484connection user1; 485show create table mysqltest_2.t1; 486Table Create Table 487t1 CREATE TABLE `t1` ( 488 `i` int(11) DEFAULT NULL 489) ENGINE=MyISAM DEFAULT CHARSET=latin1 490create table t1 like mysqltest_2.t1; 491connection default; 492disconnect user1; 493use test; 494drop database mysqltest_1; 495drop database mysqltest_2; 496drop user mysqltest_u1@localhost; 497grant all on `mysqltest\_%`.* to mysqltest_1@localhost with grant option; 498grant usage on *.* to mysqltest_2@localhost; 499connect con18600_1,localhost,mysqltest_1,,; 500create database mysqltest_1; 501use mysqltest_1; 502create table t1 (f1 int); 503grant create on `mysqltest\_1`.* to mysqltest_2@localhost; 504grant select on mysqltest_1.t1 to mysqltest_2@localhost; 505connect con3,localhost,mysqltest_2,,; 506connection con3; 507create database mysqltest_3; 508ERROR 42000: Access denied for user 'mysqltest_2'@'localhost' to database 'mysqltest_3' 509use mysqltest_1; 510create table t2(f1 int); 511select * from t1; 512f1 513connection default; 514drop database mysqltest_1; 515connection default; 516disconnect con3; 517disconnect con18600_1; 518revoke all privileges, grant option from mysqltest_1@localhost; 519revoke all privileges, grant option from mysqltest_2@localhost; 520drop user mysqltest_1@localhost; 521drop user mysqltest_2@localhost; 522CREATE DATABASE db1; 523USE db1; 524CREATE TABLE t1 (a INT, b INT); 525INSERT INTO t1 VALUES (1,1),(2,2); 526CREATE TABLE t2 (b INT, c INT); 527INSERT INTO t2 VALUES (1,100),(2,200); 528GRANT SELECT ON t1 TO mysqltest1@localhost; 529GRANT SELECT (b) ON t2 TO mysqltest1@localhost; 530connect conn1,localhost,mysqltest1,,; 531connection conn1; 532USE db1; 533SELECT c FROM t2; 534ERROR 42000: SELECT command denied to user 'mysqltest1'@'localhost' for column 'c' in table 't2' 535SELECT * FROM t2; 536ERROR 42000: SELECT command denied to user 'mysqltest1'@'localhost' for table 't2' 537SELECT * FROM t1 JOIN t2 USING (b); 538ERROR 42000: SELECT command denied to user 'mysqltest1'@'localhost' for column 'c' in table 't2' 539connection default; 540disconnect conn1; 541USE test; 542DROP TABLE db1.t1, db1.t2; 543DROP USER mysqltest1@localhost; 544DROP DATABASE db1; 545End of 5.0 tests 546USE mysql; 547SELECT LEFT(CURRENT_USER(),INSTR(CURRENT_USER(),'@')-1) INTO @u; 548SELECT MID(CURRENT_USER(),INSTR(CURRENT_USER(),'@')+1) INTO @h; 549SELECT user,host,password,plugin,authentication_string,insert_priv FROM user WHERE user=@u AND host=@h; 550User Host Password plugin authentication_string Insert_priv 551root localhost Y 552GRANT INSERT ON *.* TO CURRENT_USER(); 553SELECT user,host,password,plugin,authentication_string,insert_priv FROM user WHERE user=@u AND host=@h; 554User Host Password plugin authentication_string Insert_priv 555root localhost mysql_native_password Y 556GRANT INSERT ON *.* TO CURRENT_USER() IDENTIFIED BY 'keksdose'; 557SELECT user,host,password,plugin,authentication_string,insert_priv FROM user WHERE user=@u AND host=@h; 558User Host Password plugin authentication_string Insert_priv 559root localhost *0BB7188CF0DE9B403BA66E9DD810D82652D002EB mysql_native_password *0BB7188CF0DE9B403BA66E9DD810D82652D002EB Y 560UPDATE global_priv SET priv=@root_priv where user='root' and host='localhost'; 561FLUSH PRIVILEGES; 562USE test; 563End of 5.1 tests 564 565# -- 566# -- Bug#11746602: 27480 - Extend CREATE TEMPORARY TABLES privilege to 567# -- allow temp table operations 568# -- 569# -- Bug#12771903: User with create temporary tables priv only has full 570# -- access to a regular table 571# -- 572 573############################################################################ 574# Setup environment. 575########################################################################### 576DROP DATABASE IF EXISTS mysqltest_db1; 577DROP DATABASE IF EXISTS mysqltest_db2; 578CREATE DATABASE mysqltest_db1; 579CREATE DATABASE mysqltest_db2; 580# mysqltest_u1@localhost has CREATE_TMP_ACL, FILE_ACL and EXECUTE_ACL only 581# (EXECUTE_ACL is needed to call p0, and FILE_ACL is needed for SELECT 582# OUTFILE/LOAD DATA INFILE). 583GRANT FILE ON *.* TO mysqltest_u1@localhost; 584GRANT CREATE TEMPORARY TABLES, EXECUTE ON mysqltest_db1.* TO mysqltest_u1@localhost; 585# mysqltest_u2@localhost has all privileges but CREATE_TMP_ACL. 586GRANT ALL PRIVILEGES ON mysqltest_db1.* TO mysqltest_u2@localhost; 587REVOKE CREATE TEMPORARY TABLES ON mysqltest_db1.* FROM mysqltest_u2@localhost; 588# mysqltest_u3@localhost has CREATE_TMP_ACL & EXECUTE_ACL. 589# This user is required to check SUID-stored-routines. 590GRANT CREATE TEMPORARY TABLES ON mysqltest_db1.* TO mysqltest_u3@localhost; 591GRANT EXECUTE ON mysqltest_db1.* TO mysqltest_u3@localhost; 592# mysqltest_u4@localhost has only EXECUTE_ACL. 593# We need this user to check that once created temporary tables 594# are accessible by anyone. 595GRANT EXECUTE ON mysqltest_db1.* TO mysqltest_u4@localhost; 596# mysqltest_u5@localhost has CREATE_TMP_ACL and SELECT_ACL, UPDATE_ACL, 597# DELETE_ACL on mysqltest_db1; and only CREATE_TMP_ACL on mysqltest_db2. 598# By means of this user we check privileges required for merge tables. 599GRANT CREATE TEMPORARY TABLES ON mysqltest_db1.* TO mysqltest_u5@localhost; 600GRANT CREATE TEMPORARY TABLES ON mysqltest_db2.* TO mysqltest_u5@localhost; 601GRANT SELECT, UPDATE, DELETE ON mysqltest_db1.* TO mysqltest_u5@localhost; 602# Create stored routine to test how privilege checking is done for its 603# arguments. 604CREATE PROCEDURE mysqltest_db1.p0(i INT) SELECT i; 605# Create SUID-stored-routines. 606CREATE DEFINER = mysqltest_u3@localhost PROCEDURE mysqltest_db1.p1() 607CREATE TEMPORARY TABLE t4(x INT); 608CREATE DEFINER = mysqltest_u3@localhost PROCEDURE mysqltest_db1.p2() 609INSERT INTO t4 VALUES (1), (2), (3); 610CREATE DEFINER = mysqltest_u3@localhost PROCEDURE mysqltest_db1.p3() 611SELECT * FROM t4 ORDER BY x; 612# We need separate key cache to test CACHE INDEX and LOAD INDEX. 613SET GLOBAL keycache1.key_buffer_size = 128 * 1024; 614CREATE TABLE mysqltest_db2.t2_1(a INT); 615########################################################################### 616# Check that CREATE_TMP_ACL is enough to issue almost any supported 617# SQL-statement against temporary tables (loosely follow order in 618# sql_command enum). 619########################################################################### 620connect con1,localhost,mysqltest_u1,,mysqltest_db1; 621# 622# Variants of CREATE TABLE. 623# 624CREATE TEMPORARY TABLE t1(a INT); 625CREATE TEMPORARY TABLE t2 LIKE t1; 626CREATE TEMPORARY TABLE t3(a INT, b INT, PRIMARY KEY (a)); 627CREATE TEMPORARY TABLE t4 SELECT * FROM t1; 628# Check that we do *not* allow creation of MERGE table with underlying 629# temporary table without additional privileges. 630CREATE TEMPORARY TABLE t5(a INT) ENGINE = MyISAM; 631CREATE TEMPORARY TABLE t6(a INT) ENGINE = MERGE UNION = (t5); 632ERROR 42000: SELECT, UPDATE, DELETE command denied to user 'mysqltest_u1'@'localhost' for table 't5' 633# Check that we allow creation of MERGE table with no underlying table 634# without additional privileges. 635CREATE TEMPORARY TABLE t6(a INT) ENGINE = MERGE UNION = (); 636# 637# SELECT. 638# 639INSERT INTO t1 VALUES (1), (2), (3); 640SELECT * FROM t1 ORDER BY a; 641a 6421 6432 6443 645# 646# CREATE/DROP INDEX. 647# 648CREATE INDEX idx1 ON t3(b); 649DROP INDEX idx1 ON t3; 650# 651# ALTER TABLE. 652# 653ALTER TABLE t4 ADD COLUMN b INT; 654# Check that we allow altering of MERGE table with no underlying 655# without additional privileges. 656ALTER TABLE t6 UNION = (); 657# Check that we do *not* allow altering of MERGE table with underlying 658# temporary table without additional privileges. 659ALTER TABLE t6 UNION = (t5); 660ERROR 42000: SELECT, UPDATE, DELETE command denied to user 'mysqltest_u1'@'localhost' for table 't5' 661# 662# Simple INSERT and INSERT ... SELECT. 663# 664INSERT INTO t1 VALUES (4); 665INSERT INTO t2 SELECT a FROM t1; 666SELECT * FROM t1 ORDER BY a; 667a 6681 6692 6703 6714 672SELECT * FROM t2 ORDER BY a; 673a 6741 6752 6763 6774 678# 679# UPDATE and multi-UPDATE. 680# 681UPDATE t1 SET a = a * 10; 682UPDATE t1 SET a = 100 WHERE a = 10; 683UPDATE t1, t2 SET t1.a = 200 WHERE t1.a = t2.a * 10 AND t1.a = 20; 684SELECT * FROM t1 ORDER BY a; 685a 68630 68740 688100 689200 690# 691# DELETE and multi-DELETE. 692# 693DELETE FROM t1 WHERE a = 100; 694DELETE t1 FROM t1, t2 WHERE t1.a = t2.a * 100 AND t1.a = 200; 695SELECT * FROM t1 ORDER BY a; 696a 69730 69840 699# 700# TRUNCATE TABLE. 701# 702TRUNCATE TABLE t1; 703SELECT * FROM t1 ORDER BY a; 704a 705# 706# SHOW COLUMNS/DESCRIBE and SHOW KEYS. 707# 708SHOW COLUMNS FROM t1; 709Field Type Null Key Default Extra 710SHOW KEYS FROM t3; 711Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment 712t3 0 PRIMARY 1 a A 0 NULL NULL BTREE 713# 714# SHOW CREATE TABLE. 715# 716SHOW CREATE TABLE t1; 717Table Create Table 718t1 CREATE TEMPORARY TABLE `t1` ( 719 `a` int(11) DEFAULT NULL 720) ENGINE=MyISAM DEFAULT CHARSET=latin1 721# 722# LOAD DATA INFILE (also SELECT INTO OUTFILE). 723# 724INSERT INTO t1 VALUES (1), (2), (3); 725SELECT a INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug27480.txt' FROM t1 ; 726LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug27480.txt' INTO TABLE t1; 727SELECT * FROM t1 ORDER BY a; 728a 7291 7301 7312 7322 7333 7343 735# 736# SET. 737# 738SET @a := (SELECT COUNT(*) FROM t1); 739SELECT @a; 740@a 7416 742# 743# LOCK TABLES. 744# 745LOCK TABLES t1 READ; 746UNLOCK TABLES; 747# 748# CHECK/REPAIR/ANALYZE/OPTIMIZE and CHECKSUM. 749# 750ANALYZE TABLE t1; 751Table Op Msg_type Msg_text 752mysqltest_db1.t1 analyze status OK 753CHECK TABLE t1; 754Table Op Msg_type Msg_text 755mysqltest_db1.t1 check status OK 756OPTIMIZE TABLE t1; 757Table Op Msg_type Msg_text 758mysqltest_db1.t1 optimize status Table is already up to date 759REPAIR TABLE t1; 760Table Op Msg_type Msg_text 761mysqltest_db1.t1 repair status OK 762# 763# REPLACE and REPLACE ... SELECT. 764# 765INSERT INTO t3 VALUES (1, 111), (2, 222), (3, 333); 766REPLACE INTO t3 VALUES (1, 1111), (4, 444), (0, 001); 767REPLACE INTO t2 SELECT b FROM t3; 768SELECT * FROM t2 ORDER BY a; 769a 7701 7711 7722 7733 7744 775222 776333 777444 7781111 779SELECT * FROM t3 ORDER BY a; 780a b 7810 1 7821 1111 7832 222 7843 333 7854 444 786# 787# CACHE and LOAD INDEX. 788# 789CACHE INDEX t3 IN keycache1; 790Table Op Msg_type Msg_text 791mysqltest_db1.t3 assign_to_keycache status OK 792LOAD INDEX INTO CACHE t3; 793Table Op Msg_type Msg_text 794mysqltest_db1.t3 preload_keys status OK 795# 796# RENAME should work for temporary tables 797# 798RENAME TABLE t3 TO t3_1; 799# 800# HANDLER OPEN/READ/CLOSE. 801# 802HANDLER t1 OPEN; 803HANDLER t1 READ NEXT; 804a 8051 806HANDLER t1 CLOSE; 807# 808# DO. 809# 810DO (SELECT COUNT(*) FROM t1); 811# 812# CHECKSUM TABLE. 813# 814DELETE FROM t1; 815CHECKSUM TABLE t1; 816Table Checksum 817mysqltest_db1.t1 0 818# 819# CALL. 820# 821CALL p0((SELECT COUNT(*) FROM t1)); 822i 8230 824# 825# PREPARE, EXECUTE and DEALLOCATE. 826# 827PREPARE stmt1 FROM 'SELECT * FROM t1 ORDER BY a'; 828PREPARE stmt2 FROM 'SELECT * FROM t2 ORDER BY a'; 829EXECUTE stmt1; 830a 831EXECUTE stmt2; 832a 8331 8341 8352 8363 8374 838222 839333 840444 8411111 842DEALLOCATE PREPARE stmt1; 843DEALLOCATE PREPARE stmt2; 844# 845# DROP TABLE and DROP TEMPORARY TABLE. 846# 847DROP TABLE t1; 848CREATE TEMPORARY TABLE t1(a INT); 849DROP TEMPORARY TABLE t1; 850########################################################################### 851# - Check that even having all privileges but CREATE_TMP_ACL is not enough 852# to create temporary tables. 853# - Check that creation/working with temporary tables is possible via 854# SUID-stored-routines. 855# - Check that even outside of SUID context we can access temporary 856# table once it is created. 857########################################################################### 858connect con2,localhost,mysqltest_u2,,mysqltest_db1; 859CREATE TEMPORARY TABLE t2(a INT); 860ERROR 42000: Access denied for user 'mysqltest_u2'@'localhost' to database 'mysqltest_db1' 861CALL p1(); 862CALL p2(); 863CALL p3(); 864x 8651 8662 8673 868# Check that once table is created it can be accessed even 869# outside of such a SUID context. 870INSERT INTO t4 VALUES (4); 871UPDATE t4 SET x = 10 WHERE x = 1; 872DELETE FROM t4 WHERE x < 3; 873SELECT * FROM t4 ORDER BY x; 874x 8753 8764 87710 878DROP TEMPORARY TABLE t4; 879########################################################################### 880# - Check that once table is created it can be accessed from within any 881# context, even by user without any privileges on tables. 882########################################################################### 883connect con3,localhost,mysqltest_u4,,mysqltest_db1; 884CALL p1(); 885INSERT INTO t4 VALUES (4); 886UPDATE t4 SET x = 10 WHERE x = 1; 887DELETE FROM t4 WHERE x < 3; 888SELECT * FROM t4 ORDER BY x; 889x 8904 891DROP TEMPORARY TABLE t4; 892########################################################################### 893# Check special case for MERGE-tables: 894# - CREATE_TMP_ACL is required to create a temporary merge table; 895# - SELECT_ACL, UPDATE_ACL and DELETE_ACL are required to include 896# a temporary table into the underlying-table-list. 897########################################################################### 898connect con4,localhost,mysqltest_u5,,mysqltest_db1; 899CREATE TEMPORARY TABLE t7(a INT); 900CREATE TEMPORARY TABLE t8(a INT); 901CREATE TEMPORARY TABLE t9(a INT); 902CREATE TEMPORARY TABLE t10(a INT) ENGINE = MERGE UNION = (t7, t8); 903ALTER TABLE t10 UNION = (t9); 904ALTER TABLE t10 UNION = (mysqltest_db2.t2_1); 905ERROR 42000: SELECT, UPDATE, DELETE command denied to user 'mysqltest_u5'@'localhost' for table 't2_1' 906CREATE TEMPORARY TABLE mysqltest_db2.t2_2(a INT) ENGINE = MERGE UNION = (t7, t8); 907ALTER TABLE mysqltest_db2.t2_2 UNION = (t9); 908ALTER TABLE mysqltest_db2.t2_2 UNION = (); 909DROP TEMPORARY TABLE mysqltest_db2.t2_2; 910DROP TEMPORARY TABLE t10; 911DROP TEMPORARY TABLE t7; 912DROP TEMPORARY TABLE t8; 913DROP TEMPORARY TABLE t9; 914########################################################################### 915# That's all. Cleanup. 916########################################################################### 917connection default; 918# All remaining temporary tables are automatically dropped. 919disconnect con1; 920disconnect con2; 921disconnect con3; 922disconnect con4; 923SET GLOBAL keycache1.key_buffer_size = 0; 924DROP DATABASE mysqltest_db1; 925DROP DATABASE mysqltest_db2; 926DROP USER mysqltest_u1@localhost; 927DROP USER mysqltest_u2@localhost; 928DROP USER mysqltest_u3@localhost; 929DROP USER mysqltest_u4@localhost; 930DROP USER mysqltest_u5@localhost; 931set GLOBAL sql_mode=default; 932