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
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
|
include/save_binlog_position.inc
# ----------------------------------------------------------------------
# Begin : Tests for REVOKE on global, db and proxy privileges
CREATE USER userX, userY, userA;
CREATE DATABASE db1;
GRANT SUPER ON *.* TO userX, userA;
Warnings:
Warning 1287 The SUPER privilege identifier is deprecated
GRANT SELECT ON db1.* TO userX, userA;
GRANT PROXY ON userY TO userX, userA;
include/save_binlog_position.inc
# Initial set of grants for userX
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT SUPER ON *.* TO `userX`@`%`
GRANT SELECT ON `db1`.* TO `userX`@`%`
GRANT PROXY ON `userY`@`%` TO `userX`@`%`
# Case 1 : Revoke from non-existing user
REVOKE SUPER ON *.* FROM useX, userZ;
ERROR 42000: There is no such grant defined for user 'useX' on host '%'
REVOKE SELECT ON db1.* FROM userX, userZ;
ERROR 42000: There is no such grant defined for user 'userZ' on host '%'
REVOKE PROXY ON userY FROM userX, userZ;
ERROR 42000: There is no such grant defined for user 'userZ' on host '%'
# userX's grants must not have changed
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT SUPER ON *.* TO `userX`@`%`
GRANT SELECT ON `db1`.* TO `userX`@`%`
GRANT PROXY ON `userY`@`%` TO `userX`@`%`
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE SUPER ON.*FROM.*userX.*userZ.*)
include/assert_binlog_events.inc
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE SELCT ON.*db1.*FROM.*userX.*userZ.*)
include/assert_binlog_events.inc
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE PROXY ON .*userY.*FROM.*userX.*userZ.*)
include/assert_binlog_events.inc
# Case 2 : Revoke non-existing grants
REVOKE SELECT ON db1.* FROM userY;
ERROR 42000: There is no such grant defined for user 'userY' on host '%'
REVOKE PROXY ON userX FROM userY;
ERROR 42000: There is no such grant defined for user 'userY' on host '%'
# userX's grants must not have changed
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT SUPER ON *.* TO `userX`@`%`
GRANT SELECT ON `db1`.* TO `userX`@`%`
GRANT PROXY ON `userY`@`%` TO `userX`@`%`
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE SELECT ON .*db1.*FROM.*userY.*)
include/assert_binlog_events.inc
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE PROXY ON .*userX.*FROM.*userY.*)
include/assert_binlog_events.inc
# Case 3 : Valid revoke
REVOKE SUPER ON *.* FROM userX, userA;
Warnings:
Warning 1287 The SUPER privilege identifier is deprecated
# This event sequence pattern MUST be present in binlog: !Q(REVOKE SUPER ON *.* FROM.*userX.*userA.*)
include/assert_binlog_events.inc
include/save_binlog_position.inc
REVOKE SELECT ON db1.* FROM userX, userA;
# This event sequence pattern MUST be present in binlog: !Q(REVOKE SELECT ON .*db1.*FROM.*userX.*userA.*)
include/assert_binlog_events.inc
include/save_binlog_position.inc
REVOKE PROXY ON userY FROM userX, userA;
# This event sequence pattern MUST be present in binlog: !Q(REVOKE PROXY ON.*userY.*FROM.*userX.*userA.*)
include/assert_binlog_events.inc
# userX's grants must have changed
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
DROP USER userX, userY, userA;
DROP DATABASE db1;
# End : Tests for REVOKE on global, db and proxy privileges
# ----------------------------------------------------------------------
# Begin : Tests for REVOKE on table and column privileges
CREATE USER userX, userY, userA;
CREATE DATABASE db1;
CREATE TABLE db1.table1(c1 int, c2 int, c3 int);
GRANT SELECT ON db1.table1 TO userX, userA;
GRANT INSERT(c1, c2) ON db1.table1 TO userX, userA;
include/save_binlog_position.inc
# Initial set of grants for userX
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
GRANT SELECT, INSERT (`c1`, `c2`) ON `db1`.`table1` TO `userX`@`%`
# Case 1 : Revoke from non-existing user
REVOKE SELECT ON db1.table1 FROM userX, userZ;
ERROR 42000: There is no such grant defined for user 'userZ' on host '%' on table 'table1'
REVOKE INSERT(c1, c2) ON db1.table1 FROM userX, userZ;
ERROR 42000: There is no such grant defined for user 'userZ' on host '%' on table 'table1'
# userX's grants must not have changed
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
GRANT SELECT, INSERT (`c1`, `c2`) ON `db1`.`table1` TO `userX`@`%`
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE SELECT ON.*db1.*table1.*FROM.*userX.*userZ)
include/assert_binlog_events.inc
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE INSERT(.*c1.*c2.*) ON .*db1.*table1.*FROM.*userX.*userZ.*)
include/assert_binlog_events.inc
# Case 2 : Revoke non-existing grants
REVOKE SELECT ON db1.table1 FROM userX, userY;
ERROR 42000: There is no such grant defined for user 'userY' on host '%' on table 'table1'
REVOKE INSERT(c1, c2) ON db1.table1 FROM userX, userY;
ERROR 42000: There is no such grant defined for user 'userY' on host '%' on table 'table1'
# userX's grants must not have changed
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
GRANT SELECT, INSERT (`c1`, `c2`) ON `db1`.`table1` TO `userX`@`%`
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE SELECT ON.*db1.*table1.*FROM.*userX.*userY.*)
include/assert_binlog_events.inc
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE INSERT(.*c1.*c2.*) ON .*db1.*table1.* FROM.*userX.*userY.*)
include/assert_binlog_events.inc
# Case 3 : Valid revoke
REVOKE SELECT ON db1.table1 FROM userX, userA;
# This event sequence pattern MUST be present in binlog: !Q(REVOKE SELECT ON.*db1.*table1.*FROM.*userX.*userA.*)
include/assert_binlog_events.inc
include/save_binlog_position.inc
REVOKE INSERT(c1, c2) ON db1.table1 FROM userX, userA;
# This event sequence pattern MUST be present in binlog: !Q(REVOKE INSERT(.*c1.*c2.*) ON.*db1.*table1.*FROM.*userX.*userA.*)
include/assert_binlog_events.inc
# userX's grants must have changed
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
DROP USER userX, userY, userA;
DROP TABLE db1.table1;
DROP DATABASE db1;
include/save_binlog_position.inc
# End : Tests for REVOKE on table and column privileges
# ----------------------------------------------------------------------
# Begin : Tests for REVOKE on procedure privileges
CREATE USER userX, userY, userA;
CREATE DATABASE db1;
CREATE PROCEDURE db1.proc1()
BEGIN
SELECT 1234;
END ||
GRANT EXECUTE ON PROCEDURE db1.proc1 to userX, userA;
include/save_binlog_position.inc
# Initial set of grants for userX
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
GRANT EXECUTE ON PROCEDURE `db1`.`proc1` TO `userX`@`%`
Case 1 : Revoke from non-existing user
REVOKE EXECUTE ON PROCEDURE db1.proc1 FROM userX, userZ;
ERROR 42000: There is no such grant defined for user 'userZ' on host '%' on routine 'proc1'
# userX's grants must not have changed
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
GRANT EXECUTE ON PROCEDURE `db1`.`proc1` TO `userX`@`%`
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE EXECUTE ON PROCEDURE.*db1.*proc1.*FROM.*userX.*userZ.*)
include/assert_binlog_events.inc
Case 2 : Revoke non-existing grants
REVOKE EXECUTE ON PROCEDURE db1.proc1 FROM userX, userY;
ERROR 42000: There is no such grant defined for user 'userY' on host '%' on routine 'proc1'
# userX's grants must not have changed
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
GRANT EXECUTE ON PROCEDURE `db1`.`proc1` TO `userX`@`%`
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE EXECUTE ON PROCEDURE.*db1.*proc1.*FROM.*userX.*userY.*)
include/assert_binlog_events.inc
Case 3 : Valid revoke
REVOKE EXECUTE ON PROCEDURE db1.proc1 FROM userX, userA;
# userX's grants must have changed
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
# This event sequence pattern MUST be present in binlog: !Q(REVOKE EXECUTE ON PROCEDURE.*db1.*proc1.*FROM.*userX.*userA.*)
include/assert_binlog_events.inc
DROP USER userX, userY, userA;
DROP PROCEDURE db1.proc1;
DROP DATABASE db1;
include/save_binlog_position.inc
# End : Tests for REVOKE on procedures privileges
# ----------------------------------------------------------------------
# Begin : Tests for REVOKE on roles
CREATE USER userX, userY, userA;
CREATE ROLE roleA, roleB;
GRANT roleA, roleB TO userX, userA;
include/save_binlog_position.inc
# Initial set of grants for userX
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
GRANT `roleA`@`%`,`roleB`@`%` TO `userX`@`%`
# Case 1 : Revoke from non-existing user
REVOKE roleA, roleB FROM userX, userZ;
ERROR HY000: Unknown authorization ID `userZ`@`%`
# userX's grants must not have changed
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
GRANT `roleA`@`%`,`roleB`@`%` TO `userX`@`%`
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE.*roleA.*,.*roleB.*FROM.*userX.*,.*userZ.*)
include/assert_binlog_events.inc
# Case 2 : Valid revoke
REVOKE roleA, roleB FROM userX, userA;
# userX's grants must have changed
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
# This event sequence pattern MUST be present in binlog: !Q(REVOKE.*roleA.*,.*roleB.*FROM.*userX.*,.*userA.*)
include/assert_binlog_events.inc
DROP USER userX, userY, userA;
DROP ROLE roleA, roleB;
include/save_binlog_position.inc
# End : Tests for REVOKE on roles
# ----------------------------------------------------------------------
# Begin : Tests for REVOKE ALL
CREATE USER userX, userY, userA;
CREATE ROLE roleA, roleB;
CREATE DATABASE db1;
CREATE TABLE db1.table1(c1 int, c2 int, c3 int);
CREATE PROCEDURE db1.proc1()
BEGIN
SELECT 1234;
END ||
GRANT SUPER ON *.* TO userX, userA;
Warnings:
Warning 1287 The SUPER privilege identifier is deprecated
GRANT SELECT ON db1.* TO userX, userA;
GRANT PROXY ON userY TO userX, userA;
GRANT SELECT ON db1.table1 TO userX, userA;
GRANT INSERT(c1, c2) ON db1.table1 TO userX, userA;
GRANT EXECUTE ON PROCEDURE db1.proc1 to userX, userA;
GRANT roleA, roleB TO userX, userA;
include/save_binlog_position.inc
# Initial set of grants for userX
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT SUPER ON *.* TO `userX`@`%`
GRANT SELECT ON `db1`.* TO `userX`@`%`
GRANT SELECT, INSERT (`c1`, `c2`) ON `db1`.`table1` TO `userX`@`%`
GRANT EXECUTE ON PROCEDURE `db1`.`proc1` TO `userX`@`%`
GRANT PROXY ON `userY`@`%` TO `userX`@`%`
GRANT `roleA`@`%`,`roleB`@`%` TO `userX`@`%`
# Case 1 : Revoke from non-existing user
REVOKE ALL PRIVILEGES, GRANT OPTION FROM userX, userZ;
ERROR HY000: Can't revoke all privileges for one or more of the requested users
# userX's grants must not have changed
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT SUPER ON *.* TO `userX`@`%`
GRANT SELECT ON `db1`.* TO `userX`@`%`
GRANT SELECT, INSERT (`c1`, `c2`) ON `db1`.`table1` TO `userX`@`%`
GRANT EXECUTE ON PROCEDURE `db1`.`proc1` TO `userX`@`%`
GRANT PROXY ON `userY`@`%` TO `userX`@`%`
GRANT `roleA`@`%`,`roleB`@`%` TO `userX`@`%`
# This event sequence pattern MUST NOT be present in binlog: !Q(REVOKE ALL PRIVILEGES, GRANT OPTION FROM.*userX.*,.*userZ.*)
include/assert_binlog_events.inc
# Case 2 : Valid revoke
REVOKE ALL PRIVILEGES, GRANT OPTION FROM userX, userA;
# userX's grants must have changed
SHOW GRANTS FOR userX;
Grants for userX@%
GRANT USAGE ON *.* TO `userX`@`%`
GRANT PROXY ON `userY`@`%` TO `userX`@`%`
GRANT `roleA`@`%`,`roleB`@`%` TO `userX`@`%`
# This event sequence pattern MUST be present in binlog: !Q(REVOKE ALL PRIVILEGES, GRANT OPTION FROM.*userX.*,.*userA.*)
include/assert_binlog_events.inc
DROP USER userX, userY, userA;
DROP ROLE roleA, roleB;
DROP TABLE db1.table1;
DROP PROCEDURE db1.proc1;
DROP DATABASE db1;
include/save_binlog_position.inc
# End : Tests for REVOKE ALL
# ----------------------------------------------------------------------
|
