Bug #59662 MySQL Server 5.5 wants to change the auth plugin to the same plugin
Submitted: 21 Jan 2011 16:00 Modified: 2 Feb 2011 15:36
Reporter: Andrey Hristov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Pluggable Authentication Severity:S3 (Non-critical)
Version:5.5, 5.6.2 OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[21 Jan 2011 16:00] Andrey Hristov
Description:
I connect to MySQL 5.5, then I change the user, in my case the same user just to reset the connection. I expect that because the same authentication plugin to be used to have just a command and response. However, the server decides to send a change plugin packet (0xFE) and wants to use mysql_native_password, which is no different from the first request. The client obeys and sends the same encrypted data. The server okays it.
There should be no change of plugin.

How to repeat:
Write a single program which connects and changes the user. I am using PHP to simulate this :
php -r '$c=mysqli_connect("127.0.0.1","root","root");var_dump($c->thread_id);var_dump($c->change_user("root","root","test"));'

Here are the packet dumps :

Greetings:
0000   49 00 00 00 0a 35 2e 35 2e 38 00 e2 0a 00 00 29  I....5.5.8.....)
0010   51 3f 25 3c 3c 57 23 00 ff ff 08 02 00 0f c0 15  Q?%<<W#.........
0020   00 00 00 00 00 00 00 00 00 00 47 7b 57 21 3d 4d  ..........G{W!=M
0030   4c 75 2a 6e 6e 5c 00 6d 79 73 71 6c 5f 6e 61 74  Lu*nn\.mysql_nat
0040   69 76 65 5f 70 61 73 73 77 6f 72 64 00           ive_password.

Authenticate:
0000   50 00 00 01 05 a2 0e 00 00 00 00 40 08 00 00 00  P..........@....
0010   00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  ................
0020   00 00 00 00 72 6f 6f 74 00 14 a9 8e 59 4a 91 c3  ....root....YJ..
0030   d4 49 09 50 f0 89 62 a9 96 85 31 e2 aa 1e 6d 79  .I.P..b...1...my
0040   73 71 6c 5f 6e 61 74 69 76 65 5f 70 61 73 73 77  sql_native_passw
0050   6f 72 64 00                                      ord.

Authenticated:
0000   07 00 00 02 00 00 00 02 00 00 00                 ...........

Change user (from the client):
0000   38 00 00 00 11 72 6f 6f 74 00 14 a9 8e 59 4a 91  8....root....YJ.
0010   c3 d4 49 09 50 f0 89 62 a9 96 85 31 e2 aa 1e 74  ..I.P..b...1...t
0020   65 73 74 00 08 00 6d 79 73 71 6c 5f 6e 61 74 69  est...mysql_nati
0030   76 65 5f 70 61 73 73 77 6f 72 64 00              ve_password.

Change user response from the server (change plugin):
0000   2c 00 00 01 fe 6d 79 73 71 6c 5f 6e 61 74 69 76  ,....mysql_nativ
0010   65 5f 70 61 73 73 77 6f 72 64 00 29 51 3f 25 3c  e_password.)Q?%<
0020   3c 57 23 47 7b 57 21 3d 4d 4c 75 2a 6e 6e 5c 00                                                  <W#G{W!=MLu*nn\.

Client obeys and sends the password scrambled, again:
0000   14 00 00 02 a9 8e 59 4a 91 c3 d4 49 09 50 f0 89  ......YJ...I.P..
0010   62 a9 96 85 31 e2 aa 1e                          b...1...

This time the server is satisfied:
0000   07 00 00 03 00 00 00 02 00 00 00                 ...........
[22 Jan 2011 9:26] Sveta Smirnova
Thank you for the report.

Verified as described.
[2 Feb 2011 15:36] Georgi Kodinov
Prior to the pluggable authentication implementation the server was perfectly capable of checking the user details internally based on the user id and the password supplied with the COM_CHANGE_USER request.
This resulted into client just sending an COM_CHANGE_USER and getting an OK. 
However pluggable authentication changes this by introducing additional parties to the authentication process : the client and the server authentication plugins.
These work in concert and produce result based on a single well defined interaction sequence. Hence the information in COM_CHANGE_USER is not guaranteed to be enough to authenticate the user anymore without further interaction with the client.
Thus the server needs to initiate a full authentication cycle to be able to involve the client and the server plugins in an appropriate context every time it needs to authenticate a user.
The only tool available to get the client to participate in this cycle is to ask for a plugin change.
Thus the client that claims to support pluggable authentication is guaranteed to get a change plugin request after each COM_CHANGE_USER.
Note that the server will stay compatible with pre-pluggable authentication clients and will just check the user details locally and will return the same OK/fail packet an old server would return. This is done by creating a special case inside the server for pre-pluggable authentication clients.

COM_CHANGE_USER is often used with the same user name for its side effect (in addition to checking the user credentials): to reset the connection state.
Since now checking the credentials is more involving than just single server-side check I think we need to create a special RPC to reset the connection without the need to re-authenticate again.
We can call this e.g. COM_RESET_CONNECTION. Or better have a server-side function that does that through COM_QUERY if possible.
With the above in mind I'm changing this bug's attributes to feature request.