Proposed changes to user management in MySQL 5.7

In May we proposed deprecating and removing the old password format in MySQL 5.7. I am happy to report, that this proposal has gone ahead, and can already be seen in the 5.7 DMR5 release notes!

With old passwords removed, and with recent versions of MySQL also offering new options for authenticating to MySQL, today I wanted to write about three additional proposed changes that follow suit:

  1. Deprecate the PASSWORD() function

    As we already support multiple authentication methods (and may desire additional in the future), calling the PASSWORD() function to generate a password hash has a strange user experience.

    What I mean by this, is that PASSWORD() is not able to determine what hash format to generate without knowing the user's authentication method. i.e.

    mysql [localhost] {msandbox} (mysql) > select user,host,password,plugin from mysql.user;
    +------------+-----------+-------------------------------------------+-----------------------+
    | user       | host      | password                                  | plugin                |
    +------------+-----------+-------------------------------------------+-----------------------+
    | root       | localhost | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | mysql_native_password |
    | sha256user | localhost | $5$;Uq=HtOa2X:GRS$qKn9rc0xhh4rq3XKz | sha256_password       |
    +------------+-----------+-------------------------------------------+-----------------------+
    2 rows in set (0.00 sec)
    

    In current releases of MySQL there exists a variable called old_passwords which can hint at which plugin should be used. This creates a usability issue, since having a 'back door' influence the return value of a function is not very intuitive:

    mysql> select password('test'); /* mysql_native_password */
    +-------------------------------------------+
    | password('test')                          |
    +-------------------------------------------+
    | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
    +-------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> set old_passwords=1; /* old passwords */
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> select password('test');
    +------------------+
    | password('test') |
    +------------------+
    | 378b243e220ca493 |
    +------------------+
    1 row in set (0.00 sec)
    
    mysql> set old_passwords=2; /* sha256_password */
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select password('test');
    +----------------------------------------------------------------------+
    | password('test')                                                     |
    +----------------------------------------------------------------------+
    | $5$HN
    wN_=@;`|uzm//b$uxYgsCdA.Y/35HKXtb/DhHceH78G8QSjBA/GPfxVH70  |
    +----------------------------------------------------------------------+
    1 row in set (0.01 sec)
    
    

    A more consistent usage would be password(password_string, auth_plugin), but we believe we have a more intuitive solution than this (see #3) and are proposing to deprecate the use of the PASSWORD() function in MySQL 5.7.

  2. Deprecate the old_passwords variable

    The old_passwords variable was originally designed to be a boolean, and offer a way to restore old password hash generation. Starting with MySQL 5.6, this has been repurposed to have 3 possible values:

    • 0 = mysql_native_password (4.1 and above)
    • 1 = mysql_old_password (legacy format, removal in 5.7)
    • 2 = sha256_password (5.6 and above)

    Since we will be removing support for what is called "the old password hash" in MySQL 5.7, this makes the name choice of this variable somewhat confusing. While we could rename the variable to auth_plugin (or similar), the variable won't actually be required, provided that the PASSWORD() function no longer requires a 'back door' variable as described above.

    We are proposing to deprecate the variable old_password in MySQL 5.7.

  3. Deprecate the syntax SET PASSWORD .. = PASSWORD()

    The last piece of this proposal is to change the syntax for setting a user's password
    to eliminate the use of the PASSWORD() function. The current syntax is:


    mysql> SET PASSWORD = PASSWORD('test');
    mysql> SET PASSWORD FOR 'sha256user'@'localhost' = PASSWORD('test');

    We are proposing that this be changed to:


    mysql> SET PASSWORD = 'test'; /* raw password */
    mysql> SET PASSWORD FOR 'sha256user'@'localhost' = 'test'; /* raw password */

    With the new syntax, we will always know the user's account which we are operating on and consequently be able to apply the hashing function which applies to their authentication plugin.

    Further more, it will encourage users to reduce usage of copying password hashes from one account to another (although inserting/updating hashes in the raw mysql.user table will still be supported).

    We are aware that using password hashes is a way of obfuscating a password locally on the client so that it does not need to be sent over the wire, but we believe we have a superior solution to this in TLS.

We are seeking feedback from the community in regards to these proposed changes:

  • Do you agree that setting a variable before calling the PASSWORD() function causes a usability issue?
  • Would you agree that the syntax SET PASSWORD = 'test'; is more intuitive?
  • In the case of the SET PASSWORD syntax no longer accepting hashes but instead passwords, do you agree that it is reasonable to require users use TLS for safe password changes? An alternative to TLS is to make changes locally via unix socket or via named pipe.

Please leave a comment, or get in touch!

Published by

morgo

I joined MySQL AB in 2006, left, and am now back at Oracle working on the MySQL team. I've also worked at Percona and InPowered.

  • Ammon Sutherland

    I might be alone in this but I don't find this too difficult to understand or explain to others in its current implementation. I am glad to see the old pre-4.1 passwords being deprecated, but otherwise I rather like the way things work and find it nice and simple. Maybe that makes me sound old, though, I don't know :)

  • http://karwin.blogspot.com Bill Karwin

    I agree that the old_passwords variable was already confusing to most users, and when old_passwords=2 was introduced I just had to shake my head. There is no other way it could have been made to be more confusing! Getting rid of this variable will be a mercy.

    Some applications use the PASSWORD() function for their application-level logins. They're not supposed to. There's a prominent notice against that in the docs. But I see it regularly. If using that function generates a warning or an error log entry, it will become very noisy.

    The proposed syntax change for SET PASSWORD is an improvement. It always seemed redundant to SET PASSWORD=PASSWORD... But this change will break many existing DBA apps that provides a UI for changing users' MySQL passwords (e.g. phpMyAdmin). As this proposed change is made, the vendors of DBA tools should be notified explicitly (log an issue in their bug tracker, for instance).

    Requiring TLS is reasonable for environments that need secure transmission. Most sites with that requirement are probably using tunnels or VPN's anyway, so they might choose not to use TLS at the MySQL level. We still see people hitting bugs and library conflicts while trying to get TLS to work with MySQL, by the way.

    I assume another workaround for setting passwords using literal hashes is to use DML against mysql.user.

    • http://www.tocker.ca/ Morgan Tocker

      Hi Bill, Great feedback. Thank you!

      I have seen an application use the PASSWORD() function to hash application-level data too. I would remind that this is a deprecation proposal for 5.7, and thus the absolute earliest it would be removed is MySQL 5.8.

      I like your suggestion to contact phpMyAdmin specifically. I'm going to reach out now.

  • Daniël van Eeden

    I agree with your plan to cleanup user management.

    This might also fix these bugs:
    Bug #71359 - Validate and SHA256 plugins do not work together
    Bug #70744 - SHOW GRANTS should indicate a authentication plugin

    Also two other things should be taken in account:
    1. The NO_AUTO_CREATE_USER sql mode. It's too easy to accidentally create an account without password on a default install. Most users and software only use GRANT.. instead of CREATE USER.../GRANT... So this might not be easy.

    2. It's often needed to do "SELECT user,host,password FROM mysql.user"... I would rather use "SHOW GRANTS FOR ALL USERS" or something similar. Directly editing/querying mysql.* should not be needed for something this common.

    • Simon J Mudd

      Because of the lack of SHOW GRANTS FOR ALL USERS I've been using pt-show-grants for so long now it's the only way I ever query grant information on a server. Thanks Percona for writing this but maybe it is time that Oracle provided a command to do the same thing?

      • Daniël van Eeden

        I think mysqluserclone etc from mysql utilities can do that. But within MySQL would be better. And not all tools and commands corectly copy authentication plugin settings.

  • Simon J Mudd

    1. Setting a variable to change functional behaviour is never ideal, so cleaning that up seems good.

    2. SET PASSWORD = 'raw password' might seem more intuitive but you are already abusing the SET command. Don't suddenly change the way it works or you will end up confusing people and scripts. Provide a new syntax which is clearer and then deprecate the old one. Personally I'd go for something like the following:

    CHANGE PASSWORD TO
    PASSWORD = 'test' /* use default auth-plugin */;

    or

    CHANGE PASSWORD TO
    ENCRYPTED_PASSWORD = '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29',
    AUTH_PLUGIN = 'mysql_native_password'

    or

    CHANGE PASSWORD TO
    ENCRYPTED_PASSWORD = ENCRYPTED_PASSWORD( 'test', 'mysql_native_password' )
    AUTH_PLUGIN = 'mysql_native_password'

    Perhaps mysql can try to guess the plugin to make life easier (if not provided). That's fine but using a new syntax you avoid confusing people who used the old one and at the same time you provide a generic way to provide raw or encrypted passwords as needed. You also allow for using any new functions needed to support these (if needed).

    A suggestion is made that we can change the system tables directly, but changing system tables requires an additional FLUSH PRIVILEGES for the grants to become active so behaviour is not quite the same.
    When migrating to a new MySQL version and having a master run 5.6 and a slave 5.7 upgrading grants using systems tables is going to be tricky and with any change in syntax or behaviour this is also going to be difficult so removing completely any "old" behaviour is likely to be problematic.

    Requiring the use of a connection over TLS or unix sockets to set passwords sounds sensible but may cause issues. Ideally it would be good to be able to provide a list of "secure networks" from where TLS is not required. This then gives the DBA the flexibility to decide where to apply these rules. Default settings could assume only localhost and unix sockets are secure, so that keeps the design safe but allows freedom to adjust as needed. So a new system table like mysql.secure_networks would be good.

    • http://www.tocker.ca/ Morgan Tocker

      Thank you for the detailed comment. In response to (2), I wanted to comment that 5.6 introduced the ALTER USER command:

      http://dev.mysql.com/doc/refman/5.6/en/alter-user.html

      So far it is only used to expire users, but maybe ALTER USER [user spec] CHANGE PASSWORD TO 'clear password' would be considered consistent?

  • Federico Razzoli

    I think that, if a warning is generated on each PASSWORD() call, there will be further log pollution. Also note that many applications still use MyISAM, the mysql PHP module, etc. Thus, I think that the developers of the most widely used applications will simply ignore that warning and keep on using PASSWORD().

    I suppose that the problem with PASSWORD() is that you don't want to have a function which is supposed to use MySQL's authentication algorithm. So, my suggestion is: just don't guarantee that the PASSWORD() algorithm is the same used for authentication. Does this make sense for you? As far as I know, the change should be painless for everyone.

  • jynus

    I agree with the underlying idea, but I am not sure about the implementation. Yesterday I had to clone on user to add an extra IP where it can connect. I do not know its password. How would I do that easily without using using the GRANT.. IDENTIFIED BY PASSWORD syntax? (I suppose that goes away, too?)