1
SHOW DATABASES LIKE 'information_schema';
2
Database (information_schema)
4
#######################################################################
5
# Testcase 3.2.1.20: USE INFORMATION_SCHEMA is supported
6
#######################################################################
7
# Switch to connection default
12
USE information_schema;
16
DROP USER 'testuser1'@'localhost';
17
CREATE USER 'testuser1'@'localhost';
18
# Establish connection testuser1 (user=testuser1)
22
USE 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 42S02: Unknown table 't1' in information_schema
77
CREATE VIEW tables AS SELECT 'garbage';
78
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
79
CREATE VIEW tables AS SELECT * FROM information_schema.tables;
80
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
81
CREATE VIEW v1 AS SELECT 'garbage';
82
ERROR 42S02: Unknown table 'v1' in information_schema
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 42S02: Unknown table 't1' in information_schema
118
CREATE VIEW information_schema.tables AS SELECT 'garbage';
119
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
120
CREATE VIEW information_schema.tables AS
121
SELECT * FROM information_schema.tables;
122
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
123
CREATE VIEW information_schema.v1 AS SELECT 'garbage';
124
ERROR 42S02: Unknown table 'v1' in 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 ALL PRIVILEGES ON *.* TO 'testuser1'@'localhost'
131
# Establish connection testuser1 (user=testuser1)
132
USE information_schema;
133
CREATE TABLE schemata ( c1 INT );
134
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
135
CREATE TABLE tables ( c1 INT );
136
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
137
CREATE TABLE columns ( c1 INT );
138
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
139
CREATE TABLE character_sets ( c1 INT );
140
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
141
CREATE TABLE collations ( c1 INT );
142
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
143
CREATE TABLE collation_character_set_applicability ( c1 INT );
144
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
145
CREATE TABLE routines ( c1 INT );
146
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
147
CREATE TABLE statistics ( c1 INT );
148
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
149
CREATE TABLE views ( c1 INT );
150
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
151
CREATE TABLE user_privileges ( c1 INT );
152
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
153
CREATE TABLE schema_privileges ( c1 INT );
154
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
155
CREATE TABLE table_privileges ( c1 INT );
156
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
157
CREATE TABLE column_privileges ( c1 INT );
158
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
159
CREATE TABLE table_constraints ( c1 INT );
160
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
161
CREATE TABLE key_column_usage ( c1 INT );
162
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
163
CREATE TABLE triggers ( c1 INT );
164
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
165
CREATE TABLE t1 (f1 INT, f2 INT, f3 INT);
166
ERROR 42S02: Unknown table 't1' in information_schema
167
CREATE VIEW tables AS SELECT 'garbage';
168
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
169
CREATE VIEW tables AS SELECT * FROM information_schema.tables;
170
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
171
CREATE VIEW v1 AS SELECT 'garbage';
172
ERROR 42S02: Unknown table 'v1' in information_schema
174
CREATE TABLE information_schema. schemata ( c1 INT );
175
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
176
CREATE TABLE information_schema. tables ( c1 INT );
177
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
178
CREATE TABLE information_schema. columns ( c1 INT );
179
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
180
CREATE TABLE information_schema. character_sets ( c1 INT );
181
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
182
CREATE TABLE information_schema. collations ( c1 INT );
183
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
184
CREATE TABLE information_schema. collation_character_set_applicability ( c1 INT );
185
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
186
CREATE TABLE information_schema. routines ( c1 INT );
187
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
188
CREATE TABLE information_schema. statistics ( c1 INT );
189
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
190
CREATE TABLE information_schema. views ( c1 INT );
191
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
192
CREATE TABLE information_schema. user_privileges ( c1 INT );
193
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
194
CREATE TABLE information_schema. schema_privileges ( c1 INT );
195
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
196
CREATE TABLE information_schema. table_privileges ( c1 INT );
197
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
198
CREATE TABLE information_schema. column_privileges ( c1 INT );
199
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
200
CREATE TABLE information_schema. table_constraints ( c1 INT );
201
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
202
CREATE TABLE information_schema. key_column_usage ( c1 INT );
203
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
204
CREATE TABLE information_schema. triggers ( c1 INT );
205
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
206
CREATE TABLE information_schema.t1 (f1 INT, f2 INT, f3 INT);
207
ERROR 42S02: Unknown table 't1' in information_schema
208
CREATE VIEW information_schema.tables AS SELECT 'garbage';
209
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
210
CREATE VIEW information_schema.tables AS
211
SELECT * FROM information_schema.tables;
212
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'information_schema'
213
CREATE VIEW information_schema.v1 AS SELECT 'garbage';
214
ERROR 42S02: Unknown table 'v1' in information_schema
215
# Switch to connection default (user=root) and close connection testuser1
216
DROP USER 'testuser1'@'localhost';
217
###############################################################################
218
# Testcase 3.2.1.1+3.2.1.2: INFORMATION_SCHEMA tables can be queried via SELECT
219
###############################################################################
220
DROP DATABASE IF EXISTS db_datadict;
221
CREATE DATABASE db_datadict;
222
CREATE TABLE db_datadict.t1_first (f1 BIGINT UNIQUE, f2 BIGINT)
223
ENGINE = <some_engine>;
224
CREATE TABLE db_datadict.t1_second (f1 BIGINT UNIQUE, f2 BIGINT)
225
ENGINE = <some_engine>;
226
# Attention: The protocolling of the next result set is disabled.
227
SELECT * FROM information_schema.tables;
228
SELECT table_name FROM information_schema.tables
229
WHERE table_schema = 'db_datadict';
233
SELECT LENGTH(table_name) FROM information_schema.tables
234
WHERE table_schema = 'db_datadict' ORDER BY table_name;
238
SELECT count(table_name) FROM information_schema.tables
239
WHERE table_schema LIKE 'db_datadic%';
242
SELECT CAST((LENGTH(table_schema) + LENGTH(table_name)) AS DECIMAL(15,1))
243
FROM information_schema.tables
244
WHERE table_schema = 'db_datadict';
245
CAST((LENGTH(table_schema) + LENGTH(table_name)) AS DECIMAL(15,1))
248
SELECT table_name FROM information_schema.tables
249
WHERE table_name IN ('t1_first','t1_second') ORDER BY table_name LIMIT 1;
252
SELECT table_name FROM information_schema.tables
253
WHERE table_name IN ('t1_first','t1_second') ORDER BY table_name LIMIT 1,1;
256
SELECT table_name,table_schema AS my_col FROM information_schema.tables
257
WHERE table_name = 't1_first' AND table_schema = 'db_datadict';
260
SELECT HIGH_PRIORITY table_name AS my_col FROM information_schema.tables
261
WHERE table_name = 't1_first' OR table_name = 't1_second';
265
SELECT 1 AS my_col FROM information_schema.tables
266
WHERE table_name = 't1_third';
268
SELECT table_name,table_schema INTO @table_name,@table_schema
269
FROM information_schema.tables
270
WHERE table_schema = 'db_datadict' ORDER BY table_name LIMIT 1;
271
SELECT @table_name,@table_schema;
272
@table_name @table_schema
274
SELECT table_name,table_schema
275
INTO OUTFILE '<OUTFILE>'
276
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
277
LINES TERMINATED BY '\n'
278
FROM information_schema.tables
279
WHERE table_schema = 'db_datadict' ORDER BY table_name;
280
"t1_first","db_datadict"
281
"t1_second","db_datadict"
282
SELECT table_name FROM information_schema.tables
283
WHERE table_name = 't1_first'
285
SELECT table_name FROM information_schema.tables
286
WHERE table_name = 't1_second';
290
SELECT DISTINCT table_schema FROM information_schema.tables
291
WHERE table_name IN (SELECT table_name FROM information_schema.tables
292
WHERE table_schema = 'db_datadict')
296
SELECT table_name FROM information_schema.tables t1
297
LEFT JOIN information_schema.tables t2 USING(table_name,table_schema)
298
WHERE t2.table_schema = 'db_datadict'
304
SELECT * FROM tables;
305
ERROR 42S02: Table 'test.tables' doesn't exist
306
#########################################################################
307
# Testcase 3.2.1.17+3.2.1.18
308
#########################################################################
309
DROP DATABASE IF EXISTS db_datadict;
310
CREATE DATABASE db_datadict;
311
CREATE TABLE db_datadict.t1 (f1 BIGINT UNIQUE, f2 BIGINT)
312
ENGINE = <some_engine>;
313
SELECT * FROM db_datadict.t1;
315
DROP USER 'testuser1'@'localhost';
316
CREATE USER 'testuser1'@'localhost';
317
DROP USER 'testuser2'@'localhost';
318
CREATE USER 'testuser2'@'localhost';
319
GRANT CREATE VIEW,SELECT ON db_datadict.* TO testuser1@localhost
321
GRANT USAGE ON db_datadict.* TO testuser2@localhost;
323
GRANT SELECT on information_schema.* TO testuser1@localhost;
324
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
325
GRANT CREATE VIEW ON information_schema.* TO 'u_6_401018'@'localhost';
326
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
327
# Establish connection testuser1 (user=testuser1)
328
SELECT table_schema,table_name FROM information_schema.tables
329
WHERE table_schema = 'information_schema' AND table_name = 'tables';
330
table_schema table_name
331
information_schema tables
332
SELECT * FROM information_schema.table_privileges
333
WHERE table_schema = 'information_schema';
334
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
335
SELECT * FROM information_schema.schema_privileges
336
WHERE table_schema = 'information_schema';
337
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
338
CREATE VIEW db_datadict.v2 AS
339
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
340
FROM information_schema.tables WHERE table_schema = 'db_datadict';
341
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
343
TABLE_SCHEMA TABLE_NAME TABLE_TYPE
344
db_datadict t1 BASE TABLE
346
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
347
FROM information_schema.tables WHERE table_schema = 'db_datadict';
348
TABLE_SCHEMA TABLE_NAME TABLE_TYPE
349
db_datadict t1 BASE TABLE
351
GRANT SELECT ON db_datadict.v2 to testuser2@localhost;
352
# Establish connection testuser2 (user=testuser2)
353
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
355
TABLE_SCHEMA TABLE_NAME TABLE_TYPE
357
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
358
FROM information_schema.tables WHERE table_schema = 'db_datadict';
359
TABLE_SCHEMA TABLE_NAME TABLE_TYPE
361
# Switch to connection default and close connections testuser1 and testuser2
362
DROP USER 'testuser1'@'localhost';
363
DROP USER 'testuser2'@'localhost';
364
DROP DATABASE db_datadict;
365
#########################################################################
367
#########################################################################
368
DROP USER 'testuser1'@'localhost';
369
CREATE USER 'testuser1'@'localhost';
370
SELECT 'empty result set was expected' AS my_col
371
FROM information_schema.schema_privileges
372
WHERE table_schema = 'information_schema';
374
SELECT 'empty result set was expected' AS my_col
375
FROM information_schema.table_privileges
376
WHERE table_schema = 'information_schema';
378
SELECT 'empty result set was expected' AS my_col
379
FROM information_schema.column_privileges
380
WHERE table_schema = 'information_schema';
382
GRANT ALTER ON information_schema.*
383
TO 'testuser1'@'localhost';
384
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
385
GRANT ALTER ROUTINE ON information_schema.*
386
TO 'testuser1'@'localhost';
387
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
388
GRANT CREATE ON information_schema.*
389
TO 'testuser1'@'localhost';
390
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
391
GRANT CREATE ROUTINE ON information_schema.*
392
TO 'testuser1'@'localhost';
393
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
394
GRANT CREATE TEMPORARY TABLES ON information_schema.*
395
TO 'testuser1'@'localhost';
396
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
397
GRANT DELETE ON information_schema.*
398
TO 'testuser1'@'localhost';
399
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
400
GRANT DROP ON information_schema.*
401
TO 'testuser1'@'localhost';
402
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
403
GRANT EXECUTE ON information_schema.*
404
TO 'testuser1'@'localhost';
405
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
406
GRANT INDEX ON information_schema.*
407
TO 'testuser1'@'localhost';
408
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
409
GRANT INSERT ON information_schema.*
410
TO 'testuser1'@'localhost';
411
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
412
GRANT LOCK TABLES ON information_schema.*
413
TO 'testuser1'@'localhost';
414
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
415
GRANT UPDATE ON information_schema.*
416
TO 'testuser1'@'localhost';
417
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
418
SELECT 'empty result set was expected' AS my_col
419
FROM information_schema.schema_privileges
420
WHERE table_schema = 'information_schema';
422
SELECT 'empty result set was expected' AS my_col
423
FROM information_schema.table_privileges
424
WHERE table_schema = 'information_schema';
426
SELECT 'empty result set was expected' AS my_col
427
FROM information_schema.column_privileges
428
WHERE table_schema = 'information_schema';
430
DROP USER 'testuser1'@'localhost';
431
#########################################################################
433
#########################################################################
434
SELECT DISTINCT table_schema FROM information_schema.columns
435
WHERE table_schema LIKE 'db_data%';
437
SELECT DISTINCT table_schema FROM information_schema.column_privileges
438
WHERE table_schema LIKE 'db_data%';
440
SELECT DISTINCT constraint_schema,table_schema
441
FROM information_schema.key_column_usage
442
WHERE constraint_schema LIKE 'db_data%' OR table_schema LIKE 'db_data%';
443
constraint_schema table_schema
444
SELECT DISTINCT routine_schema FROM information_schema.routines
445
WHERE routine_schema LIKE 'db_data%';
447
SELECT DISTINCT schema_name FROM information_schema.schemata
448
WHERE schema_name LIKE 'db_data%';
450
SELECT DISTINCT table_schema FROM information_schema.schema_privileges
451
WHERE table_schema LIKE 'db_data%';
453
SELECT DISTINCT table_schema,index_schema FROM information_schema.statistics
454
WHERE table_schema LIKE 'db_data%' OR index_schema LIKE 'db_data%';
455
table_schema index_schema
456
SELECT DISTINCT table_schema FROM information_schema.tables
457
WHERE table_schema LIKE 'db_data%';
459
SELECT DISTINCT constraint_schema,table_schema
460
FROM information_schema.table_constraints
461
WHERE constraint_schema LIKE 'db_data%' OR table_schema LIKE 'db_data%';
462
constraint_schema table_schema
463
SELECT DISTINCT table_schema FROM information_schema.table_privileges
464
WHERE table_schema LIKE 'db_data%';
466
SELECT DISTINCT trigger_schema,event_object_schema
467
FROM information_schema.triggers
468
WHERE trigger_schema LIKE 'db_data%' OR event_object_schema LIKE 'db_data%';
469
trigger_schema event_object_schema
470
SELECT DISTINCT table_schema FROM information_schema.views
471
WHERE table_schema LIKE 'db_data%';
473
DROP DATABASE IF EXISTS db_datadict;
474
CREATE DATABASE db_datadict;
475
CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT NOT NULL, f3 BIGINT,
477
ENGINE = <some_engine>;
478
CREATE UNIQUE INDEX UIDX ON db_datadict.t1(f3);
479
CREATE PROCEDURE db_datadict.sproc1() SELECT 'db_datadict';
480
CREATE FUNCTION db_datadict.func1() RETURNS INT RETURN 0;
481
CREATE TRIGGER db_datadict.trig1 BEFORE INSERT ON db_datadict.t1
482
FOR EACH ROW SET @aux = 1;
483
CREATE VIEW db_datadict.v1 AS SELECT * FROM db_datadict.t1;
484
CREATE VIEW db_datadict.v2 AS SELECT * FROM information_schema.tables;
485
SELECT DISTINCT table_schema FROM information_schema.columns
486
WHERE table_schema LIKE 'db_data%';
489
SELECT DISTINCT table_schema FROM information_schema.column_privileges
490
WHERE table_schema LIKE 'db_data%';
492
SELECT DISTINCT constraint_schema,table_schema
493
FROM information_schema.key_column_usage
494
WHERE constraint_schema LIKE 'db_data%' OR table_schema LIKE 'db_data%';
495
constraint_schema table_schema
496
db_datadict db_datadict
497
SELECT DISTINCT routine_schema FROM information_schema.routines
498
WHERE routine_schema LIKE 'db_data%';
501
SELECT DISTINCT schema_name FROM information_schema.schemata
502
WHERE schema_name LIKE 'db_data%';
505
SELECT DISTINCT table_schema FROM information_schema.schema_privileges
506
WHERE table_schema LIKE 'db_data%';
508
SELECT DISTINCT table_schema,index_schema FROM information_schema.statistics
509
WHERE table_schema LIKE 'db_data%' OR index_schema LIKE 'db_data%';
510
table_schema index_schema
511
db_datadict db_datadict
512
SELECT DISTINCT table_schema FROM information_schema.tables
513
WHERE table_schema LIKE 'db_data%';
516
SELECT DISTINCT constraint_schema,table_schema
517
FROM information_schema.table_constraints
518
WHERE constraint_schema LIKE 'db_data%' OR table_schema LIKE 'db_data%';
519
constraint_schema table_schema
520
db_datadict db_datadict
521
SELECT DISTINCT table_schema FROM information_schema.table_privileges
522
WHERE table_schema LIKE 'db_data%';
524
SELECT DISTINCT trigger_schema,event_object_schema
525
FROM information_schema.triggers
526
WHERE trigger_schema LIKE 'db_data%' OR event_object_schema LIKE 'db_data%';
527
trigger_schema event_object_schema
528
db_datadict db_datadict
529
SELECT DISTINCT table_schema FROM information_schema.views
530
WHERE table_schema LIKE 'db_data%';
533
DROP USER 'testuser1'@'localhost';
534
CREATE USER 'testuser1'@'localhost';
535
GRANT ALL ON test.* TO 'testuser1'@'localhost';
536
# Establish connection testuser1 (user=testuser1)
537
SELECT DISTINCT table_schema FROM information_schema.columns
538
WHERE table_schema LIKE 'db_data%';
540
SELECT DISTINCT table_schema FROM information_schema.column_privileges
541
WHERE table_schema LIKE 'db_data%';
543
SELECT DISTINCT constraint_schema,table_schema
544
FROM information_schema.key_column_usage
545
WHERE constraint_schema LIKE 'db_data%' OR table_schema LIKE 'db_data%';
546
constraint_schema table_schema
547
SELECT DISTINCT routine_schema FROM information_schema.routines
548
WHERE routine_schema LIKE 'db_data%';
550
SELECT DISTINCT schema_name FROM information_schema.schemata
551
WHERE schema_name LIKE 'db_data%';
553
SELECT DISTINCT table_schema FROM information_schema.schema_privileges
554
WHERE table_schema LIKE 'db_data%';
556
SELECT DISTINCT table_schema,index_schema FROM information_schema.statistics
557
WHERE table_schema LIKE 'db_data%' OR index_schema LIKE 'db_data%';
558
table_schema index_schema
559
SELECT DISTINCT table_schema FROM information_schema.tables
560
WHERE table_schema LIKE 'db_data%';
562
SELECT DISTINCT constraint_schema,table_schema
563
FROM information_schema.table_constraints
564
WHERE constraint_schema LIKE 'db_data%' OR table_schema LIKE 'db_data%';
565
constraint_schema table_schema
566
SELECT DISTINCT table_schema FROM information_schema.table_privileges
567
WHERE table_schema LIKE 'db_data%';
569
SELECT DISTINCT trigger_schema,event_object_schema
570
FROM information_schema.triggers
571
WHERE trigger_schema LIKE 'db_data%' OR event_object_schema LIKE 'db_data%';
572
trigger_schema event_object_schema
573
SELECT DISTINCT table_schema FROM information_schema.views
574
WHERE table_schema LIKE 'db_data%';
576
# Switch to connection default and close connections testuser1 and testuser2
577
DROP USER 'testuser1'@'localhost';
578
DROP DATABASE db_datadict;
579
########################################################################
580
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
581
# DDL on INFORMATION_SCHEMA tables are not supported
582
########################################################################
583
DROP PROCEDURE IF EXISTS test.p1;
584
CREATE PROCEDURE test.p1()
585
INSERT INTO information_schema.tables
586
SELECT * FROM information_schema.tables LIMIT 1;
588
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
589
DROP PROCEDURE test.p1;
590
CREATE PROCEDURE test.p1()
591
UPDATE information_schema.columns SET table_schema = 'garbage';
593
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
594
DROP PROCEDURE test.p1;
595
CREATE PROCEDURE test.p1()
596
DELETE FROM information_schema.schemata;
598
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
599
DROP PROCEDURE test.p1;
600
#########################################################################
601
# Testcase 3.2.17.1+3.2.17.2: To be implemented outside of this script
602
#########################################################################