Debian Codegraph @

mysql-test/suite/auth_sec/r/system_user_priv.result

raw
1 #------------------------------------------------------------------------
2 # 1. Create the Users and Roles. Grant/REVOKE the SYSTEM_USER
3 # privilege from them.
4 #------------------------------------------------------------------------
5
6 # 1.1 User is granted SYSTEM_USER priv only if it is granted explictly
7 CREATE USER sys_usr IDENTIFIED BY 'pwd';
8 SHOW GRANTS FOR sys_usr;
9 Grants for sys_usr@%
10 GRANT USAGE ON *.* TO `sys_usr`@`%`
11 GRANT SYSTEM_USER ON *.* TO sys_usr WITH GRANT OPTION;
12 SHOW GRANTS FOR sys_usr;
13 Grants for sys_usr@%
14 GRANT USAGE ON *.* TO `sys_usr`@`%`
15 GRANT SYSTEM_USER ON *.* TO `sys_usr`@`%` WITH GRANT OPTION
16
17 # 1.2 Revoke the SYSTEM_USER privilege from user;
18 CREATE USER non_sys_usr IDENTIFIED BY 'pwd';
19 GRANT ALL ON *.* TO non_sys_usr;
20 REVOKE SYSTEM_USER ON *.* FROM non_sys_usr;
21 REVOKE UPDATE ON *.* FROM non_sys_usr;
22 SHOW GRANTS FOR non_sys_usr;
23 Grants for non_sys_usr@%
24 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 `non_sys_usr`@`%`
25 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_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `non_sys_usr`@`%`
26
27 # 1.3 Role is granted SYSTEM_USER priv only if it is granted explictly
28 CREATE ROLE sys_role;
29 SHOW GRANTS FOR sys_role;
30 Grants for sys_role@%
31 GRANT USAGE ON *.* TO `sys_role`@`%`
32 GRANT SYSTEM_USER ON *.* TO sys_role WITH GRANT OPTION;
33 SHOW GRANTS FOR sys_role;
34 Grants for sys_role@%
35 GRANT USAGE ON *.* TO `sys_role`@`%`
36 GRANT SYSTEM_USER ON *.* TO `sys_role`@`%` WITH GRANT OPTION
37
38 # 1.4 Revoke the SYSTEM_USER privilege from user;
39 CREATE USER non_sys_role;
40 GRANT ALL ON *.* TO non_sys_role;
41 REVOKE SYSTEM_USER ON *.* FROM non_sys_role;
42 SHOW GRANTS FOR non_sys_role;
43 Grants for non_sys_role@%
44 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, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `non_sys_role`@`%`
45 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_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `non_sys_role`@`%`
46 #------------------------------------------------------------------------
47 # 2. sys_usr cannot modify the other users properties unless the former
48 # is granted the relevant properties to do so
49 #------------------------------------------------------------------------
50 CREATE USER test_usr;
51 ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
52 ALTER USER non_sys_usr IDENTIFIED BY 'pwd';
53 ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
54 RENAME USER non_sys_usr TO power_usr;
55 ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
56 DROP USER non_sys_usr;
57 ERROR 42000: Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
58 #------------------------------------------------------------------------
59 # 3. non_sys_usr cannot modify the other sys_usr even if former is
60 # granted global privileges
61 #------------------------------------------------------------------------
62 CREATE USER test_user;
63 ALTER USER test_user IDENTIFIED BY 'pwd';
64 RENAME USER test_user TO temp_user;
65 DROP USER temp_user;
66 ALTER USER sys_usr IDENTIFIED BY 'pwd';
67 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
68 RENAME USER sys_usr TO power_usr;
69 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
70 DROP USER sys_usr;
71 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
72 #------------------------------------------------------------------------
73 # 4. non_sys_usr cannot grant/revoke SYSTEM_USER privilege to /from
74 # other users/roles
75 #------------------------------------------------------------------------
76 CREATE USER test_user;
77 GRANT SYSTEM_USER ON *.* TO test_user;
78 ERROR 42000: Access denied; you need (at least one of) the GRANT OPTION privilege(s) for this operation
79 REVOKE SYSTEM_USER FROM sys_usr;
80 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
81 GRANT SYSTEM_USER ON *.* TO sys_role;
82 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
83 GRANT SYSTEM_USER ON *.* TO non_sys_role;
84 ERROR 42000: Access denied; you need (at least one of) the GRANT OPTION privilege(s) for this operation
85 REVOKE SYSTEM_USER ON *.* FROM sys_role;
86 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
87 GRANT sys_role to test_user;
88 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
89 GRANT sys_role TO test_user;
90 SHOW GRANTS FOR test_user;
91 Grants for test_user@%
92 GRANT USAGE ON *.* TO `test_user`@`%`
93 GRANT `sys_role`@`%` TO `test_user`@`%`
94 REVOKE sys_role FROM test_user;
95 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
96 DROP USER test_user;
97 #------------------------------------------------------------------------
98 # 5. Mandatory Roles
99 #------------------------------------------------------------------------
100
101 # 5.1 Check with root user
102 SET @@GLOBAL.MANDATORY_ROLES='sys_role,non_sys_role';
103 ERROR HY000: Cannot set mandatory_roles: AuthId `sys_role`@`%` has 'SYSTEM_USER' privilege.
104 SET @@GLOBAL.MANDATORY_ROLES='sys_role';
105 ERROR HY000: Cannot set mandatory_roles: AuthId `sys_role`@`%` has 'SYSTEM_USER' privilege.
106 SET @@GLOBAL.MANDATORY_ROLES='non_sys_role';
107
108 # 5.2 Check with user who does not have system user privilege
109 SET @@GLOBAL.MANDATORY_ROLES='sys_role,non_sys_role';
110 ERROR HY000: Cannot set mandatory_roles: AuthId `sys_role`@`%` has 'SYSTEM_USER' privilege.
111 SET @@GLOBAL.MANDATORY_ROLES='sys_role';
112 ERROR HY000: Cannot set mandatory_roles: AuthId `sys_role`@`%` has 'SYSTEM_USER' privilege.
113 SET @@GLOBAL.MANDATORY_ROLES='non_sys_role';
114
115 # 5.3 Check with user who has system user privilege
116 SET @@GLOBAL.MANDATORY_ROLES='sys_role,non_sys_role';
117 ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
118 SET @@GLOBAL.MANDATORY_ROLES='sys_role';
119 ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
120
121 # 5.4 Non existing mandatory_role which is granted the SYSTEM_USER priv
122 # later
123 SET @@GLOBAL.MANDATORY_ROLES='test_role';
124 CREATE ROLE test_role;
125 GRANT SYSTEM_USER ON *.* TO 'test_role';
126 ERROR HY000: AuthId `test_role`@`%` is set as mandatory_roles. Cannot grant the 'SYSTEM_USER' privilege.
127 GRANT SYSTEM_USER ON *.* TO 'test_role';
128 ERROR HY000: AuthId `test_role`@`%` is set as mandatory_roles. Cannot grant the 'SYSTEM_USER' privilege.
129 GRANT SYSTEM_USER ON *.* TO 'test_role';
130 ERROR 42000: Access denied; you need (at least one of) the GRANT OPTION privilege(s) for this operation
131 SET @@GLOBAL.MANDATORY_ROLES='';
132 DROP ROLE test_role;
133
134 # 5.5 None of the roles in the role's hierarchy should have SYSTEM_USER
135 # privilege if former is set as mandatory_role.
136 CREATE ROLE r1, r2;
137 GRANT r1 TO r2;
138 GRANT sys_role TO r1;
139 SET @@GLOBAL.MANDATORY_ROLES='r2';
140 ERROR HY000: Cannot set mandatory_roles: AuthId `r2`@`%` has 'SYSTEM_USER' privilege.
141 # 5.6 Set persist should fail too.
142 SET PERSIST MANDATORY_ROLES = 'r2';
143 ERROR HY000: Cannot set mandatory_roles: AuthId `r2`@`%` has 'SYSTEM_USER' privilege.
144 # 5.6 Set persist should fail too.
145 SET PERSIST MANDATORY_ROLES = 'r2';
146 ERROR HY000: Cannot set mandatory_roles: AuthId `r2`@`%` has 'SYSTEM_USER' privilege.
147 # 5.7 We do not check the SET PERSIST_ONLY, should succeed.
148 SET PERSIST_ONLY MANDATORY_ROLES = 'r2';
149 # 5.8 On Server restart there should be a warning in the log file
150 # Restart server.
151 include/assert_grep.inc [Found the expected warning line in the server log.]
152 # System variable should not have been set.
153 SELECT @@mandatory_roles;
154 @@mandatory_roles
155
156 SET PERSIST mandatory_roles = '';
157 # 5.9 Set the system variable at the command line.
158 # Restart server.
159 include/assert_grep.inc [Found the expected warning line in the server log.]
160 # System variable should not have been set.
161 SELECT @@mandatory_roles;
162 @@mandatory_roles
163
164 # 5.10 Set the system variable in the cnf file. Warning should be logged.
165 include/assert_grep.inc [Found the expected warning line in the server log.]
166 # System variable should not have been set.
167 SELECT @@mandatory_roles;
168 @@mandatory_roles
169
170 DROP ROLE r1, r2;
171 #------------------------------------------------------------------------
172 # 6. If the definer has SYSTEM_USER privilege then owner must also have
173 # the SYSTEM_USER privilege.
174 #------------------------------------------------------------------------
175 CREATE TABLE test.t1 (c1 INT);
176 CREATE DATABASE restricted;
177 CREATE TABLE restricted.t1 (c1 int, restricted int);
178 INSERT INTO restricted.t1 VALUES (1,2);
179 GRANT SET_USER_ID ON *.* TO sys_usr;
180
181 # 6.1 Stored procedure and functions
182 SELECT CURRENT_USER();
183 CURRENT_USER()
184 sys_usr@%
185 SELECT * FROM restricted.t1;
186 ERROR 42000: SELECT command denied to user 'sys_usr'@'localhost' for table 't1'
187 USE test;
188 CREATE DEFINER=root@localhost PROCEDURE p1() SELECT * FROM restricted.t1;
189 # With the SET_USER_ID privilege we can escalate the authorization
190 CALL p1();
191 c1 restricted
192 1 2
193 CREATE DEFINER=root@localhost FUNCTION f1(i INT) RETURNS INT(10) DETERMINISTIC
194 RETURN i*10;
195 # With the SET_USER_ID privilege we can escalate the authorization
196 SELECT f1(2);
197 f1(2)
198 20
199
200 SELECT CURRENT_USER();
201 CURRENT_USER()
202 non_sys_usr@%
203 USE test;
204 CREATE DEFINER=root@localhost PROCEDURE p2() SELECT * FROM restricted.t1;
205 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
206 CREATE DEFINER=root@localhost FUNCTION f2(i INT) RETURNS INT(10) DETERMINISTIC
207 RETURN i*10;
208 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
209 # Invoker has EXECUTE privilege but not SYSTEM_USER privilege
210 CALL p1();
211 c1 restricted
212 1 2
213 ALTER PROCEDURE test.p1 SQL SECURITY INVOKER;
214 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
215 DROP PROCEDURE test.p1;
216 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
217 ALTER FUNCTION test.f1 SQL SECURITY INVOKER;
218 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
219 DROP FUNCTION test.f1;
220 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
221
222 # 6.2 Create trigger
223 SELECT CURRENT_USER();
224 CURRENT_USER()
225 sys_usr@%
226 CREATE DEFINER=root@localhost TRIGGER test.tr1 BEFORE INSERT ON test.t1
227 FOR EACH ROW INSERT INTO restricted.t1 VALUES (1,1);
228 # Use trigger to escalate privileges
229 INSERT INTO test.t1 VALUES (1);
230 SELECT * FROM restricted.t1;
231 c1 restricted
232 1 2
233 1 1
234 CREATE DEFINER=root@localhost TRIGGER test.tr2 BEFORE INSERT ON test.t1
235 FOR EACH ROW INSERT INTO restricted.t1 VALUES (1,1);
236 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
237
238 # 6.3 Create View
239 # We need some privilege on the column for CREATE VIEW to work
240 GRANT INSERT(restricted) ON restricted.t1 TO sys_usr;
241 SHOW GRANTS FOR CURRENT_USER();
242 Grants for sys_usr@%
243 GRANT USAGE ON *.* TO `sys_usr`@`%`
244 GRANT SET_USER_ID ON *.* TO `sys_usr`@`%`
245 GRANT SYSTEM_USER ON *.* TO `sys_usr`@`%` WITH GRANT OPTION
246 GRANT INSERT (`restricted`) ON `restricted`.`t1` TO `sys_usr`@`%`
247 GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`p1` TO `sys_usr`@`%`
248 GRANT EXECUTE, ALTER ROUTINE ON FUNCTION `test`.`f1` TO `sys_usr`@`%`
249 CREATE DEFINER=root@localhost SQL SECURITY DEFINER VIEW v1 AS
250 SELECT a.restricted FROM restricted.t1 as a;
251 # Use escalated privileges to read restricted column.
252 SELECT * FROM v1;
253 restricted
254 2
255 1
256 CREATE DEFINER=root@localhost SQL SECURITY DEFINER VIEW v2 AS
257 SELECT a.restricted FROM restricted.t1 as a;
258 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
259 ALTER ALGORITHM=MERGE VIEW v1 AS SELECT a.restricted FROM restricted.t1 as a;
260 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
261 DROP VIEW v1;
262 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
263
264 # 6.4 Create Event
265 CREATE DEFINER=root@localhost EVENT test.eve1 ON SCHEDULE AT
266 CURRENT_TIMESTAMP + INTERVAL 2 HOUR
267 DO BEGIN
268 INSERT INTO restricted.t1 VALUES (5,5);
269 END ;$$
270 CREATE DEFINER=root@localhost EVENT test.eve2 ON SCHEDULE AT
271 CURRENT_TIMESTAMP + INTERVAL 2 SECOND
272 DO BEGIN
273 INSERT INTO restricted.t1 VALUES (5,5);
274 END ;$$
275 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
276 ALTER EVENT test.eve1 RENAME TO test.eve2;
277 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
278 DROP EVENT test.eve1;
279 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
280 # Cleanup
281 DROP TRIGGER test.tr1;
282 DROP PROCEDURE p1;
283 DROP FUNCTION f1;
284 DROP DATABASE restricted;
285 DROP VIEW test.v1;
286 DROP TABLE test.t1;
287 DROP EVENT test.eve1;
288 #------------------------------------------------------------------------
289 # 7. If SYSTEM_USER privilege is granted to definer and invoker
290 # through roles
291 #------------------------------------------------------------------------
292 USE test;
293 CREATE TABLE test.t1 (c1 int, c2 int);
294 INSERT INTO test.t1 VALUES(1,2);
295 CREATE USER test_user@localhost;
296 GRANT sys_role TO test_user@localhost;
297 GRANT ALL ON test.* TO test_user@localhost;
298 GRANT EXECUTE ON *.* TO test_user@localhost;
299
300 # 7.1 Invoker is not granted SYSTEM_USER priv through roles
301 # 7.1.1 ALTER AND DROP STORED PROCEDURE
302 CREATE DEFINER=test_user@localhost PROCEDURE p1() SELECT * FROM test.t1;
303 SHOW GRANTS FOR test_user@localhost;
304 Grants for test_user@localhost
305 GRANT EXECUTE ON *.* TO `test_user`@`localhost`
306 GRANT ALL PRIVILEGES ON `test`.* TO `test_user`@`localhost`
307 GRANT `sys_role`@`%` TO `test_user`@`localhost`
308 CALL p1();
309 c1 c2
310 1 2
311 ALTER PROCEDURE test.p1 SQL SECURITY INVOKER;
312 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
313 DROP PROCEDURE test.p1;
314 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
315 # 7.1.2 ALTER and DROP FUNCTIONS
316 CREATE DEFINER=test_user@localhost FUNCTION f1(i INT) RETURNS INT(10) DETERMINISTIC
317 RETURN i*10;
318 Warnings:
319 Warning 1681 Integer display width is deprecated and will be removed in a future release.
320 SELECT f1(2);
321 f1(2)
322 20
323 ALTER FUNCTION test.f1 SQL SECURITY INVOKER;
324 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
325 DROP FUNCTION test.f1;
326 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
327 # 7.1.3 ALTER and DROP View
328 CREATE DEFINER=test_user@localhost SQL SECURITY DEFINER VIEW v1 AS
329 SELECT a.c1 FROM test.t1 as a;
330 SELECT * FROM v1;
331 c1
332 1
333 ALTER VIEW v1 AS SELECT a.c1 FROM test.t1 as a;
334 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
335 DROP VIEW v1;
336 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
337
338 # 7.1.4 ALTER and DROP EVENT
339 CREATE DEFINER=test_user@localhost EVENT test.eve1 ON SCHEDULE AT
340 CURRENT_TIMESTAMP + INTERVAL 2 HOUR
341 DO BEGIN
342 INSERT INTO test.t1 VALUES (5,5);
343 END ;$$
344 ALTER EVENT test.eve1 RENAME TO test.eve2;
345 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
346 DROP EVENT test.eve1;
347 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
348
349 # 7.2 Invoker is also granted SYSTEM_USER priv through roles
350 CREATE ROLE r1, r2;
351 GRANT SYSTEM_USER ON *.* TO r1;
352 GRANT r1 TO r2;
353 GRANT r2 TO non_sys_usr;
354 # 7.2.1 ALTER and DROP PROCEDURE
355 CALL p1();
356 c1 c2
357 1 2
358 ALTER PROCEDURE test.p1 SQL SECURITY INVOKER;
359 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
360 DROP PROCEDURE test.p1;
361 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
362 # Activate the role to get the SYSTEM_USER priv
363 SET ROLE r2;
364 ALTER PROCEDURE test.p1 SQL SECURITY INVOKER;
365 CALL p1();
366 c1 c2
367 1 2
368 DROP PROCEDURE test.p1;
369 SET ROLE NONE;
370 # 7.2.2 ALTER and DROP FUNCTIONS
371 SELECT f1(2);
372 f1(2)
373 20
374 ALTER FUNCTION test.f1 SQL SECURITY INVOKER;
375 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
376 DROP FUNCTION test.f1;
377 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
378 SELECT f1(2);
379 f1(2)
380 20
381 # Activate the role to get the SYSTEM_USER priv
382 SET ROLE r2;
383 ALTER FUNCTION test.f1 SQL SECURITY INVOKER;
384 DROP FUNCTION test.f1;
385 SET ROLE NONE;
386 # 7.2.3 ALTER and DROP VIEW
387 ALTER ALGORITHM=MERGE VIEW v1 AS SELECT a.c1 FROM test.t1 as a;
388 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
389 DROP VIEW v1;
390 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
391 # Activate the role to get the SYSTEM_USER priv
392 SET ROLE r2;
393 ALTER ALGORITHM=MERGE VIEW v1 AS SELECT a.c1 FROM test.t1 as a;
394 DROP VIEW v1;
395 SET ROLE NONE;
396
397 # 7.2.4 ALTER and DROP EVENT
398 ALTER EVENT test.eve1 RENAME TO test.eve2;
399 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
400 DROP EVENT test.eve1;
401 ERROR 42000: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
402 # Activate the role to get the SYSTEM_USER priv
403 SET ROLE r2;
404 ALTER EVENT test.eve1 RENAME TO test.eve2;
405 DROP EVENT test.eve2;
406 SET ROLE NONE;
407 # Cleanup
408 DROP USER test_user@localhost;
409 DROP ROLE r1, r2;
410 DROP TABLE test.t1;
411 #
412 # Global clean up
413 #
414 DROP USER sys_usr;
415 DROP USER non_sys_usr;
416 DROP USER sys_role;
417 DROP USER non_sys_role;
418 call mtr.add_suppression(".*Cannot set mandatory_roles: AuthId.* privilege.");
419 RESET PERSIST mandatory_roles;
420 # restart: