1 # this test mostly test privilege control (what doesn't work
2 # in the embedded server by default). So disabled in embedded-server mode
3 -- source include/not_embedded.inc
5 -- source include/testdb_only.inc
8 drop table if exists t1,t2;
9 drop view if exists v1,v2;
10 drop function if exists f1;
11 drop function if exists f2;
14 use INFORMATION_SCHEMA;
15 --replace_result Tables_in_INFORMATION_SCHEMA Tables_in_information_schema
17 --replace_result 'Tables_in_INFORMATION_SCHEMA (T%)' 'Tables_in_information_schema (T%)'
18 show tables from INFORMATION_SCHEMA like 'T%';
19 create database `inf%`;
20 create database mbase;
25 # Bug#18113 SELECT * FROM information_schema.xxx crashes server
26 # Bug#17204 second CALL to procedure crashes Server
27 # Crash happened when one selected data from one of INFORMATION_SCHEMA
28 # tables and in order to build its contents server had to open view which
29 # used stored function and table or view on which one had not global or
30 # database-level privileges (e.g. had only table-level or had no
33 grant all privileges on `inf%`.* to 'mysqltest_1'@'localhost';
34 grant all privileges on `mbase`.* to 'mysqltest_1'@'localhost';
35 create table t1 (f1 int);
37 create function func1(curr_int int) returns int
40 select max(f1) from t1 into ret_val;
44 create view v1 as select f1 from t1 where f1 = func1(f1);
45 create function func2() returns int return 1;
49 create procedure p1 ()
51 select table_name from information_schema.key_column_usage
57 (f1 int(10) unsigned not null,
58 f2 varchar(100) not null,
59 primary key (f1), unique key (f2));
61 connect (user1,localhost,mysqltest_1,,);
64 select * from information_schema.tables;
72 drop user mysqltest_1@localhost;
74 select table_name, table_type, table_comment from information_schema.tables
75 where table_schema='inf%' and func2();
76 select table_name, table_type, table_comment from information_schema.tables
77 where table_schema='inf%' and func2();
83 drop procedure mbase.p1;
88 # Bug#18282 INFORMATION_SCHEMA.TABLES provides inconsistent info about invalid views
91 create table t1 (i int);
92 create function f1 () returns int return (select max(i) from t1);
93 create view v1 as select f1();
94 create table t2 (id int);
95 create function f2 () returns int return (select max(i) from t2);
96 create view v2 as select f2();
98 select table_name, table_type, table_comment from information_schema.tables
99 where table_schema='test';
101 select table_name, table_type, table_comment from information_schema.tables
102 where table_schema='test';
108 # Bug#20543 select on information_schema strange warnings, view, different
112 create database testdb_1;
113 create user testdb_1@localhost;
114 grant all on testdb_1.* to testdb_1@localhost with grant option;
116 create user testdb_2@localhost;
117 grant all on test.* to testdb_2@localhost with grant option;
119 connect (testdb_1,localhost,testdb_1,,test);
121 create table t1 (f1 char(4));
122 create view v1 as select f1 from t1;
123 grant insert on v1 to testdb_2@localhost;
125 create view v5 as select f1 from t1;
126 grant show view on v5 to testdb_2@localhost;
128 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
129 create definer=`no_such_user`@`no_such_host` view v6 as select f1 from t1;
133 create view v6 as select f1 from t1;
134 grant show view on v6 to testdb_2@localhost;
136 create table t2 (f1 char(4));
137 create definer=`no_such_user`@`no_such_host` view v7 as select * from t2;
139 show fields from testdb_1.v6;
140 show create view testdb_1.v6;
142 show create view testdb_1.v7;
143 show fields from testdb_1.v7;
147 create table t3 (f1 char(4), f2 char(4));
148 create view v3 as select f1,f2 from t3;
149 grant insert(f1), insert(f2) on v3 to testdb_2@localhost;
151 connect (testdb_2,localhost,testdb_2,,test);
152 create view v2 as select f1 from testdb_1.v1;
153 create view v4 as select f1,f2 from testdb_1.v3;
155 show fields from testdb_1.v5;
156 show create view testdb_1.v5;
158 show fields from testdb_1.v6;
159 show create view testdb_1.v6;
162 show fields from testdb_1.v7;
163 show create view testdb_1.v7;
165 revoke insert(f1) on v3 from testdb_2@localhost;
166 revoke show view on v5 from testdb_2@localhost;
169 revoke show view on v6 from testdb_2@localhost;
172 --error ER_TABLEACCESS_DENIED_ERROR
173 show fields from testdb_1.v5;
174 --error ER_TABLEACCESS_DENIED_ERROR
175 show create view testdb_1.v5;
177 --error ER_TABLEACCESS_DENIED_ERROR
178 show fields from testdb_1.v6;
179 --error ER_TABLEACCESS_DENIED_ERROR
180 show create view testdb_1.v6;
182 --error ER_TABLEACCESS_DENIED_ERROR
183 show fields from testdb_1.v7;
184 --error ER_TABLEACCESS_DENIED_ERROR
185 show create view testdb_1.v7;
188 #--error ER_VIEW_NO_EXPLAIN
192 show fields from testdb_1.v1;
194 --error ER_TABLEACCESS_DENIED_ERROR
195 show create view testdb_1.v1;
197 select table_name from information_schema.columns a
198 where a.table_name = 'v2';
199 select view_definition from information_schema.views a
200 where a.table_name = 'v2';
201 select view_definition from information_schema.views a
202 where a.table_name = 'testdb_1.v1';
204 --error ER_VIEW_INVALID
209 drop view testdb_1.v1, v2, testdb_1.v3, v4;
210 drop database testdb_1;
213 --source include/wait_until_disconnected.inc
216 --source include/wait_until_disconnected.inc
218 drop user testdb_1@localhost;
219 drop user testdb_2@localhost;
222 # Bug#22763 Disrepancy between SHOW CREATE VIEW and I_S.VIEWS
224 create database testdb_1;
225 create table testdb_1.t1 (a int);
226 create view testdb_1.v1 as select * from testdb_1.t1;
228 grant show view on testdb_1.* to mysqltest_1@localhost;
229 grant select on testdb_1.v1 to mysqltest_1@localhost;
231 connect (user1,localhost,mysqltest_1,,test);
233 select table_schema, table_name, view_definition from information_schema.views
234 where table_name='v1';
235 show create view testdb_1.v1;
238 revoke select on testdb_1.v1 from mysqltest_1@localhost;
240 select table_schema, table_name, view_definition from information_schema.views
241 where table_name='v1';
242 --error ER_TABLEACCESS_DENIED_ERROR
243 show create view testdb_1.v1;
246 drop user mysqltest_1@localhost;
247 drop database testdb_1;
250 --source include/wait_until_disconnected.inc