2 # Testing SQL SECURITY of stored procedures
5 # Can't test with embedded server that doesn't support grants
6 -- source include/not_embedded.inc
8 # Save the initial number of concurrent sessions
9 --source include/count_sessions.inc
11 connect (con1root,localhost,root,,);
16 # Create user user1 with no particular access rights
17 grant usage on *.* to user1@localhost;
21 drop table if exists t1;
22 drop database if exists db1_secret;
24 # Create our secret database
25 create database db1_secret;
27 # Can create a procedure in other db
28 create procedure db1_secret.dummy() begin end;
29 drop procedure db1_secret.dummy;
33 create table t1 ( u varchar(64), i int );
34 insert into t1 values('test', 0);
36 # A test procedure and function
37 create procedure stamp(i int)
38 insert into db1_secret.t1 values (user(), i);
39 --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
40 show procedure status like 'stamp';
43 create function db() returns varchar(64)
45 declare v varchar(64);
47 select u into v from t1 limit 1;
52 --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
53 show function status like 'db';
60 grant execute on procedure db1_secret.stamp to user1@'%';
61 grant execute on function db1_secret.db to user1@'%';
62 grant execute on procedure db1_secret.stamp to ''@'%';
63 grant execute on function db1_secret.db to ''@'%';
65 connect (con2user1,localhost,user1,,);
66 connect (con3anon,localhost,anon,,);
75 call db1_secret.stamp(2);
76 select db1_secret.db();
79 --error ER_TABLEACCESS_DENIED_ERROR
80 select * from db1_secret.t1;
83 --error ER_DBACCESS_DENIED_ERROR
84 create procedure db1_secret.dummy() begin end;
85 --error ER_SP_DOES_NOT_EXIST
86 drop procedure db1_secret.dummy;
87 --error ER_PROCACCESS_DENIED_ERROR
88 drop procedure db1_secret.stamp;
89 --error ER_PROCACCESS_DENIED_ERROR
90 drop function db1_secret.db;
99 call db1_secret.stamp(3);
100 select db1_secret.db();
103 --error ER_TABLEACCESS_DENIED_ERROR
104 select * from db1_secret.t1;
107 --error ER_DBACCESS_DENIED_ERROR
108 create procedure db1_secret.dummy() begin end;
109 --error ER_SP_DOES_NOT_EXIST
110 drop procedure db1_secret.dummy;
111 --error ER_PROCACCESS_DENIED_ERROR
112 drop procedure db1_secret.stamp;
113 --error ER_PROCACCESS_DENIED_ERROR
114 drop function db1_secret.db;
124 # Change to invoker's rights
126 alter procedure stamp sql security invoker;
127 --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
128 show procedure status like 'stamp';
130 alter function db sql security invoker;
131 --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
132 show function status like 'db';
142 connection con2user1;
144 # This should not work
145 --error ER_TABLEACCESS_DENIED_ERROR
146 call db1_secret.stamp(5);
147 --error ER_TABLEACCESS_DENIED_ERROR
148 select db1_secret.db();
155 # This should not work
156 --error ER_TABLEACCESS_DENIED_ERROR
157 call db1_secret.stamp(6);
158 --error ER_TABLEACCESS_DENIED_ERROR
159 select db1_secret.db();
162 # Bug#2777 Stored procedure doesn't observe definer's rights
167 drop database if exists db2;
173 create table t2 (s1 int);
174 insert into t2 values (0);
176 grant usage on db2.* to user1@localhost;
177 grant select on db2.* to user1@localhost;
178 grant usage on db2.* to user2@localhost;
179 grant select,insert,update,delete,create routine on db2.* to user2@localhost;
180 grant create routine on db2.* to user1@localhost;
183 connection con2user1;
186 create procedure p () insert into t2 values (1);
188 # Check that this doesn't work.
189 --error ER_TABLEACCESS_DENIED_ERROR
192 connect (con4user2,localhost,user2,,);
194 connection con4user2;
197 # This should not work, since p is executed with definer's (user1's) rights.
198 --error ER_PROCACCESS_DENIED_ERROR
202 create procedure q () insert into t2 values (2);
208 grant usage on procedure db2.q to user2@localhost with grant option;
210 connection con4user2;
211 grant execute on procedure db2.q to user1@localhost;
213 connection con2user1;
221 # Bug#6030 Stored procedure has no appropriate DROP privilege
222 # (or ALTER for that matter)
224 # still connection con2user1 in db2
227 alter procedure p modifies sql data;
230 # This should NOT work
231 --error ER_PROCACCESS_DENIED_ERROR
232 alter procedure q modifies sql data;
233 --error ER_PROCACCESS_DENIED_ERROR
238 # But root always can
239 alter procedure q modifies sql data;
244 #Still connection con1root;
245 disconnect con2user1;
247 disconnect con4user2;
249 select type,db,name from mysql.proc where db like 'db%';
250 drop database db1_secret;
252 # Make sure the routines are gone
253 select type,db,name from mysql.proc where db like 'db%';
254 # Get rid of the users
255 delete from mysql.user where user='user1' or user='user2';
256 delete from mysql.user where user='' and host='%';
257 # And any routine privileges
258 delete from mysql.procs_priv where user='user1' or user='user2';
259 # Delete the grants to user ''@'%' that was created above
260 delete from mysql.procs_priv where user='' and host='%';
261 delete from mysql.db where user='user2';
264 # Test the new security acls
266 grant usage on *.* to usera@localhost;
267 grant usage on *.* to userb@localhost;
268 grant usage on *.* to userc@localhost;
269 create database sptest;
270 create table t1 ( u varchar(64), i int );
271 create procedure sptest.p1(i int) insert into test.t1 values (user(), i);
272 grant insert on t1 to usera@localhost;
273 grant execute on procedure sptest.p1 to usera@localhost;
274 show grants for usera@localhost;
275 grant execute on procedure sptest.p1 to userc@localhost with grant option;
276 show grants for userc@localhost;
278 connect (con2usera,localhost,usera,,);
279 connect (con3userb,localhost,userb,,);
280 connect (con4userc,localhost,userc,,);
282 connection con2usera;
284 --error ER_PROCACCESS_DENIED_ERROR
285 grant execute on procedure sptest.p1 to userb@localhost;
286 --error ER_PROCACCESS_DENIED_ERROR
287 drop procedure sptest.p1;
289 connection con3userb;
290 --error ER_PROCACCESS_DENIED_ERROR
292 --error ER_PROCACCESS_DENIED_ERROR
293 grant execute on procedure sptest.p1 to userb@localhost;
294 --error ER_PROCACCESS_DENIED_ERROR
295 drop procedure sptest.p1;
297 connection con4userc;
299 grant execute on procedure sptest.p1 to userb@localhost;
300 --error ER_PROCACCESS_DENIED_ERROR
301 drop procedure sptest.p1;
303 connection con3userb;
305 --error ER_PROCACCESS_DENIED_ERROR
306 grant execute on procedure sptest.p1 to userb@localhost;
307 --error ER_PROCACCESS_DENIED_ERROR
308 drop procedure sptest.p1;
313 grant all privileges on procedure sptest.p1 to userc@localhost;
314 show grants for userc@localhost;
315 show grants for userb@localhost;
317 connection con4userc;
318 revoke all privileges on procedure sptest.p1 from userb@localhost;
321 show grants for userb@localhost;
324 disconnect con4userc;
325 disconnect con3userb;
326 disconnect con2usera;
328 drop database sptest;
329 delete from mysql.user where user='usera' or user='userb' or user='userc';
330 delete from mysql.procs_priv where user='usera' or user='userb' or user='userc';
331 delete from mysql.tables_priv where user='usera';
336 # Bug#9503 reseting correct parameters of thread after error in SP function
338 connect (root,localhost,root,,test);
342 drop function if exists bug_9503;
345 create database mysqltest//
347 create table t1 (s1 int)//
348 grant select on t1 to user1@localhost//
349 create function bug_9503 () returns int sql security invoker begin declare v int;
350 select min(s1) into v from t1; return v; end//
353 connect (user1,localhost,user1,,test);
356 -- error ER_PROCACCESS_DENIED_ERROR
360 grant execute on function bug_9503 to user1@localhost;
368 REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost;
369 drop function bug_9503;
371 drop database mysqltest;
376 # correct value from current_user() in function run from "security definer"
377 # (Bug#7291 Stored procedures: wrong CURRENT_USER value)
382 select current_user();
384 create procedure bug7291_0 () sql security invoker select current_user(), user();
385 create procedure bug7291_1 () sql security definer call bug7291_0();
386 create procedure bug7291_2 () sql security invoker call bug7291_0();
387 grant execute on procedure bug7291_0 to user1@localhost;
388 grant execute on procedure bug7291_1 to user1@localhost;
389 grant execute on procedure bug7291_2 to user1@localhost;
391 connect (user1,localhost,user1,,);
398 drop procedure bug7291_1;
399 drop procedure bug7291_2;
400 drop procedure bug7291_0;
402 REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost;
403 drop user user1@localhost;
406 # Bug#12318 Wrong error message when accessing an inaccessible stored
407 # procedure in another database when the current database is
408 # information_schema.
412 drop database if exists mysqltest_1;
415 create database mysqltest_1;
417 create procedure mysqltest_1.p1()
423 grant usage on *.* to mysqltest_1@localhost;
425 connect (n1,localhost,mysqltest_1,,information_schema,$MASTER_MYPORT,$MASTER_MYSOCK);
427 --error ER_PROCACCESS_DENIED_ERROR
428 call mysqltest_1.p1();
430 # Test also without a current database
431 connect (n2,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK);
433 --error ER_PROCACCESS_DENIED_ERROR
434 call mysqltest_1.p1();
439 drop procedure mysqltest_1.p1;
440 drop database mysqltest_1;
442 revoke usage on *.* from mysqltest_1@localhost;
443 drop user mysqltest_1@localhost;
446 # Bug#12812 create view calling a function works without execute right
450 drop function if exists bug12812|
452 create function bug12812() returns char(2)
456 create user user_bug12812@localhost IDENTIFIED BY 'ABC'|
457 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
458 connect (test_user_12812,localhost,user_bug12812,ABC,test)|
459 --error ER_PROCACCESS_DENIED_ERROR
460 SELECT test.bug12812()|
461 --error ER_PROCACCESS_DENIED_ERROR
462 CREATE VIEW v1 AS SELECT test.bug12812()|
465 disconnect test_user_12812|
466 DROP USER user_bug12812@localhost|
467 drop function bug12812|
472 # Bug#14834 Server denies to execute Stored Procedure
474 # The problem here was with '_' in the database name.
476 create database db_bug14834;
478 create user user1_bug14834@localhost identified by '';
479 # The exact name of the database (no wildcard)
480 grant all on `db\_bug14834`.* to user1_bug14834@localhost;
482 create user user2_bug14834@localhost identified by '';
483 # The exact name of the database (no wildcard)
484 grant all on `db\_bug14834`.* to user2_bug14834@localhost;
486 create user user3_bug14834@localhost identified by '';
487 # Wildcards in the database name
488 grant all on `db__ug14834`.* to user3_bug14834@localhost;
490 connect (user1_bug14834,localhost,user1_bug14834,,db_bug14834);
491 # Create the procedure and check that we can call it
492 create procedure p_bug14834() select user(), current_user();
495 connect (user2_bug14834,localhost,user2_bug14834,,db_bug14834);
496 # This didn't work before
499 connect (user3_bug14834,localhost,user3_bug14834,,db_bug14834);
505 disconnect user1_bug14834;
506 disconnect user2_bug14834;
507 disconnect user3_bug14834;
508 drop user user1_bug14834@localhost;
509 drop user user2_bug14834@localhost;
510 drop user user3_bug14834@localhost;
511 drop database db_bug14834;
515 # Bug#14533 'desc tbl' in stored procedure causes error
516 # ER_TABLEACCESS_DENIED_ERROR
518 create database db_bug14533;
520 create table t1 (id int);
521 create user user_bug14533@localhost identified by '';
523 create procedure bug14533_1()
527 create procedure bug14533_2()
529 select * from db_bug14533.t1;
531 grant execute on procedure db_bug14533.bug14533_1 to user_bug14533@localhost;
532 grant execute on procedure db_bug14533.bug14533_2 to user_bug14533@localhost;
534 connect (user_bug14533,localhost,user_bug14533,,test);
537 call db_bug14533.bug14533_1();
538 call db_bug14533.bug14533_2();
540 # For reference, these should not work
541 --error ER_TABLEACCESS_DENIED_ERROR
543 --error ER_TABLEACCESS_DENIED_ERROR
544 select * from db_bug14533.t1;
548 disconnect user_bug14533;
549 drop user user_bug14533@localhost;
550 drop database db_bug14533;
554 # WL#2897 Complete definer support in the stored routines.
556 # The following cases are tested:
557 # 1. check that if DEFINER-clause is not explicitly specified, stored routines
558 # are created with CURRENT_USER privileges;
559 # 2. check that if DEFINER-clause specifies non-current user, SUPER privilege
560 # is required to create a stored routine;
561 # 3. check that if DEFINER-clause specifies non-existent user, a warning is
563 # 4. check that SHOW CREATE PROCEDURE | FUNCTION works correctly;
565 # The following cases are tested in other test suites:
566 # - check that mysqldump dumps new attribute correctly;
567 # - check that slave replicates CREATE-statements with explicitly specified
571 # Setup the environment.
574 --echo ---> connection: root
575 --connection con1root
578 DROP DATABASE IF EXISTS mysqltest;
581 CREATE DATABASE mysqltest;
583 CREATE USER mysqltest_1@localhost;
584 GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost;
586 CREATE USER mysqltest_2@localhost;
587 GRANT SUPER ON *.* TO mysqltest_2@localhost;
588 GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost;
590 --connect (mysqltest_2_con,localhost,mysqltest_2,,mysqltest)
591 --connect (mysqltest_1_con,localhost,mysqltest_1,,mysqltest)
596 --echo ---> connection: mysqltest_2_con
597 --connection mysqltest_2_con
601 CREATE PROCEDURE wl2897_p1() SELECT 1;
603 CREATE FUNCTION wl2897_f1() RETURNS INT RETURN 1;
608 --echo ---> connection: mysqltest_1_con
609 --connection mysqltest_1_con
613 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
614 CREATE DEFINER=root@localhost PROCEDURE wl2897_p2() SELECT 2;
616 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
617 CREATE DEFINER=root@localhost FUNCTION wl2897_f2() RETURNS INT RETURN 2;
622 --echo ---> connection: mysqltest_2_con
623 --connection mysqltest_2_con
627 CREATE DEFINER='a @ b @ c'@localhost PROCEDURE wl2897_p3() SELECT 3;
629 CREATE DEFINER='a @ b @ c'@localhost FUNCTION wl2897_f3() RETURNS INT RETURN 3;
634 --echo ---> connection: con1root
635 --connection con1root
639 SHOW CREATE PROCEDURE wl2897_p1;
640 SHOW CREATE PROCEDURE wl2897_p3;
642 SHOW CREATE FUNCTION wl2897_f1;
643 SHOW CREATE FUNCTION wl2897_f3;
647 DROP USER mysqltest_1@localhost;
648 DROP USER mysqltest_2@localhost;
650 DROP DATABASE mysqltest;
652 --disconnect mysqltest_1_con
653 --disconnect mysqltest_2_con
657 # Bug#13198 SP executes if definer does not exist
660 # Prepare environment.
663 --echo ---> connection: root
664 --connection con1root
667 DROP DATABASE IF EXISTS mysqltest;
670 CREATE DATABASE mysqltest;
672 CREATE USER mysqltest_1@localhost;
673 GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost;
675 CREATE USER mysqltest_2@localhost;
676 GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost;
678 --connect (mysqltest_1_con,localhost,mysqltest_1,,mysqltest)
679 --connect (mysqltest_2_con,localhost,mysqltest_2,,mysqltest)
681 # Create a procedure/function under u1.
684 --echo ---> connection: mysqltest_1_con
685 --connection mysqltest_1_con
689 CREATE PROCEDURE bug13198_p1()
692 CREATE FUNCTION bug13198_f1() RETURNS INT
697 SELECT bug13198_f1();
699 # Check that u2 can call the procedure/function.
702 --echo ---> connection: mysqltest_2_con
703 --connection mysqltest_2_con
709 SELECT bug13198_f1();
711 # Drop user u1 (definer of the object);
714 --echo ---> connection: root
715 --connection con1root
717 --disconnect mysqltest_1_con
719 DROP USER mysqltest_1@localhost;
721 # Check that u2 can not call the procedure/function.
724 --echo ---> connection: mysqltest_2_con
725 --connection mysqltest_2_con
729 --error ER_NO_SUCH_USER
732 --error ER_NO_SUCH_USER
733 SELECT bug13198_f1();
738 --echo ---> connection: root
739 --connection con1root
741 --disconnect mysqltest_2_con
743 DROP USER mysqltest_2@localhost;
745 DROP DATABASE mysqltest;
748 # Bug#19857 When a user with CREATE ROUTINE priv creates a routine,
749 # it results in NULL p/w
752 # Can't test with embedded server that doesn't support grants
754 GRANT USAGE ON *.* TO user19857@localhost IDENTIFIED BY 'meow';
755 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ROUTINE, ALTER ROUTINE ON test.* TO
757 SELECT Host,User,Password FROM mysql.user WHERE User='user19857';
759 --connect (mysqltest_2_con,localhost,user19857,meow,test)
761 --echo ---> connection: mysqltest_2_con
762 --connection mysqltest_2_con
767 CREATE PROCEDURE sp19857() DETERMINISTIC
775 SHOW CREATE PROCEDURE test.sp19857;
777 --disconnect mysqltest_2_con
778 --connect (mysqltest_2_con,localhost,user19857,meow,test)
779 --connection mysqltest_2_con
781 DROP PROCEDURE IF EXISTS test.sp19857;
784 --echo ---> connection: root
785 --connection con1root
787 --disconnect mysqltest_2_con
789 SELECT Host,User,Password FROM mysql.user WHERE User='user19857';
791 DROP USER user19857@localhost;
793 --disconnect con1root
798 # Bug#18630 Arguments of suid routine calculated in wrong security context
800 # Arguments of suid routines were calculated in definer's security
801 # context instead of caller's context thus creating security hole.
804 DROP TABLE IF EXISTS t1;
805 DROP VIEW IF EXISTS v1;
806 DROP FUNCTION IF EXISTS f_suid;
807 DROP PROCEDURE IF EXISTS p_suid;
808 DROP FUNCTION IF EXISTS f_evil;
810 DELETE FROM mysql.user WHERE user LIKE 'mysqltest\_%';
811 DELETE FROM mysql.db WHERE user LIKE 'mysqltest\_%';
812 DELETE FROM mysql.tables_priv WHERE user LIKE 'mysqltest\_%';
813 DELETE FROM mysql.columns_priv WHERE user LIKE 'mysqltest\_%';
816 CREATE TABLE t1 (i INT);
817 CREATE FUNCTION f_suid(i INT) RETURNS INT SQL SECURITY DEFINER RETURN 0;
818 CREATE PROCEDURE p_suid(IN i INT) SQL SECURITY DEFINER SET @c:= 0;
820 CREATE USER mysqltest_u1@localhost;
821 # Thanks to this grant statement privileges of anonymous users on
822 # 'test' database are not applicable for mysqltest_u1@localhost.
823 GRANT EXECUTE ON test.* TO mysqltest_u1@localhost;
826 CREATE DEFINER=mysqltest_u1@localhost FUNCTION f_evil () RETURNS INT
829 SET @a:= CURRENT_USER();
830 SET @b:= (SELECT COUNT(*) FROM t1);
835 CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT f_evil();
837 connect (conn1, localhost, mysqltest_u1,,);
839 --error ER_TABLEACCESS_DENIED_ERROR
840 SELECT COUNT(*) FROM t1;
842 --error ER_TABLEACCESS_DENIED_ERROR
846 --error ER_TABLEACCESS_DENIED_ERROR
847 SELECT f_suid(f_evil());
850 --error ER_TABLEACCESS_DENIED_ERROR
851 CALL p_suid(f_evil());
854 --error ER_TABLEACCESS_DENIED_ERROR
862 DROP FUNCTION f_evil;
863 DROP USER mysqltest_u1@localhost;
864 DROP PROCEDURE p_suid;
865 DROP FUNCTION f_suid;
869 --echo # Bug #48872 : Privileges for stored functions ignored if function name
870 --echo # is mixed case
873 CREATE DATABASE B48872;
875 CREATE TABLE `TestTab` (id INT);
876 INSERT INTO `TestTab` VALUES (1),(2);
877 CREATE FUNCTION `f_Test`() RETURNS INT RETURN 123;
878 CREATE FUNCTION `f_Test_denied`() RETURNS INT RETURN 123;
879 CREATE USER 'tester';
880 CREATE USER 'Tester';
881 GRANT SELECT ON TABLE `TestTab` TO 'tester';
882 GRANT EXECUTE ON FUNCTION `f_Test` TO 'tester';
883 GRANT EXECUTE ON FUNCTION `f_Test_denied` TO 'Tester';
886 SELECT * FROM TestTab;
888 CONNECT (con_tester,localhost,tester,,B48872);
889 CONNECT (con_tester_denied,localhost,Tester,,B48872);
890 CONNECTION con_tester;
892 SELECT * FROM TestTab;
898 CONNECTION con_tester_denied;
901 --error ER_TABLEACCESS_DENIED_ERROR
902 SELECT * FROM TestTab;
903 --error ER_PROCACCESS_DENIED_ERROR
905 --error ER_PROCACCESS_DENIED_ERROR
907 --error ER_PROCACCESS_DENIED_ERROR
909 --error ER_PROCACCESS_DENIED_ERROR
912 SELECT `f_Test_denied`();
913 SELECT `F_TEST_DENIED`();
916 DISCONNECT con_tester;
917 DISCONNECT con_tester_denied;
918 DROP TABLE `TestTab`;
919 DROP FUNCTION `f_Test`;
920 DROP FUNCTION `f_Test_denied`;
925 DROP DATABASE B48872;
927 --echo End of 5.0 tests.
930 --echo # Bug#11882603 SELECT_ACL ON ANY COLUMN IN MYSQL.PROC ALLOWS TO SEE
931 --echo # DEFINITION OF ANY ROUTINE.
935 DROP DATABASE IF EXISTS db1;
939 CREATE PROCEDURE db1.p1() SELECT 1;
940 CREATE USER user2@localhost IDENTIFIED BY '';
941 GRANT SELECT(db) ON mysql.proc TO user2@localhost;
943 --echo # Connection con2 as user2
944 connect (con2, localhost, user2);
945 --echo # The statement below before disclosed info from body_utf8 column.
946 --error ER_SP_DOES_NOT_EXIST
947 SHOW CREATE PROCEDURE db1.p1;
949 --echo # Check that SHOW works with SELECT grant on whole table
950 --echo # Connection default
952 GRANT SELECT ON mysql.proc TO user2@localhost;
954 --echo # Connection con2
956 --echo # This should work
957 SHOW CREATE PROCEDURE db1.p1;
959 --echo # Connection default
962 DROP USER user2@localhost;
966 # Wait till all disconnects are completed
967 --source include/wait_until_count_sessions.inc