1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
|
# suite/funcs_1/datadict/is_views.inc
#
# Check the layout of information_schema.views and the impact of
# CREATE/ALTER/DROP TABLE/VIEW/SCHEMA ... on it.
#
# Note:
# - This test should not check storage engine properties.
# - Please do not change the storage engines used within this test
# except you know that the impact is acceptable.
# Some storage engines might not support the modification of
# properties like in the following tests.
#
# Author:
# 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
# testsuite funcs_1
# Create this script based on older scripts and new code.
# Last Change:
# 2008-06-11 mleich Move t/is_views.test to this file and
# create variants for embedded/non embedded server.
#
let $engine_type = MEMORY;
let $other_engine_type = MyISAM;
let $is_table = VIEWS;
# The table INFORMATION_SCHEMA.VIEWS must exist
eval SHOW TABLES FROM information_schema LIKE '$is_table';
--echo #######################################################################
--echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
--echo #######################################################################
# Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
# statement, just as if it were an ordinary user-defined table.
#
--source suite/funcs_1/datadict/is_table_query.inc
--echo #########################################################################
--echo # Testcase 3.2.13.1: INFORMATION_SCHEMA.VIEWS layout
--echo #########################################################################
# Ensure that the INFORMATION_SCHEMA.VIEWS table has the following columns,
# in the following order:
#
# TABLE_CATALOG (always shows NULL),
# TABLE_SCHEMA (shows the database, or schema, in which an accessible
# view resides),
# TABLE_NAME (shows the name of a view accessible to the current user),
# VIEW_DEFINITION (shows the SELECT statement that makes up the
# view's definition),
# CHECK_OPTION (shows the value of the WITH CHECK OPTION clause used to define
# the view, either NONE, LOCAL or CASCADED),
# IS_UPDATABLE (shows whether the view is an updatable view),
# DEFINER (added with 5.0.14),
# SECURITY_TYPE (added with 5.0.14).
#
--source suite/funcs_1/datadict/datadict_bug_12777.inc
eval DESCRIBE information_schema.$is_table;
--source suite/funcs_1/datadict/datadict_bug_12777.inc
--replace_result ENGINE=MyISAM "" ENGINE=Aria "" " PAGE_CHECKSUM=1" "" " PAGE_CHECKSUM=0" ""
eval SHOW CREATE TABLE information_schema.$is_table;
--source suite/funcs_1/datadict/datadict_bug_12777.inc
eval SHOW COLUMNS FROM information_schema.$is_table;
# Note: Retrieval of information within information_schema.columns about
# information_schema.views is in is_columns_is.test.
# Show that TABLE_CATALOG is always NULL.
SELECT table_catalog, table_schema, table_name
FROM information_schema.views WHERE table_catalog IS NOT NULL;
--echo ################################################################################
--echo # Testcase 3.2.13.2 + 3.2.13.3: INFORMATION_SCHEMA.VIEWS accessible information
--echo ################################################################################
# 3.2.13.2: Ensure that the table shows the relevant information on every view for
# which the current user or PUBLIC has the SHOW CREATE VIEW privilege.
# 3.2.13.3: Ensure that the table does not show any information on any views for which
# the current user and PUBLIC have no SHOW CREATE VIEW privilege.
#
--disable_warnings
DROP DATABASE IF EXISTS db_datadict;
--enable_warnings
CREATE DATABASE db_datadict;
--error 0,ER_CANNOT_USER
DROP USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
--error 0,ER_CANNOT_USER
DROP USER 'testuser2'@'localhost';
CREATE USER 'testuser2'@'localhost';
--error 0,ER_CANNOT_USER
DROP USER 'test_no_views'@'localhost';
CREATE USER 'test_no_views'@'localhost';
--replace_result $engine_type <engine_type>
eval
CREATE TABLE db_datadict.t1(f1 INT, f2 INT, f3 INT)
ENGINE = $engine_type;
CREATE VIEW db_datadict.v_granted_to_1 AS SELECT * FROM db_datadict.t1;
CREATE VIEW db_datadict.v_granted_glob AS SELECT f2, f3 FROM db_datadict.t1;
GRANT SELECT ON db_datadict.t1 TO 'testuser1'@'localhost';
GRANT SELECT ON db_datadict.v_granted_to_1 TO 'testuser1'@'localhost';
GRANT SHOW VIEW, CREATE VIEW ON db_datadict.* TO 'testuser2'@'localhost';
let $select = SELECT * FROM information_schema.views
WHERE table_schema = 'db_datadict' ORDER BY table_name;
eval $select;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
connect (testuser1, localhost, testuser1, , test);
eval $select;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
connect (testuser2, localhost, testuser2, , test);
eval $select;
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
connect (test_no_views, localhost, test_no_views, , test);
eval $select;
# Cleanup
connection default;
disconnect testuser1;
disconnect testuser2;
disconnect test_no_views;
DROP USER 'testuser1'@'localhost';
DROP USER 'testuser2'@'localhost';
DROP USER 'test_no_views'@'localhost';
DROP DATABASE db_datadict;
--echo #########################################################################
--echo # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.VIEWS modifications
--echo #########################################################################
# 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
# column) automatically inserts all relevant information on that
# object into every appropriate INFORMATION_SCHEMA table.
# 3.2.1.14: Ensure that the alteration of any existing database object
# automatically updates all relevant information on that object in
# every appropriate INFORMATION_SCHEMA table.
# 3.2.1.15: Ensure that the dropping of any existing database object
# automatically deletes all relevant information on that object from
# every appropriate INFORMATION_SCHEMA table.
#
--disable_warnings
DROP TABLE IF EXISTS test.t1_my_table;
DROP DATABASE IF EXISTS db_datadict;
--enable_warnings
CREATE DATABASE db_datadict;
--replace_result $engine_type <engine_type>
eval
CREATE TABLE test.t1_table (f1 BIGINT, f2 CHAR(10))
DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
ENGINE = $engine_type;
--error 0,ER_CANNOT_USER
DROP USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
# Check just created VIEW
SELECT * FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_schema, table_name;
CREATE VIEW test.t1_view AS SELECT DISTINCT f1 FROM test.t1_table;
CREATE ALGORITHM=MERGE VIEW test.t1_view1 AS SELECT f1 FROM test.t1_table;
CREATE ALGORITHM=TEMPTABLE VIEW test.t1_view2 AS SELECT f1 FROM test.t1_table;
SELECT * FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_schema, table_name;
DROP VIEW test.t1_view1;
DROP VIEW test.t1_view2;
#
# Check modification of DEFINER, SECURITY_TYPE, IS_UPDATABLE, VIEW_DEFINITION,
# CHECK_OPTION
SELECT table_name,definer FROM information_schema.views
WHERE table_name = 't1_view';
ALTER DEFINER = 'testuser1'@'localhost' VIEW test.t1_view AS
SELECT DISTINCT f1 FROM test.t1_table;
# The next result set could suffer from
# Bug#22763 Disrepancy between SHOW CREATE VIEW and I_S.VIEWS
# because the VIEW definition is missing.
# Therefore we exclude the problematic columns from the result set.
SELECT table_name,definer,security_type FROM information_schema.views
WHERE table_name LIKE 't1_%';
ALTER DEFINER = 'root'@'localhost' SQL SECURITY INVOKER VIEW test.t1_view AS
SELECT f1 FROM test.t1_table WITH LOCAL CHECK OPTION;
SELECT table_name,definer,security_type FROM information_schema.views
WHERE table_name LIKE 't1_%';
#
# Check modification of TABLE_SCHEMA
SELECT table_schema,table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_schema,table_name;
--error ER_FORBID_SCHEMA_CHANGE
RENAME TABLE test.t1_view TO db_datadict.t1_view;
# Workaround for missing move to another database
DROP VIEW test.t1_view;
CREATE VIEW db_datadict.t1_view AS SELECT * FROM test.t1_table;
SELECT table_schema,table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_schema,table_name;
#
# Check modification of TABLE_NAME
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
RENAME TABLE db_datadict.t1_view TO db_datadict.t1_viewx;
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
#
# Check impact of DROP VIEW
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
DROP VIEW db_datadict.t1_viewx;
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
CREATE VIEW db_datadict.t1_view AS SELECT * FROM test.t1_table;
#
# Check impact of DROP base TABLE of VIEW
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
DROP TABLE test.t1_table;
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
--replace_result $engine_type <engine_type>
eval
CREATE TABLE test.t1_table (f1 BIGINT, f2 CHAR(10))
DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci COMMENT = 'Initial Comment'
ENGINE = $engine_type;
#
# Check impact of DROP SCHEMA
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
DROP DATABASE db_datadict;
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
# Cleanup
DROP USER 'testuser1'@'localhost';
DROP TABLE test.t1_table;
--echo ########################################################################
--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
--echo # DDL on INFORMATION_SCHEMA table are not supported
--echo ########################################################################
# 3.2.1.3: Ensure that no user may execute an INSERT statement on any
# INFORMATION_SCHEMA table.
# 3.2.1.4: Ensure that no user may execute an UPDATE statement on any
# INFORMATION_SCHEMA table.
# 3.2.1.5: Ensure that no user may execute a DELETE statement on any
# INFORMATION_SCHEMA table.
# 3.2.1.8: Ensure that no user may create an index on an
# INFORMATION_SCHEMA table.
# 3.2.1.9: Ensure that no user may alter the definition of an
# INFORMATION_SCHEMA table.
# 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
# 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
# other database.
# 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
# in an INFORMATION_SCHEMA table.
#
--disable_warnings
DROP DATABASE IF EXISTS db_datadict;
--enable_warnings
CREATE DATABASE db_datadict;
CREATE VIEW db_datadict.v1 AS SELECT 1;
--error ER_DBACCESS_DENIED_ERROR
INSERT INTO information_schema.views
SELECT * FROM information_schema.views;
--error ER_DBACCESS_DENIED_ERROR
INSERT INTO information_schema.views(table_schema, table_name)
VALUES ('db2', 'v2');
--error ER_DBACCESS_DENIED_ERROR,ER_NON_UPDATABLE_TABLE
UPDATE information_schema.views SET table_schema = 'test'
WHERE table_name = 't1';
--error ER_DBACCESS_DENIED_ERROR
DELETE FROM information_schema.views WHERE table_name = 't1';
--error ER_DBACCESS_DENIED_ERROR
TRUNCATE information_schema.views;
--error ER_DBACCESS_DENIED_ERROR
CREATE INDEX my_idx_on_views ON information_schema.views(table_schema);
--error ER_DBACCESS_DENIED_ERROR
ALTER TABLE information_schema.views DROP PRIMARY KEY;
--error ER_DBACCESS_DENIED_ERROR
ALTER TABLE information_schema.views ADD f1 INT;
--error ER_DBACCESS_DENIED_ERROR
DROP TABLE information_schema.views;
--error ER_DBACCESS_DENIED_ERROR
ALTER TABLE information_schema.views RENAME db_datadict.views;
--error ER_DBACCESS_DENIED_ERROR
ALTER TABLE information_schema.views RENAME information_schema.xviews;
# Cleanup
DROP DATABASE db_datadict;
|