summaryrefslogtreecommitdiff
path: root/mysql-test/r/sp-security.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/sp-security.result')
-rw-r--r--mysql-test/r/sp-security.result151
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');
View Lorry Controller Status