mysql Found invalid password for user

    最近接到用户反馈，一直使用的mysql帐号却连接不上了。具体错误如下：

E:\mysql-5.6.16\client\Debug>mysql.exe -h127.0.0.1 -P3306 -uedgeyang -p
Enter password: ********
ERROR 1045 (28000): Access denied for user 'edgeyang'@'127.0.0.1' (using password
: NO)

    首先，登录上去查看用户权限。发现show grants确实没有用户，但mysql.user表内是有该用户的权限记录的。初步有两个猜测：

1. 权限表没有加载到内存，但flush privileges刷新权限后同样不能登录；
2. 表损坏，但check table显示表正常。

mysql> show grants for edgeyang@'127.0.0.1';
ERROR 1141 (42000): There is no such grant defined for user 'edgeyang' on host '
127.0.0.1'

    然后，发现mysql.user表内的权限密码是明文的。密码一般都是加密存储的，明文存储mysql不能解析？

mysql> select host,user,password from user where user='edgeyang' \G
*************************** 1. row ***************************
    host: 127.0.0.1
    user: edgeyang
password: edgeyang
1 row in set (0.00 sec)

    查看mysql error log，发现如下信息。看样子是密码非法，导致mysql不能识别该用户。

[Warning] Found invalid password for user: 'edgeyang@127.0.0.1'; Ignoring user

    那mysql用户密码是用什么加密的，并且加密后密码有怎样的格式（方便以后判断是否非法密码）？
    查看mysql 5.6.16源码，追踪flush privileges命令的执行情况，看看mysql是如何加载权限表的。
    第一步，调用函数reload_acl_and_cache重载权限表，并根据需要刷新日志文件（例如错误日志，慢查询日志）。其中权限表分三个部分：

1. acl_reload函数加载的权限表，mysql.user，mysql.db，mysql.proxies_priv；
2. grant_reload函数加载的权限表，mysql.tables_priv，mysql.columns_priv；
3. servers_reload函数加载的权限表，mysql.servers。

