Debian Codegraph @

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

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