Debian Codegraph @

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

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