grant_cache.result 5.01 KB
Newer Older
1
drop table if exists test.t1,mysqltest.t1,mysqltest.t2;
2
drop database if exists mysqltest;
3 4
reset query cache;
flush status;
5 6 7 8 9 10
show grants for current_user;
Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
show grants;
Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
create database if not exists mysqltest;
create table mysqltest.t1 (a int,b int,c int);
create table mysqltest.t2 (a int,b int,c int);
insert into mysqltest.t1 values (1,1,1),(2,2,2);
insert into mysqltest.t2 values (3,3,3);
create table test.t1 (a char (10));
insert into test.t1 values ("test.t1");
select * from t1;
a
test.t1
select * from t1;
a	b	c
1	1	1
2	2	2
select a from t1;
a
1
2
select c from t1;
c
1
2
select * from t2;
a	b	c
3	3	3
select * from mysqltest.t1,test.t1;
a	b	c	a
1	1	1	test.t1
2	2	2	test.t1
show status like "Qcache_queries_in_cache";
Variable_name	Value
Qcache_queries_in_cache	6
show status like "Qcache_hits%";
Variable_name	Value
Qcache_hits	0
grant SELECT on mysqltest.* to mysqltest_1@localhost;
grant SELECT on mysqltest.t1 to mysqltest_2@localhost;
grant SELECT on test.t1 to mysqltest_2@localhost;
grant SELECT(a) on mysqltest.t1 to mysqltest_3@localhost;
50 51 52 53
show grants for current_user();
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
54 55 56 57 58 59 60 61 62
show status like "Qcache_queries_in_cache";
Variable_name	Value
Qcache_queries_in_cache	6
show status like "Qcache_hits";
Variable_name	Value
Qcache_hits	0
show status like "Qcache_not_cached";
Variable_name	Value
Qcache_not_cached	0
63 64 65
select "user1";
user1
user1
66 67 68 69 70 71 72 73 74
show status like "Qcache_queries_in_cache";
Variable_name	Value
Qcache_queries_in_cache	6
show status like "Qcache_hits";
Variable_name	Value
Qcache_hits	0
show status like "Qcache_not_cached";
Variable_name	Value
Qcache_not_cached	1
75 76 77 78
select * from t1;
a	b	c
1	1	1
2	2	2
79 80 81 82 83 84 85 86 87
show status like "Qcache_queries_in_cache";
Variable_name	Value
Qcache_queries_in_cache	6
show status like "Qcache_hits";
Variable_name	Value
Qcache_hits	1
show status like "Qcache_not_cached";
Variable_name	Value
Qcache_not_cached	1
88 89 90 91
select a from t1 ;
a
1
2
92 93 94 95 96 97 98 99 100
show status like "Qcache_queries_in_cache";
Variable_name	Value
Qcache_queries_in_cache	6
show status like "Qcache_hits";
Variable_name	Value
Qcache_hits	2
show status like "Qcache_not_cached";
Variable_name	Value
Qcache_not_cached	1
101 102 103 104 105 106 107 108 109 110 111 112 113
select c from t1;
c
1
2
show status like "Qcache_queries_in_cache";
Variable_name	Value
Qcache_queries_in_cache	6
show status like "Qcache_hits";
Variable_name	Value
Qcache_hits	3
show status like "Qcache_not_cached";
Variable_name	Value
Qcache_not_cached	1
114 115 116
show grants for current_user();
Grants for @localhost
GRANT USAGE ON *.* TO ''@'localhost'
117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136
select "user2";
user2
user2
select * from t1;
a	b	c
1	1	1
2	2	2
select a from t1;
a
1
2
select c from t1;
c
1
2
select * from mysqltest.t1,test.t1;
a	b	c	a
1	1	1	test.t1
2	2	2	test.t1
select * from t2;
unknown's avatar
unknown committed
137
ERROR 42000: SELECT command denied to user 'mysqltest_2'@'localhost' for table 't2'
138 139 140 141 142 143 144 145
show status like "Qcache_queries_in_cache";
Variable_name	Value
Qcache_queries_in_cache	6
show status like "Qcache_hits";
Variable_name	Value
Qcache_hits	7
show status like "Qcache_not_cached";
Variable_name	Value
146
Qcache_not_cached	2
147 148 149 150
select "user3";
user3
user3
select * from t1;
unknown's avatar
unknown committed
151
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for column 'b' in table 't1'
152 153 154 155 156
select a from t1;
a
1
2
select c from t1;
157
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for column 'c' in table 't1'
158
select * from t2;
unknown's avatar
unknown committed
159
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 't2'
160
select mysqltest.t1.c from test.t1,mysqltest.t1;
161
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for column 'c' in table 't1'
162 163 164 165 166 167 168 169
show status like "Qcache_queries_in_cache";
Variable_name	Value
Qcache_queries_in_cache	6
show status like "Qcache_hits";
Variable_name	Value
Qcache_hits	7
show status like "Qcache_not_cached";
Variable_name	Value
170
Qcache_not_cached	7
171 172 173
select "user4";
user4
user4
174 175 176 177
show grants;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
178
select a from t1;
179
ERROR 3D000: No database selected
180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199
select * from mysqltest.t1,test.t1;
a	b	c	a
1	1	1	test.t1
2	2	2	test.t1
select a from mysqltest.t1;
a
1
2
select a from mysqltest.t1;
a
1
2
show status like "Qcache_queries_in_cache";
Variable_name	Value
Qcache_queries_in_cache	8
show status like "Qcache_hits";
Variable_name	Value
Qcache_hits	8
show status like "Qcache_not_cached";
Variable_name	Value
200
Qcache_not_cached	8
201
set names binary;
202 203 204 205 206 207 208
delete from mysql.user where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
delete from mysql.db where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
delete from mysql.tables_priv where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
delete from mysql.columns_priv where user in ("mysqltest_1","mysqltest_2","mysqltest_3");
flush privileges;
drop table test.t1,mysqltest.t1,mysqltest.t2;
drop database mysqltest;