1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
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
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
|
SET NAMES binary;
drop database if exists mysqltest;
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;
grant all privileges on `my\_%`.* to mysqltest_1@localhost with grant option;
select current_user();
current_user()
mysqltest_1@localhost
select current_user;
current_user
mysqltest_1@localhost
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;
ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'my_%'
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;
ERROR 42000: 'mysqltest_1'@'localhost' is not allowed to create new users
grant select on `my\_1`.* to mysqltest_4@localhost identified by 'mypass'
with grant option;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT ALL PRIVILEGES ON `my\_%`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
show grants for mysqltest_2@localhost;
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
show grants for mysqltest_3@localhost;
ERROR 42000: There is no such grant defined for user 'mysqltest_3' on host 'localhost'
delete from mysql.user where user like 'mysqltest\_%';
delete from mysql.db where user like 'mysqltest\_%';
flush privileges;
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'
use mysqltest;
insert into t1 values (1, 'I can''t change it!');
update t1 set data='I can change it!' where id = 1;
ERROR 42000: update command denied to user 'mysqltest_1'@'localhost' for table 't1'
insert into t1 values (1, 'XXX') on duplicate key update data= 'I can change it!';
ERROR 42000: update command denied to user 'mysqltest_1'@'localhost' for table 't1'
select * from t1;
id data
1 I can't change it!
drop table t1;
drop database mysqltest;
use test;
delete from mysql.user where user like 'mysqltest\_%';
delete from mysql.db where user like 'mysqltest\_%';
flush privileges;
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';
ERROR HY000: Operation CREATE USER failed for 'mysqltest_1'@'%'
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';
grant all on test.t1 to 'mysqltest_1';
ERROR 42000: 'root'@'localhost' is not allowed to create new users
drop user 'mysqltest_1';
ERROR HY000: Operation DROP USER failed for 'mysqltest_1'@'%'
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';
ERROR HY000: Can't revoke all privileges, grant for one or more of the requested users
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';
ERROR HY000: Operation DROP USER failed for 'mysqltest_1'@'%','mysqltest_2'@'%','mysqltest_3'@'%'
drop user 'mysqltest_1a', 'mysqltest_2a', 'mysqltest_3a';
create user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3';
create user 'mysqltest_1a', 'mysqltest_2', 'mysqltest_3a';
ERROR HY000: Operation CREATE USER failed for 'mysqltest_2'@'%'
rename user 'mysqltest_1a' to 'mysqltest_1b', 'mysqltest_2a' TO 'mysqltest_2b', 'mysqltest_3a' TO 'mysqltest_3b';
ERROR HY000: Operation RENAME USER failed for 'mysqltest_2a'@'%'
drop user 'mysqltest_1', 'mysqltest_2', 'mysqltest_3';
drop user 'mysqltest_1b', 'mysqltest_2b', 'mysqltest_3b';
ERROR HY000: Operation DROP USER failed for 'mysqltest_2b'@'%'
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
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;
grant usage on *.* to mysqltest_2@localhost with grant option;
select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password;
ERROR 42000: select command denied to user 'mysqltest_2'@'localhost' for table 'user'
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;
grant all privileges on mysql.* to mysqltest_3@localhost;
select host,user,password from mysql.user where user like 'mysqltest_%' order by host,user,password;
host user password
% mysqltest_2 *BD447CBA355AF58578D3AE33BA2E2CD388BA08D1
localhost mysqltest_3
insert into mysql.user set host='%', user='mysqltest_B';
create user mysqltest_A@'%';
ERROR 42000: Access denied for user 'mysqltest_3'@'localhost' to database 'mysql'
rename user mysqltest_B@'%' to mysqltest_C@'%';
ERROR 42000: Access denied for user 'mysqltest_3'@'localhost' to database 'mysql'
drop user mysqltest_B@'%';
ERROR 42000: Access denied for user 'mysqltest_3'@'localhost' to database 'mysql'
drop user mysqltest_B@'%';
drop user mysqltest_3@localhost;
|
