Debian Codegraph @

mysql-test/suite/sys_vars/t/mysql_native_password_proxy_users_func.test

raw
1 ############ mysql-test\t\mysql_native_password_func.test ######################
2 # #
3 #Variable Name: mysql_native_password #
4 #Scope: SESSION #
5 #Access Type: Dynamic #
6 #Data Type: BOOLEAN #
7 #Default Value: OFF #
8 #Values: ON, OFF #
9 # #
10 # #
11 #Creation Date: 2015-01-20 #
12 #Author: Todd Farmer #
13 # #
14 #Description: Test Cases of Dynamic System Variable #
15 # "mysql_native_password" #
16 # that checks behavior of this variable in the following ways #
17 # * Functionality based on different values #
18 # #
19 #Reference: http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#
20 # option_mysqld_mysql_native_password #
21 # #
22 ################################################################################
23
24 --echo ** Setup **
25 --echo
26 #
27 # Setup
28 #
29
30
31 SET @default_mysql_native_password_proxy_users = @@global.mysql_native_password_proxy_users;
32 SET @default_check_proxy_users = @@global.check_proxy_users;
33
34 --echo '#----- 2.2.4 With super privilege ------------------------------#'
35 ###################################
36 #Setting value OFF #
37 ###################################
38
39 SET Global mysql_native_password_proxy_users=OFF;
40
41 --echo '#----- 2.2.4 Without super privilege ---------------------------#'
42 #########################################
43 #Creating user without Super privilege #
44 #########################################
45
46 --echo ** Creating new user with out super privilege**
47 CREATE USER sameea;
48 --echo ** Connecting connn using username 'sameea' **
49 CONNECT (conna,localhost,sameea,,);
50
51 --Error ER_SPECIFIC_ACCESS_DENIED_ERROR
52 SET GLOBAL mysql_native_password_proxy_users=ON;
53 --Error ER_SPECIFIC_ACCESS_DENIED_ERROR
54 SET @@global.mysql_native_password_proxy_users=ON;
55
56 --echo ** Connection default **
57 connection default;
58 --echo ** Closing connection **
59 DISCONNECT conna;
60
61 --echo '#----- 2.2.2.2 OFF no assignment of proxy user -----------------#'
62 ###################################
63 #Setting value OFF #
64 ###################################
65
66 SET Global mysql_native_password_proxy_users=OFF;
67 SELECT @@global.check_proxy_users;
68
69 ###################################
70 #Setting up proxy accounts #
71 ###################################
72
73 --echo ** Creating new base user **
74 CREATE USER ''@localhost;
75 --echo '#----- 4.4) Test to ensure users not defined with any PROXY privileges log in normally.-#';
76 CREATE USER proxy_base@localhost;
77 GRANT ALTER ON *.* TO proxy_base@localhost;
78 --echo ** Creating new proxy user **
79 CREATE USER proxy_native@localhost IDENTIFIED WITH mysql_native_password;
80 GRANT CREATE ON *.* TO proxy_native@localhost;
81 GRANT PROXY ON proxy_base@localhost TO proxy_native@localhost;
82
83 --echo ** Creating new proxy user with missing base user **
84 CREATE USER proxy_none@localhost IDENTIFIED WITH mysql_native_password;
85 GRANT PROXY ON proxy_blah@localhost TO proxy_none@localhost;
86
87 --echo '#----- 4.3)Test to ensure proxy privileges are not chained. ----------------------------#';
88 CREATE USER proxy_base_multi@localhost;
89 GRANT SELECT ON *.* TO proxy_base_multi@localhost;
90 --echo ** Creating new proxy user **
91 CREATE USER proxy_native_1@localhost IDENTIFIED WITH mysql_native_password;
92 GRANT DELETE ON *.* TO proxy_native_1@localhost;
93 CREATE USER proxy_native_2@localhost IDENTIFIED WITH mysql_native_password;
94 GRANT INSERT,DELETE ON *.* TO proxy_native_2@localhost;
95 CREATE USER proxy_native_3@localhost IDENTIFIED WITH mysql_native_password;
96 GRANT SELECT,DELETE ON *.* TO proxy_native_3@localhost;
97 GRANT PROXY ON proxy_base_multi@localhost TO proxy_native_1@localhost;
98 GRANT PROXY ON proxy_base_multi@localhost TO proxy_native_2@localhost;
99 GRANT PROXY ON proxy_base_multi@localhost TO proxy_native_3@localhost;
100
101 --echo ** Creating new proxy user **
102 CREATE USER proxy_native_4@localhost IDENTIFIED WITH mysql_native_password;
103 CREATE USER proxy_native_41@localhost IDENTIFIED WITH mysql_native_password;
104 GRANT PROXY ON proxy_base@localhost TO proxy_native_4@localhost;
105 GRANT PROXY ON proxy_base_multi@localhost TO proxy_native_4@localhost;
106 GRANT INSERT ON *.* TO proxy_native_4@localhost;
107 GRANT PROXY ON proxy_native_4@localhost TO proxy_native_41@localhost;
108 GRANT UPDATE ON *.* TO proxy_native_41@localhost;
109
110
111 --echo '#----- 4.2.check_proxy_users=OFF: mysql_native_password_proxy_users=OFF -----#';
112 ###################################
113 #Testing proxy accounts #
114 ###################################
115
116 --echo ** Connecting as proxy_native with proxy mapping disabled **
117 CONNECT (connb,localhost,proxy_native,,);
118 SELECT CURRENT_USER(), USER(), @@session.proxy_user;
119 SHOW GRANTS;
120
121 --echo ** Connection default **
122 connection default;
123 SET PASSWORD FOR 'proxy_native'@'localhost' = 'testpw';
124 --echo ** Connecting as proxy_native,password with proxy mapping disabled **
125 CONNECT (connbp,localhost,proxy_native,testpw,);
126 SELECT CURRENT_USER(), USER(), @@session.proxy_user;
127 SHOW GRANTS;
128 --echo ** Connection default **
129 connection default;
130 SET PASSWORD FOR 'proxy_native'@'localhost' = '';
131
132 --echo ** Connecting as proxy_none with proxy mapping disabled **
133 CONNECT (connc,localhost,proxy_none,,);
134 SELECT CURRENT_USER(), USER(), @@session.proxy_user;
135 SHOW GRANTS;
136
137 --echo ** Connecting as proxy_native_1 with proxy mapping disabled **
138 CONNECT (connn1,localhost,proxy_native_1,,);
139 SELECT CURRENT_USER(), USER(), @@session.proxy_user;
140 SHOW GRANTS;
141 --echo ** Connecting as proxy_native_2 with proxy mapping disabled **
142 CONNECT (connn2,localhost,proxy_native_2,,);
143 SELECT CURRENT_USER(), USER(), @@session.proxy_user;
144 SHOW GRANTS;
145
146 --echo ** Connecting as proxy_native_4 with proxy mapping disabled **
147 CONNECT (connm1,localhost,proxy_native_4,,);
148 SELECT CURRENT_USER(), USER(), @@session.proxy_user;
149 SHOW GRANTS;
150
151 --echo ** Connection default **
152 connection default;
153
154 --echo ** Disconnecting connections **
155 DISCONNECT connb;
156 DISCONNECT connbp;
157 DISCONNECT connc;
158 DISCONNECT connn1;
159 DISCONNECT connn2;
160 DISCONNECT connm1;
161
162 --echo '#----- 2.2.2.1 ON assignment of proxy user ------------------#'
163 --echo '#----- 4.2.check_proxy_users=OFF: mysql_native_password_proxy_users=ON ------#';
164 ###################################
165 #Setting value ON #
166 ###################################
167
168 SET Global mysql_native_password_proxy_users=ON;
169
170 ###################################
171 #Testing proxy accounts #
172 ###################################
173
174 --echo ** Connecting as proxy_native with proxy mapping disabled (native mapping on) **
175 CONNECT (connd,localhost,proxy_native,,);
176 SELECT CURRENT_USER(), USER(), @@session.proxy_user;
177 SHOW GRANTS;
178
179 --echo ** Connection default **
180 connection default;
181 SET PASSWORD FOR 'proxy_native'@'localhost' = 'testpw';
182 --echo ** Connecting as proxy_native,password with proxy mapping disabled (native mapping on) **
183 CONNECT (conndp,localhost,proxy_native,testpw,);
184 SELECT CURRENT_USER(), USER(), @@session.proxy_user;
185 SHOW GRANTS;
186 --echo ** Connection default **
187 connection default;
188 SET PASSWORD FOR 'proxy_native'@'localhost' = '';
189
190 --echo ** Connecting as proxy_none with proxy mapping disabled (native mapping on) **
191 CONNECT (conne,localhost,proxy_none,,);
192 SELECT CURRENT_USER(), USER(), @@session.proxy_user;
193 SHOW GRANTS;
194
195 --echo ** Connecting as proxy_native_1 with proxy mapping disabled (native mapping on)**
196 CONNECT (connn3,localhost,proxy_native_1,,);
197 SELECT CURRENT_USER(), USER(), @@session.proxy_user;
198 SHOW GRANTS;
199 --echo ** Connecting as proxy_native_2 with proxy mapping disabled (native mapping on) **
200 CONNECT (connn4,localhost,proxy_native_2,,);
201 SELECT CURRENT_USER(), USER(), @@session.proxy_user;
202 SHOW GRANTS;
203
204 --echo ** Connecting as proxy_native_4 with proxy mapping disabled (native mapping on) **
205 CONNECT (connm2,localhost,proxy_native_4,,);
206 SELECT CURRENT_USER(), USER(), @@session.proxy_user;
207 SHOW GRANTS;
208
209 --echo ** Connecting as chained proxy_native_41 with proxy mapping disabled (native mapping on) **
210 CONNECT (connm21,localhost,proxy_native_41,,);
211 SELECT CURRENT_USER(), USER(), @@session.proxy_user;
212 SHOW GRANTS;
213
214 --echo ** Connection default **
215 connection default;
216 GRANT PROXY ON ''@localhost TO proxy_native_1@localhost;
217 --echo ** Connecting as Anonymus with proxy mapping disabled (native mapping on) **
218 CONNECT (connan, localhost, '',,);
219 SELECT @@session.proxy_user IS NOT NULL;
220
221 --echo ** Connection default **
222 connection default;
223 DISCONNECT connan;
224 REVOKE PROXY ON ''@localhost FROM proxy_native_1@localhost;
225
226 --echo ** Connection default **
227 connection default;
228
229 --echo ** Disconnecting connections **
230 DISCONNECT connd;
231 DISCONNECT conndp;
232 DISCONNECT conne;
233 DISCONNECT connn3;
234 DISCONNECT connn4;
235 DISCONNECT connm2;
236 DISCONNECT connm21;
237
238 --echo '#----- 1.2.2.1 check and native ON --------------------------#'
239 --echo '#----- 4.2.check_proxy_users=ON: mysql_native_password_proxy_users=ON ------#';
240 ###################################
241 #Setting check_proxy value ON #
242 ###################################
243
244 SET Global check_proxy_users=ON;
245
246 ###################################
247 #Testing proxy accounts #
248 ###################################
249
250 --echo ** Connecting as proxy_native with proxy mapping enabled **
251 CONNECT (connf,localhost,proxy_native,,);
252 SELECT CURRENT_USER(), USER(), @@session.proxy_user;
253 SHOW GRANTS;
254
255 --echo ** Connection default **
256 connection default;
257 SET PASSWORD FOR 'proxy_native'@'localhost' = 'testpw';
258 --echo ** Connecting as proxy_native,password with proxy mapping enabled **
259 CONNECT (connfp,localhost,proxy_native,testpw,);
260 SELECT CURRENT_USER(), USER(), @@session.proxy_user;
261 SHOW GRANTS;
262 --echo ** Connection default **
263 connection default;
264 SET PASSWORD FOR 'proxy_native'@'localhost' = '';
265
266 --echo ** Connecting as proxy_none with proxy mapping enabled **
267 CONNECT (conng,localhost,proxy_none,,);
268 SELECT CURRENT_USER(), USER(), @@session.proxy_user;
269 SHOW GRANTS;
270
271 --echo ** Connecting as proxy_native_1 with proxy mapping enabled **
272 CONNECT (connn5,localhost,proxy_native_1,,);
273 SELECT CURRENT_USER(), USER(), @@session.proxy_user;
274 SHOW GRANTS;
275 --echo ** Connecting as proxy_native_2 with proxy mapping enabled **
276 CONNECT (connn6,localhost,proxy_native_2,,);
277 SELECT CURRENT_USER(), USER(), @@session.proxy_user;
278 SHOW GRANTS;
279
280 --echo ** Connecting as proxy_native_4 with proxy mapping enabled **
281 CONNECT (connm3,localhost,proxy_native_4,,);
282 SELECT CURRENT_USER() IN ('proxy_base@localhost','proxy_base_multi@localhost');
283 SELECT USER(), @@session.proxy_user;
284 SHOW GRANTS;
285
286 --echo ** Connecting as chained proxy_native_41 with proxy mapping enabled **
287 CONNECT (connm31,localhost,proxy_native_41,,);
288 SELECT CURRENT_USER() IN ('proxy_base@localhost','proxy_base_multi@localhost');
289 SELECT CURRENT_USER() IN ('proxy_native_4@localhost');
290 SELECT USER(), @@session.proxy_user;
291 SHOW GRANTS;
292
293 --echo ** Connection default **
294 connection default;
295 GRANT PROXY ON ''@localhost TO proxy_native_1@localhost;
296 --echo ** Connecting as Anonymus with proxy mapping enabled **
297 CONNECT (connan, localhost, '',,);
298 SELECT @@session.proxy_user IS NOT NULL;
299
300 --echo ** Connection default **
301 connection default;
302 DISCONNECT connan;
303 REVOKE PROXY ON ''@localhost FROM proxy_native_1@localhost;
304
305 --echo ** Disconnecting connections **
306 DISCONNECT connf;
307 DISCONNECT connfp;
308 DISCONNECT conng;
309 DISCONNECT connn5;
310 DISCONNECT connn6;
311 DISCONNECT connm3;
312 DISCONNECT connm31;
313
314 ###################################
315 #Cleaning up #
316 ###################################
317
318 DROP USER ''@localhost;
319 DROP USER proxy_base@localhost;
320 DROP USER proxy_base_multi@localhost;
321 DROP USER proxy_native@localhost;
322 DROP USER proxy_none@localhost;
323 DROP USER proxy_native_1@localhost;
324 DROP USER proxy_native_2@localhost;
325 DROP USER proxy_native_3@localhost;
326 DROP USER proxy_native_4@localhost;
327 DROP USER proxy_native_41@localhost;
328 DROP USER sameea;
329
330 SET @@global.mysql_native_password_proxy_users = @default_mysql_native_password_proxy_users;
331 SET @@global.check_proxy_users = @default_check_proxy_users;