1set @@global.character_set_server=@@session.character_set_server; 2connect con1root,localhost,root,,; 3connection con1root; 4use test; 5create user user1@localhost; 6grant usage on *.* to user1@localhost; 7flush privileges; 8drop table if exists t1; 9drop database if exists db1_secret; 10create database db1_secret; 11create procedure db1_secret.dummy() begin end; 12drop procedure db1_secret.dummy; 13use db1_secret; 14create table t1 ( u varchar(64), i int ); 15insert into t1 values('test', 0); 16create procedure stamp(i int) 17insert into db1_secret.t1 values (user(), i); 18show procedure status like 'stamp'; 19Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 20db1_secret stamp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci 21create function db() returns varchar(64) 22begin 23declare v varchar(64); 24select u into v from t1 limit 1; 25return v; 26end| 27show function status like 'db'; 28Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 29db1_secret db FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci 30call stamp(1); 31select * from t1; 32u i 33test 0 34root@localhost 1 35select db(); 36db() 37test 38create user user1@'%'; 39grant execute on procedure db1_secret.stamp to user1@'%'; 40grant execute on function db1_secret.db to user1@'%'; 41set sql_mode=''; 42grant execute on procedure db1_secret.stamp to ''@'%'; 43grant execute on function db1_secret.db to ''@'%'; 44set sql_mode=default; 45connect con2user1,localhost,user1,,; 46connect con3anon,localhost,anon,,; 47connection con2user1; 48call db1_secret.stamp(2); 49select db1_secret.db(); 50db1_secret.db() 51test 52select * from db1_secret.t1; 53ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' 54create procedure db1_secret.dummy() begin end; 55ERROR 42000: Access denied for user 'user1'@'localhost' to database 'db1_secret' 56drop procedure db1_secret.dummy; 57ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db1_secret.dummy' 58drop procedure db1_secret.stamp; 59ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db1_secret.stamp' 60drop function db1_secret.db; 61ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db1_secret.db' 62connection con3anon; 63call db1_secret.stamp(3); 64select db1_secret.db(); 65db1_secret.db() 66test 67select * from db1_secret.t1; 68ERROR 42000: SELECT command denied to user ''@'localhost' for table 't1' 69create procedure db1_secret.dummy() begin end; 70ERROR 42000: Access denied for user ''@'%' to database 'db1_secret' 71drop procedure db1_secret.dummy; 72ERROR 42000: alter routine command denied to user ''@'%' for routine 'db1_secret.dummy' 73drop procedure db1_secret.stamp; 74ERROR 42000: alter routine command denied to user ''@'%' for routine 'db1_secret.stamp' 75drop function db1_secret.db; 76ERROR 42000: alter routine command denied to user ''@'%' for routine 'db1_secret.db' 77connection con1root; 78select * from t1; 79u i 80test 0 81root@localhost 1 82user1@localhost 2 83anon@localhost 3 84alter procedure stamp sql security invoker; 85show procedure status like 'stamp'; 86Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 87db1_secret stamp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 INVOKER latin1 latin1_swedish_ci latin1_swedish_ci 88alter function db sql security invoker; 89show function status like 'db'; 90Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 91db1_secret db FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 INVOKER latin1 latin1_swedish_ci latin1_swedish_ci 92call stamp(4); 93select * from t1; 94u i 95test 0 96root@localhost 1 97user1@localhost 2 98anon@localhost 3 99root@localhost 4 100select db(); 101db() 102test 103connection con2user1; 104call db1_secret.stamp(5); 105ERROR 42000: INSERT command denied to user 'user1'@'localhost' for table 't1' 106select db1_secret.db(); 107ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' 108connection con3anon; 109call db1_secret.stamp(6); 110ERROR 42000: INSERT command denied to user ''@'localhost' for table 't1' 111select db1_secret.db(); 112ERROR 42000: SELECT command denied to user ''@'localhost' for table 't1' 113connection con1root; 114drop database if exists db2; 115create database db2; 116use db2; 117create table t2 (s1 int); 118insert into t2 values (0); 119grant usage on db2.* to user1@localhost; 120grant select on db2.* to user1@localhost; 121create user user2@localhost; 122grant usage on db2.* to user2@localhost; 123grant select,insert,update,delete,create routine on db2.* to user2@localhost; 124grant create routine on db2.* to user1@localhost; 125flush privileges; 126connection con2user1; 127use db2; 128create procedure p () insert into t2 values (1); 129call p(); 130ERROR 42000: INSERT command denied to user 'user1'@'localhost' for table 't2' 131connect con4user2,localhost,user2,,; 132connection con4user2; 133use db2; 134call p(); 135ERROR 42000: execute command denied to user 'user2'@'localhost' for routine 'db2.p' 136select * from t2; 137s1 1380 139create procedure q () insert into t2 values (2); 140call q(); 141select * from t2; 142s1 1430 1442 145connection con1root; 146grant usage on procedure db2.q to user2@localhost with grant option; 147connection con4user2; 148grant execute on procedure db2.q to user1@localhost; 149connection con2user1; 150use db2; 151call q(); 152select * from t2; 153s1 1540 1552 1562 157alter procedure p modifies sql data; 158drop procedure p; 159alter procedure q modifies sql data; 160ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db2.q' 161drop procedure q; 162ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db2.q' 163connection con1root; 164use db2; 165alter procedure q modifies sql data; 166drop procedure q; 167disconnect con2user1; 168disconnect con3anon; 169disconnect con4user2; 170use test; 171select type,db,name from mysql.proc where db like 'db%'; 172type db name 173FUNCTION db1_secret db 174PROCEDURE db1_secret stamp 175drop database db1_secret; 176drop database db2; 177select type,db,name from mysql.proc where db like 'db%'; 178type db name 179delete from mysql.user where user='user1' or user='user2'; 180delete from mysql.user where user='' and host='%'; 181delete from mysql.procs_priv where user='user1' or user='user2'; 182delete from mysql.procs_priv where user='' and host='%'; 183delete from mysql.db where user='user2'; 184flush privileges; 185create user usera@localhost; 186grant usage on *.* to usera@localhost; 187create user userb@localhost; 188grant usage on *.* to userb@localhost; 189create user userc@localhost; 190grant usage on *.* to userc@localhost; 191create database sptest; 192create table t1 ( u varchar(64), i int ); 193create procedure sptest.p1(i int) insert into test.t1 values (user(), i); 194grant insert on t1 to usera@localhost; 195grant execute on procedure sptest.p1 to usera@localhost; 196show grants for usera@localhost; 197Grants for usera@localhost 198GRANT USAGE ON *.* TO `usera`@`localhost` 199GRANT INSERT ON `test`.`t1` TO `usera`@`localhost` 200GRANT EXECUTE ON PROCEDURE `sptest`.`p1` TO `usera`@`localhost` 201grant execute on procedure sptest.p1 to userc@localhost with grant option; 202show grants for userc@localhost; 203Grants for userc@localhost 204GRANT USAGE ON *.* TO `userc`@`localhost` 205GRANT EXECUTE ON PROCEDURE `sptest`.`p1` TO `userc`@`localhost` WITH GRANT OPTION 206connect con2usera,localhost,usera,,; 207connect con3userb,localhost,userb,,; 208connect con4userc,localhost,userc,,; 209connection con2usera; 210call sptest.p1(1); 211grant execute on procedure sptest.p1 to userb@localhost; 212ERROR 42000: grant command denied to user 'usera'@'localhost' for routine 'sptest.p1' 213drop procedure sptest.p1; 214ERROR 42000: alter routine command denied to user 'usera'@'localhost' for routine 'sptest.p1' 215connection con3userb; 216call sptest.p1(2); 217ERROR 42000: execute command denied to user 'userb'@'localhost' for routine 'sptest.p1' 218grant execute on procedure sptest.p1 to userb@localhost; 219ERROR 42000: execute command denied to user 'userb'@'localhost' for routine 'sptest.p1' 220drop procedure sptest.p1; 221ERROR 42000: alter routine command denied to user 'userb'@'localhost' for routine 'sptest.p1' 222connection con4userc; 223call sptest.p1(3); 224grant execute on procedure sptest.p1 to userb@localhost; 225drop procedure sptest.p1; 226ERROR 42000: alter routine command denied to user 'userc'@'localhost' for routine 'sptest.p1' 227connection con3userb; 228call sptest.p1(4); 229grant execute on procedure sptest.p1 to userb@localhost; 230ERROR 42000: grant command denied to user 'userb'@'localhost' for routine 'sptest.p1' 231drop procedure sptest.p1; 232ERROR 42000: alter routine command denied to user 'userb'@'localhost' for routine 'sptest.p1' 233connection con1root; 234select * from t1; 235u i 236usera@localhost 1 237userc@localhost 3 238userb@localhost 4 239grant all privileges on procedure sptest.p1 to userc@localhost; 240show grants for userc@localhost; 241Grants for userc@localhost 242GRANT USAGE ON *.* TO `userc`@`localhost` 243GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `sptest`.`p1` TO `userc`@`localhost` WITH GRANT OPTION 244show grants for userb@localhost; 245Grants for userb@localhost 246GRANT USAGE ON *.* TO `userb`@`localhost` 247GRANT EXECUTE ON PROCEDURE `sptest`.`p1` TO `userb`@`localhost` 248connection con4userc; 249revoke all privileges on procedure sptest.p1 from userb@localhost; 250connection con1root; 251show grants for userb@localhost; 252Grants for userb@localhost 253GRANT USAGE ON *.* TO `userb`@`localhost` 254disconnect con4userc; 255disconnect con3userb; 256disconnect con2usera; 257use test; 258drop database sptest; 259delete from mysql.user where user='usera' or user='userb' or user='userc'; 260delete from mysql.procs_priv where user='usera' or user='userb' or user='userc'; 261delete from mysql.tables_priv where user='usera'; 262flush privileges; 263drop table t1; 264connect root,localhost,root,,test; 265connection root; 266drop function if exists bug_9503; 267drop user if exists user1@localhost; 268create database mysqltest// 269use mysqltest// 270create table t1 (s1 int)// 271create user user1@localhost// 272grant select on t1 to user1@localhost// 273create function bug_9503 () returns int sql security invoker begin declare v int; 274select min(s1) into v from t1; return v; end// 275connect user1,localhost,user1,,test; 276connection user1; 277use mysqltest; 278select bug_9503(); 279ERROR 42000: execute command denied to user 'user1'@'localhost' for routine 'mysqltest.bug_9503' 280connection root; 281grant execute on function bug_9503 to user1@localhost; 282connection user1; 283do 1; 284use test; 285disconnect user1; 286connection root; 287REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost; 288drop function bug_9503; 289use test; 290drop database mysqltest; 291connection default; 292disconnect root; 293connection con1root; 294use test; 295select current_user(); 296current_user() 297root@localhost 298select user(); 299user() 300root@localhost 301create procedure bug7291_0 () sql security invoker select current_user(), user(); 302create procedure bug7291_1 () sql security definer call bug7291_0(); 303create procedure bug7291_2 () sql security invoker call bug7291_0(); 304grant execute on procedure bug7291_0 to user1@localhost; 305grant execute on procedure bug7291_1 to user1@localhost; 306grant execute on procedure bug7291_2 to user1@localhost; 307connect user1,localhost,user1,,; 308connection user1; 309call bug7291_2(); 310current_user() user() 311user1@localhost user1@localhost 312call bug7291_1(); 313current_user() user() 314root@localhost user1@localhost 315connection con1root; 316drop procedure bug7291_1; 317drop procedure bug7291_2; 318drop procedure bug7291_0; 319disconnect user1; 320REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost; 321drop user user1@localhost; 322drop database if exists mysqltest_1; 323create database mysqltest_1; 324create procedure mysqltest_1.p1() 325begin 326select 1 from dual; 327end// 328create user mysqltest_1@localhost; 329grant usage on *.* to mysqltest_1@localhost; 330connect n1,localhost,mysqltest_1,,information_schema,$MASTER_MYPORT,$MASTER_MYSOCK; 331connection n1; 332call mysqltest_1.p1(); 333ERROR 42000: execute command denied to user 'mysqltest_1'@'localhost' for routine 'mysqltest_1.p1' 334disconnect n1; 335connect n2,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK; 336connection n2; 337call mysqltest_1.p1(); 338ERROR 42000: execute command denied to user 'mysqltest_1'@'localhost' for routine 'mysqltest_1.p1' 339disconnect n2; 340connection default; 341drop procedure mysqltest_1.p1; 342drop database mysqltest_1; 343revoke usage on *.* from mysqltest_1@localhost; 344drop user mysqltest_1@localhost; 345drop function if exists bug12812| 346create function bug12812() returns char(2) 347begin 348return 'ok'; 349end; 350create user user_bug12812@localhost IDENTIFIED BY 'ABC'| 351connect test_user_12812,localhost,user_bug12812,ABC,test; 352SELECT test.bug12812()| 353ERROR 42000: execute command denied to user 'user_bug12812'@'localhost' for routine 'test.bug12812' 354CREATE VIEW v1 AS SELECT test.bug12812()| 355ERROR 42000: execute command denied to user 'user_bug12812'@'localhost' for routine 'test.bug12812' 356connection default; 357disconnect test_user_12812; 358DROP USER user_bug12812@localhost| 359drop function bug12812| 360create database db_bug14834; 361create user user1_bug14834@localhost identified by ''; 362grant all on `db\_bug14834`.* to user1_bug14834@localhost; 363create user user2_bug14834@localhost identified by ''; 364grant all on `db\_bug14834`.* to user2_bug14834@localhost; 365create user user3_bug14834@localhost identified by ''; 366grant all on `db__ug14834`.* to user3_bug14834@localhost; 367connect user1_bug14834,localhost,user1_bug14834,,db_bug14834; 368create procedure p_bug14834() select user(), current_user(); 369call p_bug14834(); 370user() current_user() 371user1_bug14834@localhost user1_bug14834@localhost 372connect user2_bug14834,localhost,user2_bug14834,,db_bug14834; 373call p_bug14834(); 374user() current_user() 375user2_bug14834@localhost user1_bug14834@localhost 376connect user3_bug14834,localhost,user3_bug14834,,db_bug14834; 377call p_bug14834(); 378user() current_user() 379user3_bug14834@localhost user1_bug14834@localhost 380connection default; 381disconnect user1_bug14834; 382disconnect user2_bug14834; 383disconnect user3_bug14834; 384drop user user1_bug14834@localhost; 385drop user user2_bug14834@localhost; 386drop user user3_bug14834@localhost; 387drop database db_bug14834; 388create database db_bug14533; 389use db_bug14533; 390create table t1 (id int); 391create user user_bug14533@localhost identified by ''; 392create procedure bug14533_1() 393sql security definer 394desc db_bug14533.t1; 395create procedure bug14533_2() 396sql security definer 397select * from db_bug14533.t1; 398grant execute on procedure db_bug14533.bug14533_1 to user_bug14533@localhost; 399grant execute on procedure db_bug14533.bug14533_2 to user_bug14533@localhost; 400connect user_bug14533,localhost,user_bug14533,,test; 401call db_bug14533.bug14533_1(); 402Field Type Null Key Default Extra 403id int(11) YES NULL 404call db_bug14533.bug14533_2(); 405id 406desc db_bug14533.t1; 407ERROR 42000: SELECT command denied to user 'user_bug14533'@'localhost' for table 't1' 408select * from db_bug14533.t1; 409ERROR 42000: SELECT command denied to user 'user_bug14533'@'localhost' for table 't1' 410connection default; 411disconnect user_bug14533; 412drop user user_bug14533@localhost; 413drop database db_bug14533; 414connection con1root; 415DROP DATABASE IF EXISTS mysqltest; 416CREATE DATABASE mysqltest; 417CREATE USER mysqltest_1@localhost; 418GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost; 419CREATE USER mysqltest_2@localhost; 420GRANT SET USER ON *.* TO mysqltest_2@localhost; 421GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost; 422connect mysqltest_2_con,localhost,mysqltest_2,,mysqltest; 423connect mysqltest_1_con,localhost,mysqltest_1,,mysqltest; 424connection mysqltest_2_con; 425USE mysqltest; 426CREATE PROCEDURE wl2897_p1() SELECT 1; 427CREATE FUNCTION wl2897_f1() RETURNS INT RETURN 1; 428connection mysqltest_1_con; 429USE mysqltest; 430CREATE DEFINER=root@localhost PROCEDURE wl2897_p2() SELECT 2; 431ERROR 42000: Access denied; you need (at least one of) the SUPER, SET USER privilege(s) for this operation 432CREATE DEFINER=root@localhost FUNCTION wl2897_f2() RETURNS INT RETURN 2; 433ERROR 42000: Access denied; you need (at least one of) the SUPER, SET USER privilege(s) for this operation 434connection mysqltest_2_con; 435use mysqltest; 436CREATE DEFINER='a @ b @ c'@localhost PROCEDURE wl2897_p3() SELECT 3; 437Warnings: 438Note 1449 The user specified as a definer ('a @ b @ c'@'localhost') does not exist 439CREATE DEFINER='a @ b @ c'@localhost FUNCTION wl2897_f3() RETURNS INT RETURN 3; 440Warnings: 441Note 1449 The user specified as a definer ('a @ b @ c'@'localhost') does not exist 442connection con1root; 443USE mysqltest; 444SHOW CREATE PROCEDURE wl2897_p1; 445Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 446wl2897_p1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`mysqltest_2`@`localhost` PROCEDURE `wl2897_p1`() 447SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci 448SHOW CREATE PROCEDURE wl2897_p3; 449Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 450wl2897_p3 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`a @ b @ c`@`localhost` PROCEDURE `wl2897_p3`() 451SELECT 3 latin1 latin1_swedish_ci latin1_swedish_ci 452SHOW CREATE FUNCTION wl2897_f1; 453Function sql_mode Create Function character_set_client collation_connection Database Collation 454wl2897_f1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`mysqltest_2`@`localhost` FUNCTION `wl2897_f1`() RETURNS int(11) 455RETURN 1 latin1 latin1_swedish_ci latin1_swedish_ci 456SHOW CREATE FUNCTION wl2897_f3; 457Function sql_mode Create Function character_set_client collation_connection Database Collation 458wl2897_f3 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`a @ b @ c`@`localhost` FUNCTION `wl2897_f3`() RETURNS int(11) 459RETURN 3 latin1 latin1_swedish_ci latin1_swedish_ci 460DROP USER mysqltest_1@localhost; 461DROP USER mysqltest_2@localhost; 462DROP DATABASE mysqltest; 463disconnect mysqltest_1_con; 464disconnect mysqltest_2_con; 465connection con1root; 466DROP DATABASE IF EXISTS mysqltest; 467CREATE DATABASE mysqltest; 468CREATE USER mysqltest_1@localhost; 469GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost; 470CREATE USER mysqltest_2@localhost; 471GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost; 472connect mysqltest_1_con,localhost,mysqltest_1,,mysqltest; 473connect mysqltest_2_con,localhost,mysqltest_2,,mysqltest; 474connection mysqltest_1_con; 475USE mysqltest; 476CREATE PROCEDURE bug13198_p1() 477SELECT 1; 478CREATE FUNCTION bug13198_f1() RETURNS INT 479RETURN 1; 480CALL bug13198_p1(); 4811 4821 483SELECT bug13198_f1(); 484bug13198_f1() 4851 486connection mysqltest_2_con; 487USE mysqltest; 488CALL bug13198_p1(); 4891 4901 491SELECT bug13198_f1(); 492bug13198_f1() 4931 494connection con1root; 495disconnect mysqltest_1_con; 496DROP USER mysqltest_1@localhost; 497connection mysqltest_2_con; 498USE mysqltest; 499CALL bug13198_p1(); 500ERROR HY000: The user specified as a definer ('mysqltest_1'@'localhost') does not exist 501SELECT bug13198_f1(); 502ERROR HY000: The user specified as a definer ('mysqltest_1'@'localhost') does not exist 503connection con1root; 504disconnect mysqltest_2_con; 505DROP USER mysqltest_2@localhost; 506DROP DATABASE mysqltest; 507GRANT USAGE ON *.* TO user19857@localhost IDENTIFIED BY 'meow'; 508GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ROUTINE, ALTER ROUTINE ON test.* TO 509user19857@localhost; 510SELECT Host,User,Plugin,Authentication_string FROM mysql.user WHERE User='user19857'; 511Host User plugin authentication_string 512localhost user19857 mysql_native_password *82DC221D557298F6CE9961037DB1C90604792F5C 513connect mysqltest_2_con,localhost,user19857,meow,test; 514connection mysqltest_2_con; 515USE test; 516CREATE PROCEDURE sp19857() DETERMINISTIC 517BEGIN 518DECLARE a INT; 519SET a=1; 520SELECT a; 521END // 522SHOW CREATE PROCEDURE test.sp19857; 523Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 524sp19857 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`user19857`@`localhost` PROCEDURE `sp19857`() 525 DETERMINISTIC 526BEGIN 527DECLARE a INT; 528SET a=1; 529SELECT a; 530END latin1 latin1_swedish_ci latin1_swedish_ci 531disconnect mysqltest_2_con; 532connect mysqltest_2_con,localhost,user19857,meow,test; 533connection mysqltest_2_con; 534DROP PROCEDURE IF EXISTS test.sp19857; 535connection con1root; 536disconnect mysqltest_2_con; 537SELECT Host,User,Plugin,Authentication_string FROM mysql.user WHERE User='user19857'; 538Host User plugin authentication_string 539localhost user19857 mysql_native_password *82DC221D557298F6CE9961037DB1C90604792F5C 540DROP USER user19857@localhost; 541disconnect con1root; 542connection default; 543use test; 544DROP TABLE IF EXISTS t1; 545DROP VIEW IF EXISTS v1; 546DROP FUNCTION IF EXISTS f_suid; 547DROP PROCEDURE IF EXISTS p_suid; 548DROP FUNCTION IF EXISTS f_evil; 549DELETE FROM mysql.user WHERE user LIKE 'mysqltest\_%'; 550DELETE FROM mysql.db WHERE user LIKE 'mysqltest\_%'; 551DELETE FROM mysql.tables_priv WHERE user LIKE 'mysqltest\_%'; 552DELETE FROM mysql.columns_priv WHERE user LIKE 'mysqltest\_%'; 553FLUSH PRIVILEGES; 554CREATE TABLE t1 (i INT); 555CREATE FUNCTION f_suid(i INT) RETURNS INT SQL SECURITY DEFINER RETURN 0; 556CREATE PROCEDURE p_suid(IN i INT) SQL SECURITY DEFINER SET @c:= 0; 557CREATE USER mysqltest_u1@localhost; 558GRANT EXECUTE ON test.* TO mysqltest_u1@localhost; 559CREATE DEFINER=mysqltest_u1@localhost FUNCTION f_evil () RETURNS INT 560SQL SECURITY INVOKER 561BEGIN 562SET @a:= CURRENT_USER(); 563SET @b:= (SELECT COUNT(*) FROM t1); 564RETURN @b; 565END| 566CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT f_evil(); 567connect conn1, localhost, mysqltest_u1,,; 568SELECT COUNT(*) FROM t1; 569ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' 570SELECT f_evil(); 571ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' 572SELECT @a, @b; 573@a @b 574mysqltest_u1@localhost NULL 575SELECT f_suid(f_evil()); 576ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' 577SELECT @a, @b; 578@a @b 579mysqltest_u1@localhost NULL 580CALL p_suid(f_evil()); 581ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' 582SELECT @a, @b; 583@a @b 584mysqltest_u1@localhost NULL 585SELECT * FROM v1; 586ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'v1' 587SELECT @a, @b; 588@a @b 589mysqltest_u1@localhost NULL 590disconnect conn1; 591connection default; 592DROP VIEW v1; 593DROP FUNCTION f_evil; 594DROP USER mysqltest_u1@localhost; 595DROP PROCEDURE p_suid; 596DROP FUNCTION f_suid; 597DROP TABLE t1; 598# 599# Bug #48872 : Privileges for stored functions ignored if function name 600# is mixed case 601# 602CREATE DATABASE B48872; 603USE B48872; 604CREATE TABLE `TestTab` (id INT); 605INSERT INTO `TestTab` VALUES (1),(2); 606CREATE FUNCTION `f_Test`() RETURNS INT RETURN 123; 607CREATE FUNCTION `f_Test_denied`() RETURNS INT RETURN 123; 608CREATE USER 'tester'; 609CREATE USER 'Tester'; 610GRANT SELECT ON TABLE `TestTab` TO 'tester'; 611GRANT EXECUTE ON FUNCTION `f_Test` TO 'tester'; 612GRANT EXECUTE ON FUNCTION `f_Test_denied` TO 'Tester'; 613SELECT f_Test(); 614f_Test() 615123 616SELECT * FROM TestTab; 617id 6181 6192 620CONNECT con_tester,localhost,tester,,B48872; 621CONNECT con_tester_denied,localhost,Tester,,B48872; 622connection con_tester; 623SELECT * FROM TestTab; 624id 6251 6262 627SELECT `f_Test`(); 628`f_Test`() 629123 630SELECT `F_TEST`(); 631`F_TEST`() 632123 633SELECT f_Test(); 634f_Test() 635123 636SELECT F_TEST(); 637F_TEST() 638123 639connection con_tester_denied; 640SELECT * FROM TestTab; 641SELECT `f_Test`(); 642SELECT `F_TEST`(); 643SELECT f_Test(); 644SELECT F_TEST(); 645SELECT `f_Test_denied`(); 646`f_Test_denied`() 647123 648SELECT `F_TEST_DENIED`(); 649`F_TEST_DENIED`() 650123 651connection default; 652disconnect con_tester; 653disconnect con_tester_denied; 654DROP TABLE `TestTab`; 655DROP FUNCTION `f_Test`; 656DROP FUNCTION `f_Test_denied`; 657USE test; 658DROP USER 'tester'; 659DROP USER 'Tester'; 660DROP DATABASE B48872; 661End of 5.0 tests. 662# 663# Test for bug#57061 "User without privilege on routine can discover 664# its existence." 665# 666drop database if exists mysqltest_db; 667create database mysqltest_db; 668# Create user with no privileges on mysqltest_db database. 669create user bug57061_user@localhost; 670create function mysqltest_db.f1() returns int return 0; 671create procedure mysqltest_db.p1() begin end; 672connect conn1, localhost, bug57061_user,,; 673# Attempt to drop routine on which user doesn't have privileges 674# should result in the same 'access denied' type of error whether 675# routine exists or not. 676drop function if exists mysqltest_db.f_does_not_exist; 677ERROR 42000: alter routine command denied to user 'bug57061_user'@'localhost' for routine 'mysqltest_db.f_does_not_exist' 678drop procedure if exists mysqltest_db.p_does_not_exist; 679ERROR 42000: alter routine command denied to user 'bug57061_user'@'localhost' for routine 'mysqltest_db.p_does_not_exist' 680drop function if exists mysqltest_db.f1; 681ERROR 42000: alter routine command denied to user 'bug57061_user'@'localhost' for routine 'mysqltest_db.f1' 682drop procedure if exists mysqltest_db.p1; 683ERROR 42000: alter routine command denied to user 'bug57061_user'@'localhost' for routine 'mysqltest_db.p1' 684connection default; 685disconnect conn1; 686drop user bug57061_user@localhost; 687drop database mysqltest_db; 688# 689# Bug#11882603 SELECT_ACL ON ANY COLUMN IN MYSQL.PROC ALLOWS TO SEE 690# DEFINITION OF ANY ROUTINE. 691# 692DROP DATABASE IF EXISTS db1; 693CREATE DATABASE db1; 694CREATE PROCEDURE db1.p1() SELECT 1; 695CREATE USER user2@localhost IDENTIFIED BY ''; 696GRANT SELECT(db) ON mysql.proc TO user2@localhost; 697connect con2, localhost, user2; 698# The statement below before disclosed info from body_utf8 column. 699SHOW CREATE PROCEDURE db1.p1; 700ERROR 42000: PROCEDURE p1 does not exist 701# Check that SHOW works with SELECT grant on whole table 702connection default; 703GRANT SELECT ON mysql.proc TO user2@localhost; 704connection con2; 705# This should work 706SHOW CREATE PROCEDURE db1.p1; 707Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 708p1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() 709SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci 710connection default; 711disconnect con2; 712DROP USER user2@localhost; 713DROP DATABASE db1; 714create user foo@local_ost; 715create user foo@`local\_ost` identified via mysql_old_password using '0123456789ABCDEF'; 716create database foodb; 717grant create routine on foodb.* to foo@local_ost; 718connect con1,localhost,foo; 719select user(), current_user(); 720user() current_user() 721foo@localhost foo@local_ost 722show grants; 723Grants for foo@local_ost 724GRANT USAGE ON *.* TO `foo`@`local_ost` 725GRANT CREATE ROUTINE ON `foodb`.* TO `foo`@`local_ost` 726create procedure fooproc() select 'i am fooproc'; 727show grants; 728Grants for foo@local_ost 729GRANT USAGE ON *.* TO `foo`@`local_ost` 730GRANT CREATE ROUTINE ON `foodb`.* TO `foo`@`local_ost` 731GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`fooproc` TO `foo`@`local_ost` 732disconnect con1; 733connection default; 734drop user foo@local_ost; 735drop user foo@`local\_ost`; 736drop procedure fooproc; 737drop database foodb; 738# 739# Test for bug#12602983 - User without privilege on routine can discover 740# its existence by executing "select non_existing_func();" or by 741# "call non_existing_proc()"; 742# 743drop database if exists mysqltest_db; 744create database mysqltest_db; 745create function mysqltest_db.f1() returns int return 0; 746create procedure mysqltest_db.p1() begin end; 747# Create user with no privileges on mysqltest_db database. 748create user bug12602983_user@localhost; 749connect conn1, localhost, bug12602983_user,,; 750# Attempt to execute routine on which user doesn't have privileges 751# should result in the same 'access denied' error whether 752# routine exists or not. 753select mysqltest_db.f_does_not_exist(); 754ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.f_does_not_exist' 755call mysqltest_db.p_does_not_exist(); 756ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.p_does_not_exist' 757select mysqltest_db.f1(); 758ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.f1' 759call mysqltest_db.p1(); 760ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.p1' 761create view bug12602983_v1 as select mysqltest_db.f_does_not_exist(); 762ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.f_does_not_exist' 763create view bug12602983_v1 as select mysqltest_db.f1(); 764ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.f1' 765connection default; 766disconnect conn1; 767drop user bug12602983_user@localhost; 768drop database mysqltest_db; 769create user u1@localhost; 770grant all privileges on *.* to u1@localhost with grant option; 771connect u1, localhost, u1; 772set password=password('foobar'); 773create procedure sp1() select 1; 774show grants; 775Grants for u1@localhost 776GRANT ALL PRIVILEGES ON *.* TO `u1`@`localhost` IDENTIFIED BY PASSWORD '*9B500343BC52E2911172EB52AE5CF4847604C6E5' WITH GRANT OPTION 777grant execute on procedure sp1 to current_user() identified by 'barfoo'; 778show grants; 779Grants for u1@localhost 780GRANT ALL PRIVILEGES ON *.* TO `u1`@`localhost` IDENTIFIED BY PASSWORD '*343915A8181B5728EADBDC73E1F7E6B0C3998483' WITH GRANT OPTION 781GRANT EXECUTE ON PROCEDURE `test`.`sp1` TO `u1`@`localhost` 782drop procedure sp1; 783disconnect u1; 784connection default; 785drop user u1@localhost; 786# 787# MDEV-13396 Unexpected "alter routine comand defined" during CREATE OR REPLACE PROCEDURE 788# 789CREATE DATABASE u1; 790CREATE PROCEDURE u1.p1() BEGIN SELECT 1; END; $$ 791CREATE FUNCTION u1.f1() RETURNS INT BEGIN RETURN 1; END; $$ 792CREATE USER u1@localhost; 793GRANT CREATE ROUTINE ON u1.* TO u1@localhost; 794GRANT ALTER ROUTINE ON FUNCTION u1.f1 TO u1@localhost; 795GRANT ALTER ROUTINE ON PROCEDURE u1.p1 TO u1@localhost; 796connect u1, localhost, u1,,; 797USE u1; 798CREATE OR REPLACE FUNCTION f1() RETURNS INT BEGIN RETURN 2; END; $$ 799CREATE OR REPLACE PROCEDURE p1() BEGIN SELECT 1; END; $$ 800disconnect u1; 801connection default; 802DROP DATABASE u1; 803DROP USER u1@localhost; 804set @@global.character_set_server=@save_character_set_server; 805# 806# Start of 10.5 tests 807# 808# 809# MDEV-20366 Server crashes in get_current_user upon SET PASSWORD via SP 810# 811CREATE PROCEDURE p1() SET PASSWORD FOR foo@localhost=PASSWORD('x'); 812CALL p1(); 813ERROR 28000: Can't find any matching row in the user table 814DROP PROCEDURE p1; 815CREATE USER foo@localhost; 816CREATE PROCEDURE p1() SET PASSWORD FOR foo@localhost=PASSWORD('x'); 817CALL p1(); 818DROP PROCEDURE p1; 819DROP USER foo@localhost; 820# 821# End of 10.5 tests 822# 823