Debian Codegraph @

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

raw
1
2
3 =======================================================================
4 Checking the password plugin assignment with create user command
5 =======================================================================
6
7 set @default_plugin=@@global.default_authentication_plugin;
8 Warnings:
9 Warning 1287 '@@default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
10 **** Creating user with default_plugin
11 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
12 **** Creating user with sha256_password plugin
13 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
14 SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
15 **** Validating the plugin names
16 select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')=@default_plugin;
17 (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')=@default_plugin
18 1
19 1 Expected
20 select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
21 (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password'
22 1
23 1 Expected
24 Drop user 'Tanjotuser1'@'localhost';
25 Drop user 'Tanjotuser3'@'localhost';
26
27
28 =======================================================================
29 Checking the functionality and integrity of the DROP user command
30 =======================================================================
31
32 **** Creating user with default_plugin
33 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
34 **** Creating user with sha256_password plugin
35 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
36 SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
37 Connecting a session each with the above 3 users
38 dropping the above 3 users with each having a open session
39 Drop user 'Tanjotuser1'@'localhost';
40 Drop user 'Tanjotuser3'@'localhost';
41 Now trying opening a new session for the above dropped user
42 ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES)
43 ERROR 28000: Access denied for user 'Tanjotuser3'@'localhost' (using password: YES)
44 Accessing the open sessions for the above dropped user
45 select user(), current_user();
46 user() current_user()
47 Tanjotuser1@localhost Tanjotuser1@localhost
48 select user(), current_user();
49 user() current_user()
50 Tanjotuser3@localhost Tanjotuser3@localhost
51
52
53 =======================================================================
54 Checking the privellages post renaming the user
55 =======================================================================
56
57 **** Creating user with default_plugin
58 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
59 **** Creating user with sha256_password plugin
60 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
61 SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
62 **** Validating the plugin names
63 select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')=@default_plugin;
64 (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')=@default_plugin
65 1
66 1 Expected
67 select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
68 (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password'
69 1
70 1 Expected
71 select user(), current_user();
72 user() current_user()
73 Tanjotuser1@localhost Tanjotuser1@localhost
74 select user(), current_user();
75 user() current_user()
76 Tanjotuser3@localhost Tanjotuser3@localhost
77 **** Validating the integrity of the rename user command
78 RENAME USER 'Tanjotuser1'@'localhost' to 'user1'@'localhost';
79 RENAME USER 'Tanjotuser3'@'localhost' to 'user3'@'localhost';
80 **** Validating the plugin names
81 select (select plugin from mysql.user where User='user1' and Host='localhost')=@default_plugin;
82 (select plugin from mysql.user where User='user1' and Host='localhost')=@default_plugin
83 1
84 1 Expected
85 select (select plugin from mysql.user where User='user3' and Host='localhost')='sha256_password';
86 (select plugin from mysql.user where User='user3' and Host='localhost')='sha256_password'
87 1
88 1 Expected
89 **** Validating the user connections post renaming
90 select user(), current_user();
91 user() current_user()
92 Tanjotuser1@localhost Tanjotuser1@localhost
93 select user(), current_user();
94 user() current_user()
95 Tanjotuser3@localhost Tanjotuser3@localhost
96 ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES)
97 ERROR 28000: Access denied for user 'Tanjotuser3'@'localhost' (using password: YES)
98 select user(), current_user();
99 user() current_user()
100 user1@localhost user1@localhost
101 select user(), current_user();
102 user() current_user()
103 user3@localhost user3@localhost
104 **** Disconnecting the open sessions and dropping the created users
105 Drop user 'user1'@'localhost';
106 Drop user 'user3'@'localhost';
107
108
109 =======================================================================
110 Checking the privillages with grant user command
111 =======================================================================
112
113 **** Creating user with default_plugin
114 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
115 GRANT select on mysql.* to 'Tanjotuser1'@'localhost';
116 **** Creating user with sha256_password plugin
117 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password' BY '123';
118 GRANT select on mysql.* to 'Tanjotuser3'@'localhost';
119 SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
120 **** Validating the granted privilleges
121 select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
122 plugin
123 default_plugin
124 default_plugin Expected
125 select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
126 plugin
127 sha256_password
128 sha256_password Expected
129 **** Revoking select on mysql database
130 Revoke select on mysql.* from 'Tanjotuser1'@'localhost';
131 Revoke select on mysql.* from 'Tanjotuser3'@'localhost';
132 **** Validating the Revoked privilleges
133 select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
134 ERROR 42000: SELECT command denied to user 'Tanjotuser1'@'localhost' for table 'user'
135 select user(), current_user();
136 user() current_user()
137 Tanjotuser1@localhost Tanjotuser1@localhost
138 select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
139 ERROR 42000: SELECT command denied to user 'Tanjotuser3'@'localhost' for table 'user'
140 select user(), current_user();
141 user() current_user()
142 Tanjotuser3@localhost Tanjotuser3@localhost
143 Grant usage on mysql.* to 'Tanjotuser1'@'localhost';
144 Grant usage on mysql.* to 'Tanjotuser3'@'localhost';
145 **** Validating the granted privilleges
146 select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
147 ERROR 42000: SELECT command denied to user 'Tanjotuser1'@'localhost' for table 'user'
148 select user(), current_user();
149 user() current_user()
150 Tanjotuser1@localhost Tanjotuser1@localhost
151 select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
152 ERROR 42000: SELECT command denied to user 'Tanjotuser3'@'localhost' for table 'user'
153 select user(), current_user();
154 user() current_user()
155 Tanjotuser3@localhost Tanjotuser3@localhost
156 **** Validating the granted privilleges
157 Grant All on mysql.* to 'Tanjotuser1'@'localhost';
158 Grant All on mysql.* to 'Tanjotuser3'@'localhost';
159 select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
160 plugin
161 default_plugin
162 default_plugin Expected
163 select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
164 plugin
165 sha256_password
166 sha256_password Expected
167 **** Disconnecting the open sessions and dropping the created users
168 Drop user 'Tanjotuser1'@'localhost';
169 Drop user 'Tanjotuser3'@'localhost';
170
171
172 =============================================================================
173 Checking the password assignment using the update command on the user table
174 =============================================================================
175
176 **** Creating user with mysql_native_password plugin
177 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
178 UPDATE mysql.user SET Authentication_string='*0D3CED9BEC10A777AEC23CCC353A8C08A633045E' WHERE User='Tanjotuser1' AND Host='localhost';
179 FLUSH PRIVILEGES;
180 **** Creating user with sha256_password plugin
181 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
182 UPDATE IGNORE mysql.user SET Authentication_string='*0D3CED9BEC10A777AEC23CCC353A8C08A633045E' WHERE User='Tanjotuser3' AND Host='localhost';
183 FLUSH PRIVILEGES;
184 select user(), current_user();
185 user() current_user()
186 Tanjotuser1@localhost Tanjotuser1@localhost
187 set password='abcd';
188 select user(), current_user();
189 user() current_user()
190 Tanjotuser1@localhost Tanjotuser1@localhost
191 **** Disconnecting the open sessions and dropping the created users
192 Drop user 'Tanjotuser1'@'localhost';
193 Drop user 'Tanjotuser3'@'localhost';
194
195
196 ===================================================================================================
197 Starting the server with default auth as sha256_password and checking the above validations again.
198 ===================================================================================================
199
200 # Restart server with default-authentication-plugin=sha256_password;
201
202
203 =======================================================================
204 Checking the password plugin assignment with create user command
205 =======================================================================
206
207 **** Creating user with mysql_native_password plugin
208 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
209 SET PASSWORD for 'Tanjotuser1'@'localhost' = 'abc';
210 **** Creating user with sha256_password plugin
211 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
212 SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
213 **** Validating the plugin names
214 select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password';
215 (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password'
216 1
217 1 Expected
218 select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
219 (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password'
220 1
221 1 Expected
222 Drop user 'Tanjotuser1'@'localhost';
223 Drop user 'Tanjotuser3'@'localhost';
224
225
226 =======================================================================
227 Checking the functionality and integrity of the DROP user command
228 =======================================================================
229
230 **** Creating user with default_plugin
231 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED BY 'abc';
232 **** Creating user with sha256_password plugin
233 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
234 SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
235 Connecting a session each with the above 3 users
236 dropping the above 3 users with each having a open session
237 Drop user 'Tanjotuser1'@'localhost';
238 Drop user 'Tanjotuser3'@'localhost';
239 Now trying opening a new session for the above dropped user
240 ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES)
241 ERROR 28000: Access denied for user 'Tanjotuser3'@'localhost' (using password: YES)
242 Accessing the open sessions for the above dropped user
243 select user(), current_user();
244 user() current_user()
245 Tanjotuser1@localhost Tanjotuser1@localhost
246 select user(), current_user();
247 user() current_user()
248 Tanjotuser3@localhost Tanjotuser3@localhost
249
250
251 =======================================================================
252 Checking the privellages post renaming the user
253 =======================================================================
254
255 **** Creating user with mysql_native_password plugin
256 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
257 SET PASSWORD for 'Tanjotuser1'@'localhost' = 'abc';
258 **** Creating user with sha256_password plugin
259 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
260 SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
261 **** Validating the plugin names
262 select (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password';
263 (select plugin from mysql.user where User='Tanjotuser1' and Host='localhost')='mysql_native_password'
264 1
265 1 Expected
266 select (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password';
267 (select plugin from mysql.user where User='Tanjotuser3' and Host='localhost')='sha256_password'
268 1
269 1 Expected
270 select user(), current_user();
271 user() current_user()
272 Tanjotuser1@localhost Tanjotuser1@localhost
273 select user(), current_user();
274 user() current_user()
275 Tanjotuser3@localhost Tanjotuser3@localhost
276 **** Validating the integrity of the rename user command
277 RENAME USER 'Tanjotuser1'@'localhost' to 'user1'@'localhost';
278 RENAME USER 'Tanjotuser3'@'localhost' to 'user3'@'localhost';
279 **** Validating the plugin names
280 select (select plugin from mysql.user where User='user1' and Host='localhost')='mysql_native_password';
281 (select plugin from mysql.user where User='user1' and Host='localhost')='mysql_native_password'
282 1
283 1 Expected
284 select (select plugin from mysql.user where User='user3' and Host='localhost')='sha256_password';
285 (select plugin from mysql.user where User='user3' and Host='localhost')='sha256_password'
286 1
287 1 Expected
288 **** Validating the user connections post renaming
289 select user(), current_user();
290 user() current_user()
291 Tanjotuser1@localhost Tanjotuser1@localhost
292 select user(), current_user();
293 user() current_user()
294 Tanjotuser3@localhost Tanjotuser3@localhost
295 ERROR 28000: Access denied for user 'Tanjotuser1'@'localhost' (using password: YES)
296 ERROR 28000: Access denied for user 'Tanjotuser3'@'localhost' (using password: YES)
297 select user(), current_user();
298 user() current_user()
299 user1@localhost user1@localhost
300 select user(), current_user();
301 user() current_user()
302 user3@localhost user3@localhost
303 **** Disconnecting the open sessions and dropping the created users
304 Drop user 'user1'@'localhost';
305 Drop user 'user3'@'localhost';
306
307
308 =======================================================================
309 Checking the privillages with grant user command
310 =======================================================================
311
312 **** Creating user with mysql_native_password plugin
313 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '123';
314 GRANT select on mysql.* to 'Tanjotuser1'@'localhost';
315 SET PASSWORD for 'Tanjotuser1'@'localhost' = 'abc';
316 **** Creating user with sha256_password plugin
317 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password' BY '123';
318 GRANT select on mysql.* to 'Tanjotuser3'@'localhost';
319 SET PASSWORD for 'Tanjotuser3'@'localhost' = 'abc';
320 **** Validating the granted privilleges
321 select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
322 plugin
323 mysql_native_password
324 mysql_native_password Expected
325 select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
326 plugin
327 sha256_password
328 sha256_password Expected
329 **** Revoking select on mysql database
330 Revoke select on mysql.* from 'Tanjotuser1'@'localhost';
331 Revoke select on mysql.* from 'Tanjotuser3'@'localhost';
332 **** Validating the Revoked privilleges
333 select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
334 ERROR 42000: SELECT command denied to user 'Tanjotuser1'@'localhost' for table 'user'
335 select user(), current_user();
336 user() current_user()
337 Tanjotuser1@localhost Tanjotuser1@localhost
338 select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
339 ERROR 42000: SELECT command denied to user 'Tanjotuser3'@'localhost' for table 'user'
340 select user(), current_user();
341 user() current_user()
342 Tanjotuser3@localhost Tanjotuser3@localhost
343 Grant usage on mysql.* to 'Tanjotuser1'@'localhost';
344 Grant usage on mysql.* to 'Tanjotuser3'@'localhost';
345 **** Validating the granted privilleges
346 select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
347 ERROR 42000: SELECT command denied to user 'Tanjotuser1'@'localhost' for table 'user'
348 select user(), current_user();
349 user() current_user()
350 Tanjotuser1@localhost Tanjotuser1@localhost
351 select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
352 ERROR 42000: SELECT command denied to user 'Tanjotuser3'@'localhost' for table 'user'
353 select user(), current_user();
354 user() current_user()
355 Tanjotuser3@localhost Tanjotuser3@localhost
356 **** Validating the granted privilleges
357 Grant All on mysql.* to 'Tanjotuser1'@'localhost';
358 Grant All on mysql.* to 'Tanjotuser3'@'localhost';
359 select plugin from mysql.user where user='Tanjotuser1' and host='localhost';
360 plugin
361 mysql_native_password
362 mysql_native_password Expected
363 select plugin from mysql.user where user='Tanjotuser3' and host='localhost';
364 plugin
365 sha256_password
366 sha256_password Expected
367 **** Disconnecting the open sessions and dropping the created users
368 Drop user 'Tanjotuser1'@'localhost';
369 Drop user 'Tanjotuser3'@'localhost';
370
371
372 =============================================================================
373 Checking the password assignment using the update command on the user table
374 =============================================================================
375
376 **** Creating user with mysql_native_password plugin
377 CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
378 UPDATE mysql.user SET Authentication_string='*0D3CED9BEC10A777AEC23CCC353A8C08A633045E' WHERE User='Tanjotuser1' AND Host='localhost';
379 FLUSH PRIVILEGES;
380 **** Creating user with sha256_password plugin
381 CREATE USER 'Tanjotuser3'@'localhost' IDENTIFIED WITH 'sha256_password';
382 UPDATE IGNORE mysql.user SET Authentication_string='*0D3CED9BEC10A777AEC23CCC353A8C08A633045E' WHERE User='Tanjotuser3' AND Host='localhost';
383 FLUSH PRIVILEGES;
384 select user(), current_user();
385 user() current_user()
386 Tanjotuser1@localhost Tanjotuser1@localhost
387 set password='abcd';
388 select user(), current_user();
389 user() current_user()
390 Tanjotuser1@localhost Tanjotuser1@localhost
391 **** Disconnecting the open sessions and dropping the created users
392 Drop user 'Tanjotuser1'@'localhost';
393 Drop user 'Tanjotuser3'@'localhost';
394 # restart: