Bug #82733 Login errors after GRANT ON *.* in sql_mode PAD_CHAR_TO_FULL_LENGTH
Submitted: 25 Aug 2016 21:56 Modified: 27 Aug 2016 8:10
Reporter: Szymon Komendera Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.6, 5.7, 5.6.32 OS:Any
Assigned to: CPU Architecture:Any

[25 Aug 2016 21:56] Szymon Komendera
Description:
###################
## ISSUE
###################

The underlying functions used by GRANT operations against global privileges (*.*) are not safeguarded against sql_mode PAD_CHAR_TO_FULL_LENGTH. When a session running in PAD_CHAR_TO_FULL_LENGTH invokes GRANT/REVOKE ON *.* against a user, the user's authentication plugin field becomes corrupted in the ACL cache. Subsequently, the user will be unable to connect until:

- Privileges are flushed OR
- The server is restarted OR
- Another session runs GRANT/REVOKE against the same user again, this time without the PAD_CHAR_TO_FULL_LENGTH sql_mode.

This behavior is reported and was tested in the context of native_mysql_password authentication only. I did not test with other authentication methods.

###################
## AFFECTED VERSIONS
###################

- 5.6: issue exists and can break user logins
- 5.7: issue exists but doesn't break user logins due to differences in login code

See research below for more information.

###################
## RESEARCH
###################

------ 1. GRANT/REVOKE calls replace_user_table to update ACL data

Breakpoint 1, replace_user_table (thd=thd@entry=0x29ae420, table=0x1a0392a0, combo=combo@entry=0x1a1510d0, rights=rights@entry=3, revoke_grant=revoke_grant@entry=false,
    can_create_user=true, no_auto_create=false) at /home/mysql/source/mysql-5.6.27/sql/sql_acl.cc:2795
2795   	{
(gdb) bt
#0  replace_user_table (thd=thd@entry=0x29ae420, table=0x1a0392a0, combo=combo@entry=0x1a1510d0, rights=rights@entry=3, revoke_grant=revoke_grant@entry=false, can_create_user=true,
    no_auto_create=false) at /home/mysql/source/mysql-5.6.27/sql/sql_acl.cc:2795
#1  0x00000000006a66cc in mysql_grant (thd=<optimized out>, db=<optimized out>, list=..., rights=3, revoke_grant=false, is_proxy=false)
    at /home/mysql/source/mysql-5.6.27/sql/sql_acl.cc:5109

------ 2. Replace_user_table gets plugin information from cached user data:

Breakpoint 2, replace_user_table (thd=thd@entry=0x29ae420, table=0x1a0392a0, combo=combo@entry=0x1a1510d0, rights=rights@entry=3, revoke_grant=revoke_grant@entry=false,
    can_create_user=true, no_auto_create=false) at /home/mysql/source/mysql-5.6.27/sql/sql_acl.cc:2972
2972   	      get_field(thd->mem_root, table->field[MYSQL_USER_FIELD_PLUGIN]);
(gdb) l
2967   	    /*
2968   	      Get old plugin value from storage.
2969   	    */
2970
2971   	    old_plugin.str=
2972   	      get_field(thd->mem_root, table->field[MYSQL_USER_FIELD_PLUGIN]);

------ 3. Get_field gets the string value of the plugin through Field_string::val_string, which pads it with spaces:

Field_string::val_str (this=0x1a03b020, val_buffer=0x7f57e56bdfd0, val_ptr=0x7f57e56bdfd0) at /home/mysql/source/mysql-5.6.27/sql/field.cc:6797
6797   	  if (table->in_use->variables.sql_mode &
6798   	      MODE_PAD_CHAR_TO_FULL_LENGTH)
6799   	    length= my_charpos(field_charset, ptr, ptr + field_length,
6800   	                       field_length / field_charset->mbmaxlen);
6801   	  else
6802   	    length= field_charset->cset->lengthsp(field_charset, (const char*) ptr,
6803   	                                          field_length);

