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
176
|
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%';
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;
--echo ++ Test global level privileges
GRANT SELECT ON *.* TO r1;
SHOW GRANTS FOR u1@localhost USING r1;
connect(con1, localhost, u1, foo, db1);
SET ROLE r1;
--echo ++ Positive test
SELECT * FROM v1;
SELECT * FROM db2.v1;
SELECT * FROM v4;
--echo ++ Test revoke
connection default;
REVOKE SELECT ON *.* FROM r1;
SHOW GRANTS FOR u1@localhost USING r1;
connection con1;
SET ROLE r1;
--error ER_TABLEACCESS_DENIED_ERROR
SELECT * FROM v1;
--echo ++ Test schema level privileges
connection default;
GRANT SELECT ON db1.* TO r1;
SHOW GRANTS FOR u1@localhost USING r1;
connection con1;
SET ROLE r1;
--echo ++ Positive test
SELECT * FROM v1;
SELECT * FROM v2;
--echo ++ Negative test
--error ER_TABLEACCESS_DENIED_ERROR
SELECT * FROM db2.v1;
--error ER_VIEW_INVALID
SELECT * FROM v4;
connection default;
REVOKE SELECT ON db1.* FROM r1;
--echo ++ Test routine level privileges
GRANT SELECT ON db1.v1 TO r1;
connection con1;
SET ROLE r1;
--echo ++ Positive test
SELECT * FROM v1;
--echo ++ Negative test
--error ER_TABLEACCESS_DENIED_ERROR
SELECT * FROM v2;
--error ER_TABLEACCESS_DENIED_ERROR
SELECT * FROM db2.v1;
--error ER_TABLEACCESS_DENIED_ERROR
SELECT * FROM v4;
--echo ++ Test Security invoker model
connection default;
GRANT SELECT ON db2.* TO r1;
GRANT SELECT ON db1.* TO r1;
GRANT CREATE VIEW ON db1.* TO r1;
connection con1;
SET ROLE r1;
--echo ++ Positive test
SELECT * FROM v1;
SHOW GRANTS FOR CURRENT_USER;
SELECT * FROM v4;
--echo ++ Test SUID = u1@localhost with default roles
CREATE SQL SECURITY DEFINER VIEW db1.v5 AS SELECT * FROM db2.t1;
--echo Negative test; DEFINER VIEWs always use default roles
--error ER_VIEW_INVALID
SELECT * FROM v5;
--echo Positive test; Added default role.
ALTER USER u1@localhost DEFAULT ROLE r1;
SELECT * FROM v5;
connection default;
CREATE USER u2@localhost IDENTIFIED BY 'oof';
GRANT SELECT ON db1.* TO u2@localhost;
SHOW GRANTS FOR u1@localhost USING r1;
connect(con2, localhost, u2, oof, db1);
--echo ++ Positive test
SELECT * FROM db1.v5;
SELECT * FROM db1.v2;
--echo ++ Negative test
--error ER_VIEW_INVALID
SELECT * FROM db1.v4;
connection default;
REVOKE r1 FROM u1@localhost;
connection con2;
--error ER_VIEW_INVALID
SELECT * FROM v5;
--echo ++ Clean up
connection default;
DROP DATABASE db1;
DROP DATABASE db2;
DROP USER u1@localhost;
DROP ROLE r1;
DROP USER u2@localhost;
disconnect con1;
disconnect con2;
SHOW STATUS LIKE '%acl_cache%';
--echo #
--echo # BUG#34341533: "SHOW FIELDS FROM" fails against a view when a view
--echo # accessing from a view is recreated
--echo #
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;
--enable_connect_log
--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;
--echo # Without the patch, the following queries reported ER_VIEW_INVALID error
SHOW FIELDS FROM v2;
DESCRIBE v2;
--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;
DESCRIBE v4;
# Cleanup
--disconnect user_without_role
--disconnect user_with_role
--connection default
--disable_connect_log
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;
|
