Debian Codegraph @

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

raw
1 --source include/not_embedded.inc
2
3 SET sql_mode=ORACLE;
4
5 --echo #
6 --echo # MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations
7 --echo #
8
9
10 --echo #
11 --echo # Initiation:
12 --echo # - creating database db1
13 --echo # - creating user user1 with access rights to db1
14 --echo #
15
16 CREATE DATABASE db1;
17 CREATE TABLE db1.t1 (a INT, b VARCHAR(10));
18
19 CREATE USER user1;
20
21 GRANT ALL PRIVILEGES ON test.* TO user1;
22
23 connect (conn1,localhost,user1,,test);
24 SET sql_mode=ORACLE;
25
26 SELECT database();
27 SELECT user();
28
29 --echo #
30 --echo # Making sure that user1 does not have privileges to db1.t1
31 --echo #
32
33 --error ER_TABLEACCESS_DENIED_ERROR
34 SHOW CREATE TABLE db1.t1;
35 --error ER_TABLEACCESS_DENIED_ERROR
36 SHOW FIELDS IN db1.t1;
37
38
39 --echo #
40 --echo # Trigger: using %TYPE with a table we don't have access to
41 --echo #
42 CREATE TABLE test.t1 (a INT, b INT);
43 INSERT INTO test.t1 (a,b) VALUES (10,20);
44 SELECT * FROM t1;
45 DELIMITER $$;
46 CREATE TRIGGER test.tr1 BEFORE INSERT ON test.t1 FOR EACH ROW
47 BEGIN
48 DECLARE b db1.t1.b%TYPE := 20;
49 BEGIN
50 :NEW.b := 10;
51 END;
52 END
53 $$
54 DELIMITER ;$$
55 --error ER_TABLEACCESS_DENIED_ERROR
56 INSERT INTO t1 (a) VALUES (10);
57 SELECT * FROM t1;
58 DROP TRIGGER tr1;
59 DROP TABLE t1;
60
61
62 --echo #
63 --echo # Stored procedure: Using %TYPE for with a table that we don't have access to
64 --echo # DEFINER user1, SQL SECURITY DEFAULT
65 --echo #
66
67 DELIMITER $$;
68 CREATE PROCEDURE p1()
69 AS
70 a db1.t1.a%TYPE := 10;
71 BEGIN
72 SELECT a;
73 END;
74 $$
75 DELIMITER ;$$
76 --error ER_TABLEACCESS_DENIED_ERROR
77 CALL p1;
78 DROP PROCEDURE p1;
79
80 DELIMITER $$;
81 CREATE PROCEDURE p1()
82 AS
83 a db1.t1%ROWTYPE;
84 BEGIN
85 SELECT a.a;
86 END;
87 $$
88 DELIMITER ;$$
89 --error ER_TABLEACCESS_DENIED_ERROR
90 CALL p1;
91 DROP PROCEDURE p1;
92
93
94 --echo #
95 --echo # Stored procedure: Using %TYPE for with a table that we don't have access to
96 --echo # DEFINER root, SQL SECURITY INVOKER
97 --echo #
98
99 connection default;
100 DELIMITER $$;
101 CREATE PROCEDURE p1()
102 SQL SECURITY INVOKER
103 AS
104 a db1.t1.a%TYPE := 10;
105 BEGIN
106 SELECT a;
107 END;
108 $$
109 DELIMITER ;$$
110 connection conn1;
111 --error ER_TABLEACCESS_DENIED_ERROR
112 CALL p1;
113 DROP PROCEDURE p1;
114
115
116 connection default;
117 DELIMITER $$;
118 CREATE PROCEDURE p1()
119 SQL SECURITY INVOKER
120 AS
121 a db1.t1%ROWTYPE;
122 BEGIN
123 SELECT a.a;
124 END;
125 $$
126 DELIMITER ;$$
127 connection conn1;
128 --error ER_TABLEACCESS_DENIED_ERROR
129 CALL p1;
130 DROP PROCEDURE p1;
131
132
133 --echo #
134 --echo # Stored procedure: Using %TYPE for with a table that we don't have access to
135 --echo # DEFINER root, SQL SECURITY DEFINER
136 --echo #
137
138 connection default;
139 DELIMITER $$;
140 CREATE PROCEDURE p1()
141 SQL SECURITY DEFINER
142 AS
143 a db1.t1.a%TYPE := 10;
144 BEGIN
145 SELECT a;
146 END;
147 $$
148 DELIMITER ;$$
149 connection conn1;
150 CALL p1;
151 DROP PROCEDURE p1;
152
153 connection default;
154 DELIMITER $$;
155 CREATE PROCEDURE p1()
156 SQL SECURITY DEFINER
157 AS
158 a db1.t1%ROWTYPE;
159 BEGIN
160 a.a:= 10;
161 SELECT a.a;
162 END;
163 $$
164 DELIMITER ;$$
165 connection conn1;
166 CALL p1;
167 DROP PROCEDURE p1;
168
169
170 --echo #
171 --echo # Stored function: Using %TYPE for with a table that we don't have access to
172 --echo # DEFINER user1, SQL SECURITY DEFAULT
173 --echo #
174
175 CREATE TABLE t1 (a INT);
176 DELIMITER $$;
177 CREATE FUNCTION f1() RETURN INT
178 AS
179 a db1.t1.a%TYPE:=0;
180 BEGIN
181 RETURN OCTET_LENGTH(a);
182 END;
183 $$
184 DELIMITER ;$$
185 --error ER_TABLEACCESS_DENIED_ERROR
186 SELECT f1();
187 DROP FUNCTION f1;
188 DROP TABLE t1;
189
190
191 --echo #
192 --echo # Stored function: Using %TYPE for with a table that we don't have access to
193 --echo # DEFINER root, SQL SECURITY INVOKER
194 --echo #
195
196 connection default;
197 CREATE TABLE t1 (a INT);
198 DELIMITER $$;
199 CREATE FUNCTION f1() RETURN INT
200 SQL SECURITY INVOKER
201 AS
202 a db1.t1.a%TYPE:=0;
203 BEGIN
204 RETURN OCTET_LENGTH(a);
205 END;
206 $$
207 DELIMITER ;$$
208 connection conn1;
209 --error ER_TABLEACCESS_DENIED_ERROR
210 SELECT f1();
211 DROP FUNCTION f1;
212 DROP TABLE t1;
213
214
215 --echo #
216 --echo # Stored function: Using %TYPE for with a table that we don't have access to
217 --echo # DEFINER root, SQL SECURITY DEFINER
218 --echo #
219
220 connection default;
221 CREATE TABLE t1 (a INT);
222 DELIMITER $$;
223 CREATE FUNCTION f1() RETURN INT
224 SQL SECURITY DEFINER
225 AS
226 a db1.t1.a%TYPE:=0;
227 BEGIN
228 RETURN OCTET_LENGTH(a);
229 END;
230 $$
231 DELIMITER ;$$
232 connection conn1;
233 SELECT f1();
234 DROP FUNCTION f1;
235 DROP TABLE t1;
236
237
238 connection default;
239 GRANT SELECT (a) ON db1.t1 TO user1;
240 connection conn1;
241
242 --echo #
243 --echo # Making sure that user1 has access to db1.t1.a, but not to db1.t1.b
244 --echo #
245
246 --error ER_TABLEACCESS_DENIED_ERROR
247 SHOW CREATE TABLE db1.t1;
248 SHOW FIELDS IN db1.t1;
249
250 --echo #
251 --echo # Trigger: Per-column privileges
252 --echo #
253 CREATE TABLE test.t1 (a INT, b INT);
254 INSERT INTO test.t1 (a,b) VALUES (10,20);
255 SELECT * FROM t1;
256 # %TYPE reference using a column we have access to
257 DELIMITER $$;
258 CREATE TRIGGER test.tr1 BEFORE INSERT ON test.t1 FOR EACH ROW
259 BEGIN
260 DECLARE a db1.t1.a%TYPE := 20;
261 BEGIN
262 :NEW.b := 10;
263 END;
264 END
265 $$
266 DELIMITER ;$$
267 INSERT INTO t1 (a) VALUES (10);
268 SELECT * FROM t1;
269 DROP TRIGGER tr1;
270 # %TYPE reference using a column that we don't have access to
271 DELIMITER $$;
272 CREATE TRIGGER test.tr1 BEFORE INSERT ON test.t1 FOR EACH ROW
273 BEGIN
274 DECLARE b db1.t1.b%TYPE := 20;
275 BEGIN
276 :NEW.b := 10;
277 END;
278 END
279 $$
280 DELIMITER ;$$
281 --error ER_COLUMNACCESS_DENIED_ERROR
282 INSERT INTO t1 (a) VALUES (10);
283 SELECT * FROM t1;
284 DROP TRIGGER tr1;
285 DROP TABLE t1;
286
287
288
289 --echo #
290 --echo # Stored procedure: Per-column privileges
291 --echo # DEFINER user1, SQL SECURITY DEFAULT
292 --echo #
293
294 DELIMITER $$;
295 CREATE PROCEDURE p1()
296 AS
297 a db1.t1.a%TYPE := 10;
298 BEGIN
299 SELECT a;
300 END;
301 $$
302 DELIMITER ;$$
303 CALL p1;
304 DROP PROCEDURE p1;
305
306 DELIMITER $$;
307 CREATE PROCEDURE p1()
308 AS
309 b db1.t1.b%TYPE := 10;
310 BEGIN
311 SELECT b;
312 END;
313 $$
314 DELIMITER ;$$
315 --error ER_COLUMNACCESS_DENIED_ERROR
316 CALL p1;
317 DROP PROCEDURE p1;
318
319 DELIMITER $$;
320 CREATE PROCEDURE p1()
321 AS
322 b db1.t1%ROWTYPE;
323 BEGIN
324 b.b:=10;
325 SELECT b.b;
326 END;
327 $$
328 DELIMITER ;$$
329 --error ER_COLUMNACCESS_DENIED_ERROR
330 CALL p1;
331 DROP PROCEDURE p1;
332
333
334 --echo #
335 --echo # Clean up
336 --echo #
337 disconnect conn1;
338 connection default;
339
340 DROP USER user1;
341 DROP DATABASE db1;
342
343 --echo #
344 --echo # End of MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations
345 --echo #