diff options
Diffstat (limited to 'mysql-test/r/sp-security.test')
| -rw-r--r-- | mysql-test/r/sp-security.test | 1069 |
1 files changed, 0 insertions, 1069 deletions
diff --git a/mysql-test/r/sp-security.test b/mysql-test/r/sp-security.test deleted file mode 100644 index 53dc4f8c7ac..00000000000 --- a/mysql-test/r/sp-security.test +++ /dev/null @@ -1,1069 +0,0 @@ -# -# 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 |