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
|
SHOW TABLES FROM information_schema LIKE 'VIEWS';
Tables_in_information_schema (VIEWS)
VIEWS
#######################################################################
# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
#######################################################################
DROP VIEW IF EXISTS test.v1;
DROP PROCEDURE IF EXISTS test.p1;
DROP FUNCTION IF EXISTS test.f1;
CREATE VIEW test.v1 AS SELECT * FROM information_schema.VIEWS;
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.VIEWS;
CREATE FUNCTION test.f1() returns BIGINT
BEGIN
DECLARE counter BIGINT DEFAULT NULL;
SELECT COUNT(*) INTO counter FROM information_schema.VIEWS;
RETURN counter;
END//
# Attention: The printing of the next result sets is disabled.
SELECT * FROM information_schema.VIEWS;
SELECT * FROM test.v1;
CALL test.p1;
SELECT test.f1();
DROP VIEW test.v1;
DROP PROCEDURE test.p1;
DROP FUNCTION test.f1;
#########################################################################
# Testcase 3.2.13.1: INFORMATION_SCHEMA.VIEWS layout
#########################################################################
DESCRIBE information_schema.VIEWS;
Field Type Null Key Default Extra
TABLE_CATALOG varchar(512) NO NULL
TABLE_SCHEMA varchar(64) NO NULL
TABLE_NAME varchar(64) NO NULL
VIEW_DEFINITION longtext NO NULL
CHECK_OPTION varchar(8) NO NULL
IS_UPDATABLE varchar(3) NO NULL
DEFINER varchar(384) NO NULL
SECURITY_TYPE varchar(7) NO NULL
CHARACTER_SET_CLIENT varchar(32) NO NULL
COLLATION_CONNECTION varchar(32) NO NULL
ALGORITHM varchar(10) NO NULL
SHOW CREATE TABLE information_schema.VIEWS;
Table Create Table
VIEWS CREATE TEMPORARY TABLE `VIEWS` (
`TABLE_CATALOG` varchar(512) NOT NULL,
`TABLE_SCHEMA` varchar(64) NOT NULL,
`TABLE_NAME` varchar(64) NOT NULL,
`VIEW_DEFINITION` longtext NOT NULL,
`CHECK_OPTION` varchar(8) NOT NULL,
`IS_UPDATABLE` varchar(3) NOT NULL,
`DEFINER` varchar(384) NOT NULL,
`SECURITY_TYPE` varchar(7) NOT NULL,
`CHARACTER_SET_CLIENT` varchar(32) NOT NULL,
`COLLATION_CONNECTION` varchar(32) NOT NULL,
`ALGORITHM` varchar(10) NOT NULL
) DEFAULT CHARSET=utf8mb3
SHOW COLUMNS FROM information_schema.VIEWS;
Field Type Null Key Default Extra
TABLE_CATALOG varchar(512) NO NULL
TABLE_SCHEMA varchar(64) NO NULL
TABLE_NAME varchar(64) NO NULL
VIEW_DEFINITION longtext NO NULL
CHECK_OPTION varchar(8) NO NULL
IS_UPDATABLE varchar(3) NO NULL
DEFINER varchar(384) NO NULL
SECURITY_TYPE varchar(7) NO NULL
CHARACTER_SET_CLIENT varchar(32) NO NULL
COLLATION_CONNECTION varchar(32) NO NULL
ALGORITHM varchar(10) NO NULL
SELECT table_catalog, table_schema, table_name
FROM information_schema.views WHERE table_catalog <> 'def';
table_catalog table_schema table_name
################################################################################
# Testcase 3.2.13.2 + 3.2.13.3: INFORMATION_SCHEMA.VIEWS accessible information
################################################################################
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
DROP USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
DROP USER 'testuser2'@'localhost';
CREATE USER 'testuser2'@'localhost';
DROP USER 'test_no_views'@'localhost';
CREATE USER 'test_no_views'@'localhost';
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';
SELECT * FROM information_schema.views
WHERE table_schema = 'db_datadict' ORDER BY table_name;
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM
def db_datadict v_granted_glob select `db_datadict`.`t1`.`f2` AS `f2`,`db_datadict`.`t1`.`f3` AS `f3` from `db_datadict`.`t1` NONE YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED
def db_datadict v_granted_to_1 select `db_datadict`.`t1`.`f1` AS `f1`,`db_datadict`.`t1`.`f2` AS `f2`,`db_datadict`.`t1`.`f3` AS `f3` from `db_datadict`.`t1` NONE YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED
connect testuser1, localhost, testuser1, ,"*NO-ONE*";
SELECT * FROM information_schema.views
WHERE table_schema = 'db_datadict' ORDER BY table_name;
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM
def db_datadict v_granted_glob NONE YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED
def db_datadict v_granted_to_1 NONE YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED
connect testuser2, localhost, testuser2, ,"*NO-ONE*";
SELECT * FROM information_schema.views
WHERE table_schema = 'db_datadict' ORDER BY table_name;
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM
def db_datadict v_granted_glob NONE YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED
def db_datadict v_granted_to_1 NONE YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED
connect test_no_views, localhost, test_no_views, ,"*NO-ONE*";
SELECT * FROM information_schema.views
WHERE table_schema = 'db_datadict' ORDER BY table_name;
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM
def db_datadict v_granted_glob NONE YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED
def db_datadict v_granted_to_1 NONE YES root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED
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;
#########################################################################
# 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.VIEWS modifications
#########################################################################
DROP TABLE IF EXISTS test.t1_my_table;
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
CREATE TABLE test.t1_table (f1 BIGINT, f2 CHAR(10))
DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
ENGINE = <engine_type>;
DROP USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
SELECT * FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_schema, table_name;
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM
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;
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION ALGORITHM
def test t1_view select distinct `test`.`t1_table`.`f1` AS `f1` from `test`.`t1_table` NONE NO root@localhost DEFINER latin1 latin1_swedish_ci UNDEFINED
def test t1_view1 select `test`.`t1_table`.`f1` AS `f1` from `test`.`t1_table` NONE YES root@localhost DEFINER latin1 latin1_swedish_ci MERGE
def test t1_view2 select `test`.`t1_table`.`f1` AS `f1` from `test`.`t1_table` NONE NO root@localhost DEFINER latin1 latin1_swedish_ci TEMPTABLE
DROP VIEW test.t1_view1;
DROP VIEW test.t1_view2;
SELECT table_name,definer FROM information_schema.views
WHERE table_name = 't1_view';
table_name definer
t1_view root@localhost
ALTER DEFINER = 'testuser1'@'localhost' VIEW test.t1_view AS
SELECT DISTINCT f1 FROM test.t1_table;
SELECT table_name,definer,security_type FROM information_schema.views
WHERE table_name LIKE 't1_%';
table_name definer security_type
t1_view testuser1@localhost DEFINER
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_%';
table_name definer security_type
t1_view root@localhost INVOKER
SELECT table_schema,table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_schema,table_name;
table_schema table_name
test t1_view
RENAME TABLE test.t1_view TO db_datadict.t1_view;
ERROR HY000: Changing schema from 'test' to 'db_datadict' is not allowed
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;
table_schema table_name
db_datadict t1_view
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
table_name
t1_view
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;
table_name
t1_viewx
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
table_name
t1_viewx
DROP VIEW db_datadict.t1_viewx;
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
table_name
CREATE VIEW db_datadict.t1_view AS SELECT * FROM test.t1_table;
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
table_name
t1_view
DROP TABLE test.t1_table;
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
table_name
t1_view
CREATE TABLE test.t1_table (f1 BIGINT, f2 CHAR(10))
DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci COMMENT = 'Initial Comment'
ENGINE = <engine_type>;
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
table_name
t1_view
DROP DATABASE db_datadict;
SELECT table_name FROM information_schema.views
WHERE table_name LIKE 't1_%'
ORDER BY table_name;
table_name
DROP USER 'testuser1'@'localhost';
DROP TABLE test.t1_table;
########################################################################
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
# DDL on INFORMATION_SCHEMA table are not supported
########################################################################
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
CREATE VIEW db_datadict.v1 AS SELECT 1;
INSERT INTO information_schema.views
SELECT * FROM information_schema.views;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
INSERT INTO information_schema.views(table_schema, table_name)
VALUES ('db2', 'v2');
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
UPDATE information_schema.views SET table_schema = 'test'
WHERE table_name = 't1';
Got one of the listed errors
DELETE FROM information_schema.views WHERE table_name = 't1';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
TRUNCATE information_schema.views;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
CREATE INDEX my_idx_on_views ON information_schema.views(table_schema);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.views DROP PRIMARY KEY;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.views ADD f1 INT;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE information_schema.views;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.views RENAME db_datadict.views;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.views RENAME information_schema.xviews;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP DATABASE db_datadict;
|
