Debian Codegraph @

mysql-test/suite/innodb/t/information_schema_grants.test

raw
1 source include/have_innodb.inc;
2 source include/not_embedded.inc;
3
4 # make sure we've enabled everything:
5 select plugin_name,plugin_status as 'Must be ACTIVE' from information_schema.plugins where plugin_name like 'inno%' and plugin_status!='ACTIVE';
6
7 create user select_only@localhost;
8 grant select on *.* to select_only@localhost;
9
10 connect select_only,localhost,select_only;
11 connection default;
12
13 create sql security invoker view i_buffer_page as select * from information_schema.innodb_buffer_page;
14 create sql security definer view d_buffer_page as select * from information_schema.innodb_buffer_page;
15
16 create sql security invoker view i_buffer_page_lru as select * from information_schema.innodb_buffer_page_lru;
17 create sql security definer view d_buffer_page_lru as select * from information_schema.innodb_buffer_page_lru;
18
19 create sql security invoker view i_buffer_pool_stats as select * from information_schema.innodb_buffer_pool_stats;
20 create sql security definer view d_buffer_pool_stats as select * from information_schema.innodb_buffer_pool_stats;
21
22 create sql security invoker view i_cmp as select * from information_schema.innodb_cmp;
23 create sql security definer view d_cmp as select * from information_schema.innodb_cmp;
24
25 create sql security invoker view i_cmp_per_index as select * from information_schema.innodb_cmp_per_index;
26 create sql security definer view d_cmp_per_index as select * from information_schema.innodb_cmp_per_index;
27
28 create sql security invoker view i_cmp_per_index_reset as select * from information_schema.innodb_cmp_per_index_reset;
29 create sql security definer view d_cmp_per_index_reset as select * from information_schema.innodb_cmp_per_index_reset;
30
31 create sql security invoker view i_cmp_reset as select * from information_schema.innodb_cmp_reset;
32 create sql security definer view d_cmp_reset as select * from information_schema.innodb_cmp_reset;
33
34 create sql security invoker view i_cmpmem as select * from information_schema.innodb_cmpmem;
35 create sql security definer view d_cmpmem as select * from information_schema.innodb_cmpmem;
36
37 create sql security invoker view i_cmpmem_reset as select * from information_schema.innodb_cmpmem_reset;
38 create sql security definer view d_cmpmem_reset as select * from information_schema.innodb_cmpmem_reset;
39
40 create sql security invoker view i_ft_being_deleted as select * from information_schema.innodb_ft_being_deleted;
41 create sql security definer view d_ft_being_deleted as select * from information_schema.innodb_ft_being_deleted;
42
43 create sql security invoker view i_ft_config as select * from information_schema.innodb_ft_config;
44 create sql security definer view d_ft_config as select * from information_schema.innodb_ft_config;
45
46 create sql security invoker view i_ft_default_stopword as select * from information_schema.innodb_ft_default_stopword;
47 create sql security definer view d_ft_default_stopword as select * from information_schema.innodb_ft_default_stopword;
48
49 create sql security invoker view i_ft_deleted as select * from information_schema.innodb_ft_deleted;
50 create sql security definer view d_ft_deleted as select * from information_schema.innodb_ft_deleted;
51
52 create sql security invoker view i_ft_index_cache as select * from information_schema.innodb_ft_index_cache;
53 create sql security definer view d_ft_index_cache as select * from information_schema.innodb_ft_index_cache;
54
55 create sql security invoker view i_ft_index_table as select * from information_schema.innodb_ft_index_table;
56 create sql security definer view d_ft_index_table as select * from information_schema.innodb_ft_index_table;
57
58 create sql security invoker view i_lock_waits as select * from information_schema.innodb_lock_waits;
59 create sql security definer view d_lock_waits as select * from information_schema.innodb_lock_waits;
60
61 create sql security invoker view i_locks as select * from information_schema.innodb_locks;
62 create sql security definer view d_locks as select * from information_schema.innodb_locks;
63
64 create sql security invoker view i_metrics as select * from information_schema.innodb_metrics;
65 create sql security definer view d_metrics as select * from information_schema.innodb_metrics;
66
67 create sql security invoker view i_sys_columns as select * from information_schema.innodb_sys_columns;
68 create sql security definer view d_sys_columns as select * from information_schema.innodb_sys_columns;
69
70 create sql security invoker view i_sys_fields as select * from information_schema.innodb_sys_fields;
71 create sql security definer view d_sys_fields as select * from information_schema.innodb_sys_fields;
72
73 create sql security invoker view i_sys_foreign as select * from information_schema.innodb_sys_foreign;
74 create sql security definer view d_sys_foreign as select * from information_schema.innodb_sys_foreign;
75
76 create sql security invoker view i_sys_foreign_cols as select * from information_schema.innodb_sys_foreign_cols;
77 create sql security definer view d_sys_foreign_cols as select * from information_schema.innodb_sys_foreign_cols;
78
79 create sql security invoker view i_sys_indexes as select * from information_schema.innodb_sys_indexes;
80 create sql security definer view d_sys_indexes as select * from information_schema.innodb_sys_indexes;
81
82 create sql security invoker view i_sys_tables as select * from information_schema.innodb_sys_tables;
83 create sql security definer view d_sys_tables as select * from information_schema.innodb_sys_tables;
84
85 create sql security invoker view i_sys_tablespaces as select * from information_schema.innodb_sys_tablespaces;
86 create sql security definer view d_sys_tablespaces as select * from information_schema.innodb_sys_tablespaces;
87
88 create sql security invoker view i_sys_tablestats as select * from information_schema.innodb_sys_tablestats;
89 create sql security definer view d_sys_tablestats as select * from information_schema.innodb_sys_tablestats;
90
91 create sql security invoker view i_sys_virtual as select * from information_schema.innodb_sys_virtual;
92 create sql security definer view d_sys_virtual as select * from information_schema.innodb_sys_virtual;
93
94 create sql security invoker view i_tablespaces_encryption as select * from information_schema.innodb_tablespaces_encryption;
95 create sql security definer view d_tablespaces_encryption as select * from information_schema.innodb_tablespaces_encryption;
96
97 create sql security invoker view i_trx as select * from information_schema.innodb_trx;
98 create sql security definer view d_trx as select * from information_schema.innodb_trx;
99
100 connection select_only;
101 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
102 select count(*) > -1 from information_schema.innodb_buffer_page;
103 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
104 select count(*) > -1 from i_buffer_page;
105 select count(*) > -1 from d_buffer_page;
106
107 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
108 select count(*) > -1 from information_schema.innodb_buffer_page_lru;
109 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
110 select count(*) > -1 from i_buffer_page_lru;
111 select count(*) > -1 from d_buffer_page_lru;
112
113 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
114 select count(*) > -1 from information_schema.innodb_buffer_pool_stats;
115 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
116 select count(*) > -1 from i_buffer_pool_stats;
117 select count(*) > -1 from d_buffer_pool_stats;
118
119 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
120 select count(*) > -1 from information_schema.innodb_cmp;
121 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
122 select count(*) > -1 from i_cmp;
123 select count(*) > -1 from d_cmp;
124
125 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
126 select count(*) > -1 from information_schema.innodb_cmp_per_index;
127 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
128 select count(*) > -1 from i_cmp_per_index;
129 select count(*) > -1 from d_cmp_per_index;
130
131 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
132 select count(*) > -1 from information_schema.innodb_cmp_per_index_reset;
133 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
134 select count(*) > -1 from i_cmp_per_index_reset;
135 select count(*) > -1 from d_cmp_per_index_reset;
136
137 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
138 select count(*) > -1 from information_schema.innodb_cmp_reset;
139 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
140 select count(*) > -1 from i_cmp_reset;
141 select count(*) > -1 from d_cmp_reset;
142
143 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
144 select count(*) > -1 from information_schema.innodb_cmpmem;
145 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
146 select count(*) > -1 from i_cmpmem;
147 select count(*) > -1 from d_cmpmem;
148
149 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
150 select count(*) > -1 from information_schema.innodb_cmpmem_reset;
151 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
152 select count(*) > -1 from i_cmpmem_reset;
153 select count(*) > -1 from d_cmpmem_reset;
154
155 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
156 select count(*) > -1 from information_schema.innodb_ft_being_deleted;
157 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
158 select count(*) > -1 from i_ft_being_deleted;
159 select count(*) > -1 from d_ft_being_deleted;
160
161 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
162 select count(*) > -1 from information_schema.innodb_ft_config;
163 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
164 select count(*) > -1 from i_ft_config;
165 select count(*) > -1 from d_ft_config;
166
167 # non-privileged table
168 select count(*) > -1 from information_schema.innodb_ft_default_stopword;
169
170 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
171 select count(*) > -1 from information_schema.innodb_ft_deleted;
172 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
173 select count(*) > -1 from i_ft_deleted;
174 select count(*) > -1 from d_ft_deleted;
175
176 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
177 select count(*) > -1 from information_schema.innodb_ft_index_cache;
178 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
179 select count(*) > -1 from i_ft_index_cache;
180 select count(*) > -1 from d_ft_index_cache;
181
182 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
183 select count(*) > -1 from information_schema.innodb_ft_index_table;
184 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
185 select count(*) > -1 from i_ft_index_table;
186 select count(*) > -1 from d_ft_index_table;
187
188 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
189 select count(*) > -1 from information_schema.innodb_lock_waits;
190 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
191 select count(*) > -1 from i_lock_waits;
192 select count(*) > -1 from d_lock_waits;
193
194 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
195 select count(*) > -1 from information_schema.innodb_locks;
196 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
197 select count(*) > -1 from i_locks;
198 select count(*) > -1 from d_locks;
199
200 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
201 select count(*) > -1 from information_schema.innodb_metrics;
202 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
203 select count(*) > -1 from i_metrics;
204 select count(*) > -1 from d_metrics;
205
206 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
207 select count(*) > -1 from information_schema.innodb_sys_columns;
208 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
209 select count(*) > -1 from i_sys_columns;
210 select count(*) > -1 from d_sys_columns;
211
212 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
213 select count(*) > -1 from information_schema.innodb_sys_fields;
214 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
215 select count(*) > -1 from i_sys_fields;
216 select count(*) > -1 from d_sys_fields;
217
218 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
219 select count(*) > -1 from information_schema.innodb_sys_foreign;
220 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
221 select count(*) > -1 from i_sys_foreign;
222 select count(*) > -1 from d_sys_foreign;
223
224 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
225 select count(*) > -1 from information_schema.innodb_sys_foreign_cols;
226 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
227 select count(*) > -1 from i_sys_foreign_cols;
228 select count(*) > -1 from d_sys_foreign_cols;
229
230 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
231 select count(*) > -1 from information_schema.innodb_sys_indexes;
232 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
233 select count(*) > -1 from i_sys_indexes;
234 select count(*) > -1 from d_sys_indexes;
235
236 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
237 select count(*) > -1 from information_schema.innodb_sys_tables;
238 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
239 select count(*) > -1 from i_sys_tables;
240 select count(*) > -1 from d_sys_tables;
241
242 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
243 select count(*) > -1 from information_schema.innodb_sys_tablespaces;
244 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
245 select count(*) > -1 from i_sys_tablespaces;
246 select count(*) > -1 from d_sys_tablespaces;
247
248 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
249 select count(*) > -1 from information_schema.innodb_sys_tablestats;
250 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
251 select count(*) > -1 from i_sys_tablestats;
252 select count(*) > -1 from d_sys_tablestats;
253
254 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
255 select count(*) > -1 from information_schema.innodb_sys_virtual;
256 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
257 select count(*) > -1 from i_sys_virtual;
258 select count(*) > -1 from d_sys_virtual;
259
260 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
261 select count(*) > -1 from information_schema.innodb_tablespaces_encryption;
262 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
263 select count(*) > -1 from i_tablespaces_encryption;
264 select count(*) > -1 from d_tablespaces_encryption;
265
266 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
267 select count(*) > -1 from information_schema.innodb_trx;
268 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
269 select count(*) > -1 from i_trx;
270 select count(*) > -1 from d_trx;
271
272 connection default;
273 # Starting with MariaDB 10.6, ensure that DDL recovery will have completed.
274 SET GLOBAL innodb_max_purge_lag_wait=0;
275 drop database test;
276 create database test;
277 drop user select_only@localhost;