Debian Codegraph @

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

raw
1 # suite/funcs_1/t/is_column_privileges.test
2 #
3 # Check the layout of information_schema.column_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 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 = COLUMN_PRIVILEGES;
27
28 # The table INFORMATION_SCHEMA.COLUMN_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.5.1: INFORMATION_SCHEMA.COLUMN_PRIVILEGES layout
42 --echo #########################################################################
43 # Ensure that the INFORMATION_SCHEMA.COLUMN_PRIVILEGES table has the following
44 # columns, in the following order:
45 #
46 # GRANTEE (shows the name of a user who has either granted,
47 # or been granted a column privilege),
48 # TABLE_CATALOG (always shows NULL),
49 # TABLE_SCHEMA (shows the name of the schema, or database, in which the table
50 # for which a column privilege has been granted resides),
51 # TABLE_NAME (shows the name of the table),
52 # COLUMN_NAME (shows the name of the column on which a column privilege has
53 # been granted),
54 # PRIVILEGE_TYPE (shows the type of privilege that was granted; must be either
55 # SELECT, INSERT, UPDATE, or REFERENCES),
56 # IS_GRANTABLE (shows whether that privilege was granted WITH GRANT OPTION).
57 #
58 --source suite/funcs_1/datadict/datadict_bug_12777.inc
59 eval DESCRIBE information_schema.$is_table;
60 --source suite/funcs_1/datadict/datadict_bug_12777.inc
61 eval SHOW CREATE TABLE information_schema.$is_table;
62 --source suite/funcs_1/datadict/datadict_bug_12777.inc
63 eval SHOW COLUMNS FROM information_schema.$is_table;
64
65 # Note: Retrieval of information within information_schema.columns
66 # about information_schema.column_privileges is in is_columns_is.test.
67
68 # Show that TABLE_CATALOG is always NULL.
69 SELECT table_catalog, table_schema, table_name, column_name, privilege_type
70 FROM information_schema.column_privileges WHERE table_catalog IS NOT NULL;
71
72
73 --echo ######################################################################
74 --echo # Testcase 3.2.5.2+3.2.5.3+3.2.5.4:
75 --echo # INFORMATION_SCHEMA.COLUMN_PRIVILEGES accessible information
76 --echo ######################################################################
77 # 3.2.5.2: Ensure that the table shows the relevant information on every
78 # column privilege which has been granted to the current user or
79 # PUBLIC, or which was granted by the current user.
80 # 3.2.5.3: Ensure that the table does not show any information on any column
81 # privilege which was granted to any user other than the current user
82 # or PUBLIC, or which was granted by any user other than
83 # the current user.
84 # 3.2.5.4: Ensure that the table does not show any information on any
85 # privileges that are not column privileges for the current user.
86 #
87 # Note: Check of content within information_schema.column_privileges about the
88 # databases information_schema, mysql and test is in
89 # is_column_privileges_is_mysql_test.test
90 #
91 --disable_warnings
92 DROP DATABASE IF EXISTS db_datadict;
93 --enable_warnings
94 CREATE DATABASE db_datadict;
95 --replace_result $other_engine_type <other_engine_type>
96 eval
97 CREATE TABLE db_datadict.t1 (f1 INT, f2 DECIMAL, f3 TEXT)
98 ENGINE = $other_engine_type;
99
100 USE db_datadict;
101 --error 0,ER_CANNOT_USER
102 DROP USER 'testuser1'@'localhost';
103 CREATE USER 'testuser1'@'localhost';
104 --error 0,ER_CANNOT_USER
105 DROP USER 'testuser2'@'localhost';
106 CREATE USER 'testuser2'@'localhost';
107 --error 0,ER_CANNOT_USER
108 DROP USER 'testuser3'@'localhost';
109 CREATE USER 'testuser3'@'localhost';
110
111 GRANT SELECT(f1, f3) ON db_datadict.t1 TO 'testuser1'@'localhost';
112 GRANT INSERT(f1) ON db_datadict.t1 TO 'testuser1'@'localhost';
113 GRANT UPDATE(f2) ON db_datadict.t1 TO 'testuser1'@'localhost';
114 GRANT SELECT(f2) ON db_datadict.t1 TO 'testuser2'@'localhost';
115 GRANT INSERT, SELECT ON db_datadict.t1 TO 'testuser3'@'localhost';
116 GRANT SELECT(f3) ON db_datadict.t1 TO 'testuser3'@'localhost';
117
118 GRANT INSERT, SELECT ON db_datadict.t1 TO 'testuser3'@'localhost'
119 WITH GRANT OPTION;
120 GRANT ALL ON db_datadict.* TO 'testuser3'@'localhost';
121
122 let $select= SELECT * FROM information_schema.column_privileges
123 WHERE grantee LIKE '''testuser%'''
124 ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
125 eval $select;
126
127 # Note: WITH GRANT OPTION applies to all privileges on this table
128 # and not to the columns mentioned only.
129 GRANT UPDATE(f3) ON db_datadict.t1 TO 'testuser1'@'localhost'
130 WITH GRANT OPTION;
131
132 eval $select;
133
134 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
135 connect (testuser1, localhost, testuser1, , db_datadict);
136 eval $select;
137
138 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
139 connect (testuser2, localhost, testuser2, , db_datadict);
140 eval $select;
141
142 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
143 connect (testuser3, localhost, testuser3, , db_datadict);
144
145 --echo # FIXME: Is it correct that granted TABLES do not occur in COLUMN_PRIVILEGES?
146 SELECT * FROM information_schema.table_privileges
147 WHERE grantee LIKE '''testuser%'''
148 ORDER BY grantee,table_schema,table_name,privilege_type;
149 SELECT * FROM information_schema.schema_privileges
150 WHERE grantee LIKE '''testuser%'''
151 ORDER BY grantee,table_schema,privilege_type;
152 eval $select;
153 GRANT SELECT(f1, f3) ON db_datadict.t1 TO 'testuser2'@'localhost';
154
155 --echo # FIXME: Is it intended that *my* grants to others are *NOT* shown here?
156 eval $select;
157
158 connection testuser2;
159 eval $select;
160
161 # Cleanup
162 connection default;
163 disconnect testuser1;
164 disconnect testuser2;
165 disconnect testuser3;
166 DROP DATABASE db_datadict;
167 DROP USER 'testuser1'@'localhost';
168 DROP USER 'testuser2'@'localhost';
169 DROP USER 'testuser3'@'localhost';
170
171
172 --echo ################################################################################
173 --echo # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.COLUMN_PRIVILEGES modifications
174 --echo ################################################################################
175 # 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
176 # column) automatically inserts all relevant information on that
177 # object into every appropriate INFORMATION_SCHEMA table.
178 # 3.2.1.14: Ensure that the alteration of any existing database object
179 # automatically updates all relevant information on that object in
180 # every appropriate INFORMATION_SCHEMA table.
181 # 3.2.1.15: Ensure that the dropping of any existing database object
182 # automatically deletes all relevant information on that object from
183 # every appropriate INFORMATION_SCHEMA table.
184 #
185 # Note (mleich):
186 # The MySQL privilege system allows to GRANT objects before they exist.
187 # (Exception: Grant privileges for columns of not existing tables/views.)
188 # There is also no migration of privileges if objects (tables, views, columns)
189 # are moved to other databases (tables only), renamed or dropped.
190 #
191 --disable_warnings
192 DROP DATABASE IF EXISTS db_datadict;
193 --enable_warnings
194 CREATE DATABASE db_datadict;
195 --replace_result $engine_type <engine_type>
196 eval
197 CREATE TABLE db_datadict.my_table (f1 BIGINT, f2 CHAR(10), f3 DATE)
198 ENGINE = $engine_type;
199
200 --error 0,ER_CANNOT_USER
201 DROP USER 'testuser1'@'localhost';
202 CREATE USER 'testuser1'@'localhost';
203 GRANT ALL ON test.* TO 'testuser1'@'localhost';
204
205 let $my_select = SELECT * FROM information_schema.column_privileges
206 WHERE table_name = 'my_table'
207 ORDER BY grantee, table_schema,table_name,column_name,privilege_type;
208 let $my_show = SHOW GRANTS FOR 'testuser1'@'localhost';
209 eval $my_select;
210 eval $my_show;
211
212 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
213 connect (testuser1, localhost, testuser1, , test);
214 eval $my_select;
215 eval $my_show;
216
217 connection default;
218 GRANT SELECT (f1,f3) ON db_datadict.my_table TO 'testuser1'@'localhost';
219 eval $my_select;
220 eval $my_show;
221
222 connection testuser1;
223 eval $my_select;
224 eval $my_show;
225
226 connection default;
227 ALTER TABLE db_datadict.my_table DROP COLUMN f3;
228 GRANT UPDATE (f1) ON db_datadict.my_table TO 'testuser1'@'localhost';
229 eval $my_select;
230 eval $my_show;
231
232 connection testuser1;
233 eval $my_select;
234 eval $my_show;
235 --error ER_BAD_FIELD_ERROR
236 SELECT f1, f3 FROM db_datadict.my_table;
237
238 connection default;
239 ALTER TABLE db_datadict.my_table CHANGE COLUMN f1 my_col BIGINT;
240 eval $my_select;
241 eval $my_show;
242
243 connection testuser1;
244 eval $my_select;
245 eval $my_show;
246
247 connection default;
248 DROP TABLE db_datadict.my_table;
249 eval $my_select;
250 eval $my_show;
251
252 connection testuser1;
253 eval $my_select;
254 eval $my_show;
255
256 connection default;
257 REVOKE ALL ON db_datadict.my_table FROM 'testuser1'@'localhost';
258 eval $my_select;
259 eval $my_show;
260
261 connection testuser1;
262 eval $my_select;
263 eval $my_show;
264
265 connection default;
266 disconnect testuser1;
267 DROP USER 'testuser1'@'localhost';
268 DROP DATABASE db_datadict;
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
282 # INFORMATION_SCHEMA table.
283 # 3.2.1.9: Ensure that no user may alter the definition of an
284 # INFORMATION_SCHEMA table.
285 # 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
286 # 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
287 # other database.
288 # 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
289 # in an INFORMATION_SCHEMA table.
290 #
291 --disable_warnings
292 DROP DATABASE IF EXISTS db_datadict;
293 --enable_warnings
294 CREATE DATABASE db_datadict;
295 --replace_result $engine_type <engine_type>
296 eval
297 CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT)
298 ENGINE = $engine_type;
299 --error 0,ER_CANNOT_USER
300 DROP USER 'testuser1'@'localhost';
301 CREATE USER 'testuser1'@'localhost';
302 GRANT SELECT (f1) ON db_datadict.t1 TO 'testuser1'@'localhost';
303
304 --error ER_DBACCESS_DENIED_ERROR
305 INSERT INTO information_schema.column_privileges
306 SELECT * FROM information_schema.column_privileges;
307
308 --error ER_DBACCESS_DENIED_ERROR
309 UPDATE information_schema.column_privileges SET table_schema = 'test'
310 WHERE table_name = 't1';
311
312 --error ER_DBACCESS_DENIED_ERROR
313 DELETE FROM information_schema.column_privileges WHERE table_name = 't1';
314 --error ER_DBACCESS_DENIED_ERROR
315 TRUNCATE information_schema.column_privileges;
316
317 --error ER_DBACCESS_DENIED_ERROR
318 CREATE INDEX my_idx_on_tables
319 ON information_schema.column_privileges(table_schema);
320 --error ER_DBACCESS_DENIED_ERROR
321 ALTER TABLE information_schema.column_privileges ADD f1 INT;
322
323 --error ER_DBACCESS_DENIED_ERROR
324 DROP TABLE information_schema.column_privileges;
325
326 --error ER_DBACCESS_DENIED_ERROR
327 ALTER TABLE information_schema.column_privileges
328 RENAME db_datadict.column_privileges;
329 --error ER_DBACCESS_DENIED_ERROR
330 ALTER TABLE information_schema.column_privileges
331 RENAME information_schema.xcolumn_privileges;
332
333 # Cleanup
334 DROP DATABASE db_datadict;
335 DROP USER 'testuser1'@'localhost';
336