1
# suite/funcs_1/t/is_user_privileges.test
3
# Check the layout of information_schema.user_privileges, permissions and
4
# the impact of CREATE/ALTER/DROP SCHEMA on it.
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.
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 = USER_PRIVILEGES;
28
# The table INFORMATION_SCHEMA.USER_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.16.1: INFORMATION_SCHEMA.USER_PRIVILEGES layout
42
--echo #########################################################################
43
# Ensure that the INFORMATION_SCHEMA.USER_PRIVILEGES table has the following columns,
44
# in the following order:
46
# GRANTEE (shows a user to whom a user privilege has been granted),
47
# TABLE_CATALOG (always shows NULL),
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 about
59
# information_schema.user_privileges is in is_columns_is.test.
61
# Show that TABLE_CATALOG is always NULL.
62
SELECT grantee, table_catalog, privilege_type
63
FROM information_schema.user_privileges
64
WHERE table_catalog IS NOT NULL;
67
--echo ##########################################################################
68
--echo # Testcases 3.2.16.2+3.2.16.3+3.2.16.4: INFORMATION_SCHEMA.USER_PRIVILEGES
69
--echo # accessible information
70
--echo ##########################################################################
71
# 3.2.16.2: Ensure that the table shows the relevant information on every user
72
# privilege which has been granted to the current user or to PUBLIC,
73
# or has been granted by the current user.
74
# 3.2.16.3: Ensure that the table does not show any information on any user
75
# privileges which have been granted to users other than the current
76
# user or have been granted by any user other than the current user.
77
# 3.2.16.4: Ensure that the table does not show any information on any
78
# privileges that are not user privileges for the current user.
81
DROP DATABASE IF EXISTS db_datadict;
83
CREATE DATABASE db_datadict;
85
--error 0,ER_CANNOT_USER
86
DROP USER 'testuser1'@'localhost';
87
CREATE USER 'testuser1'@'localhost';
88
--error 0,ER_CANNOT_USER
89
DROP USER 'testuser2'@'localhost';
90
CREATE USER 'testuser2'@'localhost';
91
--error 0,ER_CANNOT_USER
92
DROP USER 'testuser3'@'localhost';
93
CREATE USER 'testuser3'@'localhost';
95
GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost';
96
GRANT SELECT ON mysql.user TO 'testuser1'@'localhost';
98
GRANT INSERT ON *.* TO 'testuser2'@'localhost';
99
GRANT UPDATE ON *.* TO 'testuser2'@'localhost';
101
let $my_select1= SELECT * FROM information_schema.user_privileges
102
WHERE grantee LIKE '''testuser%'''
103
ORDER BY grantee, table_catalog, privilege_type;
104
let $my_select2= SELECT * FROM mysql.user
105
WHERE user LIKE 'testuser%' ORDER BY host, user;
106
let $my_show= SHOW GRANTS;
111
--echo # Add GRANT OPTION db_datadict.* to testuser1;
112
GRANT UPDATE ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
116
--echo # Establish connection testuser1 (user=testuser1)
117
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
118
connect (testuser1, localhost, testuser1, , db_datadict);
124
--echo # Now add SELECT on *.* to testuser1;
126
--echo # Switch to connection default
128
GRANT SELECT ON *.* TO 'testuser1'@'localhost';
130
--echo # Here <SELECT NO> is shown correctly for testuser1;
134
GRANT SELECT ON *.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
136
--echo # Here <SELECT YES> is shown correctly for testuser1;
140
--echo # Switch to connection testuser1
141
# check that this appears
142
connection testuser1;
147
--echo # Establish connection testuser2 (user=testuser2)
148
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
149
connect (testuser2, localhost, testuser2, , db_datadict);
151
--error ER_TABLEACCESS_DENIED_ERROR
155
--echo # Establish connection testuser3 (user=testuser3)
156
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
157
connect (testuser3, localhost, testuser3, , test);
159
--error ER_TABLEACCESS_DENIED_ERROR
164
--echo # Revoke privileges from testuser1;
165
--echo # Switch to connection default
167
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser1'@'localhost';
171
--echo # Switch to connection testuser1
173
connection testuser1;
175
--error ER_TABLEACCESS_DENIED_ERROR
179
# OK, testuser1 has no privs here
180
--error ER_TABLEACCESS_DENIED_ERROR
181
CREATE TABLE db_datadict.tb_55 ( c1 TEXT );
183
--error ER_TABLEACCESS_DENIED_ERROR
186
# OK, testuser1 has no privs here
187
--error ER_TABLEACCESS_DENIED_ERROR
188
CREATE TABLE db_datadict.tb_66 ( c1 TEXT );
191
--echo # Add ALL on db_datadict.* (and select on mysql.user) to testuser1;
192
--echo # Switch to connection default
194
GRANT ALL ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
195
GRANT SELECT ON mysql.user TO 'testuser1'@'localhost';
199
--echo # Switch to connection testuser1
200
connection testuser1;
205
# OK, testuser1 has no privs here
206
--error ER_TABLEACCESS_DENIED_ERROR
207
CREATE TABLE db_datadict.tb_56 ( c1 TEXT );
209
# using 'USE' lets the server read the privileges new, so now the CREATE works
214
--replace_result $other_engine_type <other_engine_type>
216
CREATE TABLE tb_57 ( c1 TEXT )
217
ENGINE = $other_engine_type;
220
--echo # Revoke privileges from testuser1;
221
--echo # Switch to connection default
223
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser1'@'localhost';
227
--echo # Switch to connection testuser1
229
connection testuser1;
231
--error ER_TABLEACCESS_DENIED_ERROR
234
# WORKS, as the existing old privileges are used!
235
--replace_result $other_engine_type <other_engine_type>
237
CREATE TABLE db_datadict.tb_58 ( c1 TEXT )
238
ENGINE = $other_engine_type;
239
# existing privileges are "read" new when USE is called, user has no privileges
240
--error ER_DBACCESS_DENIED_ERROR
242
#FIXME 3.2.16: check that it is correct that this now 'works': --error ER_TABLEACCESS_DENIED_ERROR
243
--replace_result $other_engine_type <other_engine_type>
245
CREATE TABLE db_datadict.tb_59 ( c1 TEXT )
246
ENGINE = $other_engine_type;
249
--echo # Switch to connection default and close connections testuser1,testuser2,testuser3
251
disconnect testuser1;
252
disconnect testuser2;
253
disconnect testuser3;
254
DROP USER 'testuser1'@'localhost';
255
DROP USER 'testuser2'@'localhost';
256
DROP USER 'testuser3'@'localhost';
257
DROP DATABASE IF EXISTS db_datadict;
260
--echo ########################################################################################
261
--echo # Testcases 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.USER_PRIVILEGES modifications
262
--echo ########################################################################################
263
# 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
264
# column) automatically inserts all relevant information on that
265
# object into every appropriate INFORMATION_SCHEMA table.
266
# 3.2.1.14: Ensure that the alteration of any existing database object
267
# automatically updates all relevant information on that object in
268
# every appropriate INFORMATION_SCHEMA table.
269
# 3.2.1.15: Ensure that the dropping of any existing database object
270
# automatically deletes all relevant information on that object from
271
# every appropriate INFORMATION_SCHEMA table.
273
let $my_select = SELECT * FROM information_schema.user_privileges
274
WHERE grantee = '''testuser1''@''localhost''';
275
let $my_show = SHOW GRANTS FOR 'testuser1'@'localhost';
277
--error ER_NONEXISTING_GRANT
279
--error 0,ER_CANNOT_USER
280
DROP USER 'testuser1'@'localhost';
281
CREATE USER 'testuser1'@'localhost';
284
GRANT SELECT, FILE ON *.* TO 'testuser1'@'localhost';
287
DROP USER 'testuser1'@'localhost';
289
--error ER_NONEXISTING_GRANT
293
--echo ########################################################################
294
--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
295
--echo # DDL on INFORMATION_SCHEMA tables are not supported
296
--echo ########################################################################
297
# 3.2.1.3: Ensure that no user may execute an INSERT statement on any
298
# INFORMATION_SCHEMA table.
299
# 3.2.1.4: Ensure that no user may execute an UPDATE statement on any
300
# INFORMATION_SCHEMA table.
301
# 3.2.1.5: Ensure that no user may execute a DELETE statement on any
302
# INFORMATION_SCHEMA table.
303
# 3.2.1.8: Ensure that no user may create an index on an
304
# INFORMATION_SCHEMA table.
305
# 3.2.1.9: Ensure that no user may alter the definition of an
306
# INFORMATION_SCHEMA table.
307
# 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
308
# 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
310
# 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
311
# in an INFORMATION_SCHEMA table.
313
--error 0,ER_CANNOT_USER
314
DROP USER 'testuser1'@'localhost';
315
CREATE USER 'testuser1'@'localhost';
317
--error ER_DBACCESS_DENIED_ERROR
318
INSERT INTO information_schema.user_privileges
319
SELECT * FROM information_schema.user_privileges;
321
--error ER_DBACCESS_DENIED_ERROR
322
UPDATE information_schema.user_privileges
323
SET PRIVILEGE_TYPE = 'gaming';
325
--error ER_DBACCESS_DENIED_ERROR
326
DELETE FROM information_schema.user_privileges
327
WHERE grantee = '''testuser1''@''localhost''';
328
--error ER_DBACCESS_DENIED_ERROR
329
TRUNCATE information_schema.user_privileges;
331
--error ER_DBACCESS_DENIED_ERROR
332
CREATE INDEX i1 ON information_schema.user_privileges(grantee);
334
--error ER_DBACCESS_DENIED_ERROR
335
ALTER TABLE information_schema.user_privileges ADD f1 INT;
337
--error ER_DBACCESS_DENIED_ERROR
338
DROP TABLE information_schema.user_privileges;
340
--error ER_DBACCESS_DENIED_ERROR
341
ALTER TABLE information_schema.user_privileges
342
RENAME db_datadict.user_privileges;
343
--error ER_DBACCESS_DENIED_ERROR
344
ALTER TABLE information_schema.user_privileges
345
RENAME information_schema.xuser_privileges;
348
DROP USER 'testuser1'@'localhost';