1
SHOW TABLES FROM information_schema LIKE 'USER_PRIVILEGES';
2
Tables_in_information_schema (USER_PRIVILEGES)
4
#######################################################################
5
# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
6
#######################################################################
7
DROP VIEW IF EXISTS test.v1;
8
DROP PROCEDURE IF EXISTS test.p1;
9
DROP FUNCTION IF EXISTS test.f1;
10
CREATE VIEW test.v1 AS SELECT * FROM information_schema.USER_PRIVILEGES;
11
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.USER_PRIVILEGES;
12
CREATE FUNCTION test.f1() returns BIGINT
14
DECLARE counter BIGINT DEFAULT NULL;
15
SELECT COUNT(*) INTO counter FROM information_schema.USER_PRIVILEGES;
18
# Attention: The printing of the next result sets is disabled.
19
SELECT * FROM information_schema.USER_PRIVILEGES;
20
SELECT * FROM test.v1;
24
DROP PROCEDURE test.p1;
25
DROP FUNCTION test.f1;
26
#########################################################################
27
# Testcase 3.2.16.1: INFORMATION_SCHEMA.USER_PRIVILEGES layout
28
#########################################################################
29
DESCRIBE information_schema.USER_PRIVILEGES;
30
Field Type Null Key Default Extra
31
GRANTEE varchar(81) NO
32
TABLE_CATALOG varchar(512) YES NULL
33
PRIVILEGE_TYPE varchar(64) NO
34
IS_GRANTABLE varchar(3) NO
35
SHOW CREATE TABLE information_schema.USER_PRIVILEGES;
37
USER_PRIVILEGES CREATE TEMPORARY TABLE `USER_PRIVILEGES` (
38
`GRANTEE` varchar(81) NOT NULL DEFAULT '',
39
`TABLE_CATALOG` varchar(512) DEFAULT NULL,
40
`PRIVILEGE_TYPE` varchar(64) NOT NULL DEFAULT '',
41
`IS_GRANTABLE` varchar(3) NOT NULL DEFAULT ''
42
) ENGINE=MEMORY DEFAULT CHARSET=utf8
43
SHOW COLUMNS FROM information_schema.USER_PRIVILEGES;
44
Field Type Null Key Default Extra
45
GRANTEE varchar(81) NO
46
TABLE_CATALOG varchar(512) YES NULL
47
PRIVILEGE_TYPE varchar(64) NO
48
IS_GRANTABLE varchar(3) NO
49
SELECT grantee, table_catalog, privilege_type
50
FROM information_schema.user_privileges
51
WHERE table_catalog IS NOT NULL;
52
grantee table_catalog privilege_type
53
##########################################################################
54
# Testcases 3.2.16.2+3.2.16.3+3.2.16.4: INFORMATION_SCHEMA.USER_PRIVILEGES
55
# accessible information
56
##########################################################################
57
DROP DATABASE IF EXISTS db_datadict;
58
CREATE DATABASE db_datadict;
59
DROP USER 'testuser1'@'localhost';
60
CREATE USER 'testuser1'@'localhost';
61
DROP USER 'testuser2'@'localhost';
62
CREATE USER 'testuser2'@'localhost';
63
DROP USER 'testuser3'@'localhost';
64
CREATE USER 'testuser3'@'localhost';
65
GRANT SELECT ON db_datadict.* TO 'testuser1'@'localhost';
66
GRANT SELECT ON mysql.user TO 'testuser1'@'localhost';
67
GRANT INSERT ON *.* TO 'testuser2'@'localhost';
68
GRANT UPDATE ON *.* TO 'testuser2'@'localhost';
69
SELECT * FROM information_schema.user_privileges
70
WHERE grantee LIKE '''testuser%'''
71
ORDER BY grantee, table_catalog, privilege_type;
72
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
73
'testuser1'@'localhost' NULL USAGE NO
74
'testuser2'@'localhost' NULL INSERT NO
75
'testuser2'@'localhost' NULL UPDATE NO
76
'testuser3'@'localhost' NULL USAGE NO
77
SELECT * FROM mysql.user
78
WHERE user LIKE 'testuser%' ORDER BY host, user;
79
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
80
localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
81
localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
82
localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
84
# Add GRANT OPTION db_datadict.* to testuser1;
85
GRANT UPDATE ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
86
SELECT * FROM information_schema.user_privileges
87
WHERE grantee LIKE '''testuser%'''
88
ORDER BY grantee, table_catalog, privilege_type;
89
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
90
'testuser1'@'localhost' NULL USAGE NO
91
'testuser2'@'localhost' NULL INSERT NO
92
'testuser2'@'localhost' NULL UPDATE NO
93
'testuser3'@'localhost' NULL USAGE NO
94
SELECT * FROM mysql.user
95
WHERE user LIKE 'testuser%' ORDER BY host, user;
96
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
97
localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
98
localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
99
localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
100
# Establish connection testuser1 (user=testuser1)
101
SELECT * FROM information_schema.user_privileges
102
WHERE grantee LIKE '''testuser%'''
103
ORDER BY grantee, table_catalog, privilege_type;
104
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
105
'testuser1'@'localhost' NULL USAGE NO
106
SELECT * FROM mysql.user
107
WHERE user LIKE 'testuser%' ORDER BY host, user;
108
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
109
localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
110
localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
111
localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
113
Grants for testuser1@localhost
114
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
115
GRANT SELECT, UPDATE ON `db_datadict`.* TO 'testuser1'@'localhost' WITH GRANT OPTION
116
GRANT SELECT ON `mysql`.`user` TO 'testuser1'@'localhost'
118
# Now add SELECT on *.* to testuser1;
119
# Switch to connection default
120
GRANT SELECT ON *.* TO 'testuser1'@'localhost';
122
# Here <SELECT NO> is shown correctly for testuser1;
123
SELECT * FROM information_schema.user_privileges
124
WHERE grantee LIKE '''testuser%'''
125
ORDER BY grantee, table_catalog, privilege_type;
126
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
127
'testuser1'@'localhost' NULL SELECT NO
128
'testuser2'@'localhost' NULL INSERT NO
129
'testuser2'@'localhost' NULL UPDATE NO
130
'testuser3'@'localhost' NULL USAGE NO
131
SELECT * FROM mysql.user
132
WHERE user LIKE 'testuser%' ORDER BY host, user;
133
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
134
localhost testuser1 Y N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
135
localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
136
localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
137
GRANT SELECT ON *.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
139
# Here <SELECT YES> is shown correctly for testuser1;
140
SELECT * FROM information_schema.user_privileges
141
WHERE grantee LIKE '''testuser%'''
142
ORDER BY grantee, table_catalog, privilege_type;
143
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
144
'testuser1'@'localhost' NULL SELECT YES
145
'testuser2'@'localhost' NULL INSERT NO
146
'testuser2'@'localhost' NULL UPDATE NO
147
'testuser3'@'localhost' NULL USAGE NO
148
SELECT * FROM mysql.user
149
WHERE user LIKE 'testuser%' ORDER BY host, user;
150
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
151
localhost testuser1 Y N N N N N N N N N Y N N N N N N N N N N N N N N N N N 0 0 0 0
152
localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
153
localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
154
# Switch to connection testuser1
155
SELECT * FROM information_schema.user_privileges
156
WHERE grantee LIKE '''testuser%'''
157
ORDER BY grantee, table_catalog, privilege_type;
158
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
159
'testuser1'@'localhost' NULL SELECT YES
160
SELECT * FROM mysql.user
161
WHERE user LIKE 'testuser%' ORDER BY host, user;
162
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
163
localhost testuser1 Y N N N N N N N N N Y N N N N N N N N N N N N N N N N N 0 0 0 0
164
localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
165
localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
167
Grants for testuser1@localhost
168
GRANT SELECT ON *.* TO 'testuser1'@'localhost' WITH GRANT OPTION
169
GRANT SELECT, UPDATE ON `db_datadict`.* TO 'testuser1'@'localhost' WITH GRANT OPTION
170
GRANT SELECT ON `mysql`.`user` TO 'testuser1'@'localhost'
171
# Establish connection testuser2 (user=testuser2)
172
SELECT * FROM information_schema.user_privileges
173
WHERE grantee LIKE '''testuser%'''
174
ORDER BY grantee, table_catalog, privilege_type;
175
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
176
'testuser2'@'localhost' NULL INSERT NO
177
'testuser2'@'localhost' NULL UPDATE NO
178
SELECT * FROM mysql.user
179
WHERE user LIKE 'testuser%' ORDER BY host, user;
180
ERROR 42000: SELECT command denied to user 'testuser2'@'localhost' for table 'user'
182
Grants for testuser2@localhost
183
GRANT INSERT, UPDATE ON *.* TO 'testuser2'@'localhost'
184
# Establish connection testuser3 (user=testuser3)
185
SELECT * FROM information_schema.user_privileges
186
WHERE grantee LIKE '''testuser%'''
187
ORDER BY grantee, table_catalog, privilege_type;
188
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
189
'testuser3'@'localhost' NULL USAGE NO
190
SELECT * FROM mysql.user
191
WHERE user LIKE 'testuser%' ORDER BY host, user;
192
ERROR 42000: SELECT command denied to user 'testuser3'@'localhost' for table 'user'
194
Grants for testuser3@localhost
195
GRANT USAGE ON *.* TO 'testuser3'@'localhost'
197
# Revoke privileges from testuser1;
198
# Switch to connection default
199
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser1'@'localhost';
200
SELECT * FROM information_schema.user_privileges
201
WHERE grantee LIKE '''testuser%'''
202
ORDER BY grantee, table_catalog, privilege_type;
203
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
204
'testuser1'@'localhost' NULL USAGE NO
205
'testuser2'@'localhost' NULL INSERT NO
206
'testuser2'@'localhost' NULL UPDATE NO
207
'testuser3'@'localhost' NULL USAGE NO
208
SELECT * FROM mysql.user
209
WHERE user LIKE 'testuser%' ORDER BY host, user;
210
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
211
localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
212
localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
213
localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
214
# Switch to connection testuser1
215
SELECT * FROM information_schema.user_privileges
216
WHERE grantee LIKE '''testuser%'''
217
ORDER BY grantee, table_catalog, privilege_type;
218
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
219
'testuser1'@'localhost' NULL USAGE NO
220
SELECT * FROM mysql.user
221
WHERE user LIKE 'testuser%' ORDER BY host, user;
222
ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'user'
224
Grants for testuser1@localhost
225
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
226
CREATE TABLE db_datadict.tb_55 ( c1 TEXT );
227
ERROR 42000: CREATE command denied to user 'testuser1'@'localhost' for table 'tb_55'
228
SELECT * FROM information_schema.user_privileges
229
WHERE grantee LIKE '''testuser%'''
230
ORDER BY grantee, table_catalog, privilege_type;
231
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
232
'testuser1'@'localhost' NULL USAGE NO
233
SELECT * FROM mysql.user
234
WHERE user LIKE 'testuser%' ORDER BY host, user;
235
ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'user'
237
Grants for testuser1@localhost
238
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
239
CREATE TABLE db_datadict.tb_66 ( c1 TEXT );
240
ERROR 42000: CREATE command denied to user 'testuser1'@'localhost' for table 'tb_66'
242
# Add ALL on db_datadict.* (and select on mysql.user) to testuser1;
243
# Switch to connection default
244
GRANT ALL ON db_datadict.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
245
GRANT SELECT ON mysql.user TO 'testuser1'@'localhost';
246
SELECT * FROM information_schema.user_privileges
247
WHERE grantee LIKE '''testuser%'''
248
ORDER BY grantee, table_catalog, privilege_type;
249
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
250
'testuser1'@'localhost' NULL USAGE NO
251
'testuser2'@'localhost' NULL INSERT NO
252
'testuser2'@'localhost' NULL UPDATE NO
253
'testuser3'@'localhost' NULL USAGE NO
254
SELECT * FROM mysql.user
255
WHERE user LIKE 'testuser%' ORDER BY host, user;
256
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
257
localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
258
localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
259
localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
260
# Switch to connection testuser1
261
SELECT * FROM information_schema.user_privileges
262
WHERE grantee LIKE '''testuser%'''
263
ORDER BY grantee, table_catalog, privilege_type;
264
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
265
'testuser1'@'localhost' NULL USAGE NO
266
SELECT * FROM mysql.user
267
WHERE user LIKE 'testuser%' ORDER BY host, user;
268
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
269
localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
270
localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
271
localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
273
Grants for testuser1@localhost
274
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
275
GRANT ALL PRIVILEGES ON `db_datadict`.* TO 'testuser1'@'localhost' WITH GRANT OPTION
276
GRANT SELECT ON `mysql`.`user` TO 'testuser1'@'localhost'
277
CREATE TABLE db_datadict.tb_56 ( c1 TEXT );
278
ERROR 42000: CREATE command denied to user 'testuser1'@'localhost' for table 'tb_56'
280
SELECT * FROM information_schema.user_privileges
281
WHERE grantee LIKE '''testuser%'''
282
ORDER BY grantee, table_catalog, privilege_type;
283
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
284
'testuser1'@'localhost' NULL USAGE NO
285
SELECT * FROM mysql.user
286
WHERE user LIKE 'testuser%' ORDER BY host, user;
287
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
288
localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
289
localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
290
localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
292
Grants for testuser1@localhost
293
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
294
GRANT ALL PRIVILEGES ON `db_datadict`.* TO 'testuser1'@'localhost' WITH GRANT OPTION
295
GRANT SELECT ON `mysql`.`user` TO 'testuser1'@'localhost'
296
CREATE TABLE tb_57 ( c1 TEXT )
297
ENGINE = <other_engine_type>;
299
# Revoke privileges from testuser1;
300
# Switch to connection default
301
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser1'@'localhost';
302
SELECT * FROM information_schema.user_privileges
303
WHERE grantee LIKE '''testuser%'''
304
ORDER BY grantee, table_catalog, privilege_type;
305
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
306
'testuser1'@'localhost' NULL USAGE NO
307
'testuser2'@'localhost' NULL INSERT NO
308
'testuser2'@'localhost' NULL UPDATE NO
309
'testuser3'@'localhost' NULL USAGE NO
310
SELECT * FROM mysql.user
311
WHERE user LIKE 'testuser%' ORDER BY host, user;
312
Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections
313
localhost testuser1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
314
localhost testuser2 N Y Y N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
315
localhost testuser3 N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0
316
# Switch to connection testuser1
317
SELECT * FROM information_schema.user_privileges
318
WHERE grantee LIKE '''testuser%'''
319
ORDER BY grantee, table_catalog, privilege_type;
320
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
321
'testuser1'@'localhost' NULL USAGE NO
322
SELECT * FROM mysql.user
323
WHERE user LIKE 'testuser%' ORDER BY host, user;
324
ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 'user'
326
Grants for testuser1@localhost
327
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
328
CREATE TABLE db_datadict.tb_58 ( c1 TEXT )
329
ENGINE = <other_engine_type>;
331
ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'db_datadict'
332
CREATE TABLE db_datadict.tb_59 ( c1 TEXT )
333
ENGINE = <other_engine_type>;
334
# Switch to connection default and close connections testuser1,testuser2,testuser3
335
DROP USER 'testuser1'@'localhost';
336
DROP USER 'testuser2'@'localhost';
337
DROP USER 'testuser3'@'localhost';
338
DROP DATABASE IF EXISTS db_datadict;
339
########################################################################################
340
# Testcases 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.USER_PRIVILEGES modifications
341
########################################################################################
342
SELECT * FROM information_schema.user_privileges
343
WHERE grantee = '''testuser1''@''localhost''';
344
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
345
SHOW GRANTS FOR 'testuser1'@'localhost';
346
ERROR 42000: There is no such grant defined for user 'testuser1' on host 'localhost'
347
DROP USER 'testuser1'@'localhost';
348
CREATE USER 'testuser1'@'localhost';
349
SELECT * FROM information_schema.user_privileges
350
WHERE grantee = '''testuser1''@''localhost''';
351
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
352
'testuser1'@'localhost' NULL USAGE NO
353
SHOW GRANTS FOR 'testuser1'@'localhost';
354
Grants for testuser1@localhost
355
GRANT USAGE ON *.* TO 'testuser1'@'localhost'
356
GRANT SELECT, FILE ON *.* TO 'testuser1'@'localhost';
357
SELECT * FROM information_schema.user_privileges
358
WHERE grantee = '''testuser1''@''localhost''';
359
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
360
'testuser1'@'localhost' NULL SELECT NO
361
'testuser1'@'localhost' NULL FILE NO
362
SHOW GRANTS FOR 'testuser1'@'localhost';
363
Grants for testuser1@localhost
364
GRANT SELECT, FILE ON *.* TO 'testuser1'@'localhost'
365
DROP USER 'testuser1'@'localhost';
366
SELECT * FROM information_schema.user_privileges
367
WHERE grantee = '''testuser1''@''localhost''';
368
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
369
SHOW GRANTS FOR 'testuser1'@'localhost';
370
ERROR 42000: There is no such grant defined for user 'testuser1' on host 'localhost'
371
########################################################################
372
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
373
# DDL on INFORMATION_SCHEMA tables are not supported
374
########################################################################
375
DROP USER 'testuser1'@'localhost';
376
CREATE USER 'testuser1'@'localhost';
377
INSERT INTO information_schema.user_privileges
378
SELECT * FROM information_schema.user_privileges;
379
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
380
UPDATE information_schema.user_privileges
381
SET PRIVILEGE_TYPE = 'gaming';
382
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
383
DELETE FROM information_schema.user_privileges
384
WHERE grantee = '''testuser1''@''localhost''';
385
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
386
TRUNCATE information_schema.user_privileges;
387
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
388
CREATE INDEX i1 ON information_schema.user_privileges(grantee);
389
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
390
ALTER TABLE information_schema.user_privileges ADD f1 INT;
391
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
392
DROP TABLE information_schema.user_privileges;
393
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
394
ALTER TABLE information_schema.user_privileges
395
RENAME db_datadict.user_privileges;
396
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
397
ALTER TABLE information_schema.user_privileges
398
RENAME information_schema.xuser_privileges;
399
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
400
DROP USER 'testuser1'@'localhost';
added
removed
mysql-test/suite/funcs_1/r/is_user_privileges.result
