Debian Codegraph @

mysql-test/suite/rpl/r/rpl_partial_revokes_add_remove.result

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 SELECT @@global.partial_revokes;
7 @@global.partial_revokes
8 1
9 CREATE DATABASE pr_db;
10 CREATE DATABASE normal_db;
11 CREATE TABLE pr_db.t1(c1 int, c2 int);
12 INSERT INTO pr_db.t1 values (1,2), (3,4);
13 CREATE TABLE normal_db.t1(c1 int, c2 int);
14 INSERT INTO normal_db.t1 values (1,2), (3,4);
15 -------------------------------------------------------------------------
16 Tests related to users
17 -------------------------------------------------------------------------
18 #
19 # 1. Grantor does not have restriction list but has global priv
20 #
21 CREATE USER foo, bar;
22 GRANT INSERT,SELECT ON *.* TO foo WITH GRANT OPTION;
23 [connection foo_master]
24 # Must fail since no priv granted to bar yet.
25 REVOKE INSERT ON mysql.* FROM bar;
26 ERROR 42000: There is no such grant defined for user 'bar' on host '%'
27 # Create global insert priv with partial revoke for bar.
28 GRANT INSERT ON *.* TO bar;
29 REVOKE INSERT ON mysql.* FROM bar;
30 SHOW GRANTS FOR bar;
31 Grants for bar@%
32 GRANT INSERT ON *.* TO `bar`@`%`
33 REVOKE INSERT ON `mysql`.* FROM `bar`@`%`
34 include/sync_slave_sql_with_master.inc
35 [connection slave]
36 SHOW GRANTS FOR bar;
37 Grants for bar@%
38 GRANT INSERT ON *.* TO `bar`@`%`
39 REVOKE INSERT ON `mysql`.* FROM `bar`@`%`
40 [connection foo_master]
41 # Clear partial revoke from bar but global INSERT priv remains as it is
42 GRANT INSERT ON *.* TO bar;
43 SHOW GRANTS FOR bar;
44 Grants for bar@%
45 GRANT INSERT ON *.* TO `bar`@`%`
46 include/sync_slave_sql_with_master.inc
47 [connection slave]
48 SHOW GRANTS FOR bar;
49 Grants for bar@%
50 GRANT INSERT ON *.* TO `bar`@`%`
51 [connection foo_master]
52 # Create the partial revoke on mysql db again.
53 REVOKE INSERT ON mysql.* FROM bar;
54 SHOW GRANTS FOR bar;
55 Grants for bar@%
56 GRANT INSERT ON *.* TO `bar`@`%`
57 REVOKE INSERT ON `mysql`.* FROM `bar`@`%`
58 include/sync_slave_sql_with_master.inc
59 [connection slave]
60 SHOW GRANTS FOR bar;
61 Grants for bar@%
62 GRANT INSERT ON *.* TO `bar`@`%`
63 REVOKE INSERT ON `mysql`.* FROM `bar`@`%`
64 [connection foo_master]
65 # Remove partial revoke only
66 GRANT INSERT ON mysql.* TO bar;
67 SHOW GRANTS FOR bar;
68 Grants for bar@%
69 GRANT INSERT ON *.* TO `bar`@`%`
70 include/sync_slave_sql_with_master.inc
71 [connection slave]
72 SHOW GRANTS FOR bar;
73 Grants for bar@%
74 GRANT INSERT ON *.* TO `bar`@`%`
75 [connection foo_master]
76 # Grant DB level privilege only
77 GRANT INSERT ON mysql.* TO bar;
78 SHOW GRANTS FOR bar;
79 Grants for bar@%
80 GRANT INSERT ON *.* TO `bar`@`%`
81 GRANT INSERT ON `mysql`.* TO `bar`@`%`
82 include/sync_slave_sql_with_master.inc
83 [connection slave]
84 SHOW GRANTS FOR bar;
85 Grants for bar@%
86 GRANT INSERT ON *.* TO `bar`@`%`
87 GRANT INSERT ON `mysql`.* TO `bar`@`%`
88 [connection foo_master]
89 DROP USER foo, bar;
90 #
91 # 2. Grantor has restriction list and grantee has global priv
92 #
93 CREATE USER foo, bar;
94 GRANT INSERT, SELECT, UPDATE ON *.* TO foo WITH GRANT OPTION;
95 # Restrict INSERT on mysql DB.
96 REVOKE INSERT ON mysql.* FROM foo;
97 SHOW GRANTS FOR foo;
98 Grants for foo@%
99 GRANT SELECT, INSERT, UPDATE ON *.* TO `foo`@`%` WITH GRANT OPTION
100 REVOKE INSERT ON `mysql`.* FROM `foo`@`%`
101 # Restrict UPDATE on mysql DB as well.
102 REVOKE UPDATE ON mysql.* FROM foo;
103 SHOW GRANTS FOR foo;
104 Grants for foo@%
105 GRANT SELECT, INSERT, UPDATE ON *.* TO `foo`@`%` WITH GRANT OPTION
106 REVOKE INSERT, UPDATE ON `mysql`.* FROM `foo`@`%`
107 # Must fail, DELETE privilege is not granted
108 REVOKE DELETE ON mysql.* FROM foo;
109 ERROR 42000: There is no such grant defined for user 'foo' on host '%'
110 GRANT INSERT ON *.* TO bar;
111 [connection foo_master]
112 GRANT INSERT ON mysql.* TO bar;
113 ERROR 42000: Access denied for user 'foo'@'%' to database 'mysql'
114 REVOKE INSERT ON mysql.* FROM bar;
115 ERROR 42000: Access denied for user 'foo'@'%' to database 'mysql'
116 REVOKE INSERT ON *.* FROM bar;
117 ERROR HY000: Access denied for AuthId `foo`@`%` to database 'mysql'.
118 # Grantee will not get the restriction list from grantor because
119 # former already has higher priv than grantor
120 GRANT INSERT ON *.* TO bar;
121 SHOW GRANTS FOR bar;
122 Grants for bar@%
123 GRANT INSERT ON *.* TO `bar`@`%`
124 include/sync_slave_sql_with_master.inc
125 [connection slave]
126 SHOW GRANTS FOR bar;
127 Grants for bar@%
128 GRANT INSERT ON *.* TO `bar`@`%`
129 [connection foo_master]
130 DROP USER foo, bar;
131 #
132 # 3. Grantor has restriction list and grantee has DB level privileges
133 #
134 CREATE USER foo, bar;
135 GRANT INSERT, UPDATE, SELECT ON *.* TO foo WITH GRANT OPTION;
136 # Restrict INSERT and UPDATE on mysql DB from foo.
137 REVOKE INSERT,UPDATE ON mysql.* FROM foo;
138 SHOW GRANTS FOR foo;
139 Grants for foo@%
140 GRANT SELECT, INSERT, UPDATE ON *.* TO `foo`@`%` WITH GRANT OPTION
141 REVOKE INSERT, UPDATE ON `mysql`.* FROM `foo`@`%`
142 # Grant DB level INSERT priv on mysql DB to bar.
143 GRANT INSERT ON mysql.* TO bar;
144 SHOW GRANTS FOR bar;
145 Grants for bar@%
146 GRANT USAGE ON *.* TO `bar`@`%`
147 GRANT INSERT ON `mysql`.* TO `bar`@`%`
148 [connection foo_master]
149 REVOKE INSERT ON mysql.* FROM bar;
150 ERROR 42000: Access denied for user 'foo'@'%' to database 'mysql'
151 REVOKE INSERT ON *.* FROM bar;
152 ERROR HY000: Access denied for AuthId `foo`@`%` to database 'mysql'.
153 GRANT INSERT ON mysql.* TO bar;
154 ERROR 42000: Access denied for user 'foo'@'%' to database 'mysql'
155 # There should not be any restriction list:
156 # INSERT on mysql DB because bar already has access to this DB
157 # UPDATE on mysql DB because global UPDATE priv is not being granted
158 GRANT INSERT ON *.* TO bar;
159 SHOW GRANTS FOR bar;
160 Grants for bar@%
161 GRANT INSERT ON *.* TO `bar`@`%`
162 GRANT INSERT ON `mysql`.* TO `bar`@`%`
163 # Should be able to grant/revoke other DB level privileges without
164 # affecting existing partial revokes.
165 REVOKE ALL ON *.* FROM bar;
166 GRANT INSERT ON *.* TO bar;
167 GRANT INSERT ON `pr_db`.* TO bar;
168 SHOW GRANTS FOR bar;
169 Grants for bar@%
170 GRANT INSERT ON *.* TO `bar`@`%`
171 GRANT INSERT ON `pr_db`.* TO `bar`@`%`
172 REVOKE INSERT ON `mysql`.* FROM `bar`@`%`
173 # Revoke the privilge. Partial revokes should remain intact
174 REVOKE INSERT ON `pr_db`.* FROM bar;
175 SHOW GRANTS FOR bar;
176 Grants for bar@%
177 GRANT INSERT ON *.* TO `bar`@`%`
178 REVOKE INSERT ON `mysql`.* FROM `bar`@`%`
179 include/sync_slave_sql_with_master.inc
180 [connection slave]
181 SHOW GRANTS FOR bar;
182 Grants for bar@%
183 GRANT INSERT ON *.* TO `bar`@`%`
184 REVOKE INSERT ON `mysql`.* FROM `bar`@`%`
185 [connection foo_master]
186 DROP USER foo, bar;
187 #
188 # 4. Grantor and grantee both have restriction list
189 #
190 # 4.1 Restrictions are aggregated
191 CREATE USER foo, bar;
192 GRANT INSERT, SELECT, UPDATE ON *.* TO foo WITH GRANT OPTION;
193 # Restrict INSERT, UPDATE on mysql DB from foo.
194 REVOKE INSERT, UPDATE ON mysql.* FROM foo;
195 SELECT user, user_attributes FROM mysql.user WHERE user LIKE 'foo';
196 user user_attributes
197 foo {"Restrictions": [{"Database": "mysql", "Privileges": ["INSERT", "UPDATE"]}]}
198 GRANT DELETE, DROP, UPDATE, SELECT ON *.* TO bar;
199 # Restrict UPDATE and DROP on mysql DB.
200 REVOKE UPDATE, DROP ON mysql.* FROM bar;
201 SHOW GRANTS FOR bar;
202 Grants for bar@%
203 GRANT SELECT, UPDATE, DELETE, DROP ON *.* TO `bar`@`%`
204 REVOKE UPDATE, DROP ON `mysql`.* FROM `bar`@`%`
205 [connection foo_master]
206 # Restrictions from Grantor and Grantee must be aggregated
207 GRANT INSERT ON *.* TO bar;
208 SHOW GRANTS FOR bar;
209 Grants for bar@%
210 GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON *.* TO `bar`@`%`
211 REVOKE INSERT, UPDATE, DROP ON `mysql`.* FROM `bar`@`%`
212 include/sync_slave_sql_with_master.inc
213 [connection slave]
214 SHOW GRANTS FOR bar;
215 Grants for bar@%
216 GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON *.* TO `bar`@`%`
217 REVOKE INSERT, UPDATE, DROP ON `mysql`.* FROM `bar`@`%`
218 [connection foo_master]
219 REVOKE ALL ON *.* FROM foo, bar;
220 # 4.2 Restrictions are cleared
221 GRANT SELECT, CREATE, ALTER ON *.* TO foo WITH GRANT OPTION;
222 REVOKE SELECT ON mysql.* FROM foo;
223 GRANT SELECT, CREATE ON *.* TO bar;
224 REVOKE CREATE ON mysql.* FROM bar;
225 SHOW GRANTS FOR foo;
226 Grants for foo@%
227 GRANT SELECT, CREATE, ALTER ON *.* TO `foo`@`%` WITH GRANT OPTION
228 REVOKE SELECT ON `mysql`.* FROM `foo`@`%`
229 SHOW GRANTS FOR bar;
230 Grants for bar@%
231 GRANT SELECT, CREATE ON *.* TO `bar`@`%`
232 REVOKE CREATE ON `mysql`.* FROM `bar`@`%`
233 [connection foo_master]
234 # Aggregated restrictions turns out to be empty
235 GRANT SELECT, CREATE,ALTER ON *.* TO bar;
236 include/sync_slave_sql_with_master.inc
237 [connection slave]
238 SHOW GRANTS FOR bar;
239 Grants for bar@%
240 GRANT SELECT, CREATE, ALTER ON *.* TO `bar`@`%`
241 [connection foo_master]
242 SHOW GRANTS FOR bar;
243 Grants for bar@%
244 GRANT SELECT, CREATE, ALTER ON *.* TO `bar`@`%`
245 DROP USER foo, bar;
246 #
247 # 5. Grantor has restriction list and grantee does not have any priv
248 #
249 CREATE USER foo, bar;
250 GRANT INSERT, SELECT ON *.* TO foo WITH GRANT OPTION;
251 [connection foo_master]
252 # Grantor should be able to add revoke on mysql DB on itself
253 REVOKE INSERT ON mysql.* FROM foo;
254 SHOW GRANTS FOR foo;
255 Grants for foo@%
256 GRANT SELECT, INSERT ON *.* TO `foo`@`%` WITH GRANT OPTION
257 REVOKE INSERT ON `mysql`.* FROM `foo`@`%`
258 [connection foo_master_reconn]
259 # Restrictions must remain intact if grantor reconnects and try to
260 # grant global privilege to self. In other words, it must be no-op.
261 GRANT INSERT ON *.* TO foo;
262 SHOW GRANTS FOR foo;
263 Grants for foo@%
264 GRANT SELECT, INSERT ON *.* TO `foo`@`%` WITH GRANT OPTION
265 REVOKE INSERT ON `mysql`.* FROM `foo`@`%`
266 # Grantee should get the restriction_list of grantor
267 GRANT INSERT, SELECT ON *.* TO bar;
268 SHOW GRANTS FOR bar;
269 Grants for bar@%
270 GRANT SELECT, INSERT ON *.* TO `bar`@`%`
271 REVOKE INSERT ON `mysql`.* FROM `bar`@`%`
272 # Grantor should be able to add additional restrictions list
273 REVOKE INSERT,SELECT ON `pr_db`.* FROM bar;
274 SHOW GRANTS FOR bar;
275 Grants for bar@%
276 GRANT SELECT, INSERT ON *.* TO `bar`@`%`
277 REVOKE INSERT ON `mysql`.* FROM `bar`@`%`
278 REVOKE SELECT, INSERT ON `pr_db`.* FROM `bar`@`%`
279 # Additional restrictions of Grantee should be removed.
280 GRANT INSERT, SELECT ON *.* TO bar;
281 SHOW GRANTS FOR bar;
282 Grants for bar@%
283 GRANT SELECT, INSERT ON *.* TO `bar`@`%`
284 REVOKE INSERT ON `mysql`.* FROM `bar`@`%`
285 # Should remove, the global privileges and restrictions both
286 REVOKE SELECT, INSERT ON *.* FROM bar;
287 SHOW GRANTS FOR bar;
288 Grants for bar@%
289 GRANT USAGE ON *.* TO `bar`@`%`
290 # Grantee should get the restriction_list of grantor again
291 GRANT INSERT, SELECT ON *.* TO bar;
292 SHOW GRANTS FOR bar;
293 Grants for bar@%
294 GRANT SELECT, INSERT ON *.* TO `bar`@`%`
295 REVOKE INSERT ON `mysql`.* FROM `bar`@`%`
296 # Remove only global privilege from grantee
297 REVOKE SELECT ON *.* FROM bar;
298 SHOW GRANTS FOR bar;
299 Grants for bar@%
300 GRANT INSERT ON *.* TO `bar`@`%`
301 REVOKE INSERT ON `mysql`.* FROM `bar`@`%`
302 include/sync_slave_sql_with_master.inc
303 [connection slave]
304 SHOW GRANTS FOR bar;
305 Grants for bar@%
306 GRANT INSERT ON *.* TO `bar`@`%`
307 REVOKE INSERT ON `mysql`.* FROM `bar`@`%`
308 [connection foo_master]
309 DROP USER foo, bar;
310 #
311 # 6. Revoke global privilege as well restriction list from grantee
312 # when grantor does not have restriction list
313 #
314 CREATE USER foo, bar;
315 GRANT SELECT, INSERT ON *.* TO foo WITH GRANT OPTION;
316 # User already have DB level priv
317 GRANT INSERT ON mysql.* TO bar;
318 [connection foo_master]
319 # Grant global INSERT privilege to bar
320 GRANT INSERT ON *.* TO bar;
321 # Revoke INSERT on mysql DB from bar
322 REVOKE INSERT ON mysql.* FROM bar;
323 SHOW GRANTS FOR bar;
324 Grants for bar@%
325 GRANT INSERT ON *.* TO `bar`@`%`
326 include/sync_slave_sql_with_master.inc
327 [connection slave]
328 SHOW GRANTS FOR bar;
329 Grants for bar@%
330 GRANT INSERT ON *.* TO `bar`@`%`
331 [connection foo_master]
332 # Add partial revoke INSERT on mysql DB for bar.
333 REVOKE INSERT ON mysql.* FROM bar;
334 SHOW GRANTS FOR bar;
335 Grants for bar@%
336 GRANT INSERT ON *.* TO `bar`@`%`
337 REVOKE INSERT ON `mysql`.* FROM `bar`@`%`
338 include/sync_slave_sql_with_master.inc
339 [connection slave]
340 SHOW GRANTS FOR bar;
341 Grants for bar@%
342 GRANT INSERT ON *.* TO `bar`@`%`
343 REVOKE INSERT ON `mysql`.* FROM `bar`@`%`
344 [connection foo_master]
345 # Revoke Global privilege as well as partial revoke.
346 REVOKE INSERT ON *.* FROM bar;
347 SHOW GRANTS FOR bar;
348 Grants for bar@%
349 GRANT USAGE ON *.* TO `bar`@`%`
350 include/sync_slave_sql_with_master.inc
351 [connection slave]
352 SHOW GRANTS FOR bar;
353 Grants for bar@%
354 GRANT USAGE ON *.* TO `bar`@`%`
355 [connection foo_master]
356 DROP USER foo,bar;
357 #
358 # 7. Granting a global privilege should not affect the restrictions
359 # of different privileges on other databases.
360 #
361 CREATE USER foo;
362 CREATE DATABASE secret;
363 CREATE DATABASE meeting;
364 GRANT INSERT, SELECT, UPDATE ON *.* TO foo;
365 REVOKE SELECT,INSERT ON secret.* FROM foo;
366 REVOKE SELECT, UPDATE ON meeting.* FROM foo;
367 SHOW GRANTS FOR foo;
368 Grants for foo@%
369 GRANT SELECT, INSERT, UPDATE ON *.* TO `foo`@`%`
370 REVOKE SELECT, UPDATE ON `meeting`.* FROM `foo`@`%`
371 REVOKE SELECT, INSERT ON `secret`.* FROM `foo`@`%`
372 # Only SELECT restrictions should have removed from
373 # all databases in the restriction_list
374 GRANT SELECT ON *.* TO foo;
375 SHOW GRANTS FOR foo;
376 Grants for foo@%
377 GRANT SELECT, INSERT, UPDATE ON *.* TO `foo`@`%`
378 REVOKE UPDATE ON `meeting`.* FROM `foo`@`%`
379 REVOKE INSERT ON `secret`.* FROM `foo`@`%`
380 DROP USER foo;
381 DROP DATABASE secret;
382 DROP DATABASE meeting;
383 #
384 # 8. Granting a DB level privilege should not affect the restrictions
385 # of different privileges on other databases.
386 #
387 CREATE USER foo;
388 CREATE DATABASE secret;
389 CREATE DATABASE meeting;
390 GRANT INSERT,DELETE, SELECT, UPDATE ON *.* TO foo;
391 GRANT SELECT,INSERT ON meeting.* TO foo;
392 GRANT DELETE,UPDATE ON secret.* TO foo;
393 REVOKE SELECT,INSERT ON secret.* FROM foo;
394 REVOKE DELETE, UPDATE ON meeting.* FROM foo;
395 # Grant sub-set from the set of already granted privileges
396 GRANT SELECT ON meeting.* TO foo WITH GRANT OPTION;
397 # The restrictions should not have modified.
398 SHOW GRANTS FOR foo;
399 Grants for foo@%
400 GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `foo`@`%`
401 GRANT SELECT, INSERT ON `meeting`.* TO `foo`@`%` WITH GRANT OPTION
402 GRANT UPDATE, DELETE ON `secret`.* TO `foo`@`%`
403 REVOKE UPDATE, DELETE ON `meeting`.* FROM `foo`@`%`
404 REVOKE SELECT, INSERT ON `secret`.* FROM `foo`@`%`
405 # Grant all privileges from set of already granted privileges
406 GRANT SELECT,INSERT ON meeting.* TO foo WITH GRANT OPTION;
407 # The restrictions should not have modified.
408 SHOW GRANTS FOR foo;
409 Grants for foo@%
410 GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `foo`@`%`
411 GRANT SELECT, INSERT ON `meeting`.* TO `foo`@`%` WITH GRANT OPTION
412 GRANT UPDATE, DELETE ON `secret`.* TO `foo`@`%`
413 REVOKE UPDATE, DELETE ON `meeting`.* FROM `foo`@`%`
414 REVOKE SELECT, INSERT ON `secret`.* FROM `foo`@`%`
415 # Grant sub-set from the set of already granted privileges
416 GRANT UPDATE ON secret.* TO foo;
417 # The restrictions should not have modified.
418 SHOW GRANTS FOR foo;
419 Grants for foo@%
420 GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `foo`@`%`
421 GRANT SELECT, INSERT ON `meeting`.* TO `foo`@`%` WITH GRANT OPTION
422 GRANT UPDATE, DELETE ON `secret`.* TO `foo`@`%`
423 REVOKE UPDATE, DELETE ON `meeting`.* FROM `foo`@`%`
424 REVOKE SELECT, INSERT ON `secret`.* FROM `foo`@`%`
425 # Grant all privileges from set of already granted privileges
426 GRANT DELETE,UPDATE ON secret.* TO foo;
427 # The restrictions should not have modified.
428 SHOW GRANTS FOR foo;
429 Grants for foo@%
430 GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `foo`@`%`
431 GRANT SELECT, INSERT ON `meeting`.* TO `foo`@`%` WITH GRANT OPTION
432 GRANT UPDATE, DELETE ON `secret`.* TO `foo`@`%`
433 REVOKE UPDATE, DELETE ON `meeting`.* FROM `foo`@`%`
434 REVOKE SELECT, INSERT ON `secret`.* FROM `foo`@`%`
435 # Remove the partial revokes
436 GRANT SELECT,INSERT ON secret.* TO foo;
437 GRANT DELETE,UPDATE ON meeting.* TO foo;
438 SHOW GRANTS FOR foo;
439 Grants for foo@%
440 GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `foo`@`%`
441 GRANT SELECT, INSERT ON `meeting`.* TO `foo`@`%` WITH GRANT OPTION
442 GRANT UPDATE, DELETE ON `secret`.* TO `foo`@`%`
443 DROP USER foo;
444 DROP DATABASE secret;
445 DROP DATABASE meeting;
446 #
447 # 9. Revoke all privileges from user(s)
448 #
449 CREATE USER foo, bar, baz;
450 CREATE DATABASE secret_db;
451 #
452 # 9.1. Grantor has restriction list but grantee does not.
453 GRANT ALL ON *.* TO foo,baz;
454 GRANT ALL ON mysql.* TO foo,baz;
455 GRANT ALL ON mysql.* TO bar;
456 # All privileges must be cleared from all three users
457 REVOKE ALL ON *.* FROM foo;
458 REVOKE ALL ON mysql.* FROM baz;
459 REVOKE ALL ON mysql.* FROM bar;
460 SHOW GRANTS FOR foo;
461 Grants for foo@%
462 GRANT USAGE ON *.* TO `foo`@`%`
463 SHOW GRANTS FOR bar;
464 Grants for bar@%
465 GRANT USAGE ON *.* TO `bar`@`%`
466 SHOW GRANTS FOR baz;
467 Grants for baz@%
468 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 `baz`@`%`
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 `baz`@`%`
470 DROP USER baz;
471 # Create partial_revokes for foo
472 GRANT ALL ON *.* TO foo WITH GRANT OPTION;
473 REVOKE SELECT ON mysql.* FROM foo;
474 # GRANT ALL should remove the partial revokes created above
475 GRANT ALL ON *.* TO foo WITH GRANT OPTION;
476 SHOW GRANTS FOR foo;
477 Grants for foo@%
478 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 `foo`@`%` WITH GRANT OPTION
479 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 `foo`@`%` WITH GRANT OPTION
480 # create partial revokes again
481 REVOKE SELECT ON mysql.* FROM foo;
482 SHOW GRANTS FOR foo;
483 Grants for foo@%
484 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 `foo`@`%` WITH GRANT OPTION
485 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 `foo`@`%` WITH GRANT OPTION
486 REVOKE SELECT ON `mysql`.* FROM `foo`@`%`
487 GRANT ALL ON mysql.* TO bar;
488 [connection foo_master]
489 REVOKE ALL ON *.* FROM bar;
490 ERROR HY000: Access denied for AuthId `foo`@`%` to database 'mysql'.
491 REVOKE ALL ON mysql.* FROM bar;
492 GRANT ALL ON *.* TO bar;
493 REVOKE SELECT ON mysql.* FROM bar;
494 # grantor and grantee both have exact same privileges and partial revokes
495 SHOW GRANTS FOR foo;
496 Grants for foo@%
497 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 `foo`@`%` WITH GRANT OPTION
498 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 `foo`@`%` WITH GRANT OPTION
499 REVOKE SELECT ON `mysql`.* FROM `foo`@`%`
500 SHOW GRANTS FOR bar;
501 Grants for bar@%
502 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 `bar`@`%`
503 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 `bar`@`%`
504 REVOKE SELECT ON `mysql`.* FROM `bar`@`%`
505 # grantor should be able to remove the privileges & partial revokes both
506 REVOKE ALL ON *.* FROM bar;
507 SHOW GRANTS FOR bar;
508 Grants for bar@%
509 GRANT USAGE ON *.* TO `bar`@`%`
510 include/sync_slave_sql_with_master.inc
511 [connection slave]
512 SHOW GRANTS FOR bar;
513 Grants for bar@%
514 GRANT USAGE ON *.* TO `bar`@`%`
515 [connection foo_master]
516 REVOKE ALL ON *.* FROM foo,bar;
517 #
518 # 9.2. REVOKE ALL followed by GLOBAL grants should create restrictions
519 GRANT ALL ON *.* TO foo WITH GRANT OPTION;
520 # Following should create restrictions for mysql db
521 REVOKE ALL ON mysql.* FROM foo;
522 SHOW GRANTS FOR foo;
523 Grants for foo@%
524 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 `foo`@`%` WITH GRANT OPTION
525 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 `foo`@`%` WITH GRANT OPTION
526 REVOKE SELECT, 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 `mysql`.* FROM `foo`@`%`
527 # restrictions must pass to bar as well
528 GRANT ALL ON *.* TO bar;
529 SHOW GRANTS FOR bar;
530 Grants for bar@%
531 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 `bar`@`%`
532 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 `bar`@`%`
533 REVOKE SELECT, 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 `mysql`.* FROM `bar`@`%`
534 include/sync_slave_sql_with_master.inc
535 [connection slave]
536 SHOW GRANTS FOR bar;
537 Grants for bar@%
538 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 `bar`@`%`
539 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 `bar`@`%`
540 REVOKE SELECT, 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 `mysql`.* FROM `bar`@`%`
541 [connection foo_master]
542 REVOKE ALL ON *.* FROM foo,bar;
543 #
544 # 9.3. Grantee has restriction list but grantor does not.
545 GRANT CREATE USER ON *.* TO foo WITH GRANT OPTION;
546 GRANT INSERT,SELECT ON *.* TO foo, bar;
547 REVOKE SELECT,INSERT ON mysql.* FROM bar;
548 REVOKE INSERT ON secret_db.* FROM bar;
549 SHOW GRANTS FOR bar;
550 Grants for bar@%
551 GRANT SELECT, INSERT ON *.* TO `bar`@`%`
552 REVOKE SELECT, INSERT ON `mysql`.* FROM `bar`@`%`
553 REVOKE INSERT ON `secret_db`.* FROM `bar`@`%`
554 [connection foo_master]
555 REVOKE ALL ON *.* FROM bar;
556 SHOW GRANTS FOR bar;
557 Grants for bar@%
558 GRANT USAGE ON *.* TO `bar`@`%`
559 include/sync_slave_sql_with_master.inc
560 [connection slave]
561 SHOW GRANTS FOR bar;
562 Grants for bar@%
563 GRANT USAGE ON *.* TO `bar`@`%`
564 [connection foo_master]
565 #
566 # 9.4. Grantor and grantee both have restriction_list
567 GRANT INSERT,SELECT ON *.* TO bar;
568 REVOKE SELECT ON mysql.* FROM bar;
569 REVOKE SELECT,INSERT ON secret_db.* FROM bar;
570 SHOW GRANTS FOR bar;
571 Grants for bar@%
572 GRANT SELECT, INSERT ON *.* TO `bar`@`%`
573 REVOKE SELECT ON `mysql`.* FROM `bar`@`%`
574 REVOKE SELECT, INSERT ON `secret_db`.* FROM `bar`@`%`
575 GRANT UPDATE ON *.* TO foo;
576 REVOKE UPDATE,SELECT ON secret_db.* FROM foo;
577 SHOW GRANTS FOR foo;
578 Grants for foo@%
579 GRANT SELECT, INSERT, UPDATE, CREATE USER ON *.* TO `foo`@`%` WITH GRANT OPTION
580 REVOKE SELECT, UPDATE ON `secret_db`.* FROM `foo`@`%`
581 [connection foo_master]
582 # Should fail because foo cannot remove UPDATE priv on bar
583 REVOKE ALL ON *.* FROM bar;
584 ERROR HY000: Access denied for AuthId `foo`@`%` to database 'secret_db'.
585 GRANT UPDATE ON `secret_db`.* TO foo;
586 SHOW GRANTS FOR foo;
587 Grants for foo@%
588 GRANT SELECT, INSERT, UPDATE, CREATE USER ON *.* TO `foo`@`%` WITH GRANT OPTION
589 REVOKE SELECT ON `secret_db`.* FROM `foo`@`%`
590 [connection foo_master]
591 # Should work because restriction on foo is different from bar
592 REVOKE ALL ON *.* FROM bar;
593 SHOW GRANTS FOR bar;
594 Grants for bar@%
595 GRANT USAGE ON *.* TO `bar`@`%`
596 include/sync_slave_sql_with_master.inc
597 [connection slave]
598 SHOW GRANTS FOR bar;
599 Grants for bar@%
600 GRANT USAGE ON *.* TO `bar`@`%`
601 [connection foo_master]
602 DROP USER foo, bar;
603 DROP DATABASE secret_db;
604 #
605 # 10. Revoke a global privilege from grantee which is not present in the
606 # grantor's restriction_list
607 #
608 CREATE USER foo, bar;
609 GRANT SELECT,INSERT ON *.* TO foo WITH GRANT OPTION;
610 REVOKE INSERT ON mysql.* FROM foo;
611 GRANT SELECT ON *.* TO bar;
612 SHOW GRANTS FOR foo;
613 Grants for foo@%
614 GRANT SELECT, INSERT ON *.* TO `foo`@`%` WITH GRANT OPTION
615 REVOKE INSERT ON `mysql`.* FROM `foo`@`%`
616 [connection foo_master]
617 # Should work because restriction on foo is different from bar
618 REVOKE SELECT ON *.* FROM bar;
619 SHOW GRANTS FOR bar;
620 Grants for bar@%
621 GRANT USAGE ON *.* TO `bar`@`%`
622 include/sync_slave_sql_with_master.inc
623 [connection slave]
624 SHOW GRANTS FOR bar;
625 Grants for bar@%
626 GRANT USAGE ON *.* TO `bar`@`%`
627 [connection foo_master]
628 DROP USER foo, bar;
629 #
630 # 11. Partial revokes with anonymous user
631 #
632 INSERT INTO pr_db.t1 values (1,2), (3,4);
633 INSERT INTO normal_db.t1 values (1,2), (3,4);
634 #
635 # 11.1 Grantor is anonymous user
636 #
637 CREATE TEMPORARY TABLE mysql.db_copy AS SELECT * FROM mysql.db;
638 DELETE FROM mysql.db WHERE host='%';
639 FLUSH PRIVILEGES;
640 CREATE USER ''@'%', ''@'';
641 GRANT SELECT ON *.* TO ''@'%' WITH GRANT OPTION;
642 REVOKE SELECT ON pr_db.* FROM ''@'%';
643 [connection anonymous_con_1]
644 SHOW GRANTS;
645 Grants for @%
646 GRANT SELECT ON *.* TO ``@`%` WITH GRANT OPTION
647 REVOKE SELECT ON `pr_db`.* FROM ``@`%`
648 SELECT COUNT(*) FROM normal_db.t1;
649 COUNT(*)
650 4
651 SELECT COUNT(*) FROM pr_db.t1;
652 ERROR 42000: SELECT command denied to user ''@'localhost' for table 't1'
653 GRANT SELECT ON *.* TO ''@'';
654 SHOW GRANTS FOR ''@'';
655 Grants for @
656 GRANT SELECT ON *.* TO ``@``
657 REVOKE SELECT ON `pr_db`.* FROM ``@``
658 DROP USER ''@'%',''@'';
659 #
660 # 11.2 Grantee is anonymous user
661 #
662 CREATE USER foo@localhost,''@'%';
663 GRANT SELECT ON *.* TO foo@localhost,''@'%' WITH GRANT OPTION;
664 [connection foo_master]
665 # Create partial revoke for anonymous user from normal user
666 REVOKE SELECT ON pr_db.* FROM ''@'%';
667 SHOW GRANTS FOR ''@'%';
668 Grants for @%
669 GRANT SELECT ON *.* TO ``@`%` WITH GRANT OPTION
670 REVOKE SELECT ON `pr_db`.* FROM ``@`%`
671 # Anynmous user cannot access the restricted database
672 [connection anonymous_con_1]
673 SELECT COUNT(*) FROM normal_db.t1;
674 COUNT(*)
675 4
676 SELECT COUNT(*) FROM pr_db.t1;
677 ERROR 42000: SELECT command denied to user ''@'localhost' for table 't1'
678 DROP USER foo@localhost,''@'%';
679 DELETE FROM mysql.db;
680 INSERT INTO mysql.db SELECT * FROM mysql.db_copy;
681 FLUSH PRIVILEGES;
682 DROP TABLE mysql.db_copy;
683 #
684 # 12. Hybrid scenarios
685 #
686 CREATE USER foo, bar;
687 CREATE DATABASE robo;
688 GRANT UPDATE, INSERT ON *.* TO bar;
689 REVOKE UPDATE, INSERT ON robo.* FROM bar;
690 SHOW GRANTS FOR bar;
691 Grants for bar@%
692 GRANT INSERT, UPDATE ON *.* TO `bar`@`%`
693 REVOKE INSERT, UPDATE ON `robo`.* FROM `bar`@`%`
694 # 12.1. Remove the applicable partial revokes(update and insert) as
695 # well as grant applicable DB level privileges(select and delete)
696 GRANT INSERT, UPDATE, DELETE, SELECT ON robo.* TO bar;
697 SHOW GRANTS FOR bar;
698 Grants for bar@%
699 GRANT INSERT, UPDATE ON *.* TO `bar`@`%`
700 GRANT SELECT, DELETE ON `robo`.* TO `bar`@`%`
701 # 12.2. Add the applicable partial revokes(update and insert) as well
702 # as revoke applicable DB level privileges(select and delete)
703 REVOKE INSERT, UPDATE, DELETE, SELECT ON robo.* FROM bar;
704 SHOW GRANTS FOR bar;
705 Grants for bar@%
706 GRANT INSERT, UPDATE ON *.* TO `bar`@`%`
707 REVOKE INSERT, UPDATE ON `robo`.* FROM `bar`@`%`
708 # 12.3 Grant global and DB level privilege then revoke the DB level
709 GRANT SELECT, DROP ON *.* TO foo;
710 GRANT DROP ON robo.* TO foo;
711 SHOW GRANTS FOR foo;
712 Grants for foo@%
713 GRANT SELECT, DROP ON *.* TO `foo`@`%`
714 GRANT DROP ON `robo`.* TO `foo`@`%`
715 REVOKE SELECT, DROP ON robo.* FROM foo;
716 SHOW GRANTS FOR foo;
717 Grants for foo@%
718 GRANT SELECT, DROP ON *.* TO `foo`@`%`
719 REVOKE SELECT ON `robo`.* FROM `foo`@`%`
720 DROP USER foo,bar;
721 DROP DATABASE robo;
722 #
723 # 13. Misc scenarios
724 #
725 # 13.1 Revoking DB level privilege multiple times should not
726 # create/affect the restrictions
727 CREATE USER foo;
728 GRANT ALL ON mysql.* TO foo;
729 REVOKE SELECT ON mysql.* FROM foo;
730 REVOKE SELECT ON mysql.* FROM foo;
731 SHOW GRANTS FOR foo;
732 Grants for foo@%
733 GRANT USAGE ON *.* TO `foo`@`%`
734 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 `mysql`.* TO `foo`@`%`
735 # 13.2 Granting DB level privilege multiple times should not
736 # create/affect the restrictions
737 REVOKE ALL ON mysql.* FROM foo;
738 GRANT SELECT ON mysql.* TO foo;
739 GRANT SELECT ON mysql.* TO foo;
740 SHOW GRANTS FOR foo;
741 Grants for foo@%
742 GRANT USAGE ON *.* TO `foo`@`%`
743 GRANT SELECT ON `mysql`.* TO `foo`@`%`
744 DROP USER foo;
745 ------------Tests related to Users end here -----------------------------
746 -------------------------------------------------------------------------
747 Tests related to Roles
748 -------------------------------------------------------------------------
749 #
750 # 1. Activate/Deactivate roles with partial revokes
751 #
752 CREATE USER foo, bar;
753 CREATE ROLE insert_role, select_role;
754 GRANT INSERT ON *.* TO insert_role WITH GRANT OPTION;
755 REVOKE INSERT ON pr_db.* FROM insert_role;
756 GRANT SELECT ON *.* TO select_role WITH GRANT OPTION;
757 REVOKE SELECT ON pr_db.* FROM select_role;
758 GRANT ROLE_ADMIN ON *.* TO foo;
759 GRANT select_role TO foo;
760 GRANT insert_role TO foo;
761 SHOW GRANTS FOR foo;
762 Grants for foo@%
763 GRANT USAGE ON *.* TO `foo`@`%`
764 GRANT ROLE_ADMIN ON *.* TO `foo`@`%`
765 GRANT `insert_role`@`%`,`select_role`@`%` TO `foo`@`%`
766 [connection foo_master]
767 # Must fail because foo does not have the SELECT privilege
768 SELECT COUNT(*) FROM mysql.user WHERE user = 'foo';
769 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'user'
770 # 1.1. Activate the select_role, now SELECT should work except on pr_db
771 SET ROLE select_role;
772 SHOW GRANTS;
773 Grants for foo@%
774 GRANT SELECT ON *.* TO `foo`@`%` WITH GRANT OPTION
775 GRANT ROLE_ADMIN ON *.* TO `foo`@`%`
776 REVOKE SELECT ON `pr_db`.* FROM `foo`@`%`
777 GRANT `insert_role`@`%`,`select_role`@`%` TO `foo`@`%`
778 SELECT COUNT(*) FROM normal_db.t1;
779 COUNT(*)
780 4
781 SELECT COUNT(*) FROM pr_db.t1;
782 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
783 # Grant roles to another user 'bar'
784 GRANT select_role TO bar;
785 SHOW GRANTS FOR bar;
786 Grants for bar@%
787 GRANT USAGE ON *.* TO `bar`@`%`
788 GRANT `select_role`@`%` TO `bar`@`%`
789 include/sync_slave_sql_with_master.inc
790 [connection slave]
791 SHOW GRANTS FOR bar;
792 Grants for bar@%
793 GRANT USAGE ON *.* TO `bar`@`%`
794 GRANT `select_role`@`%` TO `bar`@`%`
795 [connection foo_master]
796 # 1.2. Activate the insert_role and deactivate the select_role
797 SET ROLE ALL EXCEPT select_role;
798 SHOW GRANTS;
799 Grants for foo@%
800 GRANT INSERT ON *.* TO `foo`@`%` WITH GRANT OPTION
801 GRANT ROLE_ADMIN ON *.* TO `foo`@`%`
802 REVOKE INSERT ON `pr_db`.* FROM `foo`@`%`
803 GRANT `insert_role`@`%`,`select_role`@`%` TO `foo`@`%`
804 # select must fail but insert should work
805 SELECT COUNT(*) FROM normal_db.t1;
806 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
807 INSERT INTO normal_db.t1 values (5,6);
808 # Grant roles to another user 'bar'
809 GRANT insert_role TO bar;
810 SHOW GRANTS FOR bar;
811 Grants for bar@%
812 GRANT USAGE ON *.* TO `bar`@`%`
813 GRANT `insert_role`@`%`,`select_role`@`%` TO `bar`@`%`
814 include/sync_slave_sql_with_master.inc
815 [connection slave]
816 SHOW GRANTS FOR bar;
817 Grants for bar@%
818 GRANT USAGE ON *.* TO `bar`@`%`
819 GRANT `insert_role`@`%`,`select_role`@`%` TO `bar`@`%`
820 [connection foo_master]
821 # 1.3. Deactivate the select_role activated before
822 SET ROLE NONE;
823 SHOW GRANTS;
824 Grants for foo@%
825 GRANT USAGE ON *.* TO `foo`@`%`
826 GRANT ROLE_ADMIN ON *.* TO `foo`@`%`
827 GRANT `insert_role`@`%`,`select_role`@`%` TO `foo`@`%`
828 SELECT COUNT(*) FROM normal_db.t1;
829 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
830 INSERT INTO normal_db.t1 values (5,6);
831 ERROR 42000: INSERT command denied to user 'foo'@'localhost' for table 't1'
832 # 1.4. Activate all roles
833 SET ROLE ALL;
834 SHOW GRANTS;
835 Grants for foo@%
836 GRANT SELECT, INSERT ON *.* TO `foo`@`%` WITH GRANT OPTION
837 GRANT ROLE_ADMIN ON *.* TO `foo`@`%`
838 REVOKE SELECT, INSERT ON `pr_db`.* FROM `foo`@`%`
839 GRANT `insert_role`@`%`,`select_role`@`%` TO `foo`@`%`
840 INSERT INTO normal_db.t1 values (7,8);
841 SELECT COUNT(*) FROM normal_db.t1;
842 COUNT(*)
843 6
844 SELECT COUNT(*) FROM pr_db.t1;
845 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
846 INSERT INTO pr_db.t1 values (7,8);
847 ERROR 42000: INSERT command denied to user 'foo'@'localhost' for table 't1'
848 GRANT insert_role, select_role TO bar;
849 SHOW GRANTS FOR bar;
850 Grants for bar@%
851 GRANT USAGE ON *.* TO `bar`@`%`
852 GRANT `insert_role`@`%`,`select_role`@`%` TO `bar`@`%`
853 include/sync_slave_sql_with_master.inc
854 [connection slave]
855 SHOW GRANTS FOR bar;
856 Grants for bar@%
857 GRANT USAGE ON *.* TO `bar`@`%`
858 GRANT `insert_role`@`%`,`select_role`@`%` TO `bar`@`%`
859 [connection foo_master]
860 # Deactivate the select_role activated before
861 SET ROLE NONE;
862 #
863 # 2. Default roles with partial revokes
864 #
865 CREATE USER baz;
866 GRANT select_role to baz;
867 SHOW GRANTS FOR foo;
868 Grants for foo@%
869 GRANT USAGE ON *.* TO `foo`@`%`
870 GRANT ROLE_ADMIN ON *.* TO `foo`@`%`
871 GRANT `insert_role`@`%`,`select_role`@`%` TO `foo`@`%`
872 SHOW GRANTS FOR bar;
873 Grants for bar@%
874 GRANT USAGE ON *.* TO `bar`@`%`
875 GRANT `insert_role`@`%`,`select_role`@`%` TO `bar`@`%`
876 SHOW GRANTS FOR baz;
877 Grants for baz@%
878 GRANT USAGE ON *.* TO `baz`@`%`
879 GRANT `select_role`@`%` TO `baz`@`%`
880 ALTER USER foo DEFAULT ROLE ALL;
881 SET DEFAULT ROLE select_role TO baz;
882 [connection foo_master]
883 SET ROLE DEFAULT;
884 SHOW GRANTS;
885 Grants for foo@%
886 GRANT SELECT, INSERT ON *.* TO `foo`@`%` WITH GRANT OPTION
887 GRANT ROLE_ADMIN ON *.* TO `foo`@`%`
888 REVOKE SELECT, INSERT ON `pr_db`.* FROM `foo`@`%`
889 GRANT `insert_role`@`%`,`select_role`@`%` TO `foo`@`%`
890 INSERT INTO normal_db.t1 values (9,10);
891 SELECT COUNT(*) FROM normal_db.t1;
892 COUNT(*)
893 7
894 SELECT COUNT(*) FROM pr_db.t1;
895 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
896 INSERT INTO pr_db.t1 values (9,10);
897 ERROR 42000: INSERT command denied to user 'foo'@'localhost' for table 't1'
898 GRANT insert_role, select_role TO bar;
899 SHOW GRANTS FOR bar;
900 Grants for bar@%
901 GRANT USAGE ON *.* TO `bar`@`%`
902 GRANT `insert_role`@`%`,`select_role`@`%` TO `bar`@`%`
903 include/sync_slave_sql_with_master.inc
904 [connection slave]
905 SHOW GRANTS FOR bar;
906 Grants for bar@%
907 GRANT USAGE ON *.* TO `bar`@`%`
908 GRANT `insert_role`@`%`,`select_role`@`%` TO `bar`@`%`
909 [connection foo_master]
910 [connection baz_master]
911 SET ROLE DEFAULT;
912 SHOW GRANTS;
913 Grants for baz@%
914 GRANT SELECT ON *.* TO `baz`@`%` WITH GRANT OPTION
915 REVOKE SELECT ON `pr_db`.* FROM `baz`@`%`
916 GRANT `select_role`@`%` TO `baz`@`%`
917 SELECT COUNT(*) FROM normal_db.t1;
918 COUNT(*)
919 7
920 SELECT COUNT(*) FROM pr_db.t1;
921 ERROR 42000: SELECT command denied to user 'baz'@'localhost' for table 't1'
922 DROP USER baz;
923 # Reset the default roles granted to foo earlier
924 [connection foo_master]
925 SET ROLE NONE;
926 SET DEFAULT ROLE NONE TO foo;
927 #
928 # 3. Mandatory roles with partial revokes
929 #
930 CREATE ROLE delete_role;
931 GRANT DELETE ON *.* TO delete_role;
932 REVOKE DELETE ON pr_db.* FROM delete_role;
933 SET GLOBAL mandatory_roles='delete_role';
934 [connection foo_master]
935 SET ROLE delete_role;
936 SHOW GRANTS;
937 Grants for foo@%
938 GRANT DELETE ON *.* TO `foo`@`%`
939 GRANT ROLE_ADMIN ON *.* TO `foo`@`%`
940 REVOKE DELETE ON `pr_db`.* FROM `foo`@`%`
941 GRANT `delete_role`@`%`,`insert_role`@`%`,`select_role`@`%` TO `foo`@`%`
942 DELETE FROM normal_db.t1;
943 DELETE FROM pr_db.t1;
944 ERROR 42000: DELETE command denied to user 'foo'@'localhost' for table 't1'
945 SET GLOBAL mandatory_roles='';
946 DROP ROLE delete_role;
947 DROP USER foo, bar;
948 DROP ROLE insert_role, select_role;
949 #
950 # 4. Aggregation should not reduce privileges
951 #
952 [connection default]
953 CREATE USER bar;
954 CREATE ROLE r1;
955 GRANT SELECT ON *.* TO r1, bar;
956 REVOKE SELECT ON mysql.* FROM r1;
957 GRANT INSERT ON *.* TO r1;
958 GRANT r1 TO bar;
959 SHOW GRANTS FOR bar;
960 Grants for bar@%
961 GRANT SELECT ON *.* TO `bar`@`%`
962 GRANT `r1`@`%` TO `bar`@`%`
963 SHOW GRANTS FOR bar USING 'r1';
964 Grants for bar@%
965 GRANT SELECT, INSERT ON *.* TO `bar`@`%`
966 GRANT `r1`@`%` TO `bar`@`%`
967 [connection bar_master]
968 SET ROLE ALL;
969 SELECT user FROM mysql.user WHERE user LIKE 'bar';
970 user
971 bar
972 SHOW GRANTS;
973 Grants for bar@%
974 GRANT SELECT, INSERT ON *.* TO `bar`@`%`
975 GRANT `r1`@`%` TO `bar`@`%`
976 [connection default]
977 REVOKE INSERT ON *.* FROM r1;
978 GRANT SELECT ON *.* TO r1, bar;
979 REVOKE SELECT ON mysql.* FROM bar;
980 SHOW GRANTS FOR bar;
981 Grants for bar@%
982 GRANT SELECT ON *.* TO `bar`@`%`
983 REVOKE SELECT ON `mysql`.* FROM `bar`@`%`
984 GRANT `r1`@`%` TO `bar`@`%`
985 SHOW GRANTS FOR bar USING 'r1';
986 Grants for bar@%
987 GRANT SELECT ON *.* TO `bar`@`%`
988 GRANT `r1`@`%` TO `bar`@`%`
989 [connection bar_master]
990 SET ROLE ALL;
991 SELECT user FROM mysql.user WHERE user LIKE 'bar';
992 user
993 bar
994 [connection default]
995 DROP ROLE r1;
996 DROP USER bar;
997 #
998 # 5. Restriction list of roles should apply to grantee
999 #
1000 [connection default]
1001 CREATE USER foo, bar;
1002 CREATE ROLE r1;
1003 GRANT SELECT ON *.* TO r1 WITH GRANT OPTION;
1004 REVOKE SELECT ON mysql.* FROM r1;
1005 GRANT r1 TO foo;
1006 [connection foo_master]
1007 SET ROLE ALL;
1008 SHOW GRANTS;
1009 Grants for foo@%
1010 GRANT SELECT ON *.* TO `foo`@`%` WITH GRANT OPTION
1011 REVOKE SELECT ON `mysql`.* FROM `foo`@`%`
1012 GRANT `r1`@`%` TO `foo`@`%`
1013 GRANT SELECT ON *.* TO bar;
1014 [connection default]
1015 SHOW GRANTS FOR bar;
1016 Grants for bar@%
1017 GRANT SELECT ON *.* TO `bar`@`%`
1018 REVOKE SELECT ON `mysql`.* FROM `bar`@`%`
1019 include/sync_slave_sql_with_master.inc
1020 [connection slave]
1021 SHOW GRANTS FOR bar;
1022 Grants for bar@%
1023 GRANT SELECT ON *.* TO `bar`@`%`
1024 REVOKE SELECT ON `mysql`.* FROM `bar`@`%`
1025 [connection foo_master]
1026 DROP ROLE r1;
1027 DROP USER foo, bar;
1028 #
1029 # 6. Roles have restrictions but user has global privilege
1030 #
1031 CREATE USER bar;
1032 CREATE ROLE r1;
1033 GRANT SELECT ON *.* TO r1, bar;
1034 REVOKE SELECT ON mysql.* FROM r1;
1035 GRANT r1 TO bar;
1036 [connection bar_master]
1037 SET ROLE ALL;
1038 # Restrictions of roles should not apply since user has higher privilege
1039 SHOW GRANTS;
1040 Grants for bar@%
1041 GRANT SELECT ON *.* TO `bar`@`%`
1042 GRANT `r1`@`%` TO `bar`@`%`
1043 # User's actual privileges ( and restrictions) must be visible
1044 SHOW GRANTS FOR bar;
1045 Grants for bar@%
1046 GRANT SELECT ON *.* TO `bar`@`%`
1047 GRANT `r1`@`%` TO `bar`@`%`
1048 include/sync_slave_sql_with_master.inc
1049 [connection slave]
1050 SHOW GRANTS FOR bar;
1051 Grants for bar@%
1052 GRANT SELECT ON *.* TO `bar`@`%`
1053 GRANT `r1`@`%` TO `bar`@`%`
1054 [connection bar_master]
1055 DROP USER bar;
1056 DROP ROLE r1;
1057 #
1058 # 7. Roles have global privilege but user has restrictions
1059 #
1060 CREATE USER bar;
1061 CREATE ROLE r1;
1062 GRANT SELECT ON *.* TO r1, bar;
1063 REVOKE SELECT ON mysql.* FROM bar;
1064 GRANT r1 TO bar;
1065 [connection bar_master]
1066 SET ROLE ALL;
1067 # User's restrictions should be removed through grantor(role).
1068 SHOW GRANTS;
1069 Grants for bar@%
1070 GRANT SELECT ON *.* TO `bar`@`%`
1071 GRANT `r1`@`%` TO `bar`@`%`
1072 # User's actual privileges ( and restrictions) must be visible
1073 SHOW GRANTS FOR bar;
1074 Grants for bar@%
1075 GRANT SELECT ON *.* TO `bar`@`%`
1076 REVOKE SELECT ON `mysql`.* FROM `bar`@`%`
1077 GRANT `r1`@`%` TO `bar`@`%`
1078 include/sync_slave_sql_with_master.inc
1079 [connection slave]
1080 SHOW GRANTS FOR bar;
1081 Grants for bar@%
1082 GRANT SELECT ON *.* TO `bar`@`%`
1083 REVOKE SELECT ON `mysql`.* FROM `bar`@`%`
1084 GRANT `r1`@`%` TO `bar`@`%`
1085 [connection bar_master]
1086 DROP USER bar;
1087 DROP ROLE r1;
1088 #
1089 # 8. Roles negating each other's restrictions
1090 #
1091 # 8.1 Both roles have partial revokes
1092 CREATE ROLE r1, r2;
1093 CREATE USER bar;
1094 GRANT SELECT,INSERT ON *.* TO r1, r2;
1095 REVOKE SELECT ON mysql.* FROM r1;
1096 REVOKE INSERT ON mysql.* FROM r2;
1097 # Grant privileges and roles to user
1098 GRANT DROP ON *.* TO bar;
1099 GRANT UPDATE ON mysql.* TO bar;
1100 GRANT r1, r2 TO bar;
1101 # Roles privileges and restrictions should apply to user
1102 SHOW GRANTS FOR bar USING r1;
1103 Grants for bar@%
1104 GRANT SELECT, INSERT, DROP ON *.* TO `bar`@`%`
1105 GRANT UPDATE ON `mysql`.* TO `bar`@`%`
1106 REVOKE SELECT ON `mysql`.* FROM `bar`@`%`
1107 GRANT `r1`@`%`,`r2`@`%` TO `bar`@`%`
1108 SHOW GRANTS FOR bar USING r2;
1109 Grants for bar@%
1110 GRANT SELECT, INSERT, DROP ON *.* TO `bar`@`%`
1111 GRANT UPDATE ON `mysql`.* TO `bar`@`%`
1112 REVOKE INSERT ON `mysql`.* FROM `bar`@`%`
1113 GRANT `r1`@`%`,`r2`@`%` TO `bar`@`%`
1114 # Roles global privileges should negate each others restrictions
1115 SHOW GRANTS FOR bar USING r1, r2;
1116 Grants for bar@%
1117 GRANT SELECT, INSERT, DROP ON *.* TO `bar`@`%`
1118 GRANT UPDATE ON `mysql`.* TO `bar`@`%`
1119 GRANT `r1`@`%`,`r2`@`%` TO `bar`@`%`
1120 [connection bar_master]
1121 SET ROLE r1, r2;
1122 SHOW GRANTS;
1123 Grants for bar@%
1124 GRANT SELECT, INSERT, DROP ON *.* TO `bar`@`%`
1125 GRANT UPDATE ON `mysql`.* TO `bar`@`%`
1126 GRANT `r1`@`%`,`r2`@`%` TO `bar`@`%`
1127 include/sync_slave_sql_with_master.inc
1128 [connection slave]
1129 SHOW GRANTS FOR bar;
1130 Grants for bar@%
1131 GRANT DROP ON *.* TO `bar`@`%`
1132 GRANT UPDATE ON `mysql`.* TO `bar`@`%`
1133 GRANT `r1`@`%`,`r2`@`%` TO `bar`@`%`
1134 [connection bar_master]
1135 DROP USER bar;
1136 DROP ROLE r1, r2;
1137 # 8.2 One role has partial revoke and another has DB level grant
1138 CREATE USER bar;
1139 CREATE ROLE r1, r2;
1140 GRANT r1, r2 TO bar;
1141 GRANT SELECT ON *.* TO r1;
1142 REVOKE SELECT ON mysql.* FROM r1;
1143 GRANT SELECT ON mysql.* TO r2;
1144 SHOW GRANTS FOR bar USING r1;
1145 Grants for bar@%
1146 GRANT SELECT ON *.* TO `bar`@`%`
1147 REVOKE SELECT ON `mysql`.* FROM `bar`@`%`
1148 GRANT `r1`@`%`,`r2`@`%` TO `bar`@`%`
1149 SHOW GRANTS FOR bar USING r2;
1150 Grants for bar@%
1151 GRANT USAGE ON *.* TO `bar`@`%`
1152 GRANT SELECT ON `mysql`.* TO `bar`@`%`
1153 GRANT `r1`@`%`,`r2`@`%` TO `bar`@`%`
1154 # DB level privilege and partial revoke negate each other
1155 SHOW GRANTS FOR bar USING r1, r2;
1156 Grants for bar@%
1157 GRANT SELECT ON *.* TO `bar`@`%`
1158 GRANT `r1`@`%`,`r2`@`%` TO `bar`@`%`
1159 [connection bar_master]
1160 SET ROLE r1, r2;
1161 SHOW GRANTS;
1162 Grants for bar@%
1163 GRANT SELECT ON *.* TO `bar`@`%`
1164 GRANT `r1`@`%`,`r2`@`%` TO `bar`@`%`
1165 SET ROLE r1;
1166 SHOW GRANTS;
1167 Grants for bar@%
1168 GRANT SELECT ON *.* TO `bar`@`%`
1169 REVOKE SELECT ON `mysql`.* FROM `bar`@`%`
1170 GRANT `r1`@`%`,`r2`@`%` TO `bar`@`%`
1171 SET ROLE r2;
1172 SHOW GRANTS;
1173 Grants for bar@%
1174 GRANT USAGE ON *.* TO `bar`@`%`
1175 GRANT SELECT ON `mysql`.* TO `bar`@`%`
1176 GRANT `r1`@`%`,`r2`@`%` TO `bar`@`%`
1177 SET ROLE r2, r1;
1178 SHOW GRANTS;
1179 Grants for bar@%
1180 GRANT SELECT ON *.* TO `bar`@`%`
1181 GRANT `r1`@`%`,`r2`@`%` TO `bar`@`%`
1182 include/sync_slave_sql_with_master.inc
1183 [connection slave]
1184 SHOW GRANTS FOR bar;
1185 Grants for bar@%
1186 GRANT USAGE ON *.* TO `bar`@`%`
1187 GRANT `r1`@`%`,`r2`@`%` TO `bar`@`%`
1188 [connection bar_master]
1189 DROP USER bar;
1190 DROP ROLE r1, r2;
1191 # 8.3 One role has partial revoke & grants and another has DB grants
1192 CREATE USER bar;
1193 CREATE ROLE r1, r2;
1194 GRANT r1, r2 TO bar;
1195 GRANT SELECT, DROP ON *.* TO r1;
1196 REVOKE SELECT ON mysql.* FROM r1;
1197 GRANT UPDATE ON mysql.* TO r1;
1198 GRANT SELECT, INSERT ON mysql.* TO r2;
1199 SHOW GRANTS FOR bar USING r1;
1200 Grants for bar@%
1201 GRANT SELECT, DROP ON *.* TO `bar`@`%`
1202 GRANT UPDATE ON `mysql`.* TO `bar`@`%`
1203 REVOKE SELECT ON `mysql`.* FROM `bar`@`%`
1204 GRANT `r1`@`%`,`r2`@`%` TO `bar`@`%`
1205 SHOW GRANTS FOR bar USING r2;
1206 Grants for bar@%
1207 GRANT USAGE ON *.* TO `bar`@`%`
1208 GRANT SELECT, INSERT ON `mysql`.* TO `bar`@`%`
1209 GRANT `r1`@`%`,`r2`@`%` TO `bar`@`%`
1210 # DB level privilege and partial revoke negate each other
1211 SHOW GRANTS FOR bar USING r1, r2;
1212 Grants for bar@%
1213 GRANT SELECT, DROP ON *.* TO `bar`@`%`
1214 GRANT INSERT, UPDATE ON `mysql`.* TO `bar`@`%`
1215 GRANT `r1`@`%`,`r2`@`%` TO `bar`@`%`
1216 [connection bar_master]
1217 SET ROLE r1, r2;
1218 SHOW GRANTS;
1219 Grants for bar@%
1220 GRANT SELECT, DROP ON *.* TO `bar`@`%`
1221 GRANT INSERT, UPDATE ON `mysql`.* TO `bar`@`%`
1222 GRANT `r1`@`%`,`r2`@`%` TO `bar`@`%`
1223 include/sync_slave_sql_with_master.inc
1224 [connection slave]
1225 SHOW GRANTS FOR bar;
1226 Grants for bar@%
1227 GRANT USAGE ON *.* TO `bar`@`%`
1228 GRANT `r1`@`%`,`r2`@`%` TO `bar`@`%`
1229 [connection bar_master]
1230 DROP USER bar;
1231 DROP ROLE r1,r2;
1232 #
1233 # 9. SET ROLE removes the restrictions from the user. Now, Global
1234 # grant/revoke should not carry restrictions to grantee
1235 #
1236 CREATE USER foo, bar;
1237 CREATE ROLE r1;
1238 GRANT SELECT, INSERT ON *.* TO bar WITH GRANT OPTION;
1239 REVOKE INSERT ON mysql.* FROM bar;
1240 GRANT INSERT ON *.* TO r1;
1241 GRANT r1 TO bar;
1242 [connection bar_master]
1243 # User should not have restriction for now
1244 SET ROLE ALL;
1245 SHOW GRANTS;
1246 Grants for bar@%
1247 GRANT SELECT, INSERT ON *.* TO `bar`@`%` WITH GRANT OPTION
1248 GRANT `r1`@`%` TO `bar`@`%`
1249 # Grantee should not carry the actual restrictions of grantor
1250 GRANT INSERT ON *.* TO foo;
1251 SHOW GRANTS FOR foo;
1252 Grants for foo@%
1253 GRANT INSERT ON *.* TO `foo`@`%`
1254 # Grantor should be able to remove the global privileges from grantee now
1255 REVOKE INSERT ON *.* FROM foo;
1256 SHOW GRANTS FOR foo;
1257 Grants for foo@%
1258 GRANT USAGE ON *.* TO `foo`@`%`
1259 # Grantor should be able to grant global privilege to self
1260 GRANT INSERT ON *.* TO bar;
1261 SHOW GRANTS;
1262 Grants for bar@%
1263 GRANT SELECT, INSERT ON *.* TO `bar`@`%` WITH GRANT OPTION
1264 GRANT `r1`@`%` TO `bar`@`%`
1265 [connection default]
1266 # Since, user granted himself global privilege therefore it's
1267 # restrictions has gone permanently now.
1268 SHOW GRANTS FOR bar;
1269 Grants for bar@%
1270 GRANT SELECT, INSERT ON *.* TO `bar`@`%` WITH GRANT OPTION
1271 GRANT `r1`@`%` TO `bar`@`%`
1272 include/sync_slave_sql_with_master.inc
1273 [connection slave]
1274 SHOW GRANTS FOR bar;
1275 Grants for bar@%
1276 GRANT SELECT, INSERT ON *.* TO `bar`@`%` WITH GRANT OPTION
1277 GRANT `r1`@`%` TO `bar`@`%`
1278 [connection bar_master]
1279 DROP USER foo, bar;
1280 DROP ROLE r1;
1281 #
1282 # 10. SET ROLE removes the restrictions from the user. Now, DB level
1283 # grant/revoke should work as intended.
1284 #
1285 CREATE USER foo, bar;
1286 CREATE ROLE r1;
1287 GRANT SELECT, INSERT ON *.* TO bar WITH GRANT OPTION;
1288 REVOKE INSERT ON mysql.* FROM bar;
1289 GRANT INSERT ON *.* TO r1;
1290 GRANT r1 TO bar;
1291 [connection bar_master]
1292 # User should not have restriction for now
1293 SET ROLE ALL;
1294 SHOW GRANTS;
1295 Grants for bar@%
1296 GRANT SELECT, INSERT ON *.* TO `bar`@`%` WITH GRANT OPTION
1297 GRANT `r1`@`%` TO `bar`@`%`
1298 # Grantor should be able to grant DB level privilege to grantee
1299 GRANT INSERT ON mysql.* TO foo;
1300 SHOW GRANTS FOR foo;
1301 Grants for foo@%
1302 GRANT USAGE ON *.* TO `foo`@`%`
1303 GRANT INSERT ON `mysql`.* TO `foo`@`%`
1304 # Grantor should be able to revoke DB Level priviulege from grantee
1305 REVOKE INSERT ON mysql.* FROM foo;
1306 SHOW GRANTS FOR foo;
1307 Grants for foo@%
1308 GRANT USAGE ON *.* TO `foo`@`%`
1309 # Should be able to grant DB level privilege to self
1310 GRANT INSERT ON mysql.* TO bar;
1311 SHOW GRANTS;
1312 Grants for bar@%
1313 GRANT SELECT, INSERT ON *.* TO `bar`@`%` WITH GRANT OPTION
1314 GRANT `r1`@`%` TO `bar`@`%`
1315 [connection default]
1316 # Since, user granted himself DB level privilege therefore it's
1317 # restrictions has gone permanently now.
1318 SHOW GRANTS FOR bar;
1319 Grants for bar@%
1320 GRANT SELECT, INSERT ON *.* TO `bar`@`%` WITH GRANT OPTION
1321 GRANT `r1`@`%` TO `bar`@`%`
1322 include/sync_slave_sql_with_master.inc
1323 [connection slave]
1324 SHOW GRANTS FOR bar;
1325 Grants for bar@%
1326 GRANT SELECT, INSERT ON *.* TO `bar`@`%` WITH GRANT OPTION
1327 GRANT `r1`@`%` TO `bar`@`%`
1328 [connection bar_master]
1329 DROP USER foo, bar;
1330 DROP ROLE r1;
1331 #
1332 # 11. SET ROLE removes the restrictions from the user. Now, DB level
1333 # grant/revoke ALL should work as intended.
1334 #
1335 CREATE USER foo, bar;
1336 CREATE ROLE r1;
1337 GRANT SELECT, INSERT ON *.* TO bar WITH GRANT OPTION;
1338 GRANT ALL ON mysql.* TO bar;
1339 REVOKE INSERT ON mysql.* FROM bar;
1340 REVOKE INSERT ON mysql.* FROM bar;
1341 GRANT INSERT ON *.* TO r1;
1342 GRANT r1 TO bar;
1343 [connection bar_master]
1344 # User should not have restriction for now
1345 SET ROLE ALL;
1346 SHOW GRANTS;
1347 Grants for bar@%
1348 GRANT SELECT, INSERT ON *.* TO `bar`@`%` WITH GRANT OPTION
1349 GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql`.* TO `bar`@`%`
1350 GRANT `r1`@`%` TO `bar`@`%`
1351 # Grantor should be able to grant ALL DB level privilege to grantee
1352 GRANT ALL ON mysql.* TO foo;
1353 SHOW GRANTS FOR foo;
1354 Grants for foo@%
1355 GRANT USAGE ON *.* TO `foo`@`%`
1356 GRANT ALL PRIVILEGES ON `mysql`.* TO `foo`@`%`
1357 # Grantor should be able to revoke ALL DB Level privilege from grantee
1358 REVOKE ALL ON mysql.* FROM foo;
1359 SHOW GRANTS FOR foo;
1360 Grants for foo@%
1361 GRANT USAGE ON *.* TO `foo`@`%`
1362 # Should be able to grant ALL DB level privileges to self
1363 GRANT ALL ON mysql.* TO bar;
1364 SHOW GRANTS;
1365 Grants for bar@%
1366 GRANT SELECT, INSERT ON *.* TO `bar`@`%` WITH GRANT OPTION
1367 GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql`.* TO `bar`@`%`
1368 GRANT `r1`@`%` TO `bar`@`%`
1369 [connection default]
1370 # Since, user granted himself all DB level privileges therefore it's
1371 # restrictions has gone permanently now.
1372 SHOW GRANTS FOR bar;
1373 Grants for bar@%
1374 GRANT SELECT, INSERT ON *.* TO `bar`@`%` WITH GRANT OPTION
1375 GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql`.* TO `bar`@`%`
1376 GRANT `r1`@`%` TO `bar`@`%`
1377 include/sync_slave_sql_with_master.inc
1378 [connection slave]
1379 SHOW GRANTS FOR bar;
1380 Grants for bar@%
1381 GRANT SELECT, INSERT ON *.* TO `bar`@`%` WITH GRANT OPTION
1382 GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql`.* TO `bar`@`%`
1383 GRANT `r1`@`%` TO `bar`@`%`
1384 [connection bar_master]
1385 DROP USER foo, bar;
1386 DROP ROLE r1;
1387 #
1388 # 12. SET ROLE removes the restrictions from the user. Now, global
1389 # grant/revoke ALL should work as intended.
1390 #
1391 CREATE USER foo, bar;
1392 CREATE ROLE r1;
1393 GRANT ALL ON *.* TO bar WITH GRANT OPTION;
1394 REVOKE INSERT ON mysql.* FROM bar;
1395 GRANT INSERT ON *.* TO r1;
1396 GRANT r1 TO bar;
1397 [connection bar_master]
1398 # User should not have restriction for now
1399 SET ROLE ALL;
1400 SHOW GRANTS;
1401 Grants for bar@%
1402 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 `bar`@`%` WITH GRANT OPTION
1403 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 `bar`@`%` WITH GRANT OPTION
1404 GRANT `r1`@`%` TO `bar`@`%`
1405 # Grantor should be able to grant ALL global level privilege to grantee
1406 GRANT ALL ON *.* TO foo;
1407 SHOW GRANTS FOR foo;
1408 Grants for foo@%
1409 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 `foo`@`%`
1410 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 `foo`@`%`
1411 # Grantor should be able to revoke ALL global level privilege from
1412 # grantee
1413 REVOKE ALL ON *.* FROM foo;
1414 SHOW GRANTS FOR foo;
1415 Grants for foo@%
1416 GRANT USAGE ON *.* TO `foo`@`%`
1417 # Should be able to grant ALL DB level privileges to self
1418 GRANT ALL ON *.* TO bar;
1419 SHOW GRANTS;
1420 Grants for bar@%
1421 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 `bar`@`%` WITH GRANT OPTION
1422 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 `bar`@`%` WITH GRANT OPTION
1423 GRANT `r1`@`%` TO `bar`@`%`
1424 [connection default]
1425 # Since, user granted himself all DB level privileges therefore it's
1426 # restrictions has gone permanently now.
1427 SHOW GRANTS FOR bar;
1428 Grants for bar@%
1429 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 `bar`@`%` WITH GRANT OPTION
1430 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 `bar`@`%` WITH GRANT OPTION
1431 GRANT `r1`@`%` TO `bar`@`%`
1432 include/sync_slave_sql_with_master.inc
1433 [connection slave]
1434 SHOW GRANTS FOR bar;
1435 Grants for bar@%
1436 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 `bar`@`%` WITH GRANT OPTION
1437 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 `bar`@`%` WITH GRANT OPTION
1438 GRANT `r1`@`%` TO `bar`@`%`
1439 [connection bar_master]
1440 DROP USER foo, bar;
1441 DROP ROLE r1;
1442 ---------------------Tests related to roles end here---------------------
1443 -------------------------------------------------------------------------
1444 Invalid restrictions and privilege combinations
1445 -------------------------------------------------------------------------
1446 CREATE USER foo, bar;
1447 #
1448 # 1. Add INSERT partial revoke on mysql DB for user bar
1449 #
1450 GRANT SELECT, INSERT ON *.* TO bar;
1451 REVOKE SELECT, INSERT ON mysql.* FROM bar;
1452 SHOW GRANTS FOR bar;
1453 Grants for bar@%
1454 GRANT SELECT, INSERT ON *.* TO `bar`@`%`
1455 REVOKE SELECT, INSERT ON `mysql`.* FROM `bar`@`%`
1456 INSERT INTO mysql.db(user,host, db, Insert_priv) VALUES('bar', '%', 'mysql', 'Y');
1457 FLUSH PRIVILEGES;
1458 SHOW GRANTS FOR bar;
1459 Grants for bar@%
1460 GRANT SELECT, INSERT ON *.* TO `bar`@`%`
1461 GRANT INSERT ON `mysql`.* TO `bar`@`%`
1462 REVOKE SELECT, INSERT ON `mysql`.* FROM `bar`@`%`
1463 GRANT INSERT ON *.* TO bar;
1464 ERROR HY000: 'INSERT' privilege for database 'mysql' exists both as partial revoke and mysql.db simultaneously. It could mean that the 'mysql' schema is corrupted.
1465 REVOKE INSERT ON *.* FROM bar;
1466 ERROR HY000: 'INSERT' privilege for database 'mysql' exists both as partial revoke and mysql.db simultaneously. It could mean that the 'mysql' schema is corrupted.
1467 GRANT INSERT ON mysql.* TO bar;
1468 ERROR HY000: 'INSERT' privilege for database 'mysql' exists both as partial revoke and mysql.db simultaneously. It could mean that the 'mysql' schema is corrupted.
1469 REVOKE INSERT ON mysql.* FROM bar;
1470 ERROR HY000: 'INSERT' privilege for database 'mysql' exists both as partial revoke and mysql.db simultaneously. It could mean that the 'mysql' schema is corrupted.
1471 # We should not even able to grant/revoke fresh privileges.
1472 GRANT UPDATE ON mysql.* TO bar;
1473 ERROR HY000: 'INSERT' privilege for database 'mysql' exists both as partial revoke and mysql.db simultaneously. It could mean that the 'mysql' schema is corrupted.
1474 REVOKE UPDATE ON mysql.* FROM bar;
1475 ERROR HY000: 'INSERT' privilege for database 'mysql' exists both as partial revoke and mysql.db simultaneously. It could mean that the 'mysql' schema is corrupted.
1476 GRANT UPDATE ON *.* TO bar;
1477 ERROR HY000: 'INSERT' privilege for database 'mysql' exists both as partial revoke and mysql.db simultaneously. It could mean that the 'mysql' schema is corrupted.
1478 REVOKE UPDATE ON *.* FROM bar;
1479 ERROR HY000: 'INSERT' privilege for database 'mysql' exists both as partial revoke and mysql.db simultaneously. It could mean that the 'mysql' schema is corrupted.
1480 SELECT CURRENT_USER();
1481 CURRENT_USER()
1482 bar@%
1483 SELECT COUNT(*) FROM pr_db.t1;
1484 COUNT(*)
1485 4
1486 DROP USER foo,bar;
1487 #
1488 # 2. Add privilege other than DB_ACLs in user attributes column.
1489 #
1490 CREATE USER u1;
1491 CREATE DATABASE db1;
1492 GRANT SELECT, INSERT ON *.* TO u1;
1493 REVOKE SELECT, INSERT ON db1.* FROM u1;
1494 UPDATE mysql.user SET user_attributes='{"Restrictions": [{"Database": "db1", "Privileges": ["SELECT", "INSERT", "SUPER", "DELETE"]}]}' WHERE user = 'u1';
1495 # Search for : ignored restrictions for privilege(s) 'SUPER' for database 'db1' as these are not valid database privileges
1496 Pattern "ignored restrictions for privilege(s) 'SUPER' for database 'db1' as these are not valid database privileges" not found
1497 # Search completed.
1498 # Search for : ignored restrictions for privilege(s) 'DELETE' for database 'db1' as corresponding global privilege(s) are not granted
1499 Pattern "ignored restrictions for privilege(s) 'DELETE' for database 'db1' as corresponding global privilege(s) are not granted" not found
1500 # Search completed.
1501 DROP DATABASE db1;
1502 DROP USER u1;
1503 -Tests related to invalid restrictions & privilege combinations end here-
1504
1505 -------------------------------------------------------------------------
1506 Partial revokes do not treat wildcard grants specially.
1507 -------------------------------------------------------------------------
1508 CREATE USER foo;
1509 CREATE DATABASE `db_1`;
1510 CREATE DATABASE `db%1`;
1511 GRANT SELECT ON *.* TO foo;
1512 # Create restriction - We dont use '\' to escape '_' or '%'
1513 REVOKE SELECT ON `db_1`.* FROM foo;
1514 REVOKE SELECT ON `db%1`.* FROM foo;
1515 # Should show 2 restrictions
1516 SHOW GRANTS FOR foo;
1517 Grants for foo@%
1518 GRANT SELECT ON *.* TO `foo`@`%`
1519 REVOKE SELECT ON `db%1`.* FROM `foo`@`%`
1520 REVOKE SELECT ON `db_1`.* FROM `foo`@`%`
1521 # Should not remove restrictions on db_1 or db%1,
1522 # instead both grants should be added on db(s) as it is.
1523 GRANT SELECT ON `db\_1`.* TO foo;
1524 GRANT SELECT ON `db\%1`.* TO foo;
1525 SHOW GRANTS FOR foo;
1526 Grants for foo@%
1527 GRANT SELECT ON *.* TO `foo`@`%`
1528 GRANT SELECT ON `db\%1`.* TO `foo`@`%`
1529 GRANT SELECT ON `db\_1`.* TO `foo`@`%`
1530 REVOKE SELECT ON `db%1`.* FROM `foo`@`%`
1531 REVOKE SELECT ON `db_1`.* FROM `foo`@`%`
1532 # Should remove two grants added above
1533 REVOKE SELECT ON `db\_1`.* FROM foo;
1534 REVOKE SELECT ON `db\%1`.* FROM foo;
1535 # Should show 2 restrictions
1536 SHOW GRANTS FOR foo;
1537 Grants for foo@%
1538 GRANT SELECT ON *.* TO `foo`@`%`
1539 REVOKE SELECT ON `db%1`.* FROM `foo`@`%`
1540 REVOKE SELECT ON `db_1`.* FROM `foo`@`%`
1541 REVOKE SELECT ON *.* FROM foo;
1542 DROP USER foo;
1543 DROP DATABASE `db_1`;
1544 DROP DATABASE `db%1`;
1545 --------------- Test related to wildcard grants end here. ---------------
1546
1547 -------------------------------------------------------------------------
1548 Tests related to Bug 37570206 - REVOKE EXECUTE from a user throws ERROR
1549 3879 (HY000): Access denied *
1550 -------------------------------------------------------------------------
1551 CREATE USER bug37570206_user;
1552 CREATE USER bug37570206_admin;
1553 CREATE DATABASE bug37570206_db;
1554 GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON *.* TO bug37570206_admin WITH GRANT OPTION;
1555 REVOKE SELECT, EXECUTE ON bug37570206_db.* FROM bug37570206_admin;
1556 GRANT SELECT, EXECUTE ON *.* TO bug37570206_user;
1557 SHOW GRANTS FOR bug37570206_user;
1558 Grants for bug37570206_user@%
1559 GRANT SELECT, EXECUTE ON *.* TO `bug37570206_user`@`%`
1560 REVOKE SELECT, EXECUTE ON `bug37570206_db`.* FROM `bug37570206_user`@`%`
1561 # Must succeed
1562 REVOKE SELECT, EXECUTE ON *.* FROM bug37570206_user;
1563 SHOW GRANTS FOR bug37570206_user;
1564 Grants for bug37570206_user@%
1565 GRANT USAGE ON *.* TO `bug37570206_user`@`%`
1566 REVOKE ALL ON *.* FROM bug37570206_admin, bug37570206_user;
1567 GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON *.* TO bug37570206_admin WITH GRANT OPTION;
1568 REVOKE SELECT ON bug37570206_db.* FROM bug37570206_admin;
1569 GRANT SELECT, EXECUTE ON *.* TO bug37570206_user;
1570 REVOKE SELECT, EXECUTE ON bug37570206_db.* FROM bug37570206_user;
1571 # Must succeed
1572 REVOKE SELECT, EXECUTE ON *.* FROM bug37570206_user;
1573 GRANT SELECT, EXECUTE ON *.* TO bug37570206_user;
1574 REVOKE SELECT, EXECUTE ON bug37570206_db.* FROM bug37570206_user;
1575 REVOKE SELECT, EXECUTE ON mysql.* FROM bug37570206_user;
1576 # Must succeed
1577 REVOKE SELECT, EXECUTE ON *.* FROM bug37570206_user;
1578 GRANT SELECT ON bug37570206_db.* TO bug37570206_admin;
1579 GRANT SELECT, EXECUTE ON *.* TO bug37570206_user;
1580 REVOKE SELECT, EXECUTE ON bug37570206_db.* FROM bug37570206_user;
1581 # Must succeed
1582 REVOKE SELECT, EXECUTE ON *.* FROM bug37570206_user;
1583 REVOKE ALL ON *.* FROM bug37570206_admin, bug37570206_user;
1584 GRANT SELECT, INSERT, UPDATE ON *.* TO bug37570206_admin WITH GRANT OPTION;
1585 REVOKE SELECT, INSERT ON bug37570206_db.* FROM bug37570206_admin;
1586 GRANT SELECT, EXECUTE, DELETE ON *.* TO bug37570206_user;
1587 REVOKE SELECT, EXECUTE ON bug37570206_db.* FROM bug37570206_user;
1588 # Must succeed
1589 REVOKE SELECT ON *.* FROM bug37570206_user;
1590 REVOKE ALL ON *.* FROM bug37570206_admin, bug37570206_user;
1591 GRANT ALL ON *.* TO bug37570206_admin WITH GRANT OPTION;
1592 REVOKE INSERT ON bug37570206_db.* FROM bug37570206_admin;
1593 GRANT SELECT, INSERT ON *.* TO bug37570206_user;
1594 REVOKE INSERT ON bug37570206_db.* FROM bug37570206_user;
1595 # Must succeed
1596 REVOKE ALL ON *.* FROM bug37570206_user;
1597 REVOKE ALL ON *.* FROM bug37570206_admin, bug37570206_user;
1598 GRANT SELECT, INSERT, UPDATE ON *.* TO bug37570206_admin WITH GRANT OPTION;
1599 REVOKE SELECT, INSERT ON bug37570206_db.* FROM bug37570206_admin;
1600 GRANT SELECT, EXECUTE, DELETE ON *.* TO bug37570206_user;
1601 # Must fail
1602 REVOKE SELECT ON *.* FROM bug37570206_user;
1603 ERROR HY000: Access denied for AuthId `bug37570206_admin`@`%` to database 'bug37570206_db'.
1604 REVOKE DELETE ON bug37570206_db.* FROM bug37570206_user;
1605 # Must fail
1606 REVOKE SELECT ON *.* FROM bug37570206_user;
1607 ERROR HY000: Access denied for AuthId `bug37570206_admin`@`%` to database 'bug37570206_db'.
1608 REVOKE ALL ON *.* FROM bug37570206_admin, bug37570206_user;
1609 GRANT ALL ON *.* TO bug37570206_admin WITH GRANT OPTION;
1610 REVOKE SELECT, INSERT ON bug37570206_db.* FROM bug37570206_admin;
1611 GRANT SELECT, UPDATE, DELETE, INSERT ON *.* TO bug37570206_user;
1612 # Must fail
1613 REVOKE ALL ON *.* FROM bug37570206_user;
1614 ERROR HY000: Access denied for AuthId `bug37570206_admin`@`%` to database 'bug37570206_db'.
1615 REVOKE INSERT ON bug37570206_db.* FROM bug37570206_user;
1616 # Must fail
1617 REVOKE ALL ON *.* FROM bug37570206_user;
1618 ERROR HY000: Access denied for AuthId `bug37570206_admin`@`%` to database 'bug37570206_db'.
1619 DROP USER bug37570206_admin, bug37570206_user;
1620 DROP DATABASE bug37570206_db;
1621 -------------------------------------------------------------------------
1622 Tests related to Bug 37570206 - REVOKE EXECUTE from a user throws ERROR
1623 3879 (HY000): Access denied * end here
1624 -------------------------------------------------------------------------
1625 DROP DATABASE pr_db;
1626 DROP DATABASE normal_db;
1627 call mtr.add_suppression(".*Nothing to revoke. Global privilege.*");
1628 include/rpl_end.inc