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.
929
# Wait till all disconnects are completed
930
--source include/wait_until_count_sessions.inc