diff options
Diffstat (limited to 'mysql-test/r/sp-security.test')
| -rw-r--r-- | mysql-test/r/sp-security.test | 1069 |
1 files changed, 1069 insertions, 0 deletions
diff --git a/mysql-test/r/sp-security.test b/mysql-test/r/sp-security.test new file mode 100644 index 00000000000..53dc4f8c7ac --- /dev/null +++ b/mysql-test/r/sp-security.test @@ -0,0 +1,1069 @@ +# +# Testing SQL SECURITY of stored procedures +# + +# Can't test with embedded server that doesn't support grants +-- source include/not_embedded.inc + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +connect (con1root,localhost,root,,); + +connection con1root; +use test; + +# Create user user1 with no particular access rights +grant usage on *.* to user1@localhost; +flush privileges; + +--disable_warnings +drop table if exists t1; +drop database if exists db1_secret; +--enable_warnings +# Create our secret database +create database db1_secret; + +# Can create a procedure in other db +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); + +# A test procedure and function +create procedure stamp(i int) + insert into db1_secret.t1 values (user(), i); +--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +show procedure status like 'stamp'; + +delimiter |; +create function db() returns varchar(64) +begin + declare v varchar(64); + + select u into v from t1 limit 1; + + return v; +end| +delimiter ;| +--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +show function status like 'db'; + +# root can, of course +call stamp(1); +select * from t1; +select db(); + +grant execute on procedure db1_secret.stamp to user1@'%'; +grant execute on function db1_secret.db to user1@'%'; +grant execute on procedure db1_secret.stamp to ''@'%'; +grant execute on function db1_secret.db to ''@'%'; + +connect (con2user1,localhost,user1,,); +connect (con3anon,localhost,anon,,); + + +# +# User1 can +# +connection con2user1; + +# This should work... +call db1_secret.stamp(2); +select db1_secret.db(); + +# ...but not this +--error ER_TABLEACCESS_DENIED_ERROR +select * from db1_secret.t1; + +# ...and not this +--error ER_DBACCESS_DENIED_ERROR +create procedure db1_secret.dummy() begin end; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure db1_secret.dummy; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure db1_secret.stamp; +--error ER_PROCACCESS_DENIED_ERROR +drop function db1_secret.db; + + +# +# Anonymous can +# +connection con3anon; + +# This should work... +call db1_secret.stamp(3); +select db1_secret.db(); + +# ...but not this +--error ER_TABLEACCESS_DENIED_ERROR +select * from db1_secret.t1; + +# ...and not this +--error ER_DBACCESS_DENIED_ERROR +create procedure db1_secret.dummy() begin end; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure db1_secret.dummy; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure db1_secret.stamp; +--error ER_PROCACCESS_DENIED_ERROR +drop function db1_secret.db; + + +# +# Check it out +# +connection con1root; +select * from t1; + +# +# Change to invoker's rights +# +alter procedure stamp sql security invoker; +--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +show procedure status like 'stamp'; + +alter function db sql security invoker; +--replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' +show function status like 'db'; + +# root still can +call stamp(4); +select * from t1; +select db(); + +# +# User1 cannot +# +connection con2user1; + +# This should not work +--error ER_TABLEACCESS_DENIED_ERROR +call db1_secret.stamp(5); +--error ER_TABLEACCESS_DENIED_ERROR +select db1_secret.db(); + +# +# Anonymous cannot +# +connection con3anon; + +# This should not work +--error ER_TABLEACCESS_DENIED_ERROR +call db1_secret.stamp(6); +--error ER_TABLEACCESS_DENIED_ERROR +select db1_secret.db(); + +# +# Bug#2777 Stored procedure doesn't observe definer's rights +# + +connection con1root; +--disable_warnings +drop database if exists db2; +--enable_warnings +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; +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); + +# Check that this doesn't work. +--error ER_TABLEACCESS_DENIED_ERROR +call p(); + +connect (con4user2,localhost,user2,,); + +connection con4user2; +use db2; + +# This should not work, since p is executed with definer's (user1's) rights. +--error ER_PROCACCESS_DENIED_ERROR +call p(); +select * from t2; + +create procedure q () insert into t2 values (2); + +call q(); +select * from t2; + +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; + +# This should work +call q(); +select * from t2; + +# +# Bug#6030 Stored procedure has no appropriate DROP privilege +# (or ALTER for that matter) + +# still connection con2user1 in db2 + +# This should work: +alter procedure p modifies sql data; +drop procedure p; + +# This should NOT work +--error ER_PROCACCESS_DENIED_ERROR +alter procedure q modifies sql data; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure q; + +connection con1root; +use db2; +# But root always can +alter procedure q modifies sql data; +drop procedure q; + + +# Clean up +#Still connection con1root; +disconnect con2user1; +disconnect con3anon; +disconnect con4user2; +use test; +select type,db,name from mysql.proc where db like 'db%'; +drop database db1_secret; +drop database db2; +# Make sure the routines are gone +select type,db,name from mysql.proc where db like 'db%'; +# Get rid of the users +delete from mysql.user where user='user1' or user='user2'; +delete from mysql.user where user='' and host='%'; +# And any routine privileges +delete from mysql.procs_priv where user='user1' or user='user2'; +# Delete the grants to user ''@'%' that was created above +delete from mysql.procs_priv where user='' and host='%'; +delete from mysql.db where user='user2'; +flush privileges; +# +# Test the new security acls +# +grant usage on *.* to usera@localhost; +grant usage on *.* to userb@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; +grant execute on procedure sptest.p1 to userc@localhost with grant option; +show grants for userc@localhost; + +connect (con2usera,localhost,usera,,); +connect (con3userb,localhost,userb,,); +connect (con4userc,localhost,userc,,); + +connection con2usera; +call sptest.p1(1); +--error ER_PROCACCESS_DENIED_ERROR +grant execute on procedure sptest.p1 to userb@localhost; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure sptest.p1; + +connection con3userb; +--error ER_PROCACCESS_DENIED_ERROR +call sptest.p1(2); +--error ER_PROCACCESS_DENIED_ERROR +grant execute on procedure sptest.p1 to userb@localhost; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure sptest.p1; + +connection con4userc; +call sptest.p1(3); +grant execute on procedure sptest.p1 to userb@localhost; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure sptest.p1; + +connection con3userb; +call sptest.p1(4); +--error ER_PROCACCESS_DENIED_ERROR +grant execute on procedure sptest.p1 to userb@localhost; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure sptest.p1; + +connection con1root; +select * from t1; + +grant all privileges on procedure sptest.p1 to userc@localhost; +show grants for userc@localhost; +show grants for userb@localhost; + +connection con4userc; +revoke all privileges on procedure sptest.p1 from userb@localhost; + +connection con1root; +show grants for userb@localhost; + +#cleanup +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; + +# +# Bug#9503 reseting correct parameters of thread after error in SP function +# +connect (root,localhost,root,,test); +connection root; + +--disable_warnings +drop function if exists bug_9503; +--enable_warnings +delimiter //; +create database mysqltest// +use mysqltest// +create table t1 (s1 int)// +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// +delimiter ;// + +connect (user1,localhost,user1,,test); +connection user1; +use mysqltest; +-- error ER_PROCACCESS_DENIED_ERROR +select 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; + +# +# correct value from current_user() in function run from "security definer" +# (Bug#7291 Stored procedures: wrong CURRENT_USER value) +# +connection con1root; +use test; + +select current_user(); +select user(); +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(); +call bug7291_1(); + +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; + +# +# Bug#12318 Wrong error message when accessing an inaccessible stored +# procedure in another database when the current database is +# information_schema. +# + +--disable_warnings +drop database if exists mysqltest_1; +--enable_warnings + +create database mysqltest_1; +delimiter //; +create procedure mysqltest_1.p1() +begin + select 1 from dual; +end// +delimiter ;// + +grant usage on *.* to mysqltest_1@localhost; + +connect (n1,localhost,mysqltest_1,,information_schema,$MASTER_MYPORT,$MASTER_MYSOCK); +connection n1; +--error ER_PROCACCESS_DENIED_ERROR +call mysqltest_1.p1(); +disconnect n1; +# Test also without a current database +connect (n2,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK); +connection n2; +--error ER_PROCACCESS_DENIED_ERROR +call 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; + +# +# Bug#12812 create view calling a function works without execute right +# on function +delimiter |; +--disable_warnings +drop function if exists bug12812| +--enable_warnings +create function bug12812() returns char(2) +begin + return 'ok'; +end; +create user user_bug12812@localhost IDENTIFIED BY 'ABC'| +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +connect (test_user_12812,localhost,user_bug12812,ABC,test)| +--error ER_PROCACCESS_DENIED_ERROR +SELECT test.bug12812()| +--error ER_PROCACCESS_DENIED_ERROR +CREATE VIEW v1 AS SELECT test.bug12812()| +# Cleanup +connection default| +disconnect test_user_12812| +DROP USER user_bug12812@localhost| +drop function bug12812| +delimiter ;| + + +# +# Bug#14834 Server denies to execute Stored Procedure +# +# The problem here was with '_' in the database name. +# +create database db_bug14834; + +create user user1_bug14834@localhost identified by ''; +# The exact name of the database (no wildcard) +grant all on `db\_bug14834`.* to user1_bug14834@localhost; + +create user user2_bug14834@localhost identified by ''; +# The exact name of the database (no wildcard) +grant all on `db\_bug14834`.* to user2_bug14834@localhost; + +create user user3_bug14834@localhost identified by ''; +# Wildcards in the database name +grant all on `db__ug14834`.* to user3_bug14834@localhost; + +connect (user1_bug14834,localhost,user1_bug14834,,db_bug14834); +# Create the procedure and check that we can call it +create procedure p_bug14834() select user(), current_user(); +call p_bug14834(); + +connect (user2_bug14834,localhost,user2_bug14834,,db_bug14834); +# This didn't work before +call p_bug14834(); + +connect (user3_bug14834,localhost,user3_bug14834,,db_bug14834); +# Should also work +call p_bug14834(); + +# Cleanup +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; + + +# +# Bug#14533 'desc tbl' in stored procedure causes error +# ER_TABLEACCESS_DENIED_ERROR +# +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); + +# These should work +call db_bug14533.bug14533_1(); +call db_bug14533.bug14533_2(); + +# For reference, these should not work +--error ER_TABLEACCESS_DENIED_ERROR +desc db_bug14533.t1; +--error ER_TABLEACCESS_DENIED_ERROR +select * from db_bug14533.t1; + +# Cleanup +connection default; +disconnect user_bug14533; +drop user user_bug14533@localhost; +drop database db_bug14533; + + +# +# WL#2897 Complete definer support in the stored routines. +# +# The following cases are tested: +# 1. check that if DEFINER-clause is not explicitly specified, stored routines +# are created with CURRENT_USER privileges; +# 2. check that if DEFINER-clause specifies non-current user, SUPER privilege +# is required to create a stored routine; +# 3. check that if DEFINER-clause specifies non-existent user, a warning is +# emitted. +# 4. check that SHOW CREATE PROCEDURE | FUNCTION works correctly; +# +# The following cases are tested in other test suites: +# - check that mysqldump dumps new attribute correctly; +# - check that slave replicates CREATE-statements with explicitly specified +# DEFINER correctly. +# + +# Setup the environment. + +--echo +--echo ---> connection: root +--connection con1root + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest; +--enable_warnings + +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) + +# test case (1). + +--echo +--echo ---> connection: mysqltest_2_con +--connection mysqltest_2_con + +USE mysqltest; + +CREATE PROCEDURE wl2897_p1() SELECT 1; + +CREATE FUNCTION wl2897_f1() RETURNS INT RETURN 1; + +# test case (2). + +--echo +--echo ---> connection: mysqltest_1_con +--connection mysqltest_1_con + +USE mysqltest; + +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +CREATE DEFINER=root@localhost PROCEDURE wl2897_p2() SELECT 2; + +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +CREATE DEFINER=root@localhost FUNCTION wl2897_f2() RETURNS INT RETURN 2; + +# test case (3). + +--echo +--echo ---> connection: mysqltest_2_con +--connection mysqltest_2_con + +use mysqltest; + +CREATE DEFINER='a @ b @ c'@localhost PROCEDURE wl2897_p3() SELECT 3; + +CREATE DEFINER='a @ b @ c'@localhost FUNCTION wl2897_f3() RETURNS INT RETURN 3; + +# test case (4). + +--echo +--echo ---> connection: con1root +--connection con1root + +USE mysqltest; + +SHOW CREATE PROCEDURE wl2897_p1; +SHOW CREATE PROCEDURE wl2897_p3; + +SHOW CREATE FUNCTION wl2897_f1; +SHOW CREATE FUNCTION wl2897_f3; + +# Cleanup. + +DROP USER mysqltest_1@localhost; +DROP USER mysqltest_2@localhost; + +DROP DATABASE mysqltest; + +--disconnect mysqltest_1_con +--disconnect mysqltest_2_con + + +# +# Bug#13198 SP executes if definer does not exist +# + +# Prepare environment. + +--echo +--echo ---> connection: root +--connection con1root + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest; +--enable_warnings + +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) + +# Create a procedure/function under u1. + +--echo +--echo ---> connection: mysqltest_1_con +--connection mysqltest_1_con + +USE mysqltest; + +CREATE PROCEDURE bug13198_p1() + SELECT 1; + +CREATE FUNCTION bug13198_f1() RETURNS INT + RETURN 1; + +CALL bug13198_p1(); + +SELECT bug13198_f1(); + +# Check that u2 can call the procedure/function. + +--echo +--echo ---> connection: mysqltest_2_con +--connection mysqltest_2_con + +USE mysqltest; + +CALL bug13198_p1(); + +SELECT bug13198_f1(); + +# Drop user u1 (definer of the object); + +--echo +--echo ---> connection: root +--connection con1root + +--disconnect mysqltest_1_con + +DROP USER mysqltest_1@localhost; + +# Check that u2 can not call the procedure/function. + +--echo +--echo ---> connection: mysqltest_2_con +--connection mysqltest_2_con + +USE mysqltest; + +--error ER_NO_SUCH_USER +CALL bug13198_p1(); + +--error ER_NO_SUCH_USER +SELECT bug13198_f1(); + +# Cleanup. + +--echo +--echo ---> connection: root +--connection con1root + +--disconnect mysqltest_2_con + +DROP USER mysqltest_2@localhost; + +DROP DATABASE mysqltest; + +# +# Bug#19857 When a user with CREATE ROUTINE priv creates a routine, +# it results in NULL p/w +# + +# Can't test with embedded server that doesn't support grants + +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'; + +--connect (mysqltest_2_con,localhost,user19857,meow,test) +--echo +--echo ---> connection: mysqltest_2_con +--connection mysqltest_2_con + +USE test; + +DELIMITER //; + CREATE PROCEDURE sp19857() DETERMINISTIC + BEGIN + DECLARE a INT; + SET a=1; + SELECT a; + END // +DELIMITER ;// + +SHOW CREATE PROCEDURE test.sp19857; + +--disconnect mysqltest_2_con +--connect (mysqltest_2_con,localhost,user19857,meow,test) +--connection mysqltest_2_con + +DROP PROCEDURE IF EXISTS test.sp19857; + +--echo +--echo ---> connection: root +--connection con1root + +--disconnect mysqltest_2_con + +SELECT Host,User,Password FROM mysql.user WHERE User='user19857'; + +DROP USER user19857@localhost; + +--disconnect con1root +--connection default +use test; + +# +# Bug#18630 Arguments of suid routine calculated in wrong security context +# +# Arguments of suid routines were calculated in definer's security +# context instead of caller's context thus creating security hole. +# +--disable_warnings +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; +--enable_warnings +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; +# Thanks to this grant statement privileges of anonymous users on +# 'test' database are not applicable for mysqltest_u1@localhost. +GRANT EXECUTE ON test.* TO mysqltest_u1@localhost; + +delimiter |; +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| +delimiter ;| + +CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT f_evil(); + +connect (conn1, localhost, mysqltest_u1,,); + +--error ER_TABLEACCESS_DENIED_ERROR +SELECT COUNT(*) FROM t1; + +--error ER_TABLEACCESS_DENIED_ERROR +SELECT f_evil(); +SELECT @a, @b; + +--error ER_TABLEACCESS_DENIED_ERROR +SELECT f_suid(f_evil()); +SELECT @a, @b; + +--error ER_TABLEACCESS_DENIED_ERROR +CALL p_suid(f_evil()); +SELECT @a, @b; + +--error ER_TABLEACCESS_DENIED_ERROR +SELECT * FROM v1; +SELECT @a, @b; + +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; + +--echo # +--echo # Bug #48872 : Privileges for stored functions ignored if function name +--echo # is mixed case +--echo # + +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(); +SELECT * FROM TestTab; + +CONNECT (con_tester,localhost,tester,,B48872); +CONNECT (con_tester_denied,localhost,Tester,,B48872); +CONNECTION con_tester; + +SELECT * FROM TestTab; +SELECT `f_Test`(); +SELECT `F_TEST`(); +SELECT f_Test(); +SELECT F_TEST(); + +CONNECTION con_tester_denied; + +--disable_result_log +--error ER_TABLEACCESS_DENIED_ERROR +SELECT * FROM TestTab; +--error ER_PROCACCESS_DENIED_ERROR +SELECT `f_Test`(); +--error ER_PROCACCESS_DENIED_ERROR +SELECT `F_TEST`(); +--error ER_PROCACCESS_DENIED_ERROR +SELECT f_Test(); +--error ER_PROCACCESS_DENIED_ERROR +SELECT F_TEST(); +--enable_result_log +SELECT `f_Test_denied`(); +SELECT `F_TEST_DENIED`(); + +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; + +--echo End of 5.0 tests. + + +--echo # +--echo # Test for bug#57061 "User without privilege on routine can discover +--echo # its existence." +--echo # +--disable_warnings +drop database if exists mysqltest_db; +--enable_warnings +create database mysqltest_db; +--echo # 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; +--echo # Connect as user 'bug57061_user@localhost' +connect (conn1, localhost, bug57061_user,,); +--echo # Attempt to drop routine on which user doesn't have privileges +--echo # should result in the same 'access denied' type of error whether +--echo # routine exists or not. +--error ER_PROCACCESS_DENIED_ERROR +drop function if exists mysqltest_db.f_does_not_exist; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure if exists mysqltest_db.p_does_not_exist; +--error ER_PROCACCESS_DENIED_ERROR +drop function if exists mysqltest_db.f1; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure if exists mysqltest_db.p1; +--echo # Connection 'default'. +connection default; +disconnect conn1; +drop user bug57061_user@localhost; +drop database mysqltest_db; + + +--echo # +--echo # Bug#11882603 SELECT_ACL ON ANY COLUMN IN MYSQL.PROC ALLOWS TO SEE +--echo # DEFINITION OF ANY ROUTINE. +--echo # + +--disable_warnings +DROP DATABASE IF EXISTS db1; +--enable_warnings + +CREATE DATABASE db1; +CREATE PROCEDURE db1.p1() SELECT 1; +CREATE USER user2@localhost IDENTIFIED BY ''; +GRANT SELECT(db) ON mysql.proc TO user2@localhost; + +--echo # Connection con2 as user2 +connect (con2, localhost, user2); +--echo # The statement below before disclosed info from body_utf8 column. +--error ER_SP_DOES_NOT_EXIST +SHOW CREATE PROCEDURE db1.p1; + +--echo # Check that SHOW works with SELECT grant on whole table +--echo # Connection default +connection default; +GRANT SELECT ON mysql.proc TO user2@localhost; + +--echo # Connection con2 +connection con2; +--echo # This should work +SHOW CREATE PROCEDURE db1.p1; + +--echo # Connection default +connection default; +disconnect con2; +DROP USER user2@localhost; +DROP DATABASE db1; + +# +# Bug#27407480: AUTOMATIC_SP_PRIVILEGES REQUIRES NEED THE INSERT PRIVILEGES FOR MYSQL.USER TABLE +# +create user foo@local_ost; +# +# Create a user with an authentification plugin 'foobar'. +# Instead of using a normal "CREATE USER <user> IDENTIFIED VIA <plugin>" +# we do CREATE (without VIA) followed by UPDATE and FLUSH. +# This is to avoid installing a real plugin and thus avoid the test dependency. +# We won't login under this user in the below test, so this is fine. +# +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(); +show grants; +create procedure fooproc() select 'i am fooproc'; +show grants; +disconnect con1; +connection default; +drop user foo@local_ost; +drop user foo@`local\_ost`; +drop procedure fooproc; +drop database foodb; + +--echo # +--echo # Test for bug#12602983 - User without privilege on routine can discover +--echo # its existence by executing "select non_existing_func();" or by +--echo # "call non_existing_proc()"; +--echo # +--disable_warnings +drop database if exists mysqltest_db; +--enable_warnings +create database mysqltest_db; +create function mysqltest_db.f1() returns int return 0; +create procedure mysqltest_db.p1() begin end; + +--echo # Create user with no privileges on mysqltest_db database. +create user bug12602983_user@localhost; + +--echo # Connect as user 'bug12602983_user@localhost' +connect (conn1, localhost, bug12602983_user,,); + +--echo # Attempt to execute routine on which user doesn't have privileges +--echo # should result in the same 'access denied' error whether +--echo # routine exists or not. +--error ER_PROCACCESS_DENIED_ERROR +select mysqltest_db.f_does_not_exist(); +--error ER_PROCACCESS_DENIED_ERROR +call mysqltest_db.p_does_not_exist(); + +--error ER_PROCACCESS_DENIED_ERROR +select mysqltest_db.f1(); +--error ER_PROCACCESS_DENIED_ERROR +call mysqltest_db.p1(); + +--error ER_PROCACCESS_DENIED_ERROR +create view bug12602983_v1 as select mysqltest_db.f_does_not_exist(); +--error ER_PROCACCESS_DENIED_ERROR +create view bug12602983_v1 as select mysqltest_db.f1(); + +--echo # Connection 'default'. +connection default; +disconnect conn1; +drop user bug12602983_user@localhost; +drop database mysqltest_db; + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc |