Debian Codegraph @

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

raw
1 # suite/funcs_1/t/is_schema_privileges.test
2 #
3 # Check the layout of information_schema.schema_privileges and the impact of
4 # CREATE/ALTER/DROP TABLE/VIEW/SCHEMA ... on it.
5 #
6 # Note:
7 # This test is not intended
8 # - to show information about the all time existing schemas
9 # 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 = SCHEMA_PRIVILEGES;
24
25 # The table INFORMATION_SCHEMA.SCHEMA_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.15.1: INFORMATION_SCHEMA.SCHEMA_PRIVILEGES layout
39 --echo #########################################################################
40 # Ensure that the INFORMATION_SCHEMA.SCHEMA_PRIVILEGES table has the following
41 # columns, in the following order:
42 #
43 # GRANTEE (shows a user to whom a schema privilege has been granted),
44 # TABLE_CATALOG (always shows NULL),
45 # TABLE_SCHEMA (shows the name of the database, or schema, on which the
46 # privilege has been granted),
47 # PRIVILEGE_TYPE (shows the granted privilege),
48 # IS_GRANTABLE (shows whether the privilege was granted WITH GRANT OPTION)
49 #
50 --source suite/funcs_1/datadict/datadict_bug_12777.inc
51 eval DESCRIBE information_schema.$is_table;
52 --source suite/funcs_1/datadict/datadict_bug_12777.inc
53 eval SHOW CREATE TABLE information_schema.$is_table;
54 --source suite/funcs_1/datadict/datadict_bug_12777.inc
55 eval SHOW COLUMNS FROM information_schema.$is_table;
56
57 # Note: Retrieval of information within information_schema.columns
58 # about information_schema.schema_privileges is in is_columns_is.test.
59
60 # Show that TABLE_CATALOG is always NULL.
61 SELECT GRANTEE, TABLE_CATALOG, TABLE_SCHEMA, PRIVILEGE_TYPE
62 FROM information_schema.schema_privileges WHERE table_catalog IS NOT NULL;
63
64
65 --echo ###############################################################################
66 --echo # Testcase 3.2.15.2-3.2.15.4 INFORMATION_SCHEMA.SCHEMA_PRIVILEGES accessibility
67 --echo ###############################################################################
68 # 3.2.15.2 Ensure that the table shows the relevant information on every
69 # schema-level privilege which has been granted to the current user
70 # or to PUBLIC, or has been granted by the current user.
71 # FIXME: Why is "or has been granted by the current user" invisible?
72 # 3.2.15.3 Ensure that the table does not show any information on any
73 # schema-level privileges which have been granted to users
74 # other than the current user or to PUBLIC, or that have been
75 # granted by any user other than the current user.
76 # 3.2.15.4 Ensure that the table does not show any information on any
77 # privileges that are not schema-level privileges for the
78 # current user.
79 #
80 # Note: Check of content within information_schema.schema_privileges about the
81 # databases information_schema, mysql and test is in
82 # is_schema_privileges_is_mysql_test.
83 #
84 --disable_warnings
85 DROP DATABASE IF EXISTS db_datadict_1;
86 DROP DATABASE IF EXISTS db_datadict_2;
87 DROP DATABASE IF EXISTS db_datadict_3;
88 --enable_warnings
89 CREATE DATABASE db_datadict_1;
90 CREATE DATABASE db_datadict_2;
91 CREATE DATABASE db_datadict_3;
92 --replace_result $engine_type <engine_type>
93 eval
94 CREATE TABLE db_datadict_2.t1(f1 INT, f2 INT, f3 INT)
95 ENGINE = $engine_type;
96
97 --error 0,ER_CANNOT_USER
98 DROP USER 'testuser1'@'localhost';
99 CREATE USER 'testuser1'@'localhost';
100 --error 0,ER_CANNOT_USER
101 DROP USER 'testuser2'@'localhost';
102 CREATE USER 'testuser2'@'localhost';
103
104 GRANT INSERT ON db_datadict_1.* TO 'testuser1'@'localhost';
105 GRANT INSERT ON db_datadict_2.t1 TO 'testuser1'@'localhost';
106 GRANT SELECT ON db_datadict_4.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
107 GRANT SELECT ON db_datadict_3.* TO 'testuser2'@'localhost';
108 GRANT SELECT ON db_datadict_1.* TO 'testuser2'@'localhost';
109
110 let $my_select = SELECT * FROM information_schema.schema_privileges
111 WHERE table_schema LIKE 'db_datadict%'
112 ORDER BY grantee,table_schema,privilege_type;
113 let $show_testuser1 = SHOW GRANTS FOR 'testuser1'@'localhost';
114 let $show_testuser2 = SHOW GRANTS FOR 'testuser2'@'localhost';
115
116 --echo # Establish connection testuser1 (user=testuser1)
117 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
118 connect (testuser1, localhost, testuser1, , test);
119 GRANT SELECT ON db_datadict_4.* TO 'testuser2'@'localhost';
120 --echo # Root granted INSERT db_datadict_1 to me -> visible
121 --echo # Root granted SELECT db_datadict_1 to testuser2 -> invisible
122 --echo # Root granted INSERT db_datadict_2.t1 (no schema-level priv!)
123 --echo # but not db_datadict_2 to me -> invisible
124 --echo # Root granted SELECT db_datadict_3. to testuser2 but not to me -> invisible
125 --echo # Root granted SELECT db_datadict_4. to me -> visible
126 --echo # I granted SELECT db_datadict_4. to testuser2 -> invisible (reality), visible(requirement)
127 --echo # FIXME
128 eval $my_select;
129 eval $show_testuser1;
130 --error ER_TABLEACCESS_DENIED_ERROR
131 eval $show_testuser2;
132
133 --echo # Establish connection testuser2 (user=testuser2)
134 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
135 connect (testuser2, localhost, testuser2, , test);
136 --echo # Root granted SELECT db_datadict_1 to me -> visible
137 --echo # Root granted INSERT db_datadict_1 to testuser1 -> invisible
138 --echo # Root granted INSERT db_datadict_2.t1 but not db_datadict_1 to testuser1 -> invisible
139 --echo # Root granted SELECT db_datadict_3. to me -> visible
140 --echo # testuser1 granted SELECT db_datadict_4. to me -> visible
141 eval $my_select;
142 --error ER_TABLEACCESS_DENIED_ERROR
143 eval $show_testuser1;
144 eval $show_testuser2;
145
146 --echo # Switch to connection default and close connections testuser1 and testuser2
147 connection default;
148 disconnect testuser1;
149 disconnect testuser2;
150 eval $my_select;
151 eval $show_testuser1;
152 eval $show_testuser2;
153
154 # Cleanup
155 DROP USER 'testuser1'@'localhost';
156 DROP USER 'testuser2'@'localhost';
157 DROP DATABASE db_datadict_1;
158 DROP DATABASE db_datadict_2;
159 DROP DATABASE db_datadict_3;
160
161
162 --echo ################################################################################
163 --echo # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.SCHEMA_PRIVILEGES modifications
164 --echo ################################################################################
165 # 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
166 # column) automatically inserts all relevant information on that
167 # object into every appropriate INFORMATION_SCHEMA table.
168 # 3.2.1.14: Ensure that the alteration of any existing database object
169 # automatically updates all relevant information on that object in
170 # every appropriate INFORMATION_SCHEMA table.
171 # 3.2.1.15: Ensure that the dropping of any existing database object
172 # automatically deletes all relevant information on that object from
173 # every appropriate INFORMATION_SCHEMA table.
174 #
175 # Note (mleich):
176 # The MySQL privilege system allows to GRANT objects before they exist.
177 # (Exception: Grant privileges for columns of not existing tables/views.)
178 # There is also no migration of privileges if objects (tables, views, columns)
179 # are moved to other databases (tables only), renamed or dropped.
180 #
181 --disable_warnings
182 DROP DATABASE IF EXISTS db_datadict;
183 --enable_warnings
184 CREATE DATABASE db_datadict;
185 --error 0,ER_CANNOT_USER
186 DROP USER 'the_user'@'localhost';
187 --error 0,ER_CANNOT_USER
188 DROP USER 'testuser1'@'localhost';
189 CREATE USER 'testuser1'@'localhost';
190 GRANT SELECT ON test.* TO 'testuser1'@'localhost';
191
192 let $my_select = SELECT * FROM information_schema.schema_privileges
193 WHERE table_schema = 'db_datadict'
194 ORDER BY grantee,table_schema,privilege_type;
195
196 ############ Check grant SCHEMA
197 eval $my_select;
198 --echo # Establish connection testuser1 (user=testuser1)
199 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
200 connect (testuser1, localhost, testuser1, , test);
201 eval $my_select;
202 --echo # Switch to connection default
203 connection default;
204 GRANT UPDATE ON db_datadict.* TO 'testuser1'@'localhost';
205 eval $my_select;
206 --echo # Switch to connection testuser1
207 eval $my_select;
208 ############ Check RENAME SCHEMA
209 # Implement this if RENAME SCHEMA is again available.
210 # Note(mleich): I expect that RENAME has no impact on the result sets, because
211 # the schema_name is not migrated.
212 # --echo # Switch to connection default
213 # connection default;
214 # RENAME SCHEMA db_datadict TO db_datadictx;
215 # eval $my_select;
216 # --echo # Switch to connection testuser1
217 # eval $my_select;
218 # RENAME SCHEMA db_datadictx TO db_datadict;
219 ############ Check extend PRIVILEGES (affects PRIVILEGE_TYPE) on SCHEMA
220 --echo # Switch to connection default
221 connection default;
222 GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost';
223 eval $my_select;
224 --echo # Switch to connection testuser1
225 eval $my_select;
226 ############ Check extend PRIVILEGES (affects IS_GRANTABLE) on SCHEMA
227 --echo # Switch to connection default
228 connection default;
229 GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
230 eval $my_select;
231 --echo # Switch to connection testuser1
232 eval $my_select;
233 ############ Check DROP SCHEMA
234 # No impact, because there is no "maintenance" of privileges.
235 --echo # Switch to connection default
236 connection default;
237 DROP SCHEMA db_datadict;
238 eval $my_select;
239 --echo # Switch to connection testuser1
240 eval $my_select;
241 ############ Check REVOKE PRIVILEGE
242 --echo # Switch to connection default
243 connection default;
244 REVOKE UPDATE ON db_datadict.* FROM 'testuser1'@'localhost';
245 eval $my_select;
246 --echo # Switch to connection testuser1
247 eval $my_select;
248 ############ Check RENAME USER
249 --echo # Switch to connection default
250 connection default;
251 RENAME USER 'testuser1'@'localhost' TO 'the_user'@'localhost';
252 eval $my_select;
253 --echo # Switch to connection testuser1
254 eval $my_select;
255 --echo # Close connection testuser1
256 disconnect testuser1;
257 --echo # Establish connection the_user (user=the_user)
258 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
259 connect (the_user, localhost, the_user, , test);
260 eval $my_select;
261 --echo # Close connection the_user
262 disconnect the_user;
263 ############ Check DROP USER
264 --echo # Switch to connection default
265 connection default;
266 eval $my_select;
267 DROP USER 'the_user'@'localhost';
268 eval $my_select;
269
270
271 --echo ########################################################################
272 --echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
273 --echo # DDL on INFORMATION_SCHEMA table are not supported
274 --echo ########################################################################
275 # 3.2.1.3: Ensure that no user may execute an INSERT statement on any
276 # INFORMATION_SCHEMA table.
277 # 3.2.1.4: Ensure that no user may execute an UPDATE statement on any
278 # INFORMATION_SCHEMA table.
279 # 3.2.1.5: Ensure that no user may execute a DELETE statement on any
280 # INFORMATION_SCHEMA table.
281 # 3.2.1.8: Ensure that no user may create an index on an INFORMATION_SCHEMA table.
282 # 3.2.1.9: Ensure that no user may alter the definition of an
283 # INFORMATION_SCHEMA table.
284 # 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
285 # 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
286 # other database.
287 # 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
288 # in an INFORMATION_SCHEMA table.
289 #
290 --disable_warnings
291 DROP DATABASE IF EXISTS db_datadict;
292 --enable_warnings
293 CREATE DATABASE db_datadict;
294 --replace_result $engine_type <engine_type>
295 eval
296 CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT)
297 ENGINE = $engine_type;
298 --error 0,ER_CANNOT_USER
299 DROP USER 'testuser1'@'localhost';
300 CREATE USER 'testuser1'@'localhost';
301 GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost';
302
303 --error ER_DBACCESS_DENIED_ERROR
304 INSERT INTO information_schema.schema_privileges
305 SELECT * FROM information_schema.schema_privileges;
306
307 --error ER_DBACCESS_DENIED_ERROR
308 UPDATE information_schema.schema_privileges SET table_schema = 'test'
309 WHERE table_name = 't1';
310
311 --error ER_DBACCESS_DENIED_ERROR
312 DELETE FROM information_schema.schema_privileges
313 WHERE table_schema = 'db_datadict';
314 --error ER_DBACCESS_DENIED_ERROR
315 TRUNCATE information_schema.schema_privileges;
316
317 --error ER_DBACCESS_DENIED_ERROR
318 CREATE INDEX my_idx_on_tables
319 ON information_schema.schema_privileges(table_schema);
320 --error ER_DBACCESS_DENIED_ERROR
321 ALTER TABLE information_schema.schema_privileges ADD f1 INT;
322
323 --error ER_DBACCESS_DENIED_ERROR
324 DROP TABLE information_schema.schema_privileges;
325
326 --error ER_DBACCESS_DENIED_ERROR
327 ALTER TABLE information_schema.schema_privileges
328 RENAME db_datadict.schema_privileges;
329 --error ER_DBACCESS_DENIED_ERROR
330 ALTER TABLE information_schema.schema_privileges
331 RENAME information_schema.xschema_privileges;
332
333 # Cleanup
334 DROP DATABASE db_datadict;
335 DROP USER 'testuser1'@'localhost';
336