1** Setup ** 2SET @global_automatic_sp_privileges = @@GLOBAL.automatic_sp_privileges; 3CREATE TABLE t1 (a varchar(200)); 4INSERT INTO t1 VALUES('Procedure Executed.'); 5CREATE USER 'userTest'@'localhost'; 6CREATE USER 'userTest1'@'localhost'; 7connection default; 8SET GLOBAL automatic_sp_privileges = TRUE; 9connect conUser,localhost,userTest,,; 10connection conUser; 11CREATE PROCEDURE testProc () 12BEGIN 13SELECT * FROM t1; 14END;| 15CALL testProc(); 16a 17Procedure Executed. 18Expecting SELECT executed 19connection default; 20SET GLOBAL automatic_sp_privileges = FALSE; 21connect conUser1,localhost,userTest1,,; 22connection conUser1; 23CREATE PROCEDURE testProc1 () 24BEGIN 25SELECT * FROM t1; 26END;| 27 28Expected error access denied 29CALL testProc1(); 30ERROR 42000: execute command denied to user 'userTest1'@'localhost' for routine 'test.testProc1' 31 32Expected error access denied 33ALTER PROCEDURE testProc1 COMMENT 'My Comment'; 34ERROR 42000: alter routine command denied to user 'userTest1'@'localhost' for routine 'test.testProc1' 35 36Expected error access denied 37DROP PROCEDURE testProc1; 38ERROR 42000: alter routine command denied to user 'userTest1'@'localhost' for routine 'test.testProc1' 39connection default; 40GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE testProc1 TO 'userTest1'@'localhost'; 41connection conUser1; 42CALL testProc1(); 43a 44Procedure Executed. 45Expecting seelect executed 46ALTER PROCEDURE testProc1 COMMENT 'My Comment'; 47 48** Cleanup ** 49connection default; 50disconnect conUser; 51disconnect conUser1; 52SET GLOBAL automatic_sp_privileges = @global_automatic_sp_privileges; 53REVOKE EXECUTE, ALTER ROUTINE ON PROCEDURE testProc FROM 'userTest'@'localhost'; 54REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'userTest'@'localhost'; 55REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'userTest1'@'localhost'; 56DROP PROCEDURE testProc; 57DROP PROCEDURE testProc1; 58DROP USER 'userTest'@'localhost'; 59DROP USER 'userTest1'@'localhost'; 60DROP TABLE t1; 61