CREATE ROLE r1;
CREATE USER u1@localhost IDENTIFIED BY 'foo';
GRANT r1 TO u1@localhost;
CREATE DATABASE db1;
CREATE DATABASE db2;
CREATE TABLE db1.t1 (c1 int);
CREATE TABLE db1.t2 (c1 int);
CREATE TABLE db2.t1 (c1 int);
CREATE TABLE db2.t2 (c1 int);
CREATE PROCEDURE db1.sp1()
SQL SECURITY DEFINER
BEGIN
SELECT * FROM db1.t1;
END//
CREATE PROCEDURE db2.sp1()
SQL SECURITY DEFINER
BEGIN
SELECT * FROM db2.t1;
END//
CREATE PROCEDURE test.sp1()
SQL SECURITY DEFINER
BEGIN
SELECT * FROM db1.t1;
END//
CREATE PROCEDURE db1.sp2()
SQL SECURITY DEFINER
BEGIN
SELECT * FROM db1.t2;
END//
CREATE PROCEDURE db1.sp3()
SQL SECURITY INVOKER
BEGIN
SELECT * FROM db1.t2;
END//
++ Test global level privileges
GRANT EXECUTE ON *.* TO r1;
SHOW GRANTS FOR u1@localhost USING r1;
Grants for u1@localhost
GRANT EXECUTE ON *.* TO `u1`@`localhost`
GRANT `r1`@`%` TO `u1`@`localhost`
SET ROLE r1;
++ Positive test
CALL db1.sp1();
c1
++ Negative test
CALL db1.sp3();
ERROR 42000: SELECT command denied to user 'u1'@'localhost' for table 't2'
++ Test revoke
REVOKE EXECUTE ON *.* FROM r1;
SHOW GRANTS FOR u1@localhost USING r1;
Grants for u1@localhost
GRANT USAGE ON *.* TO `u1`@`localhost`
GRANT `r1`@`%` TO `u1`@`localhost`
SET ROLE r1;
CALL db1.sp1();
ERROR 42000: execute command denied to user 'u1'@'localhost' for routine 'db1.sp1'
++ Test schema level privileges
GRANT EXECUTE ON db1.* TO r1;
SHOW GRANTS FOR u1@localhost USING r1;
Grants for u1@localhost
GRANT USAGE ON *.* TO `u1`@`localhost`
GRANT EXECUTE ON `db1`.* TO `u1`@`localhost`
GRANT `r1`@`%` TO `u1`@`localhost`
++ Positive test
CALL db1.sp1();
c1
CALL db1.sp2();
c1
++ Negative test
CALL db2.sp1();
ERROR 42000: execute command denied to user 'u1'@'localhost' for routine 'db2.sp1'
CALL db1.sp3();
ERROR 42000: SELECT command denied to user 'u1'@'localhost' for table 't2'
REVOKE EXECUTE ON db1.* FROM r1;
++ Test routine level privileges
GRANT EXECUTE ON PROCEDURE db1.sp1 TO r1;
++ Positive test
CALL db1.sp1();
c1
++ Negative test
CALL db1.sp2();
ERROR 42000: execute command denied to user 'u1'@'localhost' for routine 'db1.sp2'
CALL db2.sp1();
ERROR 42000: execute command denied to user 'u1'@'localhost' for routine 'db2.sp1'
CALL db1.sp3();
ERROR 42000: execute command denied to user 'u1'@'localhost' for routine 'db1.sp3'
++ Test Security invoker model
GRANT EXECUTE, SELECT ON db1.* TO r1;
++ Positive test
CALL db1.sp3();
c1
++ Clean up
DROP DATABASE db1;
DROP DATABASE db2;
DROP USER u1@localhost;
DROP ROLE r1;
DROP PROCEDURE test.sp1;