EVOLUTION-MANAGER

Edit File: sp-security.result

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; create user foo@local_ost; create user foo@`local\_ost`; update mysql.user set plugin='foobar' where host='local\\_ost'; flush privileges; create database foodb; grant create routine on foodb.* to foo@local_ost; connect con1,localhost,foo; select user(), current_user(); user() current_user() foo@localhost foo@local_ost show grants; Grants for foo@local_ost GRANT USAGE ON *.* TO 'foo'@'local_ost' GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'local_ost' create procedure fooproc() select 'i am fooproc'; show grants; Grants for foo@local_ost GRANT USAGE ON *.* TO 'foo'@'local_ost' GRANT CREATE ROUTINE ON `foodb`.* TO 'foo'@'local_ost' GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`fooproc` TO 'foo'@'local_ost' disconnect con1; connection default; drop user foo@local_ost; drop user foo@`local\_ost`; drop procedure fooproc; drop database foodb; # # 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=''; update mysql.user set plugin=''; # # 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;