| blob | 281468ee2e1e79d25fb1be77e55829e07dcbcfb2 |
1 drop table if exists test.t1,mysqltest.t1,mysqltest.t2;
2 drop database if exists mysqltest;
3 set GLOBAL query_cache_size=1355776;
4 reset query cache;
5 flush status;
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 show grants;
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");
20 select * from t1;
21 a
22 test.t1
23 ----- establish connection root2 -----
24 select * from t1;
25 a b c
26 1 1 1
27 2 2 2
28 select a from t1;
29 a
30 1
31 2
32 select c from t1;
33 c
34 1
35 2
36 select * from t2;
37 a b c
38 3 3 3
39 select * from mysqltest.t1,test.t1;
40 a b c a
41 1 1 1 test.t1
42 2 2 2 test.t1
43 show status like "Qcache_queries_in_cache";
44 Variable_name Value
45 Qcache_queries_in_cache 6
46 show status like "Qcache_hits%";
47 Variable_name Value
48 Qcache_hits 0
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";
59 Variable_name Value
60 Qcache_queries_in_cache 6
61 show status like "Qcache_hits";
62 Variable_name Value
63 Qcache_hits 0
64 show status like "Qcache_not_cached";
65 Variable_name Value
66 Qcache_not_cached 0
67 select "user1";
68 user1
69 user1
70 show status like "Qcache_queries_in_cache";
71 Variable_name Value
72 Qcache_queries_in_cache 6
73 show status like "Qcache_hits";
74 Variable_name Value
75 Qcache_hits 0
76 show status like "Qcache_not_cached";
77 Variable_name Value
78 Qcache_not_cached 1
79 select * from t1;
80 a b c
81 1 1 1
82 2 2 2
83 show status like "Qcache_queries_in_cache";
84 Variable_name Value
85 Qcache_queries_in_cache 6
86 show status like "Qcache_hits";
87 Variable_name Value
88 Qcache_hits 1
89 show status like "Qcache_not_cached";
90 Variable_name Value
91 Qcache_not_cached 1
92 select a from t1 ;
93 a
94 1
95 2
96 show status like "Qcache_queries_in_cache";
97 Variable_name Value
98 Qcache_queries_in_cache 6
99 show status like "Qcache_hits";
100 Variable_name Value
101 Qcache_hits 2
102 show status like "Qcache_not_cached";
103 Variable_name Value
104 Qcache_not_cached 1
105 select c from t1;
106 c
107 1
108 2
109 show status like "Qcache_queries_in_cache";
110 Variable_name Value
111 Qcache_queries_in_cache 6
112 show status like "Qcache_hits";
113 Variable_name Value
114 Qcache_hits 3
115 show status like "Qcache_not_cached";
116 Variable_name Value
117 Qcache_not_cached 1
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) -----
123 select "user2";
124 user2
125 user2
126 select * from t1;
127 a b c
128 1 1 1
129 2 2 2
130 select a from t1;
131 a
132 1
133 2
134 select c from t1;
135 c
136 1
137 2
138 select * from mysqltest.t1,test.t1;
139 a b c a
140 1 1 1 test.t1
141 2 2 2 test.t1
142 select * from t2;
143 ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table 't2'
144 show status like "Qcache_queries_in_cache";
145 Variable_name Value
146 Qcache_queries_in_cache 6
147 show status like "Qcache_hits";
148 Variable_name Value
149 Qcache_hits 7
150 show status like "Qcache_not_cached";
151 Variable_name Value
152 Qcache_not_cached 2
153 ----- establish connection user3 (user=mysqltest_3) -----
154 select "user3";
155 user3
156 user3
157 select * from t1;
158 ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 't1'
159 select a from t1;
160 a
161 1
162 2
163 select c from t1;
164 ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for column 'c' in table 't1'
165 select * from t2;
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";
170 Variable_name Value
171 Qcache_queries_in_cache 6
172 show status like "Qcache_hits";
173 Variable_name Value
174 Qcache_hits 7
175 show status like "Qcache_not_cached";
176 Variable_name Value
177 Qcache_not_cached 4
178 ----- establish connection user4 (user=mysqltest_1) -----
179 select "user4";
180 user4
181 user4
182 show grants;
183 Grants for mysqltest_1@localhost
184 GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
185 GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
186 select a from t1;
187 ERROR 3D000: No database selected
188 select * from mysqltest.t1,test.t1;
189 a b c a
190 1 1 1 test.t1
191 2 2 2 test.t1
192 select a from mysqltest.t1;
193 a
194 1
195 2
196 select a from mysqltest.t1;
197 a
198 1
199 2
200 show status like "Qcache_queries_in_cache";
201 Variable_name Value
202 Qcache_queries_in_cache 8
203 show status like "Qcache_hits";
204 Variable_name Value
205 Qcache_hits 8
206 show status like "Qcache_not_cached";
207 Variable_name Value
208 Qcache_not_cached 5
209 ----- close connections -----
210 ----- switch to connection default -----
211 set names binary;
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");
216 flush privileges;
217 drop table test.t1,mysqltest.t1,mysqltest.t2;
218 drop database mysqltest;
219 set GLOBAL query_cache_size=default;