2
drop database if exists mysqltest;
3
drop database if exists mysqltest_1;
4
delete from mysql.user where user like 'mysqltest\_%';
5
delete from mysql.db where user like 'mysqltest\_%';
6
delete from mysql.tables_priv where user like 'mysqltest\_%';
7
delete from mysql.columns_priv where user like 'mysqltest\_%';
9
grant all privileges on `my\_1`.* to mysqltest_1@localhost with grant option;
10
grant create user on *.* to mysqltest_1@localhost;
11
create user mysqltest_2@localhost;
12
grant select on `my\_1`.* to mysqltest_2@localhost;
13
grant select on `my\_1`.* to mysqltest_2@localhost identified by 'pass';
14
ERROR 42000: You must have privileges to update tables in the mysql database to be able to change passwords for others
15
grant update on mysql.* to mysqltest_1@localhost;
16
grant select on `my\_1`.* to mysqltest_2@localhost identified by 'pass';
17
grant select on `my\_1`.* to mysqltest_3@localhost;
18
grant insert on mysql.* to mysqltest_1@localhost;
19
grant select on `my\_1`.* to mysqltest_3@localhost;
20
grant select on `my\_1`.* to mysqltest_4@localhost identified by 'pass';
21
delete from mysql.user where user like 'mysqltest\_%';
22
delete from mysql.db where user like 'mysqltest\_%';
23
delete from mysql.tables_priv where user like 'mysqltest\_%';
24
delete from mysql.columns_priv where user like 'mysqltest\_%';
26
grant all privileges on `my\_%`.* to mysqltest_1@localhost with grant option;
27
grant create user on *.* to mysqltest_1@localhost;
28
select current_user();
31
grant all privileges on `my\_1`.* to mysqltest_2@localhost with grant option;
32
grant all privileges on `my_%`.* to mysqltest_3@localhost with grant option;
33
ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'my_%'
34
set @@sql_mode='NO_AUTO_CREATE_USER';
38
grant select on `my\_1`.* to mysqltest_4@localhost with grant option;
39
ERROR 42000: Can't find any matching row in the user table
40
grant select on `my\_1`.* to mysqltest_4@localhost identified by 'mypass'
42
show grants for mysqltest_1@localhost;
43
Grants for mysqltest_1@localhost
44
GRANT CREATE USER ON *.* TO 'mysqltest_1'@'localhost'
45
GRANT ALL PRIVILEGES ON `my\_%`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
46
show grants for mysqltest_2@localhost;
47
Grants for mysqltest_2@localhost
48
GRANT USAGE ON *.* TO 'mysqltest_2'@'localhost'
49
GRANT ALL PRIVILEGES ON `my\_1`.* TO 'mysqltest_2'@'localhost' WITH GRANT OPTION
50
show grants for mysqltest_3@localhost;
51
ERROR 42000: There is no such grant defined for user 'mysqltest_3' on host 'localhost'
52
delete from mysql.user where user like 'mysqltest\_%';
53
delete from mysql.db where user like 'mysqltest\_%';
55
create database mysqltest_1;
56
grant all privileges on `mysqltest\_1`.* to mysqltest_1@localhost with grant option;
57
select current_user();
65
grant all privileges on `mysqltest_1`.* to mysqltest_1@localhost with grant option;
66
ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest_1'
67
show grants for mysqltest_1@localhost;
68
Grants for mysqltest_1@localhost
69
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
70
GRANT ALL PRIVILEGES ON `mysqltest\_1`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
71
delete from mysql.user where user like 'mysqltest\_%';
72
delete from mysql.db where user like 'mysqltest\_%';
73
drop database mysqltest_1;
75
create database mysqltest;
76
grant INSERT, SELECT on mysqltest.* to mysqltest_1@localhost;
79
create table t1 (id int primary key, data varchar(255));
80
show grants for current_user();
81
Grants for mysqltest_1@localhost
82
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
83
GRANT SELECT, INSERT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
84
insert into t1 values (1, 'I can''t change it!');
85
update t1 set data='I can change it!' where id = 1;
86
ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 't1'
87
insert into t1 values (1, 'XXX') on duplicate key update data= 'I can change it!';
88
ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 't1'
93
delete from mysql.user where user like 'mysqltest\_%';
94
delete from mysql.db where user like 'mysqltest\_%';
96
create table t1 (a int, b int);
97
grant select (a) on t1 to mysqltest_1@localhost with grant option;
98
grant select (a,b) on t1 to mysqltest_2@localhost;
99
ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'b' in table 't1'
100
grant select on t1 to mysqltest_3@localhost;
101
ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 't1'
103
delete from mysql.user where user like 'mysqltest\_%';
104
delete from mysql.db where user like 'mysqltest\_%';
105
delete from mysql.tables_priv where user like 'mysqltest\_%';
106
delete from mysql.columns_priv where user like 'mysqltest\_%';
108
drop database mysqltest;
110
create user mysqltest_1@host1;
111
create user mysqltest_2@host2;
112
create user mysqltest_3@host3;
113
create user mysqltest_4@host4;
114
create user mysqltest_5@host5;
115
create user mysqltest_6@host6;
116
create user mysqltest_7@host7;
118
drop user mysqltest_3@host3;
119
drop user mysqltest_1@host1, mysqltest_2@host2, mysqltest_4@host4,
120
mysqltest_5@host5, mysqltest_6@host6, mysqltest_7@host7;
121
create database mysqltest_1;
122
grant select, insert, update on `mysqltest\_1`.* to mysqltest_1@localhost;
124
ERROR 42000: Access denied; you need the SUPER privilege for this operation
126
ERROR 42000: Access denied; you need the SUPER privilege for this operation
127
delete from mysql.user where user like 'mysqltest\_1';
128
delete from mysql.db where user like 'mysqltest\_1';
129
drop database mysqltest_1;
131
set sql_mode='maxdb';
132
drop table if exists t1, t2;
133
create table t1(c1 int);
134
create table t2(c1 int, c2 int);
135
create user 'mysqltest_1';
136
create user 'mysqltest_1';
137
ERROR HY000: Operation CREATE USER failed for 'mysqltest_1'@'%'
138
create user 'mysqltest_2' identified by 'Mysqltest-2';
139
create user 'mysqltest_3' identified by password 'fffffffffffffffffffffffffffffffffffffffff';
140
grant select on *.* to 'mysqltest_2';
141
grant insert on test.* to 'mysqltest_2';
142
grant update on test.t1 to 'mysqltest_2';
143
grant update (c2) on test.t2 to 'mysqltest_2';
144
select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password;
147
% mysqltest_2 *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1
148
% mysqltest_3 fffffffffffffffffffffffffffffffffffffffff
149
select host,db,user from mysql.db where user like 'mysqltest_%' order by host,db,user;
152
select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' order by host,db,user,table_name;
153
host db user table_name
154
% test mysqltest_2 t1
155
% test mysqltest_2 t2
156
select host,db,user,table_name,column_name from mysql.columns_priv where user like 'mysqltest_%' order by host,db,user,table_name,column_name;
157
host db user table_name column_name
158
% test mysqltest_2 t2 c2
159
show grants for 'mysqltest_1';
160
Grants for mysqltest_1@%
161
GRANT USAGE ON *.* TO 'mysqltest_1'@'%'
162
show grants for 'mysqltest_2';
163
Grants for mysqltest_2@%
164
GRANT SELECT ON *.* TO 'mysqltest_2'@'%' IDENTIFIED BY PASSWORD '*BD447CBA355AF58578D3AE33BA2E2CD388BA08D1'
165
GRANT INSERT ON "test".* TO 'mysqltest_2'@'%'
166
GRANT UPDATE (c2) ON "test"."t2" TO 'mysqltest_2'@'%'
167
GRANT UPDATE ON "test"."t1" TO 'mysqltest_2'@'%'
168
drop user 'mysqltest_1';
169
select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password;
171
% mysqltest_2 *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1
172
% mysqltest_3 fffffffffffffffffffffffffffffffffffffffff
173
select host,db,user from mysql.db where user like 'mysqltest_%' order by host,db,user;
176
select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' order by host,db,user,table_name;
177
host db user table_name
178
% test mysqltest_2 t1
179
% test mysqltest_2 t2
180
select host,db,user,table_name,column_name from mysql.columns_priv where user like 'mysqltest_%' order by host,db,user,table_name,column_name;
181
host db user table_name column_name
182
% test mysqltest_2 t2 c2
183
show grants for 'mysqltest_1';
184
ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%'
185
rename user 'mysqltest_2' to 'mysqltest_1';
186
select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password;
188
% mysqltest_1 *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1
189
% mysqltest_3 fffffffffffffffffffffffffffffffffffffffff
190
select host,db,user from mysql.db where user like 'mysqltest_%' order by host,db,user;
193
select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' order by host,db,user,table_name;
194
host db user table_name
195
% test mysqltest_1 t1
196
% test mysqltest_1 t2
197
select host,db,user,table_name,column_name from mysql.columns_priv where user like 'mysqltest_%' order by host,db,user,table_name,column_name;
198
host db user table_name column_name
199
% test mysqltest_1 t2 c2
200
show grants for 'mysqltest_1';
201
Grants for mysqltest_1@%
202
GRANT SELECT ON *.* TO 'mysqltest_1'@'%' IDENTIFIED BY PASSWORD '*BD447CBA355AF58578D3AE33BA2E2CD388BA08D1'
203
GRANT INSERT ON "test".* TO 'mysqltest_1'@'%'
204
GRANT UPDATE (c2) ON "test"."t2" TO 'mysqltest_1'@'%'
205
GRANT UPDATE ON "test"."t1" TO 'mysqltest_1'@'%'
206
drop user 'mysqltest_1', 'mysqltest_3';
207
drop user 'mysqltest_1';
208
ERROR HY000: Operation DROP USER failed for 'mysqltest_1'@'%'
210
insert into mysql.db set user='mysqltest_1', db='%', host='%';
212
show grants for 'mysqltest_1';
213
ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%'
214
revoke all privileges, grant option from 'mysqltest_1';
215
ERROR HY000: Can't revoke all privileges for one or more of the requested users
216
drop user 'mysqltest_1';
217
select host,db,user from mysql.db where user = 'mysqltest_1' order by host,db,user;
219
insert into mysql.tables_priv set host='%', db='test', user='mysqltest_1', table_name='t1';
221
show grants for 'mysqltest_1';
222
ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%'
223
drop user 'mysqltest_1';
224
select host,db,user,table_name from mysql.tables_priv where user = 'mysqltest_1' order by host,db,user,table_name;
225
host db user table_name
226
insert into mysql.columns_priv set host='%', db='test', user='mysqltest_1', table_name='t1', column_name='c1';
228
show grants for 'mysqltest_1';
229
ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%'
230
drop user 'mysqltest_1';
231
select host,db,user,table_name,column_name from mysql.columns_priv where user = 'mysqltest_1' order by host,db,user,table_name,column_name;
232
host db user table_name column_name
233
create user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3';
234
drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3';
235
create user 'mysqltest_1', 'mysqltest_2' identified by 'Mysqltest-2', 'mysqltest_3' identified by password 'fffffffffffffffffffffffffffffffffffffffff';
236
rename user 'mysqltest_1' to 'mysqltest_1a', 'mysqltest_2' TO 'mysqltest_2a', 'mysqltest_3' TO 'mysqltest_3a';
237
drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3';
238
ERROR HY000: Operation DROP USER failed for 'mysqltest_1'@'%','mysqltest_2'@'%','mysqltest_3'@'%'
239
drop user 'mysqltest_1a', 'mysqltest_2a', 'mysqltest_3a';
240
create user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3';
241
create user 'mysqltest_1a', 'mysqltest_2', 'mysqltest_3a';
242
ERROR HY000: Operation CREATE USER failed for 'mysqltest_2'@'%'
243
rename user 'mysqltest_1a' to 'mysqltest_1b', 'mysqltest_2a' TO 'mysqltest_2b', 'mysqltest_3a' TO 'mysqltest_3b';
244
ERROR HY000: Operation RENAME USER failed for 'mysqltest_2a'@'%'
245
drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3';
246
drop user 'mysqltest_1b', 'mysqltest_2b', 'mysqltest_3b';
247
ERROR HY000: Operation DROP USER failed for 'mysqltest_2b'@'%'
248
create user 'mysqltest_2' identified by 'Mysqltest-2';
249
drop user 'mysqltest_2' identified by 'Mysqltest-2';
250
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'Mysqltest-2'' at line 1
251
drop user 'mysqltest_2';
252
create user '%@b'@'b';
253
show grants for '%@b'@'b';
255
GRANT USAGE ON *.* TO '%@b'@'b'
256
grant select on mysql.* to '%@b'@'b';
257
show grants for '%@b'@'b';
259
GRANT USAGE ON *.* TO '%@b'@'b'
260
GRANT SELECT ON "mysql".* TO '%@b'@'b'
261
rename user '%@b'@'b' to '%@a'@'a';
262
show grants for '%@b'@'b';
263
ERROR 42000: There is no such grant defined for user '%@b' on host 'b'
264
show grants for '%@a'@'a';
266
GRANT USAGE ON *.* TO '%@a'@'a'
267
GRANT SELECT ON "mysql".* TO '%@a'@'a'
269
create user mysqltest_2@localhost;
270
grant create user on *.* to mysqltest_2@localhost;
271
select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password;
272
ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table 'user'
273
create user mysqltest_A@'%';
274
rename user mysqltest_A@'%' to mysqltest_B@'%';
275
drop user mysqltest_B@'%';
276
drop user mysqltest_2@localhost;
277
create user mysqltest_3@localhost;
278
grant INSERT,DELETE,UPDATE on mysql.* to mysqltest_3@localhost;
280
Grants for mysqltest_3@localhost
281
GRANT USAGE ON *.* TO 'mysqltest_3'@'localhost'
282
GRANT INSERT, UPDATE, DELETE ON `mysql`.* TO 'mysqltest_3'@'localhost'
283
select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password;
284
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 'user'
285
insert into mysql.user set host='%', user='mysqltest_B';
287
Warning 1364 Field 'ssl_cipher' doesn't have a default value
288
Warning 1364 Field 'x509_issuer' doesn't have a default value
289
Warning 1364 Field 'x509_subject' doesn't have a default value
290
create user mysqltest_A@'%';
291
rename user mysqltest_B@'%' to mysqltest_C@'%';
292
drop user mysqltest_C@'%';
293
drop user mysqltest_A@'%';
294
drop user mysqltest_3@localhost;
296
create database mysqltest_1;
297
create table mysqltest_1.t1 (i int);
298
insert into mysqltest_1.t1 values (1),(2),(3);
299
GRANT ALL ON mysqltest_1.t1 TO mysqltest_1@'127.0.0.0/255.0.0.0';
300
show grants for current_user();
301
Grants for mysqltest_1@127.0.0.0/255.0.0.0
302
GRANT USAGE ON *.* TO 'mysqltest_1'@'127.0.0.0/255.0.0.0'
303
GRANT ALL PRIVILEGES ON `mysqltest_1`.`t1` TO 'mysqltest_1'@'127.0.0.0/255.0.0.0'
309
REVOKE ALL ON mysqltest_1.t1 FROM mysqltest_1@'127.0.0.0/255.0.0.0';
310
delete from mysql.user where user like 'mysqltest\_1';
312
drop table mysqltest_1.t1;
313
grant all on mysqltest_1.* to mysqltest_1@'127.0.0.1';
314
select current_user();
316
mysqltest_1@127.0.0.1
317
set password = password('changed');
318
select host, length(password) from mysql.user where user like 'mysqltest\_1';
319
host length(password)
321
revoke all on mysqltest_1.* from mysqltest_1@'127.0.0.1';
322
delete from mysql.user where user like 'mysqltest\_1';
324
grant all on mysqltest_1.* to mysqltest_1@'127.0.0.0/255.0.0.0';
325
select current_user();
327
mysqltest_1@127.0.0.0/255.0.0.0
328
set password = password('changed');
329
select host, length(password) from mysql.user where user like 'mysqltest\_1';
330
host length(password)
331
127.0.0.0/255.0.0.0 41
332
revoke all on mysqltest_1.* from mysqltest_1@'127.0.0.0/255.0.0.0';
333
delete from mysql.user where user like 'mysqltest\_1';
335
drop database mysqltest_1;
336
set password = password("changed");
337
ERROR 42000: Access denied for user ''@'localhost' to database 'mysql'
338
lock table mysql.user write;
340
grant all on *.* to 'mysqltest_1'@'localhost';
342
lock table mysql.user write;
343
set password for 'mysqltest_1'@'localhost' = password('');
344
revoke all on *.* from 'mysqltest_1'@'localhost';
346
drop user 'mysqltest_1'@'localhost';
347
create database TESTDB;
348
create table t2(a int);
349
create temporary table t1 as select * from mysql.user;
350
delete from mysql.user where host='localhost';
351
INSERT INTO mysql.user VALUES
352
('%','mysqltest_1',password('password'),'N','N','N','N','N','N',
353
'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N',
354
'','','','',0,0,0,0);
355
INSERT INTO mysql.db VALUES
356
('%','TESTDB','mysqltest_1','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','N','Y','Y','Y','
359
Warning 1265 Data truncated for column 'Alter_routine_priv' at row 1
361
create database TEStdb;
362
Got one of the listed errors
363
delete from mysql.user;
364
delete from mysql.db where host='%' and user='mysqltest_1' and db='TESTDB';
365
insert into mysql.user select * from t1;
367
drop database TESTDB;
369
grant all privileges on test.* to `a@`@localhost;
370
grant execute on * to `a@`@localhost;
371
create table t2 (s1 int);
372
insert into t2 values (1);
373
drop function if exists f2;
374
create function f2 () returns int begin declare v int; select s1 from t2
375
into v; return v; end//
381
REVOKE ALL PRIVILEGES, GRANT OPTION FROM `a@`@localhost;
382
drop user `a@`@localhost;
added
removed
mysql-test/r/grant2.result
