Debian Codegraph @

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

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