1
#### suite/funcs_1/datadict/datadict_master.inc
3
--disable_abort_on_error
6
. It is intended that the 3 <engine>__datadict.test files are named this way to be
7
. sure they are - in a *full run* of the suite - the first tests done for each
8
. storage engine. Using two _ and the order of processing in mysql-test-run.pl
9
. ensures this in an easy way.
11
. If needed a restart could be implemented later between the storage engines if
12
. values changes in the result depending from the position where the
13
. *__datadict.test are started. This can be a result of showing e.g. maximum
14
. values of the number of rows of tables.
16
. This .result file has been checked OK with Linux 5.0.23-bk,
17
. ChangeSet@1.2211, 2006-06-28 10:11:43-07:00.
19
--source include/show_msg80.inc
21
################################################################################
23
let $message= FIXME: There are subtests that are switched off due to known bugs:;
24
--source include/show_msg.inc
25
#set variable(s) here to be able to switch crashing sub tests with ONE change HERE.
26
#change the variable(s) to enable / disable the crashing parts.
28
# different 'logics' are used because sometimes codelines needs to be switched off
29
# and otherwise some extra statements needs to be executed as long as the bug is not
31
let $have_bug_11589= 1;
33
#seems not to work: --vertical_results
34
eval SELECT $have_bug_11589 AS "have_bug_11589";
35
#seems not to work: --horizontal_results
38
# Bug#11589: mysqltest, --ps-protocol, strange output, float/double/real with zerofill
39
# is not fixed, we must switch the ps-protocol for some statements off.
40
# If this bug is fixed, please
41
# 1. set the following variable to 0
42
# 2. check, if the test passes
43
# 3. remove the workarounds
46
let $message= There are some statements where the ps-protocol is switched off.
47
This may come from the bug listed below, ir from other problems.
48
Bug#11589: mysqltest, --ps-protocol, strange output, float/double/real with zerofill;
49
--source include/show_msg80.inc
52
################################################################################
55
# loading the tables (data is not really needed in this test) is separated to
56
# make it easier in this file to show the message above.
57
--source suite/funcs_1/datadict/datadict_load.inc
59
#FIXME: - check for remaining change of object names to standards: db_, tb_, v_, u_, ...
60
#FIXME: - check warnings when data is loaded (Data truncated for column ...)
61
#FIXME: - change connect() to use less users / connections
63
#FIXME: - check for additional 'FIXME' here in the script
65
use information_schema;
66
--source suite/funcs_1/include/show_connection.inc
69
################################################################################
73
################################################################################
75
let $message= Testcase 3.2.1.1:;
76
--source include/show_msg80.inc
78
################################################################################
79
# Testcase 3.2.1.1: Ensure that every INFORMATION_SCHEMA table can be queried
80
# with a SELECT statement, just as if it were an ordinary
82
################################################################################
84
# create at least one object for all 'tables' to be checked
86
DROP DATABASE IF EXISTS db_datadict;
88
CREATE DATABASE db_datadict;
91
CREATE VIEW v1 AS SELECT * FROM information_schema.tables;
93
# try to get the server's name to be able to clean-up the result from machine
95
CREATE OR REPLACE VIEW db_datadict.vu1 as
97
FROM information_schema.user_privileges;
98
CREATE OR REPLACE VIEW db_datadict.vu as
100
SUBSTRING( u, LENGTH(SUBSTRING_INDEX(u,_utf8'@',1))+3 )
102
SUBSTRING( u, LENGTH(SUBSTRING_INDEX(u,_utf8'@',1))+3,
103
LENGTH( SUBSTRING( u,
104
LENGTH( SUBSTRING_INDEX(u, _utf8'@',1)) +3 )) - 1 )
106
FROM db_datadict.vu1;
107
--replace_result $SERVER_NAME <SERVER_NAME>
108
SELECT * FROM db_datadict.vu;
111
CREATE PROCEDURE db_datadict.sp_1()
113
SELECT * FROM db_datadict.v1;
117
#FIXME 3.2.1.1: add missing objects of each type to have something to select
118
#FIXME 3.2.1.1: - FUNCTION
119
#FIXME 3.2.1.1: - TRIGGER
121
USE information_schema;
124
select * from schemata ORDER BY 2 DESC, 1 ASC;
128
--disable_ps_protocol
131
#SELECT * FROM tables;
133
#FIXME 3.2.1.1: we split the "SELECT * FROM tables" in two parts until
134
#FIXME 3.2.1.1: Bug #12397: wrong values shown in column CREATE_OPTIONS of
135
#FIXME 3.2.1.1: INFORMATION_SCHEMA.TABLES is solved, one with 'more' and one
136
#FIXME 3.2.1.1: with 'less' replace
145
--replace_column 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "YYYY-MM-DD hh:mm:ss" 16 "YYYY-MM-DD hh:mm:ss" 20 "#CO#"
147
WHERE table_schema = 'information_schema';
155
--replace_column 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "YYYY-MM-DD hh:mm:ss" 16 "YYYY-MM-DD hh:mm:ss"
157
WHERE NOT( table_schema = 'information_schema');
161
select s.catalog_name, s.schema_name, s.default_character_set_name,
162
t.table_type, t.engine
163
from schemata s inner join tables t
164
ORDER BY s.schema_name, s.default_character_set_name, table_type, engine;
165
--source suite/funcs_1/datadict/datadict_bug_12777.inc
166
select * from columns;
167
select * from character_sets;
168
select sum(id) from collations;
169
select collation_name, character_set_name into @x,@y
170
from collation_character_set_applicability limit 1;
173
--replace_column 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss"
174
select * from routines;
176
select count(*) from routines;
177
select * from statistics;
179
--replace_result $SERVER_NAME <SERVER_NAME>
180
select * from user_privileges;
181
select * from schema_privileges;
182
select * from table_privileges;
183
select * from column_privileges;
184
select * from table_constraints;
185
select * from key_column_usage;
186
select count(*) as max_recs from key_column_usage;
188
select max(cardinality) from statistics;
190
select concat("View '",
191
table_name, "' is associated with the database '", table_schema, "'.")
192
AS "Who is Who for the Views"
195
select concat("Table or view '", table_name,
196
"' is associated with the database '", table_schema, "'.") as "Who is Who"
199
--replace_result $SERVER_NAME <SERVER_NAME>
200
select grantee as "user's having select privilege",
201
substring( grantee, length(SUBSTRING_INDEX(grantee,_utf8'@',1))+2 )
202
from user_privileges where privilege_type = 'select';
204
select all table_schema from schema_privileges limit 0,5;
206
select distinct(privilege_type) from table_privileges;
208
select * from column_privileges
209
group by table_schema having table_schema = 'db_datadict';
211
select * from table_constraints limit 0,5;
212
select count(*) as max_recs from key_column_usage limit 0,5;
214
select information_schema.tables.table_name as "table name",
215
count(distinct(column_name)) as "no of columns in the table"
216
from information_schema.tables left outer join information_schema.columns on
217
information_schema.tables.table_name = information_schema.columns.table_name
218
group by information_schema.tables.table_name;
220
# Reference Manual 22.1.16 - we will add more ...:
221
# select * from parameters;
222
# select * from referential_constraints;
223
# select * from triggers;
225
let $message= root: simple select to check all - and never forget some - tables;
226
let $dd_part1= SELECT * FROM;
227
let $dd_part2= LIMIT 1;
228
--source suite/funcs_1/datadict/datadict_tables.inc
230
# check again, but from different database (will fail due to missing database name)
234
select * from schemata;
236
select * from tables;
238
select s.catalog_name, s.schema_name, s.default_character_set_name,
239
t.table_type, t.engine
240
from schemata s inner join tables t
241
ORDER BY s.catalog_name, s.schema_name, s.default_character_set_name;
243
select * from columns limit 0, 5;
245
select * from character_sets limit 0, 5;
247
select * from collations limit 0, 5;
249
select * from collation_character_set_applicability limit 0, 5;
251
select * from routines limit 0, 5;
253
select * from statistics limit 0, 5;
255
select * from views limit 0, 5;
257
select * from user_privileges limit 0, 5;
259
select * from schema_privileges limit 0, 5;
261
select * from table_privileges limit 0, 5;
263
select * from column_privileges limit 0, 5;
265
select * from table_constraints limit 0, 5;
267
select * from key_column_usage limit 0, 5;
268
# Reference Manual 22.1.16 - we will add more ...:
270
# select * from parameters;
272
# select * from referential_constraints;
274
# select * from triggers;
275
let $message= will fail due to missing database name;
276
let $dd_part1= SELECT * FROM;
278
--source suite/funcs_1/datadict/datadict_tables_error_1146.inc
280
# now check from "other" database, but with database name
281
select * from information_schema.schemata ORDER BY 2 DESC;
283
#SELECT * FROM information_schema.tables;
285
#FIXME 3.2.1.1: we split the "SELECT * FROM tables" in two parts until
286
#FIXME 3.2.1.1: Bug #12397: wrong values shown in column CREATE_OPTIONS of
287
#FIXME 3.2.1.1: INFORMATION_SCHEMA.TABLES is solved, one with 'more' and one
288
#FIXME 3.2.1.1: with 'less' replace
299
--disable_ps_protocol
302
--replace_column 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "YYYY-MM-DD hh:mm:ss" 16 "YYYY-MM-DD hh:mm:ss" 20 "#CO#"
303
SELECT * FROM information_schema.tables
304
WHERE table_schema = 'information_schema';
312
--replace_column 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "YYYY-MM-DD hh:mm:ss" 16 "YYYY-MM-DD hh:mm:ss"
313
SELECT * FROM information_schema.tables
314
WHERE NOT( table_schema = 'information_schema');
318
select s.catalog_name, s.schema_name, s.default_character_set_name,
319
t.table_type, t.engine
320
from information_schema.schemata s inner join information_schema.tables t
321
ORDER BY s.schema_name, s.default_character_set_name, table_type, engine;
323
--source suite/funcs_1/datadict/datadict_bug_12777.inc
324
select * from information_schema.columns limit 0, 5;
325
select * from information_schema.character_sets limit 0, 5;
326
select * from information_schema.collations limit 0, 5;
327
select * from information_schema.collation_character_set_applicability limit 0, 5;
328
--replace_column 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss"
329
select * from information_schema.routines limit 0, 5;
330
select * from information_schema.statistics limit 0, 5;
331
select * from information_schema.views limit 0, 5;
332
--replace_result $SERVER_NAME <SERVER_NAME>
333
select * from information_schema.user_privileges limit 0, 5;
334
select * from information_schema.schema_privileges limit 0, 5;
335
select * from information_schema.table_privileges limit 0, 5;
336
select * from information_schema.column_privileges limit 0, 5;
337
select * from information_schema.table_constraints limit 0, 5;
338
select * from information_schema.key_column_usage limit 0, 5;
339
select count(*) as max_recs from information_schema.key_column_usage limit 0, 5;
341
# Reference Manual 22.1.16 - we will add more ...:
342
# select * from information_schema.parameters;
343
# select * from information_schema.referential_constraints;
344
# select * from information_schema.triggers;
346
let $message= root: check with db name;
347
let $dd_part1= SELECT COUNT(*) FROM information_schema.;
349
--source suite/funcs_1/datadict/datadict_tables.inc
353
DROP VIEW v1, vu1, vu;
354
DROP PROCEDURE db_datadict.sp_1;
355
USE information_schema;
356
# ------------------------------------------------------------------------------
358
let $message= Testcase 3.2.1.2:;
359
--source include/show_msg80.inc
361
################################################################################
362
# Testcase 3.2.1.2: Ensure that queries on an INFORMATION_SCHEMA table can
363
# accept all SELECT statement options and are always
364
# correctly evaluated.
365
################################################################################
367
# currently here only a subset of select statement options is checked, it's still
368
# not possible to check here all possible options
369
select catalog_name, schema_name, default_character_set_name
370
from schemata where schema_name like '%s%';
372
select count(*) as tot_tabs from tables;
373
select count(*) as the_cols from columns;
375
select max(maxlen) as the_max from character_sets;
376
select * from collations order by id asc limit 0, 5;
377
select * from collation_character_set_applicability
378
order by character_set_name desc, collation_name limit 0, 5;
380
select routine_definition from routines;
381
select * from statistics group by index_name asc limit 0, 5;
382
select concat(table_schema, ', ', table_name, ', ', view_definition) view_info
384
select concat(table_schema, ', ', table_name) "Table_info"
385
from tables ORDER BY 1;
387
--replace_result $SERVER_NAME <SERVER_NAME>
388
select distinct grantee from user_privileges;
389
select * from schema_privileges where table_catalog is null limit 0, 5;
390
select * from table_privileges where grantee like '%r%' limit 0, 5;
392
select * from column_privileges where table_catalog is not null limit 0, 5;
393
select HIGH_PRIORITY * from table_constraints
394
group by constraint_name desc limit 0, 5;
395
select sum(ordinal_position) from key_column_usage;
397
select * from schemata limit 0,5;
398
select * from schemata limit 0,5;
399
--replace_result $SERVER_NAME <SERVER_NAME>
400
select distinct grantee from user_privileges;
401
--replace_result $SERVER_NAME <SERVER_NAME>
402
select all grantee from user_privileges;
404
select id , character_set_name from collations order by id asc limit 10;
406
select table_catalog from columns
408
select table_catalog from tables limit 0,5;
409
select table_catalog from columns
411
select table_catalog from tables limit 0,5;
413
select all schema_name from information_schema.schemata;
415
# the $ENGINE_TYPE variable is used here ONLY to have 3 different file names in
416
# the three datadict testcases innodb_*, memory_* and myisam_*
418
INTO OUTFILE '../tmp/out.$ENGINE_TYPE.file'
419
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
420
LINES TERMINATED BY '\n'
421
FROM schemata LIMIT 0, 5;
426
INTO OUTFILE '../tmp/out.$ENGINE_TYPE.db.file'
427
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
428
LINES TERMINATED BY '\n'
429
FROM information_schema.schemata
430
WHERE schema_name LIKE 'db_%';
432
# check also with a 'simple' user
433
CREATE USER user_3212@localhost;
434
GRANT ALL ON db_datadict.* TO user_3212@localhost;
436
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
437
connect (u3212,localhost,user_3212,,db_datadict);
438
--source suite/funcs_1/include/show_connection.inc
440
# no db given --> db_datadict.schema does not exist
443
INTO OUTFILE '../tmp/out.$ENGINE_TYPE.user.file'
444
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
445
LINES TERMINATED BY '\n'
446
FROM schemata LIMIT 0, 5;
447
# FIXME 3.2.1.2: why do we get different error numbers with and without OUTFILE ?
450
FROM schemata LIMIT 0, 5;
453
INTO OUTFILE '../tmp/out.$ENGINE_TYPE.user.db.file'
454
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
455
LINES TERMINATED BY '\n'
456
FROM information_schema.schemata
457
WHERE schema_name LIKE 'db_%';
458
# FIXME: why do we get different error numbers with and without OUTFILE ?
459
#FIXME this should fail! --error 1146
461
FROM information_schema.schemata
462
WHERE schema_name LIKE 'db_%';
464
USE information_schema;
466
# no db given --> db_datadict.schema does not exist
468
INTO OUTFILE '../tmp/out.$ENGINE_TYPE.user_2.file'
469
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
470
LINES TERMINATED BY '\n'
471
FROM schemata LIMIT 0, 5;
472
# FIXME 3.2.1.2: why do we get different error numbers with and without OUTFILE ?
474
FROM schemata LIMIT 0, 5;
477
INTO OUTFILE '../tmp/out.$ENGINE_TYPE.user_2.db.file'
478
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
479
LINES TERMINATED BY '\n'
480
FROM information_schema.schemata
481
WHERE schema_name LIKE 'db_%';
482
# FIXME: why do we get different error numbers with and without OUTFILE ?
483
#FIXME this should fail! --error 1146
485
FROM information_schema.schemata
486
WHERE schema_name LIKE 'db_%';
490
USE information_schema;
491
--source suite/funcs_1/include/show_connection.inc
494
select table_catalog "1", table_schema "2", table_name "3", column_name "4"
495
from information_schema.columns
497
select table_catalog, table_schema, table_name,
498
concat( "*** type = ", table_type )
499
from information_schema.tables
500
order by 3, 4 desc, 1, 2 limit 30;
502
use information_schema;
503
--source suite/funcs_1/datadict/datadict_bug_12777.inc
504
select table_catalog "1", table_schema "2", table_name "3", column_name "4"
507
select table_catalog, table_schema, table_name,
508
concat( "*** type = ", table_type )
510
order by 3, 4 desc, 1, 2 limit 30;
513
DROP USER user_3212@localhost;
515
# ------------------------------------------------------------------------------
517
let $message= Testcase 3.2.1.3:;
518
--source include/show_msg80.inc
520
################################################################################
521
# Testcase 3.2.1.3: Ensure that no user may execute an INSERT statement on any
522
# INFORMATION_SCHEMA table.
523
################################################################################
525
#FIXME: in this block we had --error 1288 until Mid Sep05, check the change!
527
insert into schemata (catalog_name, schema_name, default_character_set_name, sql_path)
528
values ('null', 'db1', 'latin1', 'null');
530
insert into tables (table_schema, table_name)values('db_datadict', 't1');
532
insert into columns (table_name, column_name)values('t3', 'f2');
534
insert into character_sets (character_set_name, default_collate_name, description, maxlen)
535
values('cp1251', 'cp1251_general_ci', 'windows cyrillic', 1);
537
insert into collations ( collation_name, character_set_name, id, is_default, is_compiled, sortlen)
538
values ('cp1251_bin', 'cp1251', 50, '', '', 0);
540
insert into collation_character_set_applicability (collation_name, character_set_name)
541
values (' big5_chinese_ci', 'big6');
543
insert into routines(routine_name, routine_type ) values ('p2', 'procedure');
545
insert into statistics(table_schema, table_name, index_name)
546
values ('mysql', 'db', 'primary');
548
insert into views(table_schema, table_name) values ('db2', 'v2');
550
insert into user_privileges (privilege_type, is_grantable) values ('select', 'yes');
552
insert into schema_privileges (table_schema, privilege_type) values('db2', 'insert');
554
insert into table_privileges (able_schema, table_name, privilege_type)
555
values('db2', 'v2', 'insert');
557
insert into column_privileges (table_name, column_name, privilege_type)
558
values ('t3', 'f3', 'insert');
560
insert into table_constraints ( constraint_schema, constraint_name, table_schema)
561
values ('primary', 'mysql', 'user');
563
insert into key_column_usage (constraint_schema, constraint_name, table_name)
564
values ('mysql', 'primary', 'db');
566
# insert through a procedure
568
drop procedure if exists db_datadict.sp_4_1_3;
572
create procedure db_datadict.sp_4_1_3()
574
insert into information_schema.schema_privileges (table_schema,privilege_type)
575
values('db2','insert');
579
#FIXME: check for the diffs Win ./. Linux
580
SELECT table_schema, privilege_type FROM information_schema.schema_privileges
581
WHERE table_schema LIKE 'db%';
584
call db_datadict.sp_4_1_3();
586
#FIXME: check for the diffs Win ./. Linux
587
SELECT table_schema, privilege_type FROM information_schema.schema_privileges
588
WHERE table_schema LIKE 'db%';
591
drop procedure db_datadict.sp_4_1_3;
593
# insert into information_schema as a limited user
595
CREATE USER user_4_1_3@localhost;
597
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
598
connect (u413,localhost,user_4_1_3,,test);
599
--source suite/funcs_1/include/show_connection.inc
601
use information_schema;
603
#FIXME: check later the change from 1288 to 1044 (since Mid Sep05)
605
insert into table_constraints ( constraint_schema, constraint_name, table_schema)
606
values ('primary', 'mysql', 'user');
609
--source suite/funcs_1/include/show_connection.inc
610
# ------------------------------------------------------------------------------
612
let $message= Testcase 3.2.1.4:;
613
--source include/show_msg80.inc
615
################################################################################
616
# Testcase 3.2.1.4: Ensure that no user may execute an UPDATE statement on any
617
# INFORMATION_SCHEMA table.
618
################################################################################
620
use information_schema;
621
--source suite/funcs_1/include/show_connection.inc
623
#FIXME: check later the change from 1288 to 1044 (since Mid Sep05) in the whole next block
625
update schemata set schema_name = 'db5' where default_character_set_name = 'latin1';
627
update tables set table_schema = 'db_datadict1' where table_name = 't1';
629
update columns set table_name = 't4' where column_name = 'f2';
631
update character_sets set character_set_name = 'cp1252' where maxlen = 1;
633
update collations set collation_name = 'cp1253_bin'
634
where character_set_name = 'cp1251';
636
update collation_character_set_applicability set collation_name = 'big6_chinese_ci'
637
where character_set_name = 'big6';
639
update routines set routine_name = p2 where routine_body = 'sql';
641
update statistics set table_schema = 'mysql1' where table_name = 'db';
643
update views set table_schema = 'db3' where table_name = 'v1';
645
update user_privileges set privilege_type = 'insert' where is_grantable = 'yes';
647
update schema_privileges set table_schema = 'db2' where privilege_type = 'select';
649
update table_privileges set table_name = 'v3' where privilege_type = 'select';
651
update column_privileges set table_name = 't4' where column_name = 'f3';
653
update table_constraints set constraint_schema = 'primary'
654
where table_schema = 'proc';
656
update key_column_usage set table_name = 'db1' where constraint_name = 'primary';
658
# update through a procedure
660
drop procedure if exists db_datadict.sp_4_1_4;
664
create procedure db_datadict.sp_4_1_4()
666
update information_schema.routines set routine_name = 'p2'
667
where routine_name = 'sp_4_1_4';
671
#FIXME: check for the diffs Win ./. Linux
672
--replace_column 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss"
673
select * from information_schema.routines;
676
call db_datadict.sp_4_1_4();
678
#FIXME: check for the diffs Win ./. Linux
679
--replace_column 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss"
680
select * from information_schema.routines;
683
drop procedure db_datadict.sp_4_1_4;
685
# update information_schema as a limited user
689
use information_schema;
690
--source suite/funcs_1/include/show_connection.inc
692
#FIXME: check later the change from 1288 to 1044 (since Mid Sep05)
694
update user_privileges set privilege_type = 'insert' where is_grantable = 'yes';
697
--source suite/funcs_1/include/show_connection.inc
699
# ------------------------------------------------------------------------------
701
let $message= Testcase 3.2.1.5:;
702
--source include/show_msg80.inc
704
################################################################################
705
# Testcase 3.2.1.5: Ensure that no user may execute a DELETE statement on any
706
# INFORMATION_SCHEMA table.
707
################################################################################
709
use information_schema;
711
let $message= root: DELETE FROM any table in IS;
712
let $dd_part1= DELETE FROM;
714
--source suite/funcs_1/datadict/datadict_tables_error_1044.inc
716
#FIXME: check later the change from 1288 to 1044 (since Mid Sep05) in the whole next block
718
delete from schemata where schema_name = 'mysql';
720
delete from tables where table_name = 'abc';
724
delete from character_sets;
726
delete from collations;
728
delete from collation_character_set_applicability;
730
delete from routines;
732
delete from statistics;
736
delete from user_privileges;
738
delete from schema_privileges;
740
delete from table_privileges;
742
delete from column_privileges;
744
delete from table_constraints;
746
delete from key_column_usage;
748
# delete through a procedure
750
drop procedure if exists db_datadict.sp_4_1_5;
754
create procedure db_datadict.sp_4_1_5()
756
delete from information_schema.column_privileges;
761
call db_datadict.sp_4_1_5();
764
drop procedure db_datadict.sp_4_1_5;
766
# delete from information_schema as a limited user
770
use information_schema;
771
--source suite/funcs_1/include/show_connection.inc
773
#FIXME: check later the change from 1288 to 1044 (since Mid Sep05)
775
delete from tables where table_name = 'abc';
780
--source suite/funcs_1/include/show_connection.inc
783
DROP USER user_4_1_3@localhost;
784
# ------------------------------------------------------------------------------
786
let $message= Testcase 3.2.1.6:;
787
--source include/show_msg80.inc
789
################################################################################
790
# Testcase 3.2.1.6: Ensure that no user may create an INFORMATION_SCHEMA base
792
################################################################################
794
use information_schema;
796
let $message= root: create a table with a name of an IS table directly in IS;
797
let $dd_part1= CREATE TABLE;
798
let $dd_part2= ( c1 INT );
799
--source suite/funcs_1/datadict/datadict_tables_error_1044.inc
800
#FIXME solved - 3.2.1.6: check for better error message - 42S02: Unknown table '<xxx>' in <db> ?
802
create table t1 (f1 int, f2 int, f3 int);
806
#FIXME: check correct error message - HY000 Can't create/write to file '.\information_schema\columns.frm' (Errcode: 2)
807
let $message= root: create a table with a name of an IS table from other db;
808
let $dd_part1= CREATE TABLE information_schema.;
809
let $dd_part2= ( c1 INT );
810
--source suite/funcs_1/datadict/datadict_tables_error_1044.inc
811
#FIXME solved - 3.2.1.6: check for better error message - 42S02: Unknown table '<xxx>' in <db> ?
813
create table information_schema.t1 (f1 int, f2 int, f3 int);
815
# create a table in information_schema as a limited user with sufficient permissions
816
CREATE USER user_4_1_6@localhost;
818
grant all on *.* to user_4_1_6@localhost;
822
SHOW GRANTS FOR user_4_1_6@localhost;
824
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
825
connect (u2, localhost, user_4_1_6, , information_schema);
826
--source suite/funcs_1/include/show_connection.inc
828
use information_schema;
830
let $message= user: create a table with a name of an IS table directly in IS;
831
let $dd_part1= CREATE TABLE;
832
let $dd_part2= ( c1 INT );
833
--source suite/funcs_1/datadict/datadict_tables_error_1044.inc
834
#FIXME solved - 3.2.1.6: check for better error message - 42S02: Unknown table '<xxx>' in <db> ?
836
create table t1 (f1 int, f2 int, f3 int);
840
#FIXME 3.2.1.6: check correct error message - HY000 Can't create/write to file '.\information_schema\columns.frm' (Errcode: 2)
841
let $message= user: create a table with a name of an IS table from other db;
842
let $dd_part1= CREATE TABLE information_schema.;
843
let $dd_part2= ( c1 INT );
844
--source suite/funcs_1/datadict/datadict_tables_error_1044.inc
845
#FIXME solved - 3.2.1.6: check for better error message - 42S02: Unknown table '<xxx>' in <db> ?
847
create table information_schema.t1 (f1 int, f2 int, f3 int);
851
--source suite/funcs_1/include/show_connection.inc
853
DROP USER user_4_1_6@localhost;
854
# ------------------------------------------------------------------------------
856
let $message= Testcase 3.2.1.7:;
857
--source include/show_msg80.inc
859
################################################################################
860
# Testcase 3.2.1.7: Ensure that no user may create an INFORMATION_SCHEMA view.
861
################################################################################
863
use information_schema;
865
let $message= root: create a view with a name of an IS table directly in IS;
866
let $dd_part1= CREATE VIEW ;
867
let $dd_part2= AS SELECT * FROM mysql.time_zone;
868
#FIXME: check change from error 1 to 1044
869
--source suite/funcs_1/datadict/datadict_tables_error_1044.inc
872
CREATE VIEW v1 AS SELECT * FROM information_schema.schemata;
876
let $message= root: create a view with a name of an IS table from other db;
877
let $dd_part1= CREATE VIEW information_schema.;
878
let $dd_part2= AS SELECT * FROM mysql.time_zone;
879
#FIXME: check change from error 1 to 1044
880
--source suite/funcs_1/datadict/datadict_tables_error_1044.inc
882
# ... but creating a view that 'uses' the information_schema is allowed:
883
CREATE VIEW v1 AS SELECT * FROM information_schema.columns;
885
SELECT * FROM v1 LIMIT 5;
887
# create a view in information_schema as a limited user with sufficient permissions
888
CREATE USER user_4_1_7@localhost;
890
GRANT ALL ON db_datadict.* TO user_4_1_7@localhost;
891
#FIXME: check that GRANT ON i_s is no longer allowed
893
GRANT ALL ON information_schema.* TO user_4_1_7@localhost;
897
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
898
connect (u3, localhost, user_4_1_7, , db_datadict);
899
use information_schema;
900
--source suite/funcs_1/include/show_connection.inc
902
let $message= user: create a view with a name of an IS table directly in IS;
903
let $dd_part1= CREATE VIEW ;
904
let $dd_part2= AS SELECT * FROM db_datadict.v1;
905
#FIXME: check change from error 1 to 1044
906
--source suite/funcs_1/datadict/datadict_tables_error_1044.inc
908
create view v1 as select * from table_privileges;
912
let $message= user: create a view with a name of an IS table from other db;
913
let $dd_part1= CREATE VIEW information_schema.;
914
let $dd_part2= AS SELECT * FROM db_datadict.v1;
915
#FIXME: check change from error 1 to 1044
916
--source suite/funcs_1/datadict/datadict_tables_error_1044.inc
921
--source suite/funcs_1/include/show_connection.inc
922
DROP USER user_4_1_7@localhost;
923
DROP VIEW db_datadict.v1;
924
# ------------------------------------------------------------------------------
926
let $message= Testcase 3.2.1.8:;
927
--source include/show_msg80.inc
929
################################################################################
930
# Testcase 3.2.1.8: Ensure that no user may create an index on an
931
# INFORMATION_SCHEMA table.
932
################################################################################
934
use information_schema;
936
#FIXME: check later the change from 1288 to 1044 (since Mid Sep05)
938
create index i1 on schemata(schema_name);
940
create index i2 on tables(table_schema);
942
create index i3 on columns(table_name);
945
create index i4 on character_sets(character_set_name);
947
create index i5 on collations( collation_name);
949
create index i6 on collation_character_set_applicability(collation_name);
952
create index i7 on routines(routine_name);
954
create index i8 on statistics(table_schema);
956
create index i9 on views(table_schema);
959
create index i10 on user_privileges(privilege_type);
961
create index i11 on schema_privileges(table_schema);
963
create index i12 on table_privileges(able_schema);
966
create index i13 on column_privileges(table_name);
968
create index i14 on table_constraints(constraint_schema);
970
create index i15 on key_column_usage(constraint_schema);
972
create index i16 on triggers(trigger_name);
976
create index i15 on information_schema.key_column_usage(constraint_schema);
978
use information_schema;
980
# create an index on an information_schema table as a limited user with sufficient permissions
981
CREATE USER user_4_1_8@localhost;
983
grant select, index on *.* to user_4_1_8@localhost;
987
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
988
connect (u4, localhost, user_4_1_8, , test);
989
--source suite/funcs_1/include/show_connection.inc
991
use information_schema;
994
create index i1 on schemata(schema_name);
996
create index i2 on tables(table_schema);
998
create index i3 on columns(table_name);
1001
create index i4 on character_sets(character_set_name);
1003
create index i5 on collations( collation_name);
1005
create index i6 on collation_character_set_applicability(collation_name);
1008
create index i7 on routines(routine_name);
1010
create index i8 on statistics(table_schema);
1012
create index i9 on views(table_schema);
1015
create index i10 on user_privileges(privilege_type);
1017
create index i11 on schema_privileges(table_schema);
1019
create index i12 on table_privileges(able_schema);
1022
create index i13 on column_privileges(table_name);
1024
create index i14 on table_constraints(constraint_schema);
1026
create index i15 on key_column_usage(constraint_schema);
1028
create index i16 on triggers(trigger_name);
1032
create index i15 on information_schema.key_column_usage(constraint_schema);
1037
--source suite/funcs_1/include/show_connection.inc
1040
DROP USER user_4_1_8@localhost;
1041
# ------------------------------------------------------------------------------
1043
let $message= Testcase 3.2.1.9:;
1044
--source include/show_msg80.inc
1046
################################################################################
1047
# Testcase 3.2.1.9: Ensure that no user may alter the definition of an
1048
# INFORMATION_SCHEMA table.
1049
################################################################################
1051
#FIXME: 3.2.1.9 check for better error message than ERROR 42S02: Table 'information_schema.schemata' doesn't exist
1053
# first simple check all known - so we never forget one ...
1054
let $message= root: alter a table from other db;
1055
let $dd_part1= ALTER TABLE information_schema.;
1056
let $dd_part2= ADD f1 INT;
1057
#FIXME: check change from error 1146 to 1044
1058
--source suite/funcs_1/datadict/datadict_tables_error_1044.inc
1059
use information_schema;
1060
let $message= root: alter a table from directly;
1061
let $dd_part1= ALTER TABLE ;
1062
let $dd_part2= ADD f1 INT;
1063
#FIXME: check change from error 1146 to 1044
1064
--source suite/funcs_1/datadict/datadict_tables_error_1044.inc
1065
# now more detailed checks
1067
alter table schemata add f1 int;
1069
alter table tables drop primary key;
1071
alter table columns add f1 int;
1073
alter table character_sets disable keys;
1075
alter table collations enable keys;
1077
alter table collation_character_set_applicability add f1 int;
1079
alter table routines discard tablespace;
1081
alter table statistics import tablespace;
1083
alter table views drop column table_name;
1085
alter table user_privileges drop index privilege_type;
1087
alter table schema_privileges drop column is_grantable;
1089
alter table table_privileges order by constraint_type;
1091
alter table column_privileges rename to aaxyz;
1093
alter table table_constraints order by schema_name;
1095
alter table key_column_usage rename to information_schema.aabxyz;
1097
alter table triggers rename to information_schema.sql_mode;
1098
# Alter an information_schema table as a limited user with sufficient permissions
1100
CREATE USER user_4_1_9@localhost;
1102
grant select, alter, create, insert on *.* to user_4_1_9@localhost;
1106
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
1107
connect (u5, localhost, user_4_1_9, , db_datadict);
1108
--source suite/funcs_1/include/show_connection.inc
1112
let $message= user: alter a table from other db;
1113
let $dd_part1= ALTER TABLE information_schema.;
1114
let $dd_part2= ADD f1 INT;
1115
#FIXME: check change from error 1146 to 1044
1116
--source suite/funcs_1/datadict/datadict_tables_error_1044.inc
1117
use information_schema;
1118
let $message= user: alter a table from directly;
1119
let $dd_part1= ALTER TABLE ;
1120
let $dd_part2= ADD f1 INT;
1121
#FIXME: check change from error 1146 to 1044
1122
--source suite/funcs_1/datadict/datadict_tables_error_1044.inc
1127
--source suite/funcs_1/include/show_connection.inc
1128
DROP USER user_4_1_9@localhost;
1129
# ------------------------------------------------------------------------------
1131
let $message= Testcase 3.2.1.10:;
1132
--source include/show_msg80.inc
1134
################################################################################
1135
# Testcase 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
1136
################################################################################
1138
use information_schema;
1140
let $message= root: drop a table from IS;
1141
let $dd_part1= DROP TABLE ;
1143
#FIXME: check change from error 1051 to 1044
1144
--source suite/funcs_1/datadict/datadict_tables_error_1044.inc
1147
let $message= root: drop a table from other db;
1148
let $dd_part1= DROP TABLE information_schema.;
1150
#FIXME: check change from error 1051 to 1044
1151
--source suite/funcs_1/datadict/datadict_tables_error_1044.inc
1152
use information_schema;
1154
# drop an information_schema table as a limited user with sufficient permissions
1156
CREATE USER user_4_1_10@localhost;
1158
GRANT SELECT, DROP ON *.* TO user_4_1_10@localhost;
1162
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
1163
connect (u6,localhost,user_4_1_10,,db_datadict);
1165
use information_schema;
1166
--source suite/funcs_1/include/show_connection.inc
1168
let $message= user: drop a table from IS;
1169
let $dd_part1= DROP TABLE ;
1171
#FIXME: check change from error 1051 to 1044
1172
--source suite/funcs_1/datadict/datadict_tables_error_1044.inc
1175
let $message= user: drop a table from other db;
1176
let $dd_part1= DROP TABLE information_schema.;
1178
#FIXME: check change from error 1051 to 1044
1179
--source suite/funcs_1/datadict/datadict_tables_error_1044.inc
1183
--source suite/funcs_1/include/show_connection.inc
1186
DROP USER user_4_1_10@localhost;
1188
# Try to carry out information_schema modification operations with a user other than root having SUPER privileges
1189
CREATE USER user_4_1_11@localhost;
1191
GRANT SUPER ON *.* TO user_4_1_11@localhost;
1195
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
1196
connect (u7,localhost,user_4_1_11, ,test);
1198
use information_schema;
1199
--source suite/funcs_1/include/show_connection.inc
1202
drop table routines;
1205
alter table collations enable keys;
1208
create index i5 on collations( collation_name );
1211
create view v1 as select * from schemata;
1214
delete from columns;
1217
update columns set table_name = 't4' where column_name = 'f2';
1220
insert into collations ( collation_name, character_set_name, id, is_default,
1221
is_compiled, sortlen)
1222
values ('cp1251_bin', 'cp1251', 50, '', '', 0);
1227
--source suite/funcs_1/include/show_connection.inc
1228
DROP USER user_4_1_11@localhost;
1229
# ------------------------------------------------------------------------------
1231
let $message= Testcase 3.2.1.11:;
1232
--source include/show_msg80.inc
1234
################################################################################
1235
# Testcase 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table
1236
# to any other database.
1237
################################################################################
1240
DROP DATABASE IF EXISTS db_datadict;
1243
CREATE DATABASE db_datadict;
1244
CREATE USER 'u_6_401011'@'localhost';
1246
#FIXME: check that GRANT ON i_s is no longer allowed
1248
GRANT ALL ON information_schema.* TO 'u_6_401011'@'localhost';
1249
GRANT ALL ON db_datadict.* TO 'u_6_401011'@'localhost';
1254
ALTER TABLE information_schema.schemata RENAME db_datadict.schemata;
1256
let $message= root: move table to other DB;
1257
let $dd_part1= ALTER TABLE information_schema.;
1258
let $dd_part2= RENAME db_datadict.tb_01;
1259
#FIXME: check change from error 1146 to 1044
1260
--source suite/funcs_1/datadict/datadict_tables_error_1044.inc
1261
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
1262
connect (u_6_401011, localhost, u_6_401011, , db_datadict);
1264
USE information_schema;
1265
--source suite/funcs_1/include/show_connection.inc
1268
ALTER TABLE information_schema.schemata RENAME db_datadict.schemata;
1270
let $message= user: move table to other DB;
1271
let $dd_part1= ALTER TABLE information_schema.;
1272
let $dd_part2= RENAME db_datadict.tb_01;
1273
#FIXME: check change from error 1146 to 1044
1274
--source suite/funcs_1/datadict/datadict_tables_error_1044.inc
1275
disconnect u_6_401011;
1278
--source suite/funcs_1/include/show_connection.inc
1282
#DROP DATABASE db_datadict;
1283
DROP TABLE IF EXISTS db_datadict.schemata;
1284
DROP USER 'u_6_401011'@'localhost';
1286
# ------------------------------------------------------------------------------
1288
let $message= Testcase 3.2.1.12:;
1289
--source include/show_msg80.inc
1291
################################################################################
1292
# Testcase 3.2.1.12: Ensure that no user may directly add to, alter, or delete
1293
# any data in an INFORMATION_SCHEMA table.
1294
################################################################################
1296
# first as root a DELETE check on all tables
1297
let $message= root: delete from IS tables;
1298
let $dd_part1= DELETE FROM information_schema.;
1300
#FIXME: check change from error 1288 to 1044
1301
--source suite/funcs_1/datadict/datadict_tables_error_1044.inc
1302
# check UPDATE for all ...
1304
UPDATE information_schema.tables SET table_name = 't_4711';
1306
UPDATE information_schema.columns SET table_name = 't_4711';
1308
UPDATE information_schema.statistics SET table_name = 't_4711';
1310
UPDATE information_schema.views SET table_name = 't_4711';
1312
UPDATE information_schema.table_privileges SET table_name = 't_4711';
1314
UPDATE information_schema.column_privileges SET table_name = 't_4711';
1316
UPDATE information_schema.table_constraints SET table_name = 't_4711';
1318
UPDATE information_schema.key_column_usage SET table_name = 't_4711';
1320
UPDATE information_schema.schemata SET catalog_name = 't_4711';
1322
UPDATE information_schema.character_sets SET description = 't_4711';
1324
UPDATE information_schema.collations SET character_set_name = 't_4711';
1326
UPDATE information_schema.collation_character_set_applicability
1327
SET character_set_name = 't_4711';
1329
UPDATE information_schema.routines SET routine_type = 't_4711';
1331
UPDATE information_schema.user_privileges SET grantee = 't_4711';
1333
UPDATE information_schema.schema_privileges SET grantee = 't_4711';
1335
UPDATE information_schema.triggers SET sql_mode = 't_4711';
1337
CREATE USER 'u_6_401012'@'localhost';
1339
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
1340
connect (u_6_401012, localhost, u_6_401012, , test);
1342
use information_schema;
1345
insert into information_schema.schemata (catalog_name, schema_name,
1346
default_character_set_name, sql_path)
1347
values (null, information_schema1, utf16, null);
1348
#FIXME: check later the change from 1142 to 1044 (since Mid Sep05)
1350
alter table information_schema.schemata rename db_datadict1.schemata;
1351
#FIXME: check later the change from 1146 to 1044 (since Mid Sep05)
1353
alter table information_schema.tables drop column checksum;
1355
alter table information_schema.statistics modify packed int;
1357
alter table information_schema.routines modify created int not null;
1359
alter table information_schema.key_column_usage drop column ordinal_position;
1361
alter table information_schema.table_privileges
1362
change privilege_type rights_approved varchar(32);
1364
update columns set table_name = 't4' where column_name = 'f2';
1366
delete from information_schema.collations;
1368
disconnect u_6_401012;
1372
--source suite/funcs_1/include/show_connection.inc
1375
drop table if exists db_datadict1.schemata;
1376
DROP USER 'u_6_401012'@'localhost';
1378
#-------------------------------------------------------------------------------------------------
1380
let $message= Testcase 3.2.1.13:;
1381
--source include/show_msg80.inc
1383
################################################################################
1384
# Testcase 3.2.1.13: Ensure that the creation of any new database object
1385
# (e.g. table or column) automatically inserts all relevant
1386
# information on that object into every appropriate
1387
# INFORMATION_SCHEMA table.
1388
################################################################################
1390
use information_schema;
1392
let $dbname=db_datadict;
1393
let $message= first check status >before< creating the objects ...;
1394
--source suite/funcs_1/datadict/datadict_show_schema.inc
1397
DROP DATABASE IF EXISTS db_datadict;
1399
CREATE DATABASE db_datadict;
1402
eval create table res_t_401013(f1 char(10), f2 char(25), f3 int)
1403
engine = $ENGINE_TYPE;
1404
create view res_v_401013 as select * from res_t_401013;
1405
CREATE USER u_6_401013@localhost;
1406
create procedure sp_6_401013() select 'db_datadict';
1407
create function fn_6_401013() returns int return 0;
1408
create index i_6_401013 on res_t_401013(f3);
1410
use information_schema;
1412
let $message= now check whether all new objects exists in IS ...;
1413
--source suite/funcs_1/datadict/datadict_show_schema.inc
1417
drop index i_6_401013 on res_t_401013;
1418
drop table db_datadict.res_t_401013;
1419
drop view db_datadict.res_v_401013;
1420
DROP USER u_6_401013@localhost;
1421
drop procedure sp_6_401013;
1422
drop function fn_6_401013;
1423
drop database db_datadict;
1425
use information_schema;
1427
let $message= and now check whether all objects are removed from IS ...;
1428
--source suite/funcs_1/datadict/datadict_show_schema.inc
1429
# ------------------------------------------------------------------------------
1431
let $message= Testcase 3.2.1.14:;
1432
--source include/show_msg80.inc
1434
################################################################################
1435
# Testcase 3.2.1.14: Ensure that the alteration of any existing database object
1436
# automatically updates all relevant information on that
1437
# object in every appropriate INFORMATION_SCHEMA table.
1438
################################################################################
1441
DROP DATABASE IF EXISTS db_datadict;
1443
CREATE DATABASE db_datadict;
1446
create table res_t_401014(f1 char(10), f2 text(25), f3 int);
1447
create view res_v_401014 as select * from res_t_401014;
1448
create procedure sp_6_401014() select 'db_datadict';
1449
create function fn_6_401014() returns int return 0;
1451
# check current information in information_schema
1453
let $dbname=db_datadict;
1454
let $message= show existing objects >before< changing them ...;
1455
--source suite/funcs_1/datadict/datadict_show_schema.inc
1461
alter table res_t_401014 change f1 ff1 int;
1462
alter table res_t_401014 engine = innodb;
1463
alter table res_t_401014 change f3 f3_new bigint;
1464
alter view res_v_401014 as select ff1 from res_t_401014;
1465
alter procedure sp_6_401014 sql security invoker;
1466
alter function fn_6_401014 comment 'updated comments';
1467
alter database db_datadict character set utf8;
1469
# check updated information in information_schema
1470
let $message= now check whether the changes are visible in IS ...;
1471
--source suite/funcs_1/datadict/datadict_show_schema.inc
1477
drop table db_datadict.res_t_401014;
1478
drop view db_datadict.res_v_401014;
1479
drop procedure sp_6_401014;
1480
drop function fn_6_401014;
1481
drop database db_datadict;
1482
# ------------------------------------------------------------------------------
1484
let $message= Testcase 3.2.1.15:;
1485
--source include/show_msg80.inc
1487
################################################################################
1488
# Testcase 3.2.1.15: Ensure that the dropping of any existing database object
1489
# automatically deletes all relevant information on that
1490
# object from every appropriate INFORMATION_SCHEMA table.
1491
################################################################################
1494
DROP DATABASE IF EXISTS db_datadict;
1496
CREATE DATABASE db_datadict;
1499
create table res_t_401015(f1 char(10), f2 text(25), f3 int);
1500
create view res_v_401015 as select * from res_t_401015;
1501
CREATE USER u_6_401015@localhost;
1502
create procedure sp_6_401015() select 'test';
1503
create function fn_6_401015() returns int return 0;
1504
create index i_6_401015 on res_t_401015(f3);
1506
let $dbname=db_datadict;
1507
let $message= show existing objects >before< dropping them ...;
1508
--source suite/funcs_1/datadict/datadict_show_schema.inc
1511
drop index i_6_401015 on res_t_401015;
1512
drop table db_datadict.res_t_401015;
1513
drop view db_datadict.res_v_401015;
1514
DROP USER u_6_401015@localhost;
1515
drop procedure sp_6_401015;
1516
drop function fn_6_401015;
1517
#drop database db_datadict;
1519
let $message= now check they are really gone ...;
1520
--source suite/funcs_1/datadict/datadict_show_schema.inc
1521
# ------------------------------------------------------------------------------
1523
let $message= Testcase 3.2.1.16:;
1524
--source include/show_msg80.inc
1526
################################################################################
1527
# Testcase 3.2.1.16: Ensure that no user may use any INFORMATION_SCHEMA table to
1528
# determine any information on a database and/or its
1529
# structure unless authorized to get that information.
1530
################################################################################
1533
DROP DATABASE IF EXISTS db_datadict;
1535
CREATE DATABASE db_datadict;
1537
CREATE DATABASE db_hidden;
1539
CREATE TABLE tb_hidden ( c1 TEXT );
1543
CREATE TABLE res_t_401016(f1 char(10),f2 text(25),f3 int);
1544
CREATE TABLE res_t_401016_1(f1 char(10),f2 text(25),f3 int);
1546
CREATE USER 'u_6_401016'@'localhost';
1547
GRANT SELECT ON db_datadict.res_t_401016 TO 'u_6_401016'@'localhost';
1551
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
1552
connect (u_6_401016, localhost, u_6_401016, , test);
1554
USE information_schema;
1556
SELECT table_schema, table_name, engine
1558
# WHERE table_name LIKE 'res_t_401016%';
1561
SELECT * FROM schemata;
1563
disconnect u_6_401016;
1566
--source suite/funcs_1/include/show_connection.inc
1568
#FIXME: check that GRANT ON i_s is no longer allowed
1570
grant usage on information_schema.* to 'u_6_401016'@'localhost';
1573
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
1574
connect (u_6_401016_1, localhost, u_6_401016, , db_datadict);
1576
USE information_schema;
1578
SELECT * FROM schemata;
1580
disconnect u_6_401016_1;
1582
# all tables are checked again later with permission tests
1587
--source suite/funcs_1/include/show_connection.inc
1588
DROP USER 'u_6_401016'@'localhost';
1589
drop table res_t_401016;
1590
drop table res_t_401016_1;
1591
DROP DATABASE db_hidden;
1592
#drop database db_datadict;
1593
# ------------------------------------------------------------------------------
1595
let $message= Testcase 3.2.1.17:;
1596
--source include/show_msg80.inc
1598
################################################################################
1599
# Testcase 3.2.1.17: Ensure that the SELECT privilege is granted TO PUBLIC WITH
1600
# GRANT OPTION on every INFORMATION_SCHEMA table.
1601
################################################################################
1603
CREATE USER 'u_6_401017'@'localhost';
1605
#FIXME: check that GRANT ON i_s is no longer allowed
1607
grant select on information_schema.* to u_6_401017@localhost;
1611
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
1612
connect (u_6_401017, localhost, u_6_401017, , test);
1614
use information_schema;
1616
select * from collation_character_set_applicability;
1617
select * from schemata;
1618
select table_name from tables;
1619
--source suite/funcs_1/datadict/datadict_bug_12777.inc
1620
select table_name, column_name, column_type from columns;
1621
select character_set_name from character_sets;
1622
select collation_name from collations;
1623
select routine_name, routine_type from routines;
1624
select table_name, index_name from statistics;
1625
select table_name from views;
1626
select privilege_type from user_privileges;
1627
select grantee, privilege_type from schema_privileges;
1628
select * from table_privileges;
1629
select column_name, privilege_type from column_privileges;
1630
select table_name,constraint_type from table_constraints;
1631
select table_schema, table_name, column_name from key_column_usage;
1633
disconnect u_6_401017;
1637
--source suite/funcs_1/include/show_connection.inc
1638
DROP USER 'u_6_401017'@'localhost';
1639
# ------------------------------------------------------------------------------
1641
let $message= Testcase 3.2.1.18:;
1642
--source include/show_msg80.inc
1644
################################################################################
1645
# Testcase 3.2.1.18: Ensure that the CREATE VIEW privilege on an
1646
# INFORMATION_SCHEMA table may be granted to any user.
1647
################################################################################
1649
CREATE USER 'u_6_401018'@'localhost';
1651
#FIXME: check GRANT on IS
1653
GRANT CREATE VIEW ON information_schema.* TO 'u_6_401018'@'localhost';
1654
GRANT ALL ON db_datadict.* TO 'u_6_401018'@'localhost';
1656
SHOW GRANTS FOR 'u_6_401018'@'localhost';
1660
DROP DATABASE IF EXISTS db_datadict;
1662
CREATE DATABASE db_datadict;
1664
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
1665
connect (u_6_401018, localhost, u_6_401018, , test);
1669
create view db_datadict.v_401018 as
1670
select * from information_schema.schemata;
1671
SELECT * FROM v_401018 ORDER BY 2 DESC;
1673
disconnect u_6_401018;
1677
--source suite/funcs_1/include/show_connection.inc
1678
DROP USER 'u_6_401018'@'localhost';
1679
DROP DATABASE db_datadict;
1680
# ------------------------------------------------------------------------------
1682
let $message= Testcase 3.2.1.19:;
1683
--source include/show_msg80.inc
1685
################################################################################
1686
# Testcase 3.2.1.19: Ensure that no other privilege on an INFORMATION_SCHEMA
1687
# table is granted, or may be granted, to any user.
1688
################################################################################
1690
CREATE USER 'u_6_401019'@'localhost';
1692
#FIXME: check GRANT on IS
1694
grant alter on information_schema.* to 'u_6_401019'@'localhost';
1696
#FIXME: check GRANT on IS
1698
grant alter routine on information_schema.* to 'u_6_401019'@'localhost';
1700
#FIXME: check GRANT on IS
1702
grant create on information_schema.* to 'u_6_401019'@'localhost';
1704
#FIXME: check GRANT on IS
1706
grant create routine on information_schema.* to 'u_6_401019'@'localhost';
1708
#FIXME: check GRANT on IS
1710
grant create temporary tables
1711
on information_schema.* to 'u_6_401019'@'localhost';
1713
#FIXME: check GRANT on IS
1715
grant delete on information_schema.* to 'u_6_401019'@'localhost';
1717
#FIXME: check GRANT on IS
1719
grant drop on information_schema.* to 'u_6_401019'@'localhost';
1721
#FIXME: check GRANT on IS
1723
grant execute on information_schema.* to 'u_6_401019'@'localhost';
1725
#FIXME: check GRANT on IS
1727
grant index on information_schema.* to 'u_6_401019'@'localhost';
1729
#FIXME: check GRANT on IS
1731
grant insert on information_schema.* to 'u_6_401019'@'localhost';
1733
#FIXME: check GRANT on IS
1735
grant lock tables on information_schema.* to 'u_6_401019'@'localhost';
1737
#FIXME: check GRANT on IS
1739
grant update on information_schema.* to 'u_6_401019'@'localhost';
1741
SELECT * FROM information_schema.table_privileges
1742
WHERE table_schema = "information_schema";
1743
SELECT * FROM information_schema.column_privileges
1744
WHERE table_schema = "information_schema";
1747
DROP USER 'u_6_401019'@'localhost';
1748
# ------------------------------------------------------------------------------
1750
let $message= Testcase 3.2.1.20:;
1751
--source include/show_msg80.inc
1753
################################################################################
1754
# Testcase 3.2.1.20: Ensure that USE INFORMATION_SCHEMA allows the user to
1755
# switch to the INFORMATION_SCHEMA database, for query
1757
################################################################################
1759
CREATE USER 'u_6_401020'@'localhost';
1761
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
1762
connect (u_6_401020, localhost, u_6_401020, , test);
1764
USE information_schema;
1765
SELECT * FROM schemata;
1768
CREATE TABLE tb_not_allowed ( col TEXT );
1769
#FIXME 3.2.1.20: bad message: ERROR 42S02: Unknown table 'tb_not_allowed' in information_schema
1770
#FIXME 3.2.1.20: better: ERROR 42000: Access denied for user 'u_6_401020'@'localhost' to database 'information_schema'
1773
create view res_v1 as select * from information_schema.schemata;
1776
alter table schemata modify catalog_name varchar(255);
1779
update schemata set catalog_name = 'abc'
1780
where schema_name = 'information_schema';
1783
--error ER_BAD_DB_ERROR
1784
CREATE PROCEDURE sp_3_2_1_20()
1786
INSERT INTO information_schema.schema_privileges (table_schema,privilege_type)
1787
VALUES('db2','insert');
1792
DELETE FROM schemata WHERE schema_name = 'information_schema';
1794
disconnect u_6_401020;
1798
--source suite/funcs_1/include/show_connection.inc
1799
DROP USER 'u_6_401020'@'localhost';
1800
# ------------------------------------------------------------------------------
1802
let $message= Testcase 3.2.2.1:;
1803
--source include/show_msg80.inc
1805
################################################################################
1806
# Testcase 3.2.2.1: Ensure that the INFORMATION_SCHEMA.CHARACTER_SETS
1807
# table has the following columns, in the following order:
1809
# CHARACTER_SET_NAME (shows a character set name),
1810
# DEFAULT_COLLATE_NAME (shows the name of the default
1811
# collation for that character set),
1812
# DESCRIPTION (shows a descriptive name for that character
1814
# MAXLEN (shows the number of bytes used to store each
1815
# character supported by that character set).
1816
################################################################################
1818
let $is_table= character_sets;
1819
--source suite/funcs_1/datadict/datadict_show_table_design.inc
1820
# ------------------------------------------------------------------------------
1822
let $message= Testcase 3.2.2.2:;
1823
--source include/show_msg80.inc
1825
################################################################################
1826
# Testcase 3.2.2.2: Ensure that the table shows the relevant information on
1827
# every character set for which the current user or PUBLIC
1828
# have the USAGE privilege.
1829
################################################################################
1831
--source suite/funcs_1/include/show_connection.inc
1832
SELECT * FROM information_schema.character_sets;
1833
# ------------------------------------------------------------------------------
1835
let $message= Testcase 3.2.2.3:;
1836
--source include/show_msg80.inc
1838
################################################################################
1839
# Testcase 3.2.2.3: Ensure that the table shows the relevant information on
1840
# every character set for which the current user or PUBLIC
1841
# have the USAGE privilege.
1842
################################################################################
1844
# Test requirement is erroneous... we cannot grant / revoke privilege for using
1846
# ------------------------------------------------------------------------------
1848
let $message= Testcase 3.2.3.1:;
1849
--source include/show_msg80.inc
1851
################################################################################
1852
# Testcase 3.2.3.1: Ensure that the INFORMATION_SCHEMA.COLLATIONS
1853
# table has the following columns, in the following order:
1855
# COLLATION_NAME (shows a collation name),
1856
# CHARACTER_SET_NAME (shows the name of the character set to
1857
# which the collation applies),
1858
# ID (shows a numeric identifier for that collation/character
1860
# IS_DEFAULT (shows whether the collation is the default
1861
# collation for the character set shown),
1862
# IS_COMPILED (indicates whether the collation is compiled
1863
# into the MySQL server),
1864
# SORTLEN (shows a value related to the amount of memory
1865
# required to sort strings using this
1866
# collation/character set combination).
1867
################################################################################
1869
let $is_table= collations;
1870
--source suite/funcs_1/datadict/datadict_show_table_design.inc
1871
# -------------------------------------------------------------------------------------------------------
1873
let $message= Testcase 3.2.3.2:;
1874
--source include/show_msg80.inc
1876
################################################################################
1877
# Testcase 3.2.3.2: Ensure that the table shows the relevant information on
1878
# every collation for which the current user or PUBLIC have
1879
# the USAGE privilege.
1880
################################################################################
1882
SELECT * FROM collations;
1883
# -------------------------------------------------------------------------------------------------------
1885
let $message= Testcase 3.2.3.3:;
1886
--source include/show_msg80.inc
1888
###############################################################################
1889
# Testcase 3.2.3.3: Ensure that the table does not show any information on any
1890
# collations for which the current user and PUBLIC have no
1892
################################################################################
1894
# Test requirement is erroneous... we cannot grant / revoke privilege for using
1896
# -------------------------------------------------------------------------------------------------------
1898
let $message= Testcase 3.2.4.1:;
1899
--source include/show_msg80.inc
1901
################################################################################
1902
# Testcase 3.2.4.1: Ensure that the
1903
# INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
1904
# table has the following columns, in the following order:
1906
# COLLATION_NAME (shows the name of a collation),
1907
# CHARACTER_SET_NAME (shows the name of a character set to
1908
# which that collation applies).
1909
################################################################################
1911
let $is_table= collation_character_set_applicability;
1912
--source suite/funcs_1/datadict/datadict_show_table_design.inc
1913
# -------------------------------------------------------------------------------------------------------
1915
let $message= Testcase 3.2.4.2:;
1916
--source include/show_msg80.inc
1918
################################################################################
1919
# Testcase 3.2.4.2: Ensure that the table shows the relevant information on
1920
# every collation/character set combination for which the
1921
# current user or PUBLIC have the USAGE privilege.
1922
################################################################################
1924
SELECT * FROM collation_character_set_applicability;
1925
# -------------------------------------------------------------------------------------------------------
1927
let $message= Testcase 3.2.4.3:;
1928
--source include/show_msg80.inc
1930
################################################################################
1931
# Testcase 3.2.4.3: Ensure that the table does not show any information on any
1932
# collation/character set combinations for which the current
1933
# user and PUBLIC have no USAGE privilege.
1934
################################################################################
1936
# Test requirement is erroneous... we cannot grant / revoke privilege for using a collation.
1937
# -------------------------------------------------------------------------------------------------------
1939
let $message= Testcase 3.2.5.1:;
1940
--source include/show_msg80.inc
1942
################################################################################
1943
# Testcase 3.2.5.1: Ensure that the INFORMATION_SCHEMA.COLUMN_PRIVILEGES
1944
# table has the following columns, in the following order:
1946
# GRANTEE (shows the name of a user who has either granted,
1947
# or been granted a column privilege),
1948
# TABLE_CATALOG (always shows NULL),
1949
# TABLE_SCHEMA (shows the name of the schema, or database,
1950
# in which the table for which a column privilege has
1951
# been granted resides),
1952
# TABLE_NAME (shows the name of the table),
1953
# COLUMN_NAME (shows the name of the column on which a
1954
# column privilege has been granted),
1955
# PRIVILEGE_TYPE (shows the type of privilege that was
1956
# granted; must be either SELECT, INSERT, UPDATE, or
1958
# IS_GRANTABLE (shows whether that privilege was granted
1959
# WITH GRANT OPTION).
1960
################################################################################
1962
let $is_table= column_privileges;
1963
--source suite/funcs_1/datadict/datadict_show_table_design.inc
1964
# -------------------------------------------------------------------------------------------------------
1966
let $message= Testcase 3.2.5.2 + 3.2.5.3 + 3.2.5.4:;
1967
--source include/show_msg80.inc
1969
################################################################################
1970
# Testcase 3.2.5.2: Ensure that the table shows the relevant information on
1971
# every column privilege which has been granted to the
1972
# current user or PUBLIC, or which was granted by the current
1974
################################################################################
1975
# Testcase 3.2.5.3: Ensure that the table does not show any information on any
1976
# column privilege which was granted to any user other than
1977
# the current user or PUBLIC, or which was granted by any
1978
# user other than the current user.
1979
################################################################################
1980
# Testcase 3.2.5.4: Ensure that the table does not show any information on any
1981
# privileges that are not column privileges for the current
1983
################################################################################
1985
CREATE USER 'user_1'@'localhost';
1986
CREATE USER 'user_2'@'localhost';
1987
CREATE USER 'user_3'@'localhost';
1990
DROP DATABASE IF EXISTS db_datadict;
1992
CREATE DATABASE db_datadict;
1995
CREATE TABLE db_datadict.res_t40502 (f1 INT, f2 DECIMAL, f3 TEXT);
1997
GRANT SELECT(f1, f3) ON db_datadict.res_t40502 TO 'user_1'@'localhost';
1998
GRANT INSERT(f1) ON db_datadict.res_t40502 TO 'user_1'@'localhost';
1999
GRANT UPDATE(f2) ON db_datadict.res_t40502 TO 'user_1'@'localhost';
2000
GRANT SELECT(f2) ON db_datadict.res_t40502 TO 'user_2'@'localhost';
2001
GRANT INSERT, SELECT ON db_datadict.res_t40502 TO 'user_3'@'localhost';
2002
GRANT SELECT(f3) ON db_datadict.res_t40502 TO 'user_3'@'localhost';
2004
GRANT INSERT, SELECT ON db_datadict.res_t40502 TO 'user_3'@'localhost' WITH GRANT OPTION;
2005
GRANT ALL ON db_datadict.* TO 'user_3'@'localhost';
2007
let $select= SELECT * FROM information_schema.column_privileges
2008
WHERE grantee LIKE "'user%"
2009
ORDER BY grantee, table_name, column_name, privilege_type;
2012
let $message= FIXME: Check it is correct that the following GRANT changes ALL privs that user_1 has;
2013
--source include/show_msg.inc
2015
GRANT UPDATE(f3) ON db_datadict.res_t40502 TO 'user_1'@'localhost' WITH GRANT OPTION;
2021
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
2022
connect (user_5_1, localhost, user_1, , db_datadict);
2025
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
2026
connect (user_5_2, localhost, user_2, , db_datadict);
2029
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
2030
connect (user_5_3, localhost, user_3, , db_datadict);
2032
let $message= FIXME: check it is correct that granted TABLES doesn_t occur in COLUMN_PRIVILEGES;
2033
--source include/show_msg.inc
2034
SELECT * FROM information_schema.table_privileges WHERE grantee LIKE "'user%";
2035
SELECT * FROM information_schema.schema_privileges WHERE grantee LIKE "'user%";
2037
GRANT SELECT(f1, f3) ON db_datadict.res_t40502 TO 'user_2'@'localhost';
2039
let $message= FIXME: check whether it is intended that *my* grants to others are *NOT* shown here;
2040
--source include/show_msg.inc
2043
connection user_5_2;
2044
--source suite/funcs_1/include/show_connection.inc
2047
disconnect user_5_1;
2048
disconnect user_5_2;
2049
disconnect user_5_3;
2053
--source suite/funcs_1/include/show_connection.inc
2055
DROP TABLE IF EXISTS db_datadict.res_t40502;
2056
DROP DATABASE IF EXISTS db_datadict;
2059
DROP USER 'user_1'@'localhost';
2060
DROP USER 'user_2'@'localhost';
2061
DROP USER 'user_3'@'localhost';
2062
# -------------------------------------------------------------------------------------------------------
2064
let $message= Testcase 3.2.6.1:;
2065
--source include/show_msg80.inc
2067
################################################################################
2068
# Testcase 3.2.6.1: Ensure that the INFORMATION_SCHEMA.COLUMNS table has the
2069
# following columns, in the following order:
2071
# TABLE_CATALOG (always shows NULL),
2072
# TABLE_SCHEMA (shows the name of the database, or schema,
2073
# in which an accessible table resides),
2074
# TABLE_NAME (shows the name of an accessible table),
2075
# COLUMN_NAME (shows the name of a column within that
2077
# ORDINAL_POSITION (shows the ordinal position of that
2078
# column in that table),
2079
# COLUMN_DEFAULT (shows the column's default value),
2080
# IS_NULLABLE (shows whether the column may accept NULL
2082
# DATA_TYPE (shows the column's defined data type; keyword
2084
# CHARACTER_MAXIMUM_LENGTH (shows, for a string column, the
2085
# column's defined maximum length in characters;
2087
# CHARACTER_OCTET_LENGTH (shows, for a string column, the
2088
# column's defined maximum length in octets;
2090
# NUMERIC_PRECISION (shows, for a numeric column, the
2091
# column's or data type's defined precision;
2093
# NUMERIC_SCALE (shows, for a numeric column, the column's
2094
# or data type's defined scale; otherwise NULL),
2095
# CHARACTER_SET_NAME (shows, for a character string column,
2096
# the column's default character set; otherwise NULL),
2097
# COLLATION_NAME (shows, for a character string column, the
2098
# column's default collation; otherwise NULL),
2099
# COLUMN_TYPE (shows the column's complete, defined data
2101
# COLUMN_KEY (shows whether the column is indexed; possible
2102
# values are PRI if the column is part of a PRIMARY
2103
# KEY, UNI if the column is part of a UNIQUE key, MUL
2104
# if the column is part of an index key that allows
2106
# EXTRA (shows any additional column definition information,
2107
# e.g. whether the column was defined with the
2108
# AUTO_INCREMENT attribute),
2109
# PRIVILEGES (shows the privileges available to the user on
2111
# COLUMN_COMMENT (shows the comment, if any, defined for the
2112
# comment; otherwise NULL).
2113
################################################################################
2115
let $is_table= columns;
2116
--source suite/funcs_1/datadict/datadict_show_table_design.inc
2117
# -------------------------------------------------------------------------------------------------------
2119
let $message= Testcase 3.2.6.2 + 3.2.6.3:;
2120
--source include/show_msg80.inc
2122
################################################################################
2123
# Testcase 3.2.6.2: Ensure that the table shows the relevant information on the
2124
# columns of every table that is accessible to the current
2125
# user or to PUBLIC.
2126
################################################################################
2127
# Testcase 3.2.6.3: Ensure that the table does not show any information on the
2128
# columns of any table which is not accessible to the current
2130
################################################################################
2133
DROP DATABASE IF EXISTS db_datadict;
2135
CREATE DATABASE db_datadict;
2137
CREATE USER 'user_1'@'localhost';
2138
CREATE USER 'user_2'@'localhost';
2142
create table t_6_406001(f1 char(10), f2 text, f3 date, f4 int);
2143
grant select(f1, f2) on db_datadict.t_6_406001 to 'user_1'@'localhost';
2145
create table t_6_406002(f1 char(10), f2 text, f3 date, f4 int);
2146
GRANT INSERT(f1, f2) ON db_datadict.t_6_406002 TO 'user_2'@'localhost';
2150
let $select= SELECT * FROM information_schema.columns
2151
ORDER BY table_schema, table_name, ordinal_position;
2153
# show view of user root
2154
--source suite/funcs_1/datadict/datadict_bug_12777.inc
2157
# reconnect to mysql with user credential of user u_6_406002_1.
2158
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
2159
connect (user_6_1, localhost, user_1, , db_datadict);
2160
--source suite/funcs_1/datadict/datadict_bug_12777.inc
2163
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
2164
connect (user_6_2, localhost, user_2, , db_datadict);
2165
--source suite/funcs_1/datadict/datadict_bug_12777.inc
2168
disconnect user_6_1;
2169
disconnect user_6_2;
2172
--source suite/funcs_1/include/show_connection.inc
2174
let $message= Show the quotient of COL and CML for all COLUMNS;
2175
--source include/show_msg.inc
2177
CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
2181
FROM information_schema.columns
2182
WHERE CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH = 1
2183
ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML;
2185
#FIXME 3.2.6.2: check the value 2.0079 tinytext ucs2 ucs2_general_ci
2187
CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
2191
FROM information_schema.columns
2192
WHERE CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH <> 1
2193
ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML;
2196
CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
2200
FROM information_schema.columns
2201
WHERE CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH IS NULL
2202
ORDER BY CHARACTER_SET_NAME, COLLATION_NAME, COL_CML;
2204
echo --> CHAR(0) is allowed (see manual), and here both CHARACHTER_* values;
2205
echo --> are 0, which is intended behavior, and the result of 0 / 0 IS NULL;
2206
--source suite/funcs_1/datadict/datadict_bug_12777.inc
2207
SELECT CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH AS COL_CML,
2212
CHARACTER_MAXIMUM_LENGTH,
2213
CHARACTER_OCTET_LENGTH,
2217
FROM information_schema.columns
2218
ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;
2221
DROP USER 'user_1'@'localhost';
2222
DROP USER 'user_2'@'localhost';
2223
DROP TABLE IF EXISTS t_6_406001;
2224
DROP TABLE IF EXISTS t_6_406002;
2225
DROP DATABASE IF EXISTS db_datadict;
2226
# -------------------------------------------------------------------------------------------------------
2228
let $message= Testcase 3.2.7.1:;
2229
--source include/show_msg80.inc
2231
################################################################################
2232
# Testcase 3.2.7.1: Ensure that the INFORMATION_SCHEMA.KEY_COLUMN_USAGE
2233
# table has the following columns, in the following order:
2235
# CONSTRAINT_CATALOG (always shows NULL),
2236
# CONSTRAINT_SCHEMA (shows the database, or schema, in which
2237
# an accessible constraint, or index, resides),
2238
# CONSTRAINT_NAME (shows the name of the accessible
2240
# TABLE_CATALOG (always shows NULL),
2241
# TABLE_SCHEMA (shows the database, or schema, in which the
2242
# table constrained by that constraint resides),
2243
# TABLE_NAME (shows the name of the table constrained by the
2245
# COLUMN_NAME (shows the name of a column that is the index
2246
# key, or part of the index key),
2247
# ORDINAL_POSITION (shows the ordinal position of the column
2248
# within the constraint index),
2249
# POSITION_IN_UNIQUE_CONSTRAINT (shows, for a foreign key
2250
# column, the ordinal position of the referenced
2251
# column within the referenced unique index;
2253
# added with 5.0.6: REFERENCED_TABLE_SCHEMA,
2254
# REFERENCED_TABLE_NAME,
2255
# REFERENCED_COLUMN_NAME
2256
################################################################################
2258
let $is_table= key_column_usage;
2259
--source suite/funcs_1/datadict/datadict_show_table_design.inc
2260
# -------------------------------------------------------------------------------------------------------
2262
let $message= Testcase 3.2.7.2 + 3.2.7.3:;
2263
--source include/show_msg80.inc
2265
################################################################################
2266
# Testcase 3.2.7.2: Ensure that the table shows the relevant information on
2267
# every column, defined to be part of an index key, which is
2268
# accessible to the current user or to PUBLIC.
2269
################################################################################
2270
# Testcase 3.2.7.3: Ensure that the table does not show any information on any
2271
# indexed column that is not accessible to the current user
2273
################################################################################
2276
DROP DATABASE IF EXISTS db_datadict;
2278
CREATE DATABASE db_datadict;
2280
CREATE USER 'user_1'@'localhost';
2281
CREATE USER 'user_2'@'localhost';
2285
CREATE TABLE t_40701 (
2286
f1 INT NOT NULL, PRIMARY KEY(f1),
2287
f2 INT, INDEX f2_ind(f2)
2289
GRANT SELECT ON t_40701 to 'user_1'@'localhost';
2291
CREATE TABLE t_40702 (
2292
f1 INT NOT NULL, PRIMARY KEY(f1),
2293
f2 INT, INDEX f2_ind(f2)
2295
GRANT SELECT ON t_40702 to 'user_2'@'localhost';
2296
#FIXME: add foreign keys
2300
let $select= SELECT * FROM information_schema.key_column_usage
2301
ORDER BY constraint_catalog, constraint_schema, constraint_name,
2302
table_catalog, table_schema, table_name, ordinal_position;
2304
# show view of user root
2307
# reconnect to mysql with user credential of user u_6_406002_1.
2308
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
2309
connect (user_7_1, localhost, user_1, , db_datadict);
2312
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
2313
connect (user_7_2, localhost, user_2, , db_datadict);
2316
disconnect user_7_1;
2317
disconnect user_7_2;
2321
--source suite/funcs_1/include/show_connection.inc
2322
DROP USER 'user_1'@'localhost';
2323
DROP USER 'user_2'@'localhost';
2326
DROP DATABASE IF EXISTS db_datadict;
2327
# -------------------------------------------------------------------------------------------------------
2329
let $message= Testcase 3.2.8.1:;
2330
--source include/show_msg80.inc
2332
################################################################################
2333
# Testcase 3.2.8.1: Ensure that the INFORMATION_SCHEMA.ROUTINES
2334
# table has the following columns, in the following order:
2336
# SPECIFIC_NAME (shows the name of an accessible stored
2337
# procedure, or routine),
2338
# ROUTINE_CATALOG (always shows NULL),
2339
# ROUTINE_SCHEMA (shows the database, or schema, in which
2340
# the routine resides),
2341
# ROUTINE_NAME (shows the same stored procedure name),
2342
# ROUTINE_TYPE (shows whether the stored procedure is a
2343
# procedure or a function),
2344
# DTD_IDENTIFIER (shows, for a function, the complete
2345
# data type definition of the value the function will
2346
# return; otherwise NULL),
2347
# ROUTINE_BODY (shows the language in which the stored
2348
# procedure is written; currently always SQL),
2349
# ROUTINE_DEFINITION (shows as much of the routine body as
2350
# is possible in the allotted space),
2351
# EXTERNAL_NAME (always shows NULL),
2352
# EXTERNAL_LANGUAGE (always shows NULL),
2353
# PARAMETER_STYLE (shows the routine's parameter style;
2355
# IS_DETERMINISTIC (shows whether the routine is
2357
# SQL_DATA_ACCESS (shows the routine's defined
2358
# sql-data-access clause value),
2359
# SQL_PATH (always shows NULL),
2360
# SECURITY_TYPE (shows whether the routine's defined
2361
# security_type is 'definer' or 'invoker'),
2362
# CREATED (shows the timestamp of the time the routine was
2364
# LAST_ALTERED (shows the timestamp of the time the routine
2365
# was last altered),
2366
# SQL_MODE (shows the sql_mode setting at the time the
2367
# routine was created),
2368
# ROUTINE_COMMENT (shows the comment, if any, defined for
2369
# the routine; otherwise NULL),
2370
# DEFINER (shows the user who created the routine).
2371
################################################################################
2373
let $is_table= routines;
2374
--source suite/funcs_1/datadict/datadict_show_table_design.inc
2375
# -------------------------------------------------------------------------------------------------------
2377
let $message= Testcase 3.2.8.2 + 3.2.8.3:;
2378
--source include/show_msg80.inc
2380
################################################################################
2381
# Testcase 3.2.8.2: Ensure that the table shows the relevant information on
2382
# every SQL-invoked routine (i.e. stored procedure) which is
2383
# accessible to the current user or to PUBLIC.
2384
################################################################################
2385
# Testcase 3.2.8.3: Ensure that the table does not show any information on any
2386
# stored procedure that is not accessible to the current user
2388
##############################################################################
2391
DROP DATABASE IF EXISTS db_datadict;
2392
DROP DATABASE IF EXISTS db_datadict_2;
2395
CREATE DATABASE db_datadict;
2398
CREATE USER 'user_1'@'localhost';
2399
CREATE USER 'user_2'@'localhost';
2400
CREATE USER 'user_3'@'localhost';
2402
CREATE TABLE res_6_408002_1(f1 CHAR(3), f2 TEXT(25), f3 DATE, f4 INT);
2404
INSERT INTO res_6_408002_1(f1, f2, f3, f4)
2405
VALUES('abc', 'xyz', '1989-11-09', 0815);
2408
DROP PROCEDURE IF EXISTS sp_6_408002_1;
2412
CREATE PROCEDURE sp_6_408002_1()
2414
SELECT * FROM db_datadict.res_6_408002_1;
2418
CREATE DATABASE db_datadict_2;
2421
CREATE TABLE res_6_408002_2(f1 CHAR(3), f2 TEXT(25), f3 DATE, f4 INT);
2423
INSERT INTO res_6_408002_2(f1, f2, f3, f4)
2424
VALUES('abc', 'xyz', '1990-10-03', 4711);
2427
DROP PROCEDURE IF EXISTS sp_6_408002_2;
2431
CREATE PROCEDURE sp_6_408002_2()
2433
SELECT * FROM db_datadict_2.res_6_408002_2;
2437
GRANT SELECT ON db_datadict_2.* TO 'user_1'@'localhost';
2438
GRANT EXECUTE ON db_datadict_2.* TO 'user_1'@'localhost';
2440
GRANT EXECUTE ON db_datadict.* TO 'user_1'@'localhost';
2441
GRANT SELECT ON db_datadict.* TO 'user_2'@'localhost';
2443
GRANT EXECUTE ON PROCEDURE db_datadict_2.sp_6_408002_2 TO 'user_2'@'localhost';
2444
GRANT EXECUTE ON db_datadict_2.* TO 'user_2'@'localhost';
2447
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
2448
connect (user_8_1, localhost, user_1, , db_datadict);
2450
--replace_column 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss"
2451
SELECT * FROM information_schema.routines;
2452
disconnect user_8_1;
2454
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
2455
connect (user_8_2, localhost, user_2, , db_datadict);
2457
--replace_column 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss"
2458
SELECT * FROM information_schema.routines;
2459
disconnect user_8_2;
2461
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
2462
connect (user_8_3, localhost, user_3, , test);
2464
--replace_column 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss"
2465
SELECT * FROM information_schema.routines;
2466
disconnect user_8_3;
2470
--source suite/funcs_1/include/show_connection.inc
2471
DROP USER 'user_1'@'localhost';
2472
DROP USER 'user_2'@'localhost';
2473
DROP USER 'user_3'@'localhost';
2476
DROP TABLE res_6_408002_1;
2477
DROP PROCEDURE sp_6_408002_1;
2480
DROP TABLE res_6_408002_2;
2481
DROP PROCEDURE sp_6_408002_2;
2484
DROP DATABASE db_datadict;
2485
DROP DATABASE db_datadict_2;
2486
# -------------------------------------------------------------------------------------------------------
2488
let $message= Testcase 3.2.8.4:;
2489
--source include/show_msg80.inc
2491
################################################################################
2492
# Testcase 3.2.8.4: Ensure that a stored procedure with a routine body that is
2493
# too large to fit into the
2494
# INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION column
2495
# correctly shows as much of the information as is possible
2496
# within the allotted size.
2497
################################################################################
2500
DROP DATABASE IF EXISTS db_datadict;
2503
CREATE DATABASE db_datadict;
2506
create table res_6_408004_1(f1 longtext , f2 mediumint , f3 longblob , f4 real , f5 year);
2508
insert into res_6_408004_1 values ('abc', 98765 , 99999999 , 98765, 10);
2511
drop procedure if exists sp_6_408004;
2514
create table res_6_408004_2(f1 longtext , f2 mediumint , f3 longblob , f4 real , f5 year);
2516
insert into res_6_408004_2 values ('abc', 98765 , 99999999 , 98765, 10);
2518
let $message= Checking the max. possible length of (currently) 4 GByte is not possible in this environment here.;
2519
--source include/show_msg.inc
2522
create procedure sp_6_408004 ()
2524
declare done integer default 0;
2525
declare variable_number_1 longtext;
2526
declare variable_number_2 mediumint;
2527
declare variable_number_3 longblob;
2528
declare variable_number_4 real;
2529
declare variable_number_5 year;
2530
declare cursor_number_1 cursor for select * from res_6_408004_1 limit 0, 10;
2531
declare cursor_number_2 cursor for select * from res_6_408004_1 limit 0, 10;
2532
declare cursor_number_3 cursor for select * from res_6_408004_1 limit 0, 10;
2533
declare cursor_number_4 cursor for select * from res_6_408004_1 limit 0, 10;
2534
declare cursor_number_5 cursor for select * from res_6_408004_1 limit 0, 10;
2535
declare continue handler for sqlstate '02000' set done = 1;
2537
open cursor_number_1;
2539
fetch cursor_number_1 into variable_number_1, variable_number_2, variable_number_3, variable_number_4, variable_number_5;
2541
insert into res_6_408004_2 values (variable_number_1, variable_number_2, variable_number_3,
2542
variable_number_4, variable_number_5);
2548
open cursor_number_2;
2550
fetch cursor_number_2 into variable_number_1, variable_number_2, variable_number_3, variable_number_4, variable_number_5;
2552
insert into res_6_408004_2 values(variable_number_1, variable_number_2, variable_number_3, variable_number_4, variable_number_5);
2557
open cursor_number_3;
2559
fetch cursor_number_3 into variable_number_1, variable_number_2, variable_number_3, variable_number_4, variable_number_5;
2561
insert into res_6_408004_2 values(variable_number_1, variable_number_2, variable_number_3, variable_number_4, variable_number_5);
2568
open cursor_number_4;
2570
fetch cursor_number_4 into variable_number_1, variable_number_2, variable_number_3, variable_number_4, variable_number_5;
2572
insert into res_6_408004_2 values (variable_number_1, variable_number_2, variable_number_3, variable_number_4, variable_number_5);
2584
open cursor_number_5;
2586
fetch cursor_number_5 into variable_number_1, variable_number_2, variable_number_3, variable_number_4, variable_number_5;
2588
insert into res_6_408004_2 values (variable_number_1, variable_number_2, variable_number_3, variable_number_4, variable_number_5);
2601
call sp_6_408004 ();
2602
select * from res_6_408004_2;
2605
--replace_column 16 "YYYY-MM-DD hh:mm:ss" 17 "YYYY-MM-DD hh:mm:ss"
2606
SELECT *, LENGTH(routine_definition)
2607
FROM information_schema.routines
2608
WHERE routine_schema = 'db_datadict';
2609
--horizontal_results
2613
drop procedure sp_6_408004;
2614
drop table res_6_408004_1;
2615
drop table res_6_408004_2;
2617
drop database db_datadict;
2618
# -------------------------------------------------------------------------------------------------------
2620
let $message= Testcase 3.2.9.1:;
2621
--source include/show_msg80.inc
2623
################################################################################
2624
# Testcase 3.2.9.1: Ensure that the INFORMATION_SCHEMA.SCHEMATA
2625
# table has the following columns, in the following order:
2627
# CATALOG_NAME (always shows NULL),
2628
# SCHEMA_NAME (shows the name of a database, or schema, on
2629
# which the current user or PUBLIC has privileges),
2630
# DEFAULT_CHARACTER_SET_NAME (shows the name of that
2631
# database's default character set),
2632
# DEFAULT_COLLATION_NAME (shows the database default
2634
# SQL_PATH (always shows NULL).
2635
################################################################################
2637
let $is_table= schemata;
2638
--source suite/funcs_1/datadict/datadict_show_table_design.inc
2639
# -------------------------------------------------------------------------------------------------------
2641
let $message= Testcase 3.2.9.2 + 3.2.9.3:;
2642
--source include/show_msg80.inc
2644
################################################################################
2645
# Testcase 3.2.9.2: Ensure that the table shows the relevant information for
2646
# every database on which the current user or PUBLIC have
2648
################################################################################
2649
# Testcase 3.2.9.3: Ensure that the table does not show any information on any
2650
# databases on which the current user and PUBLIC have no
2652
################################################################################
2654
CREATE USER 'user_1'@'localhost';
2655
CREATE USER 'user_2'@'localhost';
2656
CREATE USER 'user_3'@'localhost';
2659
DROP DATABASE IF EXISTS db_datadict_1;
2660
DROP DATABASE IF EXISTS db_datadict_2;
2663
CREATE DATABASE db_datadict_1;
2664
CREATE DATABASE db_datadict_2;
2666
GRANT SELECT ON db_datadict_1.* to 'user_1'@'localhost';
2667
GRANT SELECT ON db_datadict_2.* to 'user_2'@'localhost';
2672
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
2673
connect (user_9_1, localhost, user_1, , db_datadict_1);
2675
SELECT COUNT(*) FROM information_schema.schemata;
2676
SELECT * FROM information_schema.schemata;
2677
disconnect user_9_1;
2681
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
2682
connect (user_9_2, localhost, user_2, , db_datadict_2);
2684
SELECT COUNT(*) FROM information_schema.schemata;
2685
SELECT * FROM information_schema.schemata;
2686
disconnect user_9_2;
2689
# shows neither db_1 nor db_2
2690
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
2691
connect (user_9_3, localhost, user_3, , test);
2693
SELECT COUNT(*) FROM information_schema.schemata;
2694
SELECT * FROM information_schema.schemata;
2695
disconnect user_9_3;
2700
--source suite/funcs_1/include/show_connection.inc
2701
DROP USER 'user_1'@'localhost';
2702
DROP USER 'user_2'@'localhost';
2703
DROP USER 'user_3'@'localhost';
2704
DROP DATABASE db_datadict_1;
2705
DROP DATABASE db_datadict_2;
2706
# -------------------------------------------------------------------------------------------------------
2708
let $message= Testcase 3.2.10.1:;
2709
--source include/show_msg80.inc
2711
################################################################################
2712
# Testcase 3.2.10.1: Ensure that the INFORMATION_SCHEMA.TABLE_CONSTRAINTS
2713
# table has the following columns, in the following order:
2715
# CONSTRAINT_CATALOG (always shows NULL),
2716
# CONSTRAINT_SCHEMA (shows the database, or schema, in which
2717
# a constraint an accessible table resides),
2718
# CONSTRAINT_NAME (shows the name of a constraint defined on
2719
# an accessible table),
2720
# TABLE_SCHEMA (shows the database, or schema, in which the
2722
# TABLE_NAME (shows the name of the table),
2723
# CONSTRAINT_TYPE (shows the type of the constraint; either
2724
# 'primary key', 'foreign key', 'unique', 'check').
2725
################################################################################
2727
let $is_table= table_constraints;
2728
--source suite/funcs_1/datadict/datadict_show_table_design.inc
2729
# -------------------------------------------------------------------------------------------------------
2731
let $message= Testcase 3.2.10.2 + 3.2.10.3:;
2732
--source include/show_msg80.inc
2734
################################################################################
2735
# Testcase 3.2.10.2: Ensure that the table shows the relevant information on all
2736
# constraints defined on every table for which the current
2737
# user or PUBLIC have privileges.
2738
################################################################################
2739
# Testcase 3.2.10.3: Ensure that the table does not show any information on
2740
# constraints defined on any table for which the current user
2741
# and PUBLIC have no privileges.
2742
################################################################################
2744
CREATE USER 'user_1'@'localhost';
2745
CREATE USER 'user_2'@'localhost';
2748
DROP DATABASE IF EXISTS db_datadict;
2749
DROP DATABASE IF EXISTS db_datadict_2;
2752
CREATE DATABASE db_datadict;
2753
CREATE DATABASE db_datadict_2;
2757
CREATE TABLE res_6_401003_1(f1 INT NOT NULL, PRIMARY KEY(f1), f2 INT, INDEX f2_ind(f2));
2761
CREATE TABLE res_6_401003_2(f1 INT NOT NULL, PRIMARY KEY(f1), f2 INT, INDEX f2_ind(f2));
2763
GRANT SELECT ON db_datadict.res_6_401003_1 TO 'user_1'@'localhost';
2764
GRANT SELECT ON db_datadict_2.res_6_401003_2 TO 'user_2'@'localhost';
2769
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
2770
connect (user_10_1, localhost, user_1, , db_datadict);
2772
SELECT * FROM information_schema.table_constraints;
2773
SELECT COUNT(*) FROM information_schema.table_constraints;
2774
disconnect user_10_1;
2776
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
2777
connect (user_10_2, localhost, user_2, , db_datadict_2);
2779
SELECT * FROM information_schema.table_constraints;
2780
SELECT COUNT(*) FROM information_schema.table_constraints;
2781
disconnect user_10_2;
2786
--source suite/funcs_1/include/show_connection.inc
2787
DROP USER 'user_1'@'localhost';
2788
DROP USER 'user_2'@'localhost';
2789
DROP TABLE res_6_401003_1;
2791
DROP TABLE res_6_401003_2;
2793
DROP DATABASE db_datadict;
2794
DROP DATABASE db_datadict_2;
2795
# -------------------------------------------------------------------------------------------------------
2797
let $message= Testcase 3.2.11.1:;
2798
--source include/show_msg80.inc
2800
################################################################################
2801
# Testcase 3.2.11.1: Ensure that the INFORMATION_SCHEMA.TABLE_PRIVILEGES
2802
# table has the following columns, in the following order:
2804
# GRANTEE (shows the name of a user who has either granted,
2805
# or been granted a table privilege),
2806
# TABLE_CATALOG (always shows NULL),
2807
# TABLE_SCHEMA (shows the name of the schema, or database,
2808
# in which the table for which a privilege has been
2810
# TABLE_NAME (shows the name of the table),
2811
# PRIVILEGE_TYPE (shows the type of privilege that was
2812
# granted; must be either SELECT, INSERT, UPDATE,
2813
# DELETE, REFERENCES, ALTER, INDEX, DROP, or CREATE
2815
# IS_GRANTABLE (shows whether that privilege was granted
2816
# WITH GRANT OPTION).
2817
################################################################################
2819
let $is_table= table_privileges;
2820
--source suite/funcs_1/datadict/datadict_show_table_design.inc
2821
# -------------------------------------------------------------------------------------------------------
2823
let $message= Testcase 3.2.11.2 + 3.2.11.3 + 3.2.11.4:;
2824
--source include/show_msg80.inc
2826
################################################################################
2827
# Testcase 3.2.11.2: Ensure that the table shows the relevant information on
2828
# every table privilege which has been granted to the current
2829
# user or PUBLIC, or which was granted by the current user.
2830
################################################################################
2831
# Testcase 3.2.11.3: Ensure that the table does not show any information on any
2832
# table privilege which was granted to any user other than
2833
# the current user or PUBLIC, or which was granted by any
2834
# user other than the current user.
2835
################################################################################
2836
# Testcase 3.2.11.4: Ensure that the table does not show any information on any
2837
# privileges that are not table privileges for the current
2839
################################################################################
2842
DROP DATABASE IF EXISTS db_datadict;
2845
create database db_datadict;
2847
CREATE USER 'user_1'@'localhost';
2848
GRANT CREATE, SELECT ON db_datadict.* TO 'user_1'@'localhost' WITH GRANT OPTION;
2849
CREATE USER 'user_2'@'localhost';
2850
CREATE USER 'user_3'@'localhost';
2854
create table tb1(f1 int, f2 int, f3 int);
2856
grant select on db_datadict.tb1 to 'user_1'@'localhost';
2857
GRANT ALL on db_datadict.tb1 to 'user_2'@'localhost' WITH GRANT OPTION;
2861
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
2862
connect (user_11_1, localhost, user_1, , db_datadict);
2864
CREATE TABLE tb3 (f1 TEXT);
2865
GRANT SELECT ON db_datadict.tb3 to 'user_3'@'localhost';
2867
SELECT * FROM information_schema.table_privileges
2868
WHERE table_name LIKE 'tb%';
2870
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
2871
connect (user_11_2, localhost, user_2, , db_datadict);
2873
# we see only table privileges for this user, and not any other privileges
2874
SELECT * FROM information_schema.table_privileges;
2876
SELECT USER(), COUNT(*)
2877
FROM information_schema.table_privileges
2878
WHERE grantee = USER();
2880
SELECT USER(), COUNT(*)
2881
FROM information_schema.table_privileges
2882
WHERE grantee = "'user_2'@'localhost'";
2884
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
2885
connect (user_11_3, localhost, user_3, , db_datadict);
2887
# we see only table privileges for this user, and not any other privileges
2888
SELECT * FROM information_schema.table_privileges;
2891
--source suite/funcs_1/include/show_connection.inc
2893
# we see only 'public' table privileges
2894
SELECT * FROM information_schema.table_privileges;
2898
--source suite/funcs_1/include/show_connection.inc
2899
disconnect user_11_1;
2900
disconnect user_11_2;
2901
disconnect user_11_3;
2902
DROP USER 'user_1'@'localhost';
2903
DROP USER 'user_2'@'localhost';
2904
DROP USER 'user_3'@'localhost';
2905
drop table db_datadict.tb1;
2906
drop table db_datadict.tb3;
2908
drop database db_datadict;
2909
# -------------------------------------------------------------------------------------------------------
2911
let $message= Testcase 3.2.12.1:;
2912
--source include/show_msg80.inc
2914
################################################################################
2915
# Testcase 3.2.12.1: Ensure that the INFORMATION_SCHEMA.TABLES
2916
# table has the following columns, in the following order:
2918
# TABLE_CATALOG (always shows NULL),
2919
# TABLE_SCHEMA (shows the name of the database, or schema,
2920
# in which an accessible table resides),
2921
# TABLE_NAME (shows the name of a table which the current
2923
# TABLE_TYPE (shows whether the table is a BASE TABLE, a
2924
# TEMPORARY table, or a VIEW),
2925
# ENGINE (shows the storage engine used for the table),
2926
# VERSION (shows the version number of the table's .frm
2928
# ROW_FORMAT (shows the table's row storage format; either
2929
# FIXED, DYNAMIC or COMPRESSED),
2930
# TABLE_ROWS (shows the number of rows in the table),
2931
# AVG_ROW_LENGTH (shows the average length of the table's
2933
# DATA_LENGTH (shows the length of the table's data file),
2934
# MAX_DATA_LENGTH (shows the maximum length of the table's
2936
# INDEX_LENGTH (shows the length of the index file
2937
# associated with the table),
2938
# DATA_FREE (shows the number of allocated, unused bytes),
2939
# AUTO_INCREMENT (shows the next AUTO_INCREMENT value, where
2941
# CREATE_TIME (shows the timestamp of the time the table was
2943
# UPDATE_TIME (shows the timestamp of the time the table's
2944
# data file was last updated),
2945
# CHECK_TIME (shows the timestamp of the time the table was
2947
# TABLE_COLLATION (shows the table's default collation),
2948
# CHECKSUM (shows the live checksum value for the table, if
2949
# any; otherwise NULL),
2950
# CREATE_OPTIONS (shows any additional options used in the
2951
# table's definition; otherwise NULL),
2952
# TABLE_COMMENT (shows the comment added to the table's
2953
# definition; otherwise NULL).
2954
################################################################################
2956
let $is_table= tables;
2957
--source suite/funcs_1/datadict/datadict_show_table_design.inc
2958
# -------------------------------------------------------------------------------------------------------
2960
let $message= Testcase 3.2.12.2 + 3.2.12.3:;
2961
--source include/show_msg80.inc
2963
################################################################################
2964
# Testcase 3.2.12.2: Ensure that the table shows the relevant information on
2965
# every base table and view on which the current user or
2966
# PUBLIC has privileges.
2967
################################################################################
2968
# Testcase 3.2.12.3: Ensure that the table does not show any information on any
2969
# tables on which the current user and public have no
2971
################################################################################
2974
DROP DATABASE IF EXISTS db_datadict;
2977
create database db_datadict;
2979
CREATE USER 'user_1'@'localhost';
2980
GRANT CREATE, CREATE VIEW, INSERT, SELECT ON db_datadict.*
2981
TO 'user_1'@'localhost' WITH GRANT OPTION;
2982
CREATE USER 'user_2'@'localhost';
2983
CREATE USER 'user_3'@'localhost';
2987
create table tb1(f1 int, f2 int, f3 int);
2989
grant select on db_datadict.tb1 to 'user_1'@'localhost';
2990
GRANT ALL on db_datadict.tb1 to 'user_2'@'localhost' WITH GRANT OPTION;
2994
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
2995
connect (user_12_1, localhost, user_1, , db_datadict);
2997
# tb2 is not granted to anyone
2998
CREATE TABLE tb2 (f1 DECIMAL);
2999
CREATE TABLE tb3 (f1 TEXT);
3000
GRANT SELECT ON db_datadict.tb3 to 'user_3'@'localhost';
3001
GRANT INSERT ON db_datadict.tb3 to 'user_2'@'localhost';
3003
CREATE VIEW v3 AS SELECT * FROM tb3;
3004
GRANT SELECT ON db_datadict.v3 to 'user_3'@'localhost';
3006
#FIXME 3.2.12: we split the "SELECT * FROM tables" in two parts until
3007
#FIXME 3.2.12: Bug #12397: wrong values shown in column CREATE_OPTIONS of
3008
#FIXME 3.2.12: INFORMATION_SCHEMA.TABLES is solved, one with 'more' and one
3009
#FIXME 3.2.12: with 'less' replace
3012
# 11 MAX_DATA_LENGTH
3018
if ($have_bug_11589)
3020
--disable_ps_protocol
3022
--replace_column 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "YYYY-MM-DD hh:mm:ss" 16 "YYYY-MM-DD hh:mm:ss" 20 "#CO#"
3023
SELECT * FROM information_schema.tables
3024
WHERE table_schema = 'information_schema';
3027
# 11 MAX_DATA_LENGTH
3032
--replace_column 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "YYYY-MM-DD hh:mm:ss" 16 "YYYY-MM-DD hh:mm:ss"
3033
SELECT * FROM information_schema.tables
3034
WHERE NOT( table_schema = 'information_schema');
3035
--enable_ps_protocol
3037
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
3038
connect (user_12_2, localhost, user_2, , db_datadict);
3040
# we see only tables for this user, and not any other
3041
#FIXME 3.2.12: we split the "SELECT * FROM tables" in two parts until
3042
#FIXME 3.2.12: Bug #12397: wrong values shown in column CREATE_OPTIONS of
3043
#FIXME 3.2.12: INFORMATION_SCHEMA.TABLES is solved, one with 'more' and one
3044
#FIXME 3.2.12: with 'less' replace
3047
# 11 MAX_DATA_LENGTH
3053
if ($have_bug_11589)
3055
--disable_ps_protocol
3057
--replace_column 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "YYYY-MM-DD hh:mm:ss" 16 "YYYY-MM-DD hh:mm:ss" 20 "#CO#"
3058
SELECT * FROM information_schema.tables
3059
WHERE table_schema = 'information_schema';
3062
# 11 MAX_DATA_LENGTH
3067
--replace_column 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "YYYY-MM-DD hh:mm:ss" 16 "YYYY-MM-DD hh:mm:ss"
3068
SELECT * FROM information_schema.tables
3069
WHERE NOT( table_schema = 'information_schema');
3070
--enable_ps_protocol
3072
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
3073
connect (user_12_3, localhost, user_3, , db_datadict);
3075
# we see only tables for this user, and not any other
3077
#FIXME 3.2.12: we split the "SELECT * FROM tables" in two parts until
3078
#FIXME 3.2.12: Bug #12397: wrong values shown in column CREATE_OPTIONS of
3079
#FIXME 3.2.12: INFORMATION_SCHEMA.TABLES is solved, one with 'more' and one
3080
#FIXME 3.2.12: with 'less' replace
3083
# 11 MAX_DATA_LENGTH
3089
if ($have_bug_11589)
3091
--disable_ps_protocol
3093
--replace_column 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "YYYY-MM-DD hh:mm:ss" 16 "YYYY-MM-DD hh:mm:ss" 20 "#CO#"
3094
SELECT * FROM information_schema.tables
3095
WHERE table_schema = 'information_schema';
3098
# 11 MAX_DATA_LENGTH
3103
--replace_column 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "YYYY-MM-DD hh:mm:ss" 16 "YYYY-MM-DD hh:mm:ss"
3104
SELECT * FROM information_schema.tables
3105
WHERE NOT( table_schema = 'information_schema');
3106
--enable_ps_protocol
3109
--source suite/funcs_1/include/show_connection.inc
3111
# we see only 'public' tables
3113
#FIXME 3.2.12: we split the "SELECT * FROM tables" in two parts until
3114
#FIXME 3.2.12: Bug #12397: wrong values shown in column CREATE_OPTIONS of
3115
#FIXME 3.2.12: INFORMATION_SCHEMA.TABLES is solved, one with 'more' and one
3116
#FIXME 3.2.12: with 'less' replace
3119
# 11 MAX_DATA_LENGTH
3125
if ($have_bug_11589)
3127
--disable_ps_protocol
3129
--replace_column 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "YYYY-MM-DD hh:mm:ss" 16 "YYYY-MM-DD hh:mm:ss" 20 "#CO#"
3130
SELECT * FROM information_schema.tables
3131
WHERE table_schema = 'information_schema';
3134
# 11 MAX_DATA_LENGTH
3139
--replace_column 9 "#ARL#" 10 "#DL#" 11 "#MDL#" 12 "#IL#" 13 "#DF#" 15 "YYYY-MM-DD hh:mm:ss" 16 "YYYY-MM-DD hh:mm:ss"
3140
SELECT * FROM information_schema.tables
3141
WHERE NOT( table_schema = 'information_schema');
3142
--enable_ps_protocol
3145
disconnect user_12_1;
3146
disconnect user_12_2;
3147
disconnect user_12_3;
3148
DROP USER 'user_1'@'localhost';
3149
DROP USER 'user_2'@'localhost';
3150
DROP USER 'user_3'@'localhost';
3151
DROP TABLE db_datadict.tb1;
3152
DROP TABLE db_datadict.tb3;
3153
DROP VIEW db_datadict.v3;
3155
DROP DATABASE db_datadict;
3156
# -------------------------------------------------------------------------------------------------------
3158
let $message= Testcase 3.2.13.1:;
3159
--source include/show_msg80.inc
3161
################################################################################
3162
# Testcase 3.2.13.1: Ensure that the INFORMATION_SCHEMA.VIEWS
3163
# table has the following columns, in the following order:
3165
# TABLE_CATALOG (always shows NULL),
3166
# TABLE_SCHEMA (shows the database, or schema, in which an
3167
# accessible view resides),
3168
# TABLE_NAME (shows the name of a view accessible to the
3170
# VIEW_DEFINITION (shows the SELECT statement that makes
3171
# up the view's definition),
3172
# CHECK_OPTION (shows the value of the WITH CHECK OPTION
3173
# clause used to define the view, either NONE, LOCAL
3175
# IS_UPDATABLE (shows whether the view is an updatable
3177
# DEFINER (added with 5.0.14),
3178
# SECURITY_TYPE (added with 5.0.14).
3179
################################################################################
3181
let $is_table= views;
3182
--source suite/funcs_1/datadict/datadict_show_table_design.inc
3183
# -------------------------------------------------------------------------------------------------------
3185
let $message= Testcase 3.2.13.2 + 3.2.13.3:;
3186
--source include/show_msg80.inc
3188
################################################################################
3189
# Testcase 3.2.13.2: Ensure that the table shows the relevant information on
3190
# every view for which the current user or PUBLIC has the
3191
# SHOW CREATE VIEW privilege.
3192
################################################################################
3193
# Testcase 3.2.13.3: Ensure that the table does not show any information on any
3194
# views for which the current user and PUBLIC have no SHOW
3195
# CREATE VIEW privilege.
3196
################################################################################
3199
DROP DATABASE IF EXISTS db_datadict;
3202
CREATE DATABASE db_datadict;
3204
CREATE USER 'user_1'@'localhost';
3205
CREATE USER 'user_2'@'localhost';
3206
CREATE USER 'user_no_views'@'localhost';
3209
CREATE TABLE tb_401302(f1 INT, f2 INT, f3 INT);
3210
CREATE VIEW v_granted_to_1 AS SELECT * FROM tb_401302;
3211
CREATE VIEW v_granted_glob AS SELECT f2, f3 FROM tb_401302;
3213
GRANT SELECT ON db_datadict.tb_401302 TO 'user_1'@'localhost';
3214
GRANT SELECT ON db_datadict.v_granted_to_1 TO 'user_1'@'localhost';
3215
GRANT SHOW VIEW, CREATE VIEW ON db_datadict.* TO 'user_2'@'localhost';
3219
SELECT * FROM information_schema.views;
3221
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
3222
connect (user_13_1, localhost, user_1, , test);
3224
SELECT * FROM information_schema.views;
3226
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
3227
connect (user_13_2, localhost, user_2, , test);
3229
SELECT * FROM information_schema.views;
3231
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
3232
connect (user_no_views, localhost, user_no_views, , test);
3234
SELECT * FROM information_schema.views;
3238
--source suite/funcs_1/include/show_connection.inc
3239
disconnect user_13_1;
3240
disconnect user_13_2;
3241
disconnect user_no_views;
3243
DROP USER 'user_1'@'localhost';
3244
DROP USER 'user_2'@'localhost';
3245
DROP USER 'user_no_views'@'localhost';
3246
DROP VIEW v_granted_to_1;
3247
DROP TABLE tb_401302;
3248
DROP VIEW v_granted_glob;
3250
DROP DATABASE db_datadict;
3251
# -------------------------------------------------------------------------------------------------------
3253
let $message= Testcase 3.2.14.1:;
3254
--source include/show_msg80.inc
3256
################################################################################
3257
# Testcase 3.2.14.1: Ensure that the INFORMATION_SCHEMA.STATISTICS
3258
# table has the following columns, in the following order:
3260
# TABLE_CATALOG (always shows NULL),
3261
# TABLE_SCHEMA (shows the database, or schema, in which a
3262
# table indexed by an accessible index resides),
3263
# TABLE_NAME (shows the name of the indexed table),
3264
# NON_UNIQUE (shows whether the index may contain duplicate
3265
# values; 0 if it cannot, 1 if it can),
3266
# INDEX_SCHEMA (shows the database, or schema, in which an
3267
# accessible index resides),
3268
# INDEX_NAME (shows the name of an index which the current
3270
# SEQ_IN_INDEX (shows the ordinal position of an indexed
3271
# column within the index),
3272
# COLUMN_NAME (shows the name of a column that comprises
3273
# some, or all, of an index key),
3274
# COLLATION (shows how the column is sorted in the index;
3275
# either A for ascending or NULL for unsorted
3277
# CARDINALITY (shows the number of unique values in the
3279
# SUB_PART (shows the number of indexed characters if the
3280
# index is a prefix index),
3281
# PACKED (shows how the index key is packed),
3282
# NULLABLE (shows whether the index column may contain NULL
3284
# INDEX_TYPE (shows the index type; either BTREE, FULLTEXT,
3286
# COMMENT (shows a comment on the index, if any).
3287
################################################################################
3289
let $is_table= statistics;
3290
--source suite/funcs_1/datadict/datadict_show_table_design.inc
3291
# -------------------------------------------------------------------------------------------------------
3293
let $message= Testcase 3.2.14.2 + 3.2.14.3:;
3294
--source include/show_msg80.inc
3296
################################################################################
3297
# Testcase 3.2.14.2: Ensure that the table shows the relevant information on
3298
# every index which the current user or PUBLIC may access
3299
# (usually because privileges on the indexed table have been
3301
################################################################################
3302
# Testcase 3.2.14.3: Ensure that the table does not show any information on any
3303
# indexes which the current user and PUBLIC may not access.
3304
################################################################################
3307
DROP DATABASE IF EXISTS db_datadict;
3308
DROP DATABASE IF EXISTS db_datadict_2;
3311
CREATE DATABASE db_datadict;
3312
CREATE DATABASE db_datadict_2;
3314
CREATE USER 'user_1'@'localhost';
3315
CREATE USER 'user_2'@'localhost';
3318
create table tb_6_401402_1(f1 int not null, primary key(f1), f2 int, index f2_ind(f2));
3319
create table tb_6_401402_2(f1 int not null, primary key(f1), f2 int, index f2_ind(f2));
3320
grant select on db_datadict.tb_6_401402_1 to 'user_1'@'localhost' WITH GRANT OPTION;
3324
create table tb_2_1(f1 int not null, primary key(f1), f2 int, index f2_ind(f2));
3325
create table tb_2_2(f1 int not null, primary key(f1), f2 int, index f2_ind(f2));
3326
grant select on db_datadict_2.tb_2_1 to 'user_1'@'localhost';
3330
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
3331
connect (user_14_1, localhost, user_1, , test);
3332
SELECT * FROM information_schema.statistics;
3334
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
3335
connect (user_14_2, localhost, user_2, , test);
3336
SELECT * FROM information_schema.statistics;
3339
--source suite/funcs_1/include/show_connection.inc
3340
REVOKE SELECT ON db_datadict.tb_6_401402_1 FROM 'user_1'@'localhost';
3341
SELECT * FROM information_schema.statistics;
3343
# nothing visible for user_1
3344
connection user_14_1;
3345
--source suite/funcs_1/include/show_connection.inc
3346
SELECT * FROM information_schema.statistics;
3348
# no changes visible for user_2
3349
connection user_14_2;
3350
--source suite/funcs_1/include/show_connection.inc
3351
SELECT * FROM information_schema.statistics;
3355
--source suite/funcs_1/include/show_connection.inc
3356
disconnect user_14_1;
3357
disconnect user_14_2;
3359
DROP USER 'user_1'@'localhost';
3360
DROP USER 'user_2'@'localhost';
3361
DROP TABLE tb_6_401402_1;
3362
DROP TABLE tb_6_401402_2;
3364
DROP DATABASE db_datadict;
3365
# -------------------------------------------------------------------------------------------------------
3367
let $message= Testcase 3.2.15.1:;
3368
--source include/show_msg80.inc
3370
################################################################################
3371
# Testcase 3.2.15.1: Ensure that the INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
3372
# table has the following columns, in the following order:
3374
# GRANTEE (shows a user to whom a schema privilege has been
3376
# TABLE_CATALOG (always shows NULL),
3377
# TABLE_SCHEMA (shows the name of the database, or schema,
3378
# on which the privilege has been granted),
3379
# PRIVILEGE_TYPE (shows the granted privilege),
3380
# IS_GRANTABLE (shows whether the privilege was granted WITH
3382
################################################################################
3384
let $is_table= schema_privileges;
3385
--source suite/funcs_1/datadict/datadict_show_table_design.inc
3386
# -------------------------------------------------------------------------------------------------------
3388
let $message= Testcase 3.2.15.2:;
3389
--source include/show_msg80.inc
3391
################################################################################
3392
# Testcase 3.2.15.2: Ensure that the table shows the relevant information on
3393
# every schema-level privilege which has been granted to the
3394
# current user or to PUBLIC, or has been granted by the
3396
################################################################################
3399
DROP DATABASE IF EXISTS db_datadict;
3400
DROP DATABASE IF EXISTS db_datadict_2;
3403
create database db_datadict;
3404
create database db_datadict_2;
3406
CREATE USER 'u_6_401502'@'localhost';
3410
create table res_6_401502(f1 int, f2 int, f3 int);
3411
grant insert on db_datadict.* to 'u_6_401502'@'localhost';
3415
SELECT * FROM information_schema.schema_privileges;
3417
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
3418
connect (u_6_401502, localhost, u_6_401502, , test);
3420
SELECT * FROM information_schema.schema_privileges;
3422
disconnect u_6_401502;
3428
--source suite/funcs_1/include/show_connection.inc
3429
DROP USER 'u_6_401502'@'localhost';
3430
drop table res_6_401502;
3432
drop database db_datadict;
3433
drop database db_datadict_2;
3434
# -------------------------------------------------------------------------------------------------------
3436
let $message= Testcase 3.2.15.3 + 3.2.15.4:;
3437
--source include/show_msg80.inc
3439
################################################################################
3440
# Testcase 3.2.15.3: Ensure that the table does not show any information on any
3441
# schema-level privileges which have been granted to users
3442
# other than the current user or to PUBLIC, or that have been
3443
# granted by any user other than the current user.
3444
################################################################################
3445
# Testcase 3.2.15.4: Ensure that the table does not show any information on any
3446
# privileges that are not schema-level privileges for the
3448
################################################################################
3451
DROP DATABASE IF EXISTS db_datadict;
3452
DROP DATABASE IF EXISTS db_datadict_2;
3455
create database db_datadict;
3456
create database db_datadict_2;
3458
CREATE USER 'u_6_401503_1'@'localhost';
3459
CREATE USER 'u_6_401503_2'@'localhost';
3460
CREATE USER 'u_6_401503_3'@'localhost';
3464
create table res_6_401503_1(f1 int, f2 int, f3 int);
3468
create table res_6_401503_2(f1 int, f2 int, f3 int);
3470
grant update on db_datadict.* to 'u_6_401503_1'@'localhost';
3471
grant delete on db_datadict_2.* to 'u_6_401503_2'@'localhost';
3475
SELECT * FROM information_schema.schema_privileges;
3477
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
3478
connect (u_6_401503_1, localhost, u_6_401503_1, , test);
3480
SELECT * FROM information_schema.schema_privileges;
3482
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
3483
connect (u_6_401503_2, localhost, u_6_401503_2, , test);
3485
SELECT * FROM information_schema.schema_privileges;
3487
# should not show anything
3488
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
3489
connect (u_6_401503_3, localhost, u_6_401503_3, , test);
3491
SELECT * FROM information_schema.schema_privileges;
3496
--source suite/funcs_1/include/show_connection.inc
3497
disconnect u_6_401503_1;
3498
disconnect u_6_401503_2;
3499
disconnect u_6_401503_3;
3501
DROP USER 'u_6_401503_1'@'localhost';
3502
DROP USER 'u_6_401503_2'@'localhost';
3503
DROP USER 'u_6_401503_3'@'localhost';
3504
drop table res_6_401503_1;
3506
drop table res_6_401503_2;
3508
drop database db_datadict;
3509
drop database db_datadict_2;
3510
# -------------------------------------------------------------------------------------------------------
3512
let $message= Testcase 3.2.16.1:;
3513
--source include/show_msg80.inc
3515
################################################################################
3516
# Testcase 3.2.16.1: Ensure that the INFORMATION_SCHEMA.USER_PRIVILEGES
3517
# table has the following columns, in the following order:
3519
# GRANTEE (shows a user to whom a user privilege has been
3521
# TABLE_CATALOG (always shows NULL),
3522
# PRIVILEGE_TYPE (shows the granted privilege),
3523
# IS_GRANTABLE (shows whether the privilege was granted WITH
3525
################################################################################
3528
# Bug #12063 column 'TABLE_SCHEMA' is missing in table
3529
# INFORMATION_SCHEMA.USER_PRIVILEGE
3530
# ... is not a bug, it has been added by mistake in the TP requirement document.
3533
let $is_table= user_privileges;
3534
--source suite/funcs_1/datadict/datadict_show_table_design.inc
3535
# -------------------------------------------------------------------------------------------------------
3537
let $message= Testcase 3.2.16.2 + 3.2.16.3 + 3.2.16.4:;
3538
--source include/show_msg80.inc
3540
################################################################################
3541
# Testcase 3.2.16.2: Ensure that the table shows the relevant information on
3542
# every user privilege which has been granted to the current
3543
# user or to PUBLIC, or has been granted by the current user.
3544
################################################################################
3545
# Testcase 3.2.16.3: Ensure that the table does not show any information on any
3546
# user privileges which have been granted to users other than
3547
# the current user or have been granted by any user other
3548
# than the current user.
3549
################################################################################
3550
# Testcase 3.2.16.4: Ensure that the table does not show any information on any
3551
# privileges that are not user privileges for the current
3553
################################################################################
3555
#FIXME 3.2.16: - when Bug #12269 is fixed a some of the outputs here may be
3556
#FIXME 3.2.16: deleted as I added them for checking where / which information
3557
#FIXME 3.2.16: is shown.
3560
DROP DATABASE IF EXISTS db_datadict;
3563
let $cmd1= SELECT * FROM information_schema.user_privileges
3564
WHERE grantee LIKE "%user%"
3565
ORDER BY grantee, table_catalog, privilege_type;
3566
let $cmd2= SELECT * FROM mysql.user WHERE user LIKE "%user%" ORDER BY host, user;
3567
let $cmd3= SHOW GRANTS;
3569
CREATE DATABASE db_datadict;
3571
CREATE USER 'user_1'@'localhost';
3572
CREATE USER 'user_2'@'localhost';
3573
CREATE USER 'user_3'@'localhost';
3575
GRANT SELECT ON db_datadict.* TO 'user_1'@'localhost';
3576
GRANT SELECT ON mysql.user TO 'user_1'@'localhost';
3578
GRANT INSERT ON *.* TO 'user_2'@'localhost';
3579
GRANT UPDATE ON *.* TO 'user_2'@'localhost';
3583
let $message= FIXME (see Bug 12269) Here we expect more than only <USAGE> for user_1;
3584
--source include/show_msg.inc
3589
let $message= add GRANT OPTION db_datadict.* to user_1;
3590
--source include/show_msg.inc
3591
GRANT UPDATE ON db_datadict.* TO 'user_1'@'localhost' WITH GRANT OPTION;
3593
let $message= FIXME (see Bug 12269) Here the <YES> is missing for the GRANT OPTION for user_1;
3594
--source include/show_msg.inc
3602
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
3603
connect (user_16_1, localhost, user_1, , db_datadict);
3608
# add SELECT on *.* to user_1
3609
let $message= Now add SELECT on *.* to user_1;
3610
--source include/show_msg.inc
3613
--source suite/funcs_1/include/show_connection.inc
3614
GRANT SELECT ON *.* TO 'user_1'@'localhost';
3615
let $message= Here <SELECT NO> is shown correctly for user_1;
3616
--source include/show_msg.inc
3621
GRANT SELECT ON *.* TO 'user_1'@'localhost' WITH GRANT OPTION;
3622
let $message= Here <SELECT YES> is shown correctly for user_1;
3623
--source include/show_msg.inc
3633
# check that this appears
3634
connection user_16_1;
3635
--source suite/funcs_1/include/show_connection.inc
3640
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
3641
connect (user_16_2, localhost, user_2, , db_datadict);
3647
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
3648
connect (user_16_3, localhost, user_3, , test);
3654
let $message= revoke privileges from user_1;
3655
--source include/show_msg.inc
3658
--source suite/funcs_1/include/show_connection.inc
3659
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user_1'@'localhost';
3665
connection user_16_1;
3666
--source suite/funcs_1/include/show_connection.inc
3672
--source suite/funcs_1/include/show_connection.inc
3673
# checks entered before bug #12269 was reported
3674
# OK, user_1 has no privs here
3676
CREATE TABLE db_datadict.tb_55 ( c1 TEXT );
3677
--source suite/funcs_1/include/show_connection.inc
3682
# OK, user_1 has no privs here
3684
CREATE TABLE db_datadict.tb_66 ( c1 TEXT );
3686
let $message= add ALL on db_datadict.* (and select on mysql.user) to user_1;
3687
--source include/show_msg.inc
3690
--source suite/funcs_1/include/show_connection.inc
3692
GRANT ALL ON db_datadict.* TO 'user_1'@'localhost' WITH GRANT OPTION;
3693
GRANT SELECT ON mysql.user TO 'user_1'@'localhost';
3699
connection user_16_1;
3700
--source suite/funcs_1/include/show_connection.inc
3705
# OK, user_1 has no privs here
3707
CREATE TABLE db_datadict.tb_56 ( c1 TEXT );
3709
# using 'USE' lets the server read the privileges new, so now the CREATE works
3711
--source suite/funcs_1/include/show_connection.inc
3715
CREATE TABLE tb_57 ( c1 TEXT );
3717
let $message= revoke privileges from user_1;
3718
--source include/show_msg.inc
3721
--source suite/funcs_1/include/show_connection.inc
3722
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user_1'@'localhost';
3729
connection user_16_1;
3730
--source suite/funcs_1/include/show_connection.inc
3735
# WORKS, as the existing old privileges are used!
3736
CREATE TABLE db_datadict.tb_58 ( c1 TEXT );
3737
# existing privileges are "read" new when USE is called, user has no priviliges
3740
#FIXME 3.2.16: check that it is correct that this now 'works': --error 1142
3741
CREATE TABLE db_datadict.tb_59 ( c1 TEXT );
3746
--source suite/funcs_1/include/show_connection.inc
3747
disconnect user_16_1;
3748
disconnect user_16_2;
3749
disconnect user_16_3;
3750
DROP USER 'user_1'@'localhost';
3751
DROP USER 'user_2'@'localhost';
3752
DROP USER 'user_3'@'localhost';
3753
DROP DATABASE IF EXISTS db_datadict;
3754
# -------------------------------------------------------------------------------------------------------
3756
let $message= Testcase 3.2.17: Checks on Performance - not here in this script!;
3757
--source include/show_msg80.inc
3759
################################################################################
3760
# Testcase 3.2.17.1: Ensure that every INFORMATION_SCHEMA table shows all the
3761
# correct information, and no incorrect information, for a
3762
# database to which 100 different users, each of which has a
3763
# randomly issued set of privileges and access to a
3764
# randomly chosen set of database objects, have access.
3765
# The database should contain a mixture of all types of
3766
# database objects (i.e. tables, views, stored procedures,
3768
################################################################################
3770
################################################################################
3771
# Testcase 3.2.17.2: Ensure that every INFORMATION_SCHEMA table shows all the
3772
# correct information, and no incorrect information, for 10
3773
# different databases to which 50 different users, each of
3774
# which has a randomly issued set of privileges and access
3775
# to a randomly chosen set of database objects in two or
3776
# more of the databases, have access. The databases should
3777
# each contain a mixture of all types of database objects
3778
# (i.e. tables, views, stored procedures, triggers).
3779
################################################################################
3781
# -------------------------------------------------------------------------------------------------------
3783
let $message= Testcase 3.2.18.1:;
3784
--source include/show_msg80.inc
3786
################################################################################
3787
# Testcase 3.2.18.1: Ensure that the INFORMATION_SCHEMA.TRIGGERS
3788
# table has the following columns, in the following order:
3791
# (FIXME - list copied from WL#1996)
3793
# TRIGGER_CATALOG NULL
3796
# EVENT_MANIPULATION
3797
# EVENT_OBJECT_CATALOG NULL
3798
# EVENT_OBJECT_SCHEMA
3799
# EVENT_OBJECT_TABLE
3801
# ACTION_CONDITION NULL
3803
# ACTION_ORIENTATION
3805
# ACTION_REFERENCE_OLD_TABLE NULL
3806
# ACTION_REFERENCE_NEW_TABLE NULL
3807
# ACTION_REFERENCE_OLD_ROW
3808
# ACTION_REFERENCE_NEW_ROW
3812
################################################################################
3814
let $is_table= triggers;
3815
--source suite/funcs_1/datadict/datadict_show_table_design.inc
3816
# -------------------------------------------------------------------------------------------------------
3818
let $message= Testcase 3.2.18.2 + 3.2.18.3:;
3819
--source include/show_msg80.inc
3821
################################################################################
3822
# Testcase 3.2.18.2: Ensure that the table shows the relevant information on
3823
# every trigger on which the current user or PUBLIC has
3825
################################################################################
3826
# Testcase 3.2.18.3: Ensure that the table does not show any information on any
3827
# trigger on which the current user and public have no
3829
################################################################################
3831
#FIXME 3.2.18.2: to be added.
3832
#FIXME 3.2.18.2: don't forget to add the test description to QATestPlanV50func
3834
# -------------------------------------------------------------------------------------------------------
3836
let $message= Testcase 3.2.19.1:;
3837
--source include/show_msg80.inc
3839
################################################################################
3840
# Testcase 3.2.19.1: Ensure that the INFORMATION_SCHEMA.PARAMETERS
3841
# table has the following columns, in the following order:
3843
################################################################################
3845
let $is_table= parameters;
3846
# when table is implemented remove this and the next 4 lines and "enable" 5th line:
3847
# and don't forget to add the test description to QATestPlanV50func
3848
let $message= checking a table that will be implemented later;
3849
--source include/show_msg.inc
3851
eval DESC $is_table;
3852
#--source suite/funcs_1/datadict/datadict_show_table_design.inc
3853
# -------------------------------------------------------------------------------------------------------
3855
let $message= Testcase 3.2.20.1:;
3856
--source include/show_msg80.inc
3858
################################################################################
3859
# Testcase 3.2.20.1: Ensure that the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
3860
# table has the following columns, in the following order:
3862
################################################################################
3864
let $is_table= referential_constraints;
3865
# when table is implemented remove this and the next 4 lines and "enable" 5th line:
3866
# and don't forget to add the test description to QATestPlanV50func
3867
let $message= checking a table that will be implemented later;
3868
--source include/show_msg.inc
3870
eval DESC $is_table;
3871
#--source suite/funcs_1/datadict/datadict_show_table_design.inc
3872
# -------------------------------------------------------------------------------------------------------
3875
################################################################################
3877
let $message= *** End of Data Dictionary Tests ***;
3878
--source include/show_msg80.inc
3880
################################################################################
3883
# some cleanup to be sure nothing remains
3885
DROP TABLE IF EXISTS test.tb1;
3886
DROP TABLE IF EXISTS test.tb2;
3887
DROP TABLE IF EXISTS test.tb3;
3888
DROP TABLE IF EXISTS test.tb4;
3889
DROP TABLE IF EXISTS test.t1;
3890
DROP TABLE IF EXISTS test.t2;
3891
DROP TABLE IF EXISTS test.t3;
3892
DROP TABLE IF EXISTS test.t4;
3893
DROP TABLE IF EXISTS test.t7;
3894
DROP TABLE IF EXISTS test.t8;
3895
DROP TABLE IF EXISTS test.t9;
3896
DROP TABLE IF EXISTS test.t10;
3897
DROP TABLE IF EXISTS test.t11;
3898
DROP DATABASE IF EXISTS test1;
3899
DROP DATABASE IF EXISTS test4;
3900
DROP DATABASE IF EXISTS db_datadict;
3901
DROP DATABASE IF EXISTS db_datadict_1;
3902
DROP DATABASE IF EXISTS db_datadict_2;
added
removed
mysql-test/suite/funcs_1/datadict/datadict_master.inc
