Debian Codegraph @

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

raw
1 # -----------------------------------------------------------------------
2 # Start : DML Tests for Partial Revokes
3 # -----------------------------------------------------------------------
4 # -----------------------------------------------------------------------
5 # Setup
6 # Create database and objects
7 CREATE DATABASE partial_revokes_db;
8 CREATE DATABASE partial_revokes_db_2;
9 # Create users
10 CREATE USER foo@localhost IDENTIFIED BY 'abcd';
11 CREATE USER bar@localhost IDENTIFIED BY 'efgh';
12 # -----------------------------------------------------------------------
13 # Start: Test cases for Base tables.
14 CREATE TABLE partial_revokes_db.t1(c1 int, c2 int);
15 INSERT INTO partial_revokes_db.t1 values (1,2), (3,4);
16 CREATE TABLE partial_revokes_db_2.t1(a1 int, a2 int);
17 INSERT INTO partial_revokes_db_2.t1 values (1,2), (5,6), (9,10), (11,12);
18 CREATE TABLE partial_revokes_db.t2 (a date, b date, c date not null, d date);
19 # -----------------------------------------------------------------------
20 # -----------------------------------------------------------------------
21 # Case: *.* + Partial Revoke on INSERT + INSERT
22 GRANT INSERT, SELECT ON *.* TO foo@localhost;
23 REVOKE INSERT ON partial_revokes_db.* FROM foo@localhost;
24 INSERT INTO partial_revokes_db.t1 VALUES (5,6);
25 ERROR 42000: INSERT command denied to user 'foo'@'localhost' for table 't1'
26 SELECT COUNT(*) FROM partial_revokes_db.t1;
27 COUNT(*)
28 2
29 REVOKE INSERT, SELECT ON *.* FROM foo@localhost;
30 # -----------------------------------------------------------------------
31 # Case: *.* + Partial Revoke on SELECT + INSERT ... SELECT
32 GRANT INSERT, SELECT ON *.* TO foo@localhost;
33 REVOKE SELECT ON partial_revokes_db.* FROM foo@localhost;
34 INSERT INTO partial_revokes_db_2.t1 SELECT * FROM partial_revokes_db.t1;
35 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
36 INSERT INTO partial_revokes_db_2.t1 VALUES (1,2), (3,4);
37 INSERT INTO partial_revokes_db.t1 VALUES (5,6), (7,8);
38 REVOKE INSERT, SELECT ON *.* FROM foo@localhost;
39 # -----------------------------------------------------------------------
40 # Case: *.* + Partial Revoke on UPDATE + UPDATE
41 GRANT UPDATE, SELECT ON *.* TO foo@localhost;
42 REVOKE UPDATE ON partial_revokes_db.* FROM foo@localhost;
43 UPDATE partial_revokes_db.t1 SET c1 = c1 + 1;
44 ERROR 42000: UPDATE command denied to user 'foo'@'localhost' for table 't1'
45 UPDATE partial_revokes_db.t1 SET c2 = c2 + 1 WHERE c1 = 2;
46 ERROR 42000: UPDATE command denied to user 'foo'@'localhost' for table 't1'
47 REVOKE UPDATE, SELECT ON *.* FROM foo@localhost;
48 # -----------------------------------------------------------------------
49 # Case: *.* + Partial Revoke on SELECT + UPDATE ... SELECT
50 GRANT UPDATE, SELECT ON *.* TO foo@localhost;
51 REVOKE SELECT ON partial_revokes_db.* FROM foo@localhost;
52 UPDATE partial_revokes_db.t1 SET c1 = 1;
53 UPDATE partial_revokes_db.t1 SET c1 = c1 + 1;
54 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'c1' in table 't1'
55 UPDATE partial_revokes_db.t1 SET c2 = c2 + 1 WHERE c1 = 2;
56 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'c1' in table 't1'
57 REVOKE UPDATE, SELECT ON *.* FROM foo@localhost;
58 # -----------------------------------------------------------------------
59 # Case: *.* + Partial Revoke on DELETE + DELETE
60 GRANT DELETE, SELECT ON *.* TO foo@localhost;
61 REVOKE DELETE ON partial_revokes_db.* FROM foo@localhost;
62 DELETE FROM partial_revokes_db.t1 WHERE c1 = 1;
63 ERROR 42000: DELETE command denied to user 'foo'@'localhost' for table 't1'
64 DELETE FROM partial_revokes_db.t1;
65 ERROR 42000: DELETE command denied to user 'foo'@'localhost' for table 't1'
66 REVOKE DELETE, SELECT ON *.* FROM foo@localhost;
67 # -----------------------------------------------------------------------
68 # Case: *.* + Partial Revoke on SELECT + DELETE ... SELECT
69 GRANT DELETE, SELECT ON *.* TO foo@localhost;
70 REVOKE SELECT ON partial_revokes_db.* FROM foo@localhost;
71 DELETE FROM partial_revokes_db.t1 WHERE c1 = 1;
72 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'c1' in table 't1'
73 DELETE FROM partial_revokes_db.t1;
74 REVOKE DELETE, SELECT ON *.* FROM foo@localhost;
75 # -----------------------------------------------------------------------
76 # Case: *.* + Partial Revoke on DELETE + REPLACE
77 GRANT SELECT, INSERT, DELETE ON *.* TO foo@localhost;
78 REVOKE DELETE ON partial_revokes_db.* FROM foo@localhost;
79 REPLACE INTO partial_revokes_db.t1 VALUES (1,3);
80 ERROR 42000: DELETE command denied to user 'foo'@'localhost' for table 't1'
81 REPLACE INTO partial_revokes_db.t1 VALUES (5,6);
82 ERROR 42000: DELETE command denied to user 'foo'@'localhost' for table 't1'
83 REPLACE INTO partial_revokes_db_2.t1 VALUES (1,3);
84 REPLACE INTO partial_revokes_db_2.t1 SELECT * FROM partial_revokes_db.t1 ORDER BY c1;
85 REVOKE SELECT, INSERT, DELETE ON *.* FROM foo@localhost;
86 # -----------------------------------------------------------------------
87 # Case: *.* + Partial Revoke on INSERT + REPLACE
88 GRANT SELECT, INSERT, DELETE ON *.* TO foo@localhost;
89 REVOKE INSERT ON partial_revokes_db.* FROM foo@localhost;
90 REPLACE INTO partial_revokes_db.t1 VALUES (1,3);
91 ERROR 42000: INSERT command denied to user 'foo'@'localhost' for table 't1'
92 REPLACE INTO partial_revokes_db.t1 VALUES (5,6);
93 ERROR 42000: INSERT command denied to user 'foo'@'localhost' for table 't1'
94 REPLACE INTO partial_revokes_db_2.t1 VALUES (1,3);
95 REPLACE INTO partial_revokes_db_2.t1 SELECT * FROM partial_revokes_db.t1 ORDER BY c1;
96 REVOKE SELECT, INSERT, DELETE ON *.* FROM foo@localhost;
97 # -----------------------------------------------------------------------
98 # Case: *.* + Partial Revoke on INSERT + LOAD
99 GRANT INSERT, SELECT, FILE ON *.* TO foo@localhost;
100 REVOKE INSERT ON partial_revokes_db.* FROM foo@localhost;
101 LOAD DATA INFILE '../../std_data/loaddata1.dat' IGNORE INTO TABLE partial_revokes_db.t2 FIELDS TERMINATED BY ',';
102 ERROR 42000: INSERT command denied to user 'foo'@'localhost' for table 't2'
103 SELECT * FROM partial_revokes_db.t2;
104 a b c d
105 REVOKE INSERT, SELECT ON *.* FROM foo@localhost;
106 # -----------------------------------------------------------------------
107 # Case: *.* + Partial Revoke on INSERT + CTE
108 GRANT SELECT ON *.* TO foo@localhost;
109 REVOKE SELECT ON partial_revokes_db.* FROM foo@localhost;
110 WITH cte1 AS (SELECT c1, c2 FROM partial_revokes_db.t1),
111 cte2 AS (SELECT a1, a2 FROM partial_revokes_db_2.t1)
112 SELECT c2, a2 FROM cte1 JOIN cte2 WHERE cte1.c1 = cte2.a1;
113 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 't1'
114 REVOKE SELECT ON *.* FROM foo@localhost;
115 # -----------------------------------------------------------------------
116 # End: Test cases for Base tables.
117 DROP TABLE partial_revokes_db.t1;
118 DROP TABLE partial_revokes_db.t2;
119 DROP TABLE partial_revokes_db_2.t1;
120 # -----------------------------------------------------------------------
121 # -----------------------------------------------------------------------
122 # Start: Test cases for views.
123 CREATE TABLE partial_revokes_db.t1(c1 int, c2 int);
124 INSERT INTO partial_revokes_db.t1 values (1,2), (3,4), (5,6), (7,8);
125 CREATE TABLE partial_revokes_db_2.t2(a1 int, a2 int);
126 INSERT INTO partial_revokes_db_2.t2 values (1,2), (5,6), (9,10), (11,12);
127 CREATE SQL SECURITY DEFINER VIEW partial_revokes_db.v1t1d AS SELECT * FROM partial_revokes_db.t1;
128 CREATE SQL SECURITY INVOKER VIEW partial_revokes_db.v1t1i AS SELECT * FROM partial_revokes_db.t1;
129 CREATE SQL SECURITY DEFINER VIEW partial_revokes_db.v1t2d AS SELECT * FROM partial_revokes_db_2.t2;
130 CREATE SQL SECURITY INVOKER VIEW partial_revokes_db.v1t2i AS SELECT * FROM partial_revokes_db_2.t2;
131 CREATE SQL SECURITY DEFINER VIEW partial_revokes_db_2.v1t1d AS SELECT * FROM partial_revokes_db.t1;
132 CREATE SQL SECURITY INVOKER VIEW partial_revokes_db_2.v1t1i AS SELECT * FROM partial_revokes_db.t1;
133 CREATE SQL SECURITY DEFINER VIEW partial_revokes_db_2.v1t2d AS SELECT * FROM partial_revokes_db_2.t2;
134 CREATE SQL SECURITY INVOKER VIEW partial_revokes_db_2.v1t2i AS SELECT * FROM partial_revokes_db_2.t2;
135 # -----------------------------------------------------------------------
136 # -----------------------------------------------------------------------
137 # Case: *.* + Partial Revoke on SELECT + SELECT, SHOW VIEW
138 GRANT SELECT, SHOW VIEW ON *.* TO foo@localhost;
139 REVOKE SELECT ON partial_revokes_db.* FROM foo@localhost;
140 SELECT * FROM partial_revokes_db.v1t1d;
141 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'v1t1d'
142 SELECT * FROM partial_revokes_db.v1t1i;
143 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'v1t1i'
144 SELECT * FROM partial_revokes_db.v1t2d;
145 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'v1t2d'
146 SELECT * FROM partial_revokes_db.v1t2i;
147 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'v1t2i'
148 SELECT * FROM partial_revokes_db_2.v1t1d;
149 c1 c2
150 1 2
151 3 4
152 5 6
153 7 8
154 SELECT * FROM partial_revokes_db_2.v1t1i;
155 ERROR HY000: View 'partial_revokes_db_2.v1t1i' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
156 SELECT * FROM partial_revokes_db_2.v1t2d;
157 a1 a2
158 1 2
159 5 6
160 9 10
161 11 12
162 SELECT * FROM partial_revokes_db_2.v1t2i;
163 a1 a2
164 1 2
165 5 6
166 9 10
167 11 12
168 SHOW CREATE VIEW partial_revokes_db.v1t1d;
169 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'v1t1d'
170 SHOW CREATE VIEW partial_revokes_db.v1t1i;
171 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'v1t1i'
172 SHOW CREATE VIEW partial_revokes_db.v1t2d;
173 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'v1t2d'
174 SHOW CREATE VIEW partial_revokes_db.v1t2i;
175 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'v1t2i'
176 SHOW CREATE VIEW partial_revokes_db_2.v1t1d;
177 View Create View character_set_client collation_connection
178 v1t1d CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `partial_revokes_db_2`.`v1t1d` AS select `partial_revokes_db`.`t1`.`c1` AS `c1`,`partial_revokes_db`.`t1`.`c2` AS `c2` from `partial_revokes_db`.`t1` utf8mb4 utf8mb4_0900_ai_ci
179 SHOW CREATE VIEW partial_revokes_db_2.v1t1i;
180 View Create View character_set_client collation_connection
181 v1t1i CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `partial_revokes_db_2`.`v1t1i` AS select `partial_revokes_db`.`t1`.`c1` AS `c1`,`partial_revokes_db`.`t1`.`c2` AS `c2` from `partial_revokes_db`.`t1` utf8mb4 utf8mb4_0900_ai_ci
182 SHOW CREATE VIEW partial_revokes_db_2.v1t2d;
183 View Create View character_set_client collation_connection
184 v1t2d CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `partial_revokes_db_2`.`v1t2d` AS select `partial_revokes_db_2`.`t2`.`a1` AS `a1`,`partial_revokes_db_2`.`t2`.`a2` AS `a2` from `partial_revokes_db_2`.`t2` utf8mb4 utf8mb4_0900_ai_ci
185 SHOW CREATE VIEW partial_revokes_db_2.v1t2i;
186 View Create View character_set_client collation_connection
187 v1t2i CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `partial_revokes_db_2`.`v1t2i` AS select `partial_revokes_db_2`.`t2`.`a1` AS `a1`,`partial_revokes_db_2`.`t2`.`a2` AS `a2` from `partial_revokes_db_2`.`t2` utf8mb4 utf8mb4_0900_ai_ci
188 REVOKE SELECT ON *.* FROM foo@localhost;
189 # -----------------------------------------------------------------------
190 # Case: *.* + Partial Revoke on INSERT + INSERT
191 GRANT INSERT ON *.* TO foo@localhost;
192 REVOKE INSERT ON partial_revokes_db.* FROM foo@localhost;
193 INSERT INTO partial_revokes_db.v1t1d VALUES (1,2);
194 ERROR 42000: INSERT command denied to user 'foo'@'localhost' for table 'v1t1d'
195 INSERT INTO partial_revokes_db.v1t1i VALUES (1,2);
196 ERROR 42000: INSERT command denied to user 'foo'@'localhost' for table 'v1t1i'
197 INSERT INTO partial_revokes_db.v1t2i VALUES (1,2);
198 ERROR 42000: INSERT command denied to user 'foo'@'localhost' for table 'v1t2i'
199 INSERT INTO partial_revokes_db.v1t2d VALUES (1,2);
200 ERROR 42000: INSERT command denied to user 'foo'@'localhost' for table 'v1t2d'
201 INSERT INTO partial_revokes_db_2.v1t1d VALUES (1,2);
202 INSERT INTO partial_revokes_db_2.v1t1i VALUES (1,2);
203 ERROR HY000: View 'partial_revokes_db_2.v1t1i' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
204 INSERT INTO partial_revokes_db_2.v1t2d VALUES(1,2);
205 INSERT INTO partial_revokes_db_2.v1t2i VALUES(1,2);
206 REVOKE INSERT ON *.* FROM foo@localhost;
207 DELETE FROM partial_revokes_db.t1;
208 DELETE FROM partial_revokes_db_2.t2;
209 # -----------------------------------------------------------------------
210 # Case: *.* + Partial Revoke on INSERT + INSERT ... SELECT
211 INSERT INTO partial_revokes_db.t1 values (1,2), (3,4), (5,6), (7,8);
212 GRANT INSERT, SELECT ON *.* TO foo@localhost;
213 REVOKE SELECT ON partial_revokes_db.* FROM foo@localhost;
214 INSERT INTO partial_revokes_db_2.v1t2d SELECT * FROM partial_revokes_db.v1t1d;
215 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'v1t1d'
216 INSERT INTO partial_revokes_db_2.v1t2d SELECT * FROM partial_revokes_db.v1t1i;
217 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'v1t1i'
218 INSERT INTO partial_revokes_db_2.v1t2d SELECT * FROM partial_revokes_db.v1t2d;
219 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'v1t2d'
220 INSERT INTO partial_revokes_db_2.v1t2d SELECT * FROM partial_revokes_db.v1t2i;
221 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'v1t2i'
222 INSERT INTO partial_revokes_db_2.v1t2i SELECT * FROM partial_revokes_db.v1t1i;
223 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'v1t1i'
224 INSERT INTO partial_revokes_db_2.v1t2i SELECT * FROM partial_revokes_db_2.v1t1d;
225 INSERT INTO partial_revokes_db_2.v1t2i SELECT * FROM partial_revokes_db_2.v1t2d;
226 INSERT INTO partial_revokes_db_2.v1t2i SELECT * FROM partial_revokes_db_2.v1t2i;
227 DELETE FROM partial_revokes_db.t1;
228 DELETE FROM partial_revokes_db_2.t2;
229 REVOKE INSERT, SELECT ON *.* FROM foo@localhost;
230 # -----------------------------------------------------------------------
231 # Case: *.* + Partial Revoke on DELETE + DELETE
232 INSERT INTO partial_revokes_db.t1 values (1,2), (3,4), (5,6), (7,8);
233 INSERT INTO partial_revokes_db_2.t2 values (1,2), (3,4), (5,6), (7,8);
234 GRANT DELETE ON *.* TO foo@localhost;
235 REVOKE DELETE ON partial_revokes_db.* FROM foo@localhost;
236 DELETE FROM partial_revokes_db.v1t1d;
237 ERROR 42000: DELETE command denied to user 'foo'@'localhost' for table 'v1t1d'
238 DELETE FROM partial_revokes_db.v1t1i;
239 ERROR 42000: DELETE command denied to user 'foo'@'localhost' for table 'v1t1i'
240 DELETE FROM partial_revokes_db.v1t2d;
241 ERROR 42000: DELETE command denied to user 'foo'@'localhost' for table 'v1t2d'
242 DELETE FROM partial_revokes_db.v1t2i;
243 ERROR 42000: DELETE command denied to user 'foo'@'localhost' for table 'v1t2i'
244 DELETE FROM partial_revokes_db_2.v1t1d;
245 DELETE FROM partial_revokes_db_2.v1t1i;
246 ERROR HY000: View 'partial_revokes_db_2.v1t1i' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
247 DELETE FROM partial_revokes_db_2.v1t2d;
248 DELETE FROM partial_revokes_db_2.v1t2i;
249 DELETE FROM partial_revokes_db.t1;
250 DELETE FROM partial_revokes_db_2.t2;
251 REVOKE DELETE ON *.* FROM foo@localhost;
252 # -----------------------------------------------------------------------
253 # Case: *.* + Partial Revoke on SELECT + DELETE ... SELECT
254 INSERT INTO partial_revokes_db.t1 values (1,2), (3,4), (5,6), (7,8);
255 INSERT INTO partial_revokes_db_2.t2 values (1,2), (3,4), (5,6), (7,8);
256 GRANT DELETE, SELECT ON *.* TO foo@localhost;
257 REVOKE SELECT ON partial_revokes_db.* FROM foo@localhost;
258 DELETE FROM partial_revokes_db.v1t1d WHERE c1 = 1;
259 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'c1' in table 'v1t1d'
260 DELETE FROM partial_revokes_db.v1t1i WHERE c1 = 1;
261 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'c1' in table 'v1t1i'
262 DELETE FROM partial_revokes_db.v1t2d WHERE a1 = 1;
263 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'a1' in table 'v1t2d'
264 DELETE FROM partial_revokes_db.v1t2i WHERE a1 = 1;
265 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for column 'a1' in table 'v1t2i'
266 DELETE FROM partial_revokes_db_2.v1t1d;
267 DELETE FROM partial_revokes_db_2.v1t1i WHERE c1 = 1;
268 ERROR HY000: View 'partial_revokes_db_2.v1t1i' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
269 DELETE FROM partial_revokes_db_2.v1t2d WHERE a1 = 1;
270 DELETE FROM partial_revokes_db_2.v1t2i WHERE a1 = 1;
271 DELETE FROM partial_revokes_db.t1;
272 DELETE FROM partial_revokes_db_2.t2;
273 REVOKE ALL PRIVILEGES, GRANT OPTION FROM foo@localhost;
274 # -----------------------------------------------------------------------
275 # End: Test cases for views.
276 DROP TABLE IF EXISTS partial_revokes_db.t1;
277 DROP TABLE IF EXISTS partial_revokes_db_2.t1;
278 Warnings:
279 Note 1051 Unknown table 'partial_revokes_db_2.t1'
280 DROP VIEW IF EXISTS partial_revokes_db.v1t1d;
281 DROP VIEW IF EXISTS partial_revokes_db.v1t1i;
282 DROP VIEW IF EXISTS partial_revokes_db.v1t2d;
283 DROP VIEW IF EXISTS partial_revokes_db.v1t2i;
284 DROP VIEW IF EXISTS partial_revokes_db_2.v1t1d;
285 DROP VIEW IF EXISTS partial_revokes_db_2.v1t1i;
286 DROP VIEW IF EXISTS partial_revokes_db_2.v1t2d;
287 DROP VIEW IF EXISTS partial_revokes_db_2.v1t2i;
288 # -----------------------------------------------------------------------
289 # -----------------------------------------------------------------------
290 # Case: *.* + Partial Revoke + SHOW DATABASE
291 CREATE USER baz@localhost IDENTIFIED BY 'abcd';
292 CREATE ROLE r3;
293 GRANT INSERT,SELECT ON *.* TO baz@localhost;
294 GRANT INSERT,SELECT ON *.* TO r3;
295 REVOKE INSERT,SELECT ON partial_revokes_db.* FROM baz@localhost;
296 REVOKE INSERT,SELECT ON partial_revokes_db_2.* FROM r3;
297 GRANT r3 TO baz@localhost;
298 SHOW GRANTS;
299 Grants for baz@localhost
300 GRANT SELECT, INSERT ON *.* TO `baz`@`localhost`
301 REVOKE SELECT, INSERT ON `partial_revokes_db`.* FROM `baz`@`localhost`
302 GRANT `r3`@`%` TO `baz`@`localhost`
303 SHOW DATABASES;
304 Database
305 information_schema
306 mtr
307 mysql
308 partial_revokes_db_2
309 performance_schema
310 sys
311 test
312 SET ROLE r3;
313 SHOW DATABASES;
314 Database
315 information_schema
316 mtr
317 mysql
318 partial_revokes_db
319 partial_revokes_db_2
320 performance_schema
321 sys
322 test
323 GRANT INSERT ON `partial_revokes_db`.* TO baz@localhost;
324 SHOW GRANTS;
325 Grants for baz@localhost
326 GRANT SELECT, INSERT ON *.* TO `baz`@`localhost`
327 REVOKE SELECT ON `partial_revokes_db`.* FROM `baz`@`localhost`
328 GRANT `r3`@`%` TO `baz`@`localhost`
329 SHOW DATABASES;
330 Database
331 information_schema
332 mtr
333 mysql
334 partial_revokes_db
335 partial_revokes_db_2
336 performance_schema
337 sys
338 test
339 SET ROLE r3;
340 SHOW DATABASES;
341 Database
342 information_schema
343 mtr
344 mysql
345 partial_revokes_db
346 partial_revokes_db_2
347 performance_schema
348 sys
349 test
350 GRANT SELECT ON `partial_revokes_db`.* TO baz@localhost;
351 SHOW GRANTS;
352 Grants for baz@localhost
353 GRANT SELECT, INSERT ON *.* TO `baz`@`localhost`
354 GRANT `r3`@`%` TO `baz`@`localhost`
355 SHOW DATABASES;
356 Database
357 information_schema
358 mtr
359 mysql
360 partial_revokes_db
361 partial_revokes_db_2
362 performance_schema
363 sys
364 test
365 SET ROLE r3;
366 SHOW DATABASES;
367 Database
368 information_schema
369 mtr
370 mysql
371 partial_revokes_db
372 partial_revokes_db_2
373 performance_schema
374 sys
375 test
376 # -----------------------------------------------------------------------
377 DROP USER baz@localhost;
378 DROP ROLE r3;
379 # End: Test cases for Show database.
380 # -----------------------------------------------------------------------
381 # -----------------------------------------------------------------------
382 # Start: Test cases for stored routines.
383 # -----------------------------------------------------------------------
384 CREATE TABLE partial_revokes_db.tp1(c1 int, c2 int);
385 INSERT INTO partial_revokes_db.tp1 VALUES (1,2), (3,4);
386 CREATE TABLE partial_revokes_db_2.tp2(c1 int, c2 int);
387 INSERT INTO partial_revokes_db_2.tp2 VALUES (5,6), (7,8);
388 CREATE PROCEDURE partial_revokes_db.p1tp1d_count() SELECT COUNT(*) FROM partial_revokes_db.tp1;
389 CREATE PROCEDURE partial_revokes_db.p1tp1i_count() SELECT COUNT(*) FROM partial_revokes_db.tp1;
390 ALTER PROCEDURE partial_revokes_db.p1tp1i_count SQL SECURITY INVOKER;
391 CREATE PROCEDURE partial_revokes_db.p1tp2d_count() SELECT COUNT(*) FROM partial_revokes_db_2.tp2;
392 CREATE PROCEDURE partial_revokes_db.p1tp2i_count() SELECT COUNT(*) FROM partial_revokes_db_2.tp2;
393 ALTER PROCEDURE partial_revokes_db.p1tp2i_count SQL SECURITY INVOKER;
394 CREATE PROCEDURE partial_revokes_db_2.p2tp1d_count() SELECT COUNT(*) FROM partial_revokes_db.tp1;
395 CREATE PROCEDURE partial_revokes_db_2.p2tp1i_count() SELECT COUNT(*) FROM partial_revokes_db.tp1;
396 ALTER PROCEDURE partial_revokes_db_2.p2tp1i_count SQL SECURITY INVOKER;
397 CREATE PROCEDURE partial_revokes_db_2.p2tp2d_count() SELECT COUNT(*) FROM partial_revokes_db_2.tp2;
398 CREATE PROCEDURE partial_revokes_db_2.p2tp2i_count() SELECT COUNT(*) FROM partial_revokes_db_2.tp2;
399 ALTER PROCEDURE partial_revokes_db_2.p2tp2i_count SQL SECURITY INVOKER;
400 # -----------------------------------------------------------------------
401 # -----------------------------------------------------------------------
402 # Case: *.* + Partial Revoke on EXECUTE
403 GRANT EXECUTE, SELECT ON *.* TO foo@localhost;
404 REVOKE EXECUTE, SELECT ON partial_revokes_db.* FROM foo@localhost;
405 SHOW GRANTS FOR foo@localhost;
406 Grants for foo@localhost
407 GRANT SELECT, EXECUTE ON *.* TO `foo`@`localhost`
408 REVOKE SELECT, EXECUTE ON `partial_revokes_db`.* FROM `foo`@`localhost`
409 CALL partial_revokes_db.p1tp1d_count();
410 ERROR 42000: execute command denied to user 'foo'@'localhost' for routine 'partial_revokes_db.p1tp1d_count'
411 CALL partial_revokes_db.p1tp1i_count();
412 ERROR 42000: execute command denied to user 'foo'@'localhost' for routine 'partial_revokes_db.p1tp1i_count'
413 CALL partial_revokes_db.p1tp2d_count();
414 ERROR 42000: execute command denied to user 'foo'@'localhost' for routine 'partial_revokes_db.p1tp2d_count'
415 CALL partial_revokes_db.p1tp2i_count();
416 ERROR 42000: execute command denied to user 'foo'@'localhost' for routine 'partial_revokes_db.p1tp2i_count'
417 CALL partial_revokes_db_2.p2tp1d_count();
418 COUNT(*)
419 2
420 CALL partial_revokes_db_2.p2tp1i_count();
421 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'tp1'
422 CALL partial_revokes_db_2.p2tp2d_count();
423 COUNT(*)
424 2
425 CALL partial_revokes_db_2.p2tp2i_count();
426 COUNT(*)
427 2
428 REVOKE EXECUTE, SELECT ON *.* FROM foo@localhost;
429 # -----------------------------------------------------------------------
430 DROP PROCEDURE partial_revokes_db.p1tp1d_count;
431 DROP PROCEDURE partial_revokes_db.p1tp1i_count;
432 DROP PROCEDURE partial_revokes_db.p1tp2d_count;
433 DROP PROCEDURE partial_revokes_db.p1tp2i_count;
434 DROP PROCEDURE partial_revokes_db_2.p2tp1d_count;
435 DROP PROCEDURE partial_revokes_db_2.p2tp1i_count;
436 DROP PROCEDURE partial_revokes_db_2.p2tp2d_count;
437 DROP PROCEDURE partial_revokes_db_2.p2tp2i_count;
438 DROP TABLE partial_revokes_db.tp1;
439 DROP TABLE partial_revokes_db_2.tp2;
440 # End: Test cases for stored routines.
441 # -----------------------------------------------------------------------
442 # -----------------------------------------------------------------------
443 # Start: Test case for table and column grants
444 # -----------------------------------------------------------------------
445 REVOKE ALL PRIVILEGES, GRANT OPTION FROM foo@localhost;
446 SHOW GRANTS FOR foo@localhost;
447 Grants for foo@localhost
448 GRANT USAGE ON *.* TO `foo`@`localhost`
449 CREATE TABLE partial_revokes_db.t1(c1 int);
450 INSERT INTO partial_revokes_db.t1 VALUES (1), (2), (3);
451 CREATE SQL SECURITY INVOKER VIEW partial_revokes_db_2.v1t1i AS SELECT * FROM partial_revokes_db.t1;
452 CREATE PROCEDURE partial_revokes_db_2.p1t1i_c1() SELECT COUNT(*) FROM partial_revokes_db.t1;
453 ALTER PROCEDURE partial_revokes_db_2.p1t1i_c1 SQL SECURITY INVOKER;
454 CREATE TABLE partial_revokes_db.t2(c1 int, c2 int);
455 INSERT INTO partial_revokes_db.t2 VALUES (1,2), (3,4), (5,6);
456 CREATE SQL SECURITY INVOKER VIEW partial_revokes_db_2.v2t2i AS SELECT c1 FROM partial_revokes_db.t2;
457 CREATE PROCEDURE partial_revokes_db_2.p2t2i_c1() SELECT c1 FROM partial_revokes_db.t2;
458 ALTER PROCEDURE partial_revokes_db_2.p2t2i_c1 SQL SECURITY INVOKER;
459 GRANT SELECT, EXECUTE ON *.* TO foo@localhost;
460 REVOKE SELECT ON partial_revokes_db.* FROM foo@localhost;
461 GRANT SELECT ON partial_revokes_db.t1 TO foo@localhost;
462 GRANT SELECT(c1) ON partial_revokes_db.t2 TO foo@localhost;
463 SHOW GRANTS FOR foo@localhost;
464 Grants for foo@localhost
465 GRANT SELECT, EXECUTE ON *.* TO `foo`@`localhost`
466 REVOKE SELECT ON `partial_revokes_db`.* FROM `foo`@`localhost`
467 GRANT SELECT ON `partial_revokes_db`.`t1` TO `foo`@`localhost`
468 GRANT SELECT (`c1`) ON `partial_revokes_db`.`t2` TO `foo`@`localhost`
469 SELECT * FROM partial_revokes_db.t1;
470 c1
471 1
472 2
473 3
474 SELECT * FROM partial_revokes_db_2.v1t1i;
475 c1
476 1
477 2
478 3
479 CALL partial_revokes_db_2.p1t1i_c1();
480 COUNT(*)
481 3
482 SELECT c1 FROM partial_revokes_db.t2;
483 c1
484 1
485 3
486 5
487 SELECT * FROM partial_revokes_db_2.v2t2i;
488 c1
489 1
490 3
491 5
492 CALL partial_revokes_db_2.p2t2i_c1();
493 c1
494 1
495 3
496 5
497 # -----------------------------------------------------------------------
498 REVOKE ALL PRIVILEGES, GRANT OPTION FROM foo@localhost;
499 DROP TABLE partial_revokes_db.t1;
500 DROP VIEW partial_revokes_db_2.v1t1i;
501 DROP PROCEDURE partial_revokes_db_2.p1t1i_c1;
502 DROP TABLE partial_revokes_db.t2;
503 DROP VIEW partial_revokes_db_2.v2t2i;
504 DROP PROCEDURE partial_revokes_db_2.p2t2i_c1;
505 # End: Test cases for table and column grants
506 # -----------------------------------------------------------------------
507 # -----------------------------------------------------------------------
508 # Cleanup
509 # Drop database
510 DROP DATABASE partial_revokes_db;
511 DROP DATABASE partial_revokes_db_2;
512 # Drop users
513 DROP USER foo@localhost, bar@localhost;
514 # -----------------------------------------------------------------------
515 # -----------------------------------------------------------------------
516 # End : DML Tests for Partial Revokes
517 # -----------------------------------------------------------------------