1 # suite/funcs_1/t/is_schema_privileges.test
3 # Check the layout of information_schema.schema_privileges and the impact of
4 # CREATE/ALTER/DROP TABLE/VIEW/SCHEMA ... on it.
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.
14 # 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
16 # Create this script based on older scripts and new code.
19 # This test cannot be used for the embedded server because we check here
21 --source include/not_embedded.inc
23 let $engine_type = MEMORY;
24 let $other_engine_type = MyISAM;
26 let $is_table = SCHEMA_PRIVILEGES;
28 # The table INFORMATION_SCHEMA.SCHEMA_PRIVILEGES must exist
29 eval SHOW TABLES FROM information_schema LIKE '$is_table';
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.
37 --source suite/funcs_1/datadict/is_table_query.inc
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:
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)
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;
60 # Note: Retrieval of information within information_schema.columns
61 # about information_schema.schema_privileges is in is_columns_is.test.
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;
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
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.
88 DROP DATABASE IF EXISTS db_datadict_1;
89 DROP DATABASE IF EXISTS db_datadict_2;
90 DROP DATABASE IF EXISTS db_datadict_3;
92 CREATE DATABASE db_datadict_1;
93 CREATE DATABASE db_datadict_2;
94 CREATE DATABASE db_datadict_3;
96 CREATE TABLE db_datadict_2.t1(f1 INT, f2 INT, f3 INT)
97 ENGINE = $engine_type;
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';
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';
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';
118 --echo # Establish connection testuser1 (user=testuser1)
119 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
120 connect (testuser1, localhost, testuser1, , test);
121 GRANT SELECT ON db_datadict_4.* TO 'testuser2'@'localhost';
122 --echo # Root granted INSERT db_datadict_1 to me -> visible
123 --echo # Root granted SELECT db_datadict_1 to testuser2 -> invisible
124 --echo # Root granted INSERT db_datadict_2.t1 (no schema-level priv!)
125 --echo # but not db_datadict_2 to me -> invisible
126 --echo # Root granted SELECT db_datadict_3. to testuser2 but not to me -> invisible
127 --echo # Root granted SELECT db_datadict_4. to me -> visible
128 --echo # I granted SELECT db_datadict_4. to testuser2 -> invisible (reality), visible(requirement)
131 eval $show_testuser1;
132 --error ER_DBACCESS_DENIED_ERROR
133 eval $show_testuser2;
135 --echo # Establish connection testuser2 (user=testuser2)
136 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
137 connect (testuser2, localhost, testuser2, , test);
138 --echo # Root granted SELECT db_datadict_1 to me -> visible
139 --echo # Root granted INSERT db_datadict_1 to testuser1 -> invisible
140 --echo # Root granted INSERT db_datadict_2.t1 but not db_datadict_1 to testuser1 -> invisible
141 --echo # Root granted SELECT db_datadict_3. to me -> visible
142 --echo # testuser1 granted SELECT db_datadict_4. to me -> visible
144 --error ER_DBACCESS_DENIED_ERROR
145 eval $show_testuser1;
146 eval $show_testuser2;
148 --echo # Switch to connection default and close connections testuser1 and testuser2
150 disconnect testuser1;
151 disconnect testuser2;
153 eval $show_testuser1;
154 eval $show_testuser2;
157 DROP USER 'testuser1'@'localhost';
158 DROP USER 'testuser2'@'localhost';
159 DROP DATABASE db_datadict_1;
160 DROP DATABASE db_datadict_2;
161 DROP DATABASE db_datadict_3;
164 --echo ################################################################################
165 --echo # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.SCHEMA_PRIVILEGES modifications
166 --echo ################################################################################
167 # 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
168 # column) automatically inserts all relevant information on that
169 # object into every appropriate INFORMATION_SCHEMA table.
170 # 3.2.1.14: Ensure that the alteration of any existing database object
171 # automatically updates all relevant information on that object in
172 # every appropriate INFORMATION_SCHEMA table.
173 # 3.2.1.15: Ensure that the dropping of any existing database object
174 # automatically deletes all relevant information on that object from
175 # every appropriate INFORMATION_SCHEMA table.
178 # The MySQL privilege system allows to GRANT objects before they exist.
179 # (Exception: Grant privileges for columns of not existing tables/views.)
180 # There is also no migration of privileges if objects (tables, views, columns)
181 # are moved to other databases (tables only), renamed or dropped.
184 DROP DATABASE IF EXISTS db_datadict;
186 CREATE DATABASE db_datadict;
187 --error 0,ER_CANNOT_USER
188 DROP USER 'the_user'@'localhost';
189 --error 0,ER_CANNOT_USER
190 DROP USER 'testuser1'@'localhost';
191 CREATE USER 'testuser1'@'localhost';
192 GRANT SELECT ON test.* TO 'testuser1'@'localhost';
194 let $my_select = SELECT * FROM information_schema.schema_privileges
195 WHERE table_schema = 'db_datadict'
196 ORDER BY grantee,table_schema,privilege_type;
198 ############ Check grant SCHEMA
200 --echo # Establish connection testuser1 (user=testuser1)
201 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
202 connect (testuser1, localhost, testuser1, , test);
204 --echo # Switch to connection default
206 GRANT UPDATE ON db_datadict.* TO 'testuser1'@'localhost';
208 --echo # Switch to connection testuser1
210 ############ Check RENAME SCHEMA
211 # Implement this if RENAME SCHEMA is again available.
212 # Note(mleich): I expect that RENAME has no impact on the result sets, because
213 # the schema_name is not migrated.
214 # --echo # Switch to connection default
215 # connection default;
216 # RENAME SCHEMA db_datadict TO db_datadictx;
218 # --echo # Switch to connection testuser1
220 # RENAME SCHEMA db_datadictx TO db_datadict;
221 ############ Check extend PRIVILEGES (affects PRIVILEGE_TYPE) on SCHEMA
222 --echo # Switch to connection default
224 GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost';
226 --echo # Switch to connection testuser1
228 ############ Check extend PRIVILEGES (affects IS_GRANTABLE) on SCHEMA
229 --echo # Switch to connection default
231 GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
233 --echo # Switch to connection testuser1
235 ############ Check DROP SCHEMA
236 # No impact, because there is no "maintenance" of privileges.
237 --echo # Switch to connection default
239 DROP SCHEMA db_datadict;
241 --echo # Switch to connection testuser1
243 ############ Check REVOKE PRIVILEGE
244 --echo # Switch to connection default
246 REVOKE UPDATE ON db_datadict.* FROM 'testuser1'@'localhost';
248 --echo # Switch to connection testuser1
250 ############ Check RENAME USER
251 --echo # Switch to connection default
253 RENAME USER 'testuser1'@'localhost' TO 'the_user'@'localhost';
255 --echo # Switch to connection testuser1
257 --echo # Close connection testuser1
258 disconnect testuser1;
259 --echo # Establish connection the_user (user=the_user)
260 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
261 connect (the_user, localhost, the_user, , test);
263 --echo # Close connection the_user
265 ############ Check DROP USER
266 --echo # Switch to connection default
269 DROP USER 'the_user'@'localhost';
273 --echo ########################################################################
274 --echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
275 --echo # DDL on INFORMATION_SCHEMA table are not supported
276 --echo ########################################################################
277 # 3.2.1.3: Ensure that no user may execute an INSERT statement on any
278 # INFORMATION_SCHEMA table.
279 # 3.2.1.4: Ensure that no user may execute an UPDATE statement on any
280 # INFORMATION_SCHEMA table.
281 # 3.2.1.5: Ensure that no user may execute a DELETE statement on any
282 # INFORMATION_SCHEMA table.
283 # 3.2.1.8: Ensure that no user may create an index on an INFORMATION_SCHEMA table.
284 # 3.2.1.9: Ensure that no user may alter the definition of an
285 # INFORMATION_SCHEMA table.
286 # 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
287 # 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
289 # 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
290 # in an INFORMATION_SCHEMA table.
293 DROP DATABASE IF EXISTS db_datadict;
295 CREATE DATABASE db_datadict;
296 --replace_result $engine_type <engine_type>
298 CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT)
299 ENGINE = $engine_type;
300 --error 0,ER_CANNOT_USER
301 DROP USER 'testuser1'@'localhost';
302 CREATE USER 'testuser1'@'localhost';
303 GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost';
305 --error ER_DBACCESS_DENIED_ERROR
306 INSERT INTO information_schema.schema_privileges
307 SELECT * FROM information_schema.schema_privileges;
309 --error ER_DBACCESS_DENIED_ERROR
310 UPDATE information_schema.schema_privileges SET table_schema = 'test'
311 WHERE table_name = 't1';
313 --error ER_DBACCESS_DENIED_ERROR
314 DELETE FROM information_schema.schema_privileges
315 WHERE table_schema = 'db_datadict';
316 --error ER_DBACCESS_DENIED_ERROR
317 TRUNCATE information_schema.schema_privileges;
319 --error ER_DBACCESS_DENIED_ERROR
320 CREATE INDEX my_idx_on_tables
321 ON information_schema.schema_privileges(table_schema);
322 --error ER_DBACCESS_DENIED_ERROR
323 ALTER TABLE information_schema.schema_privileges ADD f1 INT;
325 --error ER_DBACCESS_DENIED_ERROR
326 DROP TABLE information_schema.schema_privileges;
328 --error ER_DBACCESS_DENIED_ERROR
329 ALTER TABLE information_schema.schema_privileges
330 RENAME db_datadict.schema_privileges;
331 --error ER_DBACCESS_DENIED_ERROR
332 ALTER TABLE information_schema.schema_privileges
333 RENAME information_schema.xschema_privileges;
336 DROP DATABASE db_datadict;
337 DROP USER 'testuser1'@'localhost';