Debian Codegraph @

mysql-test/suite/funcs_1/t/is_user_privileges.test

raw
1 # suite/funcs_1/t/is_user_privileges.test
2 #
3 # Check the layout of information_schema.user_privileges, permissions and
4 # the impact of CREATE/ALTER/DROP SCHEMA on it.
5 #
6 # Note:
7 # This test is not intended
8 # - to show information about the all time existing tables
9 # within the databases information_schema and mysql
10 # - for checking storage engine properties
11 # Therefore please do not alter $engine_type and $other_engine_type.
12 #
13 # Author:
14 # 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
15 # testsuite funcs_1
16 # Create this script based on older scripts and new code.
17 #
18
19
20 let $engine_type = InnoDB;
21 let $other_engine_type = InnoDB;
22
23 let $is_table = USER_PRIVILEGES;
24
25 # The table INFORMATION_SCHEMA.USER_PRIVILEGES must exist
26 eval SHOW TABLES FROM information_schema LIKE '$is_table';
27
28 --echo #######################################################################
29 --echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
30 --echo #######################################################################
31 # Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
32 # statement, just as if it were an ordinary user-defined table.
33 #
34 --source suite/funcs_1/datadict/is_table_query.inc
35
36
37 --echo #########################################################################
38 --echo # Testcase 3.2.16.1: INFORMATION_SCHEMA.USER_PRIVILEGES layout
39 --echo #########################################################################
40 # Ensure that the INFORMATION_SCHEMA.USER_PRIVILEGES table has the following columns,
41 # in the following order:
42 #
43 # GRANTEE (shows a user to whom a user privilege has been granted),
44 # TABLE_CATALOG (always shows NULL),
45 # PRIVILEGE_TYPE (shows the granted privilege),
46 # IS_GRANTABLE (shows whether the privilege was granted WITH GRANT OPTION).
47 #
48 --source suite/funcs_1/datadict/datadict_bug_12777.inc
49 eval DESCRIBE information_schema.$is_table;
50 --source suite/funcs_1/datadict/datadict_bug_12777.inc
51 eval SHOW CREATE TABLE information_schema.$is_table;
52 --source suite/funcs_1/datadict/datadict_bug_12777.inc
53 eval SHOW COLUMNS FROM information_schema.$is_table;
54
55 # Note: Retrieval of information within information_schema.columns about
56 # information_schema.user_privileges is in is_columns_is.test.
57
58 # Show that TABLE_CATALOG is always 'def'.
59 SELECT grantee, table_catalog, privilege_type
60 FROM information_schema.user_privileges
61 WHERE table_catalog IS NULL OR table_catalog <> 'def';
62
63
64 --echo ##########################################################################
65 --echo # Testcases 3.2.16.2+3.2.16.3+3.2.16.4: INFORMATION_SCHEMA.USER_PRIVILEGES
66 --echo # accessible information
67 --echo ##########################################################################
68 # 3.2.16.2: Ensure that the table shows the relevant information on every user
69 # privilege which has been granted to the current user or to PUBLIC,
70 # or has been granted by the current user.
71 # 3.2.16.3: Ensure that the table does not show any information on any user
72 # privileges which have been granted to users other than the current
73 # user or have been granted by any user other than the current user.
74 # 3.2.16.4: Ensure that the table does not show any information on any
75 # privileges that are not user privileges for the current user.
76 #
77 --disable_warnings
78 DROP DATABASE IF EXISTS db_datadict;
79 --enable_warnings
80 CREATE DATABASE db_datadict;
81
82 --error 0,ER_CANNOT_USER
83 DROP USER 'testuser1'@'localhost';
84 CREATE USER 'testuser1'@'localhost';
85 --error 0,ER_CANNOT_USER
86 DROP USER 'testuser2'@'localhost';
87 CREATE USER 'testuser2'@'localhost';
88 --error 0,ER_CANNOT_USER
89 DROP USER 'testuser3'@'localhost';
90 CREATE USER 'testuser3'@'localhost';
91
92 GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost';
93 GRANT SELECT ON mysql.user TO 'testuser1'@'localhost';
94
95 GRANT INSERT ON *.* TO 'testuser2'@'localhost';
96 GRANT UPDATE ON *.* TO 'testuser2'@'localhost';
97
98 let $my_select1= SELECT * FROM information_schema.user_privileges
99 WHERE grantee LIKE '''testuser%'''
100 ORDER BY grantee, table_catalog, privilege_type;
101 let $my_select2= SELECT * FROM mysql.user
102 WHERE user LIKE 'testuser%' ORDER BY host, user;
103 let $my_show= SHOW GRANTS;
104 --vertical_results
105 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
106 eval $my_select1;
107 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
108 eval $my_select2;
109 --horizontal_results
110
111 --echo #
112 --echo # Add GRANT OPTION db_datadict.* to testuser1;
113 GRANT UPDATE ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
114 --vertical_results
115 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
116 eval $my_select1;
117 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
118 eval $my_select2;
119 --horizontal_results
120
121 --echo # Establish connection testuser1 (user=testuser1)
122 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
123 connect (testuser1, localhost, testuser1, , db_datadict);
124 --vertical_results
125 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
126 eval $my_select1;
127 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
128 eval $my_select2;
129 --horizontal_results
130 eval $my_show;
131
132 --echo
133 --echo # Now add SELECT on *.* to testuser1;
134
135 --echo # Switch to connection default
136 connection default;
137 GRANT SELECT ON *.* TO 'testuser1'@'localhost';
138 --echo #
139 --echo # Here <SELECT NO> is shown correctly for testuser1;
140 --vertical_results
141 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
142 eval $my_select1;
143 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
144 eval $my_select2;
145 --horizontal_results
146
147 GRANT SELECT ON *.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
148 --echo #
149 --echo # Here <SELECT YES> is shown correctly for testuser1;
150 --vertical_results
151 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
152 eval $my_select1;
153 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
154 eval $my_select2;
155 --horizontal_results
156
157 --echo # Switch to connection testuser1
158 # check that this appears
159 connection testuser1;
160 --vertical_results
161 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
162 eval $my_select1;
163 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
164 eval $my_select2;
165 --horizontal_results
166 eval $my_show;
167
168 --echo # Establish connection testuser2 (user=testuser2)
169 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
170 connect (testuser2, localhost, testuser2, , db_datadict);
171 --vertical_results
172 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
173 eval $my_select1;
174 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
175 --error ER_TABLEACCESS_DENIED_ERROR
176 eval $my_select2;
177 --horizontal_results
178 eval $my_show;
179
180 --echo # Establish connection testuser3 (user=testuser3)
181 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
182 connect (testuser3, localhost, testuser3, , test);
183 --vertical_results
184 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
185 eval $my_select1;
186 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
187 --error ER_TABLEACCESS_DENIED_ERROR
188 eval $my_select2;
189 --horizontal_results
190 eval $my_show;
191
192 --echo
193 --echo # Revoke privileges from testuser1;
194 --echo # Switch to connection default
195 connection default;
196 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser1'@'localhost';
197 --vertical_results
198 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
199 eval $my_select1;
200 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
201 eval $my_select2;
202 --horizontal_results
203
204 --echo # Switch to connection testuser1
205 # check for changes
206 connection testuser1;
207 --vertical_results
208 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
209 eval $my_select1;
210 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
211 --error ER_TABLEACCESS_DENIED_ERROR
212 eval $my_select2;
213 --horizontal_results
214 eval $my_show;
215
216 # OK, testuser1 has no privs here
217 --error ER_TABLEACCESS_DENIED_ERROR
218 CREATE TABLE db_datadict.tb_55 ( c1 TEXT );
219 --vertical_results
220 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
221 eval $my_select1;
222 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
223 --error ER_TABLEACCESS_DENIED_ERROR
224 eval $my_select2;
225 --horizontal_results
226 eval $my_show;
227 # OK, testuser1 has no privs here
228 --error ER_TABLEACCESS_DENIED_ERROR
229 CREATE TABLE db_datadict.tb_66 ( c1 TEXT );
230
231 --echo
232 --echo # Add ALL on db_datadict.* (and select on mysql.user) to testuser1;
233 --echo # Switch to connection default
234 connection default;
235 GRANT ALL ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
236 GRANT SELECT ON mysql.user TO 'testuser1'@'localhost';
237 --vertical_results
238 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
239 eval $my_select1;
240 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
241 eval $my_select2;
242 --horizontal_results
243
244 --echo # Switch to connection testuser1
245 connection testuser1;
246 --vertical_results
247 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
248 eval $my_select1;
249 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
250 eval $my_select2;
251 --horizontal_results
252 eval $my_show;
253
254 # OK, testuser1 has no privs here
255 --error ER_TABLEACCESS_DENIED_ERROR
256 CREATE TABLE db_datadict.tb_56 ( c1 TEXT );
257
258 # using 'USE' lets the server read the privileges new, so now the CREATE works
259 USE db_datadict;
260 --vertical_results
261 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
262 eval $my_select1;
263 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
264 eval $my_select2;
265 --horizontal_results
266 eval $my_show;
267 --replace_result $other_engine_type <other_engine_type>
268 eval
269 CREATE TABLE tb_57 ( c1 TEXT )
270 ENGINE = $other_engine_type;
271
272 --echo
273 --echo # Revoke privileges from testuser1;
274 --echo # Switch to connection default
275 connection default;
276 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser1'@'localhost';
277 --vertical_results
278 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
279 eval $my_select1;
280 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
281 eval $my_select2;
282 --horizontal_results
283
284 --echo # Switch to connection testuser1
285 # check for changes
286 connection testuser1;
287 --vertical_results
288 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
289 eval $my_select1;
290 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
291 --error ER_TABLEACCESS_DENIED_ERROR
292 eval $my_select2;
293 --horizontal_results
294 eval $my_show;
295 # WORKS, as the existing old privileges are used!
296 --replace_result $other_engine_type <other_engine_type>
297 eval
298 CREATE TABLE db_datadict.tb_58 ( c1 TEXT )
299 ENGINE = $other_engine_type;
300 # existing privileges are "read" new when USE is called, user has no privileges
301 --error ER_DBACCESS_DENIED_ERROR
302 USE db_datadict;
303 #FIXME 3.2.16: check that it is correct that this now 'works': --error ER_TABLEACCESS_DENIED_ERROR
304 --replace_result $other_engine_type <other_engine_type>
305 eval
306 CREATE TABLE db_datadict.tb_59 ( c1 TEXT )
307 ENGINE = $other_engine_type;
308
309 # Cleanup
310 --echo # Switch to connection default and close connections testuser1,testuser2,testuser3
311 connection default;
312 disconnect testuser1;
313 disconnect testuser2;
314 disconnect testuser3;
315 DROP USER 'testuser1'@'localhost';
316 DROP USER 'testuser2'@'localhost';
317 DROP USER 'testuser3'@'localhost';
318 DROP DATABASE IF EXISTS db_datadict;
319
320
321 --echo ########################################################################################
322 --echo # Testcases 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.USER_PRIVILEGES modifications
323 --echo ########################################################################################
324 # 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
325 # column) automatically inserts all relevant information on that
326 # object into every appropriate INFORMATION_SCHEMA table.
327 # 3.2.1.14: Ensure that the alteration of any existing database object
328 # automatically updates all relevant information on that object in
329 # every appropriate INFORMATION_SCHEMA table.
330 # 3.2.1.15: Ensure that the dropping of any existing database object
331 # automatically deletes all relevant information on that object from
332 # every appropriate INFORMATION_SCHEMA table.
333 #
334 let $my_select = SELECT * FROM information_schema.user_privileges
335 WHERE grantee = '''testuser1''@''localhost''';
336 let $my_show = SHOW GRANTS FOR 'testuser1'@'localhost';
337 --vertical_results
338 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
339 eval $my_select;
340 --horizontal_results
341 --error ER_NONEXISTING_GRANT
342 eval $my_show;
343 --error 0,ER_CANNOT_USER
344 DROP USER 'testuser1'@'localhost';
345 CREATE USER 'testuser1'@'localhost';
346 --vertical_results
347 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
348 eval $my_select;
349 --horizontal_results
350 eval $my_show;
351 GRANT SELECT, FILE ON *.* TO 'testuser1'@'localhost';
352 --vertical_results
353 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
354 eval $my_select;
355 --horizontal_results
356 eval $my_show;
357 DROP USER 'testuser1'@'localhost';
358 --vertical_results
359 --replace_regex /[0-9]*-[0-9]*-[0-9]* [0-9]*:[0-9]*:[0-9]*/DTVALUE/
360 eval $my_select;
361 --horizontal_results
362 --error ER_NONEXISTING_GRANT
363 eval $my_show;
364
365
366 --echo ########################################################################
367 --echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
368 --echo # DDL on INFORMATION_SCHEMA tables are not supported
369 --echo ########################################################################
370 # 3.2.1.3: Ensure that no user may execute an INSERT statement on any
371 # INFORMATION_SCHEMA table.
372 # 3.2.1.4: Ensure that no user may execute an UPDATE statement on any
373 # INFORMATION_SCHEMA table.
374 # 3.2.1.5: Ensure that no user may execute a DELETE statement on any
375 # INFORMATION_SCHEMA table.
376 # 3.2.1.8: Ensure that no user may create an index on an
377 # INFORMATION_SCHEMA table.
378 # 3.2.1.9: Ensure that no user may alter the definition of an
379 # INFORMATION_SCHEMA table.
380 # 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
381 # 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
382 # other database.
383 # 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
384 # in an INFORMATION_SCHEMA table.
385 #
386 --error 0,ER_CANNOT_USER
387 DROP USER 'testuser1'@'localhost';
388 CREATE USER 'testuser1'@'localhost';
389
390 --error ER_DBACCESS_DENIED_ERROR
391 INSERT INTO information_schema.user_privileges
392 SELECT * FROM information_schema.user_privileges;
393
394 --error ER_DBACCESS_DENIED_ERROR
395 UPDATE information_schema.user_privileges
396 SET PRIVILEGE_TYPE = 'gaming';
397
398 --error ER_DBACCESS_DENIED_ERROR
399 DELETE FROM information_schema.user_privileges
400 WHERE grantee = '''testuser1''@''localhost''';
401 --error ER_DBACCESS_DENIED_ERROR
402 TRUNCATE information_schema.user_privileges;
403
404 --error ER_DBACCESS_DENIED_ERROR
405 CREATE INDEX i1 ON information_schema.user_privileges(grantee);
406
407 --error ER_DBACCESS_DENIED_ERROR
408 ALTER TABLE information_schema.user_privileges ADD f1 INT;
409
410 --error ER_DBACCESS_DENIED_ERROR
411 DROP TABLE information_schema.user_privileges;
412
413 --error ER_DBACCESS_DENIED_ERROR
414 ALTER TABLE information_schema.user_privileges
415 RENAME db_datadict.user_privileges;
416 --error ER_DBACCESS_DENIED_ERROR
417 ALTER TABLE information_schema.user_privileges
418 RENAME information_schema.xuser_privileges;
419
420 # Cleanup
421 DROP USER 'testuser1'@'localhost';
422