Bug #71465 mysql.user row with plugin and password generates bogus warning

• Submitted: 24 Jan 2014 2:56
• Modified: 24 Jan 2014 8:03
• Reporter: Paul Dubois
• Status: Verified
• Category: MySQL Server: Pluggable Authentication
• Severity: S3 (Non-critical)
• Version: 5.5.37
• OS: Any
• CPU Architecture: Any

[24 Jan 2014 2:56] Paul Dubois

Description:
The purpose of the mysql_native_password and mysql_old_password authentication plugins is to authenticate against the password stored in the mysql.user table Password column. Yet in MySQL 5.5, if the plugin column is set to either of those plugins *and* the password is specified, the server complains about it by logging warnings to the error log. This makes no sense because those plugins are *designed* to use Password column contents.

How to repeat:
Example script:

SET old_passwords = 0;
CREATE USER 'x'@'localhost' IDENTIFIED WITH 'mysql_native_password';
SET PASSWORD FOR 'x'@'localhost' = PASSWORD('x');
SELECT * FROM mysql.user WHERE (User, Host) = ('x', 'localhost')\G
FLUSH PRIVILEGES;
DROP USER 'x'@'localhost';

SET old_passwords = 1;
CREATE USER 'y'@'localhost' IDENTIFIED WITH 'mysql_old_password';
SET PASSWORD FOR 'y'@'localhost' = PASSWORD('y');
SELECT * FROM mysql.user WHERE (User, Host) = ('y', 'localhost')\G
FLUSH PRIVILEGES;
DROP USER 'y'@'localhost';

Run the script in 5.5.37 (current 5.5 source tree), and no errors are seen:

mysql> CREATE USER 'x'@'localhost' IDENTIFIED WITH 'mysql_native_password';
Query OK, 0 rows affected (0.00 sec)

mysql> SET PASSWORD FOR 'x'@'localhost' = PASSWORD('x');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM mysql.user WHERE (User, Host) = ('x', 'localhost')\G
*************************** 1. row ***************************
                  Host: localhost
                  User: x
              Password: *B69027D44F6E5EDC07F1AEAD1477967B16F28227
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: 
1 row in set (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP USER 'x'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'y'@'localhost' IDENTIFIED WITH 'mysql_old_password';
Query OK, 0 rows affected (0.00 sec)

mysql> SET PASSWORD FOR 'y'@'localhost' = PASSWORD('y');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM mysql.user WHERE (User, Host) = ('y', 'localhost')\G
*************************** 1. row ***************************
                  Host: localhost
                  User: y
              Password: 606706696665cdda
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_old_password
 authentication_string: 
1 row in set (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP USER 'y'@'localhost';
Query OK, 0 rows affected (0.00 sec)

And this is what the server writes to the error log:

140123 20:48:48 [Warning] 'user' entry 'x@localhost' has both a password and an authentication plugin specified. The password will be ignored.
140123 20:49:33 [Warning] 'user' entry 'y@localhost' has both a password and an authentication plugin specified. The password will be ignored.

Those warnings are spurious. They lead to the impression that something is wrong with the account when that is not the case.

These warnings do not appear if the script is run against MySQL 5.6 or 5.7, so this appears to be a 5.5-only problem.

[24 Jan 2014 8:03] MySQL Verification Team

Hello Paul,

Thank you for the bug report and test case.
Verified as described on 5.5.37

//
140126  4:04:43 [Warning] 'user' entry 'x@localhost' has both a password and an authentication plugin specified. The password will be ignored.
140126  4:04:43 [Warning] 'user' entry 'y@localhost' has both a password and an authentication plugin specified. The password will be ignored.

Thanks,
Umesh