117
117
create table t1 (f1 char(4));
118
118
create view v1 as select f1 from t1;
119
119
grant insert on v1 to testdb_2@localhost;
120
create view v5 as select f1 from t1;
121
grant show view on v5 to testdb_2@localhost;
122
create definer=`no_such_user`@`no_such_host` view v6 as select f1 from t1;
123
ERROR 42000: Access denied; you need the SUPER privilege for this operation
125
create view v6 as select f1 from t1;
126
grant show view on v6 to testdb_2@localhost;
127
create table t2 (f1 char(4));
128
create definer=`no_such_user`@`no_such_host` view v7 as select * from t2;
130
Note 1449 There is no 'no_such_user'@'no_such_host' registered
131
show fields from testdb_1.v6;
132
Field Type Null Key Default Extra
134
show create view testdb_1.v6;
136
v6 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v6` AS select `t1`.`f1` AS `f1` from `t1`
137
show create view testdb_1.v7;
139
v7 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such_user`@`no_such_host` SQL SECURITY DEFINER VIEW `v7` AS select `testdb_1`.`t2`.`f1` AS `f1` from `t2`
141
Warning 1356 View 'testdb_1.v7' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
142
show fields from testdb_1.v7;
143
Field Type Null Key Default Extra
146
Note 1449 There is no 'no_such_user'@'no_such_host' registered
120
147
create table t3 (f1 char(4), f2 char(4));
121
148
create view v3 as select f1,f2 from t3;
122
149
grant insert(f1), insert(f2) on v3 to testdb_2@localhost;
123
150
create view v2 as select f1 from testdb_1.v1;
124
151
create view v4 as select f1,f2 from testdb_1.v3;
152
show fields from testdb_1.v5;
153
Field Type Null Key Default Extra
155
show create view testdb_1.v5;
157
v5 CREATE ALGORITHM=UNDEFINED DEFINER=`testdb_1`@`localhost` SQL SECURITY DEFINER VIEW `testdb_1`.`v5` AS select `testdb_1`.`t1`.`f1` AS `f1` from `testdb_1`.`t1`
158
show fields from testdb_1.v6;
159
Field Type Null Key Default Extra
161
show create view testdb_1.v6;
163
v6 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `testdb_1`.`v6` AS select `testdb_1`.`t1`.`f1` AS `f1` from `testdb_1`.`t1`
164
show fields from testdb_1.v7;
165
Field Type Null Key Default Extra
168
Note 1449 There is no 'no_such_user'@'no_such_host' registered
169
show create view testdb_1.v7;
171
v7 CREATE ALGORITHM=UNDEFINED DEFINER=`no_such_user`@`no_such_host` SQL SECURITY DEFINER VIEW `v7` AS select `testdb_1`.`t2`.`f1` AS `f1` from `t2`
173
Warning 1356 View 'testdb_1.v7' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
125
174
revoke insert(f1) on v3 from testdb_2@localhost;
175
revoke show view on v5 from testdb_2@localhost;
177
revoke show view on v6 from testdb_2@localhost;
178
show fields from testdb_1.v5;
179
ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v5'
180
show create view testdb_1.v5;
181
ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v5'
182
show fields from testdb_1.v6;
183
ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v6'
184
show create view testdb_1.v6;
185
ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v6'
186
show fields from testdb_1.v7;
187
ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v7'
188
show create view testdb_1.v7;
189
ERROR 42000: SELECT command denied to user 'testdb_2'@'localhost' for table 'v7'
126
190
show create view v4;
127
191
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
128
192
show fields from v4;
129
ERROR HY000: EXPLAIN/SHOW can not be issued; lacking privileges for underlying table
193
Field Type Null Key Default Extra
130
196
show fields from v2;
131
197
Field Type Null Key Default Extra
132
198
f1 char(4) YES NULL