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.
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.