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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
|
CREATE ROLE r1;
CREATE DATABASE db1;
CREATE DATABASE db2;
CREATE USER u1@localhost IDENTIFIED BY 'foo';
GRANT CREATE ON db1.* TO u1@localhost;
GRANT r1 TO u1@localhost;
SHOW STATUS LIKE '%acl_cache%';
Variable_name Value
Acl_cache_items_count 0
CREATE TABLE db1.t1 (c1 int);
CREATE TABLE db1.t2 (c1 int);
CREATE TABLE db2.t1 (c1 int);
CREATE TABLE db2.t2 (c1 int);
CREATE SQL SECURITY DEFINER VIEW db1.v1 AS SELECT * FROM db1.t1;
CREATE SQL SECURITY DEFINER VIEW db2.v1 AS SELECT * FROM db2.t1;
CREATE SQL SECURITY DEFINER VIEW db1.v2 AS SELECT * FROM db1.t1;
CREATE SQL SECURITY INVOKER VIEW db1.v4 AS SELECT * FROM db2.t2;
++ Test global level privileges
GRANT SELECT ON *.* TO r1;
SHOW GRANTS FOR u1@localhost USING r1;
Grants for u1@localhost
GRANT SELECT ON *.* TO `u1`@`localhost`
GRANT CREATE ON `db1`.* TO `u1`@`localhost`
GRANT `r1`@`%` TO `u1`@`localhost`
SET ROLE r1;
++ Positive test
SELECT * FROM v1;
c1
SELECT * FROM db2.v1;
c1
SELECT * FROM v4;
c1
++ Test revoke
REVOKE SELECT ON *.* FROM r1;
SHOW GRANTS FOR u1@localhost USING r1;
Grants for u1@localhost
GRANT USAGE ON *.* TO `u1`@`localhost`
GRANT CREATE ON `db1`.* TO `u1`@`localhost`
GRANT `r1`@`%` TO `u1`@`localhost`
SET ROLE r1;
SELECT * FROM v1;
ERROR 42000: SELECT command denied to user 'u1'@'localhost' for table 'v1'
++ Test schema level privileges
GRANT SELECT ON db1.* TO r1;
SHOW GRANTS FOR u1@localhost USING r1;
Grants for u1@localhost
GRANT USAGE ON *.* TO `u1`@`localhost`
GRANT SELECT, CREATE ON `db1`.* TO `u1`@`localhost`
GRANT `r1`@`%` TO `u1`@`localhost`
SET ROLE r1;
++ Positive test
SELECT * FROM v1;
c1
SELECT * FROM v2;
c1
++ Negative test
SELECT * FROM db2.v1;
ERROR 42000: SELECT command denied to user 'u1'@'localhost' for table 'v1'
SELECT * FROM v4;
ERROR HY000: View 'db1.v4' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
REVOKE SELECT ON db1.* FROM r1;
++ Test routine level privileges
GRANT SELECT ON db1.v1 TO r1;
SET ROLE r1;
++ Positive test
SELECT * FROM v1;
c1
++ Negative test
SELECT * FROM v2;
ERROR 42000: SELECT command denied to user 'u1'@'localhost' for table 'v2'
SELECT * FROM db2.v1;
ERROR 42000: SELECT command denied to user 'u1'@'localhost' for table 'v1'
SELECT * FROM v4;
ERROR 42000: SELECT command denied to user 'u1'@'localhost' for table 'v4'
++ Test Security invoker model
GRANT SELECT ON db2.* TO r1;
GRANT SELECT ON db1.* TO r1;
GRANT CREATE VIEW ON db1.* TO r1;
SET ROLE r1;
++ Positive test
SELECT * FROM v1;
c1
SHOW GRANTS FOR CURRENT_USER;
Grants for u1@localhost
GRANT USAGE ON *.* TO `u1`@`localhost`
GRANT SELECT, CREATE, CREATE VIEW ON `db1`.* TO `u1`@`localhost`
GRANT SELECT ON `db2`.* TO `u1`@`localhost`
GRANT SELECT ON `db1`.`v1` TO `u1`@`localhost`
GRANT `r1`@`%` TO `u1`@`localhost`
SELECT * FROM v4;
c1
++ Test SUID = u1@localhost with default roles
CREATE SQL SECURITY DEFINER VIEW db1.v5 AS SELECT * FROM db2.t1;
Negative test; DEFINER VIEWs always use default roles
SELECT * FROM v5;
ERROR HY000: View 'db1.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
Positive test; Added default role.
ALTER USER u1@localhost DEFAULT ROLE r1;
SELECT * FROM v5;
c1
CREATE USER u2@localhost IDENTIFIED BY 'oof';
GRANT SELECT ON db1.* TO u2@localhost;
SHOW GRANTS FOR u1@localhost USING r1;
Grants for u1@localhost
GRANT USAGE ON *.* TO `u1`@`localhost`
GRANT SELECT, CREATE, CREATE VIEW ON `db1`.* TO `u1`@`localhost`
GRANT SELECT ON `db2`.* TO `u1`@`localhost`
GRANT SELECT ON `db1`.`v1` TO `u1`@`localhost`
GRANT `r1`@`%` TO `u1`@`localhost`
++ Positive test
SELECT * FROM db1.v5;
c1
SELECT * FROM db1.v2;
c1
++ Negative test
SELECT * FROM db1.v4;
ERROR HY000: View 'db1.v4' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
REVOKE r1 FROM u1@localhost;
SELECT * FROM v5;
ERROR HY000: View 'db1.v5' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
++ Clean up
DROP DATABASE db1;
DROP DATABASE db2;
DROP USER u1@localhost;
DROP ROLE r1;
DROP USER u2@localhost;
SHOW STATUS LIKE '%acl_cache%';
Variable_name Value
Acl_cache_items_count 1
#
# BUG#34341533: "SHOW FIELDS FROM" fails against a view when a view
# accessing from a view is recreated
#
CREATE USER user_with_role@localhost;
CREATE ROLE test_role;
GRANT ALL on *.* TO test_role;
GRANT test_role TO user_with_role@localhost;
SET DEFAULT ROLE test_role TO user_with_role@localhost;
CREATE USER user_without_role@localhost;
GRANT ALL on *.* TO user_without_role@localhost;
connect user_with_role, localhost, user_with_role,,;
USE test;
CREATE TABLE t1 (c1 INT);
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE VIEW v2 AS SELECT * FROM v1;
DROP VIEW v1;
CREATE VIEW v1 AS SELECT * FROM t1;
# Without the patch, the following queries reported ER_VIEW_INVALID error
SHOW FIELDS FROM v2;
Field Type Null Key Default Extra
c1 int YES NULL
DESCRIBE v2;
Field Type Null Key Default Extra
c1 int YES NULL
connect user_without_role, localhost, user_without_role,,;
USE test;
CREATE TABLE t2 (c1 INT);
CREATE VIEW v3 AS SELECT * FROM t2;
CREATE VIEW v4 AS SELECT * FROM v3;
DROP VIEW v3;
CREATE VIEW v3 AS SELECT * FROM t2;
SHOW FIELDS FROM v4;
Field Type Null Key Default Extra
c1 int YES NULL
DESCRIBE v4;
Field Type Null Key Default Extra
c1 int YES NULL
disconnect user_without_role;
disconnect user_with_role;
connection default;
DROP VIEW v1, v2, v3, v4;
DROP TABLE t1, t2;
DROP USER user_without_role@localhost;
DROP USER user_with_role@localhost;
DROP ROLE test_role;
|
