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
10
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
12
Grants for root@localhost
13
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
14
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
15
create database if not exists mysqltest;
16
create table mysqltest.t1 (a int,b int,c int);
17
create table mysqltest.t2 (a int,b int,c int);
18
insert into mysqltest.t1 values (1,1,1),(2,2,2);
19
insert into mysqltest.t2 values (3,3,3);
20
create table test.t1 (a char (10));
21
insert into test.t1 values ("test.t1");
25
----- establish connection root2 -----
41
select * from mysqltest.t1,test.t1;
45
show status like "Qcache_queries_in_cache";
47
Qcache_queries_in_cache 6
48
show status like "Qcache_hits%";
51
grant SELECT on mysqltest.* to mysqltest_1@localhost;
52
grant SELECT on mysqltest.t1 to mysqltest_2@localhost;
53
grant SELECT on test.t1 to mysqltest_2@localhost;
54
grant SELECT(a) on mysqltest.t1 to mysqltest_3@localhost;
55
----- establish connection user1 (user=mysqltest_1) -----
56
show grants for current_user();
57
Grants for mysqltest_1@localhost
58
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
59
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
60
show status like "Qcache_queries_in_cache";
62
Qcache_queries_in_cache 6
63
show status like "Qcache_hits";
66
show status like "Qcache_not_cached";
72
show status like "Qcache_queries_in_cache";
74
Qcache_queries_in_cache 6
75
show status like "Qcache_hits";
78
show status like "Qcache_not_cached";
85
show status like "Qcache_queries_in_cache";
87
Qcache_queries_in_cache 6
88
show status like "Qcache_hits";
91
show status like "Qcache_not_cached";
98
show status like "Qcache_queries_in_cache";
100
Qcache_queries_in_cache 6
101
show status like "Qcache_hits";
104
show status like "Qcache_not_cached";
111
show status like "Qcache_queries_in_cache";
113
Qcache_queries_in_cache 6
114
show status like "Qcache_hits";
117
show status like "Qcache_not_cached";
120
----- establish connection unkuser (user=unkuser) -----
121
show grants for current_user();
122
Grants for @localhost
123
GRANT USAGE ON *.* TO ''@'localhost'
124
----- establish connection user2 (user=mysqltest_2) -----
140
select * from mysqltest.t1,test.t1;
145
ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table 't2'
146
show status like "Qcache_queries_in_cache";
148
Qcache_queries_in_cache 6
149
show status like "Qcache_hits";
152
show status like "Qcache_not_cached";
155
----- establish connection user3 (user=mysqltest_3) -----
160
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 't1'
166
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for column 'c' in table 't1'
168
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 't2'
169
select mysqltest.t1.c from test.t1,mysqltest.t1;
170
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for column 'c' in table 't1'
171
show status like "Qcache_queries_in_cache";
173
Qcache_queries_in_cache 6
174
show status like "Qcache_hits";
177
show status like "Qcache_not_cached";
180
----- establish connection user4 (user=mysqltest_1) -----
185
Grants for mysqltest_1@localhost
186
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
187
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
189
ERROR 3D000: No database selected
190
select * from mysqltest.t1,test.t1;
194
select a from mysqltest.t1;
198
select a from mysqltest.t1;
202
show status like "Qcache_queries_in_cache";
204
Qcache_queries_in_cache 8
205
show status like "Qcache_hits";
208
show status like "Qcache_not_cached";
211
----- close connections -----
212
----- switch to connection default -----
214
delete from mysql.user where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
215
delete from mysql.db where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
216
delete from mysql.tables_priv where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
217
delete from mysql.columns_priv where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
219
drop table test.t1,mysqltest.t1,mysqltest.t2;
220
drop database mysqltest;
221
set GLOBAL query_cache_size=default;