Debian Codegraph @

mysql-test/suite/compat/oracle/r/sp-package-security.result

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