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