bool reload_acl_and_cache(THD *thd, unsigned long options,
                          TABLE_LIST *tables, int *write_to_binlog)
{
    if (thd)
    {
      bool reload_acl_failed= acl_reload(thd);
      bool reload_grants_failed= grant_reload(thd);
      bool reload_servers_failed= servers_reload(thd);
    ｝
    
    if (options & REFRESH_ERROR_LOG)
        if (flush_error_log())
        {
         /*
            When flush_error_log() failed, my_error() has not been called.
            So, we have to do it here to keep the protocol.
         */
         my_error(ER_UNKNOWN_ERROR, MYF(0));
         result= 1;
        }

  if ((options & REFRESH_SLOW_LOG) && opt_slow_log)
    logger.flush_slow_log();
｝

    第二步，查看acl_reload函数。函数的功能如下：

1. 打开权限表，清空权限的缓存；
2. 调用函数acl_load加载权限；
3. 关闭权限表。

/*
Forget current user/db-level privileges and read new privileges
  from the privilege tables.
*/
my_bool acl_reload(THD *thd)
{
  TABLE_LIST tables[3];
  /*
    To avoid deadlocks we should obtain table locks before
    obtaining acl_cache->lock mutex.
  */
  tables[0].init_one_table(C_STRING_WITH_LEN("mysql"),
                           C_STRING_WITH_LEN("user"), "user", TL_READ);
  tables[1].init_one_table(C_STRING_WITH_LEN("mysql"),
                           C_STRING_WITH_LEN("db"), "db", TL_READ);
  tables[2].init_one_table(C_STRING_WITH_LEN("mysql"),
                           C_STRING_WITH_LEN("proxies_priv"), 
                           "proxies_priv", TL_READ);
  if (open_and_lock_tables(thd, tables, FALSE, MYSQL_LOCK_IGNORE_TIMEOUT))
  {
    goto end;
  }

  delete_dynamic(&acl_wild_hosts);
  my_hash_free(&acl_check_hosts);

  if ((return_val= acl_load(thd, tables)))
  {                    // Error. Revert to old list
    init_check_host();
  }
  else
  {
    //delete something
  }
  if (old_initialized)
    mysql_mutex_unlock(&acl_cache->lock);
end:
  close_acl_tables(thd);
  DBUG_RETURN(return_val);
}

    第三步，查看acl_load函数。函数功能如下：

1. 逐行读取mysql.user表记录；
2. 逐字段读取，并检查字段值在mysql各版本是否合法；
3. 读取其他表，如mysql.db。

 其中第二个小步骤里面，函数set_user_salt会检查password字段合法性。

/*
  Initialize structures responsible for user/db-level privilege checking
  and load information about grants from open privilege tables.
*/
static my_bool acl_load(THD *thd, TABLE_LIST *tables)
{
  acl_cache->clear(1);                // Clear locked hostname cache

  init_sql_alloc(&global_acl_memory, ACL_ALLOC_BLOCK_SIZE, 0);
  /*
    Prepare reading from the mysql.user table
  */
  if (init_read_record(&read_record_info, thd, table=tables[0].table,
                       NULL, 1, 1, FALSE))
    goto end;
  while (!(read_record_info.read_record(&read_record_info)))
  {
    /* Read legacy password */
    password= get_field(&global_acl_memory,
                        table->field[MYSQL_USER_FIELD_PASSWORD]);
    password_len= password ? strlen(password) : 0;
    user.auth_string.str= password ? password : const_cast<char*>("");
    user.auth_string.length= password_len;

    {
    /*省略此处代码，为了兼容mysql其他版本的权限信息*/
     /*
         Transform hex to octets and adjust the format.
       */
      if (set_user_salt(&user, password, password_len))
      {
        sql_print_warning("Found invalid password for user: '%s@%s'; "
                          "Ignoring user", user.user ? user.user : "",
                          user.host.get_host() ? user.host.get_host() : "");
        continue;
      }
    }
  } // END while reading records from the mysql.user table
}

    第四步，set_user_salt判断密码是否合法。由此可知，mysq.user存储的加密后l密码的合法格式是：

1. 为空；
2. 新格式密码为41个字符，并且以字符*开头；
3. 旧格式密码为16个字符。

#define SCRAMBLE_LENGTH 20
#define SCRAMBLE_LENGTH_323 8
/* length of password stored in the db: new passwords are preceeded with '*' */
#define SCRAMBLED_PASSWORD_CHAR_LENGTH (SCRAMBLE_LENGTH*2+1)
#define SCRAMBLED_PASSWORD_CHAR_LENGTH_323 (SCRAMBLE_LENGTH_323*2)
/**
  Convert scrambled password to binary form, according to scramble type, 
  Binary form is stored in user.salt.
*/

static 
bool
set_user_salt(ACL_USER *acl_user, const char *password, uint password_len)
{
  bool result= false;
  /* Using old password protocol */
  if (password_len == SCRAMBLED_PASSWORD_CHAR_LENGTH)
  {
    get_salt_from_password(acl_user->salt, password);
    acl_user->salt_len= SCRAMBLE_LENGTH;
  }
  else if (password_len == SCRAMBLED_PASSWORD_CHAR_LENGTH_323)
  {
    get_salt_from_password_323((ulong *) acl_user->salt, password);
    acl_user->salt_len= SCRAMBLE_LENGTH_323;
  }
  else if (password_len == 0 || password == NULL)
  {
    /* This account doesn't use a password */
    acl_user->salt_len= 0;
  }
  else if (acl_user->plugin.str == native_password_plugin_name.str ||
           acl_user->plugin.str == old_password_plugin_name.str)
  {
    /* Unexpected format of the hash; login will probably be impossible */
    result= true;
  }

  /*
    Since we're changing the password for the user we need to reset the
    expiration flag.
  */
  acl_user->password_expired= false;
  
  return result;
}

    第五步，通过password()函数加密密码后，重新flush privileges就可以登录了。

mysql> update user set password=password('edgeyang') where user='edgeyang';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for edgeyang@'127.0.0.1';
+-------------------------------------------------------------------------------
-------------------------------------------+
| Grants for edgeyang@127.0.0.1
                                           |
+-------------------------------------------------------------------------------
-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'edgeyang'@'127.0.0.1' IDENTIFIED BY PASSWORD '
*3C316168860E5204C4A2AEDDF36D13E99D80A981' |
+-------------------------------------------------------------------------------
-------------------------------------------+
1 row in set (0.00 sec)

    最后，看看password函数的实现和mysql文档，可以发现password采用了两次sha1来加密密码。

    mysql password函数文档描述：http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html#function_password

inline static
void compute_two_stage_sha1_hash(const char *password, size_t pass_len,
                                 uint8 *hash_stage1, uint8 *hash_stage2)
{
  /* Stage 1: hash password */
  compute_sha1_hash(hash_stage1, password, pass_len);

  /* Stage 2 : hash first stage's output. */
  compute_sha1_hash(hash_stage2, (const char *) hash_stage1, SHA1_HASH_SIZE);
}