Debian Codegraph @

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

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