1
SHOW TABLES FROM information_schema LIKE 'TABLE_PRIVILEGES';
2
Tables_in_information_schema (TABLE_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.TABLE_PRIVILEGES;
11
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.TABLE_PRIVILEGES;
12
CREATE FUNCTION test.f1() returns BIGINT
14
DECLARE counter BIGINT DEFAULT NULL;
15
SELECT COUNT(*) INTO counter FROM information_schema.TABLE_PRIVILEGES;
18
# Attention: The printing of the next result sets is disabled.
19
SELECT * FROM information_schema.TABLE_PRIVILEGES;
20
SELECT * FROM test.v1;
24
DROP PROCEDURE test.p1;
25
DROP FUNCTION test.f1;
26
#########################################################################
27
# Testcase 3.2.11.1: INFORMATION_SCHEMA.TABLE_PRIVILEGES layout
28
#########################################################################
29
DESCRIBE information_schema.TABLE_PRIVILEGES;
30
Field Type Null Key Default Extra
31
GRANTEE varchar(81) NO
32
TABLE_CATALOG varchar(512) YES NULL
33
TABLE_SCHEMA varchar(64) NO
34
TABLE_NAME varchar(64) NO
35
PRIVILEGE_TYPE varchar(64) NO
36
IS_GRANTABLE varchar(3) NO
37
SHOW CREATE TABLE information_schema.TABLE_PRIVILEGES;
39
TABLE_PRIVILEGES CREATE TEMPORARY TABLE `TABLE_PRIVILEGES` (
40
`GRANTEE` varchar(81) NOT NULL DEFAULT '',
41
`TABLE_CATALOG` varchar(512) DEFAULT NULL,
42
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
43
`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
44
`PRIVILEGE_TYPE` varchar(64) NOT NULL DEFAULT '',
45
`IS_GRANTABLE` varchar(3) NOT NULL DEFAULT ''
46
) ENGINE=MEMORY DEFAULT CHARSET=utf8
47
SHOW COLUMNS FROM information_schema.TABLE_PRIVILEGES;
48
Field Type Null Key Default Extra
49
GRANTEE varchar(81) NO
50
TABLE_CATALOG varchar(512) YES NULL
51
TABLE_SCHEMA varchar(64) NO
52
TABLE_NAME varchar(64) NO
53
PRIVILEGE_TYPE varchar(64) NO
54
IS_GRANTABLE varchar(3) NO
55
SELECT table_catalog, table_schema, table_name, privilege_type
56
FROM information_schema.table_privileges WHERE table_catalog IS NOT NULL;
57
table_catalog table_schema table_name privilege_type
58
######################################################################
59
# Testcase 3.2.11.2+3.2.11.3+3.2.11.4:
60
# INFORMATION_SCHEMA.TABLE_PRIVILEGES accessible information
61
######################################################################
62
DROP DATABASE IF EXISTS db_datadict;
63
CREATE DATABASE db_datadict;
64
CREATE TABLE db_datadict.tb1(f1 INT, f2 INT, f3 INT)
65
ENGINE = <engine_type>;
66
DROP USER 'testuser1'@'localhost';
67
CREATE USER 'testuser1'@'localhost';
68
GRANT CREATE, SELECT ON db_datadict.*
69
TO 'testuser1'@'localhost' WITH GRANT OPTION;
70
GRANT SELECT ON db_datadict.tb1 TO 'testuser1'@'localhost';
71
DROP USER 'testuser2'@'localhost';
72
CREATE USER 'testuser2'@'localhost';
73
GRANT ALL ON db_datadict.tb1 TO 'testuser2'@'localhost' WITH GRANT OPTION;
74
DROP USER 'testuser3'@'localhost';
75
CREATE USER 'testuser3'@'localhost';
76
# Establish connection testuser1 (user=testuser1)
77
CREATE TABLE tb3 (f1 TEXT)
78
ENGINE = <other_engine_type>;
79
GRANT SELECT ON db_datadict.tb3 TO 'testuser3'@'localhost';
80
SELECT * FROM information_schema.table_privileges
81
WHERE table_name LIKE 'tb%'
82
ORDER BY grantee,table_schema,table_name,privilege_type;
83
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
84
'testuser1'@'localhost' NULL db_datadict tb1 SELECT NO
85
SHOW GRANTS FOR 'testuser1'@'localhost';
86
Grants for testuser1@localhost
87
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
88
GRANT SELECT, CREATE ON `db_datadict`.* TO 'testuser1'@'localhost' WITH GRANT OPTION
89
GRANT SELECT ON `db_datadict`.`tb1` TO 'testuser1'@'localhost'
90
# Establish connection testuser2 (user=testuser3)
91
SELECT * FROM information_schema.table_privileges
92
WHERE table_name LIKE 'tb%'
93
ORDER BY grantee,table_schema,table_name,privilege_type;
94
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
95
'testuser2'@'localhost' NULL db_datadict tb1 ALTER YES
96
'testuser2'@'localhost' NULL db_datadict tb1 CREATE YES
97
'testuser2'@'localhost' NULL db_datadict tb1 CREATE VIEW YES
98
'testuser2'@'localhost' NULL db_datadict tb1 DELETE YES
99
'testuser2'@'localhost' NULL db_datadict tb1 DROP YES
100
'testuser2'@'localhost' NULL db_datadict tb1 INDEX YES
101
'testuser2'@'localhost' NULL db_datadict tb1 INSERT YES
102
'testuser2'@'localhost' NULL db_datadict tb1 REFERENCES YES
103
'testuser2'@'localhost' NULL db_datadict tb1 SELECT YES
104
'testuser2'@'localhost' NULL db_datadict tb1 SHOW VIEW YES
105
'testuser2'@'localhost' NULL db_datadict tb1 TRIGGER YES
106
'testuser2'@'localhost' NULL db_datadict tb1 UPDATE YES
107
SHOW GRANTS FOR 'testuser2'@'localhost';
108
Grants for testuser2@localhost
109
GRANT USAGE ON *.* TO 'testuser2'@'localhost'
110
GRANT ALL PRIVILEGES ON `db_datadict`.`tb1` TO 'testuser2'@'localhost' WITH GRANT OPTION
111
# Establish connection testuser3 (user=testuser3)
112
SELECT * FROM information_schema.table_privileges
113
WHERE table_name LIKE 'tb%'
114
ORDER BY grantee,table_schema,table_name,privilege_type;
115
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
116
'testuser3'@'localhost' NULL db_datadict tb3 SELECT NO
117
SHOW GRANTS FOR 'testuser3'@'localhost';
118
Grants for testuser3@localhost
119
GRANT USAGE ON *.* TO 'testuser3'@'localhost'
120
GRANT SELECT ON `db_datadict`.`tb3` TO 'testuser3'@'localhost'
121
# Switch to connection default and close the other connections
122
SELECT * FROM information_schema.table_privileges
123
WHERE table_name LIKE 'tb%'
124
ORDER BY grantee,table_schema,table_name,privilege_type;
125
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
126
'testuser1'@'localhost' NULL db_datadict tb1 SELECT NO
127
'testuser2'@'localhost' NULL db_datadict tb1 ALTER YES
128
'testuser2'@'localhost' NULL db_datadict tb1 CREATE YES
129
'testuser2'@'localhost' NULL db_datadict tb1 CREATE VIEW YES
130
'testuser2'@'localhost' NULL db_datadict tb1 DELETE YES
131
'testuser2'@'localhost' NULL db_datadict tb1 DROP YES
132
'testuser2'@'localhost' NULL db_datadict tb1 INDEX YES
133
'testuser2'@'localhost' NULL db_datadict tb1 INSERT YES
134
'testuser2'@'localhost' NULL db_datadict tb1 REFERENCES YES
135
'testuser2'@'localhost' NULL db_datadict tb1 SELECT YES
136
'testuser2'@'localhost' NULL db_datadict tb1 SHOW VIEW YES
137
'testuser2'@'localhost' NULL db_datadict tb1 TRIGGER YES
138
'testuser2'@'localhost' NULL db_datadict tb1 UPDATE YES
139
'testuser3'@'localhost' NULL db_datadict tb3 SELECT NO
140
SHOW GRANTS FOR 'testuser1'@'localhost';
141
Grants for testuser1@localhost
142
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
143
GRANT SELECT, CREATE ON `db_datadict`.* TO 'testuser1'@'localhost' WITH GRANT OPTION
144
GRANT SELECT ON `db_datadict`.`tb1` TO 'testuser1'@'localhost'
145
SHOW GRANTS FOR 'testuser2'@'localhost';
146
Grants for testuser2@localhost
147
GRANT USAGE ON *.* TO 'testuser2'@'localhost'
148
GRANT ALL PRIVILEGES ON `db_datadict`.`tb1` TO 'testuser2'@'localhost' WITH GRANT OPTION
149
SHOW GRANTS FOR 'testuser3'@'localhost';
150
Grants for testuser3@localhost
151
GRANT USAGE ON *.* TO 'testuser3'@'localhost'
152
GRANT SELECT ON `db_datadict`.`tb3` TO 'testuser3'@'localhost'
153
DROP USER 'testuser1'@'localhost';
154
DROP USER 'testuser2'@'localhost';
155
DROP USER 'testuser3'@'localhost';
156
DROP DATABASE db_datadict;
157
################################################################################
158
# 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TABLE_PRIVILEGES modifications
159
################################################################################
160
DROP TABLE IF EXISTS test.t1_table;
161
DROP VIEW IF EXISTS test.t1_view;
162
DROP DATABASE IF EXISTS db_datadict;
163
CREATE DATABASE db_datadict;
164
CREATE TABLE test.t1_table (f1 BIGINT)
165
DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
166
COMMENT = 'Initial Comment' ENGINE = <engine_type>;
167
CREATE VIEW test.t1_view AS SELECT 1;
168
DROP USER 'testuser1'@'localhost';
169
CREATE USER 'testuser1'@'localhost';
170
DROP USER 'the_user'@'localhost';
171
SELECT table_name FROM information_schema.table_privileges
172
WHERE table_name LIKE 't1_%';
174
GRANT ALL ON test.t1_table TO 'testuser1'@'localhost';
175
GRANT ALL ON test.t1_view TO 'testuser1'@'localhost';
176
SELECT * FROM information_schema.table_privileges
177
WHERE table_name LIKE 't1_%'
178
ORDER BY grantee, table_schema, table_name, privilege_type;
179
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
180
'testuser1'@'localhost' NULL test t1_table ALTER NO
181
'testuser1'@'localhost' NULL test t1_table CREATE NO
182
'testuser1'@'localhost' NULL test t1_table CREATE VIEW NO
183
'testuser1'@'localhost' NULL test t1_table DELETE NO
184
'testuser1'@'localhost' NULL test t1_table DROP NO
185
'testuser1'@'localhost' NULL test t1_table INDEX NO
186
'testuser1'@'localhost' NULL test t1_table INSERT NO
187
'testuser1'@'localhost' NULL test t1_table REFERENCES NO
188
'testuser1'@'localhost' NULL test t1_table SELECT NO
189
'testuser1'@'localhost' NULL test t1_table SHOW VIEW NO
190
'testuser1'@'localhost' NULL test t1_table TRIGGER NO
191
'testuser1'@'localhost' NULL test t1_table UPDATE NO
192
'testuser1'@'localhost' NULL test t1_view ALTER NO
193
'testuser1'@'localhost' NULL test t1_view CREATE NO
194
'testuser1'@'localhost' NULL test t1_view CREATE VIEW NO
195
'testuser1'@'localhost' NULL test t1_view DELETE NO
196
'testuser1'@'localhost' NULL test t1_view DROP NO
197
'testuser1'@'localhost' NULL test t1_view INDEX NO
198
'testuser1'@'localhost' NULL test t1_view INSERT NO
199
'testuser1'@'localhost' NULL test t1_view REFERENCES NO
200
'testuser1'@'localhost' NULL test t1_view SELECT NO
201
'testuser1'@'localhost' NULL test t1_view SHOW VIEW NO
202
'testuser1'@'localhost' NULL test t1_view TRIGGER NO
203
'testuser1'@'localhost' NULL test t1_view UPDATE NO
204
SELECT DISTINCT grantee, table_name FROM information_schema.table_privileges
205
WHERE table_name LIKE 't1_%'
206
ORDER BY grantee, table_name;
208
'testuser1'@'localhost' t1_table
209
'testuser1'@'localhost' t1_view
210
RENAME USER 'testuser1'@'localhost' TO 'the_user'@'localhost';
212
SELECT DISTINCT grantee, table_name FROM information_schema.table_privileges
213
WHERE table_name LIKE 't1_%'
214
ORDER BY grantee, table_name;
216
'the_user'@'localhost' t1_table
217
'the_user'@'localhost' t1_view
218
SHOW GRANTS FOR 'testuser1'@'localhost';
219
ERROR 42000: There is no such grant defined for user 'testuser1' on host 'localhost'
220
SHOW GRANTS FOR 'the_user'@'localhost';
221
Grants for the_user@localhost
222
GRANT USAGE ON *.* TO 'the_user'@'localhost'
223
GRANT ALL PRIVILEGES ON `test`.`t1_view` TO 'the_user'@'localhost'
224
GRANT ALL PRIVILEGES ON `test`.`t1_table` TO 'the_user'@'localhost'
225
SELECT DISTINCT table_schema,table_name FROM information_schema.table_privileges
226
WHERE table_name LIKE 't1_%'
227
ORDER BY table_schema,table_name;
228
table_schema table_name
231
RENAME TABLE test.t1_table TO db_datadict.t1_table;
232
RENAME TABLE test.t1_view TO db_datadict.t1_view;
233
ERROR HY000: Changing schema from 'test' to 'db_datadict' is not allowed.
234
SELECT DISTINCT table_schema,table_name FROM information_schema.table_privileges
235
WHERE table_name LIKE 't1_%'
236
ORDER BY table_schema,table_name;
237
table_schema table_name
240
SHOW GRANTS FOR 'the_user'@'localhost';
241
Grants for the_user@localhost
242
GRANT USAGE ON *.* TO 'the_user'@'localhost'
243
GRANT ALL PRIVILEGES ON `test`.`t1_view` TO 'the_user'@'localhost'
244
GRANT ALL PRIVILEGES ON `test`.`t1_table` TO 'the_user'@'localhost'
245
REVOKE ALL PRIVILEGES ON test.t1_table FROM 'the_user'@'localhost';
246
REVOKE ALL PRIVILEGES ON test.t1_view FROM 'the_user'@'localhost';
247
DROP VIEW test.t1_view;
248
CREATE VIEW db_datadict.t1_view AS SELECT 1;
249
GRANT ALL ON db_datadict.t1_table TO 'the_user'@'localhost';
250
GRANT ALL ON db_datadict.t1_view TO 'the_user'@'localhost';
251
SELECT DISTINCT table_name FROM information_schema.table_privileges
252
WHERE table_name LIKE 't1_%'
257
RENAME TABLE db_datadict.t1_table TO db_datadict.t1_tablex;
258
RENAME TABLE db_datadict.t1_view TO db_datadict.t1_viewx;
259
SELECT DISTINCT table_name FROM information_schema.table_privileges
260
WHERE table_name LIKE 't1_%'
265
RENAME TABLE db_datadict.t1_tablex TO db_datadict.t1_table;
266
RENAME TABLE db_datadict.t1_viewx TO db_datadict.t1_view;
267
SELECT DISTINCT table_name FROM information_schema.table_privileges
268
WHERE table_name LIKE 't1_%'
273
DROP TABLE db_datadict.t1_table;
274
DROP VIEW db_datadict.t1_view;
275
SELECT DISTINCT table_name FROM information_schema.table_privileges
276
WHERE table_name LIKE 't1_%'
281
CREATE TABLE db_datadict.t1_table
282
ENGINE = <engine_type> AS
284
CREATE VIEW db_datadict.t1_view AS SELECT 1;
285
GRANT ALL ON db_datadict.t1_table TO 'the_user'@'localhost';
286
GRANT ALL ON db_datadict.t1_view TO 'the_user'@'localhost';
287
SELECT DISTINCT table_name FROM information_schema.table_privileges
288
WHERE table_name LIKE 't1_%'
293
DROP DATABASE db_datadict;
294
SELECT DISTINCT table_name FROM information_schema.table_privileges
295
WHERE table_name LIKE 't1_%'
300
DROP USER 'the_user'@'localhost';
301
########################################################################
302
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
303
# DDL on INFORMATION_SCHEMA table are not supported
304
########################################################################
305
DROP DATABASE IF EXISTS db_datadict;
306
CREATE DATABASE db_datadict;
307
CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT)
308
ENGINE = <engine_type>;
309
DROP USER 'testuser1'@'localhost';
310
CREATE USER 'testuser1'@'localhost';
311
GRANT SELECT (f1) ON db_datadict.t1 TO 'testuser1'@'localhost';
312
INSERT INTO information_schema.table_privileges
313
SELECT * FROM information_schema.table_privileges;
314
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
315
UPDATE information_schema.table_privileges SET table_schema = 'test'
316
WHERE table_name = 't1';
317
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
318
DELETE FROM information_schema.table_privileges WHERE table_name = 't1';
319
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
320
TRUNCATE information_schema.table_privileges;
321
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
322
CREATE INDEX my_idx_on_tables
323
ON information_schema.table_privileges(table_schema);
324
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
325
ALTER TABLE information_schema.table_privileges ADD f1 INT;
326
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
327
DROP TABLE information_schema.table_privileges;
328
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
329
ALTER TABLE information_schema.table_privileges
330
RENAME db_datadict.table_privileges;
331
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
332
ALTER TABLE information_schema.table_privileges
333
RENAME information_schema.xtable_privileges;
334
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
335
DROP DATABASE db_datadict;
336
DROP USER 'testuser1'@'localhost';