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