1# 2# Testing SQL SECURITY of stored procedures 3# 4 5# Can't test with embedded server that doesn't support grants 6--source include/not_embedded.inc 7--source include/default_charset.inc 8set @@global.character_set_server=@@session.character_set_server; 9 10# Save the initial number of concurrent sessions 11--source include/count_sessions.inc 12connect (con1root,localhost,root,,); 13 14connection con1root; 15use test; 16 17# Create user user1 with no particular access rights 18create user user1@localhost; 19grant usage on *.* to user1@localhost; 20flush privileges; 21 22--disable_warnings 23drop table if exists t1; 24drop database if exists db1_secret; 25--enable_warnings 26# Create our secret database 27create database db1_secret; 28 29# Can create a procedure in other db 30create procedure db1_secret.dummy() begin end; 31drop procedure db1_secret.dummy; 32 33use db1_secret; 34 35create table t1 ( u varchar(64), i int ); 36insert into t1 values('test', 0); 37 38# A test procedure and function 39create procedure stamp(i int) 40 insert into db1_secret.t1 values (user(), i); 41--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' 42show procedure status like 'stamp'; 43 44delimiter |; 45create function db() returns varchar(64) 46begin 47 declare v varchar(64); 48 49 select u into v from t1 limit 1; 50 51 return v; 52end| 53delimiter ;| 54--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' 55show function status like 'db'; 56 57# root can, of course 58call stamp(1); 59select * from t1; 60select db(); 61 62create user user1@'%'; 63grant execute on procedure db1_secret.stamp to user1@'%'; 64grant execute on function db1_secret.db to user1@'%'; 65set sql_mode=''; 66grant execute on procedure db1_secret.stamp to ''@'%'; 67grant execute on function db1_secret.db to ''@'%'; 68set sql_mode=default; 69 70connect (con2user1,localhost,user1,,); 71connect (con3anon,localhost,anon,,); 72 73 74# 75# User1 can 76# 77connection con2user1; 78 79# This should work... 80call db1_secret.stamp(2); 81select db1_secret.db(); 82 83# ...but not this 84--error ER_TABLEACCESS_DENIED_ERROR 85select * from db1_secret.t1; 86 87# ...and not this 88--error ER_DBACCESS_DENIED_ERROR 89create procedure db1_secret.dummy() begin end; 90--error ER_PROCACCESS_DENIED_ERROR 91drop procedure db1_secret.dummy; 92--error ER_PROCACCESS_DENIED_ERROR 93drop procedure db1_secret.stamp; 94--error ER_PROCACCESS_DENIED_ERROR 95drop function db1_secret.db; 96 97 98# 99# Anonymous can 100# 101connection con3anon; 102 103# This should work... 104call db1_secret.stamp(3); 105select db1_secret.db(); 106 107# ...but not this 108--error ER_TABLEACCESS_DENIED_ERROR 109select * from db1_secret.t1; 110 111# ...and not this 112--error ER_DBACCESS_DENIED_ERROR 113create procedure db1_secret.dummy() begin end; 114--error ER_PROCACCESS_DENIED_ERROR 115drop procedure db1_secret.dummy; 116--error ER_PROCACCESS_DENIED_ERROR 117drop procedure db1_secret.stamp; 118--error ER_PROCACCESS_DENIED_ERROR 119drop function db1_secret.db; 120 121 122# 123# Check it out 124# 125connection con1root; 126select * from t1; 127 128# 129# Change to invoker's rights 130# 131alter procedure stamp sql security invoker; 132--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' 133show procedure status like 'stamp'; 134 135alter function db sql security invoker; 136--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' 137show function status like 'db'; 138 139# root still can 140call stamp(4); 141select * from t1; 142select db(); 143 144# 145# User1 cannot 146# 147connection con2user1; 148 149# This should not work 150--error ER_TABLEACCESS_DENIED_ERROR 151call db1_secret.stamp(5); 152--error ER_TABLEACCESS_DENIED_ERROR 153select db1_secret.db(); 154 155# 156# Anonymous cannot 157# 158connection con3anon; 159 160# This should not work 161--error ER_TABLEACCESS_DENIED_ERROR 162call db1_secret.stamp(6); 163--error ER_TABLEACCESS_DENIED_ERROR 164select db1_secret.db(); 165 166# 167# Bug#2777 Stored procedure doesn't observe definer's rights 168# 169 170connection con1root; 171--disable_warnings 172drop database if exists db2; 173--enable_warnings 174create database db2; 175 176use db2; 177 178create table t2 (s1 int); 179insert into t2 values (0); 180 181grant usage on db2.* to user1@localhost; 182grant select on db2.* to user1@localhost; 183create user user2@localhost; 184grant usage on db2.* to user2@localhost; 185grant select,insert,update,delete,create routine on db2.* to user2@localhost; 186grant create routine on db2.* to user1@localhost; 187flush privileges; 188 189connection con2user1; 190use db2; 191 192create procedure p () insert into t2 values (1); 193 194# Check that this doesn't work. 195--error ER_TABLEACCESS_DENIED_ERROR 196call p(); 197 198connect (con4user2,localhost,user2,,); 199 200connection con4user2; 201use db2; 202 203# This should not work, since p is executed with definer's (user1's) rights. 204--error ER_PROCACCESS_DENIED_ERROR 205call p(); 206select * from t2; 207 208create procedure q () insert into t2 values (2); 209 210call q(); 211select * from t2; 212 213connection con1root; 214grant usage on procedure db2.q to user2@localhost with grant option; 215 216connection con4user2; 217grant execute on procedure db2.q to user1@localhost; 218 219connection con2user1; 220use db2; 221 222# This should work 223call q(); 224select * from t2; 225 226# 227# Bug#6030 Stored procedure has no appropriate DROP privilege 228# (or ALTER for that matter) 229 230# still connection con2user1 in db2 231 232# This should work: 233alter procedure p modifies sql data; 234drop procedure p; 235 236# This should NOT work 237--error ER_PROCACCESS_DENIED_ERROR 238alter procedure q modifies sql data; 239--error ER_PROCACCESS_DENIED_ERROR 240drop procedure q; 241 242connection con1root; 243use db2; 244# But root always can 245alter procedure q modifies sql data; 246drop procedure q; 247 248 249# Clean up 250#Still connection con1root; 251disconnect con2user1; 252disconnect con3anon; 253disconnect con4user2; 254use test; 255select type,db,name from mysql.proc where db like 'db%'; 256drop database db1_secret; 257drop database db2; 258# Make sure the routines are gone 259select type,db,name from mysql.proc where db like 'db%'; 260# Get rid of the users 261delete from mysql.user where user='user1' or user='user2'; 262delete from mysql.user where user='' and host='%'; 263# And any routine privileges 264delete from mysql.procs_priv where user='user1' or user='user2'; 265# Delete the grants to user ''@'%' that was created above 266delete from mysql.procs_priv where user='' and host='%'; 267delete from mysql.db where user='user2'; 268flush privileges; 269# 270# Test the new security acls 271# 272create user usera@localhost; 273grant usage on *.* to usera@localhost; 274create user userb@localhost; 275grant usage on *.* to userb@localhost; 276create user userc@localhost; 277grant usage on *.* to userc@localhost; 278create database sptest; 279create table t1 ( u varchar(64), i int ); 280create procedure sptest.p1(i int) insert into test.t1 values (user(), i); 281grant insert on t1 to usera@localhost; 282grant execute on procedure sptest.p1 to usera@localhost; 283show grants for usera@localhost; 284grant execute on procedure sptest.p1 to userc@localhost with grant option; 285show grants for userc@localhost; 286 287connect (con2usera,localhost,usera,,); 288connect (con3userb,localhost,userb,,); 289connect (con4userc,localhost,userc,,); 290 291connection con2usera; 292call sptest.p1(1); 293--error ER_PROCACCESS_DENIED_ERROR 294grant execute on procedure sptest.p1 to userb@localhost; 295--error ER_PROCACCESS_DENIED_ERROR 296drop procedure sptest.p1; 297 298connection con3userb; 299--error ER_PROCACCESS_DENIED_ERROR 300call sptest.p1(2); 301--error ER_PROCACCESS_DENIED_ERROR 302grant execute on procedure sptest.p1 to userb@localhost; 303--error ER_PROCACCESS_DENIED_ERROR 304drop procedure sptest.p1; 305 306connection con4userc; 307call sptest.p1(3); 308grant execute on procedure sptest.p1 to userb@localhost; 309--error ER_PROCACCESS_DENIED_ERROR 310drop procedure sptest.p1; 311 312connection con3userb; 313call sptest.p1(4); 314--error ER_PROCACCESS_DENIED_ERROR 315grant execute on procedure sptest.p1 to userb@localhost; 316--error ER_PROCACCESS_DENIED_ERROR 317drop procedure sptest.p1; 318 319connection con1root; 320select * from t1; 321 322grant all privileges on procedure sptest.p1 to userc@localhost; 323show grants for userc@localhost; 324show grants for userb@localhost; 325 326connection con4userc; 327revoke all privileges on procedure sptest.p1 from userb@localhost; 328 329connection con1root; 330show grants for userb@localhost; 331 332#cleanup 333disconnect con4userc; 334disconnect con3userb; 335disconnect con2usera; 336use test; 337drop database sptest; 338delete from mysql.user where user='usera' or user='userb' or user='userc'; 339delete from mysql.procs_priv where user='usera' or user='userb' or user='userc'; 340delete from mysql.tables_priv where user='usera'; 341flush privileges; 342drop table t1; 343 344# 345# Bug#9503 resetting correct parameters of thread after error in SP function 346# 347connect (root,localhost,root,,test); 348connection root; 349 350--disable_warnings 351drop function if exists bug_9503; 352drop user if exists user1@localhost; 353--enable_warnings 354delimiter //; 355create database mysqltest// 356use mysqltest// 357create table t1 (s1 int)// 358create user user1@localhost// 359grant select on t1 to user1@localhost// 360create function bug_9503 () returns int sql security invoker begin declare v int; 361select min(s1) into v from t1; return v; end// 362delimiter ;// 363 364connect (user1,localhost,user1,,test); 365connection user1; 366use mysqltest; 367-- error ER_PROCACCESS_DENIED_ERROR 368select bug_9503(); 369 370connection root; 371grant execute on function bug_9503 to user1@localhost; 372 373connection user1; 374do 1; 375use test; 376 377disconnect user1; 378connection root; 379REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost; 380drop function bug_9503; 381use test; 382drop database mysqltest; 383connection default; 384disconnect root; 385 386# 387# correct value from current_user() in function run from "security definer" 388# (Bug#7291 Stored procedures: wrong CURRENT_USER value) 389# 390connection con1root; 391use test; 392 393select current_user(); 394select user(); 395create procedure bug7291_0 () sql security invoker select current_user(), user(); 396create procedure bug7291_1 () sql security definer call bug7291_0(); 397create procedure bug7291_2 () sql security invoker call bug7291_0(); 398grant execute on procedure bug7291_0 to user1@localhost; 399grant execute on procedure bug7291_1 to user1@localhost; 400grant execute on procedure bug7291_2 to user1@localhost; 401 402connect (user1,localhost,user1,,); 403connection user1; 404 405call bug7291_2(); 406call bug7291_1(); 407 408connection con1root; 409drop procedure bug7291_1; 410drop procedure bug7291_2; 411drop procedure bug7291_0; 412disconnect user1; 413REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost; 414drop user user1@localhost; 415 416# 417# Bug#12318 Wrong error message when accessing an inaccessible stored 418# procedure in another database when the current database is 419# information_schema. 420# 421 422--disable_warnings 423drop database if exists mysqltest_1; 424--enable_warnings 425 426create database mysqltest_1; 427delimiter //; 428create procedure mysqltest_1.p1() 429begin 430 select 1 from dual; 431end// 432delimiter ;// 433 434create user mysqltest_1@localhost; 435grant usage on *.* to mysqltest_1@localhost; 436 437connect (n1,localhost,mysqltest_1,,information_schema,$MASTER_MYPORT,$MASTER_MYSOCK); 438connection n1; 439--error ER_PROCACCESS_DENIED_ERROR 440call mysqltest_1.p1(); 441disconnect n1; 442# Test also without a current database 443connect (n2,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK); 444connection n2; 445--error ER_PROCACCESS_DENIED_ERROR 446call mysqltest_1.p1(); 447disconnect n2; 448 449connection default; 450 451drop procedure mysqltest_1.p1; 452drop database mysqltest_1; 453 454revoke usage on *.* from mysqltest_1@localhost; 455drop user mysqltest_1@localhost; 456 457# 458# Bug#12812 create view calling a function works without execute right 459# on function 460delimiter |; 461--disable_warnings 462drop function if exists bug12812| 463--enable_warnings 464create function bug12812() returns char(2) 465begin 466 return 'ok'; 467end; 468create user user_bug12812@localhost IDENTIFIED BY 'ABC'| 469--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 470connect (test_user_12812,localhost,user_bug12812,ABC,test)| 471--error ER_PROCACCESS_DENIED_ERROR 472SELECT test.bug12812()| 473--error ER_PROCACCESS_DENIED_ERROR 474CREATE VIEW v1 AS SELECT test.bug12812()| 475# Cleanup 476connection default| 477disconnect test_user_12812| 478DROP USER user_bug12812@localhost| 479drop function bug12812| 480delimiter ;| 481 482 483# 484# Bug#14834 Server denies to execute Stored Procedure 485# 486# The problem here was with '_' in the database name. 487# 488create database db_bug14834; 489 490create user user1_bug14834@localhost identified by ''; 491# The exact name of the database (no wildcard) 492grant all on `db\_bug14834`.* to user1_bug14834@localhost; 493 494create user user2_bug14834@localhost identified by ''; 495# The exact name of the database (no wildcard) 496grant all on `db\_bug14834`.* to user2_bug14834@localhost; 497 498create user user3_bug14834@localhost identified by ''; 499# Wildcards in the database name 500grant all on `db__ug14834`.* to user3_bug14834@localhost; 501 502connect (user1_bug14834,localhost,user1_bug14834,,db_bug14834); 503# Create the procedure and check that we can call it 504create procedure p_bug14834() select user(), current_user(); 505call p_bug14834(); 506 507connect (user2_bug14834,localhost,user2_bug14834,,db_bug14834); 508# This didn't work before 509call p_bug14834(); 510 511connect (user3_bug14834,localhost,user3_bug14834,,db_bug14834); 512# Should also work 513call p_bug14834(); 514 515# Cleanup 516connection default; 517disconnect user1_bug14834; 518disconnect user2_bug14834; 519disconnect user3_bug14834; 520drop user user1_bug14834@localhost; 521drop user user2_bug14834@localhost; 522drop user user3_bug14834@localhost; 523drop database db_bug14834; 524 525 526# 527# Bug#14533 'desc tbl' in stored procedure causes error 528# ER_TABLEACCESS_DENIED_ERROR 529# 530create database db_bug14533; 531use db_bug14533; 532create table t1 (id int); 533create user user_bug14533@localhost identified by ''; 534 535create procedure bug14533_1() 536 sql security definer 537 desc db_bug14533.t1; 538 539create procedure bug14533_2() 540 sql security definer 541 select * from db_bug14533.t1; 542 543grant execute on procedure db_bug14533.bug14533_1 to user_bug14533@localhost; 544grant execute on procedure db_bug14533.bug14533_2 to user_bug14533@localhost; 545 546connect (user_bug14533,localhost,user_bug14533,,test); 547 548# These should work 549call db_bug14533.bug14533_1(); 550call db_bug14533.bug14533_2(); 551 552# For reference, these should not work 553--error ER_TABLEACCESS_DENIED_ERROR 554desc db_bug14533.t1; 555--error ER_TABLEACCESS_DENIED_ERROR 556select * from db_bug14533.t1; 557 558# Cleanup 559connection default; 560disconnect user_bug14533; 561drop user user_bug14533@localhost; 562drop database db_bug14533; 563 564 565# 566# WL#2897 Complete definer support in the stored routines. 567# 568# The following cases are tested: 569# 1. check that if DEFINER-clause is not explicitly specified, stored routines 570# are created with CURRENT_USER privileges; 571# 2. check that if DEFINER-clause specifies non-current user, SUPER privilege 572# is required to create a stored routine; 573# 3. check that if DEFINER-clause specifies non-existent user, a warning is 574# emitted. 575# 4. check that SHOW CREATE PROCEDURE | FUNCTION works correctly; 576# 577# The following cases are tested in other test suites: 578# - check that mysqldump dumps new attribute correctly; 579# - check that slave replicates CREATE-statements with explicitly specified 580# DEFINER correctly. 581# 582 583# Setup the environment. 584 585--connection con1root 586 587--disable_warnings 588DROP DATABASE IF EXISTS mysqltest; 589--enable_warnings 590 591CREATE DATABASE mysqltest; 592 593CREATE USER mysqltest_1@localhost; 594GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost; 595 596CREATE USER mysqltest_2@localhost; 597GRANT SET USER ON *.* TO mysqltest_2@localhost; 598GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost; 599 600--connect (mysqltest_2_con,localhost,mysqltest_2,,mysqltest) 601--connect (mysqltest_1_con,localhost,mysqltest_1,,mysqltest) 602 603# test case (1). 604 605--connection mysqltest_2_con 606 607USE mysqltest; 608 609CREATE PROCEDURE wl2897_p1() SELECT 1; 610 611CREATE FUNCTION wl2897_f1() RETURNS INT RETURN 1; 612 613# test case (2). 614 615--connection mysqltest_1_con 616 617USE mysqltest; 618 619--error ER_SPECIFIC_ACCESS_DENIED_ERROR 620CREATE DEFINER=root@localhost PROCEDURE wl2897_p2() SELECT 2; 621 622--error ER_SPECIFIC_ACCESS_DENIED_ERROR 623CREATE DEFINER=root@localhost FUNCTION wl2897_f2() RETURNS INT RETURN 2; 624 625# test case (3). 626 627--connection mysqltest_2_con 628 629use mysqltest; 630 631CREATE DEFINER='a @ b @ c'@localhost PROCEDURE wl2897_p3() SELECT 3; 632 633CREATE DEFINER='a @ b @ c'@localhost FUNCTION wl2897_f3() RETURNS INT RETURN 3; 634 635# test case (4). 636 637--connection con1root 638 639USE mysqltest; 640 641SHOW CREATE PROCEDURE wl2897_p1; 642SHOW CREATE PROCEDURE wl2897_p3; 643 644SHOW CREATE FUNCTION wl2897_f1; 645SHOW CREATE FUNCTION wl2897_f3; 646 647# Cleanup. 648 649DROP USER mysqltest_1@localhost; 650DROP USER mysqltest_2@localhost; 651 652DROP DATABASE mysqltest; 653 654--disconnect mysqltest_1_con 655--disconnect mysqltest_2_con 656 657 658# 659# Bug#13198 SP executes if definer does not exist 660# 661 662# Prepare environment. 663 664--connection con1root 665 666--disable_warnings 667DROP DATABASE IF EXISTS mysqltest; 668--enable_warnings 669 670CREATE DATABASE mysqltest; 671 672CREATE USER mysqltest_1@localhost; 673GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost; 674 675CREATE USER mysqltest_2@localhost; 676GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost; 677 678--connect (mysqltest_1_con,localhost,mysqltest_1,,mysqltest) 679--connect (mysqltest_2_con,localhost,mysqltest_2,,mysqltest) 680 681# Create a procedure/function under u1. 682 683--connection mysqltest_1_con 684 685USE mysqltest; 686 687CREATE PROCEDURE bug13198_p1() 688 SELECT 1; 689 690CREATE FUNCTION bug13198_f1() RETURNS INT 691 RETURN 1; 692 693CALL bug13198_p1(); 694 695SELECT bug13198_f1(); 696 697# Check that u2 can call the procedure/function. 698 699--connection mysqltest_2_con 700 701USE mysqltest; 702 703CALL bug13198_p1(); 704 705SELECT bug13198_f1(); 706 707# Drop user u1 (definer of the object); 708 709--connection con1root 710 711--disconnect mysqltest_1_con 712 713DROP USER mysqltest_1@localhost; 714 715# Check that u2 can not call the procedure/function. 716 717--connection mysqltest_2_con 718 719USE mysqltest; 720 721--error ER_NO_SUCH_USER 722CALL bug13198_p1(); 723 724--error ER_NO_SUCH_USER 725SELECT bug13198_f1(); 726 727# Cleanup. 728 729--connection con1root 730 731--disconnect mysqltest_2_con 732 733DROP USER mysqltest_2@localhost; 734 735DROP DATABASE mysqltest; 736 737# 738# Bug#19857 When a user with CREATE ROUTINE priv creates a routine, 739# it results in NULL p/w 740# 741 742# Can't test with embedded server that doesn't support grants 743 744GRANT USAGE ON *.* TO user19857@localhost IDENTIFIED BY 'meow'; 745GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ROUTINE, ALTER ROUTINE ON test.* TO 746user19857@localhost; 747SELECT Host,User,Plugin,Authentication_string FROM mysql.user WHERE User='user19857'; 748 749--connect (mysqltest_2_con,localhost,user19857,meow,test) 750--connection mysqltest_2_con 751 752USE test; 753 754DELIMITER //; 755 CREATE PROCEDURE sp19857() DETERMINISTIC 756 BEGIN 757 DECLARE a INT; 758 SET a=1; 759 SELECT a; 760 END // 761DELIMITER ;// 762 763SHOW CREATE PROCEDURE test.sp19857; 764 765--disconnect mysqltest_2_con 766--connect (mysqltest_2_con,localhost,user19857,meow,test) 767--connection mysqltest_2_con 768 769DROP PROCEDURE IF EXISTS test.sp19857; 770 771--connection con1root 772 773--disconnect mysqltest_2_con 774 775SELECT Host,User,Plugin,Authentication_string FROM mysql.user WHERE User='user19857'; 776 777DROP USER user19857@localhost; 778 779--disconnect con1root 780--connection default 781use test; 782 783# 784# Bug#18630 Arguments of suid routine calculated in wrong security context 785# 786# Arguments of suid routines were calculated in definer's security 787# context instead of caller's context thus creating security hole. 788# 789--disable_warnings 790DROP TABLE IF EXISTS t1; 791DROP VIEW IF EXISTS v1; 792DROP FUNCTION IF EXISTS f_suid; 793DROP PROCEDURE IF EXISTS p_suid; 794DROP FUNCTION IF EXISTS f_evil; 795--enable_warnings 796DELETE FROM mysql.user WHERE user LIKE 'mysqltest\_%'; 797DELETE FROM mysql.db WHERE user LIKE 'mysqltest\_%'; 798DELETE FROM mysql.tables_priv WHERE user LIKE 'mysqltest\_%'; 799DELETE FROM mysql.columns_priv WHERE user LIKE 'mysqltest\_%'; 800FLUSH PRIVILEGES; 801 802CREATE TABLE t1 (i INT); 803CREATE FUNCTION f_suid(i INT) RETURNS INT SQL SECURITY DEFINER RETURN 0; 804CREATE PROCEDURE p_suid(IN i INT) SQL SECURITY DEFINER SET @c:= 0; 805 806CREATE USER mysqltest_u1@localhost; 807# Thanks to this grant statement privileges of anonymous users on 808# 'test' database are not applicable for mysqltest_u1@localhost. 809GRANT EXECUTE ON test.* TO mysqltest_u1@localhost; 810 811delimiter |; 812CREATE DEFINER=mysqltest_u1@localhost FUNCTION f_evil () RETURNS INT 813 SQL SECURITY INVOKER 814BEGIN 815 SET @a:= CURRENT_USER(); 816 SET @b:= (SELECT COUNT(*) FROM t1); 817 RETURN @b; 818END| 819delimiter ;| 820 821CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT f_evil(); 822 823connect (conn1, localhost, mysqltest_u1,,); 824 825--error ER_TABLEACCESS_DENIED_ERROR 826SELECT COUNT(*) FROM t1; 827 828--error ER_TABLEACCESS_DENIED_ERROR 829SELECT f_evil(); 830SELECT @a, @b; 831 832--error ER_TABLEACCESS_DENIED_ERROR 833SELECT f_suid(f_evil()); 834SELECT @a, @b; 835 836--error ER_TABLEACCESS_DENIED_ERROR 837CALL p_suid(f_evil()); 838SELECT @a, @b; 839 840--error ER_TABLEACCESS_DENIED_ERROR 841SELECT * FROM v1; 842SELECT @a, @b; 843 844disconnect conn1; 845connection default; 846 847DROP VIEW v1; 848DROP FUNCTION f_evil; 849DROP USER mysqltest_u1@localhost; 850DROP PROCEDURE p_suid; 851DROP FUNCTION f_suid; 852DROP TABLE t1; 853 854--echo # 855--echo # Bug #48872 : Privileges for stored functions ignored if function name 856--echo # is mixed case 857--echo # 858 859CREATE DATABASE B48872; 860USE B48872; 861CREATE TABLE `TestTab` (id INT); 862INSERT INTO `TestTab` VALUES (1),(2); 863CREATE FUNCTION `f_Test`() RETURNS INT RETURN 123; 864CREATE FUNCTION `f_Test_denied`() RETURNS INT RETURN 123; 865CREATE USER 'tester'; 866CREATE USER 'Tester'; 867GRANT SELECT ON TABLE `TestTab` TO 'tester'; 868GRANT EXECUTE ON FUNCTION `f_Test` TO 'tester'; 869GRANT EXECUTE ON FUNCTION `f_Test_denied` TO 'Tester'; 870 871SELECT f_Test(); 872SELECT * FROM TestTab; 873 874CONNECT (con_tester,localhost,tester,,B48872); 875CONNECT (con_tester_denied,localhost,Tester,,B48872); 876CONNECTION con_tester; 877 878SELECT * FROM TestTab; 879SELECT `f_Test`(); 880SELECT `F_TEST`(); 881SELECT f_Test(); 882SELECT F_TEST(); 883 884CONNECTION con_tester_denied; 885 886--disable_result_log 887--error ER_TABLEACCESS_DENIED_ERROR 888SELECT * FROM TestTab; 889--error ER_PROCACCESS_DENIED_ERROR 890SELECT `f_Test`(); 891--error ER_PROCACCESS_DENIED_ERROR 892SELECT `F_TEST`(); 893--error ER_PROCACCESS_DENIED_ERROR 894SELECT f_Test(); 895--error ER_PROCACCESS_DENIED_ERROR 896SELECT F_TEST(); 897--enable_result_log 898SELECT `f_Test_denied`(); 899SELECT `F_TEST_DENIED`(); 900 901CONNECTION default; 902DISCONNECT con_tester; 903DISCONNECT con_tester_denied; 904DROP TABLE `TestTab`; 905DROP FUNCTION `f_Test`; 906DROP FUNCTION `f_Test_denied`; 907 908USE test; 909DROP USER 'tester'; 910DROP USER 'Tester'; 911DROP DATABASE B48872; 912 913--echo End of 5.0 tests. 914 915 916--echo # 917--echo # Test for bug#57061 "User without privilege on routine can discover 918--echo # its existence." 919--echo # 920--disable_warnings 921drop database if exists mysqltest_db; 922--enable_warnings 923create database mysqltest_db; 924--echo # Create user with no privileges on mysqltest_db database. 925create user bug57061_user@localhost; 926create function mysqltest_db.f1() returns int return 0; 927create procedure mysqltest_db.p1() begin end; 928connect (conn1, localhost, bug57061_user,,); 929--echo # Attempt to drop routine on which user doesn't have privileges 930--echo # should result in the same 'access denied' type of error whether 931--echo # routine exists or not. 932--error ER_PROCACCESS_DENIED_ERROR 933drop function if exists mysqltest_db.f_does_not_exist; 934--error ER_PROCACCESS_DENIED_ERROR 935drop procedure if exists mysqltest_db.p_does_not_exist; 936--error ER_PROCACCESS_DENIED_ERROR 937drop function if exists mysqltest_db.f1; 938--error ER_PROCACCESS_DENIED_ERROR 939drop procedure if exists mysqltest_db.p1; 940connection default; 941disconnect conn1; 942drop user bug57061_user@localhost; 943drop database mysqltest_db; 944 945 946--echo # 947--echo # Bug#11882603 SELECT_ACL ON ANY COLUMN IN MYSQL.PROC ALLOWS TO SEE 948--echo # DEFINITION OF ANY ROUTINE. 949--echo # 950 951--disable_warnings 952DROP DATABASE IF EXISTS db1; 953--enable_warnings 954 955CREATE DATABASE db1; 956CREATE PROCEDURE db1.p1() SELECT 1; 957CREATE USER user2@localhost IDENTIFIED BY ''; 958GRANT SELECT(db) ON mysql.proc TO user2@localhost; 959 960connect (con2, localhost, user2); 961--echo # The statement below before disclosed info from body_utf8 column. 962--error ER_SP_DOES_NOT_EXIST 963SHOW CREATE PROCEDURE db1.p1; 964 965--echo # Check that SHOW works with SELECT grant on whole table 966connection default; 967GRANT SELECT ON mysql.proc TO user2@localhost; 968 969connection con2; 970--echo # This should work 971SHOW CREATE PROCEDURE db1.p1; 972 973connection default; 974disconnect con2; 975DROP USER user2@localhost; 976DROP DATABASE db1; 977 978# 979# Bug#27407480: AUTOMATIC_SP_PRIVILEGES REQUIRES NEED THE INSERT PRIVILEGES FOR MYSQL.USER TABLE 980# 981create user foo@local_ost; 982create user foo@`local\_ost` identified via mysql_old_password using '0123456789ABCDEF'; 983create database foodb; 984grant create routine on foodb.* to foo@local_ost; 985connect con1,localhost,foo; 986select user(), current_user(); 987show grants; 988create procedure fooproc() select 'i am fooproc'; 989show grants; 990disconnect con1; 991connection default; 992drop user foo@local_ost; 993drop user foo@`local\_ost`; 994drop procedure fooproc; 995drop database foodb; 996 997--echo # 998--echo # Test for bug#12602983 - User without privilege on routine can discover 999--echo # its existence by executing "select non_existing_func();" or by 1000--echo # "call non_existing_proc()"; 1001--echo # 1002--disable_warnings 1003drop database if exists mysqltest_db; 1004--enable_warnings 1005create database mysqltest_db; 1006create function mysqltest_db.f1() returns int return 0; 1007create procedure mysqltest_db.p1() begin end; 1008 1009--echo # Create user with no privileges on mysqltest_db database. 1010create user bug12602983_user@localhost; 1011 1012connect (conn1, localhost, bug12602983_user,,); 1013 1014--echo # Attempt to execute routine on which user doesn't have privileges 1015--echo # should result in the same 'access denied' error whether 1016--echo # routine exists or not. 1017--error ER_PROCACCESS_DENIED_ERROR 1018select mysqltest_db.f_does_not_exist(); 1019--error ER_PROCACCESS_DENIED_ERROR 1020call mysqltest_db.p_does_not_exist(); 1021 1022--error ER_PROCACCESS_DENIED_ERROR 1023select mysqltest_db.f1(); 1024--error ER_PROCACCESS_DENIED_ERROR 1025call mysqltest_db.p1(); 1026 1027--error ER_PROCACCESS_DENIED_ERROR 1028create view bug12602983_v1 as select mysqltest_db.f_does_not_exist(); 1029--error ER_PROCACCESS_DENIED_ERROR 1030create view bug12602983_v1 as select mysqltest_db.f1(); 1031 1032connection default; 1033disconnect conn1; 1034drop user bug12602983_user@localhost; 1035drop database mysqltest_db; 1036 1037# Wait till all disconnects are completed 1038--source include/wait_until_count_sessions.inc 1039 1040create user u1@localhost; 1041grant all privileges on *.* to u1@localhost with grant option; 1042connect u1, localhost, u1; 1043set password=password('foobar'); 1044create procedure sp1() select 1; 1045show grants; 1046grant execute on procedure sp1 to current_user() identified by 'barfoo'; 1047show grants; 1048drop procedure sp1; 1049disconnect u1; 1050connection default; 1051drop user u1@localhost; 1052 1053--echo # 1054--echo # MDEV-13396 Unexpected "alter routine comand defined" during CREATE OR REPLACE PROCEDURE 1055--echo # 1056 1057CREATE DATABASE u1; 1058DELIMITER $$; 1059CREATE PROCEDURE u1.p1() BEGIN SELECT 1; END; $$ 1060CREATE FUNCTION u1.f1() RETURNS INT BEGIN RETURN 1; END; $$ 1061DELIMITER ;$$ 1062 1063CREATE USER u1@localhost; 1064GRANT CREATE ROUTINE ON u1.* TO u1@localhost; 1065GRANT ALTER ROUTINE ON FUNCTION u1.f1 TO u1@localhost; 1066GRANT ALTER ROUTINE ON PROCEDURE u1.p1 TO u1@localhost; 1067 1068connect (u1, localhost, u1,,); 1069USE u1; 1070DELIMITER $$; 1071CREATE OR REPLACE FUNCTION f1() RETURNS INT BEGIN RETURN 2; END; $$ 1072CREATE OR REPLACE PROCEDURE p1() BEGIN SELECT 1; END; $$ 1073DELIMITER ;$$ 1074 1075disconnect u1; 1076connection default; 1077DROP DATABASE u1; 1078DROP USER u1@localhost; 1079 1080set @@global.character_set_server=@save_character_set_server; 1081 1082 1083--echo # 1084--echo # Start of 10.5 tests 1085--echo # 1086 1087--echo # 1088--echo # MDEV-20366 Server crashes in get_current_user upon SET PASSWORD via SP 1089--echo # 1090 1091# Testing without the user 1092CREATE PROCEDURE p1() SET PASSWORD FOR foo@localhost=PASSWORD('x'); 1093--error ER_PASSWORD_NO_MATCH 1094CALL p1(); 1095DROP PROCEDURE p1; 1096 1097# Testing with the user 1098CREATE USER foo@localhost; 1099CREATE PROCEDURE p1() SET PASSWORD FOR foo@localhost=PASSWORD('x'); 1100CALL p1(); 1101DROP PROCEDURE p1; 1102DROP USER foo@localhost; 1103 1104 1105--echo # 1106--echo # End of 10.5 tests 1107--echo # 1108