1
1
# Can't test with embedded server
2
2
-- source include/not_embedded.inc
4
# Save the initial number of concurrent sessions
5
--source include/count_sessions.inc
5
8
drop database if exists mysqltest;
6
9
drop view if exists v1,v2,v3;
32
35
connect (user1,localhost,mysqltest_1,,test);
38
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
35
39
create definer=root@localhost view v1 as select * from mysqltest.t1;
36
40
create view v1 as select * from mysqltest.t1;
37
41
# try to modify view without DROP privilege on it
42
--error ER_TABLEACCESS_DENIED_ERROR
38
43
alter view v1 as select * from mysqltest.t1;
44
--error ER_TABLEACCESS_DENIED_ERROR
39
45
create or replace view v1 as select * from mysqltest.t1;
40
46
# no CRETE VIEW privilege
47
--error ER_TABLEACCESS_DENIED_ERROR
41
48
create view mysqltest.v2 as select * from mysqltest.t1;
42
49
# no SELECT privilege
50
--error ER_TABLEACCESS_DENIED_ERROR
43
51
create view v2 as select * from mysqltest.t2;
54
57
grant create view,drop,select on test.* to mysqltest_1@localhost;
57
# following 'use' command is workaround of bug #9582 and should be removed
60
# following 'use' command is workaround of Bug#9582 and should be removed
58
61
# when that bug will be fixed
60
63
alter view v1 as select * from mysqltest.t1;
117
120
--disable_warnings
118
121
create database mysqltest;
119
122
--enable_warnings
120
#prepare views and tables
123
# prepare views and tables
121
124
create table mysqltest.t1 (a int, b int);
122
125
create table mysqltest.t2 (a int, b int);
123
126
create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1;
139
142
show columns from mysqltest.v1;
140
143
show columns from mysqltest.v2;
141
144
# but explain/show do not
145
--error ER_VIEW_NO_EXPLAIN
142
146
explain select c from mysqltest.v1;
147
--error ER_TABLEACCESS_DENIED_ERROR
143
148
show create view mysqltest.v1;
149
--error ER_VIEW_NO_EXPLAIN
144
150
explain select c from mysqltest.v2;
151
--error ER_TABLEACCESS_DENIED_ERROR
145
152
show create view mysqltest.v2;
153
--error ER_VIEW_NO_EXPLAIN
146
154
explain select c from mysqltest.v3;
155
--error ER_TABLEACCESS_DENIED_ERROR
147
156
show create view mysqltest.v3;
157
--error ER_VIEW_NO_EXPLAIN
148
158
explain select c from mysqltest.v4;
159
--error ER_TABLEACCESS_DENIED_ERROR
149
160
show create view mysqltest.v4;
151
162
# allow to see one of underlying table
162
165
connection user1;
163
166
# EXPLAIN of view on above table works
164
167
explain select c from mysqltest.v1;
168
--error ER_TABLEACCESS_DENIED_ERROR
165
169
show create view mysqltest.v1;
166
170
explain select c from mysqltest.v2;
171
--error ER_TABLEACCESS_DENIED_ERROR
167
172
show create view mysqltest.v2;
168
173
# but other EXPLAINs do not
174
--error ER_VIEW_NO_EXPLAIN
169
175
explain select c from mysqltest.v3;
176
--error ER_TABLEACCESS_DENIED_ERROR
170
177
show create view mysqltest.v3;
178
--error ER_VIEW_NO_EXPLAIN
171
179
explain select c from mysqltest.v4;
180
--error ER_TABLEACCESS_DENIED_ERROR
172
181
show create view mysqltest.v4;
174
183
# allow to see any view in mysqltest database
228
231
update v2 set a=a+c;
229
232
select * from t1;
230
233
# no rights on column
234
--error ER_COLUMNACCESS_DENIED_ERROR
231
235
update t2,v2 set v2.c=v2.a+v2.c where t2.x=v2.c;
236
--error ER_COLUMNACCESS_DENIED_ERROR
232
237
update v2 set c=a+c;
233
238
# no rights for view
239
--error ER_TABLEACCESS_DENIED_ERROR
234
240
update t2,v3 set v3.a=v3.a+v3.c where t2.x=v3.c;
241
--error ER_TABLEACCESS_DENIED_ERROR
235
242
update v3 set a=a+c;
269
272
delete v1 from t2,v1 where t2.x=v1.c;
270
273
select * from t1;
271
274
# no rights for view
275
--error ER_TABLEACCESS_DENIED_ERROR
272
276
delete v2 from t2,v2 where t2.x=v2.c;
277
--error ER_TABLEACCESS_DENIED_ERROR
273
278
delete from v2 where c < 4;
305
308
insert into v1 select x,y from t2;
306
309
select * from t1;
307
310
# no rights for view
311
--error ER_TABLEACCESS_DENIED_ERROR
308
312
insert into v2 values (5,6);
313
--error ER_TABLEACCESS_DENIED_ERROR
309
314
insert into v2 select x,y from t2;
335
338
create view v1 as select * from mysqltest.t1;
336
339
create view v2 as select b from mysqltest.t2;
337
340
# There are not rights on mysqltest.v1
341
--error ER_TABLEACCESS_DENIED_ERROR
338
342
create view mysqltest.v1 as select * from mysqltest.t1;
339
343
# There are not any rights on mysqltest.t2.a
344
--error ER_COLUMNACCESS_DENIED_ERROR
340
345
create view v3 as select a from mysqltest.t2;
342
347
# give CREATE VIEW privileges (without any privileges for result column)
360
363
# Expression need select privileges
364
--error ER_COLUMNACCESS_DENIED_ERROR
361
365
create view v4 as select b+1 from mysqltest.t2;
364
368
grant create view,update,select on test.* to mysqltest_1@localhost;
365
369
connection user1;
370
--error ER_COLUMNACCESS_DENIED_ERROR
366
371
create view v4 as select b+1 from mysqltest.t2;
723
727
GRANT DELETE ON mysqltest1.v_td TO readonly@localhost;
724
728
GRANT DELETE,SELECT ON mysqltest1.v_tds TO readonly@localhost;
726
CONNECT (n1,localhost,readonly,,);
730
connect (n1,localhost,readonly,,);
733
--error ER_VIEW_INVALID
730
734
SELECT * FROM mysqltest1.v_t1;
735
--error ER_VIEW_INVALID
732
736
INSERT INTO mysqltest1.v_t1 VALUES(4);
737
--error ER_VIEW_INVALID
734
738
DELETE FROM mysqltest1.v_t1 WHERE x = 1;
739
--error ER_VIEW_INVALID
736
740
UPDATE mysqltest1.v_t1 SET x = 3 WHERE x = 2;
741
--error ER_VIEW_INVALID
738
742
UPDATE mysqltest1.v_t1 SET x = 3;
743
--error ER_VIEW_INVALID
740
744
DELETE FROM mysqltest1.v_t1;
745
--error ER_VIEW_INVALID
742
746
SELECT 1 FROM mysqltest1.v_t1;
747
--error ER_TABLEACCESS_DENIED_ERROR
744
748
SELECT * FROM mysqltest1.t1;
746
750
SELECT * FROM mysqltest1.v_ts;
751
--error ER_TABLEACCESS_DENIED_ERROR
748
752
SELECT * FROM mysqltest1.v_ts, mysqltest1.t1 WHERE mysqltest1.t1.x = mysqltest1.v_ts.x;
753
--error ER_TABLEACCESS_DENIED_ERROR
750
754
SELECT * FROM mysqltest1.v_ti;
756
--error ER_TABLEACCESS_DENIED_ERROR
753
757
INSERT INTO mysqltest1.v_ts VALUES (100);
754
758
INSERT INTO mysqltest1.v_ti VALUES (100);
760
--error ER_TABLEACCESS_DENIED_ERROR
757
761
UPDATE mysqltest1.v_ts SET x= 200 WHERE x = 100;
762
--error ER_TABLEACCESS_DENIED_ERROR
759
763
UPDATE mysqltest1.v_ts SET x= 200;
760
764
UPDATE mysqltest1.v_tu SET x= 200 WHERE x = 100;
761
765
UPDATE mysqltest1.v_tus SET x= 200 WHERE x = 100;
762
766
UPDATE mysqltest1.v_tu SET x= 200;
768
--error ER_TABLEACCESS_DENIED_ERROR
765
769
DELETE FROM mysqltest1.v_ts WHERE x= 200;
770
--error ER_TABLEACCESS_DENIED_ERROR
767
771
DELETE FROM mysqltest1.v_ts;
772
--error ER_COLUMNACCESS_DENIED_ERROR
769
773
DELETE FROM mysqltest1.v_td WHERE x= 200;
770
774
DELETE FROM mysqltest1.v_tds WHERE x= 200;
771
775
DELETE FROM mysqltest1.v_td;
774
779
DROP VIEW mysqltest1.v_tds;
775
780
DROP VIEW mysqltest1.v_td;
776
781
DROP VIEW mysqltest1.v_tus;
783
788
DROP DATABASE mysqltest1;
786
# BUG#14875: Bad view DEFINER makes SHOW CREATE VIEW fail
791
# Bug#14875 Bad view DEFINER makes SHOW CREATE VIEW fail
788
793
CREATE TABLE t1 (a INT PRIMARY KEY);
789
794
INSERT INTO t1 VALUES (1), (2), (3);
790
795
CREATE DEFINER = 'no-such-user'@localhost VIEW v AS SELECT a from t1;
796
#--warning ER_VIEW_OTHER_USER
792
797
SHOW CREATE VIEW v;
798
--error ER_NO_SUCH_USER
800
# Bug#20363: Create view on just created view is now denied
805
# Bug#20363 Create view on just created view is now denied
802
807
eval CREATE USER mysqltest_db1@localhost identified by 'PWD';
803
808
eval GRANT ALL ON mysqltest_db1.* TO mysqltest_db1@localhost WITH GRANT OPTION;
846
852
CREATE TABLE test1.t0 (a VARCHAR(20));
847
853
CREATE TABLE test2.t1 (a VARCHAR(20));
848
854
CREATE VIEW test2.t3 AS SELECT * FROM test1.t0;
849
CREATE OR REPLACE VIEW test.v1 AS
855
CREATE OR REPLACE VIEW test.v1 AS
850
856
SELECT ta.a AS col1, tb.a AS col2 FROM test2.t3 ta, test2.t1 tb;
852
858
DROP VIEW test.v1;
1194
disconnect bug24040_con;
1189
1196
DROP DATABASE mysqltest1;
1190
1197
DROP DATABASE mysqltest2;
1191
1198
DROP USER mysqltest_u1@localhost;
1202
# Bug#41354 Access control is bypassed when all columns of a view are
1203
# selected by * wildcard
1205
CREATE DATABASE db1;
1207
CREATE TABLE t1(f1 INT, f2 INT);
1208
CREATE VIEW v1 AS SELECT f1, f2 FROM t1;
1210
GRANT SELECT (f1) ON t1 TO foo;
1211
GRANT SELECT (f1) ON v1 TO foo;
1213
connect (addconfoo, localhost, foo,,);
1214
connection addconfoo;
1218
--error ER_COLUMNACCESS_DENIED_ERROR
1220
--error ER_TABLEACCESS_DENIED_ERROR
1224
--error ER_COLUMNACCESS_DENIED_ERROR
1226
--error ER_TABLEACCESS_DENIED_ERROR
1231
disconnect addconfoo;
1233
REVOKE SELECT (f1) ON db1.t1 FROM foo;
1234
REVOKE SELECT (f1) ON db1.v1 FROM foo;
1193
1240
--echo End of 5.0 tests.
1268
# Bug#37191: Failed assertion in CREATE VIEW
1270
CREATE USER mysqluser1@localhost;
1271
CREATE DATABASE mysqltest1;
1275
CREATE TABLE t1 ( a INT );
1276
CREATE TABLE t2 ( b INT );
1278
INSERT INTO t1 VALUES (1), (2);
1279
INSERT INTO t2 VALUES (1), (2);
1281
GRANT CREATE VIEW ON mysqltest1.* TO mysqluser1@localhost;
1283
GRANT SELECT ON t1 TO mysqluser1@localhost;
1284
GRANT INSERT ON t2 TO mysqluser1@localhost;
1286
--connect (connection1, localhost, mysqluser1, , mysqltest1)
1288
--echo This would lead to failed assertion.
1289
CREATE VIEW v1 AS SELECT a, b FROM t1, t2;
1291
--error ER_TABLEACCESS_DENIED_ERROR
1293
--error ER_TABLEACCESS_DENIED_ERROR
1296
--disconnect connection1
1297
--connection default
1301
DROP DATABASE mysqltest1;
1302
DROP USER mysqluser1@localhost;
1221
1305
--echo End of 5.1 tests.
1254
1338
DROP USER mysqluser1@localhost;
1257
# Bug#35600: Security breach via view, I_S table and prepared
1258
# statement/stored procedure
1341
# Bug#35600 Security breach via view, I_S table and prepared
1342
# statement/stored procedure
1260
1344
CREATE USER mysqluser1@localhost;
1261
1345
CREATE DATABASE mysqltest1;