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