Debian Codegraph @

mysql-test/suite/sys_vars/t/automatic_sp_privileges_func.test

raw
1 ############# mysql-test\t\automatic_sp_privileges_func.test ############################
2 # #
3 # Variable Name: automatic_sp_privileges #
4 # Scope: GLOBAL #
5 # Access Type: Dynamic #
6 # Data Type: BOOLEAN #
7 # Default Value: 1 TRUE #
8 # Values: 1 TRUE, 0 FALSE #
9 # #
10 # #
11 # Creation Date: 2008-03-04 #
12 # Author: Sharique Abdullah #
13 # #
14 # Description: Test Cases of Dynamic System Variable "automatic_sp_privileges" #
15 # that checks behavior of this variable in the following ways #
16 # * Functionality based on different values #
17 # #
18 # Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
19 # server-system-variables.html#option_mysqld_automatic_sp_privileges #
20 # #
21 #########################################################################################
22
23 #
24 # Setup
25 #
26
27 --source include/not_embedded.inc
28
29 --echo ** Setup **
30 SET @global_automatic_sp_privileges = @@GLOBAL.automatic_sp_privileges;
31
32 CREATE TABLE t1 (a varchar(200));
33
34 INSERT INTO t1 VALUES('Procedure Executed.');
35
36 #
37 # Creating test user
38 #
39
40 CREATE USER 'userTest'@'localhost';
41 GRANT CREATE ROUTINE, SELECT ON test.* to 'userTest'@'localhost';
42 CREATE USER 'userTest1'@'localhost';
43 GRANT CREATE ROUTINE, SELECT ON test.* to 'userTest1'@'localhost';
44
45 #
46 # Value TRUE
47 #
48 connection default;
49 SET GLOBAL automatic_sp_privileges = TRUE;
50
51 connect (conUser,localhost,userTest,,);
52 connection conUser;
53
54 delimiter |;
55
56 CREATE PROCEDURE testProc ()
57 BEGIN
58 SELECT * FROM t1;
59 END;|
60
61 delimiter ;|
62
63 CALL testProc();
64 --echo Expecting SELECT executed
65
66 #
67 # Value FALSE
68 #
69 connection default;
70 SET GLOBAL automatic_sp_privileges = FALSE;
71
72 connect (conUser1,localhost,userTest1,,);
73 connection conUser1;
74
75 delimiter |;
76
77 CREATE PROCEDURE testProc1 ()
78 BEGIN
79 SELECT * FROM t1;
80 END;|
81
82 delimiter ;|
83
84 --echo
85 --echo Expected error access denied
86 --error ER_PROCACCESS_DENIED_ERROR
87 CALL testProc1();
88
89 --echo
90 --echo Expected error access denied
91 --error ER_PROCACCESS_DENIED_ERROR
92 ALTER PROCEDURE testProc1 COMMENT 'My Comment';
93
94 --echo
95 --echo Expected error access denied
96 --error ER_PROCACCESS_DENIED_ERROR
97 DROP PROCEDURE testProc1;
98
99 connection default;
100
101 GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE testProc1 TO 'userTest1'@'localhost';
102
103 connection conUser1;
104
105 CALL testProc1();
106 --echo Expecting seelect executed
107
108 ALTER PROCEDURE testProc1 COMMENT 'My Comment';
109
110 --echo
111 #
112 # Cleanup
113 #
114 --echo ** Cleanup **
115 connection default;
116
117 disconnect conUser;
118 disconnect conUser1;
119
120 SET GLOBAL automatic_sp_privileges = @global_automatic_sp_privileges;
121
122 # Disabled due to differences in results: Bug#35384
123 #SHOW GRANTS FOR 'userTest'@'localhost';
124
125 # on Linux (5.1.24) successful, on Windows (5.1.23) error
126 --error 0,ER_NONEXISTING_PROC_GRANT
127 REVOKE EXECUTE, ALTER ROUTINE ON PROCEDURE testProc FROM 'userTest'@'localhost';
128
129 --error 0,ER_NONEXISTING_PROC_GRANT
130 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'userTest'@'localhost';
131
132 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'userTest1'@'localhost';
133
134 --disable_warnings
135 DROP PROCEDURE testProc;
136 --enable_warnings
137 DROP PROCEDURE testProc1;
138
139 DROP USER 'userTest'@'localhost';
140 DROP USER 'userTest1'@'localhost';
141
142 DROP TABLE t1;