grant2.result 17.8 KB
Newer Older
1
SET NAMES binary;
2
drop database if exists mysqltest;
serg@serg.mylan's avatar
serg@serg.mylan committed
3
drop database if exists mysqltest_1;
4 5
delete from mysql.user where user like 'mysqltest\_%';
delete from mysql.db where user like 'mysqltest\_%';
6 7
delete from mysql.tables_priv where user like 'mysqltest\_%';
delete from mysql.columns_priv where user like 'mysqltest\_%';
8
flush privileges;
9
grant all privileges on `my\_1`.* to mysqltest_1@localhost with grant option;
10
grant create user on *.* to mysqltest_1@localhost;
11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
create user mysqltest_2@localhost;
grant select on `my\_1`.* to mysqltest_2@localhost;
grant select on `my\_1`.* to mysqltest_2@localhost identified by 'pass';
ERROR 42000: You must have privileges to update tables in the mysql database to be able to change passwords for others
grant update on mysql.* to mysqltest_1@localhost;
grant select on `my\_1`.* to mysqltest_2@localhost identified by 'pass';
grant select on `my\_1`.* to mysqltest_3@localhost;
grant insert on mysql.* to mysqltest_1@localhost;
grant select on `my\_1`.* to mysqltest_3@localhost;
grant select on `my\_1`.* to mysqltest_4@localhost identified by 'pass';
delete from mysql.user where user like 'mysqltest\_%';
delete from mysql.db where user like 'mysqltest\_%';
delete from mysql.tables_priv where user like 'mysqltest\_%';
delete from mysql.columns_priv where user like 'mysqltest\_%';
flush privileges;
26
grant all privileges on `my\_%`.* to mysqltest_1@localhost with grant option;
27
grant create user on *.* to mysqltest_1@localhost;
28 29 30
select current_user();
current_user()
mysqltest_1@localhost
31 32
grant all privileges on `my\_1`.* to mysqltest_2@localhost with grant option;
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 35 36 37 38
set @@sql_mode='NO_AUTO_CREATE_USER';
select @@sql_mode;
@@sql_mode
NO_AUTO_CREATE_USER
grant select on `my\_1`.* to mysqltest_4@localhost with grant option;
serg@serg.mylan's avatar
serg@serg.mylan committed
39
ERROR 42000: Can't find any matching row in the user table
40 41
grant select on `my\_1`.* to mysqltest_4@localhost identified by 'mypass'
with grant option;
42 43
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
44
GRANT CREATE USER ON *.* TO 'mysqltest_1'@'localhost'
45 46
GRANT ALL PRIVILEGES ON `my\_%`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
show grants for mysqltest_2@localhost;
serg@serg.mylan's avatar
serg@serg.mylan committed
47 48 49
Grants for mysqltest_2@localhost
GRANT USAGE ON *.* TO 'mysqltest_2'@'localhost'
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 53 54
delete from mysql.user where user like 'mysqltest\_%';
delete from mysql.db where user like 'mysqltest\_%';
flush privileges;
serg@serg.mylan's avatar
serg@serg.mylan committed
55 56 57 58 59 60 61
create database mysqltest_1;
grant all privileges on `mysqltest\_1`.* to mysqltest_1@localhost with grant option;
select current_user();
current_user()
mysqltest_1@localhost
show databases;
Database
serg@serg.mylan's avatar
serg@serg.mylan committed
62
information_schema
serg@serg.mylan's avatar
serg@serg.mylan committed
63 64 65 66 67 68 69 70 71 72 73 74
mysqltest_1
test
grant all privileges on `mysqltest_1`.* to mysqltest_1@localhost with grant option;
ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest_1'
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT ALL PRIVILEGES ON `mysqltest\_1`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
delete from mysql.user where user like 'mysqltest\_%';
delete from mysql.db where user like 'mysqltest\_%';
drop database mysqltest_1;
flush privileges;
75 76 77 78 79 80 81 82 83 84 85
create database mysqltest;
grant INSERT, SELECT on mysqltest.* to mysqltest_1@localhost;
flush privileges;
use mysqltest;
create table t1 (id int primary key, data varchar(255));
show grants for current_user();
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT SELECT, INSERT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
insert into t1 values (1, 'I can''t change it!');
update t1 set data='I can change it!' where id = 1;
serg@sergbook.mysql.com's avatar
serg@sergbook.mysql.com committed
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!';
serg@sergbook.mysql.com's avatar
serg@sergbook.mysql.com committed
88
ERROR 42000: UPDATE command denied to user 'mysqltest_1'@'localhost' for table 't1'
89 90 91 92 93 94 95
select * from t1;
id	data
1	I can't change it!
drop table t1;
delete from mysql.user where user like 'mysqltest\_%';
delete from mysql.db where user like 'mysqltest\_%';
flush privileges;
serg@sergbook.mysql.com's avatar
serg@sergbook.mysql.com committed
96 97 98
create table t1 (a int, b int);
grant select (a) on t1 to mysqltest_1@localhost with grant option;
grant select (a,b) on t1 to mysqltest_2@localhost;
serg@serg.mylan's avatar
serg@serg.mylan committed
99
ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for column 'b' in table 't1'
serg@sergbook.mysql.com's avatar
serg@sergbook.mysql.com committed
100
grant select on t1 to mysqltest_3@localhost;
serg@serg.mylan's avatar
serg@serg.mylan committed
101
ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 't1'
serg@sergbook.mysql.com's avatar
serg@sergbook.mysql.com committed
102 103 104 105 106 107 108 109
drop table t1;
delete from mysql.user where user like 'mysqltest\_%';
delete from mysql.db where user like 'mysqltest\_%';
delete from mysql.tables_priv where user like 'mysqltest\_%';
delete from mysql.columns_priv where user like 'mysqltest\_%';
flush privileges;
drop database mysqltest;
use test;
110 111 112 113 114 115 116
create user mysqltest_1@host1;
create user mysqltest_2@host2;
create user mysqltest_3@host3;
create user mysqltest_4@host4;
create user mysqltest_5@host5;
create user mysqltest_6@host6;
create user mysqltest_7@host7;
117 118 119 120
flush privileges;
drop user mysqltest_3@host3;
drop user mysqltest_1@host1, mysqltest_2@host2, mysqltest_4@host4,
mysqltest_5@host5, mysqltest_6@host6, mysqltest_7@host7;
121 122 123
create database mysqltest_1;
grant select, insert, update on `mysqltest\_1`.* to mysqltest_1@localhost;
set sql_log_off = 1;
cmiller@zippy.cornsilk.net's avatar
cmiller@zippy.cornsilk.net committed
124
ERROR 42000: Access denied; you need the SUPER privilege for this operation
125
set sql_log_bin = 0;
cmiller@zippy.cornsilk.net's avatar
cmiller@zippy.cornsilk.net committed
126
ERROR 42000: Access denied; you need the SUPER privilege for this operation
127 128 129 130
delete from mysql.user where user like 'mysqltest\_1';
delete from mysql.db where user like 'mysqltest\_1';
drop database mysqltest_1;
flush privileges;
131 132 133 134 135 136
set sql_mode='maxdb';
drop table if exists t1, t2;
create table t1(c1 int);
create table t2(c1 int, c2 int);
create user 'mysqltest_1';
create user 'mysqltest_1';
137
ERROR HY000: Operation CREATE USER failed for 'mysqltest_1'@'%'
138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206
create user 'mysqltest_2' identified by 'Mysqltest-2';
create user 'mysqltest_3' identified by password 'fffffffffffffffffffffffffffffffffffffffff';
grant select on *.* to 'mysqltest_2';
grant insert on test.* to 'mysqltest_2';
grant update on test.t1 to 'mysqltest_2';
grant update (c2) on test.t2 to 'mysqltest_2';
select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password;
host	user	password
%	mysqltest_1	
%	mysqltest_2	*BD447CBA355AF58578D3AE33BA2E2CD388BA08D1
%	mysqltest_3	fffffffffffffffffffffffffffffffffffffffff
select host,db,user from mysql.db where user like 'mysqltest_%' order by host,db,user;
host	db	user
%	test	mysqltest_2
select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' order by host,db,user,table_name;
host	db	user	table_name
%	test	mysqltest_2	t1
%	test	mysqltest_2	t2
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;
host	db	user	table_name	column_name
%	test	mysqltest_2	t2	c2
show grants for 'mysqltest_1';
Grants for mysqltest_1@%
GRANT USAGE ON *.* TO 'mysqltest_1'@'%'
show grants for 'mysqltest_2';
Grants for mysqltest_2@%
GRANT SELECT ON *.* TO 'mysqltest_2'@'%' IDENTIFIED BY PASSWORD '*BD447CBA355AF58578D3AE33BA2E2CD388BA08D1'
GRANT INSERT ON "test".* TO 'mysqltest_2'@'%'
GRANT UPDATE (c2) ON "test"."t2" TO 'mysqltest_2'@'%'
GRANT UPDATE ON "test"."t1" TO 'mysqltest_2'@'%'
drop user 'mysqltest_1';
select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password;
host	user	password
%	mysqltest_2	*BD447CBA355AF58578D3AE33BA2E2CD388BA08D1
%	mysqltest_3	fffffffffffffffffffffffffffffffffffffffff
select host,db,user from mysql.db where user like 'mysqltest_%' order by host,db,user;
host	db	user
%	test	mysqltest_2
select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' order by host,db,user,table_name;
host	db	user	table_name
%	test	mysqltest_2	t1
%	test	mysqltest_2	t2
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;
host	db	user	table_name	column_name
%	test	mysqltest_2	t2	c2
show grants for 'mysqltest_1';
ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%'
rename user 'mysqltest_2' to 'mysqltest_1';
select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password;
host	user	password
%	mysqltest_1	*BD447CBA355AF58578D3AE33BA2E2CD388BA08D1
%	mysqltest_3	fffffffffffffffffffffffffffffffffffffffff
select host,db,user from mysql.db where user like 'mysqltest_%' order by host,db,user;
host	db	user
%	test	mysqltest_1
select host,db,user,table_name from mysql.tables_priv where user like 'mysqltest_%' order by host,db,user,table_name;
host	db	user	table_name
%	test	mysqltest_1	t1
%	test	mysqltest_1	t2
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;
host	db	user	table_name	column_name
%	test	mysqltest_1	t2	c2
show grants for 'mysqltest_1';
Grants for mysqltest_1@%
GRANT SELECT ON *.* TO 'mysqltest_1'@'%' IDENTIFIED BY PASSWORD '*BD447CBA355AF58578D3AE33BA2E2CD388BA08D1'
GRANT INSERT ON "test".* TO 'mysqltest_1'@'%'
GRANT UPDATE (c2) ON "test"."t2" TO 'mysqltest_1'@'%'
GRANT UPDATE ON "test"."t1" TO 'mysqltest_1'@'%'
drop user 'mysqltest_1', 'mysqltest_3';
207
drop user 'mysqltest_1';
208
ERROR HY000: Operation DROP USER failed for 'mysqltest_1'@'%'
209 210 211 212 213 214
drop table t1, t2;
insert into mysql.db set user='mysqltest_1', db='%', host='%';
flush privileges;
show grants for 'mysqltest_1';
ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%'
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 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237
drop user 'mysqltest_1';
select host,db,user from mysql.db where user = 'mysqltest_1' order by host,db,user;
host	db	user
insert into mysql.tables_priv set host='%', db='test', user='mysqltest_1', table_name='t1';
flush privileges;
show grants for 'mysqltest_1';
ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%'
drop user 'mysqltest_1';
select host,db,user,table_name from mysql.tables_priv where user = 'mysqltest_1' order by host,db,user,table_name;
host	db	user	table_name
insert into mysql.columns_priv set host='%', db='test', user='mysqltest_1', table_name='t1', column_name='c1';
flush privileges;
show grants for 'mysqltest_1';
ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host '%'
drop user 'mysqltest_1';
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;
host	db	user	table_name	column_name
create user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3';
drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3';
create user 'mysqltest_1', 'mysqltest_2' identified by 'Mysqltest-2', 'mysqltest_3' identified by password 'fffffffffffffffffffffffffffffffffffffffff';
rename user 'mysqltest_1' to 'mysqltest_1a', 'mysqltest_2' TO 'mysqltest_2a', 'mysqltest_3' TO 'mysqltest_3a';
drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3';
238
ERROR HY000: Operation DROP USER failed for 'mysqltest_1'@'%','mysqltest_2'@'%','mysqltest_3'@'%'
239 240 241
drop user 'mysqltest_1a', 'mysqltest_2a', 'mysqltest_3a';
create user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3';
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 246
drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3';
drop user 'mysqltest_1b', 'mysqltest_2b', 'mysqltest_3b';
247
ERROR HY000: Operation DROP USER failed for 'mysqltest_2b'@'%'
248 249 250
create user 'mysqltest_2' identified by 'Mysqltest-2';
drop user 'mysqltest_2' identified by 'Mysqltest-2';
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 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269
create user '%@b'@'b';
show grants for '%@b'@'b';
Grants for %@b@b
GRANT USAGE ON *.* TO '%@b'@'b'
grant select on mysql.* to '%@b'@'b';
show grants for '%@b'@'b';
Grants for %@b@b
GRANT USAGE ON *.* TO '%@b'@'b'
GRANT SELECT ON "mysql".* TO '%@b'@'b'
rename user '%@b'@'b' to '%@a'@'a';
show grants for '%@b'@'b';
ERROR 42000: There is no such grant defined for user '%@b' on host 'b'
show grants for '%@a'@'a';
Grants for %@a@a
GRANT USAGE ON *.* TO '%@a'@'a'
GRANT SELECT ON "mysql".* TO '%@a'@'a'
drop user '%@a'@'a';
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;
serg@sergbook.mysql.com's avatar
serg@sergbook.mysql.com committed
272
ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table 'user'
273 274 275 276 277
create user mysqltest_A@'%';
rename user mysqltest_A@'%' to mysqltest_B@'%';
drop user mysqltest_B@'%';
drop user mysqltest_2@localhost;
create user mysqltest_3@localhost;
278 279 280 281 282
grant INSERT,DELETE,UPDATE on mysql.* to mysqltest_3@localhost;
show grants;
Grants for mysqltest_3@localhost
GRANT USAGE ON *.* TO 'mysqltest_3'@'localhost'
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';
286 287 288 289
Warnings:
Warning	1364	Field 'ssl_cipher' doesn't have a default value
Warning	1364	Field 'x509_issuer' doesn't have a default value
Warning	1364	Field 'x509_subject' doesn't have a default value
290 291
create user mysqltest_A@'%';
rename user mysqltest_B@'%' to mysqltest_C@'%';
292
drop user mysqltest_C@'%';
293
drop user mysqltest_A@'%';
294
drop user mysqltest_3@localhost;
295
set @@sql_mode='';
296 297 298 299 300 301 302 303 304 305 306 307 308 309
create database mysqltest_1;
create table mysqltest_1.t1 (i int);
insert into mysqltest_1.t1 values (1),(2),(3);
GRANT ALL ON mysqltest_1.t1 TO mysqltest_1@'127.0.0.0/255.0.0.0';
show grants for current_user();
Grants for mysqltest_1@127.0.0.0/255.0.0.0
GRANT USAGE ON *.* TO 'mysqltest_1'@'127.0.0.0/255.0.0.0'
GRANT ALL PRIVILEGES ON `mysqltest_1`.`t1` TO 'mysqltest_1'@'127.0.0.0/255.0.0.0'
select * from t1;
i
1
2
3
REVOKE ALL ON mysqltest_1.t1 FROM mysqltest_1@'127.0.0.0/255.0.0.0';
310 311
delete from mysql.user where user like 'mysqltest\_1';
flush privileges;
312
drop table mysqltest_1.t1;
313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334
grant all on mysqltest_1.* to mysqltest_1@'127.0.0.1';
select current_user();
current_user()
mysqltest_1@127.0.0.1
set password = password('changed');
select host, length(password) from mysql.user where user like 'mysqltest\_1';
host	length(password)
127.0.0.1	41
revoke all on mysqltest_1.* from mysqltest_1@'127.0.0.1';
delete from mysql.user where user like 'mysqltest\_1';
flush privileges;
grant all on mysqltest_1.* to mysqltest_1@'127.0.0.0/255.0.0.0';
select current_user();
current_user()
mysqltest_1@127.0.0.0/255.0.0.0
set password = password('changed');
select host, length(password) from mysql.user where user like 'mysqltest\_1';
host	length(password)
127.0.0.0/255.0.0.0	41
revoke all on mysqltest_1.* from mysqltest_1@'127.0.0.0/255.0.0.0';
delete from mysql.user where user like 'mysqltest\_1';
flush privileges;
335
drop database mysqltest_1;
336 337
set password = password("changed");
ERROR 42000: Access denied for user ''@'localhost' to database 'mysql'
338
lock table mysql.user write;
339 340
flush privileges;
grant all on *.* to 'mysqltest_1'@'localhost';
341 342
unlock tables;
lock table mysql.user write;
343 344
set password for 'mysqltest_1'@'localhost' = password('');
revoke all on *.* from 'mysqltest_1'@'localhost';
345 346
unlock tables;
drop user 'mysqltest_1'@'localhost';
347 348 349 350 351 352 353 354 355 356 357 358 359 360 361
create database TESTDB;
create table t2(a int);
create temporary table t1 as select * from mysql.user;
delete from mysql.user where host='localhost';
INSERT INTO mysql.user VALUES
('%','mysqltest_1',password('password'),'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);
INSERT INTO mysql.db VALUES
('%','TESTDB','mysqltest_1','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','N','Y','Y','Y','
Y','N');
Warnings:
Warning	1265	Data truncated for column 'Alter_routine_priv' at row 1
FLUSH PRIVILEGES;
create database TEStdb;
362
Got one of the listed errors
363 364 365 366 367 368
delete from mysql.user;
delete from mysql.db where host='%' and user='mysqltest_1' and db='TESTDB';
insert into mysql.user select * from t1;
drop table t1, t2;
drop database TESTDB;
flush privileges;
369 370 371 372 373 374 375 376 377 378 379 380 381
grant all privileges on test.* to `a@`@localhost;
grant execute on * to `a@`@localhost;
create table t2 (s1 int);
insert into t2 values (1);
drop function if exists f2;
create function f2 () returns int begin declare v int; select s1 from t2
into v; return v; end//
select f2();
f2()
1
drop function f2;
drop table t2;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM `a@`@localhost;
382
drop user `a@`@localhost;
383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406
drop database if exists mysqltest_1;
drop database if exists mysqltest_2;
drop user mysqltest_u1@localhost;
create database mysqltest_1;
create database mysqltest_2;
grant all on mysqltest_1.* to mysqltest_u1@localhost;
use mysqltest_2;
create table t1 (i int);
show create table mysqltest_2.t1;
ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1'
create table t1 like mysqltest_2.t1;
ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1'
grant select on mysqltest_2.t1 to mysqltest_u1@localhost;
show create table mysqltest_2.t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
create table t1 like mysqltest_2.t1;
use test;
drop database mysqltest_1;
drop database mysqltest_2;
drop user mysqltest_u1@localhost;
End of 5.0 tests