Debian Codegraph @

mysql-test/suite/funcs_1/datadict/is_schemata_extensions.inc

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