diff options
Diffstat (limited to 'mysql-test/main/sp-security.test')
| -rw-r--r-- | mysql-test/main/sp-security.test | 554 |
1 files changed, 554 insertions, 0 deletions
diff --git a/mysql-test/main/sp-security.test b/mysql-test/main/sp-security.test index 85f21835..af9ba61d 100644 --- a/mysql-test/main/sp-security.test +++ b/mysql-test/main/sp-security.test @@ -1123,3 +1123,557 @@ drop function f; --echo # --echo # End of 10.6 tests --echo # + +--echo # +--echo # MDEV-29167: new db-level SHOW CREATE ROUTINE privilege +--echo # + +--echo ### +--echo ### SHOW-Like commad test +--echo ### + +SET @save_sql_mode=@@sql_mode; + +--echo # +--echo ### Prepare functions for the test and SHOW-like by root +--echo # + +create database test_db; +use test_db; +create procedure test_db.sp() select 1; +show create procedure test_db.sp; +--replace_column 5 # 6 # +SHOW PROCEDURE STATUS WHERE name="sp"; +SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="sp"; +CREATE FUNCTION test_db.fn() RETURNS INT RETURN 1; +show create function test_db.fn; +--replace_column 5 # 6 # +SHOW FUNCTION STATUS WHERE name="fn"; +SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="fn"; + +SET sql_mode=ORACLE; +DELIMITER $$; +CREATE PACKAGE test_db.pk AS + FUNCTION pkf() RETURN INT; + PROCEDURE pkp(); +END; +$$ +CREATE PACKAGE BODY test_db.pk AS + pkv INT:=1; + + PROCEDURE pkhp() AS + BEGIN + SELECT pkv FROM DUAL; + END; + + FUNCTION pkhf() RETURN INT AS + BEGIN + RETURN pkv; + END; + + PROCEDURE pkp() AS + BEGIN + CALL pkhp(); + END; + FUNCTION pkf() RETURN INT AS + BEGIN + RETURN pkhf(); + END; + +BEGIN + pkv:=2; +END; +$$ +DELIMITER ;$$ + +SET sql_mode=@save_sql_mode; +show create package test_db.pk; +show create package body test_db.pk; +--replace_column 5 # 6 # +SHOW PACKAGE STATUS WHERE name="pk"; +SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk"; + + +use test; + + +--echo ### +--echo ### Pre-"SHOW-CREATE-ROUTINE" behaviour tests +--echo ### + + +--echo # +--echo ### Rights on mysql.proc +--echo # + +create user user@localhost; +grant all privileges on mysql.* to user@localhost; +grant all privileges on test.* to user@localhost; + +connect conn1, localhost, user, , test; + +show create procedure test_db.sp; +--replace_column 5 # 6 # +SHOW PROCEDURE STATUS WHERE name="sp"; +SELECT ROUTINE_NAME from information_schema.ROUTINES where ROUTINE_NAME="sp"; +show create function test_db.fn; +--replace_column 5 # 6 # +SHOW FUNCTION STATUS WHERE name="fn"; +SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where +ROUTINE_NAME="fn"; +show create package test_db.pk; +show create package body test_db.pk; +--replace_column 5 # 6 # +SHOW PACKAGE STATUS WHERE name="pk"; +SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk"; + +connection default; +disconnect conn1; + +revoke all privileges on mysql.* from user@localhost; + + +--echo # +--echo ### No privileges +--echo # + +connect conn1, localhost, user, , test; + +--error ER_SP_DOES_NOT_EXIST +show create procedure test_db.sp; +--replace_column 5 # 6 # +SHOW PROCEDURE STATUS WHERE name="sp"; +SELECT ROUTINE_NAME from information_schema.ROUTINES where ROUTINE_NAME="sp"; +--error ER_SP_DOES_NOT_EXIST +show create function test_db.fn; +--replace_column 5 # 6 # +SHOW FUNCTION STATUS WHERE name="fn"; +SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where +ROUTINE_NAME="fn"; +--error ER_SP_DOES_NOT_EXIST +show create package test_db.pk; +--error ER_SP_DOES_NOT_EXIST +show create package body test_db.pk; +--replace_column 5 # 6 # +SHOW PACKAGE STATUS WHERE name="pk"; +SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk"; + +connection default; +disconnect conn1; + +--echo # +--echo ### Execute provilege PROCEDURE/FUNCTION +--echo # + +grant execute on procedure test_db.sp to user@localhost; +grant execute on function test_db.fn to user@localhost; + +connect conn1, localhost, user, , test; + +show create procedure test_db.sp; +--replace_column 5 # 6 # +SHOW PROCEDURE STATUS WHERE name="sp"; +SELECT ROUTINE_NAME from information_schema.ROUTINES where ROUTINE_NAME="sp"; +call test_db.sp(); +show create function test_db.fn; +--replace_column 5 # 6 # +SHOW FUNCTION STATUS WHERE name="fn"; +SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where +ROUTINE_NAME="fn"; +select test_db.fn(); + +connection default; +disconnect conn1; + +revoke execute on procedure test_db.sp from user@localhost; +revoke execute on function test_db.fn from user@localhost; + +--echo # +--echo ### Execute provilege PACKAGE+ PACKAGE BODY- +--echo # + +SET sql_mode=ORACLE; +grant execute on package test_db.pk to user@localhost; +SET sql_mode=@save_sql_mode; + +connect conn1, localhost, user, , test; + +show create package test_db.pk; +--error ER_SP_DOES_NOT_EXIST +show create package body test_db.pk; +--replace_column 5 # 6 # +SHOW PACKAGE STATUS WHERE name="pk"; +SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk"; +--error ER_PROCACCESS_DENIED_ERROR +call test_db.pk.pkp(); +--error ER_PROCACCESS_DENIED_ERROR +select test_db.pk.pkf(); + +connection default; +disconnect conn1; + +SET sql_mode=ORACLE; +revoke execute on package test_db.pk from user@localhost; +SET sql_mode=@save_sql_mode; + + +--echo # +--echo ### Execute provilege PACKAGE- PACKAGE BODY+ +--echo # + +SET sql_mode=ORACLE; +grant execute on package body test_db.pk to user@localhost; +SET sql_mode=@save_sql_mode; + +connect conn1, localhost, user, , test; + +--error ER_SP_DOES_NOT_EXIST +show create package test_db.pk; +show create package body test_db.pk; +--replace_column 5 # 6 # +SHOW PACKAGE STATUS WHERE name="pk"; +SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk"; +call test_db.pk.pkp(); +select test_db.pk.pkf(); + +connection default; +disconnect conn1; + +SET sql_mode=ORACLE; +revoke execute on package body test_db.pk from user@localhost; +SET sql_mode=@save_sql_mode; + +--echo # +--echo ### Alter routine provilege PROCEDURE/FUNCTION +--echo # + +grant alter routine on procedure test_db.sp to user@localhost; +grant alter routine on function test_db.fn to user@localhost; + +connect conn1, localhost, user, , test; + +show create procedure test_db.sp; +--replace_column 5 # 6 # +SHOW PROCEDURE STATUS WHERE name="sp"; +SELECT ROUTINE_NAME from information_schema.ROUTINES where ROUTINE_NAME="sp"; +show create function test_db.fn; +--replace_column 5 # 6 # +SHOW FUNCTION STATUS WHERE name="fn"; +SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where +ROUTINE_NAME="fn"; + +connection default; +disconnect conn1; + + +revoke alter routine on procedure test_db.sp from user@localhost; +revoke alter routine on function test_db.fn from user@localhost; + +--echo # +--echo ### Alter routine provilege PACKAGE+ PACKAGE BODY- +--echo # + +SET sql_mode=ORACLE; +grant alter routine on package test_db.pk to user@localhost; +SET sql_mode=@save_sql_mode; + +connect conn1, localhost, user, , test; + +show create package test_db.pk; +--error ER_SP_DOES_NOT_EXIST +show create package body test_db.pk; +--replace_column 5 # 6 # +SHOW PACKAGE STATUS WHERE name="pk"; +SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk"; + +connection default; +disconnect conn1; + +SET sql_mode=ORACLE; +revoke alter routine on package test_db.pk from user@localhost; +SET sql_mode=@save_sql_mode; + + +--echo # +--echo ### Alter routine provilege PACKAGE+ PACKAGE BODY- +--echo # + +SET sql_mode=ORACLE; +grant alter routine on package body test_db.pk to user@localhost; +SET sql_mode=@save_sql_mode; + +connect conn1, localhost, user, , test; + +--error ER_SP_DOES_NOT_EXIST +show create package test_db.pk; +show create package body test_db.pk; +--replace_column 5 # 6 # +SHOW PACKAGE STATUS WHERE name="pk"; +SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk"; + +connection default; +disconnect conn1; + +SET sql_mode=ORACLE; +revoke alter routine on package body test_db.pk from user@localhost; +SET sql_mode=@save_sql_mode; + + +--echo ### +--echo ### SHOW CREATE PROCEDURE tests +--echo ### + + +--echo # +--echo ### Global "show create routine" test +--echo # + +grant show create routine on *.* to user@localhost; +show grants for user@localhost; + +connect conn1, localhost, user, , test; + +show create procedure test_db.sp; +--replace_column 5 # 6 # +SHOW PROCEDURE STATUS WHERE name="sp"; +SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="sp"; +show create function test_db.fn; +--replace_column 5 # 6 # +SHOW FUNCTION STATUS WHERE name="fn"; +SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="fn"; +show create package test_db.pk; +show create package body test_db.pk; +--replace_column 5 # 6 # +SHOW PACKAGE STATUS WHERE name="pk"; +SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk"; + +connection default; +disconnect conn1; + +revoke show create routine on *.* from user@localhost; + +--echo # +--echo ### DB-level "show create routine" but other DB test +--echo # + +grant show create routine on db_test.* to user@localhost; +show grants for user@localhost; + +connect conn1, localhost, user, , test; + +--error ER_SP_DOES_NOT_EXIST +show create procedure test_db.sp; +--replace_column 5 # 6 # +SHOW PROCEDURE STATUS WHERE name="sp"; +SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="sp"; +--error ER_SP_DOES_NOT_EXIST +show create function test_db.fn; +--replace_column 5 # 6 # +SHOW FUNCTION STATUS WHERE name="fn"; +SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="fn"; +--error ER_SP_DOES_NOT_EXIST +show create package test_db.pk; +--error ER_SP_DOES_NOT_EXIST +show create package body test_db.pk; +--replace_column 5 # 6 # +SHOW PACKAGE STATUS WHERE name="pk"; +SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk"; + +connection default; +disconnect conn1; + +revoke show create routine on db_test.* from user@localhost; + +--echo # +--echo ### DB-level "show create routine" test +--echo # + +grant show create routine on test_db.* to user@localhost; +show grants for user@localhost; + +connect conn1, localhost, user, , test; + +show create procedure test_db.sp; +--replace_column 5 # 6 # +SHOW PROCEDURE STATUS WHERE name="sp"; +SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="sp"; +show create function test_db.fn; +--replace_column 5 # 6 # +SHOW FUNCTION STATUS WHERE name="fn"; +SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="fn"; +show create package test_db.pk; +show create package body test_db.pk; +--replace_column 5 # 6 # +SHOW PACKAGE STATUS WHERE name="pk"; +SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk"; + +connection default; +disconnect conn1; + +revoke show create routine on test_db.* from user@localhost; + + +--echo # +--echo ### Routine-level "show create routine" PROCEDURE and FUNCTION +--echo # + +grant show create routine on procedure test_db.sp to user@localhost; +grant show create routine on function test_db.fn to user@localhost; + +connect conn1, localhost, user, , test; + +show create procedure test_db.sp; +--replace_column 5 # 6 # +SHOW PROCEDURE STATUS WHERE name="sp"; +SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="sp"; +-- error ER_PROCACCESS_DENIED_ERROR +call test_db.sp(); +show create function test_db.fn; +--replace_column 5 # 6 # +SHOW FUNCTION STATUS WHERE name="fn"; +SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where +ROUTINE_NAME="fn"; +-- error ER_PROCACCESS_DENIED_ERROR +select test_db.fn(); + +connection default; +disconnect conn1; + +revoke show create routine on procedure test_db.sp from user@localhost; +revoke show create routine on function test_db.fn from user@localhost; + + +--echo # +--echo ### Routine-level "show create routine" PACKAGE+ PACKAGE BODY- +--echo # + +SET sql_mode=ORACLE; +grant show create routine on package test_db.pk to user@localhost; +SET sql_mode=@save_sql_mode; + +connect conn1, localhost, user, , test; + +show create package test_db.pk; +--error ER_SP_DOES_NOT_EXIST +show create package body test_db.pk; +--replace_column 5 # 6 # +SHOW PACKAGE STATUS WHERE name="pk"; +SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk"; +--error ER_PROCACCESS_DENIED_ERROR +call test_db.pk.pkp(); +--error ER_PROCACCESS_DENIED_ERROR +select test_db.pk.pkf(); + +connection default; +disconnect conn1; + +SET sql_mode=ORACLE; +revoke show create routine on package test_db.pk from user@localhost; +SET sql_mode=@save_sql_mode; + + +--echo # +--echo ### Routine-level "show create routine" PACKAGE- PACKAGE BODY+ +--echo # + +SET sql_mode=ORACLE; +grant show create routine on package body test_db.pk to user@localhost; +SET sql_mode=@save_sql_mode; + + +connect conn1, localhost, user, , test; + +--error ER_SP_DOES_NOT_EXIST +show create package test_db.pk; +show create package body test_db.pk; +--replace_column 5 # 6 # +SHOW PACKAGE STATUS WHERE name="pk"; +SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk"; +--error ER_PROCACCESS_DENIED_ERROR +call test_db.pk.pkp(); +--error ER_PROCACCESS_DENIED_ERROR +select test_db.pk.pkf(); + +connection default; +disconnect conn1; + +SET sql_mode=ORACLE; +revoke show create routine on package body test_db.pk from user@localhost; +SET sql_mode=@save_sql_mode; + +drop user user@localhost; +drop database test_db; + +--echo # +--echo ### Check owner only rights +--echo # + +create user user@localhost; +create database test_db; +use test_db; +create definer=user@localhost procedure test_db.sp() select 1; +CREATE definer=user@localhost FUNCTION test_db.fn() RETURNS INT RETURN 1; +SET sql_mode=ORACLE; +DELIMITER $$; +CREATE definer=user@localhost PACKAGE test_db.pk AS + FUNCTION pkf() RETURN INT; + PROCEDURE pkp(); +END; +$$ +CREATE definer=user@localhost PACKAGE BODY test_db.pk AS + pkv INT:=1; + + PROCEDURE pkhp() AS + BEGIN + SELECT pkv FROM DUAL; + END; + + FUNCTION pkhf() RETURN INT AS + BEGIN + RETURN pkv; + END; + + PROCEDURE pkp() AS + BEGIN + CALL pkhp(); + END; + FUNCTION pkf() RETURN INT AS + BEGIN + RETURN pkhf(); + END; + +BEGIN + pkv:=2; +END; +$$ +DELIMITER ;$$ + +use test; + + +connect conn1, localhost, user, , "*NO-ONE*"; + +show create procedure test_db.sp; +--replace_column 5 # 6 # +SHOW PROCEDURE STATUS WHERE name="sp"; +SELECT ROUTINE_NAME from information_schema.ROUTINES where ROUTINE_NAME="sp"; +show create function test_db.fn; +--replace_column 5 # 6 # +SHOW FUNCTION STATUS WHERE name="fn"; +SELECT ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES where +ROUTINE_NAME="fn"; +show create package test_db.pk; +show create package body test_db.pk; +--replace_column 5 # 6 # +SHOW PACKAGE STATUS WHERE name="pk"; +SELECT ROUTINE_NAME, ROUTINE_TYPE, ROUTINE_DEFINITION from information_schema.ROUTINES where ROUTINE_NAME="pk"; + +connection default; +disconnect conn1; + +drop user user@localhost; +drop database test_db; + +--echo # +--echo # End of 11.3 tests +--echo # |