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
# --source suite/funcs_1/datadict/datadict.pre
21
let $engine_type = MEMORY;
22
let $other_engine_type = MyISAM;
24
let $is_table = SCHEMA_PRIVILEGES;
26
# The table INFORMATION_SCHEMA.SCHEMA_PRIVILEGES must exist
27
eval SHOW TABLES FROM information_schema LIKE '$is_table';
29
--echo #######################################################################
30
--echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
31
--echo #######################################################################
32
# Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
33
# statement, just as if it were an ordinary user-defined table.
35
--source suite/funcs_1/datadict/is_table_query.inc
38
--echo #########################################################################
39
--echo # Testcase 3.2.15.1: INFORMATION_SCHEMA.SCHEMA_PRIVILEGES layout
40
--echo #########################################################################
41
# Ensure that the INFORMATION_SCHEMA.SCHEMA_PRIVILEGES table has the following
42
# columns, in the following order:
44
# GRANTEE (shows a user to whom a schema privilege has been granted),
45
# TABLE_CATALOG (always shows NULL),
46
# TABLE_SCHEMA (shows the name of the database, or schema, on which the
47
# privilege has been granted),
48
# PRIVILEGE_TYPE (shows the granted privilege),
49
# IS_GRANTABLE (shows whether the privilege was granted WITH GRANT OPTION)
51
--source suite/funcs_1/datadict/datadict_bug_12777.inc
52
eval DESCRIBE information_schema.$is_table;
53
--source suite/funcs_1/datadict/datadict_bug_12777.inc
54
eval SHOW CREATE TABLE information_schema.$is_table;
55
--source suite/funcs_1/datadict/datadict_bug_12777.inc
56
eval SHOW COLUMNS FROM information_schema.$is_table;
58
# Note: Retrieval of information within information_schema.columns
59
# about information_schema.schema_privileges is in is_columns_is.test.
61
# Show that TABLE_CATALOG is always NULL.
62
SELECT GRANTEE, TABLE_CATALOG, TABLE_SCHEMA, PRIVILEGE_TYPE
63
FROM information_schema.schema_privileges WHERE table_catalog IS NOT NULL;
66
--echo ###############################################################################
67
--echo # Testcase 3.2.15.2-3.2.15.4 INFORMATION_SCHEMA.SCHEMA_PRIVILEGES accessibility
68
--echo ###############################################################################
69
# 3.2.15.2 Ensure that the table shows the relevant information on every
70
# schema-level privilege which has been granted to the current user
71
# or to PUBLIC, or has been granted by the current user.
72
# FIXME: Why is "or has been granted by the current user" invisible?
73
# 3.2.15.3 Ensure that the table does not show any information on any
74
# schema-level privileges which have been granted to users
75
# other than the current user or to PUBLIC, or that have been
76
# granted by any user other than the current user.
77
# 3.2.15.4 Ensure that the table does not show any information on any
78
# privileges that are not schema-level privileges for the
81
# Note: Check of content within information_schema.schema_privileges about the
82
# databases information_schema, mysql and test is in
83
# is_schema_privileges_is_mysql_test.
86
DROP DATABASE IF EXISTS db_datadict_1;
87
DROP DATABASE IF EXISTS db_datadict_2;
88
DROP DATABASE IF EXISTS db_datadict_3;
90
CREATE DATABASE db_datadict_1;
91
CREATE DATABASE db_datadict_2;
92
CREATE DATABASE db_datadict_3;
94
CREATE TABLE db_datadict_2.t1(f1 INT, f2 INT, f3 INT)
95
ENGINE = $engine_type;
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';
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';
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';
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)
129
eval $show_testuser1;
130
--error ER_DBACCESS_DENIED_ERROR
131
eval $show_testuser2;
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
142
--error ER_DBACCESS_DENIED_ERROR
143
eval $show_testuser1;
144
eval $show_testuser2;
146
--echo # Switch to connection default and close connections testuser1 and testuser2
148
disconnect testuser1;
149
disconnect testuser2;
151
eval $show_testuser1;
152
eval $show_testuser2;
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;
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.
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.
182
DROP DATABASE IF EXISTS db_datadict;
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';
192
let $my_select = SELECT * FROM information_schema.schema_privileges
193
WHERE table_schema = 'db_datadict'
194
ORDER BY grantee,table_schema,privilege_type;
196
############ Check grant SCHEMA
198
--echo # Establish connection testuser1 (user=testuser1)
199
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
200
connect (testuser1, localhost, testuser1, , test);
202
--echo # Switch to connection default
204
GRANT UPDATE ON db_datadict.* TO 'testuser1'@'localhost';
206
--echo # Switch to connection testuser1
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;
216
# --echo # Switch to connection testuser1
218
# RENAME SCHEMA db_datadictx TO db_datadict;
219
############ Check extend PRIVILEGES (affects PRIVILEGE_TYPE) on SCHEMA
220
--echo # Switch to connection default
222
GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost';
224
--echo # Switch to connection testuser1
226
############ Check extend PRIVILEGES (affects IS_GRANTABLE) on SCHEMA
227
--echo # Switch to connection default
229
GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
231
--echo # Switch to connection testuser1
233
############ Check DROP SCHEMA
234
# No impact, because there is no "maintenance" of privileges.
235
--echo # Switch to connection default
237
DROP SCHEMA db_datadict;
239
--echo # Switch to connection testuser1
241
############ Check REVOKE PRIVILEGE
242
--echo # Switch to connection default
244
REVOKE UPDATE ON db_datadict.* FROM 'testuser1'@'localhost';
246
--echo # Switch to connection testuser1
248
############ Check RENAME USER
249
--echo # Switch to connection default
251
RENAME USER 'testuser1'@'localhost' TO 'the_user'@'localhost';
253
--echo # Switch to connection testuser1
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);
261
--echo # Close connection the_user
263
############ Check DROP USER
264
--echo # Switch to connection default
267
DROP USER 'the_user'@'localhost';
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
287
# 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
288
# in an INFORMATION_SCHEMA table.
291
DROP DATABASE IF EXISTS db_datadict;
293
CREATE DATABASE db_datadict;
294
--replace_result $engine_type <engine_type>
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';
303
--error ER_DBACCESS_DENIED_ERROR
304
INSERT INTO information_schema.schema_privileges
305
SELECT * FROM information_schema.schema_privileges;
307
--error ER_DBACCESS_DENIED_ERROR
308
UPDATE information_schema.schema_privileges SET table_schema = 'test'
309
WHERE table_name = 't1';
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;
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;
323
--error ER_DBACCESS_DENIED_ERROR
324
DROP TABLE information_schema.schema_privileges;
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;
334
DROP DATABASE db_datadict;
335
DROP USER 'testuser1'@'localhost';
added
removed
mysql-test/suite/funcs_1/t/is_schema_privileges.test
