Debian Codegraph @

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

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