1
# Can't test with embedded server
2
-- source include/not_embedded.inc
4
# Save the initial number of concurrent sessions
5
--source include/count_sessions.inc
8
drop database if exists mysqltest;
9
drop view if exists v1,v2,v3;
13
# simple test of grants
14
grant create view on test.* to test@localhost;
15
show grants for test@localhost;
16
revoke create view on test.* from test@localhost;
17
show grants for test@localhost;
18
# The grant above creates a new user test@localhost, delete it
19
drop user test@localhost;
21
# grant create view test
23
connect (root,localhost,root,,test);
26
create database mysqltest;
29
create table mysqltest.t1 (a int, b int);
30
create table mysqltest.t2 (a int, b int);
32
grant select on mysqltest.t1 to mysqltest_1@localhost;
33
grant create view,select on test.* to mysqltest_1@localhost;
35
connect (user1,localhost,mysqltest_1,,test);
38
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
39
create definer=root@localhost view v1 as select * from mysqltest.t1;
40
create view v1 as select * from mysqltest.t1;
41
# try to modify view without DROP privilege on it
42
--error ER_TABLEACCESS_DENIED_ERROR
43
alter view v1 as select * from mysqltest.t1;
44
--error ER_TABLEACCESS_DENIED_ERROR
45
create or replace view v1 as select * from mysqltest.t1;
46
# no CRETE VIEW privilege
47
--error ER_TABLEACCESS_DENIED_ERROR
48
create view mysqltest.v2 as select * from mysqltest.t1;
50
--error ER_TABLEACCESS_DENIED_ERROR
51
create view v2 as select * from mysqltest.t2;
54
# check view definer information
57
grant create view,drop,select on test.* to mysqltest_1@localhost;
60
# following 'use' command is workaround of Bug#9582 and should be removed
61
# when that bug will be fixed
63
alter view v1 as select * from mysqltest.t1;
64
create or replace view v1 as select * from mysqltest.t1;
67
revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
68
revoke all privileges on test.* from mysqltest_1@localhost;
70
drop database mysqltest;
78
create database mysqltest;
81
create table mysqltest.t1 (a int, b int);
82
create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1;
83
grant select (c) on mysqltest.v1 to mysqltest_1@localhost;
86
select c from mysqltest.v1;
87
# there are no privileges on column 'd'
88
--error ER_COLUMNACCESS_DENIED_ERROR
89
select d from mysqltest.v1;
92
revoke all privileges on mysqltest.v1 from mysqltest_1@localhost;
93
delete from mysql.user where user='mysqltest_1';
94
drop database mysqltest;
96
# TEMPORARY TABLE algorithm
98
create database mysqltest;
101
create table mysqltest.t1 (a int, b int);
102
create algorithm=temptable view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1;
103
grant select (c) on mysqltest.v1 to mysqltest_1@localhost;
106
select c from mysqltest.v1;
107
# there are no privileges on column 'd'
108
--error ER_COLUMNACCESS_DENIED_ERROR
109
select d from mysqltest.v1;
112
revoke all privileges on mysqltest.v1 from mysqltest_1@localhost;
113
delete from mysql.user where user='mysqltest_1';
114
drop database mysqltest;
121
create database mysqltest;
123
# prepare views and tables
124
create table mysqltest.t1 (a int, b int);
125
create table mysqltest.t2 (a int, b int);
126
create view mysqltest.v1 (c,d) as select a+1,b+1 from mysqltest.t1;
127
create algorithm=temptable view mysqltest.v2 (c,d) as select a+1,b+1 from mysqltest.t1;
128
create view mysqltest.v3 (c,d) as select a+1,b+1 from mysqltest.t2;
129
create algorithm=temptable view mysqltest.v4 (c,d) as select a+1,b+1 from mysqltest.t2;
130
# v5: SHOW VIEW, but no SELECT
131
create view mysqltest.v5 (c,d) as select a+1,b+1 from mysqltest.t1;
132
grant select on mysqltest.v1 to mysqltest_1@localhost;
133
grant select on mysqltest.v2 to mysqltest_1@localhost;
134
grant select on mysqltest.v3 to mysqltest_1@localhost;
135
grant select on mysqltest.v4 to mysqltest_1@localhost;
136
grant show view on mysqltest.v5 to mysqltest_1@localhost;
139
# all SELECTs works, except v5 which lacks SELECT privs
140
select c from mysqltest.v1;
141
select c from mysqltest.v2;
142
select c from mysqltest.v3;
143
select c from mysqltest.v4;
144
--error ER_TABLEACCESS_DENIED_ERROR
145
select c from mysqltest.v5;
146
# test of show coluns
147
show columns from mysqltest.v1;
148
show columns from mysqltest.v2;
150
--error ER_VIEW_NO_EXPLAIN
151
explain select c from mysqltest.v1;
152
--error ER_TABLEACCESS_DENIED_ERROR
153
show create view mysqltest.v1;
154
--error ER_VIEW_NO_EXPLAIN
155
explain select c from mysqltest.v2;
156
--error ER_TABLEACCESS_DENIED_ERROR
157
show create view mysqltest.v2;
158
--error ER_VIEW_NO_EXPLAIN
159
explain select c from mysqltest.v3;
160
--error ER_TABLEACCESS_DENIED_ERROR
161
show create view mysqltest.v3;
162
--error ER_VIEW_NO_EXPLAIN
163
explain select c from mysqltest.v4;
164
--error ER_TABLEACCESS_DENIED_ERROR
165
show create view mysqltest.v4;
166
--error ER_TABLEACCESS_DENIED_ERROR
167
explain select c from mysqltest.v5;
168
# new in 5.5: SHOW CREATE VIEW needs SELECT now (MySQL Bug#27145)
169
--error ER_TABLEACCESS_DENIED_ERROR
170
show create view mysqltest.v5;
172
grant select on mysqltest.v5 to mysqltest_1@localhost;
174
show create view mysqltest.v5;
176
# missing SELECT on underlying t1, no SHOW VIEW on v1 either.
177
--error ER_VIEW_NO_EXPLAIN
178
explain select c from mysqltest.v1;
180
--error ER_TABLEACCESS_DENIED_ERROR
181
show create view mysqltest.v1;
182
# allow to see one of underlying table
184
grant show view on mysqltest.v1 to mysqltest_1@localhost;
185
grant select on mysqltest.t1 to mysqltest_1@localhost;
186
revoke select on mysqltest.v5 from mysqltest_1@localhost;
189
explain select c from mysqltest.v1;
190
show create view mysqltest.v1;
192
--error ER_VIEW_NO_EXPLAIN
193
explain select c from mysqltest.v2;
194
--error ER_TABLEACCESS_DENIED_ERROR
195
show create view mysqltest.v2;
196
# but other EXPLAINs do not
197
--error ER_VIEW_NO_EXPLAIN
198
explain select c from mysqltest.v3;
199
--error ER_TABLEACCESS_DENIED_ERROR
200
show create view mysqltest.v3;
201
--error ER_VIEW_NO_EXPLAIN
202
explain select c from mysqltest.v4;
203
--error ER_TABLEACCESS_DENIED_ERROR
204
show create view mysqltest.v4;
205
# we have SHOW VIEW on v5, and SELECT on t1 -- not enough
206
--error ER_TABLEACCESS_DENIED_ERROR
207
explain select c from mysqltest.v5;
209
# allow to see any view in mysqltest database
211
grant show view on mysqltest.* to mysqltest_1@localhost;
213
explain select c from mysqltest.v1;
214
show create view mysqltest.v1;
215
explain select c from mysqltest.v2;
216
show create view mysqltest.v2;
217
# have SHOW VIEW | SELECT on v3, but no SELECT on t2
218
--error ER_VIEW_NO_EXPLAIN
219
explain select c from mysqltest.v3;
220
show create view mysqltest.v3;
221
# have SHOW VIEW | SELECT on v4, but no SELECT on t2
222
--error ER_VIEW_NO_EXPLAIN
223
explain select c from mysqltest.v4;
224
show create view mysqltest.v4;
227
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
228
delete from mysql.user where user='mysqltest_1';
229
drop database mysqltest;
232
# UPDATE privileges on VIEW columns and whole VIEW
236
create database mysqltest;
239
create table mysqltest.t1 (a int, b int, primary key(a));
240
insert into mysqltest.t1 values (10,2), (20,3), (30,4), (40,5), (50,10);
241
create table mysqltest.t2 (x int);
242
insert into mysqltest.t2 values (3), (4), (5), (6);
243
create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1;
244
create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1;
245
create view mysqltest.v3 (a,c) as select a, b+1 from mysqltest.t1;
247
grant update (a) on mysqltest.v2 to mysqltest_1@localhost;
248
grant update on mysqltest.v1 to mysqltest_1@localhost;
249
grant select on mysqltest.* to mysqltest_1@localhost;
253
# update with rights on VIEW column
254
update t2,v1 set v1.a=v1.a+v1.c where t2.x=v1.c;
258
# update with rights on whole VIEW
259
update t2,v2 set v2.a=v2.a+v2.c where t2.x=v2.c;
263
# no rights on column
264
--error ER_COLUMNACCESS_DENIED_ERROR
265
update t2,v2 set v2.c=v2.a+v2.c where t2.x=v2.c;
266
--error ER_COLUMNACCESS_DENIED_ERROR
269
--error ER_TABLEACCESS_DENIED_ERROR
270
update t2,v3 set v3.a=v3.a+v3.c where t2.x=v3.c;
271
--error ER_TABLEACCESS_DENIED_ERROR
276
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
277
drop database mysqltest;
280
# DELETE privileges on VIEW
284
create database mysqltest;
287
create table mysqltest.t1 (a int, b int, primary key(a));
288
insert into mysqltest.t1 values (1,2), (2,3), (3,4), (4,5), (5,10);
289
create table mysqltest.t2 (x int);
290
insert into mysqltest.t2 values (3), (4), (5), (6);
291
create view mysqltest.v1 (a,c) as select a, b+1 from mysqltest.t1;
292
create view mysqltest.v2 (a,c) as select a, b+1 from mysqltest.t1;
294
grant delete on mysqltest.v1 to mysqltest_1@localhost;
295
grant select on mysqltest.* to mysqltest_1@localhost;
299
# update with rights on VIEW column
300
delete from v1 where c < 4;
302
delete v1 from t2,v1 where t2.x=v1.c;
305
--error ER_TABLEACCESS_DENIED_ERROR
306
delete v2 from t2,v2 where t2.x=v2.c;
307
--error ER_TABLEACCESS_DENIED_ERROR
308
delete from v2 where c < 4;
312
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
313
drop database mysqltest;
316
# insert privileges on VIEW
320
create database mysqltest;
323
create table mysqltest.t1 (a int, b int, primary key(a));
324
insert into mysqltest.t1 values (1,2), (2,3);
325
create table mysqltest.t2 (x int, y int);
326
insert into mysqltest.t2 values (3,4);
327
create view mysqltest.v1 (a,c) as select a, b from mysqltest.t1;
328
create view mysqltest.v2 (a,c) as select a, b from mysqltest.t1;
330
grant insert on mysqltest.v1 to mysqltest_1@localhost;
331
grant select on mysqltest.* to mysqltest_1@localhost;
335
# update with rights on VIEW column
336
insert into v1 values (5,6);
338
insert into v1 select x,y from t2;
341
--error ER_TABLEACCESS_DENIED_ERROR
342
insert into v2 values (5,6);
343
--error ER_TABLEACCESS_DENIED_ERROR
344
insert into v2 select x,y from t2;
348
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
349
drop database mysqltest;
352
# test of CREATE VIEW privileges if we have limited privileges
356
create database mysqltest;
359
create table mysqltest.t1 (a int, b int);
360
create table mysqltest.t2 (a int, b int);
362
grant update on mysqltest.t1 to mysqltest_1@localhost;
363
grant update(b) on mysqltest.t2 to mysqltest_1@localhost;
364
grant create view,update on test.* to mysqltest_1@localhost;
368
create view v1 as select * from mysqltest.t1;
369
create view v2 as select b from mysqltest.t2;
370
# There are not rights on mysqltest.v1
371
--error ER_TABLEACCESS_DENIED_ERROR
372
create view mysqltest.v1 as select * from mysqltest.t1;
373
# There are not any rights on mysqltest.t2.a
374
--error ER_COLUMNACCESS_DENIED_ERROR
375
create view v3 as select a from mysqltest.t2;
377
# give CREATE VIEW privileges (without any privileges for result column)
379
create table mysqltest.v3 (b int);
380
grant create view on mysqltest.v3 to mysqltest_1@localhost;
381
drop table mysqltest.v3;
383
create view mysqltest.v3 as select b from mysqltest.t2;
385
# give UPDATE privileges
387
grant create view, update on mysqltest.v3 to mysqltest_1@localhost;
388
drop view mysqltest.v3;
390
create view mysqltest.v3 as select b from mysqltest.t2;
393
# Expression need select privileges
394
--error ER_COLUMNACCESS_DENIED_ERROR
395
create view v4 as select b+1 from mysqltest.t2;
398
grant create view,update,select on test.* to mysqltest_1@localhost;
400
--error ER_COLUMNACCESS_DENIED_ERROR
401
create view v4 as select b+1 from mysqltest.t2;
404
grant update,select(b) on mysqltest.t2 to mysqltest_1@localhost;
406
create view v4 as select b+1 from mysqltest.t2;
409
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
410
drop database mysqltest;
414
# user with global DB privileges
418
create database mysqltest;
420
create table mysqltest.t1 (a int);
421
grant all privileges on mysqltest.* to mysqltest_1@localhost;
425
create view v1 as select * from t1;
429
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
430
drop database mysqltest;
433
# view definer grants revoking
437
create database mysqltest;
440
create table mysqltest.t1 (a int, b int);
442
grant select on mysqltest.t1 to mysqltest_1@localhost;
443
grant create view,select on test.* to mysqltest_1@localhost;
447
create view v1 as select * from mysqltest.t1;
450
# check view definer information
452
revoke select on mysqltest.t1 from mysqltest_1@localhost;
453
--error ER_VIEW_INVALID
455
grant select on mysqltest.t1 to mysqltest_1@localhost;
457
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
459
drop database mysqltest;
462
# rights on execution of view underlying functiond (Bug#9505)
466
create database mysqltest;
470
create table t1 (a int);
471
insert into t1 values (1);
472
create table t2 (s1 int);
474
drop function if exists f2;
477
create function f2 () returns int begin declare v int; select s1 from t2
478
into v; return v; end//
480
create algorithm=TEMPTABLE view v1 as select f2() from t1;
481
create algorithm=MERGE view v2 as select f2() from t1;
482
create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1;
483
create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1;
484
create SQL SECURITY INVOKER view v5 as select * from v4;
485
grant select on v1 to mysqltest_1@localhost;
486
grant select on v2 to mysqltest_1@localhost;
487
grant select on v3 to mysqltest_1@localhost;
488
grant select on v4 to mysqltest_1@localhost;
489
grant select on v5 to mysqltest_1@localhost;
495
--error ER_VIEW_INVALID
497
--error ER_VIEW_INVALID
499
--error ER_VIEW_INVALID
504
drop view v1, v2, v3, v4, v5;
508
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
509
drop database mysqltest;
512
# revertion of previous test, definer of view lost his/her rights to execute
518
create database mysqltest;
522
create table t1 (a int);
523
insert into t1 values (1);
524
create table t2 (s1 int);
526
drop function if exists f2;
529
create function f2 () returns int begin declare v int; select s1 from t2
530
into v; return v; end//
532
grant select on t1 to mysqltest_1@localhost;
533
grant execute on function f2 to mysqltest_1@localhost;
534
grant create view on mysqltest.* to mysqltest_1@localhost;
538
create algorithm=TEMPTABLE view v1 as select f2() from t1;
539
create algorithm=MERGE view v2 as select f2() from t1;
540
create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1;
541
create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1;
545
create view v5 as select * from v1;
546
revoke execute on function f2 from mysqltest_1@localhost;
547
--error ER_VIEW_INVALID
549
--error ER_VIEW_INVALID
553
--error ER_VIEW_INVALID
556
drop view v1, v2, v3, v4, v5;
560
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
561
drop database mysqltest;
564
# definer/invoker rights for columns
568
create database mysqltest;
572
create table t1 (a int);
573
create table v1 (a int);
574
insert into t1 values (1);
575
grant select on t1 to mysqltest_1@localhost;
576
grant select on v1 to mysqltest_1@localhost;
577
grant create view on mysqltest.* to mysqltest_1@localhost;
582
create algorithm=TEMPTABLE view v1 as select *, a as b from t1;
583
create algorithm=MERGE view v2 as select *, a as b from t1;
584
create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1;
585
create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1;
586
create view v5 as select * from v1;
590
revoke select on t1 from mysqltest_1@localhost;
591
--error ER_VIEW_INVALID
593
--error ER_VIEW_INVALID
597
--error ER_VIEW_INVALID
600
#drop view v1, v2, v3, v4, v5;
603
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
604
drop database mysqltest;
609
create database mysqltest;
613
create table t1 (a int);
614
insert into t1 values (1);
615
create algorithm=TEMPTABLE view v1 as select *, a as b from t1;
616
create algorithm=MERGE view v2 as select *, a as b from t1;
617
create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1;
618
create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1;
619
create SQL SECURITY INVOKER view v5 as select * from v4;
620
grant select on v1 to mysqltest_1@localhost;
621
grant select on v2 to mysqltest_1@localhost;
622
grant select on v3 to mysqltest_1@localhost;
623
grant select on v4 to mysqltest_1@localhost;
624
grant select on v5 to mysqltest_1@localhost;
630
--error ER_VIEW_INVALID
632
--error ER_VIEW_INVALID
634
--error ER_VIEW_INVALID
639
drop view v1, v2, v3, v4, v5;
642
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
643
drop database mysqltest;
646
# Bug#14256 definer in view definition is not fully qualified
649
drop view if exists v1;
650
drop table if exists t1;
653
# Backup anonymous users and remove them. (They get in the way of
654
# the one we test with here otherwise.)
655
create table t1 as select * from mysql.user where user='';
656
delete from mysql.user where user='';
659
# Create the test user
660
grant all on test.* to 'test14256'@'%';
662
connect (test14256,localhost,test14256,,test);
663
connection test14256;
666
create view v1 as select 42;
669
select definer into @v1def1 from information_schema.views
670
where table_schema = 'test' and table_name='v1';
673
create definer=`test14256`@`%` view v1 as select 42;
676
select definer into @v1def2 from information_schema.views
677
where table_schema = 'test' and table_name='v1';
680
select @v1def1, @v1def2, @v1def1=@v1def2;
683
disconnect test14256;
686
# Restore the anonymous users.
687
insert into mysql.user select * from t1;
693
# Bug#14726 freeing stack variable in case of an error of opening a view when
694
# we have locked tables with LOCK TABLES statement.
698
create database mysqltest;
702
CREATE TABLE t1 (i INT);
703
CREATE VIEW v1 AS SELECT * FROM t1;
705
GRANT SELECT, LOCK TABLES ON mysqltest.* TO mysqltest_1@localhost;
711
--error ER_TABLEACCESS_DENIED_ERROR
712
SHOW CREATE TABLE v1;
718
drop user mysqltest_1@localhost;
719
drop database mysqltest;
722
# switch to default connection
729
# DEFINER information check
731
create definer=some_user@`` sql security invoker view v1 as select 1;
732
create definer=some_user@localhost sql security invoker view v2 as select 1;
739
# Bug#18681 View privileges are broken
741
CREATE DATABASE mysqltest1;
742
CREATE USER readonly@localhost;
743
CREATE TABLE mysqltest1.t1 (x INT);
744
INSERT INTO mysqltest1.t1 VALUES (1), (2);
745
CREATE SQL SECURITY INVOKER VIEW mysqltest1.v_t1 AS SELECT * FROM mysqltest1.t1;
746
CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_ts AS SELECT * FROM mysqltest1.t1;
747
CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_ti AS SELECT * FROM mysqltest1.t1;
748
CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tu AS SELECT * FROM mysqltest1.t1;
749
CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tus AS SELECT * FROM mysqltest1.t1;
750
CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_td AS SELECT * FROM mysqltest1.t1;
751
CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tds AS SELECT * FROM mysqltest1.t1;
752
GRANT SELECT, INSERT, UPDATE, DELETE ON mysqltest1.v_t1 TO readonly@localhost;
753
GRANT SELECT ON mysqltest1.v_ts TO readonly@localhost;
754
GRANT INSERT ON mysqltest1.v_ti TO readonly@localhost;
755
GRANT UPDATE ON mysqltest1.v_tu TO readonly@localhost;
756
GRANT UPDATE,SELECT ON mysqltest1.v_tus TO readonly@localhost;
757
GRANT DELETE ON mysqltest1.v_td TO readonly@localhost;
758
GRANT DELETE,SELECT ON mysqltest1.v_tds TO readonly@localhost;
760
connect (n1,localhost,readonly,,);
763
--error ER_VIEW_INVALID
764
SELECT * FROM mysqltest1.v_t1;
765
--error ER_VIEW_INVALID
766
INSERT INTO mysqltest1.v_t1 VALUES(4);
767
--error ER_VIEW_INVALID
768
DELETE FROM mysqltest1.v_t1 WHERE x = 1;
769
--error ER_VIEW_INVALID
770
UPDATE mysqltest1.v_t1 SET x = 3 WHERE x = 2;
771
--error ER_VIEW_INVALID
772
UPDATE mysqltest1.v_t1 SET x = 3;
773
--error ER_VIEW_INVALID
774
DELETE FROM mysqltest1.v_t1;
775
--error ER_VIEW_INVALID
776
SELECT 1 FROM mysqltest1.v_t1;
777
--error ER_TABLEACCESS_DENIED_ERROR
778
SELECT * FROM mysqltest1.t1;
780
SELECT * FROM mysqltest1.v_ts;
781
--error ER_TABLEACCESS_DENIED_ERROR
782
SELECT * FROM mysqltest1.v_ts, mysqltest1.t1 WHERE mysqltest1.t1.x = mysqltest1.v_ts.x;
783
--error ER_TABLEACCESS_DENIED_ERROR
784
SELECT * FROM mysqltest1.v_ti;
786
--error ER_TABLEACCESS_DENIED_ERROR
787
INSERT INTO mysqltest1.v_ts VALUES (100);
788
INSERT INTO mysqltest1.v_ti VALUES (100);
790
--error ER_TABLEACCESS_DENIED_ERROR
791
UPDATE mysqltest1.v_ts SET x= 200 WHERE x = 100;
792
--error ER_TABLEACCESS_DENIED_ERROR
793
UPDATE mysqltest1.v_ts SET x= 200;
794
UPDATE mysqltest1.v_tu SET x= 200 WHERE x = 100;
795
UPDATE mysqltest1.v_tus SET x= 200 WHERE x = 100;
796
UPDATE mysqltest1.v_tu SET x= 200;
798
--error ER_TABLEACCESS_DENIED_ERROR
799
DELETE FROM mysqltest1.v_ts WHERE x= 200;
800
--error ER_TABLEACCESS_DENIED_ERROR
801
DELETE FROM mysqltest1.v_ts;
802
--error ER_COLUMNACCESS_DENIED_ERROR
803
DELETE FROM mysqltest1.v_td WHERE x= 200;
804
DELETE FROM mysqltest1.v_tds WHERE x= 200;
805
DELETE FROM mysqltest1.v_td;
809
DROP VIEW mysqltest1.v_tds;
810
DROP VIEW mysqltest1.v_td;
811
DROP VIEW mysqltest1.v_tus;
812
DROP VIEW mysqltest1.v_tu;
813
DROP VIEW mysqltest1.v_ti;
814
DROP VIEW mysqltest1.v_ts;
815
DROP VIEW mysqltest1.v_t1;
816
DROP TABLE mysqltest1.t1;
817
DROP USER readonly@localhost;
818
DROP DATABASE mysqltest1;
821
# Bug#14875 Bad view DEFINER makes SHOW CREATE VIEW fail
823
CREATE TABLE t1 (a INT PRIMARY KEY);
824
INSERT INTO t1 VALUES (1), (2), (3);
825
CREATE DEFINER = 'no-such-user'@localhost VIEW v AS SELECT a from t1;
826
#--warning ER_VIEW_OTHER_USER
828
--error ER_NO_SUCH_USER
835
# Bug#20363 Create view on just created view is now denied
837
eval CREATE USER mysqltest_db1@localhost identified by 'PWD';
838
eval GRANT ALL ON mysqltest_db1.* TO mysqltest_db1@localhost WITH GRANT OPTION;
840
# The session with the non root user is needed.
841
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
842
connect (session1,localhost,mysqltest_db1,PWD,test);
844
CREATE SCHEMA mysqltest_db1 ;
847
CREATE TABLE t1 (f1 INTEGER);
851
SHOW CREATE VIEW view1;
855
--echo # Here comes a suspicious warning
856
SHOW CREATE VIEW view2;
857
--echo # But the view view2 is usable
867
DROP VIEW mysqltest_db1.view3;
868
DROP VIEW mysqltest_db1.view2;
869
DROP VIEW mysqltest_db1.view1;
870
DROP TABLE mysqltest_db1.t1;
871
DROP SCHEMA mysqltest_db1;
872
DROP USER mysqltest_db1@localhost;
874
# Bug#20482 failure on Create join view with sources views/tables
875
# in different schemas
878
CREATE DATABASE test1;
879
CREATE DATABASE test2;
882
CREATE TABLE test1.t0 (a VARCHAR(20));
883
CREATE TABLE test2.t1 (a VARCHAR(20));
884
CREATE VIEW test2.t3 AS SELECT * FROM test1.t0;
885
CREATE OR REPLACE VIEW test.v1 AS
886
SELECT ta.a AS col1, tb.a AS col2 FROM test2.t3 ta, test2.t1 tb;
890
DROP TABLE test2.t1, test1.t0;
896
# Bug#20570 CURRENT_USER() in a VIEW with SQL SECURITY DEFINER returns
900
DROP VIEW IF EXISTS v1;
901
DROP VIEW IF EXISTS v2;
902
DROP VIEW IF EXISTS v3;
903
DROP FUNCTION IF EXISTS f1;
904
DROP FUNCTION IF EXISTS f2;
905
DROP PROCEDURE IF EXISTS p1;
908
CREATE SQL SECURITY DEFINER VIEW v1 AS SELECT CURRENT_USER() AS cu;
910
CREATE FUNCTION f1() RETURNS VARCHAR(77) SQL SECURITY INVOKER
911
RETURN CURRENT_USER();
912
CREATE SQL SECURITY DEFINER VIEW v2 AS SELECT f1() AS cu;
914
CREATE PROCEDURE p1(OUT cu VARCHAR(77)) SQL SECURITY INVOKER
915
SET cu= CURRENT_USER();
917
CREATE FUNCTION f2() RETURNS VARCHAR(77) SQL SECURITY INVOKER
919
DECLARE cu VARCHAR(77);
924
CREATE SQL SECURITY DEFINER VIEW v3 AS SELECT f2() AS cu;
926
CREATE USER mysqltest_u1@localhost;
927
GRANT ALL ON test.* TO mysqltest_u1@localhost;
929
connect (conn1, localhost, mysqltest_u1,,);
932
--echo The following tests should all return 1.
934
SELECT CURRENT_USER() = 'mysqltest_u1@localhost';
935
SELECT f1() = 'mysqltest_u1@localhost';
937
SELECT @cu = 'mysqltest_u1@localhost';
938
SELECT f2() = 'mysqltest_u1@localhost';
939
SELECT cu = 'root@localhost' FROM v1;
940
SELECT cu = 'root@localhost' FROM v2;
941
SELECT cu = 'root@localhost' FROM v3;
952
DROP USER mysqltest_u1@localhost;
956
# Bug#17254 Error for DEFINER security on VIEW provides too much info
958
connect (root,localhost,root,,);
960
CREATE DATABASE db17254;
962
CREATE TABLE t1 (f1 INT);
963
INSERT INTO t1 VALUES (10),(20);
964
CREATE USER def_17254@localhost;
965
GRANT SELECT ON db17254.* TO def_17254@localhost;
966
CREATE USER inv_17254@localhost;
967
GRANT SELECT ON db17254.t1 TO inv_17254@localhost;
968
GRANT CREATE VIEW ON db17254.* TO def_17254@localhost;
970
connect (def,localhost,def_17254,,db17254);
972
CREATE VIEW v1 AS SELECT * FROM t1;
975
DROP USER def_17254@localhost;
977
connect (inv,localhost,inv_17254,,db17254);
980
--error ER_TABLEACCESS_DENIED_ERROR
984
--echo for a superuser
985
--error ER_NO_SUCH_USER
987
DROP USER inv_17254@localhost;
988
DROP DATABASE db17254;
994
# Bug#24404 strange bug with view+permission+prepared statement
997
DROP DATABASE IF EXISTS mysqltest_db1;
998
DROP DATABASE IF EXISTS mysqltest_db2;
1000
--error 0,ER_CANNOT_USER
1001
DROP USER mysqltest_u1;
1002
--error 0,ER_CANNOT_USER
1003
DROP USER mysqltest_u2;
1005
CREATE USER mysqltest_u1@localhost;
1006
CREATE USER mysqltest_u2@localhost;
1008
CREATE DATABASE mysqltest_db1;
1009
CREATE DATABASE mysqltest_db2;
1011
GRANT ALL ON mysqltest_db1.* TO mysqltest_u1@localhost WITH GRANT OPTION;
1012
GRANT ALL ON mysqltest_db2.* TO mysqltest_u2@localhost;
1014
connect (conn1, localhost, mysqltest_u1, , mysqltest_db1);
1016
CREATE TABLE t1 (i INT);
1017
INSERT INTO t1 VALUES (1);
1019
# Use view with subquery for better coverage.
1020
CREATE VIEW v1 AS SELECT i FROM t1 WHERE 1 IN (SELECT * FROM t1);
1022
CREATE TABLE t2 (s CHAR(7));
1023
INSERT INTO t2 VALUES ('public');
1025
GRANT SELECT ON v1 TO mysqltest_u2@localhost;
1026
GRANT SELECT ON t2 TO mysqltest_u2@localhost;
1028
connect (conn2, localhost, mysqltest_u2, , mysqltest_db2);
1030
SELECT * FROM mysqltest_db1.v1, mysqltest_db1.t2;
1031
PREPARE stmt1 FROM "SELECT * FROM mysqltest_db1.t2";
1033
PREPARE stmt2 FROM "SELECT * FROM mysqltest_db1.v1, mysqltest_db1.t2";
1037
# Make table 't2' private.
1038
REVOKE SELECT ON t2 FROM mysqltest_u2@localhost;
1039
UPDATE t2 SET s = 'private' WHERE s = 'public';
1042
--error ER_TABLEACCESS_DENIED_ERROR
1043
SELECT * FROM mysqltest_db1.v1, mysqltest_db1.t2;
1044
--error ER_TABLEACCESS_DENIED_ERROR
1046
# Original bug was here: the statement didn't fail.
1047
--error ER_TABLEACCESS_DENIED_ERROR
1054
REVOKE ALL ON mysqltest_db1.* FROM mysqltest_u1@localhost;
1055
REVOKE ALL ON mysqltest_db2.* FROM mysqltest_u2@localhost;
1056
DROP DATABASE mysqltest_db1;
1057
DROP DATABASE mysqltest_db2;
1058
DROP USER mysqltest_u1@localhost;
1059
DROP USER mysqltest_u2@localhost;
1062
# Bug#26813 The SUPER privilege is wrongly required to alter a view created
1066
CREATE DATABASE db26813;
1068
CREATE TABLE t1(f1 INT, f2 INT);
1069
CREATE VIEW v1 AS SELECT f1 FROM t1;
1070
CREATE VIEW v2 AS SELECT f1 FROM t1;
1071
CREATE VIEW v3 AS SELECT f1 FROM t1;
1072
CREATE USER u26813@localhost;
1073
GRANT DROP ON db26813.v1 TO u26813@localhost;
1074
GRANT CREATE VIEW ON db26813.v2 TO u26813@localhost;
1075
GRANT DROP, CREATE VIEW ON db26813.v3 TO u26813@localhost;
1076
GRANT SELECT ON db26813.t1 TO u26813@localhost;
1078
connect (u1,localhost,u26813,,db26813);
1080
--error ER_TABLEACCESS_DENIED_ERROR
1081
ALTER VIEW v1 AS SELECT f2 FROM t1;
1082
--error ER_TABLEACCESS_DENIED_ERROR
1083
ALTER VIEW v2 AS SELECT f2 FROM t1;
1084
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
1085
ALTER VIEW v3 AS SELECT f2 FROM t1;
1088
SHOW CREATE VIEW v3;
1090
DROP USER u26813@localhost;
1091
DROP DATABASE db26813;
1095
--echo # Bug#29908 A user can gain additional access through the ALTER VIEW.
1098
CREATE DATABASE mysqltest_29908;
1099
USE mysqltest_29908;
1100
CREATE TABLE t1(f1 INT, f2 INT);
1101
CREATE USER u29908_1@localhost;
1102
CREATE DEFINER = u29908_1@localhost VIEW v1 AS SELECT f1 FROM t1;
1103
CREATE DEFINER = u29908_1@localhost SQL SECURITY INVOKER VIEW v2 AS
1105
GRANT SELECT, DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v1 TO u29908_1@localhost;
1106
GRANT SELECT, DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_1@localhost;
1107
GRANT SELECT ON mysqltest_29908.t1 TO u29908_1@localhost;
1108
CREATE USER u29908_2@localhost;
1109
GRANT SELECT, DROP, CREATE VIEW ON mysqltest_29908.v1 TO u29908_2@localhost;
1110
GRANT SELECT, DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_2@localhost;
1111
GRANT SELECT ON mysqltest_29908.t1 TO u29908_2@localhost;
1113
connect (u2,localhost,u29908_2,,mysqltest_29908);
1114
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
1115
ALTER VIEW v1 AS SELECT f2 FROM t1;
1116
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
1117
ALTER VIEW v2 AS SELECT f2 FROM t1;
1118
SHOW CREATE VIEW v2;
1120
connect (u1,localhost,u29908_1,,mysqltest_29908);
1121
ALTER VIEW v1 AS SELECT f2 FROM t1;
1122
SHOW CREATE VIEW v1;
1123
ALTER VIEW v2 AS SELECT f2 FROM t1;
1124
SHOW CREATE VIEW v2;
1127
ALTER VIEW v1 AS SELECT f1 FROM t1;
1128
SHOW CREATE VIEW v1;
1129
ALTER VIEW v2 AS SELECT f1 FROM t1;
1130
SHOW CREATE VIEW v2;
1132
DROP USER u29908_1@localhost;
1133
DROP USER u29908_2@localhost;
1134
DROP DATABASE mysqltest_29908;
1137
--echo #######################################################################
1140
# Bug#24040 Create View don't succed with "all privileges" on a database.
1146
DROP DATABASE IF EXISTS mysqltest1;
1147
DROP DATABASE IF EXISTS mysqltest2;
1150
CREATE DATABASE mysqltest1;
1151
CREATE DATABASE mysqltest2;
1155
CREATE TABLE mysqltest1.t1(c1 INT);
1156
CREATE TABLE mysqltest1.t2(c2 INT);
1157
CREATE TABLE mysqltest1.t3(c3 INT);
1158
CREATE TABLE mysqltest1.t4(c4 INT);
1160
INSERT INTO mysqltest1.t1 VALUES (11), (12), (13), (14);
1161
INSERT INTO mysqltest1.t2 VALUES (21), (22), (23), (24);
1162
INSERT INTO mysqltest1.t3 VALUES (31), (32), (33), (34);
1163
INSERT INTO mysqltest1.t4 VALUES (41), (42), (43), (44);
1165
GRANT SELECT ON mysqltest1.t1 TO mysqltest_u1@localhost;
1166
GRANT INSERT ON mysqltest1.t2 TO mysqltest_u1@localhost;
1167
GRANT SELECT, UPDATE ON mysqltest1.t3 TO mysqltest_u1@localhost;
1168
GRANT SELECT, DELETE ON mysqltest1.t4 TO mysqltest_u1@localhost;
1170
GRANT ALL PRIVILEGES ON mysqltest2.* TO mysqltest_u1@localhost;
1172
--connect (bug24040_con,localhost,mysqltest_u1,,mysqltest2)
1174
--echo ---> connection: bug24040_con
1176
SELECT * FROM mysqltest1.t1;
1177
INSERT INTO mysqltest1.t2 VALUES(25);
1178
UPDATE mysqltest1.t3 SET c3 = 331 WHERE c3 = 31;
1179
DELETE FROM mysqltest1.t4 WHERE c4 = 44;
1181
CREATE VIEW v1 AS SELECT * FROM mysqltest1.t1;
1182
CREATE VIEW v2 AS SELECT * FROM mysqltest1.t2;
1183
CREATE VIEW v3 AS SELECT * FROM mysqltest1.t3;
1184
CREATE VIEW v4 AS SELECT * FROM mysqltest1.t4;
1187
INSERT INTO v2 VALUES(26);
1188
UPDATE v3 SET c3 = 332 WHERE c3 = 32;
1189
DELETE FROM v4 WHERE c4 = 43;
1191
--error ER_COLUMNACCESS_DENIED_ERROR
1192
CREATE VIEW v12 AS SELECT c1, c2 FROM mysqltest1.t1, mysqltest1.t2;
1193
CREATE VIEW v13 AS SELECT c1, c3 FROM mysqltest1.t1, mysqltest1.t3;
1194
CREATE VIEW v14 AS SELECT c1, c4 FROM mysqltest1.t1, mysqltest1.t4;
1196
--error ER_COLUMNACCESS_DENIED_ERROR
1197
CREATE VIEW v21 AS SELECT c2, c1 FROM mysqltest1.t2, mysqltest1.t1;
1198
--error ER_COLUMNACCESS_DENIED_ERROR
1199
CREATE VIEW v23 AS SELECT c2, c3 FROM mysqltest1.t2, mysqltest1.t3;
1200
--error ER_COLUMNACCESS_DENIED_ERROR
1201
CREATE VIEW v24 AS SELECT c2, c4 FROM mysqltest1.t2, mysqltest1.t4;
1203
CREATE VIEW v31 AS SELECT c3, c1 FROM mysqltest1.t3, mysqltest1.t1;
1204
--error ER_COLUMNACCESS_DENIED_ERROR
1205
CREATE VIEW v32 AS SELECT c3, c2 FROM mysqltest1.t3, mysqltest1.t2;
1206
CREATE VIEW v34 AS SELECT c3, c4 FROM mysqltest1.t3, mysqltest1.t4;
1208
CREATE VIEW v41 AS SELECT c4, c1 FROM mysqltest1.t4, mysqltest1.t1;
1209
--error ER_COLUMNACCESS_DENIED_ERROR
1210
CREATE VIEW v42 AS SELECT c4, c2 FROM mysqltest1.t4, mysqltest1.t2;
1211
CREATE VIEW v43 AS SELECT c4, c3 FROM mysqltest1.t4, mysqltest1.t3;
1213
--connection default
1215
--echo ---> connection: default
1217
SELECT * FROM mysqltest1.t1;
1218
SELECT * FROM mysqltest1.t2;
1219
SELECT * FROM mysqltest1.t3;
1220
SELECT * FROM mysqltest1.t4;
1224
disconnect bug24040_con;
1226
DROP DATABASE mysqltest1;
1227
DROP DATABASE mysqltest2;
1228
DROP USER mysqltest_u1@localhost;
1232
# Bug#41354 Access control is bypassed when all columns of a view are
1233
# selected by * wildcard
1235
CREATE DATABASE db1;
1237
CREATE TABLE t1(f1 INT, f2 INT);
1238
CREATE VIEW v1 AS SELECT f1, f2 FROM t1;
1240
GRANT SELECT (f1) ON t1 TO foo;
1241
GRANT SELECT (f1) ON v1 TO foo;
1243
connect (addconfoo, localhost, foo,,);
1244
connection addconfoo;
1248
--error ER_COLUMNACCESS_DENIED_ERROR
1250
--error ER_TABLEACCESS_DENIED_ERROR
1254
--error ER_COLUMNACCESS_DENIED_ERROR
1256
--error ER_TABLEACCESS_DENIED_ERROR
1261
disconnect addconfoo;
1263
REVOKE SELECT (f1) ON db1.t1 FROM foo;
1264
REVOKE SELECT (f1) ON db1.v1 FROM foo;
1273
--echo Bug #11765687/#58677:
1274
--echo No privilege on table/view, but can know #rows / underlying table's name
1276
# As a root-like user
1277
connect (root,localhost,root,,test);
1280
create database mysqltest1;
1281
create table mysqltest1.t1 (i int);
1282
create table mysqltest1.t2 (j int);
1283
create table mysqltest1.t3 (k int, secret int);
1285
create user alice@localhost;
1286
create user bob@localhost;
1287
create user cecil@localhost;
1288
create user dan@localhost;
1289
create user eugene@localhost;
1290
create user fiona@localhost;
1291
create user greg@localhost;
1292
create user han@localhost;
1293
create user inga@localhost;
1294
create user jamie@localhost;
1295
create user karl@localhost;
1296
create user lena@localhost;
1297
create user mhairi@localhost;
1298
create user noam@localhost;
1299
create user olga@localhost;
1300
create user pjotr@localhost;
1301
create user quintessa@localhost;
1303
grant all privileges on mysqltest1.* to alice@localhost with grant option;
1307
connect (test11765687,localhost,alice,,mysqltest1);
1308
connection test11765687;
1310
create view v1 as select * from t1;
1311
create view v2 as select * from v1, t2;
1312
create view v3 as select k from t3;
1314
grant select on mysqltest1.v1 to bob@localhost;
1316
grant show view on mysqltest1.v1 to cecil@localhost;
1318
grant select, show view on mysqltest1.v1 to dan@localhost;
1319
grant select on mysqltest1.t1 to dan@localhost;
1321
grant select on mysqltest1.* to eugene@localhost;
1323
grant select, show view on mysqltest1.v2 to fiona@localhost;
1325
grant select, show view on mysqltest1.v2 to greg@localhost;
1326
grant show view on mysqltest1.v1 to greg@localhost;
1328
grant select(k) on mysqltest1.t3 to han@localhost;
1329
grant select, show view on mysqltest1.v3 to han@localhost;
1331
grant select on mysqltest1.t1 to inga@localhost;
1332
grant select on mysqltest1.t2 to inga@localhost;
1333
grant select on mysqltest1.v1 to inga@localhost;
1334
grant select, show view on mysqltest1.v2 to inga@localhost;
1336
grant select on mysqltest1.t1 to jamie@localhost;
1337
grant select on mysqltest1.t2 to jamie@localhost;
1338
grant show view on mysqltest1.v1 to jamie@localhost;
1339
grant select, show view on mysqltest1.v2 to jamie@localhost;
1341
grant select on mysqltest1.t1 to karl@localhost;
1342
grant select on mysqltest1.t2 to karl@localhost;
1343
grant select, show view on mysqltest1.v1 to karl@localhost;
1344
grant select on mysqltest1.v2 to karl@localhost;
1346
grant select on mysqltest1.t1 to lena@localhost;
1347
grant select on mysqltest1.t2 to lena@localhost;
1348
grant select, show view on mysqltest1.v1 to lena@localhost;
1349
grant show view on mysqltest1.v2 to lena@localhost;
1351
grant select on mysqltest1.t1 to mhairi@localhost;
1352
grant select on mysqltest1.t2 to mhairi@localhost;
1353
grant select, show view on mysqltest1.v1 to mhairi@localhost;
1354
grant select, show view on mysqltest1.v2 to mhairi@localhost;
1356
grant select on mysqltest1.t1 to noam@localhost;
1357
grant select, show view on mysqltest1.v1 to noam@localhost;
1358
grant select, show view on mysqltest1.v2 to noam@localhost;
1360
grant select on mysqltest1.t2 to olga@localhost;
1361
grant select, show view on mysqltest1.v1 to olga@localhost;
1362
grant select, show view on mysqltest1.v2 to olga@localhost;
1364
grant select on mysqltest1.t1 to pjotr@localhost;
1365
grant select on mysqltest1.t2 to pjotr@localhost;
1366
grant select, show view on mysqltest1.v2 to pjotr@localhost;
1368
grant select, show view on mysqltest1.v1 to quintessa@localhost;
1370
disconnect test11765687;
1374
connect (test11765687,localhost,bob,,mysqltest1);
1375
connection test11765687;
1377
select * from v1; # Should succeed.
1378
--error ER_VIEW_NO_EXPLAIN
1379
explain select * from v1; # fail, no SHOW_VIEW
1381
disconnect test11765687;
1385
connect (test11765687,localhost,cecil,,mysqltest1);
1386
connection test11765687;
1388
--error ER_TABLEACCESS_DENIED_ERROR
1389
select * from v1; # fail, no SELECT
1390
--error ER_TABLEACCESS_DENIED_ERROR
1391
explain select * from v1; # fail, no SELECT
1393
disconnect test11765687;
1397
connect (test11765687,localhost,dan,,mysqltest1);
1398
connection test11765687;
1400
select * from v1; # Should succeed.
1401
explain select * from v1; # Should succeed.
1403
disconnect test11765687;
1406
--echo ... as eugene
1407
connect (test11765687,localhost,eugene,,mysqltest1);
1408
connection test11765687;
1410
select * from v1; # Should succeed.
1411
--error ER_VIEW_NO_EXPLAIN
1412
explain select * from v1; # fail, no SHOW_VIEW
1414
disconnect test11765687;
1418
connect (test11765687,localhost,fiona,,mysqltest1);
1419
connection test11765687;
1421
select * from v2; # Should succeed.
1422
show create view v2; # Should succeed, but...
1423
--error ER_TABLEACCESS_DENIED_ERROR
1424
explain select * from t1; # fail, shouldn't see t1!
1425
--error ER_TABLEACCESS_DENIED_ERROR
1426
# err msg must give view name, no table names!!
1427
explain select * from v1; # fail, have no privs on v1!
1428
--error ER_TABLEACCESS_DENIED_ERROR
1429
explain select * from t2; # fail, have no privs on t2!
1430
--error ER_VIEW_NO_EXPLAIN
1431
explain select * from v2; # fail, shouldn't see t2!
1433
disconnect test11765687;
1437
connect (test11765687,localhost,greg,,mysqltest1);
1438
connection test11765687;
1440
select * from v2; # Should succeed.
1441
--error ER_TABLEACCESS_DENIED_ERROR
1442
explain select * from v1; # fail; no SELECT on v1!
1443
--error ER_VIEW_NO_EXPLAIN
1444
explain select * from v2; # fail; no SELECT on v1!
1446
disconnect test11765687;
1450
connect (test11765687,localhost,han,,mysqltest1);
1451
connection test11765687;
1453
--error ER_TABLEACCESS_DENIED_ERROR
1454
select * from t3; # don't have privs on all columns,
1455
--error ER_TABLEACCESS_DENIED_ERROR
1456
explain select * from t3; # so EXPLAIN on "forbidden" columns should fail.
1457
select k from t3; # but we do have SELECT on column k though,
1458
explain select k from t3; # so EXPLAIN just on k should work,
1459
select * from v3; # and so should SELECT on view only using allowed columns
1460
explain select * from v3; # as should the associated EXPLAIN
1462
disconnect test11765687;
1466
connect (test11765687,localhost,inga,,mysqltest1);
1467
connection test11765687;
1470
# has sel/show on v2, sel on t1/t2, only sel v1
1471
# fail: lacks show on v1
1472
--error ER_VIEW_NO_EXPLAIN
1473
explain select * from v2;
1474
disconnect test11765687;
1478
connect (test11765687,localhost,jamie,,mysqltest1);
1479
connection test11765687;
1482
# has sel/show on v2, sel on t1/t2, only show v1
1483
# fail: lacks sel on v1
1484
--error ER_VIEW_NO_EXPLAIN
1485
explain select * from v2;
1486
disconnect test11765687;
1490
connect (test11765687,localhost,karl,,mysqltest1);
1491
connection test11765687;
1494
# has sel only on v2, sel on t1/t2, sel/show v1
1495
# fail: lacks show on v2
1496
--error ER_VIEW_NO_EXPLAIN
1497
explain select * from v2;
1498
disconnect test11765687;
1503
connect (test11765687,localhost,lena,,mysqltest1);
1504
connection test11765687;
1505
--error ER_TABLEACCESS_DENIED_ERROR
1507
# has show only on v2, sel on t1/t2, sel/show v1
1508
# fail: lacks sel on v2
1509
--error ER_TABLEACCESS_DENIED_ERROR
1510
explain select * from v2;
1511
disconnect test11765687;
1514
--echo ... as mhairi
1515
connect (test11765687,localhost,mhairi,,mysqltest1);
1516
connection test11765687;
1519
# has sel/show on v2, sel on t1/t2, sel/show v1
1520
explain select * from v2;
1521
disconnect test11765687;
1525
connect (test11765687,localhost,noam,,mysqltest1);
1526
connection test11765687;
1529
# has sel/show on v2, sel only on t1, sel/show v1 (no sel on t2!)
1530
--error ER_VIEW_NO_EXPLAIN
1531
explain select * from v2;
1532
disconnect test11765687;
1536
connect (test11765687,localhost,olga,,mysqltest1);
1537
connection test11765687;
1540
# has sel/show on v2, sel only on t2, sel/show v1 (no sel on t1!)
1541
--error ER_VIEW_NO_EXPLAIN
1542
explain select * from v2;
1543
disconnect test11765687;
1547
connect (test11765687,localhost,pjotr,,mysqltest1);
1548
connection test11765687;
1551
# has sel/show on v2, sel only on t2, nothing on v1
1552
# fail: lacks show on v1
1553
--error ER_VIEW_NO_EXPLAIN
1554
explain select * from v2;
1555
disconnect test11765687;
1558
--echo ... as quintessa
1559
connect (test11765687,localhost,quintessa,,mysqltest1);
1560
connection test11765687;
1562
select * from v1; # Should succeed.
1563
--error ER_VIEW_NO_EXPLAIN
1564
explain select * from v1; # fail: lacks select on t1
1566
disconnect test11765687;
1571
--echo ... as root again at last: clean-up time!
1574
drop user alice@localhost;
1575
drop user bob@localhost;
1576
drop user cecil@localhost;
1577
drop user dan@localhost;
1578
drop user eugene@localhost;
1579
drop user fiona@localhost;
1580
drop user greg@localhost;
1581
drop user han@localhost;
1582
drop user inga@localhost;
1583
drop user jamie@localhost;
1584
drop user karl@localhost;
1585
drop user lena@localhost;
1586
drop user mhairi@localhost;
1587
drop user noam@localhost;
1588
drop user olga@localhost;
1589
drop user pjotr@localhost;
1590
drop user quintessa@localhost;
1592
drop database mysqltest1;
1598
--echo End of 5.0 tests.
1602
# Test that ALTER VIEW accepts DEFINER and ALGORITHM, see bug#16425.
1606
DROP VIEW IF EXISTS v1;
1607
DROP TABLE IF EXISTS t1;
1610
CREATE TABLE t1 (i INT);
1611
CREATE VIEW v1 AS SELECT * FROM t1;
1613
ALTER VIEW v1 AS SELECT * FROM t1;
1614
SHOW CREATE VIEW v1;
1615
ALTER DEFINER=no_such@user_1 VIEW v1 AS SELECT * FROM t1;
1616
SHOW CREATE VIEW v1;
1617
ALTER ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t1;
1618
SHOW CREATE VIEW v1;
1619
ALTER ALGORITHM=TEMPTABLE DEFINER=no_such@user_2 VIEW v1 AS SELECT * FROM t1;
1620
SHOW CREATE VIEW v1;
1626
# Bug#37191: Failed assertion in CREATE VIEW
1628
CREATE USER mysqluser1@localhost;
1629
CREATE DATABASE mysqltest1;
1633
CREATE TABLE t1 ( a INT );
1634
CREATE TABLE t2 ( b INT );
1636
INSERT INTO t1 VALUES (1), (2);
1637
INSERT INTO t2 VALUES (1), (2);
1639
GRANT CREATE VIEW ON mysqltest1.* TO mysqluser1@localhost;
1641
GRANT SELECT ON t1 TO mysqluser1@localhost;
1642
GRANT INSERT ON t2 TO mysqluser1@localhost;
1644
--connect (connection1, localhost, mysqluser1, , mysqltest1)
1646
--echo This would lead to failed assertion.
1647
CREATE VIEW v1 AS SELECT a, b FROM t1, t2;
1649
--error ER_TABLEACCESS_DENIED_ERROR
1651
--error ER_TABLEACCESS_DENIED_ERROR
1654
--disconnect connection1
1655
--connection default
1659
DROP DATABASE mysqltest1;
1660
DROP USER mysqluser1@localhost;
1663
--echo End of 5.1 tests.
1666
# Bug#36086: SELECT * from views don't check column grants
1668
CREATE USER mysqluser1@localhost;
1669
CREATE DATABASE mysqltest1;
1673
CREATE TABLE t1 ( a INT, b INT );
1674
CREATE TABLE t2 ( a INT, b INT );
1676
CREATE VIEW v1 AS SELECT a, b FROM t1;
1678
GRANT SELECT( a ) ON v1 TO mysqluser1@localhost;
1679
GRANT UPDATE( b ) ON t2 TO mysqluser1@localhost;
1681
--connect (connection1, localhost, mysqluser1, , test)
1683
--error ER_TABLEACCESS_DENIED_ERROR
1684
SELECT * FROM mysqltest1.v1;
1686
--error ER_TABLEACCESS_DENIED_ERROR
1687
CREATE VIEW v1 AS SELECT * FROM mysqltest1.t2;
1689
--disconnect connection1
1691
--connection default
1695
DROP DATABASE mysqltest1;
1696
DROP USER mysqluser1@localhost;
1699
# Bug#35600 Security breach via view, I_S table and prepared
1700
# statement/stored procedure
1702
CREATE USER mysqluser1@localhost;
1703
CREATE DATABASE mysqltest1;
1707
CREATE VIEW v1 AS SELECT * FROM information_schema.tables LIMIT 1;
1708
CREATE ALGORITHM = TEMPTABLE VIEW v2 AS SELECT 1 AS A;
1710
CREATE VIEW test.v3 AS SELECT 1 AS a;
1712
--connection default
1713
GRANT SELECT ON mysqltest1.* to mysqluser1@localhost;
1714
GRANT ALL ON test.* TO mysqluser1@localhost;
1716
--connect (connection1, localhost, mysqluser1, , test)
1717
PREPARE stmt_v1 FROM "SELECT * FROM mysqltest1.v1";
1718
PREPARE stmt_v2 FROM "SELECT * FROM mysqltest1.v2";
1720
--connection default
1721
REVOKE SELECT ON mysqltest1.* FROM mysqluser1@localhost;
1723
--connection connection1
1725
--error ER_TABLEACCESS_DENIED_ERROR
1727
--error ER_TABLEACCESS_DENIED_ERROR
1729
--disconnect connection1
1731
--connect (connection2, localhost, mysqluser1,,)
1732
PREPARE stmt FROM "SELECT a FROM v3";
1734
--disconnect connection2
1736
--connection default
1738
DROP DATABASE mysqltest1;
1740
DROP USER mysqluser1@localhost;
1744
--echo # Bug#35996: SELECT + SHOW VIEW should be enough to display view
1747
-- source include/not_embedded.inc
1748
CREATE USER mysqluser1@localhost;
1749
CREATE DATABASE mysqltest1;
1750
CREATE DATABASE mysqltest2;
1751
GRANT USAGE, SELECT, CREATE VIEW, SHOW VIEW
1752
ON mysqltest2.* TO mysqluser1@localhost;
1756
CREATE TABLE t1( a INT );
1757
CREATE TABLE t2( a INT, b INT );
1758
CREATE FUNCTION f1() RETURNS INT RETURN 1;
1759
CREATE VIEW v1 AS SELECT 1 AS a;
1760
CREATE VIEW v2 AS SELECT 1 AS a, 2 AS b;
1762
GRANT SELECT ON TABLE t1 TO mysqluser1@localhost;
1763
GRANT SELECT (a, b) ON TABLE t2 TO mysqluser1@localhost;
1764
GRANT EXECUTE ON FUNCTION f1 TO mysqluser1@localhost;
1765
GRANT SELECT ON TABLE v1 TO mysqluser1@localhost;
1766
GRANT SELECT (a, b) ON TABLE v2 TO mysqluser1@localhost;
1768
CREATE VIEW v_t1 AS SELECT * FROM t1;
1769
CREATE VIEW v_t2 AS SELECT * FROM t2;
1770
CREATE VIEW v_f1 AS SELECT f1() AS a;
1771
CREATE VIEW v_v1 AS SELECT * FROM v1;
1772
CREATE VIEW v_v2 AS SELECT * FROM v2;
1774
GRANT SELECT, SHOW VIEW ON v_t1 TO mysqluser1@localhost;
1775
GRANT SELECT, SHOW VIEW ON v_t2 TO mysqluser1@localhost;
1776
GRANT SELECT, SHOW VIEW ON v_f1 TO mysqluser1@localhost;
1777
GRANT SELECT, SHOW VIEW ON v_v1 TO mysqluser1@localhost;
1778
GRANT SELECT, SHOW VIEW ON v_v2 TO mysqluser1@localhost;
1780
--connect (connection1, localhost, mysqluser1,, mysqltest2)
1781
CREATE VIEW v_mysqluser1_t1 AS SELECT * FROM mysqltest1.t1;
1782
CREATE VIEW v_mysqluser1_t2 AS SELECT * FROM mysqltest1.t2;
1783
CREATE VIEW v_mysqluser1_f1 AS SELECT mysqltest1.f1() AS a;
1784
CREATE VIEW v_mysqluser1_v1 AS SELECT * FROM mysqltest1.v1;
1785
CREATE VIEW v_mysqluser1_v2 AS SELECT * FROM mysqltest1.v2;
1787
SHOW CREATE VIEW mysqltest1.v_t1;
1788
SHOW CREATE VIEW mysqltest1.v_t2;
1789
SHOW CREATE VIEW mysqltest1.v_f1;
1790
SHOW CREATE VIEW mysqltest1.v_v1;
1791
SHOW CREATE VIEW mysqltest1.v_v2;
1793
SHOW CREATE VIEW v_mysqluser1_t1;
1794
SHOW CREATE VIEW v_mysqluser1_t2;
1795
SHOW CREATE VIEW v_mysqluser1_f1;
1796
SHOW CREATE VIEW v_mysqluser1_v1;
1797
SHOW CREATE VIEW v_mysqluser1_v2;
1799
--connection default
1800
REVOKE SELECT ON TABLE t1 FROM mysqluser1@localhost;
1801
REVOKE SELECT (a) ON TABLE t2 FROM mysqluser1@localhost;
1802
REVOKE EXECUTE ON FUNCTION f1 FROM mysqluser1@localhost;
1803
REVOKE SELECT ON TABLE v1 FROM mysqluser1@localhost;
1805
--connection connection1
1806
SHOW CREATE VIEW mysqltest1.v_t1;
1807
SHOW CREATE VIEW mysqltest1.v_t2;
1808
SHOW CREATE VIEW mysqltest1.v_f1;
1809
SHOW CREATE VIEW mysqltest1.v_v1;
1810
SHOW CREATE VIEW mysqltest1.v_v2;
1812
SHOW CREATE VIEW v_mysqluser1_t1;
1813
SHOW CREATE VIEW v_mysqluser1_t2;
1814
SHOW CREATE VIEW v_mysqluser1_f1;
1815
SHOW CREATE VIEW v_mysqluser1_v1;
1816
SHOW CREATE VIEW v_mysqluser1_v2;
1818
--connection default
1819
--echo # Testing the case when the views reference missing objects.
1820
--echo # Obviously, there are no privileges to check for, so we
1821
--echo # need only each object type once.
1826
--connection connection1
1827
SHOW CREATE VIEW mysqltest1.v_t1;
1828
SHOW CREATE VIEW mysqltest1.v_f1;
1829
SHOW CREATE VIEW mysqltest1.v_v1;
1831
SHOW CREATE VIEW v_mysqluser1_t1;
1832
SHOW CREATE VIEW v_mysqluser1_f1;
1833
SHOW CREATE VIEW v_mysqluser1_v1;
1835
--connection default
1836
REVOKE SHOW VIEW ON v_t1 FROM mysqluser1@localhost;
1837
REVOKE SHOW VIEW ON v_f1 FROM mysqluser1@localhost;
1838
REVOKE SHOW VIEW ON v_v1 FROM mysqluser1@localhost;
1840
--connection connection1
1841
--error ER_TABLEACCESS_DENIED_ERROR
1842
SHOW CREATE VIEW mysqltest1.v_t1;
1843
--error ER_TABLEACCESS_DENIED_ERROR
1844
SHOW CREATE VIEW mysqltest1.v_f1;
1845
--error ER_TABLEACCESS_DENIED_ERROR
1846
SHOW CREATE VIEW mysqltest1.v_v1;
1847
SHOW CREATE VIEW v_mysqluser1_t1;
1848
SHOW CREATE VIEW v_mysqluser1_f1;
1849
SHOW CREATE VIEW v_mysqluser1_v1;
1851
--disconnect connection1
1852
--connection default
1853
DROP USER mysqluser1@localhost;
1854
DROP DATABASE mysqltest1;
1855
DROP DATABASE mysqltest2;
1858
CREATE TABLE t1( a INT );
1859
CREATE DEFINER = no_such_user@no_such_host VIEW v1 AS SELECT * FROM t1;
1860
SHOW CREATE VIEW v1;
1866
--echo # Bug #46019: ERROR 1356 When selecting from within another
1867
--echo # view that has Group By
1869
CREATE DATABASE mysqltest1;
1872
CREATE TABLE t1 (a INT);
1874
CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT a FROM t1 GROUP BY a;
1875
CREATE SQL SECURITY INVOKER VIEW v2 AS SELECT a FROM v1;
1877
CREATE USER mysqluser1;
1879
GRANT SELECT ON TABLE t1 TO mysqluser1;
1880
GRANT SELECT, SHOW VIEW ON TABLE v1 TO mysqluser1;
1881
GRANT SELECT, SHOW VIEW ON TABLE v2 TO mysqluser1;
1883
--connect (mysqluser1, localhost, mysqluser1,,mysqltest1)
1887
--connection default
1888
--disconnect mysqluser1
1889
DROP USER mysqluser1;
1890
DROP DATABASE mysqltest1;
1894
--echo # Bug#47734: Assertion failed: ! is_set() when locking a view with non-existing definer
1898
DROP VIEW IF EXISTS v1;
1901
CREATE DEFINER=`unknown`@`unknown` SQL SECURITY DEFINER VIEW v1 AS SELECT 1;
1902
--error ER_NO_SUCH_USER
1903
LOCK TABLES v1 READ;
1908
--echo # Bug #58499 "DEFINER-security view selecting from INVOKER-security view
1909
--echo # access check wrong".
1911
--echo # Check that we correctly handle privileges for various combinations
1912
--echo # of INVOKER and DEFINER-security views using each other.
1914
DROP DATABASE IF EXISTS mysqltest1;
1916
CREATE DATABASE mysqltest1;
1918
CREATE TABLE t1 (i INT);
1919
CREATE TABLE t2 (j INT);
1920
INSERT INTO t1 VALUES (1);
1921
INSERT INTO t2 VALUES (2);
1923
--echo # 1) DEFINER-security view uses INVOKER-security view (covers
1924
--echo # scenario originally described in the bug report).
1925
CREATE SQL SECURITY INVOKER VIEW v1_uses_t1 AS SELECT * FROM t1;
1926
CREATE SQL SECURITY INVOKER VIEW v1_uses_t2 AS SELECT * FROM t2;
1927
CREATE USER 'mysqluser1'@'%';
1928
GRANT CREATE VIEW ON mysqltest1.* TO 'mysqluser1'@'%';
1929
GRANT SELECT ON t1 TO 'mysqluser1'@'%';
1930
--echo # To be able create 'v2_uses_t2' we also need select on t2.
1931
GRANT SELECT ON t2 TO 'mysqluser1'@'%';
1932
GRANT SELECT ON v1_uses_t1 TO 'mysqluser1'@'%';
1933
GRANT SELECT ON v1_uses_t2 TO 'mysqluser1'@'%';
1935
--echo # Connection 'mysqluser1'.
1936
--connect (mysqluser1, localhost, mysqluser1,,mysqltest1)
1937
CREATE SQL SECURITY DEFINER VIEW v2_uses_t1 AS SELECT * FROM v1_uses_t1;
1938
CREATE SQL SECURITY DEFINER VIEW v2_uses_t2 AS SELECT * FROM v1_uses_t2;
1940
--echo # Connection 'default'.
1941
--connection default
1942
CREATE USER 'mysqluser2'@'%';
1943
GRANT SELECT ON v2_uses_t1 TO 'mysqluser2'@'%';
1944
GRANT SELECT ON v2_uses_t2 TO 'mysqluser2'@'%';
1945
GRANT SELECT ON t2 TO 'mysqluser2'@'%';
1946
GRANT CREATE VIEW ON mysqltest1.* TO 'mysqluser2'@'%';
1947
--echo # Make 'mysqluser1' unable to access t2.
1948
REVOKE SELECT ON t2 FROM 'mysqluser1'@'%';
1950
--echo # Connection 'mysqluser2'.
1951
--connect (mysqluser2, localhost, mysqluser2,,mysqltest1)
1952
--echo # The below statement should succeed thanks to suid nature of v2_uses_t1.
1953
SELECT * FROM v2_uses_t1;
1954
--echo # The below statement should fail due to suid nature of v2_uses_t2.
1955
--error ER_VIEW_INVALID
1956
SELECT * FROM v2_uses_t2;
1958
--echo # 2) INVOKER-security view uses INVOKER-security view.
1960
--echo # Connection 'default'.
1961
--connection default
1962
DROP VIEW v2_uses_t1, v2_uses_t2;
1963
CREATE SQL SECURITY INVOKER VIEW v2_uses_t1 AS SELECT * FROM v1_uses_t1;
1964
CREATE SQL SECURITY INVOKER VIEW v2_uses_t2 AS SELECT * FROM v1_uses_t2;
1965
GRANT SELECT ON v2_uses_t1 TO 'mysqluser1'@'%';
1966
GRANT SELECT ON v2_uses_t2 TO 'mysqluser1'@'%';
1967
GRANT SELECT ON v1_uses_t1 TO 'mysqluser2'@'%';
1968
GRANT SELECT ON v1_uses_t2 TO 'mysqluser2'@'%';
1970
--echo # Connection 'mysqluser1'.
1971
--connection mysqluser1
1972
--echo # For both versions of 'v2' 'mysqluser1' privileges should be used.
1973
SELECT * FROM v2_uses_t1;
1974
--error ER_VIEW_INVALID
1975
SELECT * FROM v2_uses_t2;
1977
--echo # Connection 'mysqluser2'.
1978
--connection mysqluser2
1979
--echo # And now for both versions of 'v2' 'mysqluser2' privileges should
1981
--error ER_VIEW_INVALID
1982
SELECT * FROM v2_uses_t1;
1983
SELECT * FROM v2_uses_t2;
1985
--echo # 3) INVOKER-security view uses DEFINER-security view.
1987
--echo # Connection 'default'.
1988
--connection default
1989
DROP VIEW v1_uses_t1, v1_uses_t2;
1990
--echo # To be able create 'v1_uses_t2' we also need select on t2.
1991
GRANT SELECT ON t2 TO 'mysqluser1'@'%';
1993
--echo # Connection 'mysqluser1'.
1994
--connection mysqluser1
1995
CREATE SQL SECURITY DEFINER VIEW v1_uses_t1 AS SELECT * FROM t1;
1996
CREATE SQL SECURITY DEFINER VIEW v1_uses_t2 AS SELECT * FROM t2;
1998
--echo # Connection 'default'.
1999
--connection default
2000
--echo # Make 'mysqluser1' unable to access t2.
2001
REVOKE SELECT ON t2 FROM 'mysqluser1'@'%';
2003
--echo # Connection 'mysqluser2'.
2004
--connection mysqluser2
2005
--echo # Due to suid nature of v1_uses_t1 and v1_uses_t2 the first
2006
--echo # select should succeed and the second select should fail.
2007
SELECT * FROM v2_uses_t1;
2008
--error ER_VIEW_INVALID
2009
SELECT * FROM v2_uses_t2;
2011
--echo # 4) DEFINER-security view uses DEFINER-security view.
2013
--echo # Connection 'default'.
2014
--connection default
2015
DROP VIEW v2_uses_t1, v2_uses_t2;
2016
--echo # To be able create 'v2_uses_t2' we also need select on t2.
2017
GRANT SELECT ON t2 TO 'mysqluser1'@'%';
2019
--echo # Connection 'mysqluser2'.
2020
--connection mysqluser2
2021
CREATE SQL SECURITY DEFINER VIEW v2_uses_t1 AS SELECT * FROM v1_uses_t1;
2022
CREATE SQL SECURITY DEFINER VIEW v2_uses_t2 AS SELECT * FROM v1_uses_t2;
2024
--echo # Connection 'default'.
2025
--connection default
2026
--echo # Make 'mysqluser1' unable to access t2.
2027
REVOKE SELECT ON t2 FROM 'mysqluser1'@'%';
2029
--echo # Connection 'mysqluser2'.
2030
--connection mysqluser2
2031
--echo # Again privileges of creator of innermost views should apply.
2032
SELECT * FROM v2_uses_t1;
2033
--error ER_VIEW_INVALID
2034
SELECT * FROM v2_uses_t2;
2036
--disconnect mysqluser1
2037
--disconnect mysqluser2
2038
--connection default
2040
DROP DATABASE mysqltest1;
2041
DROP USER 'mysqluser1'@'%';
2042
DROP USER 'mysqluser2'@'%';
2045
# Wait till we reached the initial number of concurrent sessions
2046
--source include/wait_until_count_sessions.inc