Debian Codegraph @

mysql-test/suite/funcs_1/r/is_basics_mixed.result

raw
1 SHOW DATABASES LIKE 'information_schema';
2 Database (information_schema)
3 information_schema
4 #######################################################################
5 # Testcase 3.2.1.20: USE INFORMATION_SCHEMA is supported
6 #######################################################################
7 connection default;
8 USE test;
9 SELECT DATABASE();
10 DATABASE()
11 test
12 USE information_schema;
13 SELECT DATABASE();
14 DATABASE()
15 information_schema
16 DROP USER 'testuser1'@'localhost';
17 CREATE USER 'testuser1'@'localhost';
18 GRANT SELECT ON test.* TO 'testuser1'@'localhost';
19 connect testuser1, localhost, testuser1, , test;
20 SELECT DATABASE();
21 DATABASE()
22 test
23 USE information_schema;
24 SELECT DATABASE();
25 DATABASE()
26 information_schema
27 connection default;
28 disconnect testuser1;
29 DROP USER 'testuser1'@'localhost';
30 #######################################################################
31 # Testcase TBD1: The INFORMATION_SCHEMA cannot be dropped.
32 #######################################################################
33 DROP DATABASE information_schema;
34 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
35 #######################################################################
36 # Testcase TBD2: There cannot be a second database INFORMATION_SCHEMA.
37 #######################################################################
38 CREATE DATABASE information_schema;
39 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
40 ##################################################################################
41 # Testcase 3.2.1.6+3.2.1.7: No user may create an INFORMATION_SCHEMA table or view
42 ##################################################################################
43 connection default;
44 USE information_schema;
45 CREATE TABLE schemata ( c1 INT );
46 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
47 CREATE TABLE tables ( c1 INT );
48 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
49 CREATE TABLE columns ( c1 INT );
50 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
51 CREATE TABLE character_sets ( c1 INT );
52 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
53 CREATE TABLE collations ( c1 INT );
54 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
55 CREATE TABLE collation_character_set_applicability ( c1 INT );
56 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
57 CREATE TABLE routines ( c1 INT );
58 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
59 CREATE TABLE statistics ( c1 INT );
60 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
61 CREATE TABLE views ( c1 INT );
62 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
63 CREATE TABLE user_privileges ( c1 INT );
64 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
65 CREATE TABLE schema_privileges ( c1 INT );
66 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
67 CREATE TABLE table_privileges ( c1 INT );
68 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
69 CREATE TABLE column_privileges ( c1 INT );
70 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
71 CREATE TABLE table_constraints ( c1 INT );
72 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
73 CREATE TABLE key_column_usage ( c1 INT );
74 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
75 CREATE TABLE triggers ( c1 INT );
76 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
77 CREATE TABLE t1 (f1 INT, f2 INT, f3 INT);
78 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
79 CREATE VIEW tables AS SELECT 'garbage';
80 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
81 CREATE VIEW tables AS SELECT * FROM information_schema.tables;
82 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
83 CREATE VIEW v1 AS SELECT 'garbage';
84 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
85 USE test;
86 CREATE TABLE information_schema. schemata ( c1 INT );
87 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
88 CREATE TABLE information_schema. tables ( c1 INT );
89 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
90 CREATE TABLE information_schema. columns ( c1 INT );
91 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
92 CREATE TABLE information_schema. character_sets ( c1 INT );
93 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
94 CREATE TABLE information_schema. collations ( c1 INT );
95 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
96 CREATE TABLE information_schema. collation_character_set_applicability ( c1 INT );
97 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
98 CREATE TABLE information_schema. routines ( c1 INT );
99 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
100 CREATE TABLE information_schema. statistics ( c1 INT );
101 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
102 CREATE TABLE information_schema. views ( c1 INT );
103 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
104 CREATE TABLE information_schema. user_privileges ( c1 INT );
105 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
106 CREATE TABLE information_schema. schema_privileges ( c1 INT );
107 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
108 CREATE TABLE information_schema. table_privileges ( c1 INT );
109 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
110 CREATE TABLE information_schema. column_privileges ( c1 INT );
111 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
112 CREATE TABLE information_schema. table_constraints ( c1 INT );
113 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
114 CREATE TABLE information_schema. key_column_usage ( c1 INT );
115 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
116 CREATE TABLE information_schema. triggers ( c1 INT );
117 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
118 CREATE TABLE information_schema.t1 (f1 INT, f2 INT, f3 INT);
119 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
120 CREATE VIEW information_schema.tables AS SELECT 'garbage';
121 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
122 CREATE VIEW information_schema.tables AS
123 SELECT * FROM information_schema.tables;
124 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
125 CREATE VIEW information_schema.v1 AS SELECT 'garbage';
126 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
127 DROP USER 'testuser1'@'localhost';
128 CREATE USER 'testuser1'@'localhost';
129 GRANT ALL ON *.* TO testuser1@localhost;
130 SHOW GRANTS FOR testuser1@localhost;
131 Grants for testuser1@localhost
132 GRANT ALL PRIVILEGES ON *.* TO `testuser1`@`localhost`
133 connect testuser1, localhost, testuser1, , test;
134 USE information_schema;
135 CREATE TABLE schemata ( c1 INT );
136 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
137 CREATE TABLE tables ( c1 INT );
138 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
139 CREATE TABLE columns ( c1 INT );
140 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
141 CREATE TABLE character_sets ( c1 INT );
142 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
143 CREATE TABLE collations ( c1 INT );
144 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
145 CREATE TABLE collation_character_set_applicability ( c1 INT );
146 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
147 CREATE TABLE routines ( c1 INT );
148 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
149 CREATE TABLE statistics ( c1 INT );
150 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
151 CREATE TABLE views ( c1 INT );
152 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
153 CREATE TABLE user_privileges ( c1 INT );
154 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
155 CREATE TABLE schema_privileges ( c1 INT );
156 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
157 CREATE TABLE table_privileges ( c1 INT );
158 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
159 CREATE TABLE column_privileges ( c1 INT );
160 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
161 CREATE TABLE table_constraints ( c1 INT );
162 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
163 CREATE TABLE key_column_usage ( c1 INT );
164 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
165 CREATE TABLE triggers ( c1 INT );
166 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
167 CREATE TABLE t1 (f1 INT, f2 INT, f3 INT);
168 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
169 CREATE VIEW tables AS SELECT 'garbage';
170 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
171 CREATE VIEW tables AS SELECT * FROM information_schema.tables;
172 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
173 CREATE VIEW v1 AS SELECT 'garbage';
174 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
175 USE test;
176 CREATE TABLE information_schema. schemata ( c1 INT );
177 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
178 CREATE TABLE information_schema. tables ( c1 INT );
179 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
180 CREATE TABLE information_schema. columns ( c1 INT );
181 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
182 CREATE TABLE information_schema. character_sets ( c1 INT );
183 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
184 CREATE TABLE information_schema. collations ( c1 INT );
185 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
186 CREATE TABLE information_schema. collation_character_set_applicability ( c1 INT );
187 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
188 CREATE TABLE information_schema. routines ( c1 INT );
189 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
190 CREATE TABLE information_schema. statistics ( c1 INT );
191 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
192 CREATE TABLE information_schema. views ( c1 INT );
193 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
194 CREATE TABLE information_schema. user_privileges ( c1 INT );
195 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
196 CREATE TABLE information_schema. schema_privileges ( c1 INT );
197 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
198 CREATE TABLE information_schema. table_privileges ( c1 INT );
199 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
200 CREATE TABLE information_schema. column_privileges ( c1 INT );
201 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
202 CREATE TABLE information_schema. table_constraints ( c1 INT );
203 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
204 CREATE TABLE information_schema. key_column_usage ( c1 INT );
205 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
206 CREATE TABLE information_schema. triggers ( c1 INT );
207 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
208 CREATE TABLE information_schema.t1 (f1 INT, f2 INT, f3 INT);
209 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
210 CREATE VIEW information_schema.tables AS SELECT 'garbage';
211 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
212 CREATE VIEW information_schema.tables AS
213 SELECT * FROM information_schema.tables;
214 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
215 CREATE VIEW information_schema.v1 AS SELECT 'garbage';
216 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
217 connection default;
218 disconnect testuser1;
219 DROP USER 'testuser1'@'localhost';
220 ###############################################################################
221 # Testcase 3.2.1.1+3.2.1.2: INFORMATION_SCHEMA tables can be queried via SELECT
222 ###############################################################################
223 DROP DATABASE IF EXISTS db_datadict;
224 CREATE DATABASE db_datadict;
225 CREATE TABLE db_datadict.t1_first (f1 BIGINT UNIQUE, f2 BIGINT)
226 ENGINE = <some_engine>;
227 CREATE TABLE db_datadict.t1_second (f1 BIGINT UNIQUE, f2 BIGINT)
228 ENGINE = <some_engine>;
229 # Attention: The protocolling of the next result set is disabled.
230 SELECT * FROM information_schema.tables;
231 SELECT table_name FROM information_schema.tables
232 WHERE table_schema = 'db_datadict';
233 table_name
234 t1_first
235 t1_second
236 SELECT LENGTH(table_name) FROM information_schema.tables
237 WHERE table_schema = 'db_datadict' ORDER BY table_name;
238 LENGTH(table_name)
239 8
240 9
241 SELECT count(table_name) FROM information_schema.tables
242 WHERE table_schema LIKE 'db_datadic%';
243 count(table_name)
244 2
245 SELECT CAST((LENGTH(table_schema) + LENGTH(table_name)) AS DECIMAL(15,1))
246 FROM information_schema.tables
247 WHERE table_schema = 'db_datadict';
248 CAST((LENGTH(table_schema) + LENGTH(table_name)) AS DECIMAL(15,1))
249 19.0
250 20.0
251 SELECT table_name FROM information_schema.tables
252 WHERE table_name IN ('t1_first','t1_second') ORDER BY table_name LIMIT 1;
253 table_name
254 t1_first
255 SELECT table_name FROM information_schema.tables
256 WHERE table_name IN ('t1_first','t1_second') ORDER BY table_name LIMIT 1,1;
257 table_name
258 t1_second
259 SELECT table_name,table_schema AS my_col FROM information_schema.tables
260 WHERE table_name = 't1_first' AND table_schema = 'db_datadict';
261 table_name my_col
262 t1_first db_datadict
263 SELECT HIGH_PRIORITY table_name AS my_col FROM information_schema.tables
264 WHERE table_name = 't1_first' OR table_name = 't1_second';
265 my_col
266 t1_first
267 t1_second
268 SELECT 1 AS my_col FROM information_schema.tables
269 WHERE table_name = 't1_third';
270 my_col
271 SELECT table_name,table_schema INTO @table_name,@table_schema
272 FROM information_schema.tables
273 WHERE table_schema = 'db_datadict' ORDER BY table_name LIMIT 1;
274 SELECT @table_name,@table_schema;
275 @table_name @table_schema
276 t1_first db_datadict
277 SELECT table_name,table_schema
278 INTO OUTFILE '<OUTFILE>'
279 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
280 LINES TERMINATED BY '\n'
281 FROM information_schema.tables
282 WHERE table_schema = 'db_datadict' ORDER BY table_name;
283 "t1_first","db_datadict"
284 "t1_second","db_datadict"
285 SELECT table_name FROM information_schema.tables
286 WHERE table_name = 't1_first'
287 UNION ALL
288 SELECT table_name FROM information_schema.tables
289 WHERE table_name = 't1_second';
290 table_name
291 t1_first
292 t1_second
293 SELECT DISTINCT table_schema FROM information_schema.tables
294 WHERE table_name IN (SELECT table_name FROM information_schema.tables
295 WHERE table_schema = 'db_datadict')
296 ORDER BY table_name;
297 table_schema
298 db_datadict
299 SELECT table_name FROM information_schema.tables t1
300 LEFT JOIN information_schema.tables t2 USING(table_name,table_schema)
301 WHERE t2.table_schema = 'db_datadict'
302 ORDER BY table_name;
303 table_name
304 t1_first
305 t1_second
306 USE test;
307 SELECT * FROM tables;
308 ERROR 42S02: Table 'test.tables' doesn't exist
309 #########################################################################
310 # Testcase 3.2.1.17+3.2.1.18
311 #########################################################################
312 DROP DATABASE IF EXISTS db_datadict;
313 CREATE DATABASE db_datadict;
314 CREATE TABLE db_datadict.t1 (f1 BIGINT UNIQUE, f2 BIGINT)
315 ENGINE = <some_engine>;
316 SELECT * FROM db_datadict.t1;
317 f1 f2
318 DROP USER 'testuser1'@'localhost';
319 CREATE USER 'testuser1'@'localhost';
320 DROP USER 'testuser2'@'localhost';
321 CREATE USER 'testuser2'@'localhost';
322 GRANT CREATE VIEW,SELECT ON db_datadict.* TO testuser1@localhost
323 WITH GRANT OPTION;
324 GRANT USAGE ON db_datadict.* TO testuser2@localhost;
325 FLUSH PRIVILEGES;
326 GRANT SELECT on information_schema.* TO testuser1@localhost;
327 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
328 GRANT CREATE VIEW ON information_schema.* TO 'u_6_401018'@'localhost';
329 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
330 connect testuser1, localhost, testuser1, , db_datadict;
331 SELECT table_schema,table_name FROM information_schema.tables
332 WHERE table_schema = 'information_schema' AND table_name = 'tables';
333 table_schema table_name
334 information_schema TABLES
335 SELECT * FROM information_schema.table_privileges
336 WHERE table_schema = 'information_schema';
337 GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
338 SELECT * FROM information_schema.schema_privileges
339 WHERE table_schema = 'information_schema';
340 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
341 CREATE SQL SECURITY INVOKER VIEW db_datadict.v2 AS
342 SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
343 FROM information_schema.tables WHERE table_schema = 'db_datadict';
344 SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
345 FROM db_datadict.v2
346 ORDER BY TABLE_NAME;
347 TABLE_SCHEMA TABLE_NAME TABLE_TYPE
348 db_datadict t1 BASE TABLE
349 db_datadict v2 VIEW
350 SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
351 FROM information_schema.tables WHERE table_schema = 'db_datadict'
352 ORDER BY TABLE_NAME;
353 TABLE_SCHEMA TABLE_NAME TABLE_TYPE
354 db_datadict t1 BASE TABLE
355 db_datadict v2 VIEW
356 GRANT SELECT ON db_datadict.v2 to testuser2@localhost;
357 connect testuser2, localhost, testuser2, , db_datadict;
358 SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
359 FROM db_datadict.v2;
360 TABLE_SCHEMA TABLE_NAME TABLE_TYPE
361 db_datadict v2 VIEW
362 SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
363 FROM information_schema.tables WHERE table_schema = 'db_datadict';
364 TABLE_SCHEMA TABLE_NAME TABLE_TYPE
365 db_datadict v2 VIEW
366 connection default;
367 disconnect testuser1;
368 disconnect testuser2;
369 DROP USER 'testuser1'@'localhost';
370 DROP USER 'testuser2'@'localhost';
371 DROP DATABASE db_datadict;
372 #########################################################################
373 # Testcase 3.2.1.19
374 #########################################################################
375 DROP USER 'testuser1'@'localhost';
376 CREATE USER 'testuser1'@'localhost';
377 SELECT 'empty result set was expected' AS my_col
378 FROM information_schema.schema_privileges
379 WHERE table_schema = 'information_schema';
380 my_col
381 SELECT 'empty result set was expected' AS my_col
382 FROM information_schema.table_privileges
383 WHERE table_schema = 'information_schema';
384 my_col
385 SELECT 'empty result set was expected' AS my_col
386 FROM information_schema.column_privileges
387 WHERE table_schema = 'information_schema';
388 my_col
389 GRANT ALTER ON information_schema.*
390 TO 'testuser1'@'localhost';
391 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
392 GRANT ALTER ROUTINE ON information_schema.*
393 TO 'testuser1'@'localhost';
394 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
395 GRANT CREATE ON information_schema.*
396 TO 'testuser1'@'localhost';
397 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
398 GRANT CREATE ROUTINE ON information_schema.*
399 TO 'testuser1'@'localhost';
400 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
401 GRANT CREATE TEMPORARY TABLES ON information_schema.*
402 TO 'testuser1'@'localhost';
403 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
404 GRANT DELETE ON information_schema.*
405 TO 'testuser1'@'localhost';
406 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
407 GRANT DROP ON information_schema.*
408 TO 'testuser1'@'localhost';
409 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
410 GRANT EXECUTE ON information_schema.*
411 TO 'testuser1'@'localhost';
412 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
413 GRANT INDEX ON information_schema.*
414 TO 'testuser1'@'localhost';
415 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
416 GRANT INSERT ON information_schema.*
417 TO 'testuser1'@'localhost';
418 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
419 GRANT LOCK TABLES ON information_schema.*
420 TO 'testuser1'@'localhost';
421 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
422 GRANT UPDATE ON information_schema.*
423 TO 'testuser1'@'localhost';
424 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
425 SELECT 'empty result set was expected' AS my_col
426 FROM information_schema.schema_privileges
427 WHERE table_schema = 'information_schema';
428 my_col
429 SELECT 'empty result set was expected' AS my_col
430 FROM information_schema.table_privileges
431 WHERE table_schema = 'information_schema';
432 my_col
433 SELECT 'empty result set was expected' AS my_col
434 FROM information_schema.column_privileges
435 WHERE table_schema = 'information_schema';
436 my_col
437 DROP USER 'testuser1'@'localhost';
438 #########################################################################
439 # Testcase 3.2.1.16
440 #########################################################################
441 SELECT DISTINCT table_schema FROM information_schema.columns
442 WHERE table_schema LIKE 'db_data%';
443 table_schema
444 SELECT DISTINCT table_schema FROM information_schema.column_privileges
445 WHERE table_schema LIKE 'db_data%';
446 table_schema
447 SELECT DISTINCT constraint_schema,table_schema
448 FROM information_schema.key_column_usage
449 WHERE constraint_schema LIKE 'db_data%' OR table_schema LIKE 'db_data%';
450 constraint_schema table_schema
451 SELECT DISTINCT routine_schema FROM information_schema.routines
452 WHERE routine_schema LIKE 'db_data%';
453 routine_schema
454 SELECT DISTINCT schema_name FROM information_schema.schemata
455 WHERE schema_name LIKE 'db_data%';
456 schema_name
457 SELECT DISTINCT table_schema FROM information_schema.schema_privileges
458 WHERE table_schema LIKE 'db_data%';
459 table_schema
460 SELECT DISTINCT table_schema,index_schema FROM information_schema.statistics
461 WHERE table_schema LIKE 'db_data%' OR index_schema LIKE 'db_data%';
462 table_schema index_schema
463 SELECT DISTINCT table_schema FROM information_schema.tables
464 WHERE table_schema LIKE 'db_data%';
465 table_schema
466 SELECT DISTINCT constraint_schema,table_schema
467 FROM information_schema.table_constraints
468 WHERE constraint_schema LIKE 'db_data%' OR table_schema LIKE 'db_data%';
469 constraint_schema table_schema
470 SELECT DISTINCT table_schema FROM information_schema.table_privileges
471 WHERE table_schema LIKE 'db_data%';
472 table_schema
473 SELECT DISTINCT trigger_schema,event_object_schema
474 FROM information_schema.triggers
475 WHERE trigger_schema LIKE 'db_data%' OR event_object_schema LIKE 'db_data%';
476 trigger_schema event_object_schema
477 SELECT DISTINCT table_schema FROM information_schema.views
478 WHERE table_schema LIKE 'db_data%';
479 table_schema
480 DROP DATABASE IF EXISTS db_datadict;
481 CREATE DATABASE db_datadict;
482 CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT NOT NULL, f3 BIGINT,
483 PRIMARY KEY(f1))
484 ENGINE = <some_engine>;
485 CREATE UNIQUE INDEX UIDX ON db_datadict.t1(f3);
486 CREATE PROCEDURE db_datadict.sproc1() SELECT 'db_datadict';
487 CREATE FUNCTION db_datadict.func1() RETURNS INT RETURN 0;
488 CREATE TRIGGER db_datadict.trig1 BEFORE INSERT ON db_datadict.t1
489 FOR EACH ROW SET @aux = 1;
490 CREATE VIEW db_datadict.v1 AS SELECT * FROM db_datadict.t1;
491 CREATE VIEW db_datadict.v2 AS SELECT * FROM information_schema.tables;
492 SELECT DISTINCT table_schema FROM information_schema.columns
493 WHERE table_schema LIKE 'db_data%';
494 table_schema
495 db_datadict
496 SELECT DISTINCT table_schema FROM information_schema.column_privileges
497 WHERE table_schema LIKE 'db_data%';
498 table_schema
499 SELECT DISTINCT constraint_schema,table_schema
500 FROM information_schema.key_column_usage
501 WHERE constraint_schema LIKE 'db_data%' OR table_schema LIKE 'db_data%';
502 constraint_schema table_schema
503 db_datadict db_datadict
504 SELECT DISTINCT routine_schema FROM information_schema.routines
505 WHERE routine_schema LIKE 'db_data%';
506 routine_schema
507 db_datadict
508 SELECT DISTINCT schema_name FROM information_schema.schemata
509 WHERE schema_name LIKE 'db_data%';
510 schema_name
511 db_datadict
512 SELECT DISTINCT table_schema FROM information_schema.schema_privileges
513 WHERE table_schema LIKE 'db_data%';
514 table_schema
515 SELECT DISTINCT table_schema,index_schema FROM information_schema.statistics
516 WHERE table_schema LIKE 'db_data%' OR index_schema LIKE 'db_data%';
517 table_schema index_schema
518 db_datadict db_datadict
519 SELECT DISTINCT table_schema FROM information_schema.tables
520 WHERE table_schema LIKE 'db_data%';
521 table_schema
522 db_datadict
523 SELECT DISTINCT constraint_schema,table_schema
524 FROM information_schema.table_constraints
525 WHERE constraint_schema LIKE 'db_data%' OR table_schema LIKE 'db_data%';
526 constraint_schema table_schema
527 db_datadict db_datadict
528 SELECT DISTINCT table_schema FROM information_schema.table_privileges
529 WHERE table_schema LIKE 'db_data%';
530 table_schema
531 SELECT DISTINCT trigger_schema,event_object_schema
532 FROM information_schema.triggers
533 WHERE trigger_schema LIKE 'db_data%' OR event_object_schema LIKE 'db_data%';
534 trigger_schema event_object_schema
535 db_datadict db_datadict
536 SELECT DISTINCT table_schema FROM information_schema.views
537 WHERE table_schema LIKE 'db_data%';
538 table_schema
539 db_datadict
540 DROP USER 'testuser1'@'localhost';
541 CREATE USER 'testuser1'@'localhost';
542 GRANT ALL ON test.* TO 'testuser1'@'localhost';
543 connect testuser1, localhost, testuser1, , test;
544 SELECT DISTINCT table_schema FROM information_schema.columns
545 WHERE table_schema LIKE 'db_data%';
546 table_schema
547 SELECT DISTINCT table_schema FROM information_schema.column_privileges
548 WHERE table_schema LIKE 'db_data%';
549 table_schema
550 SELECT DISTINCT constraint_schema,table_schema
551 FROM information_schema.key_column_usage
552 WHERE constraint_schema LIKE 'db_data%' OR table_schema LIKE 'db_data%';
553 constraint_schema table_schema
554 SELECT DISTINCT routine_schema FROM information_schema.routines
555 WHERE routine_schema LIKE 'db_data%';
556 routine_schema
557 SELECT DISTINCT schema_name FROM information_schema.schemata
558 WHERE schema_name LIKE 'db_data%';
559 schema_name
560 SELECT DISTINCT table_schema FROM information_schema.schema_privileges
561 WHERE table_schema LIKE 'db_data%';
562 table_schema
563 SELECT DISTINCT table_schema,index_schema FROM information_schema.statistics
564 WHERE table_schema LIKE 'db_data%' OR index_schema LIKE 'db_data%';
565 table_schema index_schema
566 SELECT DISTINCT table_schema FROM information_schema.tables
567 WHERE table_schema LIKE 'db_data%';
568 table_schema
569 SELECT DISTINCT constraint_schema,table_schema
570 FROM information_schema.table_constraints
571 WHERE constraint_schema LIKE 'db_data%' OR table_schema LIKE 'db_data%';
572 constraint_schema table_schema
573 SELECT DISTINCT table_schema FROM information_schema.table_privileges
574 WHERE table_schema LIKE 'db_data%';
575 table_schema
576 SELECT DISTINCT trigger_schema,event_object_schema
577 FROM information_schema.triggers
578 WHERE trigger_schema LIKE 'db_data%' OR event_object_schema LIKE 'db_data%';
579 trigger_schema event_object_schema
580 SELECT DISTINCT table_schema FROM information_schema.views
581 WHERE table_schema LIKE 'db_data%';
582 table_schema
583 connection default;
584 disconnect testuser1;
585 DROP USER 'testuser1'@'localhost';
586 DROP DATABASE db_datadict;
587 ########################################################################
588 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
589 # DDL on INFORMATION_SCHEMA tables are not supported
590 ########################################################################
591 DROP PROCEDURE IF EXISTS test.p1;
592 CREATE PROCEDURE test.p1()
593 INSERT INTO information_schema.tables
594 SELECT * FROM information_schema.tables LIMIT 1;
595 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
596 CREATE PROCEDURE test.p1()
597 UPDATE information_schema.columns SET table_schema = 'garbage';
598 CALL test.p1();
599 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
600 DROP PROCEDURE test.p1;
601 CREATE PROCEDURE test.p1()
602 DELETE FROM information_schema.schemata;
603 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
604 #########################################################################
605 # Testcase 3.2.17.1+3.2.17.2: To be implemented outside of this script
606 #########################################################################