Debian Codegraph @

mysql-test/suite/auth_sec/r/atomic_revokes.result

raw
1 include/save_binlog_position.inc
2 # ----------------------------------------------------------------------
3 # Begin : Tests for REVOKE on global, db and proxy privileges
4 CREATE USER userX, userY, userA;
5 CREATE DATABASE db1;
6 GRANT SUPER ON *.* TO userX, userA;
7 Warnings:
8 Warning 1287 The SUPER privilege identifier is deprecated
9 GRANT SELECT ON db1.* TO userX, userA;
10 GRANT PROXY ON userY TO userX, userA;
11 include/save_binlog_position.inc
12 # Initial set of grants for userX
13 SHOW GRANTS FOR userX;
14 Grants for userX@%
15 GRANT SUPER ON *.* TO `userX`@`%`
16 GRANT SELECT ON `db1`.* TO `userX`@`%`
17 GRANT PROXY ON `userY`@`%` TO `userX`@`%`
18 # Case 1 : Revoke from non-existing user
19 REVOKE SUPER ON *.* FROM useX, userZ;
20 ERROR 42000: There is no such grant defined for user 'useX' on host '%'
21 REVOKE SELECT ON db1.* FROM userX, userZ;
22 ERROR 42000: There is no such grant defined for user 'userZ' on host '%'
23 REVOKE PROXY ON userY FROM userX, userZ;
24 ERROR 42000: There is no such grant defined for user 'userZ' on host '%'
25 # userX's grants must not have changed
26 SHOW GRANTS FOR userX;
27 Grants for userX@%
28 GRANT SUPER ON *.* TO `userX`@`%`
29 GRANT SELECT ON `db1`.* TO `userX`@`%`
30 GRANT PROXY ON `userY`@`%` TO `userX`@`%`
31 # This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE SUPER ON.*FROM.*userX.*userZ.*)
32 include/assert_binlog_events.inc
33 # This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE SELCT ON.*db1.*FROM.*userX.*userZ.*)
34 include/assert_binlog_events.inc
35 # This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE PROXY ON .*userY.*FROM.*userX.*userZ.*)
36 include/assert_binlog_events.inc
37 # Case 2 : Revoke non-existing grants
38 REVOKE SELECT ON db1.* FROM userY;
39 ERROR 42000: There is no such grant defined for user 'userY' on host '%'
40 REVOKE PROXY ON userX FROM userY;
41 ERROR 42000: There is no such grant defined for user 'userY' on host '%'
42 # userX's grants must not have changed
43 SHOW GRANTS FOR userX;
44 Grants for userX@%
45 GRANT SUPER ON *.* TO `userX`@`%`
46 GRANT SELECT ON `db1`.* TO `userX`@`%`
47 GRANT PROXY ON `userY`@`%` TO `userX`@`%`
48 # This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE SELECT ON .*db1.*FROM.*userY.*)
49 include/assert_binlog_events.inc
50 # This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE PROXY ON .*userX.*FROM.*userY.*)
51 include/assert_binlog_events.inc
52 # Case 3 : Valid revoke
53 REVOKE SUPER ON *.* FROM userX, userA;
54 Warnings:
55 Warning 1287 The SUPER privilege identifier is deprecated
56 # This event sequence pattern MUST be present in binlog: !Q(REVOKE SUPER ON *.* FROM.*userX.*userA.*)
57 include/assert_binlog_events.inc
58 include/save_binlog_position.inc
59 REVOKE SELECT ON db1.* FROM userX, userA;
60 # This event sequence pattern MUST be present in binlog: !Q(REVOKE SELECT ON .*db1.*FROM.*userX.*userA.*)
61 include/assert_binlog_events.inc
62 include/save_binlog_position.inc
63 REVOKE PROXY ON userY FROM userX, userA;
64 # This event sequence pattern MUST be present in binlog: !Q(REVOKE PROXY ON.*userY.*FROM.*userX.*userA.*)
65 include/assert_binlog_events.inc
66 # userX's grants must have changed
67 SHOW GRANTS FOR userX;
68 Grants for userX@%
69 GRANT USAGE ON *.* TO `userX`@`%`
70 DROP USER userX, userY, userA;
71 DROP DATABASE db1;
72 # End : Tests for REVOKE on global, db and proxy privileges
73 # ----------------------------------------------------------------------
74 # Begin : Tests for REVOKE on table and column privileges
75 CREATE USER userX, userY, userA;
76 CREATE DATABASE db1;
77 CREATE TABLE db1.table1(c1 int, c2 int, c3 int);
78 GRANT SELECT ON db1.table1 TO userX, userA;
79 GRANT INSERT(c1, c2) ON db1.table1 TO userX, userA;
80 include/save_binlog_position.inc
81 # Initial set of grants for userX
82 SHOW GRANTS FOR userX;
83 Grants for userX@%
84 GRANT USAGE ON *.* TO `userX`@`%`
85 GRANT SELECT, INSERT (`c1`, `c2`) ON `db1`.`table1` TO `userX`@`%`
86 # Case 1 : Revoke from non-existing user
87 REVOKE SELECT ON db1.table1 FROM userX, userZ;
88 ERROR 42000: There is no such grant defined for user 'userZ' on host '%' on table 'table1'
89 REVOKE INSERT(c1, c2) ON db1.table1 FROM userX, userZ;
90 ERROR 42000: There is no such grant defined for user 'userZ' on host '%' on table 'table1'
91 # userX's grants must not have changed
92 SHOW GRANTS FOR userX;
93 Grants for userX@%
94 GRANT USAGE ON *.* TO `userX`@`%`
95 GRANT SELECT, INSERT (`c1`, `c2`) ON `db1`.`table1` TO `userX`@`%`
96 # This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE SELECT ON.*db1.*table1.*FROM.*userX.*userZ)
97 include/assert_binlog_events.inc
98 # This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE INSERT(.*c1.*c2.*) ON .*db1.*table1.*FROM.*userX.*userZ.*)
99 include/assert_binlog_events.inc
100 # Case 2 : Revoke non-existing grants
101 REVOKE SELECT ON db1.table1 FROM userX, userY;
102 ERROR 42000: There is no such grant defined for user 'userY' on host '%' on table 'table1'
103 REVOKE INSERT(c1, c2) ON db1.table1 FROM userX, userY;
104 ERROR 42000: There is no such grant defined for user 'userY' on host '%' on table 'table1'
105 # userX's grants must not have changed
106 SHOW GRANTS FOR userX;
107 Grants for userX@%
108 GRANT USAGE ON *.* TO `userX`@`%`
109 GRANT SELECT, INSERT (`c1`, `c2`) ON `db1`.`table1` TO `userX`@`%`
110 # This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE SELECT ON.*db1.*table1.*FROM.*userX.*userY.*)
111 include/assert_binlog_events.inc
112 # This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE INSERT(.*c1.*c2.*) ON .*db1.*table1.* FROM.*userX.*userY.*)
113 include/assert_binlog_events.inc
114 # Case 3 : Valid revoke
115 REVOKE SELECT ON db1.table1 FROM userX, userA;
116 # This event sequence pattern MUST be present in binlog: !Q(REVOKE SELECT ON.*db1.*table1.*FROM.*userX.*userA.*)
117 include/assert_binlog_events.inc
118 include/save_binlog_position.inc
119 REVOKE INSERT(c1, c2) ON db1.table1 FROM userX, userA;
120 # This event sequence pattern MUST be present in binlog: !Q(REVOKE INSERT(.*c1.*c2.*) ON.*db1.*table1.*FROM.*userX.*userA.*)
121 include/assert_binlog_events.inc
122 # userX's grants must have changed
123 SHOW GRANTS FOR userX;
124 Grants for userX@%
125 GRANT USAGE ON *.* TO `userX`@`%`
126 DROP USER userX, userY, userA;
127 DROP TABLE db1.table1;
128 DROP DATABASE db1;
129 include/save_binlog_position.inc
130 # End : Tests for REVOKE on table and column privileges
131 # ----------------------------------------------------------------------
132 # Begin : Tests for REVOKE on procedure privileges
133 CREATE USER userX, userY, userA;
134 CREATE DATABASE db1;
135 CREATE PROCEDURE db1.proc1()
136 BEGIN
137 SELECT 1234;
138 END ||
139 GRANT EXECUTE ON PROCEDURE db1.proc1 to userX, userA;
140 include/save_binlog_position.inc
141 # Initial set of grants for userX
142 SHOW GRANTS FOR userX;
143 Grants for userX@%
144 GRANT USAGE ON *.* TO `userX`@`%`
145 GRANT EXECUTE ON PROCEDURE `db1`.`proc1` TO `userX`@`%`
146 Case 1 : Revoke from non-existing user
147 REVOKE EXECUTE ON PROCEDURE db1.proc1 FROM userX, userZ;
148 ERROR 42000: There is no such grant defined for user 'userZ' on host '%' on routine 'proc1'
149 # userX's grants must not have changed
150 SHOW GRANTS FOR userX;
151 Grants for userX@%
152 GRANT USAGE ON *.* TO `userX`@`%`
153 GRANT EXECUTE ON PROCEDURE `db1`.`proc1` TO `userX`@`%`
154 # This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE EXECUTE ON PROCEDURE.*db1.*proc1.*FROM.*userX.*userZ.*)
155 include/assert_binlog_events.inc
156 Case 2 : Revoke non-existing grants
157 REVOKE EXECUTE ON PROCEDURE db1.proc1 FROM userX, userY;
158 ERROR 42000: There is no such grant defined for user 'userY' on host '%' on routine 'proc1'
159 # userX's grants must not have changed
160 SHOW GRANTS FOR userX;
161 Grants for userX@%
162 GRANT USAGE ON *.* TO `userX`@`%`
163 GRANT EXECUTE ON PROCEDURE `db1`.`proc1` TO `userX`@`%`
164 # This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE EXECUTE ON PROCEDURE.*db1.*proc1.*FROM.*userX.*userY.*)
165 include/assert_binlog_events.inc
166 Case 3 : Valid revoke
167 REVOKE EXECUTE ON PROCEDURE db1.proc1 FROM userX, userA;
168 # userX's grants must have changed
169 SHOW GRANTS FOR userX;
170 Grants for userX@%
171 GRANT USAGE ON *.* TO `userX`@`%`
172 # This event sequence pattern MUST be present in binlog: !Q(REVOKE EXECUTE ON PROCEDURE.*db1.*proc1.*FROM.*userX.*userA.*)
173 include/assert_binlog_events.inc
174 DROP USER userX, userY, userA;
175 DROP PROCEDURE db1.proc1;
176 DROP DATABASE db1;
177 include/save_binlog_position.inc
178 # End : Tests for REVOKE on procedures privileges
179 # ----------------------------------------------------------------------
180 # Begin : Tests for REVOKE on roles
181 CREATE USER userX, userY, userA;
182 CREATE ROLE roleA, roleB;
183 GRANT roleA, roleB TO userX, userA;
184 include/save_binlog_position.inc
185 # Initial set of grants for userX
186 SHOW GRANTS FOR userX;
187 Grants for userX@%
188 GRANT USAGE ON *.* TO `userX`@`%`
189 GRANT `roleA`@`%`,`roleB`@`%` TO `userX`@`%`
190 # Case 1 : Revoke from non-existing user
191 REVOKE roleA, roleB FROM userX, userZ;
192 ERROR HY000: Unknown authorization ID `userZ`@`%`
193 # userX's grants must not have changed
194 SHOW GRANTS FOR userX;
195 Grants for userX@%
196 GRANT USAGE ON *.* TO `userX`@`%`
197 GRANT `roleA`@`%`,`roleB`@`%` TO `userX`@`%`
198 # This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE.*roleA.*,.*roleB.*FROM.*userX.*,.*userZ.*)
199 include/assert_binlog_events.inc
200 # Case 2 : Valid revoke
201 REVOKE roleA, roleB FROM userX, userA;
202 # userX's grants must have changed
203 SHOW GRANTS FOR userX;
204 Grants for userX@%
205 GRANT USAGE ON *.* TO `userX`@`%`
206 # This event sequence pattern MUST be present in binlog: !Q(REVOKE.*roleA.*,.*roleB.*FROM.*userX.*,.*userA.*)
207 include/assert_binlog_events.inc
208 DROP USER userX, userY, userA;
209 DROP ROLE roleA, roleB;
210 include/save_binlog_position.inc
211 # End : Tests for REVOKE on roles
212 # ----------------------------------------------------------------------
213 # Begin : Tests for REVOKE ALL
214 CREATE USER userX, userY, userA;
215 CREATE ROLE roleA, roleB;
216 CREATE DATABASE db1;
217 CREATE TABLE db1.table1(c1 int, c2 int, c3 int);
218 CREATE PROCEDURE db1.proc1()
219 BEGIN
220 SELECT 1234;
221 END ||
222 GRANT SUPER ON *.* TO userX, userA;
223 Warnings:
224 Warning 1287 The SUPER privilege identifier is deprecated
225 GRANT SELECT ON db1.* TO userX, userA;
226 GRANT PROXY ON userY TO userX, userA;
227 GRANT SELECT ON db1.table1 TO userX, userA;
228 GRANT INSERT(c1, c2) ON db1.table1 TO userX, userA;
229 GRANT EXECUTE ON PROCEDURE db1.proc1 to userX, userA;
230 GRANT roleA, roleB TO userX, userA;
231 include/save_binlog_position.inc
232 # Initial set of grants for userX
233 SHOW GRANTS FOR userX;
234 Grants for userX@%
235 GRANT SUPER ON *.* TO `userX`@`%`
236 GRANT SELECT ON `db1`.* TO `userX`@`%`
237 GRANT SELECT, INSERT (`c1`, `c2`) ON `db1`.`table1` TO `userX`@`%`
238 GRANT EXECUTE ON PROCEDURE `db1`.`proc1` TO `userX`@`%`
239 GRANT PROXY ON `userY`@`%` TO `userX`@`%`
240 GRANT `roleA`@`%`,`roleB`@`%` TO `userX`@`%`
241 # Case 1 : Revoke from non-existing user
242 REVOKE ALL PRIVILEGES, GRANT OPTION FROM userX, userZ;
243 ERROR HY000: Can't revoke all privileges for one or more of the requested users
244 # userX's grants must not have changed
245 SHOW GRANTS FOR userX;
246 Grants for userX@%
247 GRANT SUPER ON *.* TO `userX`@`%`
248 GRANT SELECT ON `db1`.* TO `userX`@`%`
249 GRANT SELECT, INSERT (`c1`, `c2`) ON `db1`.`table1` TO `userX`@`%`
250 GRANT EXECUTE ON PROCEDURE `db1`.`proc1` TO `userX`@`%`
251 GRANT PROXY ON `userY`@`%` TO `userX`@`%`
252 GRANT `roleA`@`%`,`roleB`@`%` TO `userX`@`%`
253 # This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE ALL PRIVILEGES, GRANT OPTION FROM.*userX.*,.*userZ.*)
254 include/assert_binlog_events.inc
255 # Case 2 : Valid revoke
256 REVOKE ALL PRIVILEGES, GRANT OPTION FROM userX, userA;
257 # userX's grants must have changed
258 SHOW GRANTS FOR userX;
259 Grants for userX@%
260 GRANT USAGE ON *.* TO `userX`@`%`
261 GRANT PROXY ON `userY`@`%` TO `userX`@`%`
262 GRANT `roleA`@`%`,`roleB`@`%` TO `userX`@`%`
263 # This event sequence pattern MUST be present in binlog: !Q(REVOKE ALL PRIVILEGES, GRANT OPTION FROM.*userX.*,.*userA.*)
264 include/assert_binlog_events.inc
265 DROP USER userX, userY, userA;
266 DROP ROLE roleA, roleB;
267 DROP TABLE db1.table1;
268 DROP PROCEDURE db1.proc1;
269 DROP DATABASE db1;
270 include/save_binlog_position.inc
271 # End : Tests for REVOKE ALL
272 # ----------------------------------------------------------------------