Debian Codegraph @

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

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