1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
|
source include/not_embedded.inc;
source include/have_perfschema.inc;
--disable_service_connection
#
# LOCK TABLES and privileges on views
#
create database mysqltest1;
create database mysqltest2;
create database mysqltest3;
create user invoker@localhost;
create user definer@localhost;
grant select,show view on mysqltest1.* to invoker@localhost;
grant select,show view on mysqltest1.* to definer@localhost;
grant select,show view on mysqltest2.* to invoker@localhost;
grant select,show view on mysqltest2.* to definer@localhost;
grant select,show view on mysqltest3.* to invoker@localhost;
grant select on performance_schema.* to definer@localhost;
create table mysqltest1.t1 (a int);
create definer=definer@localhost view mysqltest2.v2 as select * from mysqltest1.t1;
create definer=definer@localhost view mysqltest3.v3 as select * from mysqltest2.v2;
create definer=definer@localhost view mysqltest3.v3is as select schema_name from information_schema.schemata order by schema_name;
create definer=definer@localhost view mysqltest3.v3ps as select user from performance_schema.users where current_connections>0 order by user;
create definer=definer@localhost view mysqltest3.v3nt as select 1;
create definer=definer@localhost sql security invoker view mysqltest3.v3i as select * from mysqltest1.t1;
exec $MYSQL_DUMP --no-autocommit=0 --compact -B mysqltest1 mysqltest2 mysqltest3;
connect inv,localhost,invoker;
error ER_DBACCESS_DENIED_ERROR;
lock table mysqltest3.v3 write;
disconnect inv;
connection default;
grant lock tables on mysqltest3.* to invoker@localhost;
connect inv,localhost,invoker;
show create view mysqltest3.v3;
show create view mysqltest3.v3is;
show create view mysqltest3.v3ps;
show create view mysqltest3.v3nt;
show create view mysqltest3.v3i;
error ER_VIEW_INVALID;
lock table mysqltest3.v3 write;
error ER_VIEW_INVALID;
lock table mysqltest3.v3i write;
lock table mysqltest3.v3is write; select * from mysqltest3.v3is;
lock table mysqltest3.v3ps write; select * from mysqltest3.v3ps;
lock table mysqltest3.v3nt write; select * from mysqltest3.v3nt;
disconnect inv;
connection default;
grant lock tables on mysqltest2.* to invoker@localhost;
connect inv,localhost,invoker;
error ER_VIEW_INVALID;
lock table mysqltest3.v3 write;
error ER_VIEW_INVALID;
lock table mysqltest3.v3i write;
disconnect inv;
connection default;
grant lock tables on mysqltest1.* to definer@localhost;
connect inv,localhost,invoker;
lock table mysqltest3.v3 write; select * from mysqltest3.v3;
error ER_VIEW_INVALID;
lock table mysqltest3.v3i write;
disconnect inv;
connection default;
grant lock tables on mysqltest1.* to invoker@localhost;
connect inv,localhost,invoker;
lock table mysqltest3.v3i write; select * from mysqltest3.v3i;
disconnect inv;
connection default;
drop user invoker@localhost;
drop user definer@localhost;
drop database mysqltest1;
drop database mysqltest2;
drop database mysqltest3;
--echo #
--echo # MDEV-24331 mysqldump fails with "Got error: 1356" if the database contains a view with a subquery
--echo #
create user u1@localhost;
grant all privileges on test.* to u1@localhost;
connect con1,localhost,u1;
use test;
create table t1 (id int not null);
create view v1 as select * from (select * from t1) dt;
lock table v1 read;
disconnect con1;
connection default;
exec $MYSQL_DUMP --no-autocommit=0 test v1 -uu1 --compact;
drop view v1;
drop table t1;
drop user u1@localhost;
--enable_service_connection
|
