Debian Codegraph @

mysql-test/suite/compat/oracle/t/sp-package-security.test

raw
1 --source include/not_embedded.inc
2 --source include/default_charset.inc
3
4 SET sql_mode=ORACLE;
5
6 CREATE DATABASE db1;
7 CREATE USER u1@localhost IDENTIFIED BY '';
8 GRANT SELECT ON db1.* TO u1@localhost;
9
10 connect (conn1,localhost,u1,,db1);
11 SELECT CURRENT_USER;
12 SET sql_mode=ORACLE;
13
14 --echo #
15 --echo # User u1 cannot drop PROCEDURE, PACKAGE, PACKAGE BODY by default
16 --echo #
17
18 --error ER_PROCACCESS_DENIED_ERROR
19 DROP PROCEDURE p1;
20 --error ER_PROCACCESS_DENIED_ERROR
21 DROP PACKAGE pkg1;
22 --error ER_PROCACCESS_DENIED_ERROR
23 DROP PACKAGE BODY pkg1;
24
25 --echo #
26 --echo # User u1 cannot create PROCEDURE, PACKAGE, PACKAGE BODY by default
27 --echo #
28
29 DELIMITER $$;
30 --error ER_DBACCESS_DENIED_ERROR
31 CREATE PROCEDURE p1 AS
32 BEGIN
33 NULL;
34 END;
35 $$
36 DELIMITER ;$$
37
38 DELIMITER $$;
39 --error ER_DBACCESS_DENIED_ERROR
40 CREATE PACKAGE pkg1 AS
41 PROCEDURE p1;
42 END;
43 $$
44 DELIMITER ;$$
45
46 # TODO: this should probably return ER_DBACCESS_DENIED_ERROR
47 DELIMITER $$;
48 --error ER_SP_DOES_NOT_EXIST
49 CREATE PACKAGE BODY pkg1 AS
50 PROCEDURE p1 AS BEGIN NULL; END;
51 END;
52 $$
53 DELIMITER ;$$
54
55
56 --echo #
57 --echo # Now create a PACKAGE by root
58 --echo #
59
60 connection default;
61 USE db1;
62
63 DELIMITER $$;
64 CREATE PROCEDURE p1root AS
65 BEGIN
66 SELECT 1;
67 END;
68 $$
69 DELIMITER ;$$
70
71 DELIMITER $$;
72 CREATE PACKAGE pkg1 AS
73 PROCEDURE p1;
74 FUNCTION f1 RETURN TEXT;
75 END;
76 $$
77 DELIMITER ;$$
78 SHOW CREATE PACKAGE pkg1;
79
80 --echo #
81 --echo # u1 cannot SHOW yet:
82 --echo # - the standalone procedure earlier created by root
83 --echo # - the package specifications earlier create by root
84 --echo #
85
86 connection conn1;
87 --error ER_SP_DOES_NOT_EXIST
88 SHOW CREATE PROCEDURE p1root;
89 --error ER_SP_DOES_NOT_EXIST
90 SHOW CREATE PACKAGE pkg1;
91
92
93 --echo #
94 --echo # User u1 still cannot create a PACKAGE BODY
95 --echo #
96
97 connection conn1;
98 DELIMITER $$;
99 --error ER_DBACCESS_DENIED_ERROR
100 CREATE PACKAGE BODY pkg1 AS
101 PROCEDURE p1 AS BEGIN NULL; END;
102 FUNCTION f1 RETURN TEXT AS BEGIN RETURN 'This is f1'; END;
103 END;
104 $$
105 DELIMITER ;$$
106
107
108 --echo #
109 --echo # Now grant EXECUTE:
110 --echo # - on the standalone procedure earlier created by root
111 --echo # - on the package specification earlier created by root
112 --echo #
113 connection default;
114 GRANT EXECUTE ON PROCEDURE db1.p1root TO u1@localhost;
115 GRANT EXECUTE ON PACKAGE db1.pkg1 TO u1@localhost;
116
117 --echo #
118 --echo # Now u1 can do SHOW for:
119 --echo # - the standalone procedure earlier created by root
120 --echo # - the package specification earlier created by root
121 --echo #
122
123 disconnect conn1;
124 connect (conn1,localhost,u1,,db1);
125 SET sql_mode=ORACLE;
126 SHOW CREATE PROCEDURE db1.p1root;
127 SHOW CREATE PACKAGE db1.pkg1;
128
129
130 --echo #
131 --echo # Now revoke EXECUTE and grant CREATE ROUTINE instead
132 --echo #
133
134 connection default;
135 REVOKE EXECUTE ON PROCEDURE db1.p1root FROM u1@localhost;
136 REVOKE EXECUTE ON PACKAGE db1.pkg1 FROM u1@localhost;
137 GRANT CREATE ROUTINE ON db1.* TO u1@localhost;
138
139 --echo #
140 --echo # Reconnect u1 to make new grants have effect
141 --echo #
142
143 disconnect conn1;
144 connect (conn1,localhost,u1,,db1);
145 SET sql_mode=ORACLE;
146
147 --echo #
148 --echo # Now u1 can SHOW:
149 --echo # - standalone routines earlier created by root
150 --echo # - package specifications earlier created by root
151 --echo #
152 SHOW CREATE PROCEDURE p1root;
153 SHOW CREATE PACKAGE pkg1;
154
155 --echo #
156 --echo # Now u1 can CREATE, DROP and EXECUTE its own standalone procedures
157 --echo #
158
159 DELIMITER $$;
160 CREATE PROCEDURE p1 AS
161 BEGIN
162 NULL;
163 END;
164 $$
165 DELIMITER ;$$
166 SHOW GRANTS;
167 CALL p1;
168 DROP PROCEDURE p1;
169 SHOW GRANTS;
170
171 --echo #
172 --echo # Now u1 can also CREATE, DROP its own package specifications
173 --echo #
174
175 DELIMITER $$;
176 CREATE PACKAGE pkg2 AS
177 PROCEDURE p1;
178 FUNCTION f1 RETURN TEXT;
179 END;
180 $$
181 DELIMITER ;$$
182 SHOW CREATE PACKAGE pkg2;
183 SHOW GRANTS;
184 DROP PACKAGE pkg2;
185 SHOW GRANTS;
186
187
188 --echo #
189 --echo # Now u1 can also CREATE, DROP package bodies and EXECUTE package body routines
190 --echo #
191
192 DELIMITER $$;
193 CREATE PACKAGE BODY pkg1 AS
194 PROCEDURE p1 AS BEGIN SELECT 'This is pkg1.p1' AS `comment`; END;
195 FUNCTION f1 RETURN TEXT AS BEGIN RETURN 'This is pkg1.f1'; END;
196 END;
197 $$
198 DELIMITER ;$$
199 SHOW CREATE PACKAGE pkg1;
200 SHOW CREATE PACKAGE BODY pkg1;
201 SHOW GRANTS;
202 CALL pkg1.p1;
203 SELECT pkg1.f1();
204 DROP PACKAGE BODY pkg1;
205 SHOW GRANTS;
206
207 --echo #
208 --echo # Now create a PACKAGE BODY by root.
209 --echo # u1 does not have EXECUTE access by default.
210 --echo #
211
212 connection default;
213 DELIMITER $$;
214 CREATE PACKAGE BODY pkg1 AS
215 PROCEDURE p1 AS BEGIN SELECT 'This is pkg1.p1' AS `comment`; END;
216 FUNCTION f1 RETURN TEXT AS BEGIN RETURN 'This is pkg1.f1'; END;
217 END;
218 $$
219 DELIMITER ;$$
220
221 connection conn1;
222 SHOW CREATE PACKAGE pkg1;
223 SHOW CREATE PACKAGE BODY pkg1;
224 --error ER_PROCACCESS_DENIED_ERROR
225 CALL pkg1.p1;
226 --error ER_PROCACCESS_DENIED_ERROR
227 SELECT pkg1.f1();
228
229 --echo #
230 --echo # Now grant EXECUTE to u1 on the PACKAGE BODY created by root
231 --echo #
232
233 connection default;
234 GRANT EXECUTE ON PACKAGE BODY db1.pkg1 TO u1@localhost;
235 disconnect conn1;
236 connect (conn1,localhost,u1,,db1);
237 SELECT CURRENT_USER;
238 SET sql_mode=ORACLE;
239 SHOW GRANTS;
240 CALL pkg1.p1;
241 SELECT pkg1.f1();
242
243 connection default;
244 DROP PACKAGE BODY pkg1;
245
246
247 --echo #
248 --echo # u1 still cannot DROP the package specification earlier created by root.
249 --echo #
250
251 connection conn1;
252 --error ER_PROCACCESS_DENIED_ERROR
253 DROP PACKAGE pkg1;
254
255 --echo #
256 --echo # Grant ALTER ROUTINE to u1
257 --echo #
258
259 connection default;
260 GRANT ALTER ROUTINE ON db1.* TO u1@localhost;
261
262 --echo #
263 --echo # Now u1 can DROP:
264 --echo # - the standalone procedure earlier created by root
265 --echo # - the package specification earlier created by root
266 --echo #
267
268 disconnect conn1;
269 connect (conn1,localhost,u1,,db1);
270 SET sql_mode=ORACLE;
271 DROP PACKAGE pkg1;
272 DROP PROCEDURE p1root;
273
274 disconnect conn1;
275 connection default;
276
277 DROP USER u1@localhost;
278 DROP DATABASE db1;
279 USE test;
280
281
282 --echo #
283 --echo # Creator=root, definer=xxx
284 --echo #
285
286 CREATE USER xxx@localhost;
287 DELIMITER $$;
288 CREATE DEFINER=xxx@localhost PACKAGE p1 AS
289 PROCEDURE p1;
290 END;
291 $$
292 CREATE DEFINER=xxx@localhost PACKAGE BODY p1 AS
293 PROCEDURE p1 AS
294 BEGIN
295 SELECT SESSION_USER(), CURRENT_USER(), 'p1.p1' AS msg;
296 END;
297 BEGIN
298 SELECT SESSION_USER(), CURRENT_USER(), 'package body p1' AS msg;
299 END;
300 $$
301 DELIMITER ;$$
302 --error ER_PROCACCESS_DENIED_ERROR
303 CALL p1.p1;
304 GRANT EXECUTE ON PACKAGE BODY test.p1 TO xxx@localhost;
305 CALL p1.p1;
306 DROP PACKAGE p1;
307 DROP USER xxx@localhost;
308
309
310 --echo #
311 --echo # Creator=root, definer=xxx, SQL SECURITY INVOKER
312 --echo #
313
314 CREATE USER xxx@localhost;
315 DELIMITER $$;
316 CREATE DEFINER=xxx@localhost PACKAGE p1 AS
317 PROCEDURE p1;
318 END;
319 $$
320 CREATE DEFINER=xxx@localhost PACKAGE BODY p1 SQL SECURITY INVOKER AS
321 PROCEDURE p1 AS
322 BEGIN
323 SELECT SESSION_USER(), CURRENT_USER(), 'p1.p1' AS msg;
324 END;
325 BEGIN
326 SELECT SESSION_USER(), CURRENT_USER(), 'package body p1' AS msg;
327 END;
328 $$
329 DELIMITER ;$$
330 CALL p1.p1;
331 DROP PACKAGE p1;
332 DROP USER xxx@localhost;