diff options
Diffstat (limited to '')
| -rw-r--r-- | mysql-test/main/sp-package-security.result | 322 |
1 files changed, 322 insertions, 0 deletions
diff --git a/mysql-test/main/sp-package-security.result b/mysql-test/main/sp-package-security.result new file mode 100644 index 00000000..cd859b04 --- /dev/null +++ b/mysql-test/main/sp-package-security.result @@ -0,0 +1,322 @@ +# +# Start of 11.4 tests +# +CREATE DATABASE db1; +CREATE USER u1@localhost IDENTIFIED BY ''; +GRANT SELECT ON db1.* TO u1@localhost; +connect conn1,localhost,u1,,db1; +SELECT CURRENT_USER; +CURRENT_USER +u1@localhost +# +# User u1 cannot drop PROCEDURE, PACKAGE, PACKAGE BODY by default +# +DROP PROCEDURE p1; +ERROR 42000: alter routine command denied to user 'u1'@'localhost' for routine 'db1.p1' +DROP PACKAGE pkg1; +ERROR 42000: alter routine command denied to user 'u1'@'localhost' for routine 'db1.pkg1' +DROP PACKAGE BODY pkg1; +ERROR 42000: alter routine command denied to user 'u1'@'localhost' for routine 'db1.pkg1' +# +# User u1 cannot create PROCEDURE, PACKAGE, PACKAGE BODY by default +# +CREATE PROCEDURE p1() +BEGIN +END; +$$ +ERROR 42000: Access denied for user 'u1'@'localhost' to database 'db1' +CREATE PACKAGE pkg1 +PROCEDURE p1(); +END; +$$ +ERROR 42000: Access denied for user 'u1'@'localhost' to database 'db1' +CREATE PACKAGE BODY pkg1 AS +PROCEDURE p1() AS BEGIN END; +END; +$$ +ERROR 42000: PACKAGE db1.pkg1 does not exist +# +# Now create a PACKAGE by root +# +connection default; +USE db1; +CREATE PROCEDURE p1root() +BEGIN +SELECT 1; +END; +$$ +CREATE PACKAGE pkg1 +PROCEDURE p1(); +FUNCTION f1() RETURNS TEXT; +END; +$$ +SHOW CREATE PACKAGE pkg1; +Package sql_mode Create Package character_set_client collation_connection Database Collation +pkg1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PACKAGE `pkg1` PROCEDURE p1(); +FUNCTION f1() RETURNS TEXT; +END latin1 latin1_swedish_ci latin1_swedish_ci +# +# u1 cannot SHOW yet: +# - the standalone procedure earlier created by root +# - the package specifications earlier create by root +# +connection conn1; +SHOW CREATE PROCEDURE p1root; +ERROR 42000: PROCEDURE p1root does not exist +SHOW CREATE PACKAGE pkg1; +ERROR 42000: PACKAGE pkg1 does not exist +# +# User u1 still cannot create a PACKAGE BODY +# +connection conn1; +CREATE PACKAGE BODY pkg1 +PROCEDURE p1() BEGIN END; +FUNCTION f1() RETURNS TEXT BEGIN RETURN 'This is f1'; END; +END; +$$ +ERROR 42000: Access denied for user 'u1'@'localhost' to database 'db1' +# +# Now grant EXECUTE: +# - on the standalone procedure earlier created by root +# - on the package specification earlier created by root +# +connection default; +GRANT EXECUTE ON PROCEDURE db1.p1root TO u1@localhost; +GRANT EXECUTE ON PACKAGE db1.pkg1 TO u1@localhost; +# +# Now u1 can do SHOW for: +# - the standalone procedure earlier created by root +# - the package specification earlier created by root +# +disconnect conn1; +connect conn1,localhost,u1,,db1; +SHOW CREATE PROCEDURE db1.p1root; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +p1root STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION NULL latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE PACKAGE db1.pkg1; +Package sql_mode Create Package character_set_client collation_connection Database Collation +pkg1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION NULL latin1 latin1_swedish_ci latin1_swedish_ci +# +# Now revoke EXECUTE and grant CREATE ROUTINE instead +# +connection default; +REVOKE EXECUTE ON PROCEDURE db1.p1root FROM u1@localhost; +REVOKE EXECUTE ON PACKAGE db1.pkg1 FROM u1@localhost; +GRANT CREATE ROUTINE ON db1.* TO u1@localhost; +# +# Reconnect u1 to make new grants have effect +# +disconnect conn1; +connect conn1,localhost,u1,,db1; +# +# Now u1 can SHOW: +# - standalone routines earlier created by root +# - package specifications earlier created by root +# +SHOW CREATE PROCEDURE p1root; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +p1root STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION NULL latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE PACKAGE pkg1; +Package sql_mode Create Package character_set_client collation_connection Database Collation +pkg1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION NULL latin1 latin1_swedish_ci latin1_swedish_ci +# +# Now u1 can CREATE, DROP and EXECUTE its own standalone procedures +# +CREATE PROCEDURE p1() +BEGIN +END; +$$ +SHOW GRANTS; +Grants for u1@localhost +GRANT USAGE ON *.* TO `u1`@`localhost` +GRANT SELECT, CREATE ROUTINE ON `db1`.* TO `u1`@`localhost` +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `db1`.`p1` TO `u1`@`localhost` +CALL p1; +DROP PROCEDURE p1; +SHOW GRANTS; +Grants for u1@localhost +GRANT USAGE ON *.* TO `u1`@`localhost` +GRANT SELECT, CREATE ROUTINE ON `db1`.* TO `u1`@`localhost` +# +# Now u1 can also CREATE, DROP its own package specifications +# +CREATE PACKAGE pkg2 +PROCEDURE p1(); +FUNCTION f1() RETURNS TEXT; +END; +$$ +SHOW CREATE PACKAGE pkg2; +Package sql_mode Create Package character_set_client collation_connection Database Collation +pkg2 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`u1`@`localhost` PACKAGE `pkg2` PROCEDURE p1(); +FUNCTION f1() RETURNS TEXT; +END latin1 latin1_swedish_ci latin1_swedish_ci +SHOW GRANTS; +Grants for u1@localhost +GRANT USAGE ON *.* TO `u1`@`localhost` +GRANT SELECT, CREATE ROUTINE ON `db1`.* TO `u1`@`localhost` +GRANT EXECUTE, ALTER ROUTINE ON PACKAGE `db1`.`pkg2` TO `u1`@`localhost` +DROP PACKAGE pkg2; +SHOW GRANTS; +Grants for u1@localhost +GRANT USAGE ON *.* TO `u1`@`localhost` +GRANT SELECT, CREATE ROUTINE ON `db1`.* TO `u1`@`localhost` +# +# Now u1 can also CREATE, DROP package bodies and EXECUTE package body routines +# +CREATE PACKAGE BODY pkg1 +PROCEDURE p1() BEGIN SELECT 'This is pkg1.p1' AS `comment`; END; +FUNCTION f1() RETURNS TEXT BEGIN RETURN 'This is pkg1.f1'; END; +END; +$$ +SHOW CREATE PACKAGE pkg1; +Package sql_mode Create Package character_set_client collation_connection Database Collation +pkg1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION NULL latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE PACKAGE BODY pkg1; +Package body sql_mode Create Package Body character_set_client collation_connection Database Collation +pkg1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`u1`@`localhost` PACKAGE BODY `pkg1` PROCEDURE p1() BEGIN SELECT 'This is pkg1.p1' AS `comment`; END; +FUNCTION f1() RETURNS TEXT BEGIN RETURN 'This is pkg1.f1'; END; +END latin1 latin1_swedish_ci latin1_swedish_ci +SHOW GRANTS; +Grants for u1@localhost +GRANT USAGE ON *.* TO `u1`@`localhost` +GRANT SELECT, CREATE ROUTINE ON `db1`.* TO `u1`@`localhost` +GRANT EXECUTE, ALTER ROUTINE ON PACKAGE BODY `db1`.`pkg1` TO `u1`@`localhost` +CALL pkg1.p1; +comment +This is pkg1.p1 +SELECT pkg1.f1(); +pkg1.f1() +This is pkg1.f1 +DROP PACKAGE BODY pkg1; +SHOW GRANTS; +Grants for u1@localhost +GRANT USAGE ON *.* TO `u1`@`localhost` +GRANT SELECT, CREATE ROUTINE ON `db1`.* TO `u1`@`localhost` +# +# Now create a PACKAGE BODY by root. +# u1 does not have EXECUTE access by default. +# +connection default; +CREATE PACKAGE BODY pkg1 +PROCEDURE p1() BEGIN SELECT 'This is pkg1.p1' AS `comment`; END; +FUNCTION f1() RETURNS TEXT RETURN 'This is pkg1.f1'; +END; +$$ +connection conn1; +SHOW CREATE PACKAGE pkg1; +Package sql_mode Create Package character_set_client collation_connection Database Collation +pkg1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION NULL latin1 latin1_swedish_ci latin1_swedish_ci +SHOW CREATE PACKAGE BODY pkg1; +Package body sql_mode Create Package Body character_set_client collation_connection Database Collation +pkg1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION NULL latin1 latin1_swedish_ci latin1_swedish_ci +CALL pkg1.p1; +ERROR 42000: execute command denied to user 'u1'@'localhost' for routine 'db1.pkg1' +SELECT pkg1.f1(); +ERROR 42000: execute command denied to user 'u1'@'localhost' for routine 'db1.pkg1' +# +# Now grant EXECUTE to u1 on the PACKAGE BODY created by root +# +connection default; +GRANT EXECUTE ON PACKAGE BODY db1.pkg1 TO u1@localhost; +disconnect conn1; +connect conn1,localhost,u1,,db1; +SELECT CURRENT_USER; +CURRENT_USER +u1@localhost +SHOW GRANTS; +Grants for u1@localhost +GRANT USAGE ON *.* TO `u1`@`localhost` +GRANT SELECT, CREATE ROUTINE ON `db1`.* TO `u1`@`localhost` +GRANT EXECUTE ON PACKAGE BODY `db1`.`pkg1` TO `u1`@`localhost` +CALL pkg1.p1; +comment +This is pkg1.p1 +SELECT pkg1.f1(); +pkg1.f1() +This is pkg1.f1 +connection default; +DROP PACKAGE BODY pkg1; +# +# u1 still cannot DROP the package specification earlier created by root. +# +connection conn1; +DROP PACKAGE pkg1; +ERROR 42000: alter routine command denied to user 'u1'@'localhost' for routine 'db1.pkg1' +# +# Grant ALTER ROUTINE to u1 +# +connection default; +GRANT ALTER ROUTINE ON db1.* TO u1@localhost; +# +# Now u1 can DROP: +# - the standalone procedure earlier created by root +# - the package specification earlier created by root +# +disconnect conn1; +connect conn1,localhost,u1,,db1; +DROP PACKAGE pkg1; +DROP PROCEDURE p1root; +disconnect conn1; +connection default; +DROP USER u1@localhost; +DROP DATABASE db1; +USE test; +# +# Creator=root, definer=xxx +# +CREATE USER xxx@localhost; +CREATE DEFINER=xxx@localhost PACKAGE p1 +PROCEDURE p1(); +END; +$$ +CREATE DEFINER=xxx@localhost PACKAGE BODY p1 +PROCEDURE p1() +BEGIN +SELECT SESSION_USER(), CURRENT_USER(), 'p1.p1' AS msg; +END; +SELECT SESSION_USER(), CURRENT_USER(), 'package body p1' AS msg; +END; +$$ +CALL p1.p1; +ERROR 42000: execute command denied to user 'xxx'@'localhost' for routine 'test.p1' +GRANT EXECUTE ON PACKAGE BODY test.p1 TO xxx@localhost; +CALL p1.p1; +SESSION_USER() CURRENT_USER() msg +root@localhost xxx@localhost package body p1 +SESSION_USER() CURRENT_USER() msg +root@localhost xxx@localhost p1.p1 +DROP PACKAGE p1; +DROP USER xxx@localhost; +# +# Creator=root, definer=xxx, SQL SECURITY INVOKER +# +CREATE USER xxx@localhost; +CREATE DEFINER=xxx@localhost PACKAGE p1 +PROCEDURE p1(); +END; +$$ +CREATE DEFINER=xxx@localhost PACKAGE BODY p1 SQL SECURITY INVOKER +PROCEDURE p1() +BEGIN +SELECT SESSION_USER(), CURRENT_USER(), 'p1.p1' AS msg; +END; +SELECT SESSION_USER(), CURRENT_USER(), 'package body p1' AS msg; +END; +$$ +CALL p1.p1; +SESSION_USER() CURRENT_USER() msg +root@localhost root@localhost package body p1 +SESSION_USER() CURRENT_USER() msg +root@localhost root@localhost p1.p1 +DROP PACKAGE p1; +DROP USER xxx@localhost; +# +# MDEV-33386 Wrong error message on `GRANT .. ON PACKAGE no_such_package ..` +# +GRANT EXECUTE ON PACKAGE no_such_package TO PUBLIC; +ERROR 42000: PACKAGE no_such_package does not exist +GRANT EXECUTE ON PACKAGE BODY no_such_package TO PUBLIC; +ERROR 42000: PACKAGE BODY no_such_package does not exist +# +# End of 11.4 tests +# |