diff options
Diffstat (limited to 'mysql-test/r/sp-security.result')
-rw-r--r-- | mysql-test/r/sp-security.result | 151 |
1 files changed, 117 insertions, 34 deletions
diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result index 276da41a0fe..22172bf18fc 100644 --- a/mysql-test/r/sp-security.result +++ b/mysql-test/r/sp-security.result @@ -1,3 +1,5 @@ +connect con1root,localhost,root,,; +connection con1root; use test; create user user1@localhost; grant usage on *.* to user1@localhost; @@ -39,6 +41,9 @@ 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() @@ -53,6 +58,7 @@ 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() @@ -67,6 +73,7 @@ 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 @@ -92,14 +99,17 @@ 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; @@ -112,10 +122,13 @@ 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' @@ -128,8 +141,11 @@ 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; @@ -143,9 +159,13 @@ 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 @@ -182,26 +202,34 @@ 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 @@ -216,10 +244,15 @@ 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'; @@ -227,6 +260,8 @@ 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// @@ -236,16 +271,25 @@ 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() @@ -259,15 +303,19 @@ 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; @@ -278,10 +326,17 @@ 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; @@ -292,10 +347,13 @@ 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; @@ -305,16 +363,23 @@ 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; @@ -331,6 +396,7 @@ 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 @@ -340,10 +406,11 @@ 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: root +connection con1root; DROP DATABASE IF EXISTS mysqltest; CREATE DATABASE mysqltest; CREATE USER mysqltest_1@localhost; @@ -351,20 +418,19 @@ 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; - ----> connection: mysqltest_2_con +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 +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 +connection mysqltest_2_con; use mysqltest; CREATE DEFINER='a @ b @ c'@localhost PROCEDURE wl2897_p3() SELECT 3; Warnings: @@ -372,8 +438,7 @@ Note 1449 The user specified as a definer ('a @ b @ c'@'localhost') does not exi 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 +connection con1root; USE mysqltest; SHOW CREATE PROCEDURE wl2897_p1; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation @@ -394,16 +459,18 @@ RETURN 3 latin1 latin1_swedish_ci latin1_swedish_ci DROP USER mysqltest_1@localhost; DROP USER mysqltest_2@localhost; DROP DATABASE mysqltest; - ----> connection: root +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; - ----> connection: mysqltest_1_con +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; @@ -415,8 +482,7 @@ CALL bug13198_p1(); SELECT bug13198_f1(); bug13198_f1() 1 - ----> connection: mysqltest_2_con +connection mysqltest_2_con; USE mysqltest; CALL bug13198_p1(); 1 @@ -424,18 +490,17 @@ CALL bug13198_p1(); SELECT bug13198_f1(); bug13198_f1() 1 - ----> connection: root +connection con1root; +disconnect mysqltest_1_con; DROP USER mysqltest_1@localhost; - ----> connection: mysqltest_2_con +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: root +connection con1root; +disconnect mysqltest_2_con; DROP USER mysqltest_2@localhost; DROP DATABASE mysqltest; GRANT USAGE ON *.* TO user19857@localhost IDENTIFIED BY 'meow'; @@ -444,8 +509,8 @@ user19857@localhost; SELECT Host,User,Password FROM mysql.user WHERE User='user19857'; Host User Password localhost user19857 *82DC221D557298F6CE9961037DB1C90604792F5C - ----> connection: mysqltest_2_con +connect mysqltest_2_con,localhost,user19857,meow,test; +connection mysqltest_2_con; USE test; CREATE PROCEDURE sp19857() DETERMINISTIC BEGIN @@ -462,13 +527,18 @@ 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: root +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; @@ -493,6 +563,7 @@ 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(); @@ -515,6 +586,8 @@ ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 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; @@ -543,6 +616,9 @@ 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 @@ -559,6 +635,7 @@ f_Test() SELECT F_TEST(); F_TEST() 123 +connection con_tester_denied; SELECT * FROM TestTab; SELECT `f_Test`(); SELECT `F_TEST`(); @@ -570,6 +647,9 @@ SELECT `f_Test_denied`(); 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`; @@ -588,7 +668,7 @@ create database mysqltest_db; create user bug57061_user@localhost; create function mysqltest_db.f1() returns int return 0; create procedure mysqltest_db.p1() begin end; -# Connect as user 'bug57061_user@localhost' +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. @@ -600,7 +680,8 @@ 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'. +connection default; +disconnect conn1; drop user bug57061_user@localhost; drop database mysqltest_db; # @@ -612,20 +693,21 @@ CREATE DATABASE db1; CREATE PROCEDURE db1.p1() SELECT 1; CREATE USER user2@localhost IDENTIFIED BY ''; GRANT SELECT(db) ON mysql.proc TO user2@localhost; -# Connection con2 as user2 +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 +connection default; GRANT SELECT ON mysql.proc TO user2@localhost; -# Connection con2 +connection con2; # This should work SHOW CREATE PROCEDURE db1.p1; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation p1 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci -# Connection default +connection default; +disconnect con2; DROP USER user2@localhost; DROP DATABASE db1; # @@ -639,7 +721,7 @@ 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 as 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. @@ -655,7 +737,8 @@ 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'. +connection default; +disconnect conn1; drop user bug12602983_user@localhost; drop database mysqltest_db; set password=password('foobar'); |