1# suite/funcs_1/datadict/is_schemata.inc 2# 3# Check the layout of information_schema.schemata, permissions and the impact of 4# CREATE/ALTER/DROP SCHEMA on it. 5# 6# Note: 7# This test is not intended 8# - to show information about the all time existing databases 9# information_schema and mysql 10# - for checking storage engine properties 11# 12# Author: 13# 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of 14# testsuite funcs_1 15# Create this script based on older scripts and new code. 16# Last Change: 17# 2008-06-11 mleich Move t/is_schemata.test to this file and 18# create variants for embedded/non embedded server. 19# 20 21--source include/default_charset.inc 22let $is_table = SCHEMATA; 23 24# The table INFORMATION_SCHEMA.SCHEMATA must exist 25eval SHOW TABLES FROM information_schema LIKE '$is_table'; 26 27--echo ####################################################################### 28--echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT 29--echo ####################################################################### 30# Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT 31# statement, just as if it were an ordinary user-defined table. 32# 33--source suite/funcs_1/datadict/is_table_query.inc 34 35 36--echo ######################################################################### 37--echo # Testcase 3.2.9.1: INFORMATION_SCHEMA.SCHEMATA layout; 38--echo ######################################################################### 39# Ensure that the INFORMATION_SCHEMA.SCHEMATA table has the following columns, 40# in the following order: 41# 42# CATALOG_NAME (always shows NULL), 43# SCHEMA_NAME (shows the name of a database, or schema, on which the current 44# user or PUBLIC has privileges), 45# DEFAULT_CHARACTER_SET_NAME (shows the name of that database's default 46# character set), 47# DEFAULT_COLLATION_NAME (shows the database defaul collation) 48# SQL_PATH (always shows NULL). 49# 50--source suite/funcs_1/datadict/datadict_bug_12777.inc 51eval DESCRIBE information_schema.$is_table; 52--source suite/funcs_1/datadict/datadict_bug_12777.inc 53eval SHOW CREATE TABLE information_schema.$is_table; 54--source suite/funcs_1/datadict/datadict_bug_12777.inc 55eval SHOW COLUMNS FROM information_schema.$is_table; 56 57# Note: Retrieval of information within information_schema.columns about 58# information_schema.schemata is in is_columns_is.test. 59 60# Show that CATALOG_NAME and SQL_PATH are always NULL. 61SELECT catalog_name, schema_name, sql_path 62FROM information_schema.schemata 63WHERE catalog_name IS NOT NULL or sql_path IS NOT NULL 64ORDER BY schema_name; 65 66 67--echo ############################################################################### 68--echo # Testcases 3.2.9.2+3.2.9.3: INFORMATION_SCHEMA.SCHEMATA accessible information 69--echo ############################################################################### 70# 3.2.9.2 Ensure that the table shows the relevant information for every 71# database on which the current user or PUBLIC have privileges. 72# 3.2.9.3 Ensure that the table does not show any information on any databases 73# on which the current user and PUBLIC have no privileges. 74# 75# Note: Check of content within information_schema.schemata about the databases 76# information_schema and mysql is in 77# suite/funcs_1/t/is_schemata_is_mysql.test. 78# 79--disable_warnings 80DROP DATABASE IF EXISTS db_datadict_1; 81DROP DATABASE IF EXISTS db_datadict_2; 82--enable_warnings 83CREATE DATABASE db_datadict_1; 84CREATE DATABASE db_datadict_2; 85 86--error 0,ER_CANNOT_USER 87DROP USER 'testuser1'@'localhost'; 88CREATE USER 'testuser1'@'localhost'; 89--error 0,ER_CANNOT_USER 90DROP USER 'testuser2'@'localhost'; 91CREATE USER 'testuser2'@'localhost'; 92--error 0,ER_CANNOT_USER 93DROP USER 'testuser3'@'localhost'; 94CREATE USER 'testuser3'@'localhost'; 95 96GRANT SELECT ON db_datadict_1.* to 'testuser1'@'localhost'; 97GRANT SELECT ON db_datadict_1.* to 'testuser2'@'localhost'; 98GRANT SELECT ON db_datadict_2.* to 'testuser2'@'localhost'; 99 100let $my_select = SELECT * FROM information_schema.schemata 101WHERE schema_name LIKE 'db_datadict_%' ORDER BY schema_name; 102let $my_show = SHOW DATABASES LIKE 'db_datadict_%'; 103 104eval $my_select; 105--sorted_result 106eval $my_show; 107 108--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 109connect (testuser1, localhost, testuser1, , db_datadict_1); 110# Shows db_datadict_1 111eval $my_select; 112--sorted_result 113eval $my_show; 114 115--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 116connect (testuser2, localhost, testuser2, , db_datadict_2); 117# Shows db_datadict_1 and db_datadict_2 118eval $my_select; 119--sorted_result 120eval $my_show; 121 122--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK 123connect (testuser3, localhost, testuser3, , test); 124# Shows neither db_datadict_1 nor db_datadict_2 125eval $my_select; 126--sorted_result 127eval $my_show; 128 129# Cleanup 130connection default; 131disconnect testuser1; 132disconnect testuser2; 133disconnect testuser3; 134DROP USER 'testuser1'@'localhost'; 135DROP USER 'testuser2'@'localhost'; 136DROP USER 'testuser3'@'localhost'; 137DROP DATABASE db_datadict_1; 138DROP DATABASE db_datadict_2; 139 140 141--echo ################################################################################# 142--echo # Testcases 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.SCHEMATA modifications 143--echo ################################################################################# 144# 3.2.1.13: Ensure that the creation of any new database object (e.g. table or 145# column) automatically inserts all relevant information on that 146# object into every appropriate INFORMATION_SCHEMA table. 147# 3.2.1.14: Ensure that the alteration of any existing database object 148# automatically updates all relevant information on that object in 149# every appropriate INFORMATION_SCHEMA table. 150# 3.2.1.15: Ensure that the dropping of any existing database object 151# automatically deletes all relevant information on that object from 152# every appropriate INFORMATION_SCHEMA table. 153# 154--disable_warnings 155DROP DATABASE IF EXISTS db_datadict; 156--enable_warnings 157 158SELECT * FROM information_schema.schemata WHERE schema_name = 'db_datadict'; 159CREATE DATABASE db_datadict CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'; 160SELECT * FROM information_schema.schemata WHERE schema_name = 'db_datadict'; 161 162# Check modify default CHARACTER SET 163SELECT schema_name, default_character_set_name 164FROM information_schema.schemata WHERE schema_name = 'db_datadict'; 165ALTER SCHEMA db_datadict CHARACTER SET 'utf8'; 166SELECT schema_name, default_character_set_name 167FROM information_schema.schemata WHERE schema_name = 'db_datadict'; 168ALTER SCHEMA db_datadict CHARACTER SET 'latin1'; 169 170# Check modify default COLLATION 171SELECT schema_name, default_collation_name FROM information_schema.schemata 172WHERE schema_name = 'db_datadict'; 173ALTER SCHEMA db_datadict COLLATE 'latin1_general_cs'; 174SELECT schema_name, default_collation_name FROM information_schema.schemata 175WHERE schema_name = 'db_datadict'; 176 177# Check DROP DATABASE 178SELECT schema_name 179FROM information_schema.schemata WHERE schema_name = 'db_datadict'; 180DROP DATABASE db_datadict; 181SELECT schema_name 182FROM information_schema.schemata WHERE schema_name = 'db_datadict'; 183 184 185--echo ######################################################################## 186--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and 187--echo # DDL on INFORMATION_SCHEMA tables are not supported 188--echo ######################################################################## 189# 3.2.1.3: Ensure that no user may execute an INSERT statement on any 190# INFORMATION_SCHEMA table. 191# 3.2.1.4: Ensure that no user may execute an UPDATE statement on any 192# INFORMATION_SCHEMA table. 193# 3.2.1.5: Ensure that no user may execute a DELETE statement on any 194# INFORMATION_SCHEMA table. 195# 3.2.1.8: Ensure that no user may create an index on an INFORMATION_SCHEMA table. 196# 3.2.1.9: Ensure that no user may alter the definition of an 197# INFORMATION_SCHEMA table. 198# 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table. 199# 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any 200# other database. 201# 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data 202# in an INFORMATION_SCHEMA table. 203# 204--disable_warnings 205DROP DATABASE IF EXISTS db_datadict; 206--enable_warnings 207CREATE DATABASE db_datadict CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'; 208 209# Note(mleich): 210# 1. We can get here different error messages. 211# 2. We do not want to unify the individual messages to the far to unspecific 212# 'Got one of the listed errors'. 213let $my_error_message = 214##### The previous statement must fail ###### 215# Server type | expected error name | expected error message 216# -------------------------------------------------------------------------------------------------------------------- 217# not embedded | ER_DBACCESS_DENIED_ERROR | ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' 218# embedded | ER_NON_INSERTABLE_TABLE | ERROR HY000: The target table schemata of the INSERT is not insertable-into 219# | or similar | or similar 220; 221 222--disable_abort_on_error 223INSERT INTO information_schema.schemata 224 (catalog_name, schema_name, default_character_set_name, sql_path) 225VALUES (NULL, 'db1', 'latin1', NULL); 226if (!$mysql_errno) 227{ 228 --echo $my_error_message 229 exit; 230} 231INSERT INTO information_schema.schemata 232SELECT * FROM information_schema.schemata; 233if (!$mysql_errno) 234{ 235 --echo $my_error_message 236 exit; 237} 238UPDATE information_schema.schemata 239SET default_character_set_name = 'utf8' 240WHERE schema_name = 'db_datadict'; 241if (!$mysql_errno) 242{ 243 --echo $my_error_message 244 exit; 245} 246UPDATE information_schema.schemata SET catalog_name = 't_4711'; 247if (!$mysql_errno) 248{ 249 --echo $my_error_message 250 exit; 251} 252DELETE FROM information_schema.schemata WHERE schema_name = 'db_datadict'; 253if (!$mysql_errno) 254{ 255 --echo $my_error_message 256 exit; 257} 258TRUNCATE information_schema.schemata; 259if (!$mysql_errno) 260{ 261 --echo $my_error_message 262 exit; 263} 264CREATE INDEX i1 ON information_schema.schemata(schema_name); 265if (!$mysql_errno) 266{ 267 --echo $my_error_message 268 exit; 269} 270ALTER TABLE information_schema.schemata ADD f1 INT; 271if (!$mysql_errno) 272{ 273 --echo $my_error_message 274 exit; 275} 276DROP TABLE information_schema.schemata; 277if (!$mysql_errno) 278{ 279 --echo $my_error_message 280 exit; 281} 282ALTER TABLE information_schema.schemata RENAME db_datadict.schemata; 283if (!$mysql_errno) 284{ 285 --echo $my_error_message 286 exit; 287} 288ALTER TABLE information_schema.schemata RENAME information_schema.xschemata; 289if (!$mysql_errno) 290{ 291 --echo $my_error_message 292 exit; 293} 294--enable_abort_on_error 295 296# Cleanup 297DROP DATABASE db_datadict; 298 299