Debian Codegraph @

mysql-test/suite/funcs_1/r/is_column_privileges.result

raw
1 SHOW TABLES FROM information_schema LIKE 'COLUMN_PRIVILEGES';
2 Tables_in_information_schema (COLUMN_PRIVILEGES)
3 COLUMN_PRIVILEGES
4 #######################################################################
5 # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
6 #######################################################################
7 DROP VIEW IF EXISTS test.v1;
8 DROP PROCEDURE IF EXISTS test.p1;
9 DROP FUNCTION IF EXISTS test.f1;
10 CREATE VIEW test.v1 AS SELECT * FROM information_schema.COLUMN_PRIVILEGES;
11 CREATE PROCEDURE test.p1() SELECT * FROM information_schema.COLUMN_PRIVILEGES;
12 CREATE FUNCTION test.f1() returns BIGINT
13 BEGIN
14 DECLARE counter BIGINT DEFAULT NULL;
15 SELECT COUNT(*) INTO counter FROM information_schema.COLUMN_PRIVILEGES;
16 RETURN counter;
17 END//
18 # Attention: The printing of the next result sets is disabled.
19 SELECT * FROM information_schema.COLUMN_PRIVILEGES;
20 SELECT * FROM test.v1;
21 CALL test.p1;
22 SELECT test.f1();
23 DROP VIEW test.v1;
24 DROP PROCEDURE test.p1;
25 DROP FUNCTION test.f1;
26 #########################################################################
27 # Testcase 3.2.5.1: INFORMATION_SCHEMA.COLUMN_PRIVILEGES layout
28 #########################################################################
29 DESCRIBE information_schema.COLUMN_PRIVILEGES;
30 Field Type Null Key Default Extra
31 GRANTEE varchar(292) NO
32 TABLE_CATALOG varchar(512) NO
33 TABLE_SCHEMA varchar(64) NO
34 TABLE_NAME varchar(64) NO
35 COLUMN_NAME varchar(64) NO
36 PRIVILEGE_TYPE varchar(64) NO
37 IS_GRANTABLE varchar(3) NO
38 SHOW CREATE TABLE information_schema.COLUMN_PRIVILEGES;
39 Table Create Table
40 COLUMN_PRIVILEGES CREATE TEMPORARY TABLE `COLUMN_PRIVILEGES` (
41 `GRANTEE` varchar(292) NOT NULL DEFAULT '',
42 `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
43 `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
44 `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
45 `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
46 `PRIVILEGE_TYPE` varchar(64) NOT NULL DEFAULT '',
47 `IS_GRANTABLE` varchar(3) NOT NULL DEFAULT ''
48 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3
49 SHOW COLUMNS FROM information_schema.COLUMN_PRIVILEGES;
50 Field Type Null Key Default Extra
51 GRANTEE varchar(292) NO
52 TABLE_CATALOG varchar(512) NO
53 TABLE_SCHEMA varchar(64) NO
54 TABLE_NAME varchar(64) NO
55 COLUMN_NAME varchar(64) NO
56 PRIVILEGE_TYPE varchar(64) NO
57 IS_GRANTABLE varchar(3) NO
58 SELECT table_catalog, table_schema, table_name, column_name, privilege_type
59 FROM information_schema.column_privileges WHERE table_catalog IS NOT NULL;
60 table_catalog table_schema table_name column_name privilege_type
61 ######################################################################
62 # Testcase 3.2.5.2+3.2.5.3+3.2.5.4:
63 # INFORMATION_SCHEMA.COLUMN_PRIVILEGES accessible information
64 ######################################################################
65 DROP DATABASE IF EXISTS db_datadict;
66 CREATE DATABASE db_datadict;
67 CREATE TABLE db_datadict.t1 (f1 INT, f2 DECIMAL, f3 TEXT)
68 ENGINE = <other_engine_type>;
69 USE db_datadict;
70 DROP USER 'testuser1'@'localhost';
71 CREATE USER 'testuser1'@'localhost';
72 DROP USER 'testuser2'@'localhost';
73 CREATE USER 'testuser2'@'localhost';
74 DROP USER 'testuser3'@'localhost';
75 CREATE USER 'testuser3'@'localhost';
76 GRANT SELECT(f1, f3) ON db_datadict.t1 TO 'testuser1'@'localhost';
77 GRANT INSERT(f1) ON db_datadict.t1 TO 'testuser1'@'localhost';
78 GRANT UPDATE(f2) ON db_datadict.t1 TO 'testuser1'@'localhost';
79 GRANT SELECT(f2) ON db_datadict.t1 TO 'testuser2'@'localhost';
80 GRANT INSERT, SELECT ON db_datadict.t1 TO 'testuser3'@'localhost';
81 GRANT SELECT(f3) ON db_datadict.t1 TO 'testuser3'@'localhost';
82 GRANT INSERT, SELECT ON db_datadict.t1 TO 'testuser3'@'localhost'
83 WITH GRANT OPTION;
84 GRANT ALL ON db_datadict.* TO 'testuser3'@'localhost';
85 SELECT * FROM information_schema.column_privileges
86 WHERE grantee LIKE '''testuser%'''
87 ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
88 GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
89 'testuser1'@'localhost' def db_datadict t1 f1 INSERT NO
90 'testuser1'@'localhost' def db_datadict t1 f1 SELECT NO
91 'testuser1'@'localhost' def db_datadict t1 f2 UPDATE NO
92 'testuser1'@'localhost' def db_datadict t1 f3 SELECT NO
93 'testuser2'@'localhost' def db_datadict t1 f2 SELECT NO
94 'testuser3'@'localhost' def db_datadict t1 f3 SELECT YES
95 GRANT UPDATE(f3) ON db_datadict.t1 TO 'testuser1'@'localhost'
96 WITH GRANT OPTION;
97 SELECT * FROM information_schema.column_privileges
98 WHERE grantee LIKE '''testuser%'''
99 ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
100 GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
101 'testuser1'@'localhost' def db_datadict t1 f1 INSERT YES
102 'testuser1'@'localhost' def db_datadict t1 f1 SELECT YES
103 'testuser1'@'localhost' def db_datadict t1 f2 UPDATE YES
104 'testuser1'@'localhost' def db_datadict t1 f3 SELECT YES
105 'testuser1'@'localhost' def db_datadict t1 f3 UPDATE YES
106 'testuser2'@'localhost' def db_datadict t1 f2 SELECT NO
107 'testuser3'@'localhost' def db_datadict t1 f3 SELECT YES
108 # Establish connection testuser1 (user=testuser1)
109 SELECT * FROM information_schema.column_privileges
110 WHERE grantee LIKE '''testuser%'''
111 ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
112 GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
113 'testuser1'@'localhost' def db_datadict t1 f1 INSERT YES
114 'testuser1'@'localhost' def db_datadict t1 f1 SELECT YES
115 'testuser1'@'localhost' def db_datadict t1 f2 UPDATE YES
116 'testuser1'@'localhost' def db_datadict t1 f3 SELECT YES
117 'testuser1'@'localhost' def db_datadict t1 f3 UPDATE YES
118 # Establish connection testuser2 (user=testuser2)
119 SELECT * FROM information_schema.column_privileges
120 WHERE grantee LIKE '''testuser%'''
121 ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
122 GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
123 'testuser2'@'localhost' def db_datadict t1 f2 SELECT NO
124 # Establish connection testuser3 (user=testuser3)
125 # FIXME: Is it correct that granted TABLES do not occur in COLUMN_PRIVILEGES?
126 SELECT * FROM information_schema.table_privileges
127 WHERE grantee LIKE '''testuser%'''
128 ORDER BY grantee,table_schema,table_name,privilege_type;
129 GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
130 'testuser3'@'localhost' def db_datadict t1 INSERT YES
131 'testuser3'@'localhost' def db_datadict t1 SELECT YES
132 SELECT * FROM information_schema.schema_privileges
133 WHERE grantee LIKE '''testuser%'''
134 ORDER BY grantee,table_schema,privilege_type;
135 GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
136 'testuser3'@'localhost' def db_datadict ALTER NO
137 'testuser3'@'localhost' def db_datadict ALTER ROUTINE NO
138 'testuser3'@'localhost' def db_datadict CREATE NO
139 'testuser3'@'localhost' def db_datadict CREATE ROUTINE NO
140 'testuser3'@'localhost' def db_datadict CREATE TEMPORARY TABLES NO
141 'testuser3'@'localhost' def db_datadict CREATE VIEW NO
142 'testuser3'@'localhost' def db_datadict DELETE NO
143 'testuser3'@'localhost' def db_datadict DROP NO
144 'testuser3'@'localhost' def db_datadict EVENT NO
145 'testuser3'@'localhost' def db_datadict EXECUTE NO
146 'testuser3'@'localhost' def db_datadict INDEX NO
147 'testuser3'@'localhost' def db_datadict INSERT NO
148 'testuser3'@'localhost' def db_datadict LOCK TABLES NO
149 'testuser3'@'localhost' def db_datadict REFERENCES NO
150 'testuser3'@'localhost' def db_datadict SELECT NO
151 'testuser3'@'localhost' def db_datadict SHOW VIEW NO
152 'testuser3'@'localhost' def db_datadict TRIGGER NO
153 'testuser3'@'localhost' def db_datadict UPDATE NO
154 SELECT * FROM information_schema.column_privileges
155 WHERE grantee LIKE '''testuser%'''
156 ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
157 GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
158 'testuser3'@'localhost' def db_datadict t1 f3 SELECT YES
159 GRANT SELECT(f1, f3) ON db_datadict.t1 TO 'testuser2'@'localhost';
160 # FIXME: Is it intended that *my* grants to others are *NOT* shown here?
161 SELECT * FROM information_schema.column_privileges
162 WHERE grantee LIKE '''testuser%'''
163 ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
164 GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
165 'testuser3'@'localhost' def db_datadict t1 f3 SELECT YES
166 # Switch to connection testuser2 (user=testuser2)
167 SELECT * FROM information_schema.column_privileges
168 WHERE grantee LIKE '''testuser%'''
169 ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
170 GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
171 'testuser2'@'localhost' def db_datadict t1 f1 SELECT NO
172 'testuser2'@'localhost' def db_datadict t1 f2 SELECT NO
173 'testuser2'@'localhost' def db_datadict t1 f3 SELECT NO
174 # Switch to connection default and close connections testuser1,testuser2,testuser3
175 DROP DATABASE db_datadict;
176 DROP USER 'testuser1'@'localhost';
177 DROP USER 'testuser2'@'localhost';
178 DROP USER 'testuser3'@'localhost';
179 ################################################################################
180 # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.COLUMN_PRIVILEGES modifications
181 ################################################################################
182 DROP DATABASE IF EXISTS db_datadict;
183 CREATE DATABASE db_datadict;
184 CREATE TABLE db_datadict.my_table (f1 BIGINT, f2 CHAR(10), f3 DATE)
185 ENGINE = <engine_type>;
186 DROP USER 'testuser1'@'localhost';
187 CREATE USER 'testuser1'@'localhost';
188 GRANT ALL ON test.* TO 'testuser1'@'localhost';
189 SELECT * FROM information_schema.column_privileges
190 WHERE table_name = 'my_table'
191 ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
192 GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
193 SHOW GRANTS FOR 'testuser1'@'localhost';
194 Grants for testuser1@localhost
195 GRANT USAGE ON *.* TO `testuser1`@`localhost`
196 GRANT ALL PRIVILEGES ON `test`.* TO `testuser1`@`localhost`
197 # Establish connection testuser1 (user=testuser1)
198 SELECT * FROM information_schema.column_privileges
199 WHERE table_name = 'my_table'
200 ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
201 GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
202 SHOW GRANTS FOR 'testuser1'@'localhost';
203 Grants for testuser1@localhost
204 GRANT USAGE ON *.* TO `testuser1`@`localhost`
205 GRANT ALL PRIVILEGES ON `test`.* TO `testuser1`@`localhost`
206 # Switch to connection default
207 GRANT SELECT (f1,f3) ON db_datadict.my_table TO 'testuser1'@'localhost';
208 SELECT * FROM information_schema.column_privileges
209 WHERE table_name = 'my_table'
210 ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
211 GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
212 'testuser1'@'localhost' def db_datadict my_table f1 SELECT NO
213 'testuser1'@'localhost' def db_datadict my_table f3 SELECT NO
214 SHOW GRANTS FOR 'testuser1'@'localhost';
215 Grants for testuser1@localhost
216 GRANT USAGE ON *.* TO `testuser1`@`localhost`
217 GRANT ALL PRIVILEGES ON `test`.* TO `testuser1`@`localhost`
218 GRANT SELECT (`f1`, `f3`) ON `db_datadict`.`my_table` TO `testuser1`@`localhost`
219 # Switch to connection testuser1
220 SELECT * FROM information_schema.column_privileges
221 WHERE table_name = 'my_table'
222 ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
223 GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
224 'testuser1'@'localhost' def db_datadict my_table f1 SELECT NO
225 'testuser1'@'localhost' def db_datadict my_table f3 SELECT NO
226 SHOW GRANTS FOR 'testuser1'@'localhost';
227 Grants for testuser1@localhost
228 GRANT USAGE ON *.* TO `testuser1`@`localhost`
229 GRANT ALL PRIVILEGES ON `test`.* TO `testuser1`@`localhost`
230 GRANT SELECT (`f1`, `f3`) ON `db_datadict`.`my_table` TO `testuser1`@`localhost`
231 # Switch to connection default
232 ALTER TABLE db_datadict.my_table DROP COLUMN f3;
233 GRANT UPDATE (f1) ON db_datadict.my_table TO 'testuser1'@'localhost';
234 SELECT * FROM information_schema.column_privileges
235 WHERE table_name = 'my_table'
236 ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
237 GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
238 'testuser1'@'localhost' def db_datadict my_table f1 SELECT NO
239 'testuser1'@'localhost' def db_datadict my_table f1 UPDATE NO
240 'testuser1'@'localhost' def db_datadict my_table f3 SELECT NO
241 SHOW GRANTS FOR 'testuser1'@'localhost';
242 Grants for testuser1@localhost
243 GRANT USAGE ON *.* TO `testuser1`@`localhost`
244 GRANT ALL PRIVILEGES ON `test`.* TO `testuser1`@`localhost`
245 GRANT SELECT (`f1`, `f3`), UPDATE (`f1`) ON `db_datadict`.`my_table` TO `testuser1`@`localhost`
246 # Switch to connection testuser1
247 SELECT * FROM information_schema.column_privileges
248 WHERE table_name = 'my_table'
249 ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
250 GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
251 'testuser1'@'localhost' def db_datadict my_table f1 SELECT NO
252 'testuser1'@'localhost' def db_datadict my_table f1 UPDATE NO
253 'testuser1'@'localhost' def db_datadict my_table f3 SELECT NO
254 SHOW GRANTS FOR 'testuser1'@'localhost';
255 Grants for testuser1@localhost
256 GRANT USAGE ON *.* TO `testuser1`@`localhost`
257 GRANT ALL PRIVILEGES ON `test`.* TO `testuser1`@`localhost`
258 GRANT SELECT (`f1`, `f3`), UPDATE (`f1`) ON `db_datadict`.`my_table` TO `testuser1`@`localhost`
259 SELECT f1, f3 FROM db_datadict.my_table;
260 ERROR 42S22: Unknown column 'f3' in 'field list'
261 # Switch to connection default
262 ALTER TABLE db_datadict.my_table CHANGE COLUMN f1 my_col BIGINT;
263 SELECT * FROM information_schema.column_privileges
264 WHERE table_name = 'my_table'
265 ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
266 GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
267 'testuser1'@'localhost' def db_datadict my_table f1 SELECT NO
268 'testuser1'@'localhost' def db_datadict my_table f1 UPDATE NO
269 'testuser1'@'localhost' def db_datadict my_table f3 SELECT NO
270 SHOW GRANTS FOR 'testuser1'@'localhost';
271 Grants for testuser1@localhost
272 GRANT USAGE ON *.* TO `testuser1`@`localhost`
273 GRANT ALL PRIVILEGES ON `test`.* TO `testuser1`@`localhost`
274 GRANT SELECT (`f1`, `f3`), UPDATE (`f1`) ON `db_datadict`.`my_table` TO `testuser1`@`localhost`
275 # Switch to connection testuser1
276 SELECT * FROM information_schema.column_privileges
277 WHERE table_name = 'my_table'
278 ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
279 GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
280 'testuser1'@'localhost' def db_datadict my_table f1 SELECT NO
281 'testuser1'@'localhost' def db_datadict my_table f1 UPDATE NO
282 'testuser1'@'localhost' def db_datadict my_table f3 SELECT NO
283 SHOW GRANTS FOR 'testuser1'@'localhost';
284 Grants for testuser1@localhost
285 GRANT USAGE ON *.* TO `testuser1`@`localhost`
286 GRANT ALL PRIVILEGES ON `test`.* TO `testuser1`@`localhost`
287 GRANT SELECT (`f1`, `f3`), UPDATE (`f1`) ON `db_datadict`.`my_table` TO `testuser1`@`localhost`
288 # Switch to connection default
289 DROP TABLE db_datadict.my_table;
290 SELECT * FROM information_schema.column_privileges
291 WHERE table_name = 'my_table'
292 ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
293 GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
294 'testuser1'@'localhost' def db_datadict my_table f1 SELECT NO
295 'testuser1'@'localhost' def db_datadict my_table f1 UPDATE NO
296 'testuser1'@'localhost' def db_datadict my_table f3 SELECT NO
297 SHOW GRANTS FOR 'testuser1'@'localhost';
298 Grants for testuser1@localhost
299 GRANT USAGE ON *.* TO `testuser1`@`localhost`
300 GRANT ALL PRIVILEGES ON `test`.* TO `testuser1`@`localhost`
301 GRANT SELECT (`f1`, `f3`), UPDATE (`f1`) ON `db_datadict`.`my_table` TO `testuser1`@`localhost`
302 # Switch to connection testuser1
303 SELECT * FROM information_schema.column_privileges
304 WHERE table_name = 'my_table'
305 ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
306 GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
307 'testuser1'@'localhost' def db_datadict my_table f1 SELECT NO
308 'testuser1'@'localhost' def db_datadict my_table f1 UPDATE NO
309 'testuser1'@'localhost' def db_datadict my_table f3 SELECT NO
310 SHOW GRANTS FOR 'testuser1'@'localhost';
311 Grants for testuser1@localhost
312 GRANT USAGE ON *.* TO `testuser1`@`localhost`
313 GRANT ALL PRIVILEGES ON `test`.* TO `testuser1`@`localhost`
314 GRANT SELECT (`f1`, `f3`), UPDATE (`f1`) ON `db_datadict`.`my_table` TO `testuser1`@`localhost`
315 # Switch to connection default
316 REVOKE ALL ON db_datadict.my_table FROM 'testuser1'@'localhost';
317 SELECT * FROM information_schema.column_privileges
318 WHERE table_name = 'my_table'
319 ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
320 GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
321 SHOW GRANTS FOR 'testuser1'@'localhost';
322 Grants for testuser1@localhost
323 GRANT USAGE ON *.* TO `testuser1`@`localhost`
324 GRANT ALL PRIVILEGES ON `test`.* TO `testuser1`@`localhost`
325 # Switch to connection testuser1
326 SELECT * FROM information_schema.column_privileges
327 WHERE table_name = 'my_table'
328 ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
329 GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
330 SHOW GRANTS FOR 'testuser1'@'localhost';
331 Grants for testuser1@localhost
332 GRANT USAGE ON *.* TO `testuser1`@`localhost`
333 GRANT ALL PRIVILEGES ON `test`.* TO `testuser1`@`localhost`
334 # Switch to connection default and close connection testuser1
335 DROP USER 'testuser1'@'localhost';
336 DROP DATABASE db_datadict;
337 ########################################################################
338 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
339 # DDL on INFORMATION_SCHEMA table are not supported
340 ########################################################################
341 DROP DATABASE IF EXISTS db_datadict;
342 CREATE DATABASE db_datadict;
343 CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT)
344 ENGINE = <engine_type>;
345 DROP USER 'testuser1'@'localhost';
346 CREATE USER 'testuser1'@'localhost';
347 GRANT SELECT (f1) ON db_datadict.t1 TO 'testuser1'@'localhost';
348 INSERT INTO information_schema.column_privileges
349 SELECT * FROM information_schema.column_privileges;
350 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
351 UPDATE information_schema.column_privileges SET table_schema = 'test'
352 WHERE table_name = 't1';
353 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
354 DELETE FROM information_schema.column_privileges WHERE table_name = 't1';
355 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
356 TRUNCATE information_schema.column_privileges;
357 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
358 CREATE INDEX my_idx_on_tables
359 ON information_schema.column_privileges(table_schema);
360 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
361 ALTER TABLE information_schema.column_privileges ADD f1 INT;
362 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
363 DROP TABLE information_schema.column_privileges;
364 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
365 ALTER TABLE information_schema.column_privileges
366 RENAME db_datadict.column_privileges;
367 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
368 ALTER TABLE information_schema.column_privileges
369 RENAME information_schema.xcolumn_privileges;
370 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
371 DROP DATABASE db_datadict;
372 DROP USER 'testuser1'@'localhost';