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;