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
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
|
SHOW TABLES FROM information_schema LIKE 'COLUMN_PRIVILEGES';
Tables_in_information_schema (COLUMN_PRIVILEGES)
COLUMN_PRIVILEGES
#######################################################################
# 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.COLUMN_PRIVILEGES;
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.COLUMN_PRIVILEGES;
CREATE FUNCTION test.f1() returns BIGINT
BEGIN
DECLARE counter BIGINT DEFAULT NULL;
SELECT COUNT(*) INTO counter FROM information_schema.COLUMN_PRIVILEGES;
RETURN counter;
END//
# Attention: The printing of the next result sets is disabled.
SELECT * FROM information_schema.COLUMN_PRIVILEGES;
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.5.1: INFORMATION_SCHEMA.COLUMN_PRIVILEGES layout
#########################################################################
DESCRIBE information_schema.COLUMN_PRIVILEGES;
Field Type Null Key Default Extra
GRANTEE varchar(190) NO
TABLE_CATALOG varchar(512) NO
TABLE_SCHEMA varchar(64) NO
TABLE_NAME varchar(64) NO
COLUMN_NAME varchar(64) NO
PRIVILEGE_TYPE varchar(64) NO
IS_GRANTABLE varchar(3) NO
SHOW CREATE TABLE information_schema.COLUMN_PRIVILEGES;
Table Create Table
COLUMN_PRIVILEGES CREATE TEMPORARY TABLE `COLUMN_PRIVILEGES` (
`GRANTEE` varchar(190) NOT NULL DEFAULT '',
`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
`COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
`PRIVILEGE_TYPE` varchar(64) NOT NULL DEFAULT '',
`IS_GRANTABLE` varchar(3) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
SHOW COLUMNS FROM information_schema.COLUMN_PRIVILEGES;
Field Type Null Key Default Extra
GRANTEE varchar(190) NO
TABLE_CATALOG varchar(512) NO
TABLE_SCHEMA varchar(64) NO
TABLE_NAME varchar(64) NO
COLUMN_NAME varchar(64) NO
PRIVILEGE_TYPE varchar(64) NO
IS_GRANTABLE varchar(3) NO
SELECT table_catalog, table_schema, table_name, column_name, privilege_type
FROM information_schema.column_privileges WHERE table_catalog IS NOT NULL;
table_catalog table_schema table_name column_name privilege_type
######################################################################
# Testcase 3.2.5.2+3.2.5.3+3.2.5.4:
# INFORMATION_SCHEMA.COLUMN_PRIVILEGES accessible information
######################################################################
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
CREATE TABLE db_datadict.t1 (f1 INT, f2 DECIMAL, f3 TEXT)
ENGINE = <other_engine_type>;
USE db_datadict;
DROP USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
DROP USER 'testuser2'@'localhost';
CREATE USER 'testuser2'@'localhost';
DROP USER 'testuser3'@'localhost';
CREATE USER 'testuser3'@'localhost';
GRANT SELECT(f1, f3) ON db_datadict.t1 TO 'testuser1'@'localhost';
GRANT INSERT(f1) ON db_datadict.t1 TO 'testuser1'@'localhost';
GRANT UPDATE(f2) ON db_datadict.t1 TO 'testuser1'@'localhost';
GRANT SELECT(f2) ON db_datadict.t1 TO 'testuser2'@'localhost';
GRANT INSERT, SELECT ON db_datadict.t1 TO 'testuser3'@'localhost';
GRANT SELECT(f3) ON db_datadict.t1 TO 'testuser3'@'localhost';
GRANT INSERT, SELECT ON db_datadict.t1 TO 'testuser3'@'localhost'
WITH GRANT OPTION;
GRANT ALL ON db_datadict.* TO 'testuser3'@'localhost';
SELECT * FROM information_schema.column_privileges
WHERE grantee LIKE '''testuser%'''
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict t1 f1 INSERT NO
'testuser1'@'localhost' def db_datadict t1 f1 SELECT NO
'testuser1'@'localhost' def db_datadict t1 f2 UPDATE NO
'testuser1'@'localhost' def db_datadict t1 f3 SELECT NO
'testuser2'@'localhost' def db_datadict t1 f2 SELECT NO
'testuser3'@'localhost' def db_datadict t1 f3 SELECT YES
GRANT UPDATE(f3) ON db_datadict.t1 TO 'testuser1'@'localhost'
WITH GRANT OPTION;
SELECT * FROM information_schema.column_privileges
WHERE grantee LIKE '''testuser%'''
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict t1 f1 INSERT YES
'testuser1'@'localhost' def db_datadict t1 f1 SELECT YES
'testuser1'@'localhost' def db_datadict t1 f2 UPDATE YES
'testuser1'@'localhost' def db_datadict t1 f3 SELECT YES
'testuser1'@'localhost' def db_datadict t1 f3 UPDATE YES
'testuser2'@'localhost' def db_datadict t1 f2 SELECT NO
'testuser3'@'localhost' def db_datadict t1 f3 SELECT YES
connect testuser1, localhost, testuser1, , db_datadict;
SELECT * FROM information_schema.column_privileges
WHERE grantee LIKE '''testuser%'''
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict t1 f1 INSERT YES
'testuser1'@'localhost' def db_datadict t1 f1 SELECT YES
'testuser1'@'localhost' def db_datadict t1 f2 UPDATE YES
'testuser1'@'localhost' def db_datadict t1 f3 SELECT YES
'testuser1'@'localhost' def db_datadict t1 f3 UPDATE YES
connect testuser2, localhost, testuser2, , db_datadict;
SELECT * FROM information_schema.column_privileges
WHERE grantee LIKE '''testuser%'''
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'testuser2'@'localhost' def db_datadict t1 f2 SELECT NO
connect testuser3, localhost, testuser3, , db_datadict;
# FIXME: Is it correct that granted TABLES do not occur in COLUMN_PRIVILEGES?
SELECT * FROM information_schema.table_privileges
WHERE grantee LIKE '''testuser%'''
ORDER BY grantee,table_schema,table_name,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
'testuser3'@'localhost' def db_datadict t1 INSERT YES
'testuser3'@'localhost' def db_datadict t1 SELECT YES
SELECT * FROM information_schema.schema_privileges
WHERE grantee LIKE '''testuser%'''
ORDER BY grantee,table_schema,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
'testuser3'@'localhost' def db_datadict ALTER NO
'testuser3'@'localhost' def db_datadict ALTER ROUTINE NO
'testuser3'@'localhost' def db_datadict CREATE NO
'testuser3'@'localhost' def db_datadict CREATE ROUTINE NO
'testuser3'@'localhost' def db_datadict CREATE TEMPORARY TABLES NO
'testuser3'@'localhost' def db_datadict CREATE VIEW NO
'testuser3'@'localhost' def db_datadict DELETE NO
'testuser3'@'localhost' def db_datadict DELETE HISTORY NO
'testuser3'@'localhost' def db_datadict DROP NO
'testuser3'@'localhost' def db_datadict EVENT NO
'testuser3'@'localhost' def db_datadict EXECUTE NO
'testuser3'@'localhost' def db_datadict INDEX NO
'testuser3'@'localhost' def db_datadict INSERT NO
'testuser3'@'localhost' def db_datadict LOCK TABLES NO
'testuser3'@'localhost' def db_datadict REFERENCES NO
'testuser3'@'localhost' def db_datadict SELECT NO
'testuser3'@'localhost' def db_datadict SHOW VIEW NO
'testuser3'@'localhost' def db_datadict TRIGGER NO
'testuser3'@'localhost' def db_datadict UPDATE NO
SELECT * FROM information_schema.column_privileges
WHERE grantee LIKE '''testuser%'''
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'testuser3'@'localhost' def db_datadict t1 f3 SELECT YES
GRANT SELECT(f1, f3) ON db_datadict.t1 TO 'testuser2'@'localhost';
# FIXME: Is it intended that *my* grants to others are *NOT* shown here?
SELECT * FROM information_schema.column_privileges
WHERE grantee LIKE '''testuser%'''
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'testuser3'@'localhost' def db_datadict t1 f3 SELECT YES
connection testuser2;
SELECT * FROM information_schema.column_privileges
WHERE grantee LIKE '''testuser%'''
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'testuser2'@'localhost' def db_datadict t1 f1 SELECT NO
'testuser2'@'localhost' def db_datadict t1 f2 SELECT NO
'testuser2'@'localhost' def db_datadict t1 f3 SELECT NO
connection default;
disconnect testuser1;
disconnect testuser2;
disconnect testuser3;
DROP DATABASE db_datadict;
DROP USER 'testuser1'@'localhost';
DROP USER 'testuser2'@'localhost';
DROP USER 'testuser3'@'localhost';
################################################################################
# 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.COLUMN_PRIVILEGES modifications
################################################################################
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
CREATE TABLE db_datadict.my_table (f1 BIGINT, f2 CHAR(10), f3 DATE)
ENGINE = <engine_type>;
DROP USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
GRANT ALL ON test.* TO 'testuser1'@'localhost';
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
connect testuser1, localhost, testuser1, , test;
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
connection default;
GRANT SELECT (f1,f3) ON db_datadict.my_table TO 'testuser1'@'localhost';
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict my_table f1 SELECT NO
'testuser1'@'localhost' def db_datadict my_table f3 SELECT NO
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
GRANT SELECT (f3, f1) ON `db_datadict`.`my_table` TO 'testuser1'@'localhost'
connection testuser1;
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict my_table f1 SELECT NO
'testuser1'@'localhost' def db_datadict my_table f3 SELECT NO
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
GRANT SELECT (f3, f1) ON `db_datadict`.`my_table` TO 'testuser1'@'localhost'
connection default;
ALTER TABLE db_datadict.my_table DROP COLUMN f3;
GRANT UPDATE (f1) ON db_datadict.my_table TO 'testuser1'@'localhost';
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict my_table f1 SELECT NO
'testuser1'@'localhost' def db_datadict my_table f1 UPDATE NO
'testuser1'@'localhost' def db_datadict my_table f3 SELECT NO
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
GRANT SELECT (f3, f1), UPDATE (f1) ON `db_datadict`.`my_table` TO 'testuser1'@'localhost'
connection testuser1;
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict my_table f1 SELECT NO
'testuser1'@'localhost' def db_datadict my_table f1 UPDATE NO
'testuser1'@'localhost' def db_datadict my_table f3 SELECT NO
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
GRANT SELECT (f3, f1), UPDATE (f1) ON `db_datadict`.`my_table` TO 'testuser1'@'localhost'
SELECT f1, f3 FROM db_datadict.my_table;
ERROR 42S22: Unknown column 'f3' in 'field list'
connection default;
ALTER TABLE db_datadict.my_table CHANGE COLUMN f1 my_col BIGINT;
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict my_table f1 SELECT NO
'testuser1'@'localhost' def db_datadict my_table f1 UPDATE NO
'testuser1'@'localhost' def db_datadict my_table f3 SELECT NO
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
GRANT SELECT (f3, f1), UPDATE (f1) ON `db_datadict`.`my_table` TO 'testuser1'@'localhost'
connection testuser1;
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict my_table f1 SELECT NO
'testuser1'@'localhost' def db_datadict my_table f1 UPDATE NO
'testuser1'@'localhost' def db_datadict my_table f3 SELECT NO
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
GRANT SELECT (f3, f1), UPDATE (f1) ON `db_datadict`.`my_table` TO 'testuser1'@'localhost'
connection default;
DROP TABLE db_datadict.my_table;
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict my_table f1 SELECT NO
'testuser1'@'localhost' def db_datadict my_table f1 UPDATE NO
'testuser1'@'localhost' def db_datadict my_table f3 SELECT NO
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
GRANT SELECT (f3, f1), UPDATE (f1) ON `db_datadict`.`my_table` TO 'testuser1'@'localhost'
connection testuser1;
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
'testuser1'@'localhost' def db_datadict my_table f1 SELECT NO
'testuser1'@'localhost' def db_datadict my_table f1 UPDATE NO
'testuser1'@'localhost' def db_datadict my_table f3 SELECT NO
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
GRANT SELECT (f3, f1), UPDATE (f1) ON `db_datadict`.`my_table` TO 'testuser1'@'localhost'
connection default;
REVOKE ALL ON db_datadict.my_table FROM 'testuser1'@'localhost';
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
connection testuser1;
SELECT * FROM information_schema.column_privileges
WHERE table_name = 'my_table'
ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
SHOW GRANTS FOR 'testuser1'@'localhost';
Grants for testuser1@localhost
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
GRANT ALL PRIVILEGES ON `test`.* TO 'testuser1'@'localhost'
connection default;
disconnect testuser1;
DROP USER 'testuser1'@'localhost';
DROP DATABASE db_datadict;
########################################################################
# 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 TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT)
ENGINE = <engine_type>;
DROP USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
GRANT SELECT (f1) ON db_datadict.t1 TO 'testuser1'@'localhost';
INSERT INTO information_schema.column_privileges
SELECT * FROM information_schema.column_privileges;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
UPDATE information_schema.column_privileges SET table_schema = 'test'
WHERE table_name = 't1';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DELETE FROM information_schema.column_privileges WHERE table_name = 't1';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
TRUNCATE information_schema.column_privileges;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
CREATE INDEX my_idx_on_tables
ON information_schema.column_privileges(table_schema);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.column_privileges ADD f1 INT;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE information_schema.column_privileges;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.column_privileges
RENAME db_datadict.column_privileges;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.column_privileges
RENAME information_schema.xcolumn_privileges;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP DATABASE db_datadict;
DROP USER 'testuser1'@'localhost';
|
