Debian Codegraph @

mysql-test/suite/auth_sec/t/atomic_table_column_grants.test

raw
1 --source include/have_log_bin.inc
2 --source include/save_binlog_position.inc
3
4 # Save the initial number of concurrent sessions
5 --source include/count_sessions.inc
6
7 --echo # ----------------------------------------------------------------------
8
9 --echo # Begin : Tests for GRANT : Table grants
10
11 CREATE USER userX, userY, userZ;
12 CREATE DATABASE db1;
13 CREATE TABLE db1.table1(c1 int, c2 int, c3 int);
14 --source include/save_binlog_position.inc
15
16 --echo # Case 1 : Valid grants to multiple users
17 GRANT SELECT ON db1.table1 TO userX, userY, userZ;
18
19 --echo # Must show new grants
20 SHOW GRANTS FOR userX;
21 SHOW GRANTS FOR userY;
22 SHOW GRANTS FOR userZ;
23
24 --let $event= !Q(GRANT SELECT ON.*db1.*table1.*TO.*userX.*userY.*userZ.*)
25 --source ../include/auth_sec_assert_binlog_events.inc
26 --source include/save_binlog_position.inc
27
28 --echo # Update existing grant
29 GRANT INSERT , UPDATE ON db1.table1 TO userX, userY, userZ;
30
31 --echo # Must show updated grants
32 SHOW GRANTS FOR userX;
33 SHOW GRANTS FOR userY;
34 SHOW GRANTS FOR userZ;
35
36 --let $event= !Q(GRANT INSERT, UPDATE ON.*db1.*table1.*TO.*userX.*userY.*userZ.*)
37 --source ../include/auth_sec_assert_binlog_events.inc
38 --source include/save_binlog_position.inc
39
40 --echo # Case 6 : Create new user with an account without CREATE USER privilege
41
42 CREATE USER userA, userB;
43 GRANT SELECT ON db1.table1 to userX WITH GRANT OPTION;
44 --source include/save_binlog_position.inc
45
46 SHOW GRANTS FOR userY;
47 SHOW GRANTS FOR userZ;
48 SHOW GRANTS FOR userA;
49 SHOW GRANTS FOR userB;
50
51 connect(conn_userX, localhost, userX,,,,,);
52 connection conn_userX;
53
54 --echo # should succeed
55 GRANT SELECT ON db1.table1 TO userY, userZ;
56
57 --echo # should fail
58 --error ER_CANT_CREATE_USER_WITH_GRANT
59 GRANT SELECT ON db1.table1 TO userA, userB, userC;
60
61 connection default;
62 disconnect conn_userX;
63
64 --echo # Must show updated grants
65
66 SHOW GRANTS FOR userY;
67 SHOW GRANTS FOR userZ;
68
69 --echo # Must not have any change
70 SHOW GRANTS FOR userA;
71 SHOW GRANTS FOR userB;
72
73 --let $event= !Q(GRANT SELECT ON.*db1.*table1.*TO.*userY.*userZ.*)
74 --source ../include/auth_sec_assert_binlog_events.inc
75
76 DROP USER userX, userY, userZ, userA, userB;
77 DROP TABLE db1.table1;
78 DROP DATABASE db1;
79 --source include/save_binlog_position.inc
80 --echo # End : Tests for GRANT : Table grants
81
82 --echo # ----------------------------------------------------------------------
83
84 --echo # Begin : Tests for GRANT : Column grants
85
86 CREATE USER userX, userY, userZ;
87 CREATE DATABASE db1;
88 CREATE TABLE db1.table1(c1 int, c2 int, c3 int);
89 --source include/save_binlog_position.inc
90
91 --echo # Case 1 : Valid grants to multiple users
92 GRANT SELECT(c1, c2) ON db1.table1 TO userX, userY, userZ;
93
94 --echo # Must show new grants
95 SHOW GRANTS FOR userX;
96 SHOW GRANTS FOR userY;
97 SHOW GRANTS FOR userZ;
98
99 --let $event= !Q(GRANT SELECT(.*c1.*c2.*) ON.*db1.*table1.*TO.*userX.*userY.*userZ.*)
100 --source ../include/auth_sec_assert_binlog_events.inc
101 --source include/save_binlog_position.inc
102
103 --echo # Update existing grant
104 GRANT INSERT(c1, c3), UPDATE(c2, c3) ON db1.table1 TO userX, userY, userZ;
105
106 --echo # Must show updated grants
107 SHOW GRANTS FOR userX;
108 SHOW GRANTS FOR userY;
109 SHOW GRANTS FOR userZ;
110
111 --let $event= !Q(GRANT INSERT (.*), UPDATE (.*) ON.*db1.*table1.*TO.*userX.*userY.*userZ.*)
112 --source ../include/auth_sec_assert_binlog_events.inc
113 --source include/save_binlog_position.inc
114
115 --echo # Case 6 : Create new user with an account without CREATE USER privilege
116
117 CREATE USER userA, userB;
118 GRANT SELECT(c1, c3) ON db1.table1 to userX WITH GRANT OPTION;
119 --source include/save_binlog_position.inc
120
121 SHOW GRANTS FOR userY;
122 SHOW GRANTS FOR userZ;
123 SHOW GRANTS FOR userA;
124 SHOW GRANTS FOR userB;
125
126 connect(conn_userX, localhost, userX,,,,,);
127 connection conn_userX;
128
129 --echo # should succeed
130 GRANT SELECT(c1) ON db1.table1 TO userY, userZ;
131
132 --echo # should fail
133 --error ER_CANT_CREATE_USER_WITH_GRANT
134 GRANT SELECT(c1) ON db1.table1 TO userA, userB, userC;
135
136 connection default;
137 disconnect conn_userX;
138
139 --echo # Must show updated grants
140
141 SHOW GRANTS FOR userY;
142 SHOW GRANTS FOR userZ;
143
144 --echo # Must not have any change
145 SHOW GRANTS FOR userA;
146 SHOW GRANTS FOR userB;
147
148 --let $event= !Q(GRANT SELECT (.*) ON.*db1.*table1.*TO.*userY.*userZ.*)
149 --source ../include/auth_sec_assert_binlog_events.inc
150
151 DROP USER userX, userY, userZ, userA, userB;
152 DROP TABLE db1.table1;
153 DROP DATABASE db1;
154 --source include/save_binlog_position.inc
155 --echo # End : Tests for GRANT : Column grants
156
157 --echo # ----------------------------------------------------------------------
158
159 --echo # Begin : Tests for GRANT : Table and Column grants
160
161 CREATE USER userX, userY, userZ;
162 CREATE DATABASE db1;
163 CREATE TABLE db1.table1(c1 int, c2 int, c3 int);
164 --source include/save_binlog_position.inc
165
166 --echo # Case 1 : Valid grants to multiple users
167 GRANT SELECT ON db1.table1 TO userX, userY, userZ;
168
169 --echo # Must show new grants
170 SHOW GRANTS FOR userX;
171 SHOW GRANTS FOR userY;
172 SHOW GRANTS FOR userZ;
173
174 --let $event= !Q(GRANT SELECT ON.*db1.*table1.*TO.*userX.*userY.*userZ.*)
175 --source ../include/auth_sec_assert_binlog_events.inc
176 --source include/save_binlog_position.inc
177
178 --echo # Update existing grant
179 GRANT INSERT(c1, c3) , UPDATE ON db1.table1 TO userX, userY, userZ;
180
181 --echo # Must show updated grants
182 SHOW GRANTS FOR userX;
183 SHOW GRANTS FOR userY;
184 SHOW GRANTS FOR userZ;
185
186 --let $event= !Q(GRANT INSERT (.*), UPDATE ON.*db1.*table1.*TO.*userX.*userY.*userZ.*)
187 --source ../include/auth_sec_assert_binlog_events.inc
188 --source include/save_binlog_position.inc
189
190 --echo # Case 6 : Create new user with an account without CREATE USER privilege
191
192 CREATE USER userA, userB;
193 GRANT SELECT(c1, c3), INSERT ON db1.table1 to userX WITH GRANT OPTION;
194 --source include/save_binlog_position.inc
195
196 SHOW GRANTS FOR userY;
197 SHOW GRANTS FOR userZ;
198 SHOW GRANTS FOR userA;
199 SHOW GRANTS FOR userB;
200
201 connect(conn_userX, localhost, userX,,,,,);
202 connection conn_userX;
203
204 --echo # should succeed
205 GRANT SELECT(c1), INSERT ON db1.table1 TO userY, userZ;
206
207 --echo # should fail
208 --error ER_CANT_CREATE_USER_WITH_GRANT
209 GRANT SELECT(c1), INSERT ON db1.table1 TO userA, userB, userC;
210
211 connection default;
212 disconnect conn_userX;
213
214 --echo # Must show updated grants
215
216 SHOW GRANTS FOR userY;
217 SHOW GRANTS FOR userZ;
218
219 --echo # Must not have any change
220 SHOW GRANTS FOR userA;
221 SHOW GRANTS FOR userB;
222
223 --let $event= !Q(GRANT SELECT (.*), INSERT ON.*db1.*table1.*TO.*userY.*userZ.*)
224 --source ../include/auth_sec_assert_binlog_events.inc
225
226 DROP USER userX, userY, userZ, userA, userB;
227 DROP TABLE db1.table1;
228 DROP DATABASE db1;
229 --source include/save_binlog_position.inc
230 --echo # End : Tests for GRANT : Table and Column grants
231
232 --echo # ----------------------------------------------------------------------
233
234 # Wait till we reached the initial number of concurrent sessions
235 --source include/wait_until_count_sessions.inc