###################
## 5.6 vs 5.7
###################

---- See notes inline marked with "<<---"

int
acl_authenticate(THD *thd, uint com_change_user_pkt_len)
{
  int res= CR_OK;
  MPVIO_EXT mpvio;
  Thd_charset_adapter charset_adapter(thd);

  LEX_STRING auth_plugin_name= default_auth_plugin_name;

  ...

  if (command == COM_CHANGE_USER)
  {
    ...
  }
  else
  {
    ...

    <<!!!--- at this point "auth_plugin_name" is still OK ('mysql_native_password')

    res= do_auth_once(thd, &auth_plugin_name, &mpvio);

    <<!!!--- at this point mpvio.status is MPVIO_EXT::RESTART

  }

...

  /*
   retry the authentication, if - after receiving the user name -
   we found that we need to switch to a non-default plugin
  */
  if (mpvio.status == MPVIO_EXT::RESTART) <<!!!--- 5.6 enters here, but not 5.7 (in 5.7 mpvio.status is MPVIO_EXT::FAILURE)
  {
    DBUG_ASSERT(mpvio.acl_user);
    DBUG_ASSERT(command == COM_CHANGE_USER ||
                my_strcasecmp(system_charset_info, auth_plugin_name.str,
                              mpvio.acl_user->plugin.str));
    auth_plugin_name= mpvio.acl_user->plugin; <<!!!--- This is where the plugin string gets overwritten with incorrect information (padded with spaces) from ACL cache
    res= do_auth_once(thd, &auth_plugin_name, &mpvio);

How to repeat:
###################
## REPRO
###################

Note that this is a simplified repro where a user grants a privilege to himself and therefore breaks his own login ability. The repro is simplified for brevity, but this also reproduces in general case when granting/revoking privileges against arbitrary users.

root@sandbox:/home/admin# mysql -h127.0.0.1 -P5627 -uroot -p{...}

mysql> set sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> grant insert on *.* to 'root'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> \r
ERROR 1524 (HY000): Plugin 'mysql_native_password                                           ' is not loaded

Suggested fix:
###################
## SUGGESTED FIX
###################

The PAD_CHAR_TO_FULL_LENGTH mode is already being disabled in multiple places in MySQL code for the duration of privilege-related actions.

For example, this is from mysql_drop_user:

------------

bool mysql_drop_user(THD *thd, List <LEX_USER> &list)
{
  int result;
  String wrong_users;
  LEX_USER *user_name, *tmp_user_name;
  List_iterator <LEX_USER> user_list(list);
  TABLE_LIST tables[GRANT_TABLES];
  bool some_users_deleted= FALSE;
  sql_mode_t old_sql_mode= thd->variables.sql_mode;

...

  thd->variables.sql_mode&= ~MODE_PAD_CHAR_TO_FULL_LENGTH;

... (function body) ...

  thd->variables.sql_mode= old_sql_mode;

  DBUG_RETURN(result);

}

------------

Would suggest similar approach here.
[25 Aug 2016 21:57] Szymon Komendera
Note that I did check for existing bugs with PAD_CHAR_TO_FULL_LENGTH. Found quite a few, but nothing that's still Open that would match the symptoms here.
[27 Aug 2016 8:06] Umesh Shastry
Hello Szymon,

Thank you for the report.
Observed this with 5.6.32 build.

--
bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.32-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)

mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> grant insert on *.* to 'root'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> \r
ERROR 1524 (HY000): Plugin 'mysql_native_password                                           ' is not loaded
mysql>
mysql>
mysql> \q
Bye
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.32: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
ERROR 1524 (HY000): Plugin 'mysql_native_password                                           ' is not loaded
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.32:
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.32: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
ERROR 1524 (HY000): Plugin 'mysql_native_password                                           ' is not loaded

Thanks,
Umesh