Debian Codegraph @

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

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