diff options
Diffstat (limited to 'mysql-test/main/sp-security.result')
| -rw-r--r-- | mysql-test/main/sp-security.result | 775 |
1 files changed, 775 insertions, 0 deletions
diff --git a/mysql-test/main/sp-security.result b/mysql-test/main/sp-security.result new file mode 100644 index 00000000000..882072ff7c7 --- /dev/null +++ b/mysql-test/main/sp-security.result @@ -0,0 +1,775 @@ +connect con1root,localhost,root,,; +connection con1root; +use test; +create user user1@localhost; +grant usage on *.* to user1@localhost; +flush privileges; +drop table if exists t1; +drop database if exists db1_secret; +create database db1_secret; +create procedure db1_secret.dummy() begin end; +drop procedure db1_secret.dummy; +use db1_secret; +create table t1 ( u varchar(64), i int ); +insert into t1 values('test', 0); +create procedure stamp(i int) +insert into db1_secret.t1 values (user(), i); +show procedure status like 'stamp'; +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +db1_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 +create function db() returns varchar(64) +begin +declare v varchar(64); +select u into v from t1 limit 1; +return v; +end| +show function status like 'db'; +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +db1_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 +call stamp(1); +select * from t1; +u i +test 0 +root@localhost 1 +select db(); +db() +test +create user user1@'%'; +grant execute on procedure db1_secret.stamp to user1@'%'; +grant execute on function db1_secret.db to user1@'%'; +set sql_mode=''; +grant execute on procedure db1_secret.stamp to ''@'%'; +grant execute on function db1_secret.db to ''@'%'; +set sql_mode=default; +connect con2user1,localhost,user1,,; +connect con3anon,localhost,anon,,; +connection con2user1; +call db1_secret.stamp(2); +select db1_secret.db(); +db1_secret.db() +test +select * from db1_secret.t1; +ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' +create procedure db1_secret.dummy() begin end; +ERROR 42000: Access denied for user 'user1'@'localhost' to database 'db1_secret' +drop procedure db1_secret.dummy; +ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db1_secret.dummy' +drop procedure db1_secret.stamp; +ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db1_secret.stamp' +drop function db1_secret.db; +ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db1_secret.db' +connection con3anon; +call db1_secret.stamp(3); +select db1_secret.db(); +db1_secret.db() +test +select * from db1_secret.t1; +ERROR 42000: SELECT command denied to user ''@'localhost' for table 't1' +create procedure db1_secret.dummy() begin end; +ERROR 42000: Access denied for user ''@'%' to database 'db1_secret' +drop procedure db1_secret.dummy; +ERROR 42000: alter routine command denied to user ''@'%' for routine 'db1_secret.dummy' +drop procedure db1_secret.stamp; +ERROR 42000: alter routine command denied to user ''@'%' for routine 'db1_secret.stamp' +drop function db1_secret.db; +ERROR 42000: alter routine command denied to user ''@'%' for routine 'db1_secret.db' +connection con1root; +select * from t1; +u i +test 0 +root@localhost 1 +user1@localhost 2 +anon@localhost 3 +alter procedure stamp sql security invoker; +show procedure status like 'stamp'; +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +db1_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 +alter function db sql security invoker; +show function status like 'db'; +Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation +db1_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 +call stamp(4); +select * from t1; +u i +test 0 +root@localhost 1 +user1@localhost 2 +anon@localhost 3 +root@localhost 4 +select db(); +db() +test +connection con2user1; +call db1_secret.stamp(5); +ERROR 42000: INSERT command denied to user 'user1'@'localhost' for table 't1' +select db1_secret.db(); +ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' +connection con3anon; +call db1_secret.stamp(6); +ERROR 42000: INSERT command denied to user ''@'localhost' for table 't1' +select db1_secret.db(); +ERROR 42000: SELECT command denied to user ''@'localhost' for table 't1' +connection con1root; +drop database if exists db2; +create database db2; +use db2; +create table t2 (s1 int); +insert into t2 values (0); +grant usage on db2.* to user1@localhost; +grant select on db2.* to user1@localhost; +create user user2@localhost; +grant usage on db2.* to user2@localhost; +grant select,insert,update,delete,create routine on db2.* to user2@localhost; +grant create routine on db2.* to user1@localhost; +flush privileges; +connection con2user1; +use db2; +create procedure p () insert into t2 values (1); +call p(); +ERROR 42000: INSERT command denied to user 'user1'@'localhost' for table 't2' +connect con4user2,localhost,user2,,; +connection con4user2; +use db2; +call p(); +ERROR 42000: execute command denied to user 'user2'@'localhost' for routine 'db2.p' +select * from t2; +s1 +0 +create procedure q () insert into t2 values (2); +call q(); +select * from t2; +s1 +0 +2 +connection con1root; +grant usage on procedure db2.q to user2@localhost with grant option; +connection con4user2; +grant execute on procedure db2.q to user1@localhost; +connection con2user1; +use db2; +call q(); +select * from t2; +s1 +0 +2 +2 +alter procedure p modifies sql data; +drop procedure p; +alter procedure q modifies sql data; +ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db2.q' +drop procedure q; +ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db2.q' +connection con1root; +use db2; +alter procedure q modifies sql data; +drop procedure q; +disconnect con2user1; +disconnect con3anon; +disconnect con4user2; +use test; +select type,db,name from mysql.proc where db like 'db%'; +type db name +FUNCTION db1_secret db +PROCEDURE db1_secret stamp +drop database db1_secret; +drop database db2; +select type,db,name from mysql.proc where db like 'db%'; +type db name +delete from mysql.user where user='user1' or user='user2'; +delete from mysql.user where user='' and host='%'; +delete from mysql.procs_priv where user='user1' or user='user2'; +delete from mysql.procs_priv where user='' and host='%'; +delete from mysql.db where user='user2'; +flush privileges; +create user usera@localhost; +grant usage on *.* to usera@localhost; +create user userb@localhost; +grant usage on *.* to userb@localhost; +create user userc@localhost; +grant usage on *.* to userc@localhost; +create database sptest; +create table t1 ( u varchar(64), i int ); +create procedure sptest.p1(i int) insert into test.t1 values (user(), i); +grant insert on t1 to usera@localhost; +grant execute on procedure sptest.p1 to usera@localhost; +show grants for usera@localhost; +Grants for usera@localhost +GRANT USAGE ON *.* TO 'usera'@'localhost' +GRANT INSERT ON `test`.`t1` TO 'usera'@'localhost' +GRANT EXECUTE ON PROCEDURE `sptest`.`p1` TO 'usera'@'localhost' +grant execute on procedure sptest.p1 to userc@localhost with grant option; +show grants for userc@localhost; +Grants for userc@localhost +GRANT USAGE ON *.* TO 'userc'@'localhost' +GRANT EXECUTE ON PROCEDURE `sptest`.`p1` TO 'userc'@'localhost' WITH GRANT OPTION +connect con2usera,localhost,usera,,; +connect con3userb,localhost,userb,,; +connect con4userc,localhost,userc,,; +connection con2usera; +call sptest.p1(1); +grant execute on procedure sptest.p1 to userb@localhost; +ERROR 42000: grant command denied to user 'usera'@'localhost' for routine 'sptest.p1' +drop procedure sptest.p1; +ERROR 42000: alter routine command denied to user 'usera'@'localhost' for routine 'sptest.p1' +connection con3userb; +call sptest.p1(2); +ERROR 42000: execute command denied to user 'userb'@'localhost' for routine 'sptest.p1' +grant execute on procedure sptest.p1 to userb@localhost; +ERROR 42000: execute command denied to user 'userb'@'localhost' for routine 'sptest.p1' +drop procedure sptest.p1; +ERROR 42000: alter routine command denied to user 'userb'@'localhost' for routine 'sptest.p1' +connection con4userc; +call sptest.p1(3); +grant execute on procedure sptest.p1 to userb@localhost; +drop procedure sptest.p1; +ERROR 42000: alter routine command denied to user 'userc'@'localhost' for routine 'sptest.p1' +connection con3userb; +call sptest.p1(4); +grant execute on procedure sptest.p1 to userb@localhost; +ERROR 42000: grant command denied to user 'userb'@'localhost' for routine 'sptest.p1' +drop procedure sptest.p1; +ERROR 42000: alter routine command denied to user 'userb'@'localhost' for routine 'sptest.p1' +connection con1root; +select * from t1; +u i +usera@localhost 1 +userc@localhost 3 +userb@localhost 4 +grant all privileges on procedure sptest.p1 to userc@localhost; +show grants for userc@localhost; +Grants for userc@localhost +GRANT USAGE ON *.* TO 'userc'@'localhost' +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `sptest`.`p1` TO 'userc'@'localhost' WITH GRANT OPTION +show grants for userb@localhost; +Grants for userb@localhost +GRANT USAGE ON *.* TO 'userb'@'localhost' +GRANT EXECUTE ON PROCEDURE `sptest`.`p1` TO 'userb'@'localhost' +connection con4userc; +revoke all privileges on procedure sptest.p1 from userb@localhost; +connection con1root; +show grants for userb@localhost; +Grants for userb@localhost +GRANT USAGE ON *.* TO 'userb'@'localhost' +disconnect con4userc; +disconnect con3userb; +disconnect con2usera; +use test; +drop database sptest; +delete from mysql.user where user='usera' or user='userb' or user='userc'; +delete from mysql.procs_priv where user='usera' or user='userb' or user='userc'; +delete from mysql.tables_priv where user='usera'; +flush privileges; +drop table t1; +connect root,localhost,root,,test; +connection root; +drop function if exists bug_9503; +drop user if exists user1@localhost; +create database mysqltest// +use mysqltest// +create table t1 (s1 int)// +create user user1@localhost// +grant select on t1 to user1@localhost// +create function bug_9503 () returns int sql security invoker begin declare v int; +select min(s1) into v from t1; return v; end// +connect user1,localhost,user1,,test; +connection user1; +use mysqltest; +select bug_9503(); +ERROR 42000: execute command denied to user 'user1'@'localhost' for routine 'mysqltest.bug_9503' +connection root; +grant execute on function bug_9503 to user1@localhost; +connection user1; +do 1; +use test; +disconnect user1; +connection root; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost; +drop function bug_9503; +use test; +drop database mysqltest; +connection default; +disconnect root; +connection con1root; +use test; +select current_user(); +current_user() +root@localhost +select user(); +user() +root@localhost +create procedure bug7291_0 () sql security invoker select current_user(), user(); +create procedure bug7291_1 () sql security definer call bug7291_0(); +create procedure bug7291_2 () sql security invoker call bug7291_0(); +grant execute on procedure bug7291_0 to user1@localhost; +grant execute on procedure bug7291_1 to user1@localhost; +grant execute on procedure bug7291_2 to user1@localhost; +connect user1,localhost,user1,,; +connection user1; +call bug7291_2(); +current_user() user() +user1@localhost user1@localhost +call bug7291_1(); +current_user() user() +root@localhost user1@localhost +connection con1root; +drop procedure bug7291_1; +drop procedure bug7291_2; +drop procedure bug7291_0; +disconnect user1; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost; +drop user user1@localhost; +drop database if exists mysqltest_1; +create database mysqltest_1; +create procedure mysqltest_1.p1() +begin +select 1 from dual; +end// +create user mysqltest_1@localhost; +grant usage on *.* to mysqltest_1@localhost; +connect n1,localhost,mysqltest_1,,information_schema,$MASTER_MYPORT,$MASTER_MYSOCK; +connection n1; +call mysqltest_1.p1(); +ERROR 42000: execute command denied to user 'mysqltest_1'@'localhost' for routine 'mysqltest_1.p1' +disconnect n1; +connect n2,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK; +connection n2; +call mysqltest_1.p1(); +ERROR 42000: execute command denied to user 'mysqltest_1'@'localhost' for routine 'mysqltest_1.p1' +disconnect n2; +connection default; +drop procedure mysqltest_1.p1; +drop database mysqltest_1; +revoke usage on *.* from mysqltest_1@localhost; +drop user mysqltest_1@localhost; +drop function if exists bug12812| +create function bug12812() returns char(2) +begin +return 'ok'; +end; +create user user_bug12812@localhost IDENTIFIED BY 'ABC'| +connect test_user_12812,localhost,user_bug12812,ABC,test; +SELECT test.bug12812()| +ERROR 42000: execute command denied to user 'user_bug12812'@'localhost' for routine 'test.bug12812' +CREATE VIEW v1 AS SELECT test.bug12812()| +ERROR 42000: execute command denied to user 'user_bug12812'@'localhost' for routine 'test.bug12812' +connection default; +disconnect test_user_12812; +DROP USER user_bug12812@localhost| +drop function bug12812| +create database db_bug14834; +create user user1_bug14834@localhost identified by ''; +grant all on `db\_bug14834`.* to user1_bug14834@localhost; +create user user2_bug14834@localhost identified by ''; +grant all on `db\_bug14834`.* to user2_bug14834@localhost; +create user user3_bug14834@localhost identified by ''; +grant all on `db__ug14834`.* to user3_bug14834@localhost; +connect user1_bug14834,localhost,user1_bug14834,,db_bug14834; +create procedure p_bug14834() select user(), current_user(); +call p_bug14834(); +user() current_user() +user1_bug14834@localhost user1_bug14834@localhost +connect user2_bug14834,localhost,user2_bug14834,,db_bug14834; +call p_bug14834(); +user() current_user() +user2_bug14834@localhost user1_bug14834@localhost +connect user3_bug14834,localhost,user3_bug14834,,db_bug14834; +call p_bug14834(); +user() current_user() +user3_bug14834@localhost user1_bug14834@localhost +connection default; +disconnect user1_bug14834; +disconnect user2_bug14834; +disconnect user3_bug14834; +drop user user1_bug14834@localhost; +drop user user2_bug14834@localhost; +drop user user3_bug14834@localhost; +drop database db_bug14834; +create database db_bug14533; +use db_bug14533; +create table t1 (id int); +create user user_bug14533@localhost identified by ''; +create procedure bug14533_1() +sql security definer +desc db_bug14533.t1; +create procedure bug14533_2() +sql security definer +select * from db_bug14533.t1; +grant execute on procedure db_bug14533.bug14533_1 to user_bug14533@localhost; +grant execute on procedure db_bug14533.bug14533_2 to user_bug14533@localhost; +connect user_bug14533,localhost,user_bug14533,,test; +call db_bug14533.bug14533_1(); +Field Type Null Key Default Extra +id int(11) YES NULL +call db_bug14533.bug14533_2(); +id +desc db_bug14533.t1; +ERROR 42000: SELECT command denied to user 'user_bug14533'@'localhost' for table 't1' +select * from db_bug14533.t1; +ERROR 42000: SELECT command denied to user 'user_bug14533'@'localhost' for table 't1' +connection default; +disconnect user_bug14533; +drop user user_bug14533@localhost; +drop database db_bug14533; +connection con1root; +DROP DATABASE IF EXISTS mysqltest; +CREATE DATABASE mysqltest; +CREATE USER mysqltest_1@localhost; +GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost; +CREATE USER mysqltest_2@localhost; +GRANT SUPER ON *.* TO mysqltest_2@localhost; +GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost; +connect mysqltest_2_con,localhost,mysqltest_2,,mysqltest; +connect mysqltest_1_con,localhost,mysqltest_1,,mysqltest; +connection mysqltest_2_con; +USE mysqltest; +CREATE PROCEDURE wl2897_p1() SELECT 1; +CREATE FUNCTION wl2897_f1() RETURNS INT RETURN 1; +connection mysqltest_1_con; +USE mysqltest; +CREATE DEFINER=root@localhost PROCEDURE wl2897_p2() SELECT 2; +ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation +CREATE DEFINER=root@localhost FUNCTION wl2897_f2() RETURNS INT RETURN 2; +ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation +connection mysqltest_2_con; +use mysqltest; +CREATE DEFINER='a @ b @ c'@localhost PROCEDURE wl2897_p3() SELECT 3; +Warnings: +Note 1449 The user specified as a definer ('a @ b @ c'@'localhost') does not exist +CREATE DEFINER='a @ b @ c'@localhost FUNCTION wl2897_f3() RETURNS INT RETURN 3; +Warnings: +Note 1449 The user specified as a definer ('a @ b @ c'@'localhost') does not exist +connection con1root; +USE mysqltest; +SHOW CREATE PROCEDURE wl2897_p1; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +wl2897_p1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`mysqltest_2`@`localhost` PROCEDURE `wl2897_p1`() +SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE PROCEDURE wl2897_p3; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +wl2897_p3 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`a @ b @ c`@`localhost` PROCEDURE `wl2897_p3`() +SELECT 3 latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE FUNCTION wl2897_f1; +Function sql_mode Create Function character_set_client collation_connection Database Collation +wl2897_f1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`mysqltest_2`@`localhost` FUNCTION `wl2897_f1`() RETURNS int(11) +RETURN 1 latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE FUNCTION wl2897_f3; +Function sql_mode Create Function character_set_client collation_connection Database Collation +wl2897_f3 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`a @ b @ c`@`localhost` FUNCTION `wl2897_f3`() RETURNS int(11) +RETURN 3 latin1 latin1_swedish_ci latin1_swedish_ci +DROP USER mysqltest_1@localhost; +DROP USER mysqltest_2@localhost; +DROP DATABASE mysqltest; +disconnect mysqltest_1_con; +disconnect mysqltest_2_con; +connection con1root; +DROP DATABASE IF EXISTS mysqltest; +CREATE DATABASE mysqltest; +CREATE USER mysqltest_1@localhost; +GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost; +CREATE USER mysqltest_2@localhost; +GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost; +connect mysqltest_1_con,localhost,mysqltest_1,,mysqltest; +connect mysqltest_2_con,localhost,mysqltest_2,,mysqltest; +connection mysqltest_1_con; +USE mysqltest; +CREATE PROCEDURE bug13198_p1() +SELECT 1; +CREATE FUNCTION bug13198_f1() RETURNS INT +RETURN 1; +CALL bug13198_p1(); +1 +1 +SELECT bug13198_f1(); +bug13198_f1() +1 +connection mysqltest_2_con; +USE mysqltest; +CALL bug13198_p1(); +1 +1 +SELECT bug13198_f1(); +bug13198_f1() +1 +connection con1root; +disconnect mysqltest_1_con; +DROP USER mysqltest_1@localhost; +connection mysqltest_2_con; +USE mysqltest; +CALL bug13198_p1(); +ERROR HY000: The user specified as a definer ('mysqltest_1'@'localhost') does not exist +SELECT bug13198_f1(); +ERROR HY000: The user specified as a definer ('mysqltest_1'@'localhost') does not exist +connection con1root; +disconnect mysqltest_2_con; +DROP USER mysqltest_2@localhost; +DROP DATABASE mysqltest; +GRANT USAGE ON *.* TO user19857@localhost IDENTIFIED BY 'meow'; +GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ROUTINE, ALTER ROUTINE ON test.* TO +user19857@localhost; +SELECT Host,User,Password FROM mysql.user WHERE User='user19857'; +Host User Password +localhost user19857 *82DC221D557298F6CE9961037DB1C90604792F5C +connect mysqltest_2_con,localhost,user19857,meow,test; +connection mysqltest_2_con; +USE test; +CREATE PROCEDURE sp19857() DETERMINISTIC +BEGIN +DECLARE a INT; +SET a=1; +SELECT a; +END // +SHOW CREATE PROCEDURE test.sp19857; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +sp19857 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`user19857`@`localhost` PROCEDURE `sp19857`() + DETERMINISTIC +BEGIN +DECLARE a INT; +SET a=1; +SELECT a; +END latin1 latin1_swedish_ci latin1_swedish_ci +disconnect mysqltest_2_con; +connect mysqltest_2_con,localhost,user19857,meow,test; +connection mysqltest_2_con; +DROP PROCEDURE IF EXISTS test.sp19857; +connection con1root; +disconnect mysqltest_2_con; +SELECT Host,User,Password FROM mysql.user WHERE User='user19857'; +Host User Password +localhost user19857 *82DC221D557298F6CE9961037DB1C90604792F5C +DROP USER user19857@localhost; +disconnect con1root; +connection default; +use test; +DROP TABLE IF EXISTS t1; +DROP VIEW IF EXISTS v1; +DROP FUNCTION IF EXISTS f_suid; +DROP PROCEDURE IF EXISTS p_suid; +DROP FUNCTION IF EXISTS f_evil; +DELETE FROM mysql.user WHERE user LIKE 'mysqltest\_%'; +DELETE FROM mysql.db WHERE user LIKE 'mysqltest\_%'; +DELETE FROM mysql.tables_priv WHERE user LIKE 'mysqltest\_%'; +DELETE FROM mysql.columns_priv WHERE user LIKE 'mysqltest\_%'; +FLUSH PRIVILEGES; +CREATE TABLE t1 (i INT); +CREATE FUNCTION f_suid(i INT) RETURNS INT SQL SECURITY DEFINER RETURN 0; +CREATE PROCEDURE p_suid(IN i INT) SQL SECURITY DEFINER SET @c:= 0; +CREATE USER mysqltest_u1@localhost; +GRANT EXECUTE ON test.* TO mysqltest_u1@localhost; +CREATE DEFINER=mysqltest_u1@localhost FUNCTION f_evil () RETURNS INT +SQL SECURITY INVOKER +BEGIN +SET @a:= CURRENT_USER(); +SET @b:= (SELECT COUNT(*) FROM t1); +RETURN @b; +END| +CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT f_evil(); +connect conn1, localhost, mysqltest_u1,,; +SELECT COUNT(*) FROM t1; +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' +SELECT f_evil(); +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' +SELECT @a, @b; +@a @b +mysqltest_u1@localhost NULL +SELECT f_suid(f_evil()); +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' +SELECT @a, @b; +@a @b +mysqltest_u1@localhost NULL +CALL p_suid(f_evil()); +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1' +SELECT @a, @b; +@a @b +mysqltest_u1@localhost NULL +SELECT * FROM v1; +ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'v1' +SELECT @a, @b; +@a @b +mysqltest_u1@localhost NULL +disconnect conn1; +connection default; +DROP VIEW v1; +DROP FUNCTION f_evil; +DROP USER mysqltest_u1@localhost; +DROP PROCEDURE p_suid; +DROP FUNCTION f_suid; +DROP TABLE t1; +# +# Bug #48872 : Privileges for stored functions ignored if function name +# is mixed case +# +CREATE DATABASE B48872; +USE B48872; +CREATE TABLE `TestTab` (id INT); +INSERT INTO `TestTab` VALUES (1),(2); +CREATE FUNCTION `f_Test`() RETURNS INT RETURN 123; +CREATE FUNCTION `f_Test_denied`() RETURNS INT RETURN 123; +CREATE USER 'tester'; +CREATE USER 'Tester'; +GRANT SELECT ON TABLE `TestTab` TO 'tester'; +GRANT EXECUTE ON FUNCTION `f_Test` TO 'tester'; +GRANT EXECUTE ON FUNCTION `f_Test_denied` TO 'Tester'; +SELECT f_Test(); +f_Test() +123 +SELECT * FROM TestTab; +id +1 +2 +CONNECT con_tester,localhost,tester,,B48872; +CONNECT con_tester_denied,localhost,Tester,,B48872; +connection con_tester; +SELECT * FROM TestTab; +id +1 +2 +SELECT `f_Test`(); +`f_Test`() +123 +SELECT `F_TEST`(); +`F_TEST`() +123 +SELECT f_Test(); +f_Test() +123 +SELECT F_TEST(); +F_TEST() +123 +connection con_tester_denied; +SELECT * FROM TestTab; +SELECT `f_Test`(); +SELECT `F_TEST`(); +SELECT f_Test(); +SELECT F_TEST(); +SELECT `f_Test_denied`(); +`f_Test_denied`() +123 +SELECT `F_TEST_DENIED`(); +`F_TEST_DENIED`() +123 +connection default; +disconnect con_tester; +disconnect con_tester_denied; +DROP TABLE `TestTab`; +DROP FUNCTION `f_Test`; +DROP FUNCTION `f_Test_denied`; +USE test; +DROP USER 'tester'; +DROP USER 'Tester'; +DROP DATABASE B48872; +End of 5.0 tests. +# +# Test for bug#57061 "User without privilege on routine can discover +# its existence." +# +drop database if exists mysqltest_db; +create database mysqltest_db; +# Create user with no privileges on mysqltest_db database. +create user bug57061_user@localhost; +create function mysqltest_db.f1() returns int return 0; +create procedure mysqltest_db.p1() begin end; +connect conn1, localhost, bug57061_user,,; +# Attempt to drop routine on which user doesn't have privileges +# should result in the same 'access denied' type of error whether +# routine exists or not. +drop function if exists mysqltest_db.f_does_not_exist; +ERROR 42000: alter routine command denied to user 'bug57061_user'@'localhost' for routine 'mysqltest_db.f_does_not_exist' +drop procedure if exists mysqltest_db.p_does_not_exist; +ERROR 42000: alter routine command denied to user 'bug57061_user'@'localhost' for routine 'mysqltest_db.p_does_not_exist' +drop function if exists mysqltest_db.f1; +ERROR 42000: alter routine command denied to user 'bug57061_user'@'localhost' for routine 'mysqltest_db.f1' +drop procedure if exists mysqltest_db.p1; +ERROR 42000: alter routine command denied to user 'bug57061_user'@'localhost' for routine 'mysqltest_db.p1' +connection default; +disconnect conn1; +drop user bug57061_user@localhost; +drop database mysqltest_db; +# +# Bug#11882603 SELECT_ACL ON ANY COLUMN IN MYSQL.PROC ALLOWS TO SEE +# DEFINITION OF ANY ROUTINE. +# +DROP DATABASE IF EXISTS db1; +CREATE DATABASE db1; +CREATE PROCEDURE db1.p1() SELECT 1; +CREATE USER user2@localhost IDENTIFIED BY ''; +GRANT SELECT(db) ON mysql.proc TO user2@localhost; +connect con2, localhost, user2; +# The statement below before disclosed info from body_utf8 column. +SHOW CREATE PROCEDURE db1.p1; +ERROR 42000: PROCEDURE p1 does not exist +# Check that SHOW works with SELECT grant on whole table +connection default; +GRANT SELECT ON mysql.proc TO user2@localhost; +connection con2; +# This should work +SHOW CREATE PROCEDURE db1.p1; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +p1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() +SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci +connection default; +disconnect con2; +DROP USER user2@localhost; +DROP DATABASE db1; +# +# Test for bug#12602983 - User without privilege on routine can discover +# its existence by executing "select non_existing_func();" or by +# "call non_existing_proc()"; +# +drop database if exists mysqltest_db; +create database mysqltest_db; +create function mysqltest_db.f1() returns int return 0; +create procedure mysqltest_db.p1() begin end; +# Create user with no privileges on mysqltest_db database. +create user bug12602983_user@localhost; +connect conn1, localhost, bug12602983_user,,; +# Attempt to execute routine on which user doesn't have privileges +# should result in the same 'access denied' error whether +# routine exists or not. +select mysqltest_db.f_does_not_exist(); +ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.f_does_not_exist' +call mysqltest_db.p_does_not_exist(); +ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.p_does_not_exist' +select mysqltest_db.f1(); +ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.f1' +call mysqltest_db.p1(); +ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.p1' +create view bug12602983_v1 as select mysqltest_db.f_does_not_exist(); +ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.f_does_not_exist' +create view bug12602983_v1 as select mysqltest_db.f1(); +ERROR 42000: execute command denied to user 'bug12602983_user'@'localhost' for routine 'mysqltest_db.f1' +connection default; +disconnect conn1; +drop user bug12602983_user@localhost; +drop database mysqltest_db; +set password=password('foobar'); +create procedure sp1() select 1; +show grants; +Grants for root@localhost +GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*9B500343BC52E2911172EB52AE5CF4847604C6E5' WITH GRANT OPTION +GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION +grant execute on procedure sp1 to current_user() identified by 'barfoo'; +show grants; +Grants for root@localhost +GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*343915A8181B5728EADBDC73E1F7E6B0C3998483' WITH GRANT OPTION +GRANT EXECUTE ON PROCEDURE `test`.`sp1` TO 'root'@'localhost' +GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION +drop procedure sp1; +set password=''; +# +# MDEV-13396 Unexpected "alter routine comand defined" during CREATE OR REPLACE PROCEDURE +# +CREATE DATABASE u1; +CREATE PROCEDURE u1.p1() BEGIN SELECT 1; END; $$ +CREATE FUNCTION u1.f1() RETURNS INT BEGIN RETURN 1; END; $$ +CREATE USER u1@localhost; +GRANT CREATE ROUTINE ON u1.* TO u1@localhost; +GRANT ALTER ROUTINE ON FUNCTION u1.f1 TO u1@localhost; +GRANT ALTER ROUTINE ON PROCEDURE u1.p1 TO u1@localhost; +connect u1, localhost, u1,,; +USE u1; +CREATE OR REPLACE FUNCTION f1() RETURNS INT BEGIN RETURN 2; END; $$ +CREATE OR REPLACE PROCEDURE p1() BEGIN SELECT 1; END; $$ +disconnect u1; +connection default; +DROP DATABASE u1; +DROP USER u1@localhost; |