Debian Codegraph @

mysql-test/suite/funcs_1/r/memory_trig_03.result

raw
1 SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
2 USE test;
3 drop table if exists tb3;
4 create table tb3 (
5 f118 char not null DEFAULT 'a',
6 f119 char binary not null DEFAULT b'101',
7 f120 char ascii not null DEFAULT b'101',
8 f121 char(50),
9 f122 char(50),
10 f129 binary not null DEFAULT b'101',
11 f130 tinyint not null DEFAULT 99,
12 f131 tinyint unsigned not null DEFAULT 99,
13 f132 tinyint zerofill not null DEFAULT 99,
14 f133 tinyint unsigned zerofill not null DEFAULT 99,
15 f134 smallint not null DEFAULT 999,
16 f135 smallint unsigned not null DEFAULT 999,
17 f136 smallint zerofill not null DEFAULT 999,
18 f137 smallint unsigned zerofill not null DEFAULT 999,
19 f138 mediumint not null DEFAULT 9999,
20 f139 mediumint unsigned not null DEFAULT 9999,
21 f140 mediumint zerofill not null DEFAULT 9999,
22 f141 mediumint unsigned zerofill not null DEFAULT 9999,
23 f142 int not null DEFAULT 99999,
24 f143 int unsigned not null DEFAULT 99999,
25 f144 int zerofill not null DEFAULT 99999,
26 f145 int unsigned zerofill not null DEFAULT 99999,
27 f146 bigint not null DEFAULT 999999,
28 f147 bigint unsigned not null DEFAULT 999999,
29 f148 bigint zerofill not null DEFAULT 999999,
30 f149 bigint unsigned zerofill not null DEFAULT 999999,
31 f150 decimal not null DEFAULT 999.999,
32 f151 decimal unsigned not null DEFAULT 999.17,
33 f152 decimal zerofill not null DEFAULT 999.999,
34 f153 decimal unsigned zerofill,
35 f154 decimal (0),
36 f155 decimal (64),
37 f156 decimal (0) unsigned,
38 f157 decimal (64) unsigned,
39 f158 decimal (0) zerofill,
40 f159 decimal (64) zerofill,
41 f160 decimal (0) unsigned zerofill,
42 f161 decimal (64) unsigned zerofill,
43 f162 decimal (0,0),
44 f163 decimal (63,30),
45 f164 decimal (0,0) unsigned,
46 f165 decimal (63,30) unsigned,
47 f166 decimal (0,0) zerofill,
48 f167 decimal (63,30) zerofill,
49 f168 decimal (0,0) unsigned zerofill,
50 f169 decimal (63,30) unsigned zerofill,
51 f170 numeric,
52 f171 numeric unsigned,
53 f172 numeric zerofill,
54 f173 numeric unsigned zerofill,
55 f174 numeric (0),
56 f175 numeric (64)
57 ) engine = <engine_to_be_used>;
58 Warnings:
59 Note 1265 Data truncated for column 'f150' at row 0
60 Note 1265 Data truncated for column 'f151' at row 0
61 Note 1265 Data truncated for column 'f152' at row 0
62
63 Testcase 3.5.3:
64 ---------------
65 drop database if exists priv_db;
66 create database priv_db;
67 use priv_db;
68 create table t1 (f1 char(20)) engine= <engine_to_be_used>;
69 create User test_noprivs@localhost;
70 set password for test_noprivs@localhost = password('PWD');
71 create User test_yesprivs@localhost;
72 set password for test_yesprivs@localhost = password('PWD');
73
74 Testcase 3.5.3.2/6:
75 -------------------
76 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
77 grant ALL on *.* to test_noprivs@localhost;
78 revoke TRIGGER on *.* from test_noprivs@localhost;
79 show grants for test_noprivs@localhost;
80 Grants for test_noprivs@localhost
81 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, BINLOG MONITOR, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, CREATE TABLESPACE, DELETE HISTORY, SET USER, FEDERATED ADMIN, CONNECTION ADMIN, READ_ONLY ADMIN, REPLICATION SLAVE ADMIN, REPLICATION MASTER ADMIN, BINLOG ADMIN, BINLOG REPLAY, SLAVE MONITOR, SHOW CREATE ROUTINE ON *.* TO `test_noprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
82 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
83 grant TRIGGER on *.* to test_yesprivs@localhost;
84 grant SELECT on priv_db.t1 to test_yesprivs@localhost;
85 show grants for test_yesprivs@localhost;
86 Grants for test_yesprivs@localhost
87 GRANT TRIGGER ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
88 GRANT SELECT ON `priv_db`.`t1` TO `test_yesprivs`@`localhost`
89 connect no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
90 connect yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
91 connection default;
92
93 Testcase 3.5.3.2:
94 -----------------
95 connection no_privs;
96 select current_user;
97 current_user
98 test_noprivs@localhost
99 use priv_db;
100 create trigger trg1_1 before INSERT on t1 for each row
101 set new.f1 = 'trig 3.5.3.2_1-no';
102 ERROR 42000: TRIGGER command denied to user 'test_noprivs'@'localhost' for table `priv_db`.`t1`
103 connection default;
104 use priv_db;
105 insert into t1 (f1) values ('insert 3.5.3.2-no');
106 select f1 from t1 order by f1;
107 f1
108 insert 3.5.3.2-no
109 connection yes_privs;
110 select current_user;
111 current_user
112 test_yesprivs@localhost
113 use priv_db;
114 create trigger trg1_2 before INSERT on t1 for each row
115 set new.f1 = 'trig 3.5.3.2_2-yes';
116 connection default;
117 select current_user;
118 current_user
119 root@localhost
120 use priv_db;
121 insert into t1 (f1) values ('insert 3.5.3.2-yes');
122 ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for column 'f1' in table 't1'
123 select f1 from t1 order by f1;
124 f1
125 insert 3.5.3.2-no
126 grant UPDATE on priv_db.t1 to test_yesprivs@localhost;
127 insert into t1 (f1) values ('insert 3.5.3.2-yes');
128 select f1 from t1 order by f1;
129 f1
130 insert 3.5.3.2-no
131 trig 3.5.3.2_2-yes
132
133 Testcase 3.5.3.6:
134 -----------------
135 connection no_privs;
136 use priv_db;
137 drop trigger trg1_2;
138 ERROR 42000: TRIGGER command denied to user 'test_noprivs'@'localhost' for table `priv_db`.`t1`
139 connection default;
140 use priv_db;
141 insert into t1 (f1) values ('insert 3.5.3.6-yes');
142 select f1 from t1 order by f1;
143 f1
144 insert 3.5.3.2-no
145 trig 3.5.3.2_2-yes
146 trig 3.5.3.2_2-yes
147 connection yes_privs;
148 use priv_db;
149 drop trigger trg1_2;
150 connection default;
151 use priv_db;
152 insert into t1 (f1) values ('insert 3.5.3.6-no');
153 select f1 from t1 order by f1;
154 f1
155 insert 3.5.3.2-no
156 insert 3.5.3.6-no
157 trig 3.5.3.2_2-yes
158 trig 3.5.3.2_2-yes
159 connection default;
160 drop trigger trg1_2;
161 disconnect no_privs;
162 disconnect yes_privs;
163
164 Testcase 3.5.3.7a:
165 ------------------
166 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
167 grant ALL on *.* to test_noprivs@localhost;
168 revoke UPDATE on *.* from test_noprivs@localhost;
169 show grants for test_noprivs@localhost;
170 Grants for test_noprivs@localhost
171 GRANT SELECT, INSERT, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, BINLOG MONITOR, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, DELETE HISTORY, SET USER, FEDERATED ADMIN, CONNECTION ADMIN, READ_ONLY ADMIN, REPLICATION SLAVE ADMIN, REPLICATION MASTER ADMIN, BINLOG ADMIN, BINLOG REPLAY, SLAVE MONITOR, SHOW CREATE ROUTINE ON *.* TO `test_noprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
172 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
173 grant TRIGGER, UPDATE on *.* to test_yesprivs@localhost;
174 show grants for test_yesprivs@localhost;
175 Grants for test_yesprivs@localhost
176 GRANT UPDATE, TRIGGER ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
177 connect no_privs_424a,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
178 connect yes_privs_424a,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
179 connection no_privs_424a;
180 select current_user;
181 current_user
182 test_noprivs@localhost
183 use priv_db;
184 show grants;
185 Grants for test_noprivs@localhost
186 GRANT SELECT, INSERT, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, BINLOG MONITOR, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, DELETE HISTORY, SET USER, FEDERATED ADMIN, CONNECTION ADMIN, READ_ONLY ADMIN, REPLICATION SLAVE ADMIN, REPLICATION MASTER ADMIN, BINLOG ADMIN, BINLOG REPLAY, SLAVE MONITOR, SHOW CREATE ROUTINE ON *.* TO `test_noprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
187 select f1 from t1 order by f1;
188 f1
189 insert 3.5.3.2-no
190 insert 3.5.3.6-no
191 trig 3.5.3.2_2-yes
192 trig 3.5.3.2_2-yes
193 create trigger trg4a_1 before INSERT on t1 for each row
194 set new.f1 = 'trig 3.5.3.7-1a';
195 connection default;
196 insert into t1 (f1) values ('insert 3.5.3.7-1a');
197 ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
198 select f1 from t1 order by f1;
199 f1
200 insert 3.5.3.2-no
201 insert 3.5.3.6-no
202 trig 3.5.3.2_2-yes
203 trig 3.5.3.2_2-yes
204 drop trigger trg4a_1;
205 connection yes_privs_424a;
206 use priv_db;
207 select current_user;
208 current_user
209 test_yesprivs@localhost
210 show grants;
211 Grants for test_yesprivs@localhost
212 GRANT UPDATE, TRIGGER ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
213 create trigger trg4a_2 before INSERT on t1 for each row
214 set new.f1 = 'trig 3.5.3.7-2a';
215 connection default;
216 insert into t1 (f1) values ('insert 3.5.3.7-2b');
217 select f1 from t1 order by f1;
218 f1
219 insert 3.5.3.2-no
220 insert 3.5.3.6-no
221 trig 3.5.3.2_2-yes
222 trig 3.5.3.2_2-yes
223 trig 3.5.3.7-2a
224 drop trigger trg4a_2;
225 disconnect no_privs_424a;
226 disconnect yes_privs_424a;
227
228 Testcase 3.5.3.7b:
229 ------------------
230 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
231 grant TRIGGER on *.* to test_noprivs;
232 grant ALL on priv_db.* to test_noprivs@localhost;
233 revoke UPDATE on priv_db.* from test_noprivs@localhost;
234 show grants for test_noprivs;
235 Grants for test_noprivs@%
236 GRANT TRIGGER ON *.* TO `test_noprivs`@`%`
237 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
238 grant TRIGGER on *.* to test_yesprivs@localhost;
239 grant UPDATE on priv_db.* to test_yesprivs@localhost;
240 show grants for test_yesprivs@localhost;
241 Grants for test_yesprivs@localhost
242 GRANT TRIGGER ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
243 GRANT UPDATE ON `priv_db`.* TO `test_yesprivs`@`localhost`
244 connect no_privs_424b,localhost,test_noprivs,PWD,priv_db,$MASTER_MYPORT,$MASTER_MYSOCK;
245 connect yes_privs_424b,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
246 connection default;
247 connection no_privs_424b;
248 show grants;
249 Grants for test_noprivs@localhost
250 GRANT USAGE ON *.* TO `test_noprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
251 GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER, DELETE HISTORY, SHOW CREATE ROUTINE ON `priv_db`.* TO `test_noprivs`@`localhost`
252 use priv_db;
253 create trigger trg4b_1 before UPDATE on t1 for each row
254 set new.f1 = 'trig 3.5.3.7-1b';
255 connection default;
256 insert into t1 (f1) values ('insert 3.5.3.7-1b');
257 select f1 from t1 order by f1;
258 f1
259 insert 3.5.3.2-no
260 insert 3.5.3.6-no
261 insert 3.5.3.7-1b
262 trig 3.5.3.2_2-yes
263 trig 3.5.3.2_2-yes
264 trig 3.5.3.7-2a
265 update t1 set f1 = 'update 3.5.3.7-1b' where f1 = 'insert 3.5.3.7-1b';
266 ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
267 select f1 from t1 order by f1;
268 f1
269 insert 3.5.3.2-no
270 insert 3.5.3.6-no
271 insert 3.5.3.7-1b
272 trig 3.5.3.2_2-yes
273 trig 3.5.3.2_2-yes
274 trig 3.5.3.7-2a
275 drop trigger trg4b_1;
276 connection yes_privs_424b;
277 show grants;
278 Grants for test_yesprivs@localhost
279 GRANT TRIGGER ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
280 GRANT UPDATE ON `priv_db`.* TO `test_yesprivs`@`localhost`
281 use priv_db;
282 create trigger trg4b_2 before UPDATE on t1 for each row
283 set new.f1 = 'trig 3.5.3.7-2b';
284 connection default;
285 insert into t1 (f1) values ('insert 3.5.3.7-2b');
286 select f1 from t1 order by f1;
287 f1
288 insert 3.5.3.2-no
289 insert 3.5.3.6-no
290 insert 3.5.3.7-1b
291 insert 3.5.3.7-2b
292 trig 3.5.3.2_2-yes
293 trig 3.5.3.2_2-yes
294 trig 3.5.3.7-2a
295 update t1 set f1 = 'update 3.5.3.7-2b' where f1 = 'insert 3.5.3.7-2b';
296 select f1 from t1 order by f1;
297 f1
298 insert 3.5.3.2-no
299 insert 3.5.3.6-no
300 insert 3.5.3.7-1b
301 trig 3.5.3.2_2-yes
302 trig 3.5.3.2_2-yes
303 trig 3.5.3.7-2a
304 trig 3.5.3.7-2b
305 drop trigger trg4b_2;
306 disconnect no_privs_424b;
307 disconnect yes_privs_424b;
308
309 Testcase 3.5.3.7c
310 -----------------
311 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
312 grant TRIGGER on *.* to test_noprivs@localhost;
313 grant ALL on priv_db.t1 to test_noprivs@localhost;
314 revoke UPDATE on priv_db.t1 from test_noprivs@localhost;
315 show grants for test_noprivs;
316 Grants for test_noprivs@%
317 GRANT TRIGGER ON *.* TO `test_noprivs`@`%`
318 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
319 grant TRIGGER on *.* to test_yesprivs@localhost;
320 grant UPDATE on priv_db.t1 to test_yesprivs@localhost;
321 show grants for test_yesprivs@localhost;
322 Grants for test_yesprivs@localhost
323 GRANT TRIGGER ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
324 GRANT UPDATE ON `priv_db`.`t1` TO `test_yesprivs`@`localhost`
325 connect no_privs_424c,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
326 connect yes_privs_424c,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
327 connection default;
328 connection no_privs_424c;
329 show grants;
330 Grants for test_noprivs@localhost
331 GRANT TRIGGER ON *.* TO `test_noprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
332 GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER, DELETE HISTORY ON `priv_db`.`t1` TO `test_noprivs`@`localhost`
333 use priv_db;
334 create trigger trg4c_1 before INSERT on t1 for each row
335 set new.f1 = 'trig 3.5.3.7-1c';
336 connection default;
337 insert into t1 (f1) values ('insert 3.5.3.7-1c');
338 ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
339 select f1 from t1 order by f1;
340 f1
341 insert 3.5.3.2-no
342 insert 3.5.3.6-no
343 insert 3.5.3.7-1b
344 trig 3.5.3.2_2-yes
345 trig 3.5.3.2_2-yes
346 trig 3.5.3.7-2a
347 trig 3.5.3.7-2b
348 drop trigger trg4c_1;
349 connection yes_privs_424c;
350 show grants;
351 Grants for test_yesprivs@localhost
352 GRANT TRIGGER ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
353 GRANT UPDATE ON `priv_db`.`t1` TO `test_yesprivs`@`localhost`
354 use priv_db;
355 create trigger trg4c_2 before INSERT on t1 for each row
356 set new.f1 = 'trig 3.5.3.7-2c';
357 connection default;
358 insert into t1 (f1) values ('insert 3.5.3.7-2c');
359 select f1 from t1 order by f1;
360 f1
361 insert 3.5.3.2-no
362 insert 3.5.3.6-no
363 insert 3.5.3.7-1b
364 trig 3.5.3.2_2-yes
365 trig 3.5.3.2_2-yes
366 trig 3.5.3.7-2a
367 trig 3.5.3.7-2b
368 trig 3.5.3.7-2c
369 drop trigger trg4c_2;
370 disconnect no_privs_424c;
371 disconnect yes_privs_424c;
372
373 Testcase 3.5.3.7d:
374 ------------------
375 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
376 grant TRIGGER on *.* to test_noprivs@localhost;
377 grant SELECT (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
378 show grants for test_noprivs;
379 Grants for test_noprivs@%
380 GRANT TRIGGER ON *.* TO `test_noprivs`@`%`
381 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
382 grant TRIGGER on *.* to test_yesprivs@localhost;
383 grant UPDATE (f1) on priv_db.t1 to test_yesprivs@localhost;
384 show grants for test_noprivs;
385 Grants for test_noprivs@%
386 GRANT TRIGGER ON *.* TO `test_noprivs`@`%`
387 connect no_privs_424d,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
388 connect yes_privs_424d,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
389 connection default;
390 connection no_privs_424d;
391 show grants;
392 Grants for test_noprivs@localhost
393 GRANT TRIGGER ON *.* TO `test_noprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
394 GRANT SELECT (`f1`), INSERT (`f1`) ON `priv_db`.`t1` TO `test_noprivs`@`localhost`
395 use priv_db;
396 create trigger trg4d_1 before INSERT on t1 for each row
397 set new.f1 = 'trig 3.5.3.7-1d';
398 connection default;
399 insert into t1 (f1) values ('insert 3.5.3.7-1d');
400 ERROR 42000: UPDATE command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
401 select f1 from t1 order by f1;
402 f1
403 insert 3.5.3.2-no
404 insert 3.5.3.6-no
405 insert 3.5.3.7-1b
406 trig 3.5.3.2_2-yes
407 trig 3.5.3.2_2-yes
408 trig 3.5.3.7-2a
409 trig 3.5.3.7-2b
410 trig 3.5.3.7-2c
411 drop trigger trg4d_1;
412 connection yes_privs_424d;
413 show grants;
414 Grants for test_yesprivs@localhost
415 GRANT TRIGGER ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
416 GRANT UPDATE (`f1`) ON `priv_db`.`t1` TO `test_yesprivs`@`localhost`
417 use priv_db;
418 create trigger trg4d_2 before INSERT on t1 for each row
419 set new.f1 = 'trig 3.5.3.7-2d';
420 connection default;
421 insert into t1 (f1) values ('insert 3.5.3.7-2d');
422 select f1 from t1 order by f1;
423 f1
424 insert 3.5.3.2-no
425 insert 3.5.3.6-no
426 insert 3.5.3.7-1b
427 trig 3.5.3.2_2-yes
428 trig 3.5.3.2_2-yes
429 trig 3.5.3.7-2a
430 trig 3.5.3.7-2b
431 trig 3.5.3.7-2c
432 trig 3.5.3.7-2d
433 drop trigger trg4d_2;
434 disconnect no_privs_424d;
435 disconnect yes_privs_424d;
436
437 Testcase 3.5.3.8a:
438 ------------------
439 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
440 grant ALL on *.* to test_noprivs@localhost;
441 revoke SELECT on *.* from test_noprivs@localhost;
442 show grants for test_noprivs@localhost;
443 Grants for test_noprivs@localhost
444 GRANT INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, BINLOG MONITOR, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, DELETE HISTORY, SET USER, FEDERATED ADMIN, CONNECTION ADMIN, READ_ONLY ADMIN, REPLICATION SLAVE ADMIN, REPLICATION MASTER ADMIN, BINLOG ADMIN, BINLOG REPLAY, SLAVE MONITOR, SHOW CREATE ROUTINE ON *.* TO `test_noprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
445 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
446 grant TRIGGER, SELECT on *.* to test_yesprivs@localhost;
447 show grants for test_yesprivs@localhost;
448 Grants for test_yesprivs@localhost
449 GRANT SELECT, TRIGGER ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
450 connect no_privs_425a,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
451 connect yes_privs_425a,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
452 connection default;
453 connection no_privs_425a;
454 select current_user;
455 current_user
456 test_noprivs@localhost
457 use priv_db;
458 show grants;
459 Grants for test_noprivs@localhost
460 GRANT INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, BINLOG MONITOR, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, DELETE HISTORY, SET USER, FEDERATED ADMIN, CONNECTION ADMIN, READ_ONLY ADMIN, REPLICATION SLAVE ADMIN, REPLICATION MASTER ADMIN, BINLOG ADMIN, BINLOG REPLAY, SLAVE MONITOR, SHOW CREATE ROUTINE ON *.* TO `test_noprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
461 create trigger trg5a_1 before INSERT on t1 for each row
462 set @test_var = new.f1;
463 connection default;
464 set @test_var = 'before trig 3.5.3.8-1a';
465 select @test_var;
466 @test_var
467 before trig 3.5.3.8-1a
468 insert into t1 (f1) values ('insert 3.5.3.8-1a');
469 ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
470 select @test_var;
471 @test_var
472 before trig 3.5.3.8-1a
473 drop trigger trg5a_1;
474 connection yes_privs_425a;
475 use priv_db;
476 select current_user;
477 current_user
478 test_yesprivs@localhost
479 show grants;
480 Grants for test_yesprivs@localhost
481 GRANT SELECT, TRIGGER ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
482 create trigger trg5a_2 before INSERT on t1 for each row
483 set @test_var= new.f1;
484 connection default;
485 set @test_var= 'before trig 3.5.3.8-2a';
486 select @test_var;
487 @test_var
488 before trig 3.5.3.8-2a
489 insert into t1 (f1) values ('insert 3.5.3.8-2a');
490 select @test_var;
491 @test_var
492 insert 3.5.3.8-2a
493 drop trigger trg5a_2;
494 disconnect no_privs_425a;
495 disconnect yes_privs_425a;
496
497 Testcase: 3.5.3.8b
498 ------------------
499 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
500 grant TRIGGER on *.* to test_noprivs@localhost;
501 grant ALL on priv_db.* to test_noprivs@localhost;
502 revoke SELECT on priv_db.* from test_noprivs@localhost;
503 show grants for test_noprivs@localhost;
504 Grants for test_noprivs@localhost
505 GRANT TRIGGER ON *.* TO `test_noprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
506 GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER, DELETE HISTORY, SHOW CREATE ROUTINE ON `priv_db`.* TO `test_noprivs`@`localhost`
507 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
508 grant TRIGGER on *.* to test_yesprivs@localhost;
509 grant SELECT on priv_db.* to test_yesprivs@localhost;
510 show grants for test_yesprivs@localhost;
511 Grants for test_yesprivs@localhost
512 GRANT TRIGGER ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
513 GRANT SELECT ON `priv_db`.* TO `test_yesprivs`@`localhost`
514 connect no_privs_425b,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
515 connect yes_privs_425b,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
516 connection default;
517 connection no_privs_425b;
518 show grants;
519 Grants for test_noprivs@localhost
520 GRANT TRIGGER ON *.* TO `test_noprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
521 GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER, DELETE HISTORY, SHOW CREATE ROUTINE ON `priv_db`.* TO `test_noprivs`@`localhost`
522 use priv_db;
523 create trigger trg5b_1 before UPDATE on t1 for each row
524 set @test_var= new.f1;
525 connection default;
526 set @test_var= 'before trig 3.5.3.8-1b';
527 insert into t1 (f1) values ('insert 3.5.3.8-1b');
528 select @test_var;
529 @test_var
530 before trig 3.5.3.8-1b
531 update t1 set f1= 'update 3.5.3.8-1b' where f1 = 'insert 3.5.3.8-1b';
532 ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
533 select @test_var;
534 @test_var
535 before trig 3.5.3.8-1b
536 drop trigger trg5b_1;
537 connection yes_privs_425b;
538 show grants;
539 Grants for test_yesprivs@localhost
540 GRANT TRIGGER ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
541 GRANT SELECT ON `priv_db`.* TO `test_yesprivs`@`localhost`
542 use priv_db;
543 create trigger trg5b_2 before UPDATE on t1 for each row
544 set @test_var= new.f1;
545 connection default;
546 set @test_var= 'before trig 3.5.3.8-2b';
547 insert into t1 (f1) values ('insert 3.5.3.8-2b');
548 select @test_var;
549 @test_var
550 before trig 3.5.3.8-2b
551 update t1 set f1= 'update 3.5.3.8-2b' where f1 = 'insert 3.5.3.8-2b';
552 select @test_var;
553 @test_var
554 update 3.5.3.8-2b
555 drop trigger trg5b_2;
556 disconnect no_privs_425b;
557 disconnect yes_privs_425b;
558
559 Testcase 3.5.3.8c:
560 ------------------
561 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
562 grant TRIGGER on *.* to test_noprivs@localhost;
563 grant ALL on priv_db.t1 to test_noprivs@localhost;
564 revoke SELECT on priv_db.t1 from test_noprivs@localhost;
565 show grants for test_noprivs@localhost;
566 Grants for test_noprivs@localhost
567 GRANT TRIGGER ON *.* TO `test_noprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
568 GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER, DELETE HISTORY ON `priv_db`.`t1` TO `test_noprivs`@`localhost`
569 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
570 grant TRIGGER on *.* to test_yesprivs@localhost;
571 grant SELECT on priv_db.t1 to test_yesprivs@localhost;
572 show grants for test_yesprivs@localhost;
573 Grants for test_yesprivs@localhost
574 GRANT TRIGGER ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
575 GRANT SELECT ON `priv_db`.`t1` TO `test_yesprivs`@`localhost`
576 connect no_privs_425c,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
577 connect yes_privs_425c,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
578 connection default;
579 connection no_privs_425c;
580 show grants;
581 Grants for test_noprivs@localhost
582 GRANT TRIGGER ON *.* TO `test_noprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
583 GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER, DELETE HISTORY ON `priv_db`.`t1` TO `test_noprivs`@`localhost`
584 use priv_db;
585 create trigger trg5c_1 before INSERT on t1 for each row
586 set @test_var= new.f1;
587 connection default;
588 set @test_var= 'before trig 3.5.3.8-1c';
589 insert into t1 (f1) values ('insert 3.5.3.8-1c');
590 ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
591 select @test_var;
592 @test_var
593 before trig 3.5.3.8-1c
594 drop trigger trg5c_1;
595 connection yes_privs_425c;
596 show grants;
597 Grants for test_yesprivs@localhost
598 GRANT TRIGGER ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
599 GRANT SELECT ON `priv_db`.`t1` TO `test_yesprivs`@`localhost`
600 use priv_db;
601 create trigger trg5c_2 before INSERT on t1 for each row
602 set @test_var= new.f1;
603 connection default;
604 set @test_var='before trig 3.5.3.8-2c';
605 insert into t1 (f1) values ('insert 3.5.3.8-2c');
606 select @test_var;
607 @test_var
608 insert 3.5.3.8-2c
609 drop trigger trg5c_2;
610 disconnect no_privs_425c;
611 disconnect yes_privs_425c;
612
613 Testcase: 3.5.3.8d:
614 -------------------
615 revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
616 grant TRIGGER on *.* to test_noprivs@localhost;
617 grant UPDATE (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
618 show grants for test_noprivs@localhost;
619 Grants for test_noprivs@localhost
620 GRANT TRIGGER ON *.* TO `test_noprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
621 GRANT INSERT (`f1`), UPDATE (`f1`) ON `priv_db`.`t1` TO `test_noprivs`@`localhost`
622 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
623 grant TRIGGER on *.* to test_yesprivs@localhost;
624 grant SELECT (f1) on priv_db.t1 to test_yesprivs@localhost;
625 show grants for test_noprivs@localhost;
626 Grants for test_noprivs@localhost
627 GRANT TRIGGER ON *.* TO `test_noprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
628 GRANT INSERT (`f1`), UPDATE (`f1`) ON `priv_db`.`t1` TO `test_noprivs`@`localhost`
629 connect no_privs_425d,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
630 connect yes_privs_425d,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
631 connection default;
632 connection no_privs_425d;
633 show grants;
634 Grants for test_noprivs@localhost
635 GRANT TRIGGER ON *.* TO `test_noprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
636 GRANT INSERT (`f1`), UPDATE (`f1`) ON `priv_db`.`t1` TO `test_noprivs`@`localhost`
637 use priv_db;
638 create trigger trg5d_1 before INSERT on t1 for each row
639 set @test_var= new.f1;
640 connection default;
641 set @test_var='before trig 3.5.3.8-1d';
642 insert into t1 (f1) values ('insert 3.5.3.8-1d');
643 ERROR 42000: SELECT command denied to user 'test_noprivs'@'localhost' for column 'f1' in table 't1'
644 select @test_var;
645 @test_var
646 before trig 3.5.3.8-1d
647 drop trigger trg5d_1;
648 connection yes_privs_425d;
649 show grants;
650 Grants for test_yesprivs@localhost
651 GRANT TRIGGER ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
652 GRANT SELECT (`f1`) ON `priv_db`.`t1` TO `test_yesprivs`@`localhost`
653 use priv_db;
654 create trigger trg5d_2 before INSERT on t1 for each row
655 set @test_var= new.f1;
656 connection default;
657 set @test_var='before trig 3.5.3.8-2d';
658 insert into t1 (f1) values ('insert 3.5.3.8-2d');
659 select @test_var;
660 @test_var
661 insert 3.5.3.8-2d
662 drop trigger trg5d_2;
663
664 Testcase: 3.5.3.x:
665 ------------------
666 use priv_db;
667 drop table if exists t1;
668 drop table if exists t2;
669 create table t1 (f1 int) engine= <engine_to_be_used>;
670 create table t2 (f2 int) engine= <engine_to_be_used>;
671 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
672 grant TRIGGER on *.* to test_yesprivs@localhost;
673 grant SELECT, UPDATE on priv_db.t1 to test_yesprivs@localhost;
674 grant SELECT on priv_db.t2 to test_yesprivs@localhost;
675 show grants for test_yesprivs@localhost;
676 Grants for test_yesprivs@localhost
677 GRANT TRIGGER ON *.* TO `test_yesprivs`@`localhost` IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
678 GRANT SELECT ON `priv_db`.`t2` TO `test_yesprivs`@`localhost`
679 GRANT SELECT, UPDATE ON `priv_db`.`t1` TO `test_yesprivs`@`localhost`
680 connect yes_353x,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK;
681 connection yes_353x;
682 select current_user;
683 current_user
684 test_yesprivs@localhost
685 use priv_db;
686 create trigger trg1 before insert on t1 for each row
687 insert into t2 values (new.f1);
688 connection default;
689 use priv_db;
690 insert into t1 (f1) values (4);
691 ERROR 42000: INSERT command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t2`
692 revoke SELECT on priv_db.t2 from test_yesprivs@localhost;
693 grant INSERT on priv_db.t2 to test_yesprivs@localhost;
694 insert into t1 (f1) values (4);
695 select f1 from t1 order by f1;
696 f1
697 4
698 select f2 from t2 order by f2;
699 f2
700 4
701 connection yes_353x;
702 use priv_db;
703 drop trigger trg1;
704 create trigger trg2 before insert on t1 for each row
705 update t2 set f2=new.f1-1;
706 connection default;
707 use priv_db;
708 insert into t1 (f1) values (2);
709 ERROR 42000: UPDATE command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t2`
710 revoke INSERT on priv_db.t2 from test_yesprivs@localhost;
711 grant UPDATE on priv_db.t2 to test_yesprivs@localhost;
712 insert into t1 (f1) values (2);
713 select f1 from t1 order by f1;
714 f1
715 2
716 4
717 select f2 from t2 order by f2;
718 f2
719 1
720 connection yes_353x;
721 use priv_db;
722 drop trigger trg2;
723 create trigger trg3 before insert on t1 for each row
724 select f2 into @aaa from t2 where f2=new.f1;
725 connection default;
726 use priv_db;
727 insert into t1 (f1) values (1);
728 ERROR 42000: SELECT command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t2`
729 revoke UPDATE on priv_db.t2 from test_yesprivs@localhost;
730 grant SELECT on priv_db.t2 to test_yesprivs@localhost;
731 insert into t1 (f1) values (1);
732 select f1 from t1 order by f1;
733 f1
734 1
735 2
736 4
737 select f2 from t2 order by f2;
738 f2
739 1
740 select @aaa;
741 @aaa
742 1
743 connection yes_353x;
744 use priv_db;
745 drop trigger trg3;
746 create trigger trg4 before insert on t1 for each row
747 delete from t2;
748 connection default;
749 use priv_db;
750 insert into t1 (f1) values (1);
751 ERROR 42000: DELETE command denied to user 'test_yesprivs'@'localhost' for table `priv_db`.`t2`
752 revoke SELECT on priv_db.t2 from test_yesprivs@localhost;
753 grant DELETE on priv_db.t2 to test_yesprivs@localhost;
754 insert into t1 (f1) values (1);
755 select f1 from t1 order by f1;
756 f1
757 1
758 1
759 2
760 4
761 select f2 from t2 order by f2;
762 f2
763 drop database if exists priv_db;
764 drop user test_yesprivs@localhost;
765 drop user test_noprivs@localhost;
766 drop user test_noprivs;
767 use test;
768 drop table tb3;