Debian Codegraph @

mysql-test/suite/sys_vars/r/automatic_sp_privileges_func.result

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