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