Debian Codegraph @

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

raw
1 include/save_binlog_position.inc
2 # ----------------------------------------------------------------------
3 # Begin : Tests for GRANT : Table grants
4 CREATE USER userX, userY, userZ;
5 CREATE DATABASE db1;
6 CREATE TABLE db1.table1(c1 int, c2 int, c3 int);
7 include/save_binlog_position.inc
8 # Case 1 : Valid grants to multiple users
9 GRANT SELECT ON db1.table1 TO userX, userY, userZ;
10 # Must show new grants
11 SHOW GRANTS FOR userX;
12 Grants for userX@%
13 GRANT USAGE ON *.* TO `userX`@`%`
14 GRANT SELECT ON `db1`.`table1` TO `userX`@`%`
15 SHOW GRANTS FOR userY;
16 Grants for userY@%
17 GRANT USAGE ON *.* TO `userY`@`%`
18 GRANT SELECT ON `db1`.`table1` TO `userY`@`%`
19 SHOW GRANTS FOR userZ;
20 Grants for userZ@%
21 GRANT USAGE ON *.* TO `userZ`@`%`
22 GRANT SELECT ON `db1`.`table1` TO `userZ`@`%`
23 # This event sequence pattern MUST be present in binlog: !Q(GRANT SELECT ON.*db1.*table1.*TO.*userX.*userY.*userZ.*)
24 include/assert_binlog_events.inc
25 include/save_binlog_position.inc
26 # Update existing grant
27 GRANT INSERT , UPDATE ON db1.table1 TO userX, userY, userZ;
28 # Must show updated grants
29 SHOW GRANTS FOR userX;
30 Grants for userX@%
31 GRANT USAGE ON *.* TO `userX`@`%`
32 GRANT SELECT, INSERT, UPDATE ON `db1`.`table1` TO `userX`@`%`
33 SHOW GRANTS FOR userY;
34 Grants for userY@%
35 GRANT USAGE ON *.* TO `userY`@`%`
36 GRANT SELECT, INSERT, UPDATE ON `db1`.`table1` TO `userY`@`%`
37 SHOW GRANTS FOR userZ;
38 Grants for userZ@%
39 GRANT USAGE ON *.* TO `userZ`@`%`
40 GRANT SELECT, INSERT, UPDATE ON `db1`.`table1` TO `userZ`@`%`
41 # This event sequence pattern MUST be present in binlog: !Q(GRANT INSERT, UPDATE ON.*db1.*table1.*TO.*userX.*userY.*userZ.*)
42 include/assert_binlog_events.inc
43 include/save_binlog_position.inc
44 # Case 6 : Create new user with an account without CREATE USER privilege
45 CREATE USER userA, userB;
46 GRANT SELECT ON db1.table1 to userX WITH GRANT OPTION;
47 include/save_binlog_position.inc
48 SHOW GRANTS FOR userY;
49 Grants for userY@%
50 GRANT USAGE ON *.* TO `userY`@`%`
51 GRANT SELECT, INSERT, UPDATE ON `db1`.`table1` TO `userY`@`%`
52 SHOW GRANTS FOR userZ;
53 Grants for userZ@%
54 GRANT USAGE ON *.* TO `userZ`@`%`
55 GRANT SELECT, INSERT, UPDATE ON `db1`.`table1` TO `userZ`@`%`
56 SHOW GRANTS FOR userA;
57 Grants for userA@%
58 GRANT USAGE ON *.* TO `userA`@`%`
59 SHOW GRANTS FOR userB;
60 Grants for userB@%
61 GRANT USAGE ON *.* TO `userB`@`%`
62 # should succeed
63 GRANT SELECT ON db1.table1 TO userY, userZ;
64 # should fail
65 GRANT SELECT ON db1.table1 TO userA, userB, userC;
66 ERROR 42000: You are not allowed to create a user with GRANT
67 # Must show updated grants
68 SHOW GRANTS FOR userY;
69 Grants for userY@%
70 GRANT USAGE ON *.* TO `userY`@`%`
71 GRANT SELECT, INSERT, UPDATE ON `db1`.`table1` TO `userY`@`%`
72 SHOW GRANTS FOR userZ;
73 Grants for userZ@%
74 GRANT USAGE ON *.* TO `userZ`@`%`
75 GRANT SELECT, INSERT, UPDATE ON `db1`.`table1` TO `userZ`@`%`
76 # Must not have any change
77 SHOW GRANTS FOR userA;
78 Grants for userA@%
79 GRANT USAGE ON *.* TO `userA`@`%`
80 SHOW GRANTS FOR userB;
81 Grants for userB@%
82 GRANT USAGE ON *.* TO `userB`@`%`
83 # This event sequence pattern MUST be present in binlog: !Q(GRANT SELECT ON.*db1.*table1.*TO.*userY.*userZ.*)
84 include/assert_binlog_events.inc
85 DROP USER userX, userY, userZ, userA, userB;
86 DROP TABLE db1.table1;
87 DROP DATABASE db1;
88 include/save_binlog_position.inc
89 # End : Tests for GRANT : Table grants
90 # ----------------------------------------------------------------------
91 # Begin : Tests for GRANT : Column grants
92 CREATE USER userX, userY, userZ;
93 CREATE DATABASE db1;
94 CREATE TABLE db1.table1(c1 int, c2 int, c3 int);
95 include/save_binlog_position.inc
96 # Case 1 : Valid grants to multiple users
97 GRANT SELECT(c1, c2) ON db1.table1 TO userX, userY, userZ;
98 # Must show new grants
99 SHOW GRANTS FOR userX;
100 Grants for userX@%
101 GRANT USAGE ON *.* TO `userX`@`%`
102 GRANT SELECT (`c1`, `c2`) ON `db1`.`table1` TO `userX`@`%`
103 SHOW GRANTS FOR userY;
104 Grants for userY@%
105 GRANT USAGE ON *.* TO `userY`@`%`
106 GRANT SELECT (`c1`, `c2`) ON `db1`.`table1` TO `userY`@`%`
107 SHOW GRANTS FOR userZ;
108 Grants for userZ@%
109 GRANT USAGE ON *.* TO `userZ`@`%`
110 GRANT SELECT (`c1`, `c2`) ON `db1`.`table1` TO `userZ`@`%`
111 # This event sequence pattern MUST be present in binlog: !Q(GRANT SELECT(.*c1.*c2.*) ON.*db1.*table1.*TO.*userX.*userY.*userZ.*)
112 include/assert_binlog_events.inc
113 include/save_binlog_position.inc
114 # Update existing grant
115 GRANT INSERT(c1, c3), UPDATE(c2, c3) ON db1.table1 TO userX, userY, userZ;
116 # Must show updated grants
117 SHOW GRANTS FOR userX;
118 Grants for userX@%
119 GRANT USAGE ON *.* TO `userX`@`%`
120 GRANT SELECT (`c1`, `c2`), INSERT (`c1`, `c3`), UPDATE (`c2`, `c3`) ON `db1`.`table1` TO `userX`@`%`
121 SHOW GRANTS FOR userY;
122 Grants for userY@%
123 GRANT USAGE ON *.* TO `userY`@`%`
124 GRANT SELECT (`c1`, `c2`), INSERT (`c1`, `c3`), UPDATE (`c2`, `c3`) ON `db1`.`table1` TO `userY`@`%`
125 SHOW GRANTS FOR userZ;
126 Grants for userZ@%
127 GRANT USAGE ON *.* TO `userZ`@`%`
128 GRANT SELECT (`c1`, `c2`), INSERT (`c1`, `c3`), UPDATE (`c2`, `c3`) ON `db1`.`table1` TO `userZ`@`%`
129 # This event sequence pattern MUST be present in binlog: !Q(GRANT INSERT (.*), UPDATE (.*) ON.*db1.*table1.*TO.*userX.*userY.*userZ.*)
130 include/assert_binlog_events.inc
131 include/save_binlog_position.inc
132 # Case 6 : Create new user with an account without CREATE USER privilege
133 CREATE USER userA, userB;
134 GRANT SELECT(c1, c3) ON db1.table1 to userX WITH GRANT OPTION;
135 include/save_binlog_position.inc
136 SHOW GRANTS FOR userY;
137 Grants for userY@%
138 GRANT USAGE ON *.* TO `userY`@`%`
139 GRANT SELECT (`c1`, `c2`), INSERT (`c1`, `c3`), UPDATE (`c2`, `c3`) ON `db1`.`table1` TO `userY`@`%`
140 SHOW GRANTS FOR userZ;
141 Grants for userZ@%
142 GRANT USAGE ON *.* TO `userZ`@`%`
143 GRANT SELECT (`c1`, `c2`), INSERT (`c1`, `c3`), UPDATE (`c2`, `c3`) ON `db1`.`table1` TO `userZ`@`%`
144 SHOW GRANTS FOR userA;
145 Grants for userA@%
146 GRANT USAGE ON *.* TO `userA`@`%`
147 SHOW GRANTS FOR userB;
148 Grants for userB@%
149 GRANT USAGE ON *.* TO `userB`@`%`
150 # should succeed
151 GRANT SELECT(c1) ON db1.table1 TO userY, userZ;
152 # should fail
153 GRANT SELECT(c1) ON db1.table1 TO userA, userB, userC;
154 ERROR 42000: You are not allowed to create a user with GRANT
155 # Must show updated grants
156 SHOW GRANTS FOR userY;
157 Grants for userY@%
158 GRANT USAGE ON *.* TO `userY`@`%`
159 GRANT SELECT (`c1`, `c2`), INSERT (`c1`, `c3`), UPDATE (`c2`, `c3`) ON `db1`.`table1` TO `userY`@`%`
160 SHOW GRANTS FOR userZ;
161 Grants for userZ@%
162 GRANT USAGE ON *.* TO `userZ`@`%`
163 GRANT SELECT (`c1`, `c2`), INSERT (`c1`, `c3`), UPDATE (`c2`, `c3`) ON `db1`.`table1` TO `userZ`@`%`
164 # Must not have any change
165 SHOW GRANTS FOR userA;
166 Grants for userA@%
167 GRANT USAGE ON *.* TO `userA`@`%`
168 SHOW GRANTS FOR userB;
169 Grants for userB@%
170 GRANT USAGE ON *.* TO `userB`@`%`
171 # This event sequence pattern MUST be present in binlog: !Q(GRANT SELECT (.*) ON.*db1.*table1.*TO.*userY.*userZ.*)
172 include/assert_binlog_events.inc
173 DROP USER userX, userY, userZ, userA, userB;
174 DROP TABLE db1.table1;
175 DROP DATABASE db1;
176 include/save_binlog_position.inc
177 # End : Tests for GRANT : Column grants
178 # ----------------------------------------------------------------------
179 # Begin : Tests for GRANT : Table and Column grants
180 CREATE USER userX, userY, userZ;
181 CREATE DATABASE db1;
182 CREATE TABLE db1.table1(c1 int, c2 int, c3 int);
183 include/save_binlog_position.inc
184 # Case 1 : Valid grants to multiple users
185 GRANT SELECT ON db1.table1 TO userX, userY, userZ;
186 # Must show new grants
187 SHOW GRANTS FOR userX;
188 Grants for userX@%
189 GRANT USAGE ON *.* TO `userX`@`%`
190 GRANT SELECT ON `db1`.`table1` TO `userX`@`%`
191 SHOW GRANTS FOR userY;
192 Grants for userY@%
193 GRANT USAGE ON *.* TO `userY`@`%`
194 GRANT SELECT ON `db1`.`table1` TO `userY`@`%`
195 SHOW GRANTS FOR userZ;
196 Grants for userZ@%
197 GRANT USAGE ON *.* TO `userZ`@`%`
198 GRANT SELECT ON `db1`.`table1` TO `userZ`@`%`
199 # This event sequence pattern MUST be present in binlog: !Q(GRANT SELECT ON.*db1.*table1.*TO.*userX.*userY.*userZ.*)
200 include/assert_binlog_events.inc
201 include/save_binlog_position.inc
202 # Update existing grant
203 GRANT INSERT(c1, c3) , UPDATE ON db1.table1 TO userX, userY, userZ;
204 # Must show updated grants
205 SHOW GRANTS FOR userX;
206 Grants for userX@%
207 GRANT USAGE ON *.* TO `userX`@`%`
208 GRANT SELECT, INSERT (`c1`, `c3`), UPDATE ON `db1`.`table1` TO `userX`@`%`
209 SHOW GRANTS FOR userY;
210 Grants for userY@%
211 GRANT USAGE ON *.* TO `userY`@`%`
212 GRANT SELECT, INSERT (`c1`, `c3`), UPDATE ON `db1`.`table1` TO `userY`@`%`
213 SHOW GRANTS FOR userZ;
214 Grants for userZ@%
215 GRANT USAGE ON *.* TO `userZ`@`%`
216 GRANT SELECT, INSERT (`c1`, `c3`), UPDATE ON `db1`.`table1` TO `userZ`@`%`
217 # This event sequence pattern MUST be present in binlog: !Q(GRANT INSERT (.*), UPDATE ON.*db1.*table1.*TO.*userX.*userY.*userZ.*)
218 include/assert_binlog_events.inc
219 include/save_binlog_position.inc
220 # Case 6 : Create new user with an account without CREATE USER privilege
221 CREATE USER userA, userB;
222 GRANT SELECT(c1, c3), INSERT ON db1.table1 to userX WITH GRANT OPTION;
223 include/save_binlog_position.inc
224 SHOW GRANTS FOR userY;
225 Grants for userY@%
226 GRANT USAGE ON *.* TO `userY`@`%`
227 GRANT SELECT, INSERT (`c1`, `c3`), UPDATE ON `db1`.`table1` TO `userY`@`%`
228 SHOW GRANTS FOR userZ;
229 Grants for userZ@%
230 GRANT USAGE ON *.* TO `userZ`@`%`
231 GRANT SELECT, INSERT (`c1`, `c3`), UPDATE ON `db1`.`table1` TO `userZ`@`%`
232 SHOW GRANTS FOR userA;
233 Grants for userA@%
234 GRANT USAGE ON *.* TO `userA`@`%`
235 SHOW GRANTS FOR userB;
236 Grants for userB@%
237 GRANT USAGE ON *.* TO `userB`@`%`
238 # should succeed
239 GRANT SELECT(c1), INSERT ON db1.table1 TO userY, userZ;
240 # should fail
241 GRANT SELECT(c1), INSERT ON db1.table1 TO userA, userB, userC;
242 ERROR 42000: You are not allowed to create a user with GRANT
243 # Must show updated grants
244 SHOW GRANTS FOR userY;
245 Grants for userY@%
246 GRANT USAGE ON *.* TO `userY`@`%`
247 GRANT SELECT, SELECT (`c1`), INSERT, INSERT (`c1`, `c3`), UPDATE ON `db1`.`table1` TO `userY`@`%`
248 SHOW GRANTS FOR userZ;
249 Grants for userZ@%
250 GRANT USAGE ON *.* TO `userZ`@`%`
251 GRANT SELECT, SELECT (`c1`), INSERT, INSERT (`c1`, `c3`), UPDATE ON `db1`.`table1` TO `userZ`@`%`
252 # Must not have any change
253 SHOW GRANTS FOR userA;
254 Grants for userA@%
255 GRANT USAGE ON *.* TO `userA`@`%`
256 SHOW GRANTS FOR userB;
257 Grants for userB@%
258 GRANT USAGE ON *.* TO `userB`@`%`
259 # This event sequence pattern MUST be present in binlog: !Q(GRANT SELECT (.*), INSERT ON.*db1.*table1.*TO.*userY.*userZ.*)
260 include/assert_binlog_events.inc
261 DROP USER userX, userY, userZ, userA, userB;
262 DROP TABLE db1.table1;
263 DROP DATABASE db1;
264 include/save_binlog_position.inc
265 # End : Tests for GRANT : Table and Column grants
266 # ----------------------------------------------------------------------