1set @orig_sql_mode_session= @@SESSION.sql_mode; 2set @orig_sql_mode_global= @@GLOBAL.sql_mode; 3set GLOBAL sql_mode= (select replace(@@GLOBAL.sql_mode,'NO_AUTO_CREATE_USER','')); 4Warnings: 5Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 6set SESSION sql_mode= (select replace(@@SESSION.sql_mode,'NO_AUTO_CREATE_USER','')); 7Warnings: 8Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 9use test; 10grant usage on *.* to user1@localhost; 11Warnings: 12Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 13flush privileges; 14drop table if exists t1; 15drop database if exists db1_secret; 16create database db1_secret; 17create procedure db1_secret.dummy() begin end; 18drop procedure db1_secret.dummy; 19use db1_secret; 20create table t1 ( u varchar(64), i int ); 21insert into t1 values('test', 0); 22create procedure stamp(i int) 23insert into db1_secret.t1 values (user(), i); 24show procedure status like 'stamp'; 25Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 26db1_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 27create function db() returns varchar(64) 28begin 29declare v varchar(64); 30select u into v from t1 limit 1; 31return v; 32end| 33show function status like 'db'; 34Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 35db1_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 36call stamp(1); 37select * from t1; 38u i 39test 0 40root@localhost 1 41select db(); 42db() 43test 44grant execute on procedure db1_secret.stamp to user1@'%'; 45Warnings: 46Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 47grant execute on function db1_secret.db to user1@'%'; 48grant execute on procedure db1_secret.stamp to ''@'%'; 49Warnings: 50Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 51grant execute on function db1_secret.db to ''@'%'; 52call db1_secret.stamp(2); 53select db1_secret.db(); 54db1_secret.db() 55test 56select * from db1_secret.t1; 57ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' 58create procedure db1_secret.dummy() begin end; 59ERROR 42000: Access denied for user 'user1'@'localhost' to database 'db1_secret' 60drop procedure db1_secret.dummy; 61ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db1_secret.dummy' 62drop procedure db1_secret.stamp; 63ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db1_secret.stamp' 64drop function db1_secret.db; 65ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db1_secret.db' 66call db1_secret.stamp(3); 67select db1_secret.db(); 68db1_secret.db() 69test 70select * from db1_secret.t1; 71ERROR 42000: SELECT command denied to user ''@'localhost' for table 't1' 72create procedure db1_secret.dummy() begin end; 73ERROR 42000: Access denied for user ''@'%' to database 'db1_secret' 74drop procedure db1_secret.dummy; 75ERROR 42000: alter routine command denied to user ''@'%' for routine 'db1_secret.dummy' 76drop procedure db1_secret.stamp; 77ERROR 42000: alter routine command denied to user ''@'%' for routine 'db1_secret.stamp' 78drop function db1_secret.db; 79ERROR 42000: alter routine command denied to user ''@'%' for routine 'db1_secret.db' 80select * from t1; 81u i 82test 0 83root@localhost 1 84user1@localhost 2 85anon@localhost 3 86alter procedure stamp sql security invoker; 87show procedure status like 'stamp'; 88Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 89db1_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 90alter function db sql security invoker; 91show function status like 'db'; 92Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation 93db1_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 94call stamp(4); 95select * from t1; 96u i 97test 0 98root@localhost 1 99user1@localhost 2 100anon@localhost 3 101root@localhost 4 102select db(); 103db() 104test 105call db1_secret.stamp(5); 106ERROR 42000: INSERT command denied to user 'user1'@'localhost' for table 't1' 107select db1_secret.db(); 108ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' 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' 113drop database if exists db2; 114create database db2; 115use db2; 116create table t2 (s1 int); 117insert into t2 values (0); 118grant usage on db2.* to user1@localhost; 119grant select on db2.* to user1@localhost; 120grant usage on db2.* to user2@localhost; 121Warnings: 122Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 123grant select,insert,update,delete,create routine on db2.* to user2@localhost; 124grant create routine on db2.* to user1@localhost; 125flush privileges; 126use db2; 127create procedure p () insert into t2 values (1); 128call p(); 129ERROR 42000: INSERT command denied to user 'user1'@'localhost' for table 't2' 130use db2; 131call p(); 132ERROR 42000: execute command denied to user 'user2'@'localhost' for routine 'db2.p' 133select * from t2; 134s1 1350 136create procedure q () insert into t2 values (2); 137call q(); 138select * from t2; 139s1 1400 1412 142grant usage on procedure db2.q to user2@localhost with grant option; 143grant execute on procedure db2.q to user1@localhost; 144use db2; 145call q(); 146select * from t2; 147s1 1480 1492 1502 151alter procedure p modifies sql data; 152drop procedure p; 153alter procedure q modifies sql data; 154ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db2.q' 155drop procedure q; 156ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db2.q' 157use db2; 158alter procedure q modifies sql data; 159drop procedure q; 160use test; 161select type,db,name from mysql.proc where db like 'db%'; 162type db name 163FUNCTION db1_secret db 164PROCEDURE db1_secret stamp 165drop database db1_secret; 166drop database db2; 167select type,db,name from mysql.proc where db like 'db%'; 168type db name 169delete from mysql.user where user='user1' or user='user2'; 170delete from mysql.user where user='' and host='%'; 171delete from mysql.procs_priv where user='user1' or user='user2'; 172delete from mysql.procs_priv where user='' and host='%'; 173delete from mysql.db where user='user2'; 174flush privileges; 175grant usage on *.* to usera@localhost; 176Warnings: 177Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 178grant usage on *.* to userb@localhost; 179Warnings: 180Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 181grant usage on *.* to userc@localhost; 182Warnings: 183Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 184create database sptest; 185create table t1 ( u varchar(64), i int ); 186create procedure sptest.p1(i int) insert into test.t1 values (user(), i); 187grant insert on t1 to usera@localhost; 188grant execute on procedure sptest.p1 to usera@localhost; 189show grants for usera@localhost; 190Grants for usera@localhost 191GRANT USAGE ON *.* TO 'usera'@'localhost' 192GRANT INSERT ON `test`.`t1` TO 'usera'@'localhost' 193GRANT EXECUTE ON PROCEDURE `sptest`.`p1` TO 'usera'@'localhost' 194grant execute on procedure sptest.p1 to userc@localhost with grant option; 195show grants for userc@localhost; 196Grants for userc@localhost 197GRANT USAGE ON *.* TO 'userc'@'localhost' 198GRANT EXECUTE ON PROCEDURE `sptest`.`p1` TO 'userc'@'localhost' WITH GRANT OPTION 199call sptest.p1(1); 200grant execute on procedure sptest.p1 to userb@localhost; 201ERROR 42000: grant command denied to user 'usera'@'localhost' for routine 'sptest.p1' 202drop procedure sptest.p1; 203ERROR 42000: alter routine command denied to user 'usera'@'localhost' for routine 'sptest.p1' 204call sptest.p1(2); 205ERROR 42000: execute command denied to user 'userb'@'localhost' for routine 'sptest.p1' 206grant execute on procedure sptest.p1 to userb@localhost; 207ERROR 42000: execute command denied to user 'userb'@'localhost' for routine 'sptest.p1' 208drop procedure sptest.p1; 209ERROR 42000: alter routine command denied to user 'userb'@'localhost' for routine 'sptest.p1' 210call sptest.p1(3); 211grant execute on procedure sptest.p1 to userb@localhost; 212drop procedure sptest.p1; 213ERROR 42000: alter routine command denied to user 'userc'@'localhost' for routine 'sptest.p1' 214call sptest.p1(4); 215grant execute on procedure sptest.p1 to userb@localhost; 216ERROR 42000: grant command denied to user 'userb'@'localhost' for routine 'sptest.p1' 217drop procedure sptest.p1; 218ERROR 42000: alter routine command denied to user 'userb'@'localhost' for routine 'sptest.p1' 219select * from t1; 220u i 221usera@localhost 1 222userc@localhost 3 223userb@localhost 4 224grant all privileges on procedure sptest.p1 to userc@localhost; 225show grants for userc@localhost; 226Grants for userc@localhost 227GRANT USAGE ON *.* TO 'userc'@'localhost' 228GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `sptest`.`p1` TO 'userc'@'localhost' WITH GRANT OPTION 229show grants for userb@localhost; 230Grants for userb@localhost 231GRANT USAGE ON *.* TO 'userb'@'localhost' 232GRANT EXECUTE ON PROCEDURE `sptest`.`p1` TO 'userb'@'localhost' 233revoke all privileges on procedure sptest.p1 from userb@localhost; 234show grants for userb@localhost; 235Grants for userb@localhost 236GRANT USAGE ON *.* TO 'userb'@'localhost' 237use test; 238drop database sptest; 239delete from mysql.user where user='usera' or user='userb' or user='userc'; 240delete from mysql.procs_priv where user='usera' or user='userb' or user='userc'; 241delete from mysql.tables_priv where user='usera'; 242flush privileges; 243drop table t1; 244drop function if exists bug_9503; 245create database mysqltest// 246use mysqltest// 247create table t1 (s1 int)// 248grant select on t1 to user1@localhost// 249Warnings: 250Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 251create function bug_9503 () returns int sql security invoker begin declare v int; 252select min(s1) into v from t1; return v; end// 253use mysqltest; 254select bug_9503(); 255ERROR 42000: execute command denied to user 'user1'@'localhost' for routine 'mysqltest.bug_9503' 256grant execute on function bug_9503 to user1@localhost; 257do 1; 258use test; 259REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost; 260drop function bug_9503; 261use test; 262drop database mysqltest; 263use test; 264select current_user(); 265current_user() 266root@localhost 267select user(); 268user() 269root@localhost 270create procedure bug7291_0 () sql security invoker select current_user(), user(); 271create procedure bug7291_1 () sql security definer call bug7291_0(); 272create procedure bug7291_2 () sql security invoker call bug7291_0(); 273grant execute on procedure bug7291_0 to user1@localhost; 274grant execute on procedure bug7291_1 to user1@localhost; 275grant execute on procedure bug7291_2 to user1@localhost; 276call bug7291_2(); 277current_user() user() 278user1@localhost user1@localhost 279call bug7291_1(); 280current_user() user() 281root@localhost user1@localhost 282drop procedure bug7291_1; 283drop procedure bug7291_2; 284drop procedure bug7291_0; 285REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost; 286drop user user1@localhost; 287drop database if exists mysqltest_1; 288create database mysqltest_1; 289create procedure mysqltest_1.p1() 290begin 291select 1 from dual; 292end// 293grant usage on *.* to mysqltest_1@localhost; 294Warnings: 295Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 296call mysqltest_1.p1(); 297ERROR 42000: execute command denied to user 'mysqltest_1'@'localhost' for routine 'mysqltest_1.p1' 298call mysqltest_1.p1(); 299ERROR 42000: execute command denied to user 'mysqltest_1'@'localhost' for routine 'mysqltest_1.p1' 300drop procedure mysqltest_1.p1; 301drop database mysqltest_1; 302revoke usage on *.* from mysqltest_1@localhost; 303drop user mysqltest_1@localhost; 304drop function if exists bug12812| 305create function bug12812() returns char(2) 306begin 307return 'ok'; 308end; 309create user user_bug12812@localhost IDENTIFIED BY 'ABC'| 310SELECT test.bug12812()| 311ERROR 42000: execute command denied to user 'user_bug12812'@'localhost' for routine 'test.bug12812' 312CREATE VIEW v1 AS SELECT test.bug12812()| 313ERROR 42000: execute command denied to user 'user_bug12812'@'localhost' for routine 'test.bug12812' 314DROP USER user_bug12812@localhost| 315drop function bug12812| 316create database db_bug14834; 317create user user1_bug14834@localhost identified by ''; 318grant all on `db\_bug14834`.* to user1_bug14834@localhost; 319create user user2_bug14834@localhost identified by ''; 320grant all on `db\_bug14834`.* to user2_bug14834@localhost; 321create user user3_bug14834@localhost identified by ''; 322grant all on `db__ug14834`.* to user3_bug14834@localhost; 323create procedure p_bug14834() select user(), current_user(); 324call p_bug14834(); 325user() current_user() 326user1_bug14834@localhost user1_bug14834@localhost 327call p_bug14834(); 328user() current_user() 329user2_bug14834@localhost user1_bug14834@localhost 330call p_bug14834(); 331user() current_user() 332user3_bug14834@localhost user1_bug14834@localhost 333drop user user1_bug14834@localhost; 334drop user user2_bug14834@localhost; 335drop user user3_bug14834@localhost; 336drop database db_bug14834; 337create database db_bug14533; 338use db_bug14533; 339create table t1 (id int); 340create user user_bug14533@localhost identified by ''; 341create procedure bug14533_1() 342sql security definer 343desc db_bug14533.t1; 344create procedure bug14533_2() 345sql security definer 346select * from db_bug14533.t1; 347grant execute on procedure db_bug14533.bug14533_1 to user_bug14533@localhost; 348grant execute on procedure db_bug14533.bug14533_2 to user_bug14533@localhost; 349call db_bug14533.bug14533_1(); 350Field Type Null Key Default Extra 351id int(11) YES NULL 352call db_bug14533.bug14533_2(); 353id 354desc db_bug14533.t1; 355ERROR 42000: SELECT command denied to user 'user_bug14533'@'localhost' for table 't1' 356select * from db_bug14533.t1; 357ERROR 42000: SELECT command denied to user 'user_bug14533'@'localhost' for table 't1' 358drop user user_bug14533@localhost; 359drop database db_bug14533; 360 361---> connection: root 362DROP DATABASE IF EXISTS mysqltest; 363CREATE DATABASE mysqltest; 364CREATE USER mysqltest_1@localhost; 365GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost; 366CREATE USER mysqltest_2@localhost; 367GRANT SUPER ON *.* TO mysqltest_2@localhost; 368GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost; 369 370---> connection: mysqltest_2_con 371USE mysqltest; 372CREATE PROCEDURE wl2897_p1() SELECT 1; 373CREATE FUNCTION wl2897_f1() RETURNS INT RETURN 1; 374 375---> connection: mysqltest_1_con 376USE mysqltest; 377CREATE DEFINER=root@localhost PROCEDURE wl2897_p2() SELECT 2; 378ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation 379CREATE DEFINER=root@localhost FUNCTION wl2897_f2() RETURNS INT RETURN 2; 380ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation 381 382---> connection: mysqltest_2_con 383use mysqltest; 384CREATE DEFINER='a @ b @ c'@localhost PROCEDURE wl2897_p3() SELECT 3; 385Warnings: 386Note 1449 The user specified as a definer ('a @ b @ c'@'localhost') does not exist 387CREATE DEFINER='a @ b @ c'@localhost FUNCTION wl2897_f3() RETURNS INT RETURN 3; 388Warnings: 389Note 1449 The user specified as a definer ('a @ b @ c'@'localhost') does not exist 390 391---> connection: con1root 392USE mysqltest; 393SHOW CREATE PROCEDURE wl2897_p1; 394Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 395wl2897_p1 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`mysqltest_2`@`localhost` PROCEDURE `wl2897_p1`() 396SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci 397SHOW CREATE PROCEDURE wl2897_p3; 398Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 399wl2897_p3 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`a @ b @ c`@`localhost` PROCEDURE `wl2897_p3`() 400SELECT 3 latin1 latin1_swedish_ci latin1_swedish_ci 401SHOW CREATE FUNCTION wl2897_f1; 402Function sql_mode Create Function character_set_client collation_connection Database Collation 403wl2897_f1 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`mysqltest_2`@`localhost` FUNCTION `wl2897_f1`() RETURNS int(11) 404RETURN 1 latin1 latin1_swedish_ci latin1_swedish_ci 405SHOW CREATE FUNCTION wl2897_f3; 406Function sql_mode Create Function character_set_client collation_connection Database Collation 407wl2897_f3 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`a @ b @ c`@`localhost` FUNCTION `wl2897_f3`() RETURNS int(11) 408RETURN 3 latin1 latin1_swedish_ci latin1_swedish_ci 409DROP USER mysqltest_1@localhost; 410DROP USER mysqltest_2@localhost; 411DROP DATABASE mysqltest; 412 413---> connection: root 414DROP DATABASE IF EXISTS mysqltest; 415CREATE DATABASE mysqltest; 416CREATE USER mysqltest_1@localhost; 417GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost; 418CREATE USER mysqltest_2@localhost; 419GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost; 420 421---> connection: mysqltest_1_con 422USE mysqltest; 423CREATE PROCEDURE bug13198_p1() 424SELECT 1; 425CREATE FUNCTION bug13198_f1() RETURNS INT 426RETURN 1; 427CALL bug13198_p1(); 4281 4291 430SELECT bug13198_f1(); 431bug13198_f1() 4321 433 434---> connection: mysqltest_2_con 435USE mysqltest; 436CALL bug13198_p1(); 4371 4381 439SELECT bug13198_f1(); 440bug13198_f1() 4411 442 443---> connection: root 444DROP USER mysqltest_1@localhost; 445 446---> connection: mysqltest_2_con 447USE mysqltest; 448CALL bug13198_p1(); 449ERROR HY000: The user specified as a definer ('mysqltest_1'@'localhost') does not exist 450SELECT bug13198_f1(); 451ERROR HY000: The user specified as a definer ('mysqltest_1'@'localhost') does not exist 452 453---> connection: root 454DROP USER mysqltest_2@localhost; 455DROP DATABASE mysqltest; 456GRANT USAGE ON *.* TO user19857@localhost IDENTIFIED BY 'meow'; 457Warnings: 458Warning 1287 Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 459GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ROUTINE, ALTER ROUTINE ON test.* TO 460user19857@localhost; 461SELECT Host,User,authentication_string FROM mysql.user WHERE User='user19857'; 462Host User authentication_string 463localhost user19857 *82DC221D557298F6CE9961037DB1C90604792F5C 464 465---> connection: mysqltest_2_con 466USE test; 467CREATE PROCEDURE sp19857() DETERMINISTIC 468BEGIN 469DECLARE a INT; 470SET a=1; 471SELECT a; 472END // 473SHOW CREATE PROCEDURE test.sp19857; 474Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 475sp19857 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`user19857`@`localhost` PROCEDURE `sp19857`() 476 DETERMINISTIC 477BEGIN 478DECLARE a INT; 479SET a=1; 480SELECT a; 481END latin1 latin1_swedish_ci latin1_swedish_ci 482DROP PROCEDURE IF EXISTS test.sp19857; 483 484---> connection: root 485SELECT Host,User,authentication_string FROM mysql.user WHERE User='user19857'; 486Host User authentication_string 487localhost user19857 *82DC221D557298F6CE9961037DB1C90604792F5C 488DROP USER user19857@localhost; 489use test; 490DROP TABLE IF EXISTS t1; 491DROP VIEW IF EXISTS v1; 492DROP FUNCTION IF EXISTS f_suid; 493DROP PROCEDURE IF EXISTS p_suid; 494DROP FUNCTION IF EXISTS f_evil; 495DELETE FROM mysql.user WHERE user LIKE 'mysqltest\_%'; 496DELETE FROM mysql.db WHERE user LIKE 'mysqltest\_%'; 497DELETE FROM mysql.tables_priv WHERE user LIKE 'mysqltest\_%'; 498DELETE FROM mysql.columns_priv WHERE user LIKE 'mysqltest\_%'; 499FLUSH PRIVILEGES; 500CREATE TABLE t1 (i INT); 501CREATE FUNCTION f_suid(i INT) RETURNS INT SQL SECURITY DEFINER RETURN 0; 502CREATE PROCEDURE p_suid(IN i INT) SQL SECURITY DEFINER SET @c:= 0; 503CREATE USER mysqltest_u1@localhost; 504GRANT EXECUTE ON test.* TO mysqltest_u1@localhost; 505CREATE DEFINER=mysqltest_u1@localhost FUNCTION f_evil () RETURNS INT 506SQL SECURITY INVOKER 507BEGIN 508SET @a:= CURRENT_USER(); 509SET @b:= (SELECT COUNT(*) FROM t1); 510RETURN @b; 511END| 512CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT f_evil(); 513SELECT COUNT(*) FROM t1; 514ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' 515SELECT f_evil(); 516ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' 517SELECT @a, @b; 518@a @b 519mysqltest_u1@localhost NULL 520SELECT f_suid(f_evil()); 521ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' 522SELECT @a, @b; 523@a @b 524mysqltest_u1@localhost NULL 525CALL p_suid(f_evil()); 526ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' 527SELECT @a, @b; 528@a @b 529mysqltest_u1@localhost NULL 530SELECT * FROM v1; 531ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'v1' 532SELECT @a, @b; 533@a @b 534mysqltest_u1@localhost NULL 535DROP VIEW v1; 536DROP FUNCTION f_evil; 537DROP USER mysqltest_u1@localhost; 538DROP PROCEDURE p_suid; 539DROP FUNCTION f_suid; 540DROP TABLE t1; 541# 542# Bug #48872 : Privileges for stored functions ignored if function name 543# is mixed case 544# 545CREATE DATABASE B48872; 546USE B48872; 547CREATE TABLE `TestTab` (id INT); 548INSERT INTO `TestTab` VALUES (1),(2); 549CREATE FUNCTION `f_Test`() RETURNS INT RETURN 123; 550CREATE FUNCTION `f_Test_denied`() RETURNS INT RETURN 123; 551CREATE USER 'tester'; 552CREATE USER 'Tester'; 553GRANT SELECT ON TABLE `TestTab` TO 'tester'; 554GRANT EXECUTE ON FUNCTION `f_Test` TO 'tester'; 555GRANT EXECUTE ON FUNCTION `f_Test_denied` TO 'Tester'; 556SELECT f_Test(); 557f_Test() 558123 559SELECT * FROM TestTab; 560id 5611 5622 563SELECT * FROM TestTab; 564id 5651 5662 567SELECT `f_Test`(); 568`f_Test`() 569123 570SELECT `F_TEST`(); 571`F_TEST`() 572123 573SELECT f_Test(); 574f_Test() 575123 576SELECT F_TEST(); 577F_TEST() 578123 579SELECT * FROM TestTab; 580SELECT `f_Test`(); 581SELECT `F_TEST`(); 582SELECT f_Test(); 583SELECT F_TEST(); 584SELECT `f_Test_denied`(); 585`f_Test_denied`() 586123 587SELECT `F_TEST_DENIED`(); 588`F_TEST_DENIED`() 589123 590DROP TABLE `TestTab`; 591DROP FUNCTION `f_Test`; 592DROP FUNCTION `f_Test_denied`; 593USE test; 594DROP USER 'tester'; 595DROP USER 'Tester'; 596DROP DATABASE B48872; 597End of 5.0 tests. 598# 599# Test for bug#57061 "User without privilege on routine can discover 600# its existence." 601# 602drop database if exists mysqltest_db; 603create database mysqltest_db; 604# Create user with no privileges on mysqltest_db database. 605create user bug57061_user@localhost; 606create function mysqltest_db.f1() returns int return 0; 607create procedure mysqltest_db.p1() begin end; 608# Connect as user 'bug57061_user@localhost' 609# Attempt to drop routine on which user doesn't have privileges 610# should result in the same 'access denied' type of error whether 611# routine exists or not. 612drop function if exists mysqltest_db.f_does_not_exist; 613ERROR 42000: alter routine command denied to user 'bug57061_user'@'localhost' for routine 'mysqltest_db.f_does_not_exist' 614drop procedure if exists mysqltest_db.p_does_not_exist; 615ERROR 42000: alter routine command denied to user 'bug57061_user'@'localhost' for routine 'mysqltest_db.p_does_not_exist' 616drop function if exists mysqltest_db.f1; 617ERROR 42000: alter routine command denied to user 'bug57061_user'@'localhost' for routine 'mysqltest_db.f1' 618drop procedure if exists mysqltest_db.p1; 619ERROR 42000: alter routine command denied to user 'bug57061_user'@'localhost' for routine 'mysqltest_db.p1' 620# Connection 'default'. 621drop user bug57061_user@localhost; 622drop database mysqltest_db; 623# 624# Bug#11882603 SELECT_ACL ON ANY COLUMN IN MYSQL.PROC ALLOWS TO SEE 625# DEFINITION OF ANY ROUTINE. 626# 627DROP DATABASE IF EXISTS db1; 628CREATE DATABASE db1; 629CREATE PROCEDURE db1.p1() SELECT 1; 630CREATE USER user2@localhost IDENTIFIED BY ''; 631GRANT SELECT(db) ON mysql.proc TO user2@localhost; 632# Connection con2 as user2 633# The statement below before disclosed info from body_utf8 column. 634SHOW CREATE PROCEDURE db1.p1; 635ERROR 42000: PROCEDURE p1 does not exist 636# Check that SHOW works with SELECT grant on whole table 637# Connection default 638GRANT SELECT ON mysql.proc TO user2@localhost; 639# Connection con2 640# This should work 641SHOW CREATE PROCEDURE db1.p1; 642Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation 643p1 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() 644SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci 645# Connection default 646DROP USER user2@localhost; 647DROP DATABASE db1; 648# 649# Test for bug#12602983 - User without privilege on routine can discover 650# its existence by executing "select non_existing_func();" or by 651# "call non_existing_proc()"; 652# 653drop database if exists mysqltest_db; 654create database mysqltest_db; 655create function mysqltest_db.f1() returns int return 0; 656create procedure mysqltest_db.p1() begin end; 657# Create user with no privileges on mysqltest_db database. 658create user bug12602983_user@localhost; 659# Connect as user 'bug12602983_user@localhost' 660# Attempt to execute routine on which user doesn't have privileges 661# should result in the same 'access denied' error whether 662# routine exists or not. 663select mysqltest_db.f_does_not_exist(); 664ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.f_does_not_exist' 665call mysqltest_db.p_does_not_exist(); 666ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.p_does_not_exist' 667select mysqltest_db.f1(); 668ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.f1' 669call mysqltest_db.p1(); 670ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.p1' 671create view bug12602983_v1 as select mysqltest_db.f_does_not_exist(); 672ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.f_does_not_exist' 673create view bug12602983_v1 as select mysqltest_db.f1(); 674ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.f1' 675# Connection 'default'. 676drop user bug12602983_user@localhost; 677drop database mysqltest_db; 678set GLOBAL sql_mode= @orig_sql_mode_global; 679Warnings: 680Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 681set SESSION sql_mode= @orig_sql_mode_session; 682Warnings: 683Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 684