1
drop table if exists test.t1,mysqltest.t1,mysqltest.t2;
2
drop database if exists mysqltest;
3
set GLOBAL query_cache_size=1355776;
6
----- establish connection root -----
7
show grants for current_user;
8
Grants for root@localhost
9
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
11
Grants for root@localhost
12
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
13
create database if not exists mysqltest;
14
create table mysqltest.t1 (a int,b int,c int);
15
create table mysqltest.t2 (a int,b int,c int);
16
insert into mysqltest.t1 values (1,1,1),(2,2,2);
17
insert into mysqltest.t2 values (3,3,3);
18
create table test.t1 (a char (10));
19
insert into test.t1 values ("test.t1");
23
----- establish connection root2 -----
39
select * from mysqltest.t1,test.t1;
43
show status like "Qcache_queries_in_cache";
45
Qcache_queries_in_cache 6
46
show status like "Qcache_hits%";
49
grant SELECT on mysqltest.* to mysqltest_1@localhost;
50
grant SELECT on mysqltest.t1 to mysqltest_2@localhost;
51
grant SELECT on test.t1 to mysqltest_2@localhost;
52
grant SELECT(a) on mysqltest.t1 to mysqltest_3@localhost;
53
----- establish connection user1 (user=mysqltest_1) -----
54
show grants for current_user();
55
Grants for mysqltest_1@localhost
56
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
57
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
58
show status like "Qcache_queries_in_cache";
60
Qcache_queries_in_cache 6
61
show status like "Qcache_hits";
64
show status like "Qcache_not_cached";
70
show status like "Qcache_queries_in_cache";
72
Qcache_queries_in_cache 6
73
show status like "Qcache_hits";
76
show status like "Qcache_not_cached";
83
show status like "Qcache_queries_in_cache";
85
Qcache_queries_in_cache 6
86
show status like "Qcache_hits";
89
show status like "Qcache_not_cached";
96
show status like "Qcache_queries_in_cache";
98
Qcache_queries_in_cache 6
99
show status like "Qcache_hits";
102
show status like "Qcache_not_cached";
109
show status like "Qcache_queries_in_cache";
111
Qcache_queries_in_cache 6
112
show status like "Qcache_hits";
115
show status like "Qcache_not_cached";
118
----- establish connection unkuser (user=unkuser) -----
119
show grants for current_user();
120
Grants for @localhost
121
GRANT USAGE ON *.* TO ''@'localhost'
122
----- establish connection user2 (user=mysqltest_2) -----
138
select * from mysqltest.t1,test.t1;
143
ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table 't2'
144
show status like "Qcache_queries_in_cache";
146
Qcache_queries_in_cache 6
147
show status like "Qcache_hits";
150
show status like "Qcache_not_cached";
153
----- establish connection user3 (user=mysqltest_3) -----
158
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 't1'
164
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for column 'c' in table 't1'
166
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 't2'
167
select mysqltest.t1.c from test.t1,mysqltest.t1;
168
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for column 'c' in table 't1'
169
show status like "Qcache_queries_in_cache";
171
Qcache_queries_in_cache 6
172
show status like "Qcache_hits";
175
show status like "Qcache_not_cached";
178
----- establish connection user4 (user=mysqltest_1) -----
183
Grants for mysqltest_1@localhost
184
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
185
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
187
ERROR 3D000: No database selected
188
select * from mysqltest.t1,test.t1;
192
select a from mysqltest.t1;
196
select a from mysqltest.t1;
200
show status like "Qcache_queries_in_cache";
202
Qcache_queries_in_cache 8
203
show status like "Qcache_hits";
206
show status like "Qcache_not_cached";
209
----- close connections -----
210
----- switch to connection default -----
212
delete from mysql.user where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
213
delete from mysql.db where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
214
delete from mysql.tables_priv where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
215
delete from mysql.columns_priv where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
217
drop table test.t1,mysqltest.t1,mysqltest.t2;
218
drop database mysqltest;
219
set GLOBAL query_cache_size=default;