Debian Codegraph @

mysql-test/suite/funcs_1/t/is_basics_mixed.test

raw
1 # $engine_type must point to storage engine which is all time available.
2 let $engine_type = InnoDB;
3
4 # suite/funcs_1/t/is_basics_mixed.test
5 #
6 # Checks of some basic properties of the INFORMATION_SCHEMA which are not
7 # related to a certain INFORMATION_SCHEMA table.
8 #
9 # This test should not check properties related to storage engines.
10 #
11 # Author:
12 # 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
13 # testsuite funcs_1
14 # Create this script based on older scripts and new code.
15 #
16
17 --source suite/funcs_1/datadict/datadict.pre
18
19
20
21 # The INFORMATION_SCHEMA database must exist.
22 SHOW DATABASES LIKE 'information_schema';
23
24
25 --echo #######################################################################
26 --echo # Testcase 3.2.1.20: USE INFORMATION_SCHEMA is supported
27 --echo #######################################################################
28 # Ensure that USE INFORMATION_SCHEMA allows the user to switch to the
29 # INFORMATION_SCHEMA database, for query purposes only.
30 #
31 # Note: The "for query purposes only" is checked in other tests.
32 # High privileged user (root)
33 --echo # Switch to connection default
34 connection default;
35 USE test;
36 SELECT DATABASE();
37 USE information_schema;
38 SELECT DATABASE();
39 #
40 --error 0,ER_CANNOT_USER
41 DROP USER 'testuser1'@'localhost';
42 CREATE USER 'testuser1'@'localhost';
43 # Low privileged user
44 --echo # Establish connection testuser1 (user=testuser1)
45 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
46 connect (testuser1, localhost, testuser1, , test);
47 SELECT DATABASE();
48 USE information_schema;
49 SELECT DATABASE();
50 #
51 --echo # Switch to connection default and close connection testuser1
52 connection default;
53 disconnect testuser1;
54 DROP USER 'testuser1'@'localhost';
55
56
57 --echo #######################################################################
58 --echo # Testcase TBD1: The INFORMATION_SCHEMA cannot be dropped.
59 --echo #######################################################################
60 --error ER_DBACCESS_DENIED_ERROR
61 DROP DATABASE information_schema;
62
63
64 --echo #######################################################################
65 --echo # Testcase TBD2: There cannot be a second database INFORMATION_SCHEMA.
66 --echo #######################################################################
67 --error ER_DBACCESS_DENIED_ERROR
68 CREATE DATABASE information_schema;
69
70
71 --echo ##################################################################################
72 --echo # Testcase 3.2.1.6+3.2.1.7: No user may create an INFORMATION_SCHEMA table or view
73 --echo ##################################################################################
74 # 3.2.1.6 Ensure that no user may create an INFORMATION_SCHEMA base table.
75 # 3.2.1.7 Ensure that no user may create an INFORMATION_SCHEMA view
76 #
77
78 # 1. High privileged user (root)
79 --echo # Switch to connection default (user=root)
80 connection default;
81 --source suite/funcs_1/datadict/basics_mixed1.inc
82
83 # 2. High privileged user (testuser1)
84 --error 0,ER_CANNOT_USER
85 DROP USER 'testuser1'@'localhost';
86 CREATE USER 'testuser1'@'localhost';
87 GRANT ALL ON *.* TO testuser1@localhost;
88 SHOW GRANTS FOR testuser1@localhost;
89 --echo # Establish connection testuser1 (user=testuser1)
90 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
91 connect (testuser1, localhost, testuser1, , test);
92 --source suite/funcs_1/datadict/basics_mixed1.inc
93
94 --echo # Switch to connection default (user=root) and close connection testuser1
95 connection default;
96 disconnect testuser1;
97 DROP USER 'testuser1'@'localhost';
98
99 --echo ###############################################################################
100 --echo # Testcase 3.2.1.1+3.2.1.2: INFORMATION_SCHEMA tables can be queried via SELECT
101 --echo ###############################################################################
102 # 3.2.1.1 Ensure that every INFORMATION_SCHEMA table can be queried with
103 # a SELECT statement, just as if it were an ordinary user-defined table.
104 # 3.2.1.2 Ensure that queries on an INFORMATION_SCHEMA table can accept all
105 # SELECT statement options and are always correctly evaluated.
106 #
107 # Some notes(mleich):
108 # - Currently here only a subset of select statement options is checked, it's
109 # still not possible to check here all possible options
110 # - The content of many INFORMATION_SCHEMA tables is checked in other tests.
111 # - We work here only with a subset of the columns of information_schema.tables
112 # because we want have a stable base (all time existing table, stable layout).
113 --disable_warnings
114 DROP DATABASE IF EXISTS db_datadict;
115 --enable_warnings
116 CREATE DATABASE db_datadict;
117 --replace_result $engine_type <some_engine>
118 eval
119 CREATE TABLE db_datadict.t1_first (f1 BIGINT UNIQUE, f2 BIGINT)
120 ENGINE = $engine_type;
121 --replace_result $engine_type <some_engine>
122 eval
123 CREATE TABLE db_datadict.t1_second (f1 BIGINT UNIQUE, f2 BIGINT)
124 ENGINE = $engine_type;
125
126 # SELECT *
127 --echo # Attention: The protocolling of the next result set is disabled.
128 --disable_result_log
129 SELECT * FROM information_schema.tables;
130 --enable_result_log
131 #
132 # SELECT <some columns> + WHERE
133 --sorted_result
134 SELECT table_name FROM information_schema.tables
135 WHERE table_schema = 'db_datadict';
136 #
137 # SELECT string_function(<some column>) + ORDER BY
138 SELECT LENGTH(table_name) FROM information_schema.tables
139 WHERE table_schema = 'db_datadict' ORDER BY table_name;
140 #
141 # SELECT aggregate_function(<some column>) + WHERE with LIKE
142 SELECT count(table_name) FROM information_schema.tables
143 WHERE table_schema LIKE 'db_datadic%';
144 #
145 # SELECT with addition in column list
146 --sorted_result
147 SELECT CAST((LENGTH(table_schema) + LENGTH(table_name)) AS DECIMAL(15,1))
148 FROM information_schema.tables
149 WHERE table_schema = 'db_datadict';
150 #
151 # WHERE with IN + LIMIT
152 SELECT table_name FROM information_schema.tables
153 WHERE table_name IN ('t1_first','t1_second') ORDER BY table_name LIMIT 1;
154 SELECT table_name FROM information_schema.tables
155 WHERE table_name IN ('t1_first','t1_second') ORDER BY table_name LIMIT 1,1;
156 #
157 # WHERE with AND
158 SELECT table_name,table_schema AS my_col FROM information_schema.tables
159 WHERE table_name = 't1_first' AND table_schema = 'db_datadict';
160 #
161 # SELECT HIGH_PRIORITY + WHERE with OR
162 --sorted_result
163 SELECT HIGH_PRIORITY table_name AS my_col FROM information_schema.tables
164 WHERE table_name = 't1_first' OR table_name = 't1_second';
165 #
166 # Empty result set
167 SELECT 1 AS my_col FROM information_schema.tables
168 WHERE table_name = 't1_third';
169 #
170 # SELECT INTO USER VARIABLE
171 SELECT table_name,table_schema INTO @table_name,@table_schema
172 FROM information_schema.tables
173 WHERE table_schema = 'db_datadict' ORDER BY table_name LIMIT 1;
174 SELECT @table_name,@table_schema;
175 #
176 # SELECT INTO OUTFILE
177 let $OUTFILE = $MYSQLTEST_VARDIR/tmp/datadict.out;
178 --error 0,1
179 remove_file $OUTFILE;
180 --replace_result $OUTFILE <OUTFILE>
181 eval SELECT table_name,table_schema
182 INTO OUTFILE '$OUTFILE'
183 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
184 LINES TERMINATED BY '\n'
185 FROM information_schema.tables
186 WHERE table_schema = 'db_datadict' ORDER BY table_name;
187 cat_file $OUTFILE;
188 remove_file $OUTFILE;
189 #
190 # UNION
191 --sorted_result
192 SELECT table_name FROM information_schema.tables
193 WHERE table_name = 't1_first'
194 UNION ALL
195 SELECT table_name FROM information_schema.tables
196 WHERE table_name = 't1_second';
197 #
198 # DISTINCT + SUBQUERY
199 --source include/turn_off_only_full_group_by.inc
200 SELECT DISTINCT table_schema FROM information_schema.tables
201 WHERE table_name IN (SELECT table_name FROM information_schema.tables
202 WHERE table_schema = 'db_datadict')
203 ORDER BY table_name;
204 --source include/restore_sql_mode_after_turn_off_only_full_group_by.inc
205 #
206 # JOIN
207 SELECT table_name FROM information_schema.tables t1
208 LEFT JOIN information_schema.tables t2 USING(table_name,table_schema)
209 WHERE t2.table_schema = 'db_datadict'
210 ORDER BY table_name;
211 #
212 # No schema assigned in SELECT + we are in SCHEMA test
213 # --> The table tables does not exist
214 USE test;
215 --error ER_NO_SUCH_TABLE
216 SELECT * FROM tables;
217
218
219 --echo #########################################################################
220 --echo # Testcase 3.2.1.17+3.2.1.18
221 --echo #########################################################################
222 # 3.2.1.17: Ensure that the SELECT privilege is granted TO PUBLIC WITH GRANT
223 # OPTION on every INFORMATION_SCHEMA table.
224 #
225 # 3.2.1.18: Ensure that the CREATE VIEW privilege on an INFORMATION_SCHEMA table
226 # may be granted to any user.
227 #
228 # Note (mleich): The requirements are to some extend outdated.
229 # Every user is allowed to SELECT on the INFORMATION_SCHEMA.
230 # But the result sets depend on the privileges of the user.
231 #
232 --disable_warnings
233 DROP DATABASE IF EXISTS db_datadict;
234 --enable_warnings
235 CREATE DATABASE db_datadict;
236 --replace_result $engine_type <some_engine>
237 eval
238 CREATE TABLE db_datadict.t1 (f1 BIGINT UNIQUE, f2 BIGINT)
239 ENGINE = $engine_type;
240 SELECT * FROM db_datadict.t1;
241
242 --error 0,ER_CANNOT_USER
243 DROP USER 'testuser1'@'localhost';
244 CREATE USER 'testuser1'@'localhost';
245 --error 0,ER_CANNOT_USER
246 DROP USER 'testuser2'@'localhost';
247 CREATE USER 'testuser2'@'localhost';
248 GRANT CREATE VIEW,SELECT ON db_datadict.* TO testuser1@localhost
249 WITH GRANT OPTION;
250 GRANT USAGE ON db_datadict.* TO testuser2@localhost;
251 FLUSH PRIVILEGES;
252
253 # Check 0: Reveal that GRANT <some privilege> ON INFORMATION_SCHEMA is no
254 # longer allowed.
255 --error ER_DBACCESS_DENIED_ERROR
256 GRANT SELECT on information_schema.* TO testuser1@localhost;
257 --error ER_DBACCESS_DENIED_ERROR
258 GRANT CREATE VIEW ON information_schema.* TO 'u_6_401018'@'localhost';
259
260 # Check 1: Show that a "simple" user (<> root) has the permission to SELECT
261 # on some INFORMATION_SCHEMA table.
262 --echo # Establish connection testuser1 (user=testuser1)
263 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
264 connect (testuser1, localhost, testuser1, , db_datadict);
265 SELECT table_schema,table_name FROM information_schema.tables
266 WHERE table_schema = 'information_schema' AND table_name = 'TABLES';
267
268 # Check 2: Show the privileges of the user on some INFORMATION_SCHEMA tables.
269 SELECT * FROM information_schema.table_privileges
270 WHERE table_schema = 'information_schema';
271 SELECT * FROM information_schema.schema_privileges
272 WHERE table_schema = 'information_schema';
273
274 # Check 3: Show the following
275 # 1. If a simple user (testuser1) has the privilege to create a VIEW
276 # than this VIEW could use a SELECT on an INFORMATION_SCHEMA table.
277 # 2. This user (testuser1) is also able to GRANT the SELECT privilege
278 # on this VIEW to another user (testuser2).
279 # 3. The other user (testuser2) must be able to SELECT on this VIEW
280 # but gets a different result set than testuser1.
281 CREATE VIEW db_datadict.v2 AS
282 SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
283 FROM information_schema.tables WHERE table_schema = 'db_datadict';
284 SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
285 FROM db_datadict.v2;
286 SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
287 FROM information_schema.tables WHERE table_schema = 'db_datadict';
288 GRANT SELECT ON db_datadict.v2 to testuser2@localhost;
289 #
290 --echo # Establish connection testuser2 (user=testuser2)
291 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
292 connect (testuser2, localhost, testuser2, , db_datadict);
293 SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
294 FROM db_datadict.v2;
295 SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
296 FROM information_schema.tables WHERE table_schema = 'db_datadict';
297
298 # Cleanup
299 --echo # Switch to connection default and close connections testuser1 and testuser2
300 connection default;
301 disconnect testuser1;
302 disconnect testuser2;
303 DROP USER 'testuser1'@'localhost';
304 DROP USER 'testuser2'@'localhost';
305 DROP DATABASE db_datadict;
306
307
308 --echo #########################################################################
309 --echo # Testcase 3.2.1.19
310 --echo #########################################################################
311 # Ensure that no other privilege on an INFORMATION_SCHEMA table is granted, or
312 # may be granted, to any user.
313 #
314 --error 0,ER_CANNOT_USER
315 DROP USER 'testuser1'@'localhost';
316 CREATE USER 'testuser1'@'localhost';
317
318 # Initial privileges on the INFORMATION_SCHEMA tables (empty result sets)
319 let $my_select1 = SELECT 'empty result set was expected' AS my_col
320 FROM information_schema.schema_privileges
321 WHERE table_schema = 'information_schema';
322 let $my_select2 = SELECT 'empty result set was expected' AS my_col
323 FROM information_schema.table_privileges
324 WHERE table_schema = 'information_schema';
325 let $my_select3 = SELECT 'empty result set was expected' AS my_col
326 FROM information_schema.column_privileges
327 WHERE table_schema = 'information_schema';
328 eval $my_select1;
329 eval $my_select2;
330 eval $my_select3;
331
332 #FIXME: check GRANT on IS
333 --error ER_DBACCESS_DENIED_ERROR
334 GRANT ALTER ON information_schema.*
335 TO 'testuser1'@'localhost';
336
337 #FIXME: check GRANT on IS
338 --error ER_DBACCESS_DENIED_ERROR
339 GRANT ALTER ROUTINE ON information_schema.*
340 TO 'testuser1'@'localhost';
341
342 #FIXME: check GRANT on IS
343 --error ER_DBACCESS_DENIED_ERROR
344 GRANT CREATE ON information_schema.*
345 TO 'testuser1'@'localhost';
346
347 #FIXME: check GRANT on IS
348 --error ER_DBACCESS_DENIED_ERROR
349 GRANT CREATE ROUTINE ON information_schema.*
350 TO 'testuser1'@'localhost';
351
352 #FIXME: check GRANT on IS
353 --error ER_DBACCESS_DENIED_ERROR
354 GRANT CREATE TEMPORARY TABLES ON information_schema.*
355 TO 'testuser1'@'localhost';
356
357 #FIXME: check GRANT on IS
358 --error ER_DBACCESS_DENIED_ERROR
359 GRANT DELETE ON information_schema.*
360 TO 'testuser1'@'localhost';
361
362 #FIXME: check GRANT on IS
363 --error ER_DBACCESS_DENIED_ERROR
364 GRANT DROP ON information_schema.*
365 TO 'testuser1'@'localhost';
366
367 #FIXME: check GRANT on IS
368 --error ER_DBACCESS_DENIED_ERROR
369 GRANT EXECUTE ON information_schema.*
370 TO 'testuser1'@'localhost';
371
372 #FIXME: check GRANT on IS
373 --error ER_DBACCESS_DENIED_ERROR
374 GRANT INDEX ON information_schema.*
375 TO 'testuser1'@'localhost';
376
377 #FIXME: check GRANT on IS
378 --error ER_DBACCESS_DENIED_ERROR
379 GRANT INSERT ON information_schema.*
380 TO 'testuser1'@'localhost';
381
382 #FIXME: check GRANT on IS
383 --error ER_DBACCESS_DENIED_ERROR
384 GRANT LOCK TABLES ON information_schema.*
385 TO 'testuser1'@'localhost';
386
387 #FIXME: check GRANT on IS
388 --error ER_DBACCESS_DENIED_ERROR
389 GRANT UPDATE ON information_schema.*
390 TO 'testuser1'@'localhost';
391
392 # Has something accidently changed?
393 eval $my_select1;
394 eval $my_select2;
395 eval $my_select3;
396
397 # Cleanup
398 DROP USER 'testuser1'@'localhost';
399
400
401 --echo #########################################################################
402 --echo # Testcase 3.2.1.16
403 --echo #########################################################################
404 # Ensure that no user may use any INFORMATION_SCHEMA table to determine any
405 # information on a database and/or its structure unless authorized to get that
406 # information.
407 # Note: The plan is to create a new database and objects within it so that
408 # any INFORMATION_SCHEMA table gets additional rows if possible.
409 # A user having no rights on the new database and no rights on objects
410 # must nowhere see tha name of the new database.
411 --source suite/funcs_1/datadict/basics_mixed3.inc
412
413 --disable_warnings
414 DROP DATABASE IF EXISTS db_datadict;
415 --enable_warnings
416 CREATE DATABASE db_datadict;
417 --replace_result $engine_type <some_engine>
418 eval
419 CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT NOT NULL, f3 BIGINT,
420 PRIMARY KEY(f1))
421 ENGINE = $engine_type;
422 CREATE UNIQUE INDEX UIDX ON db_datadict.t1(f3);
423 CREATE PROCEDURE db_datadict.sproc1() SELECT 'db_datadict';
424 CREATE FUNCTION db_datadict.func1() RETURNS INT RETURN 0;
425 CREATE TRIGGER db_datadict.trig1 BEFORE INSERT ON db_datadict.t1
426 FOR EACH ROW SET @aux = 1;
427 CREATE VIEW db_datadict.v1 AS SELECT * FROM db_datadict.t1;
428 CREATE VIEW db_datadict.v2 AS SELECT * FROM information_schema.tables;
429
430 --source suite/funcs_1/datadict/basics_mixed3.inc
431
432 --error 0,ER_CANNOT_USER
433 DROP USER 'testuser1'@'localhost';
434 CREATE USER 'testuser1'@'localhost';
435 GRANT ALL ON test.* TO 'testuser1'@'localhost';
436
437 --echo # Establish connection testuser1 (user=testuser1)
438 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
439 connect (testuser1, localhost, testuser1, , test);
440 --source suite/funcs_1/datadict/basics_mixed3.inc
441
442 # Cleanup
443 --echo # Switch to connection default and close connections testuser1 and testuser2
444 connection default;
445 disconnect testuser1;
446 DROP USER 'testuser1'@'localhost';
447 DROP DATABASE db_datadict;
448
449 --echo ########################################################################
450 --echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
451 --echo # DDL on INFORMATION_SCHEMA tables are not supported
452 --echo ########################################################################
453 # Thorough tests checking the requirements above per every INFORMATION_SCHEMA
454 # table are within other scripts.
455 # We check here only that the requirement is fulfilled even when using a
456 # STORED PROCEDURE.
457 --disable_warnings
458 DROP PROCEDURE IF EXISTS test.p1;
459 --enable_warnings
460 --error ER_DBACCESS_DENIED_ERROR
461 CREATE PROCEDURE test.p1()
462 INSERT INTO information_schema.tables
463 SELECT * FROM information_schema.tables LIMIT 1;
464
465 CREATE PROCEDURE test.p1()
466 UPDATE information_schema.columns SET table_schema = 'garbage';
467 --error ER_DBACCESS_DENIED_ERROR
468 CALL test.p1();
469
470 DROP PROCEDURE test.p1;
471 --error ER_DBACCESS_DENIED_ERROR
472 CREATE PROCEDURE test.p1()
473 DELETE FROM information_schema.schemata;
474
475
476 --echo #########################################################################
477 --echo # Testcase 3.2.17.1+3.2.17.2: To be implemented outside of this script
478 --echo #########################################################################
479 # 3.2.17.1 Ensure that every INFORMATION_SCHEMA table shows all the correct
480 # information, and no incorrect information, for a database to which
481 # 100 different users, each of which has a randomly issued set of
482 # privileges and access to a randomly chosen set of database objects,
483 # have access.
484 # The database should contain a mixture of all types of database
485 # objects (i.e. tables, views, stored procedures, triggers).
486 # 3.2.17.2 Ensure that every INFORMATION_SCHEMA table shows all the correct
487 # information, and no incorrect information, for 10 different
488 # databases to which 50 different users, each of which has a randomly
489 # issued set of privileges and access to a randomly chosen set of
490 # database objects in two or more of the databases, have access.
491 # The databases should each contain a mixture of all types of database
492 # objects (i.e. tables, views, stored procedures, triggers).
493 #
494 # Note(mleich): These requirements are kept here so that they do not get lost.
495 # The tests are not yet implemented.
496 # If they are ever developed than they should be stored in other
497 # scripts. They will have most probably a long runtime because
498 # the current INFORMATION_SCHEMA implementation has some performance
499 # issues if a lot of users, privileges and objects are involved.
500 #