1
drop table if exists t1;
2
drop database if exists mysqltest;
4
delete from mysql.user where user='mysqltest_1';
5
delete from mysql.db where user='mysqltest_1';
7
grant select on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA";
8
show grants for mysqltest_1@localhost;
9
Grants for mysqltest_1@localhost
10
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA'
11
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
12
grant delete on mysqltest.* to mysqltest_1@localhost;
13
select * from mysql.user where user="mysqltest_1";
14
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
15
localhost mysqltest_1 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 SPECIFIED EDH-RSA-DES-CBC3-SHA 0 0 0 0
16
show grants for mysqltest_1@localhost;
17
Grants for mysqltest_1@localhost
18
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA'
19
GRANT SELECT, DELETE ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
20
revoke delete on mysqltest.* from mysqltest_1@localhost;
21
show grants for mysqltest_1@localhost;
22
Grants for mysqltest_1@localhost
23
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA'
24
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
25
grant select on mysqltest.* to mysqltest_1@localhost require NONE;
26
show grants for mysqltest_1@localhost;
27
Grants for mysqltest_1@localhost
28
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
29
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
30
grant USAGE on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA" AND SUBJECT "testsubject" ISSUER "MySQL AB";
31
show grants for mysqltest_1@localhost;
32
Grants for mysqltest_1@localhost
33
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE ISSUER 'MySQL AB' SUBJECT 'testsubject' CIPHER 'EDH-RSA-DES-CBC3-SHA'
34
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
35
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
36
show grants for mysqltest_1@localhost;
37
Grants for mysqltest_1@localhost
38
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE ISSUER 'MySQL AB' SUBJECT 'testsubject' CIPHER 'EDH-RSA-DES-CBC3-SHA'
39
delete from mysql.user where user='mysqltest_1';
41
delete from mysql.user where user='mysqltest_1';
43
grant usage on *.* to mysqltest_1@localhost with max_queries_per_hour 10;
44
select * from mysql.user where user="mysqltest_1";
45
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
46
localhost mysqltest_1 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 10 0 0 0
47
show grants for mysqltest_1@localhost;
48
Grants for mysqltest_1@localhost
49
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10
50
grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 20 max_connections_per_hour 30;
51
select * from mysql.user where user="mysqltest_1";
52
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
53
localhost mysqltest_1 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 10 20 30 0
54
show grants for mysqltest_1@localhost;
55
Grants for mysqltest_1@localhost
56
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10 MAX_UPDATES_PER_HOUR 20 MAX_CONNECTIONS_PER_HOUR 30
58
show grants for mysqltest_1@localhost;
59
Grants for mysqltest_1@localhost
60
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10 MAX_UPDATES_PER_HOUR 20 MAX_CONNECTIONS_PER_HOUR 30
61
delete from mysql.user where user='mysqltest_1';
63
grant CREATE TEMPORARY TABLES, LOCK TABLES on mysqltest.* to mysqltest_1@localhost;
64
show grants for mysqltest_1@localhost;
65
Grants for mysqltest_1@localhost
66
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
67
GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
69
show grants for mysqltest_1@localhost;
70
Grants for mysqltest_1@localhost
71
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
72
GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
73
revoke CREATE TEMPORARY TABLES on mysqltest.* from mysqltest_1@localhost;
74
show grants for mysqltest_1@localhost;
75
Grants for mysqltest_1@localhost
76
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
77
GRANT LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
78
grant ALL PRIVILEGES on mysqltest.* to mysqltest_1@localhost with GRANT OPTION;
80
show grants for mysqltest_1@localhost;
81
Grants for mysqltest_1@localhost
82
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
83
GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
84
revoke LOCK TABLES, ALTER on mysqltest.* from mysqltest_1@localhost;
85
show grants for mysqltest_1@localhost;
86
Grants for mysqltest_1@localhost
87
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
88
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
89
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
90
delete from mysql.user where user='mysqltest_1';
92
grant usage on test.* to mysqltest_1@localhost with grant option;
93
show grants for mysqltest_1@localhost;
94
Grants for mysqltest_1@localhost
95
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
96
GRANT USAGE ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
97
GRANT USAGE ON `test`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
98
delete from mysql.user where user='mysqltest_1';
99
delete from mysql.db where user='mysqltest_1';
100
delete from mysql.tables_priv where user='mysqltest_1';
101
delete from mysql.columns_priv where user='mysqltest_1';
103
show grants for mysqltest_1@localhost;
104
ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host 'localhost'
105
create table t1 (a int);
106
GRANT select,update,insert on t1 to mysqltest_1@localhost;
107
GRANT select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost;
108
show grants for mysqltest_1@localhost;
109
Grants for mysqltest_1@localhost
110
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
111
GRANT SELECT, SELECT (a), INSERT, INSERT (a), UPDATE, UPDATE (a), REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost'
112
select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1";
113
table_priv column_priv
114
Select,Insert,Update Select,Insert,Update,References
115
REVOKE select (a), update on t1 from mysqltest_1@localhost;
116
show grants for mysqltest_1@localhost;
117
Grants for mysqltest_1@localhost
118
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
119
GRANT SELECT, INSERT, INSERT (a), REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost'
120
REVOKE select,update,insert,insert (a) on t1 from mysqltest_1@localhost;
121
show grants for mysqltest_1@localhost;
122
Grants for mysqltest_1@localhost
123
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
124
GRANT REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost'
125
GRANT select,references on t1 to mysqltest_1@localhost;
126
select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1";
127
table_priv column_priv
128
Select,References References
129
grant all on test.* to mysqltest_3@localhost with grant option;
130
revoke all on test.* from mysqltest_3@localhost;
131
show grants for mysqltest_3@localhost;
132
Grants for mysqltest_3@localhost
133
GRANT USAGE ON *.* TO 'mysqltest_3'@'localhost'
134
GRANT USAGE ON `test`.* TO 'mysqltest_3'@'localhost' WITH GRANT OPTION
135
revoke grant option on test.* from mysqltest_3@localhost;
136
show grants for mysqltest_3@localhost;
137
Grants for mysqltest_3@localhost
138
GRANT USAGE ON *.* TO 'mysqltest_3'@'localhost'
139
grant all on test.t1 to mysqltest_2@localhost with grant option;
140
revoke all on test.t1 from mysqltest_2@localhost;
141
show grants for mysqltest_2@localhost;
142
Grants for mysqltest_2@localhost
143
GRANT USAGE ON *.* TO 'mysqltest_2'@'localhost'
144
GRANT USAGE ON `test`.`t1` TO 'mysqltest_2'@'localhost' WITH GRANT OPTION
145
revoke grant option on test.t1 from mysqltest_2@localhost;
146
show grants for mysqltest_2@localhost;
147
Grants for mysqltest_2@localhost
148
GRANT USAGE ON *.* TO 'mysqltest_2'@'localhost'
149
delete from mysql.user where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
150
delete from mysql.db where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
151
delete from mysql.tables_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
152
delete from mysql.columns_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
155
GRANT FILE on mysqltest.* to mysqltest_1@localhost;
156
ERROR HY000: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
160
insert into mysql.user (host, user) values ('localhost', 'test11');
162
Warning 1364 Field 'ssl_cipher' doesn't have a default value
163
Warning 1364 Field 'x509_issuer' doesn't have a default value
164
Warning 1364 Field 'x509_subject' doesn't have a default value
165
insert into mysql.db (host, db, user, select_priv) values
166
('localhost', 'a%', 'test11', 'Y'), ('localhost', 'ab%', 'test11', 'Y');
167
alter table mysql.db order by db asc;
169
show grants for test11@localhost;
170
Grants for test11@localhost
171
GRANT USAGE ON *.* TO 'test11'@'localhost'
172
GRANT SELECT ON `ab%`.* TO 'test11'@'localhost'
173
GRANT SELECT ON `a%`.* TO 'test11'@'localhost'
174
alter table mysql.db order by db desc;
176
show grants for test11@localhost;
177
Grants for test11@localhost
178
GRANT USAGE ON *.* TO 'test11'@'localhost'
179
GRANT SELECT ON `ab%`.* TO 'test11'@'localhost'
180
GRANT SELECT ON `a%`.* TO 'test11'@'localhost'
181
delete from mysql.user where user='test11';
182
delete from mysql.db where user='test11';
183
create database mysqltest1;
184
grant usage on mysqltest1.* to test6123 identified by 'magic123';
185
select host,db,user,select_priv,insert_priv from mysql.db where db="mysqltest1";
186
host db user select_priv insert_priv
187
delete from mysql.user where user='test6123';
188
drop database mysqltest1;
189
create table t1 (a int);
190
grant ALL PRIVILEGES on *.* to drop_user2@localhost with GRANT OPTION;
191
show grants for drop_user2@localhost;
192
Grants for drop_user2@localhost
193
GRANT ALL PRIVILEGES ON *.* TO 'drop_user2'@'localhost' WITH GRANT OPTION
194
revoke all privileges, grant option from drop_user2@localhost;
195
drop user drop_user2@localhost;
196
grant ALL PRIVILEGES on *.* to drop_user@localhost with GRANT OPTION;
197
grant ALL PRIVILEGES on test.* to drop_user@localhost with GRANT OPTION;
198
grant select(a) on test.t1 to drop_user@localhost;
199
show grants for drop_user@localhost;
200
Grants for drop_user@localhost
201
GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION
202
GRANT ALL PRIVILEGES ON `test`.* TO 'drop_user'@'localhost' WITH GRANT OPTION
203
GRANT SELECT (a) ON `test`.`t1` TO 'drop_user'@'localhost'
204
set sql_mode=ansi_quotes;
205
show grants for drop_user@localhost;
206
Grants for drop_user@localhost
207
GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION
208
GRANT ALL PRIVILEGES ON "test".* TO 'drop_user'@'localhost' WITH GRANT OPTION
209
GRANT SELECT (a) ON "test"."t1" TO 'drop_user'@'localhost'
210
set sql_mode=default;
211
set sql_quote_show_create=0;
212
show grants for drop_user@localhost;
213
Grants for drop_user@localhost
214
GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION
215
GRANT ALL PRIVILEGES ON test.* TO 'drop_user'@'localhost' WITH GRANT OPTION
216
GRANT SELECT (a) ON test.t1 TO 'drop_user'@'localhost'
217
set sql_mode="ansi_quotes";
218
show grants for drop_user@localhost;
219
Grants for drop_user@localhost
220
GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION
221
GRANT ALL PRIVILEGES ON test.* TO 'drop_user'@'localhost' WITH GRANT OPTION
222
GRANT SELECT (a) ON test.t1 TO 'drop_user'@'localhost'
223
set sql_quote_show_create=1;
224
show grants for drop_user@localhost;
225
Grants for drop_user@localhost
226
GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION
227
GRANT ALL PRIVILEGES ON "test".* TO 'drop_user'@'localhost' WITH GRANT OPTION
228
GRANT SELECT (a) ON "test"."t1" TO 'drop_user'@'localhost'
230
show grants for drop_user@localhost;
231
Grants for drop_user@localhost
232
GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION
233
GRANT ALL PRIVILEGES ON `test`.* TO 'drop_user'@'localhost' WITH GRANT OPTION
234
GRANT SELECT (a) ON `test`.`t1` TO 'drop_user'@'localhost'
235
revoke all privileges, grant option from drop_user@localhost;
236
show grants for drop_user@localhost;
237
Grants for drop_user@localhost
238
GRANT USAGE ON *.* TO 'drop_user'@'localhost'
239
drop user drop_user@localhost;
240
revoke all privileges, grant option from drop_user@localhost;
241
ERROR HY000: Can't revoke all privileges for one or more of the requested users
242
grant select(a) on test.t1 to drop_user1@localhost;
243
grant select on test.t1 to drop_user2@localhost;
244
grant select on test.* to drop_user3@localhost;
245
grant select on *.* to drop_user4@localhost;
246
drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost,
247
drop_user4@localhost;
248
revoke all privileges, grant option from drop_user1@localhost, drop_user2@localhost,
249
drop_user3@localhost, drop_user4@localhost;
250
ERROR HY000: Can't revoke all privileges for one or more of the requested users
251
drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost,
252
drop_user4@localhost;
253
ERROR HY000: Operation DROP USER failed for 'drop_user1'@'localhost','drop_user2'@'localhost','drop_user3'@'localhost','drop_user4'@'localhost'
255
grant usage on *.* to mysqltest_1@localhost identified by "password";
256
grant select, update, insert on test.* to mysqltest_1@localhost;
257
show grants for mysqltest_1@localhost;
258
Grants for mysqltest_1@localhost
259
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
260
GRANT SELECT, INSERT, UPDATE ON `test`.* TO 'mysqltest_1'@'localhost'
261
drop user mysqltest_1@localhost;
265
CREATE TABLE ��� (��� int);
266
GRANT SELECT ON ��.* TO ����@localhost;
267
SHOW GRANTS FOR ����@localhost;
268
Grants for ����@localhost
269
GRANT USAGE ON *.* TO '����'@'localhost'
270
GRANT SELECT ON `��`.* TO '����'@'localhost'
271
REVOKE SELECT ON ��.* FROM ����@localhost;
272
GRANT SELECT ON ��.��� TO ����@localhost;
273
SHOW GRANTS FOR ����@localhost;
274
Grants for ����@localhost
275
GRANT USAGE ON *.* TO '����'@'localhost'
276
GRANT SELECT ON `��`.`���` TO '����'@'localhost'
277
REVOKE SELECT ON ��.��� FROM ����@localhost;
278
GRANT SELECT (���) ON ��.��� TO ����@localhost;
279
SHOW GRANTS FOR ����@localhost;
280
Grants for ����@localhost
281
GRANT USAGE ON *.* TO '����'@'localhost'
282
GRANT SELECT (���) ON `��`.`���` TO '����'@'localhost'
283
REVOKE SELECT (���) ON ��.��� FROM ����@localhost;
284
DROP USER ����@localhost;
288
CREATE TABLE t1 (a int );
289
CREATE TABLE t2 LIKE t1;
290
CREATE TABLE t3 LIKE t1;
291
CREATE TABLE t4 LIKE t1;
292
CREATE TABLE t5 LIKE t1;
293
CREATE TABLE t6 LIKE t1;
294
CREATE TABLE t7 LIKE t1;
295
CREATE TABLE t8 LIKE t1;
296
CREATE TABLE t9 LIKE t1;
297
CREATE TABLE t10 LIKE t1;
298
CREATE DATABASE testdb1;
299
CREATE DATABASE testdb2;
300
CREATE DATABASE testdb3;
301
CREATE DATABASE testdb4;
302
CREATE DATABASE testdb5;
303
CREATE DATABASE testdb6;
304
CREATE DATABASE testdb7;
305
CREATE DATABASE testdb8;
306
CREATE DATABASE testdb9;
307
CREATE DATABASE testdb10;
308
GRANT ALL ON testdb1.* TO testuser@localhost;
309
GRANT ALL ON testdb2.* TO testuser@localhost;
310
GRANT ALL ON testdb3.* TO testuser@localhost;
311
GRANT ALL ON testdb4.* TO testuser@localhost;
312
GRANT ALL ON testdb5.* TO testuser@localhost;
313
GRANT ALL ON testdb6.* TO testuser@localhost;
314
GRANT ALL ON testdb7.* TO testuser@localhost;
315
GRANT ALL ON testdb8.* TO testuser@localhost;
316
GRANT ALL ON testdb9.* TO testuser@localhost;
317
GRANT ALL ON testdb10.* TO testuser@localhost;
318
GRANT SELECT ON test.t1 TO testuser@localhost;
319
GRANT SELECT ON test.t2 TO testuser@localhost;
320
GRANT SELECT ON test.t3 TO testuser@localhost;
321
GRANT SELECT ON test.t4 TO testuser@localhost;
322
GRANT SELECT ON test.t5 TO testuser@localhost;
323
GRANT SELECT ON test.t6 TO testuser@localhost;
324
GRANT SELECT ON test.t7 TO testuser@localhost;
325
GRANT SELECT ON test.t8 TO testuser@localhost;
326
GRANT SELECT ON test.t9 TO testuser@localhost;
327
GRANT SELECT ON test.t10 TO testuser@localhost;
328
GRANT SELECT (a) ON test.t1 TO testuser@localhost;
329
GRANT SELECT (a) ON test.t2 TO testuser@localhost;
330
GRANT SELECT (a) ON test.t3 TO testuser@localhost;
331
GRANT SELECT (a) ON test.t4 TO testuser@localhost;
332
GRANT SELECT (a) ON test.t5 TO testuser@localhost;
333
GRANT SELECT (a) ON test.t6 TO testuser@localhost;
334
GRANT SELECT (a) ON test.t7 TO testuser@localhost;
335
GRANT SELECT (a) ON test.t8 TO testuser@localhost;
336
GRANT SELECT (a) ON test.t9 TO testuser@localhost;
337
GRANT SELECT (a) ON test.t10 TO testuser@localhost;
338
REVOKE ALL PRIVILEGES, GRANT OPTION FROM testuser@localhost;
339
SHOW GRANTS FOR testuser@localhost;
340
Grants for testuser@localhost
341
GRANT USAGE ON *.* TO 'testuser'@'localhost'
342
DROP USER testuser@localhost;
343
DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10;
344
DROP DATABASE testdb1;
345
DROP DATABASE testdb2;
346
DROP DATABASE testdb3;
347
DROP DATABASE testdb4;
348
DROP DATABASE testdb5;
349
DROP DATABASE testdb6;
350
DROP DATABASE testdb7;
351
DROP DATABASE testdb8;
352
DROP DATABASE testdb9;
353
DROP DATABASE testdb10;
354
create table t1(a int, b int, c int, d int);
355
grant insert(b), insert(c), insert(d), insert(a) on t1 to grant_user@localhost;
356
show grants for grant_user@localhost;
357
Grants for grant_user@localhost
358
GRANT USAGE ON *.* TO 'grant_user'@'localhost'
359
GRANT INSERT (a, d, c, b) ON `test`.`t1` TO 'grant_user'@'localhost'
360
select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv order by Column_name;
361
Host Db User Table_name Column_name Column_priv
362
localhost test grant_user t1 a Insert
363
localhost test grant_user t1 b Insert
364
localhost test grant_user t1 c Insert
365
localhost test grant_user t1 d Insert
366
revoke ALL PRIVILEGES on t1 from grant_user@localhost;
367
show grants for grant_user@localhost;
368
Grants for grant_user@localhost
369
GRANT USAGE ON *.* TO 'grant_user'@'localhost'
370
select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv;
371
Host Db User Table_name Column_name Column_priv
372
drop user grant_user@localhost;
374
create database mysqltest_1;
375
create database mysqltest_2;
376
create table mysqltest_1.t1 select 1 a, 2 q;
377
create table mysqltest_1.t2 select 1 b, 2 r;
378
create table mysqltest_2.t1 engine=myisam select 1 c, 2 s;
379
create table mysqltest_2.t2 engine=myisam select 1 d, 2 t;
380
grant update (a) on mysqltest_1.t1 to mysqltest_3@localhost;
381
grant select (b) on mysqltest_1.t2 to mysqltest_3@localhost;
382
grant select (c) on mysqltest_2.t1 to mysqltest_3@localhost;
383
grant update (d) on mysqltest_2.t2 to mysqltest_3@localhost;
384
SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
385
WHERE GRANTEE = '''mysqltest_3''@''localhost'''
386
ORDER BY TABLE_NAME,COLUMN_NAME,PRIVILEGE_TYPE;
387
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
388
'mysqltest_3'@'localhost' NULL mysqltest_1 t1 a UPDATE NO
389
'mysqltest_3'@'localhost' NULL mysqltest_2 t1 c SELECT NO
390
'mysqltest_3'@'localhost' NULL mysqltest_1 t2 b SELECT NO
391
'mysqltest_3'@'localhost' NULL mysqltest_2 t2 d UPDATE NO
392
SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
393
WHERE GRANTEE = '''mysqltest_3''@''localhost'''
394
ORDER BY TABLE_NAME,PRIVILEGE_TYPE;
395
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
396
SELECT * from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
397
WHERE GRANTEE = '''mysqltest_3''@''localhost'''
398
ORDER BY TABLE_SCHEMA,PRIVILEGE_TYPE;
399
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
400
SELECT * from INFORMATION_SCHEMA.USER_PRIVILEGES
401
WHERE GRANTEE = '''mysqltest_3''@''localhost'''
402
ORDER BY TABLE_CATALOG,PRIVILEGE_TYPE;
403
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
404
'mysqltest_3'@'localhost' NULL USAGE NO
405
update mysqltest_1.t1, mysqltest_1.t2 set q=10 where b=1;
406
ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for column 'q' in table 't1'
407
update mysqltest_1.t2, mysqltest_2.t2 set d=20 where d=1;
408
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for column 'd' in table 't2'
409
update mysqltest_1.t1, mysqltest_2.t2 set d=20 where d=1;
410
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 't1'
411
update mysqltest_2.t1, mysqltest_1.t2 set c=20 where b=1;
412
ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't1'
413
update mysqltest_2.t1, mysqltest_2.t2 set d=10 where s=2;
414
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for column 's' in table 't1'
415
update mysqltest_1.t1, mysqltest_2.t2 set a=10,d=10;
416
update mysqltest_1.t1, mysqltest_2.t1 set a=20 where c=20;
417
select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2;
420
select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2;
423
revoke all on mysqltest_1.t1 from mysqltest_3@localhost;
424
revoke all on mysqltest_1.t2 from mysqltest_3@localhost;
425
revoke all on mysqltest_2.t1 from mysqltest_3@localhost;
426
revoke all on mysqltest_2.t2 from mysqltest_3@localhost;
427
grant all on mysqltest_2.* to mysqltest_3@localhost;
428
grant select on *.* to mysqltest_3@localhost;
429
grant select on mysqltest_2.t1 to mysqltest_3@localhost;
432
update mysqltest_2.t1, mysqltest_2.t2 set c=500,d=600;
433
update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200;
434
ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't1'
436
update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200;
437
ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't1'
438
update mysqltest_2.t1, mysqltest_1.t2 set c=100,b=200;
439
ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't2'
440
update mysqltest_1.t1, mysqltest_2.t2 set a=100,d=200;
441
ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't1'
442
select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2;
445
select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2;
448
delete from mysql.user where user='mysqltest_3';
449
delete from mysql.db where user="mysqltest_3";
450
delete from mysql.tables_priv where user="mysqltest_3";
451
delete from mysql.columns_priv where user="mysqltest_3";
453
drop database mysqltest_1;
454
drop database mysqltest_2;
456
Privilege Context Comment
457
Alter Tables To alter the table
458
Alter routine Functions,Procedures To alter or drop stored functions/procedures
459
Create Databases,Tables,Indexes To create new databases and tables
460
Create routine Functions,Procedures To use CREATE FUNCTION/PROCEDURE
461
Create temporary tables Databases To use CREATE TEMPORARY TABLE
462
Create view Tables To create new views
463
Create user Server Admin To create new users
464
Delete Tables To delete existing rows
465
Drop Databases,Tables To drop databases, tables, and views
466
Event Server Admin To create, alter, drop and execute events
467
Execute Functions,Procedures To execute stored routines
468
File File access on server To read and write files on the server
469
Grant option Databases,Tables,Functions,Procedures To give to other users those privileges you possess
470
Index Tables To create or drop indexes
471
Insert Tables To insert data into tables
472
Lock tables Databases To use LOCK TABLES (together with SELECT privilege)
473
Process Server Admin To view the plain text of currently executing queries
474
References Databases,Tables To have references on tables
475
Reload Server Admin To reload or refresh tables, logs and privileges
476
Replication client Server Admin To ask where the slave or master servers are
477
Replication slave Server Admin To read binary log events from the master
478
Select Tables To retrieve rows from table
479
Show databases Server Admin To see all databases with SHOW DATABASES
480
Show view Tables To see views with SHOW CREATE VIEW
481
Shutdown Server Admin To shut down the server
482
Super Server Admin To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.
483
Trigger Tables To use triggers
484
Update Tables To update existing rows
485
Usage Server Admin No privileges - allow connect only
486
create database mysqltest;
487
create table mysqltest.t1 (a int,b int,c int);
488
grant all on mysqltest.t1 to mysqltest_1@localhost;
489
alter table t1 rename t2;
490
ERROR 42000: INSERT,CREATE command denied to user 'mysqltest_1'@'localhost' for table 't2'
491
revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
492
delete from mysql.user where user=_binary'mysqltest_1';
493
drop database mysqltest;
494
CREATE USER dummy@localhost;
495
CREATE DATABASE mysqltest;
496
CREATE TABLE mysqltest.dummytable (dummyfield INT);
497
CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable;
498
GRANT ALL PRIVILEGES ON mysqltest.dummytable TO dummy@localhost;
499
GRANT ALL PRIVILEGES ON mysqltest.dummyview TO dummy@localhost;
500
SHOW GRANTS FOR dummy@localhost;
501
Grants for dummy@localhost
502
GRANT USAGE ON *.* TO 'dummy'@'localhost'
503
GRANT ALL PRIVILEGES ON `mysqltest`.`dummyview` TO 'dummy'@'localhost'
504
GRANT ALL PRIVILEGES ON `mysqltest`.`dummytable` TO 'dummy'@'localhost'
505
use INFORMATION_SCHEMA;
506
SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
507
PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
508
= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
509
TABLE_SCHEMA TABLE_NAME PRIVILEGES
510
mysqltest dummytable ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE
511
mysqltest dummyview ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE
513
SHOW GRANTS FOR dummy@localhost;
514
Grants for dummy@localhost
515
GRANT USAGE ON *.* TO 'dummy'@'localhost'
516
GRANT ALL PRIVILEGES ON `mysqltest`.`dummyview` TO 'dummy'@'localhost'
517
GRANT ALL PRIVILEGES ON `mysqltest`.`dummytable` TO 'dummy'@'localhost'
518
SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
519
PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
520
= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
521
TABLE_SCHEMA TABLE_NAME PRIVILEGES
522
mysqltest dummytable ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE
523
mysqltest dummyview ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE
524
SHOW FIELDS FROM mysql.tables_priv;
525
Field Type Null Key Default Extra
529
Table_name char(64) NO PRI
530
Grantor char(77) NO MUL
531
Timestamp timestamp NO CURRENT_TIMESTAMP
532
Table_priv set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') NO
533
Column_priv set('Select','Insert','Update','References') NO
535
REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost;
536
DROP USER dummy@localhost;
537
DROP DATABASE mysqltest;
538
CREATE USER dummy@localhost;
539
CREATE DATABASE mysqltest;
540
CREATE TABLE mysqltest.dummytable (dummyfield INT);
541
CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable;
542
GRANT CREATE VIEW ON mysqltest.dummytable TO dummy@localhost;
543
GRANT CREATE VIEW ON mysqltest.dummyview TO dummy@localhost;
544
SHOW GRANTS FOR dummy@localhost;
545
Grants for dummy@localhost
546
GRANT USAGE ON *.* TO 'dummy'@'localhost'
547
GRANT CREATE VIEW ON `mysqltest`.`dummyview` TO 'dummy'@'localhost'
548
GRANT CREATE VIEW ON `mysqltest`.`dummytable` TO 'dummy'@'localhost'
549
use INFORMATION_SCHEMA;
550
SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
551
PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
552
= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
553
TABLE_SCHEMA TABLE_NAME PRIVILEGES
554
mysqltest dummytable CREATE VIEW
555
mysqltest dummyview CREATE VIEW
557
SHOW GRANTS FOR dummy@localhost;
558
Grants for dummy@localhost
559
GRANT USAGE ON *.* TO 'dummy'@'localhost'
560
GRANT CREATE VIEW ON `mysqltest`.`dummyview` TO 'dummy'@'localhost'
561
GRANT CREATE VIEW ON `mysqltest`.`dummytable` TO 'dummy'@'localhost'
562
SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
563
PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
564
= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
565
TABLE_SCHEMA TABLE_NAME PRIVILEGES
566
mysqltest dummytable CREATE VIEW
567
mysqltest dummyview CREATE VIEW
569
REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost;
570
DROP USER dummy@localhost;
571
DROP DATABASE mysqltest;
572
CREATE USER dummy@localhost;
573
CREATE DATABASE mysqltest;
574
CREATE TABLE mysqltest.dummytable (dummyfield INT);
575
CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable;
576
GRANT SHOW VIEW ON mysqltest.dummytable TO dummy@localhost;
577
GRANT SHOW VIEW ON mysqltest.dummyview TO dummy@localhost;
578
SHOW GRANTS FOR dummy@localhost;
579
Grants for dummy@localhost
580
GRANT USAGE ON *.* TO 'dummy'@'localhost'
581
GRANT SHOW VIEW ON `mysqltest`.`dummyview` TO 'dummy'@'localhost'
582
GRANT SHOW VIEW ON `mysqltest`.`dummytable` TO 'dummy'@'localhost'
583
use INFORMATION_SCHEMA;
584
SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
585
PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
586
= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
587
TABLE_SCHEMA TABLE_NAME PRIVILEGES
588
mysqltest dummytable SHOW VIEW
589
mysqltest dummyview SHOW VIEW
591
SHOW GRANTS FOR dummy@localhost;
592
Grants for dummy@localhost
593
GRANT USAGE ON *.* TO 'dummy'@'localhost'
594
GRANT SHOW VIEW ON `mysqltest`.`dummyview` TO 'dummy'@'localhost'
595
GRANT SHOW VIEW ON `mysqltest`.`dummytable` TO 'dummy'@'localhost'
596
SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
597
PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
598
= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
599
TABLE_SCHEMA TABLE_NAME PRIVILEGES
600
mysqltest dummytable SHOW VIEW
601
mysqltest dummyview SHOW VIEW
603
REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost;
604
DROP USER dummy@localhost;
605
DROP DATABASE mysqltest;
607
insert into tables_priv values ('','test_db','mysqltest_1','test_table','test_grantor',CURRENT_TIMESTAMP,'Select','Select');
609
delete from tables_priv where host = '' and user = 'mysqltest_1';
612
set @user123="non-existent";
613
select * from mysql.db where user=@user123;
614
Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv
617
grant select on ��.* to root@localhost;
618
select hex(Db) from mysql.db where Db='��';
621
show grants for root@localhost;
622
Grants for root@localhost
623
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
624
GRANT SELECT ON `��`.* TO 'root'@'localhost'
626
show grants for root@localhost;
627
Grants for root@localhost
628
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
629
GRANT SELECT ON `��`.* TO 'root'@'localhost'
631
revoke all privileges on ��.* from root@localhost;
632
show grants for root@localhost;
633
Grants for root@localhost
634
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
636
create user mysqltest_7@;
637
set password for mysqltest_7@ = password('systpass');
638
show grants for mysqltest_7@;
639
Grants for mysqltest_7@
640
GRANT USAGE ON *.* TO 'mysqltest_7'@'' IDENTIFIED BY PASSWORD '*2FB071A056F9BB745219D9C876814231DAF46517'
641
drop user mysqltest_7@;
642
show grants for mysqltest_7@;
643
ERROR 42000: There is no such grant defined for user 'mysqltest_7' on host ''
644
create database mysqltest;
646
create table t1(f1 int);
647
GRANT DELETE ON mysqltest.t1 TO mysqltest1@'%';
648
GRANT SELECT ON mysqltest.t1 TO mysqltest1@'192.%';
649
show grants for mysqltest1@'192.%';
650
Grants for mysqltest1@192.%
651
GRANT USAGE ON *.* TO 'mysqltest1'@'192.%'
652
GRANT SELECT ON `mysqltest`.`t1` TO 'mysqltest1'@'192.%'
653
show grants for mysqltest1@'%';
654
Grants for mysqltest1@%
655
GRANT USAGE ON *.* TO 'mysqltest1'@'%'
656
GRANT DELETE ON `mysqltest`.`t1` TO 'mysqltest1'@'%'
657
delete from mysql.user where user='mysqltest1';
658
delete from mysql.db where user='mysqltest1';
659
delete from mysql.tables_priv where user='mysqltest1';
661
drop database mysqltest;
663
create table t1 (a int);
664
create table t2 as select * from mysql.user where user='';
665
delete from mysql.user where user='';
667
create user mysqltest_8@'';
668
create user mysqltest_8;
669
create user mysqltest_8@host8;
670
create user mysqltest_8@'';
671
ERROR HY000: Operation CREATE USER failed for 'mysqltest_8'@''
672
create user mysqltest_8;
673
ERROR HY000: Operation CREATE USER failed for 'mysqltest_8'@'%'
674
create user mysqltest_8@host8;
675
ERROR HY000: Operation CREATE USER failed for 'mysqltest_8'@'host8'
676
select user, QUOTE(host) from mysql.user where user="mysqltest_8";
682
grant select on mysqltest.* to mysqltest_8@'';
683
show grants for mysqltest_8@'';
684
Grants for mysqltest_8@
685
GRANT USAGE ON *.* TO 'mysqltest_8'@''
686
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@''
687
grant select on mysqltest.* to mysqltest_8@;
688
show grants for mysqltest_8@;
689
Grants for mysqltest_8@
690
GRANT USAGE ON *.* TO 'mysqltest_8'@''
691
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@''
692
grant select on mysqltest.* to mysqltest_8;
693
show grants for mysqltest_8;
694
Grants for mysqltest_8@%
695
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
696
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@'%'
697
select * from information_schema.schema_privileges
698
where grantee like "'mysqltest_8'%";
699
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
700
'mysqltest_8'@'%' NULL mysqltest SELECT NO
701
'mysqltest_8'@'' NULL mysqltest SELECT NO
704
revoke select on mysqltest.* from mysqltest_8@'';
705
revoke select on mysqltest.* from mysqltest_8;
706
show grants for mysqltest_8@'';
707
Grants for mysqltest_8@
708
GRANT USAGE ON *.* TO 'mysqltest_8'@''
709
show grants for mysqltest_8;
710
Grants for mysqltest_8@%
711
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
712
select * from information_schema.schema_privileges
713
where grantee like "'mysqltest_8'%";
714
GRANTEE TABLE_CATALOG TABLE_SCHEMA PRIVILEGE_TYPE IS_GRANTABLE
716
show grants for mysqltest_8@'';
717
Grants for mysqltest_8@
718
GRANT USAGE ON *.* TO 'mysqltest_8'@''
719
show grants for mysqltest_8@;
720
Grants for mysqltest_8@
721
GRANT USAGE ON *.* TO 'mysqltest_8'@''
722
grant select on mysqltest.* to mysqltest_8@'';
724
show grants for mysqltest_8@;
725
Grants for mysqltest_8@
726
GRANT USAGE ON *.* TO 'mysqltest_8'@''
727
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@''
728
revoke select on mysqltest.* from mysqltest_8@'';
731
grant update (a) on t1 to mysqltest_8@'';
732
grant update (a) on t1 to mysqltest_8;
733
show grants for mysqltest_8@'';
734
Grants for mysqltest_8@
735
GRANT USAGE ON *.* TO 'mysqltest_8'@''
736
GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@''
737
show grants for mysqltest_8;
738
Grants for mysqltest_8@%
739
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
740
GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%'
742
show grants for mysqltest_8@'';
743
Grants for mysqltest_8@
744
GRANT USAGE ON *.* TO 'mysqltest_8'@''
745
GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@''
746
show grants for mysqltest_8;
747
Grants for mysqltest_8@%
748
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
749
GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%'
750
select * from information_schema.column_privileges;
751
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
752
'mysqltest_8'@'%' NULL test t1 a UPDATE NO
753
'mysqltest_8'@'' NULL test t1 a UPDATE NO
756
revoke update (a) on t1 from mysqltest_8@'';
757
revoke update (a) on t1 from mysqltest_8;
758
show grants for mysqltest_8@'';
759
Grants for mysqltest_8@
760
GRANT USAGE ON *.* TO 'mysqltest_8'@''
761
show grants for mysqltest_8;
762
Grants for mysqltest_8@%
763
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
764
select * from information_schema.column_privileges;
765
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME PRIVILEGE_TYPE IS_GRANTABLE
767
show grants for mysqltest_8@'';
768
Grants for mysqltest_8@
769
GRANT USAGE ON *.* TO 'mysqltest_8'@''
770
show grants for mysqltest_8;
771
Grants for mysqltest_8@%
772
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
774
grant update on t1 to mysqltest_8@'';
775
grant update on t1 to mysqltest_8;
776
show grants for mysqltest_8@'';
777
Grants for mysqltest_8@
778
GRANT USAGE ON *.* TO 'mysqltest_8'@''
779
GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@''
780
show grants for mysqltest_8;
781
Grants for mysqltest_8@%
782
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
783
GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@'%'
785
show grants for mysqltest_8@'';
786
Grants for mysqltest_8@
787
GRANT USAGE ON *.* TO 'mysqltest_8'@''
788
GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@''
789
show grants for mysqltest_8;
790
Grants for mysqltest_8@%
791
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
792
GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@'%'
793
select * from information_schema.table_privileges;
794
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
795
'mysqltest_8'@'%' NULL test t1 UPDATE NO
796
'mysqltest_8'@'' NULL test t1 UPDATE NO
799
revoke update on t1 from mysqltest_8@'';
800
revoke update on t1 from mysqltest_8;
801
show grants for mysqltest_8@'';
802
Grants for mysqltest_8@
803
GRANT USAGE ON *.* TO 'mysqltest_8'@''
804
show grants for mysqltest_8;
805
Grants for mysqltest_8@%
806
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
807
select * from information_schema.table_privileges;
808
GRANTEE TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PRIVILEGE_TYPE IS_GRANTABLE
810
show grants for mysqltest_8@'';
811
Grants for mysqltest_8@
812
GRANT USAGE ON *.* TO 'mysqltest_8'@''
813
show grants for mysqltest_8;
814
Grants for mysqltest_8@%
815
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
816
"DROP USER" should clear privileges
817
grant all privileges on mysqltest.* to mysqltest_8@'';
818
grant select on mysqltest.* to mysqltest_8@'';
819
grant update on t1 to mysqltest_8@'';
820
grant update (a) on t1 to mysqltest_8@'';
821
grant all privileges on mysqltest.* to mysqltest_8;
822
show grants for mysqltest_8@'';
823
Grants for mysqltest_8@
824
GRANT USAGE ON *.* TO 'mysqltest_8'@''
825
GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@''
826
GRANT UPDATE, UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@''
827
show grants for mysqltest_8;
828
Grants for mysqltest_8@%
829
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
830
GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'%'
831
select * from information_schema.user_privileges
832
where grantee like "'mysqltest_8'%";
833
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
834
'mysqltest_8'@'host8' NULL USAGE NO
835
'mysqltest_8'@'%' NULL USAGE NO
836
'mysqltest_8'@'' NULL USAGE NO
840
show grants for mysqltest_8@'';
841
Grants for mysqltest_8@
842
GRANT USAGE ON *.* TO 'mysqltest_8'@''
843
GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@''
844
GRANT UPDATE, UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@''
845
show grants for mysqltest_8;
846
Grants for mysqltest_8@%
847
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
848
GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'%'
849
drop user mysqltest_8@'';
850
show grants for mysqltest_8@'';
851
ERROR 42000: There is no such grant defined for user 'mysqltest_8' on host ''
852
show grants for mysqltest_8;
853
Grants for mysqltest_8@%
854
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
855
GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'%'
856
select * from information_schema.user_privileges
857
where grantee like "'mysqltest_8'%";
858
GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
859
'mysqltest_8'@'host8' NULL USAGE NO
860
'mysqltest_8'@'%' NULL USAGE NO
861
drop user mysqltest_8;
862
connect(localhost,mysqltest_8,,test,MASTER_PORT,MASTER_SOCKET);
863
ERROR 28000: Access denied for user 'mysqltest_8'@'localhost' (using password: NO)
864
show grants for mysqltest_8;
865
ERROR 42000: There is no such grant defined for user 'mysqltest_8' on host '%'
866
drop user mysqltest_8@host8;
867
show grants for mysqltest_8@host8;
868
ERROR 42000: There is no such grant defined for user 'mysqltest_8' on host 'host8'
869
insert into mysql.user select * from t2;
873
CREATE DATABASE mysqltest3;
875
CREATE TABLE t_nn (c1 INT);
876
CREATE VIEW v_nn AS SELECT * FROM t_nn;
877
CREATE DATABASE mysqltest2;
879
CREATE TABLE t_nn (c1 INT);
880
CREATE VIEW v_nn AS SELECT * FROM t_nn;
881
CREATE VIEW v_yn AS SELECT * FROM t_nn;
882
CREATE VIEW v_gy AS SELECT * FROM t_nn;
883
CREATE VIEW v_ny AS SELECT * FROM t_nn;
884
CREATE VIEW v_yy AS SELECT * FROM t_nn WHERE c1=55;
885
GRANT SHOW VIEW ON mysqltest2.v_ny TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
886
GRANT SELECT ON mysqltest2.v_yn TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
887
GRANT SELECT ON mysqltest2.* TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
888
GRANT SHOW VIEW,SELECT ON mysqltest2.v_yy TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
889
SHOW CREATE VIEW mysqltest2.v_nn;
890
ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v_nn'
891
SHOW CREATE TABLE mysqltest2.v_nn;
892
ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v_nn'
893
SHOW CREATE VIEW mysqltest2.v_yn;
894
ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v_yn'
895
SHOW CREATE TABLE mysqltest2.v_yn;
896
ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v_yn'
897
SHOW CREATE TABLE mysqltest2.v_ny;
899
v_ny CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_ny` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn`
900
SHOW CREATE VIEW mysqltest2.v_ny;
902
v_ny CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_ny` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn`
903
SHOW CREATE TABLE mysqltest3.t_nn;
904
ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 't_nn'
905
SHOW CREATE VIEW mysqltest3.t_nn;
906
ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 't_nn'
907
SHOW CREATE VIEW mysqltest3.v_nn;
908
ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v_nn'
909
SHOW CREATE TABLE mysqltest3.v_nn;
910
ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v_nn'
911
SHOW CREATE TABLE mysqltest2.t_nn;
913
t_nn CREATE TABLE `t_nn` (
914
`c1` int(11) DEFAULT NULL
915
) ENGINE=PBXT DEFAULT CHARSET=latin1
916
SHOW CREATE VIEW mysqltest2.t_nn;
917
ERROR HY000: 'mysqltest2.t_nn' is not VIEW
918
SHOW CREATE VIEW mysqltest2.v_yy;
920
v_yy CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_yy` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn` where (`mysqltest2`.`t_nn`.`c1` = 55)
921
SHOW CREATE TABLE mysqltest2.v_yy;
923
v_yy CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_yy` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn` where (`mysqltest2`.`t_nn`.`c1` = 55)
924
SHOW CREATE TABLE mysqltest2.v_nn;
926
v_nn CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_nn` AS select `t_nn`.`c1` AS `c1` from `t_nn`
927
SHOW CREATE VIEW mysqltest2.v_nn;
929
v_nn CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_nn` AS select `t_nn`.`c1` AS `c1` from `t_nn`
930
SHOW CREATE TABLE mysqltest2.t_nn;
932
t_nn CREATE TABLE `t_nn` (
933
`c1` int(11) DEFAULT NULL
934
) ENGINE=PBXT DEFAULT CHARSET=latin1
935
SHOW CREATE VIEW mysqltest2.t_nn;
936
ERROR HY000: 'mysqltest2.t_nn' is not VIEW
937
DROP VIEW mysqltest2.v_nn;
938
DROP VIEW mysqltest2.v_yn;
939
DROP VIEW mysqltest2.v_ny;
940
DROP VIEW mysqltest2.v_yy;
941
DROP TABLE mysqltest2.t_nn;
942
DROP DATABASE mysqltest2;
943
DROP VIEW mysqltest3.v_nn;
944
DROP TABLE mysqltest3.t_nn;
945
DROP DATABASE mysqltest3;
946
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'mysqltest_1'@'localhost';
947
DROP USER 'mysqltest_1'@'localhost';
949
create user mysqltest1_thisisreallytoolong;
950
ERROR HY000: String 'mysqltest1_thisisreallytoolong' is too long for user name (should be no longer than 16)
951
GRANT CREATE ON mysqltest.* TO 1234567890abcdefGHIKL@localhost;
952
ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)
953
GRANT CREATE ON mysqltest.* TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
954
ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)
955
REVOKE CREATE ON mysqltest.* FROM 1234567890abcdefGHIKL@localhost;
956
ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)
957
REVOKE CREATE ON mysqltest.* FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
958
ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)
959
GRANT CREATE ON t1 TO 1234567890abcdefGHIKL@localhost;
960
ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)
961
GRANT CREATE ON t1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
962
ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)
963
REVOKE CREATE ON t1 FROM 1234567890abcdefGHIKL@localhost;
964
ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)
965
REVOKE CREATE ON t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
966
ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)
967
GRANT EXECUTE ON PROCEDURE p1 TO 1234567890abcdefGHIKL@localhost;
968
ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)
969
GRANT EXECUTE ON PROCEDURE p1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
970
ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)
971
REVOKE EXECUTE ON PROCEDURE p1 FROM 1234567890abcdefGHIKL@localhost;
972
ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)
973
REVOKE EXECUTE ON PROCEDURE t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
974
ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)
added
removed
pbxt/mysql-test-update/mysql-test/r/grant.result
