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