Debian Codegraph @

mysql-test/suite/auth_sec/t/system_user_priv.test

raw
1 ###############################################################################
2 # WL#12098: MySQL system users #
3 # This test file covers the scenarios around the SYSTEM_USER dynamic #
4 # privilege. #
5 # #
6 ###############################################################################
7
8 # Save the initial number of concurrent sessions
9 --source include/count_sessions.inc
10
11 --echo #------------------------------------------------------------------------
12 --echo # 1. Create the Users and Roles. Grant/REVOKE the SYSTEM_USER
13 --echo # privilege from them.
14 --echo #------------------------------------------------------------------------
15
16 --echo
17 --echo # 1.1 User is granted SYSTEM_USER priv only if it is granted explictly
18 CREATE USER sys_usr IDENTIFIED BY 'pwd';
19 SHOW GRANTS FOR sys_usr;
20 GRANT SYSTEM_USER ON *.* TO sys_usr WITH GRANT OPTION;
21 SHOW GRANTS FOR sys_usr;
22
23 --echo
24 --echo # 1.2 Revoke the SYSTEM_USER privilege from user;
25 CREATE USER non_sys_usr IDENTIFIED BY 'pwd';
26 GRANT ALL ON *.* TO non_sys_usr;
27 REVOKE SYSTEM_USER ON *.* FROM non_sys_usr;
28 # We shall partially revoke the update access from mysql.* once the former
29 # feature will be available.
30 REVOKE UPDATE ON *.* FROM non_sys_usr;
31 SHOW GRANTS FOR non_sys_usr;
32
33 --echo
34 --echo # 1.3 Role is granted SYSTEM_USER priv only if it is granted explictly
35 CREATE ROLE sys_role;
36 SHOW GRANTS FOR sys_role;
37 GRANT SYSTEM_USER ON *.* TO sys_role WITH GRANT OPTION;
38 SHOW GRANTS FOR sys_role;
39
40 --echo
41 --echo # 1.4 Revoke the SYSTEM_USER privilege from user;
42 CREATE USER non_sys_role;
43 GRANT ALL ON *.* TO non_sys_role;
44 REVOKE SYSTEM_USER ON *.* FROM non_sys_role;
45 SHOW GRANTS FOR non_sys_role;
46
47 --echo #------------------------------------------------------------------------
48 --echo # 2. sys_usr cannot modify the other users properties unless the former
49 --echo # is granted the relevant properties to do so
50 --echo #------------------------------------------------------------------------
51 connect(sys_usr_con,localhost,sys_usr,pwd,);
52 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
53 CREATE USER test_usr;
54 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
55 ALTER USER non_sys_usr IDENTIFIED BY 'pwd';
56 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
57 RENAME USER non_sys_usr TO power_usr;
58 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
59 DROP USER non_sys_usr;
60 connection default;
61
62 --echo #------------------------------------------------------------------------
63 --echo # 3. non_sys_usr cannot modify the other sys_usr even if former is
64 --echo # granted global privileges
65 --echo #------------------------------------------------------------------------
66 connect(non_sys_usr_con,localhost,non_sys_usr,pwd,);
67 CREATE USER test_user;
68 ALTER USER test_user IDENTIFIED BY 'pwd';
69 RENAME USER test_user TO temp_user;
70 DROP USER temp_user;
71 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
72 ALTER USER sys_usr IDENTIFIED BY 'pwd';
73 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
74 RENAME USER sys_usr TO power_usr;
75 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
76 DROP USER sys_usr;
77 connection default;
78
79 --echo #------------------------------------------------------------------------
80 --echo # 4. non_sys_usr cannot grant/revoke SYSTEM_USER privilege to /from
81 --echo # other users/roles
82 --echo #------------------------------------------------------------------------
83 connection non_sys_usr_con;
84 CREATE USER test_user;
85 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
86 GRANT SYSTEM_USER ON *.* TO test_user;
87 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
88 REVOKE SYSTEM_USER FROM sys_usr;
89 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
90 GRANT SYSTEM_USER ON *.* TO sys_role;
91 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
92 GRANT SYSTEM_USER ON *.* TO non_sys_role;
93 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
94 REVOKE SYSTEM_USER ON *.* FROM sys_role;
95 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
96 GRANT sys_role to test_user;
97 connection default;
98 GRANT sys_role TO test_user;
99 SHOW GRANTS FOR test_user;
100 connection non_sys_usr_con;
101 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
102 REVOKE sys_role FROM test_user;
103 DROP USER test_user;
104 connection default;
105
106 --echo #------------------------------------------------------------------------
107 --echo # 5. Mandatory Roles
108 --echo #------------------------------------------------------------------------
109 --echo
110 --echo # 5.1 Check with root user
111 --error ER_DA_AUTH_ID_WITH_SYSTEM_USER_PRIV_IN_MANDATORY_ROLES
112 SET @@GLOBAL.MANDATORY_ROLES='sys_role,non_sys_role';
113
114 --error ER_DA_AUTH_ID_WITH_SYSTEM_USER_PRIV_IN_MANDATORY_ROLES
115 SET @@GLOBAL.MANDATORY_ROLES='sys_role';
116
117 SET @@GLOBAL.MANDATORY_ROLES='non_sys_role';
118 --echo
119 --echo # 5.2 Check with user who does not have system user privilege
120 connection non_sys_usr_con;
121 --error ER_DA_AUTH_ID_WITH_SYSTEM_USER_PRIV_IN_MANDATORY_ROLES
122 SET @@GLOBAL.MANDATORY_ROLES='sys_role,non_sys_role';
123
124 --error ER_DA_AUTH_ID_WITH_SYSTEM_USER_PRIV_IN_MANDATORY_ROLES
125 SET @@GLOBAL.MANDATORY_ROLES='sys_role';
126
127 SET @@GLOBAL.MANDATORY_ROLES='non_sys_role';
128 --echo
129 --echo # 5.3 Check with user who has system user privilege
130 connection sys_usr_con;
131 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
132 SET @@GLOBAL.MANDATORY_ROLES='sys_role,non_sys_role';
133
134 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
135 SET @@GLOBAL.MANDATORY_ROLES='sys_role';
136 --echo
137 --echo # 5.4 Non existing mandatory_role which is granted the SYSTEM_USER priv
138 --echo # later
139 connection default;
140 SET @@GLOBAL.MANDATORY_ROLES='test_role';
141 CREATE ROLE test_role;
142 --error ER_CANNOT_GRANT_SYSTEM_PRIV_TO_MANDATORY_ROLE
143 GRANT SYSTEM_USER ON *.* TO 'test_role';
144 connection sys_usr_con;
145 --error ER_CANNOT_GRANT_SYSTEM_PRIV_TO_MANDATORY_ROLE
146 GRANT SYSTEM_USER ON *.* TO 'test_role';
147 connection non_sys_usr_con;
148 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
149 GRANT SYSTEM_USER ON *.* TO 'test_role';
150 connection default;
151 # Clear the mandatory_role and delete the test_role
152 SET @@GLOBAL.MANDATORY_ROLES='';
153 DROP ROLE test_role;
154 --echo
155 --echo # 5.5 None of the roles in the role's hierarchy should have SYSTEM_USER
156 --echo # privilege if former is set as mandatory_role.
157 CREATE ROLE r1, r2;
158 GRANT r1 TO r2;
159 GRANT sys_role TO r1;
160 --error ER_DA_AUTH_ID_WITH_SYSTEM_USER_PRIV_IN_MANDATORY_ROLES
161 SET @@GLOBAL.MANDATORY_ROLES='r2';
162 --echo # 5.6 Set persist should fail too.
163 --error ER_DA_AUTH_ID_WITH_SYSTEM_USER_PRIV_IN_MANDATORY_ROLES
164 SET PERSIST MANDATORY_ROLES = 'r2';
165 --echo # 5.6 Set persist should fail too.
166 --error ER_DA_AUTH_ID_WITH_SYSTEM_USER_PRIV_IN_MANDATORY_ROLES
167 SET PERSIST MANDATORY_ROLES = 'r2';
168 --echo # 5.7 We do not check the SET PERSIST_ONLY, should succeed.
169 SET PERSIST_ONLY MANDATORY_ROLES = 'r2';
170 --echo # 5.8 On Server restart there should be a warning in the log file
171 --echo # Restart server.
172 --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
173 --shutdown_server
174 --source include/wait_until_disconnected.inc
175 # Remove the server log file so that we find the expected warning only once.
176 perl;
177 my $filetodelete = "$ENV{'MYSQLTEST_VARDIR'}/log/mysqld.1.err";
178 while (-e $filetodelete) {
179 unlink $filetodelete;
180 sleep 1;
181 }
182 EOF
183 --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
184 --enable_reconnect
185 --source include/wait_until_connected_again.inc
186 --let $assert_file=$MYSQLTEST_VARDIR/log/mysqld.1.err
187 --let $assert_select=.* \[Warning\] \[[^]]*\] \[[^]]*\] Cannot set mandatory_roles: AuthId .* has 'SYSTEM_USER' privilege.
188 --let $assert_text= Found the expected warning line in the server log.
189 --let $assert_count=1
190 --source include/assert_grep.inc
191 --echo # System variable should not have been set.
192 SELECT @@mandatory_roles;
193 # Clear the system variable in the .cnf file
194 SET PERSIST mandatory_roles = '';
195
196 --echo # 5.9 Set the system variable at the command line.
197 # Server should restart with a warning
198 --echo # Restart server.
199 --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
200 --shutdown_server
201 --source include/wait_until_disconnected.inc
202 # Remove the server log file so that we find the expected warning only once.
203 perl;
204 my $filetodelete = "$ENV{'MYSQLTEST_VARDIR'}/log/mysqld.1.err";
205 while (-e $filetodelete) {
206 unlink $filetodelete;
207 sleep 1;
208 }
209 EOF
210 --exec echo "restart:--mandatory_roles='r2'" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
211 --enable_reconnect
212 --source include/wait_until_connected_again.inc
213 --let $assert_file=$MYSQLTEST_VARDIR/log/mysqld.1.err
214 --let $assert_select=.* \[Warning\] \[[^]]*\] \[[^]]*\] Cannot set mandatory_roles: AuthId .* has 'SYSTEM_USER' privilege.
215 --let $assert_text= Found the expected warning line in the server log.
216 --let $assert_count=1
217 --source include/assert_grep.inc
218 --echo # System variable should not have been set.
219 SELECT @@mandatory_roles;
220 --echo # 5.10 Set the system variable in the cnf file. Warning should be logged.
221
222 let $MYSQLD_DATADIR= `SELECT @@datadir`;
223 let $MYSQL_BASEDIR= `SELECT @@basedir`;
224 let $MYSQL_SOCKET= `SELECT @@socket`;
225 let $MYSQL_PIDFILE= `SELECT @@pid_file`;
226 let $MYSQL_PORT= `SELECT @@port`;
227 let $MYSQL_MESSAGESDIR= `SELECT @@lc_messages_dir`;
228 let $MYSQL_HOME=`SELECT @@basedir`;
229 let $MYSQL_SERVER_ID= `SELECT @@server_id`;
230
231 copy_file $MYSQLTEST_VARDIR/my.cnf $MYSQLTEST_VARDIR/tmp/temp_my.cnf;
232 append_file $MYSQLTEST_VARDIR/tmp/temp_my.cnf;
233 [mysqld]
234 mandatory_roles=r2
235 EOF
236 --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
237 --shutdown_server
238 --source include/wait_until_disconnected.inc
239 # Remove the server log file so that we find the expected warning only once.
240 perl;
241 my $filetodelete = "$ENV{'MYSQLTEST_VARDIR'}/log/mysqld.1.err";
242 while (-e $filetodelete) {
243 unlink $filetodelete;
244 sleep 1;
245 }
246 EOF
247
248 --exec echo "restart:--defaults-file=$MYSQLTEST_VARDIR/tmp/temp_my.cnf" --basedir=$MYSQL_BASEDIR --datadir=$MYSQLD_DATADIR --lc-messages-dir=$MYSQL_MESSAGESDIR --innodb_dedicated_server=OFF --skip-mysqlx > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
249 --enable_reconnect
250 --source include/wait_until_connected_again.inc
251 --let $assert_file=$MYSQLTEST_VARDIR/log/mysqld.1.err
252 --let $assert_select=.* \[Warning\] \[[^]]*\] \[[^]]*\] Cannot set mandatory_roles: AuthId .* has 'SYSTEM_USER' privilege.
253 --let $assert_text= Found the expected warning line in the server log.
254 --let $assert_count=1
255 --source include/assert_grep.inc
256 --echo # System variable should not have been set.
257 SELECT @@mandatory_roles;
258 DROP ROLE r1, r2;
259
260 # Restart Server as usual
261 --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
262 --enable_reconnect
263 --source include/wait_until_connected_again.inc
264 --enable_reconnect
265 --source include/wait_until_connected_again.inc
266 remove_file $MYSQLTEST_VARDIR/tmp/temp_my.cnf;
267 # Reset the connections
268 disconnect sys_usr_con;
269 connect(sys_usr_con,localhost,sys_usr,pwd,);
270 disconnect non_sys_usr_con;
271 connect(non_sys_usr_con,localhost,non_sys_usr,pwd,);
272 connection default;
273 --echo #------------------------------------------------------------------------
274 --echo # 6. If the definer has SYSTEM_USER privilege then owner must also have
275 --echo # the SYSTEM_USER privilege.
276 --echo #------------------------------------------------------------------------
277 CREATE TABLE test.t1 (c1 INT);
278 CREATE DATABASE restricted;
279 CREATE TABLE restricted.t1 (c1 int, restricted int);
280 INSERT INTO restricted.t1 VALUES (1,2);
281 GRANT SET_USER_ID ON *.* TO sys_usr;
282 --echo
283 --echo # 6.1 Stored procedure and functions
284 connection sys_usr_con;
285 SELECT CURRENT_USER();
286 --error ER_TABLEACCESS_DENIED_ERROR
287 SELECT * FROM restricted.t1;
288 USE test;
289 CREATE DEFINER=root@localhost PROCEDURE p1() SELECT * FROM restricted.t1;
290 --echo # With the SET_USER_ID privilege we can escalate the authorization
291 CALL p1();
292 CREATE DEFINER=root@localhost FUNCTION f1(i INT) RETURNS INT(10) DETERMINISTIC
293 RETURN i*10;
294 --echo # With the SET_USER_ID privilege we can escalate the authorization
295 SELECT f1(2);
296 connection non_sys_usr_con;
297 --echo
298 SELECT CURRENT_USER();
299 USE test;
300 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
301 CREATE DEFINER=root@localhost PROCEDURE p2() SELECT * FROM restricted.t1;
302 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
303 CREATE DEFINER=root@localhost FUNCTION f2(i INT) RETURNS INT(10) DETERMINISTIC
304 RETURN i*10;
305 --echo # Invoker has EXECUTE privilege but not SYSTEM_USER privilege
306 CALL p1();
307 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
308 ALTER PROCEDURE test.p1 SQL SECURITY INVOKER;
309 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
310 DROP PROCEDURE test.p1;
311 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
312 ALTER FUNCTION test.f1 SQL SECURITY INVOKER;
313 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
314 DROP FUNCTION test.f1;
315 --echo
316 --echo # 6.2 Create trigger
317 connection sys_usr_con;
318 SELECT CURRENT_USER();
319 CREATE DEFINER=root@localhost TRIGGER test.tr1 BEFORE INSERT ON test.t1
320 FOR EACH ROW INSERT INTO restricted.t1 VALUES (1,1);
321 --echo # Use trigger to escalate privileges
322 INSERT INTO test.t1 VALUES (1);
323 connection default;
324 SELECT * FROM restricted.t1;
325 connection non_sys_usr_con;
326 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
327 CREATE DEFINER=root@localhost TRIGGER test.tr2 BEFORE INSERT ON test.t1
328 FOR EACH ROW INSERT INTO restricted.t1 VALUES (1,1);
329 --echo
330 --echo # 6.3 Create View
331 connection default;
332 --echo # We need some privilege on the column for CREATE VIEW to work
333 GRANT INSERT(restricted) ON restricted.t1 TO sys_usr;
334 connection sys_usr_con;
335 SHOW GRANTS FOR CURRENT_USER();
336 CREATE DEFINER=root@localhost SQL SECURITY DEFINER VIEW v1 AS
337 SELECT a.restricted FROM restricted.t1 as a;
338 --echo # Use escalated privileges to read restricted column.
339 SELECT * FROM v1;
340 connection non_sys_usr_con;
341 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
342 CREATE DEFINER=root@localhost SQL SECURITY DEFINER VIEW v2 AS
343 SELECT a.restricted FROM restricted.t1 as a;
344 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
345 ALTER ALGORITHM=MERGE VIEW v1 AS SELECT a.restricted FROM restricted.t1 as a;
346 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
347 DROP VIEW v1;
348 --echo
349 --echo # 6.4 Create Event
350 connection sys_usr_con;
351 DELIMITER $$;
352 CREATE DEFINER=root@localhost EVENT test.eve1 ON SCHEDULE AT
353 CURRENT_TIMESTAMP + INTERVAL 2 HOUR
354 DO BEGIN
355 INSERT INTO restricted.t1 VALUES (5,5);
356 END ;$$
357 DELIMITER ;$$
358 connection non_sys_usr_con;
359 DELIMITER $$;
360 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
361 CREATE DEFINER=root@localhost EVENT test.eve2 ON SCHEDULE AT
362 CURRENT_TIMESTAMP + INTERVAL 2 SECOND
363 DO BEGIN
364 INSERT INTO restricted.t1 VALUES (5,5);
365 END ;$$
366 DELIMITER ;$$
367 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
368 ALTER EVENT test.eve1 RENAME TO test.eve2;
369 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
370 DROP EVENT test.eve1;
371 --echo # Cleanup
372 connection default;
373 DROP TRIGGER test.tr1;
374 DROP PROCEDURE p1;
375 DROP FUNCTION f1;
376 DROP DATABASE restricted;
377 DROP VIEW test.v1;
378 DROP TABLE test.t1;
379 DROP EVENT test.eve1;
380 --echo #------------------------------------------------------------------------
381 --echo # 7. If SYSTEM_USER privilege is granted to definer and invoker
382 --echo # through roles
383 --echo #------------------------------------------------------------------------
384 USE test;
385 CREATE TABLE test.t1 (c1 int, c2 int);
386 INSERT INTO test.t1 VALUES(1,2);
387 CREATE USER test_user@localhost;
388 GRANT sys_role TO test_user@localhost;
389 GRANT ALL ON test.* TO test_user@localhost;
390 GRANT EXECUTE ON *.* TO test_user@localhost;
391 --echo
392 --echo # 7.1 Invoker is not granted SYSTEM_USER priv through roles
393 --echo # 7.1.1 ALTER AND DROP STORED PROCEDURE
394 CREATE DEFINER=test_user@localhost PROCEDURE p1() SELECT * FROM test.t1;
395 SHOW GRANTS FOR test_user@localhost;
396 connection non_sys_usr_con;
397 CALL p1();
398 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
399 ALTER PROCEDURE test.p1 SQL SECURITY INVOKER;
400 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
401 DROP PROCEDURE test.p1;
402 --echo # 7.1.2 ALTER and DROP FUNCTIONS
403 CREATE DEFINER=test_user@localhost FUNCTION f1(i INT) RETURNS INT(10) DETERMINISTIC
404 RETURN i*10;
405 connection non_sys_usr_con;
406 SELECT f1(2);
407 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
408 ALTER FUNCTION test.f1 SQL SECURITY INVOKER;
409 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
410 DROP FUNCTION test.f1;
411 --echo # 7.1.3 ALTER and DROP View
412 connection default;
413 CREATE DEFINER=test_user@localhost SQL SECURITY DEFINER VIEW v1 AS
414 SELECT a.c1 FROM test.t1 as a;
415 SELECT * FROM v1;
416 connection non_sys_usr_con;
417 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
418 ALTER VIEW v1 AS SELECT a.c1 FROM test.t1 as a;
419 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
420 DROP VIEW v1;
421 --echo
422 --echo # 7.1.4 ALTER and DROP EVENT
423 connection default;
424 DELIMITER $$;
425 CREATE DEFINER=test_user@localhost EVENT test.eve1 ON SCHEDULE AT
426 CURRENT_TIMESTAMP + INTERVAL 2 HOUR
427 DO BEGIN
428 INSERT INTO test.t1 VALUES (5,5);
429 END ;$$
430 DELIMITER ;$$
431 connection non_sys_usr_con;
432 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
433 ALTER EVENT test.eve1 RENAME TO test.eve2;
434 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
435 DROP EVENT test.eve1;
436 --echo
437 --echo # 7.2 Invoker is also granted SYSTEM_USER priv through roles
438 connection default;
439 CREATE ROLE r1, r2;
440 GRANT SYSTEM_USER ON *.* TO r1;
441 GRANT r1 TO r2;
442 GRANT r2 TO non_sys_usr;
443 --echo # 7.2.1 ALTER and DROP PROCEDURE
444 connection non_sys_usr_con;
445 CALL p1();
446 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
447 ALTER PROCEDURE test.p1 SQL SECURITY INVOKER;
448 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
449 DROP PROCEDURE test.p1;
450 --echo # Activate the role to get the SYSTEM_USER priv
451 SET ROLE r2;
452 ALTER PROCEDURE test.p1 SQL SECURITY INVOKER;
453 CALL p1();
454 DROP PROCEDURE test.p1;
455 SET ROLE NONE;
456 --echo # 7.2.2 ALTER and DROP FUNCTIONS
457 SELECT f1(2);
458 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
459 ALTER FUNCTION test.f1 SQL SECURITY INVOKER;
460 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
461 DROP FUNCTION test.f1;
462 SELECT f1(2);
463 --echo # Activate the role to get the SYSTEM_USER priv
464 SET ROLE r2;
465 ALTER FUNCTION test.f1 SQL SECURITY INVOKER;
466 DROP FUNCTION test.f1;
467 SET ROLE NONE;
468 --echo # 7.2.3 ALTER and DROP VIEW
469 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
470 ALTER ALGORITHM=MERGE VIEW v1 AS SELECT a.c1 FROM test.t1 as a;
471 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
472 DROP VIEW v1;
473 --echo # Activate the role to get the SYSTEM_USER priv
474 SET ROLE r2;
475 ALTER ALGORITHM=MERGE VIEW v1 AS SELECT a.c1 FROM test.t1 as a;
476 DROP VIEW v1;
477 SET ROLE NONE;
478 --echo
479 --echo # 7.2.4 ALTER and DROP EVENT
480 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
481 ALTER EVENT test.eve1 RENAME TO test.eve2;
482 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
483 DROP EVENT test.eve1;
484 --echo # Activate the role to get the SYSTEM_USER priv
485 SET ROLE r2;
486 ALTER EVENT test.eve1 RENAME TO test.eve2;
487 DROP EVENT test.eve2;
488 SET ROLE NONE;
489 --echo # Cleanup
490 connection default;
491 DROP USER test_user@localhost;
492 DROP ROLE r1, r2;
493 DROP TABLE test.t1;
494 --echo #
495 --echo # Global clean up
496 --echo #
497 DROP USER sys_usr;
498 DROP USER non_sys_usr;
499 DROP USER sys_role;
500 DROP USER non_sys_role;
501 disconnect sys_usr_con;
502 disconnect non_sys_usr_con;
503 # Wait till all disconnects are completed
504 --source include/wait_until_count_sessions.inc
505
506 call mtr.add_suppression(".*Cannot set mandatory_roles: AuthId.* privilege.");
507
508 RESET PERSIST mandatory_roles;
509
510 # Restore default settings in opt file and clean up
511 --let $restart_parameters = restart:
512 --source include/restart_mysqld.inc