Tuesday, June 4, 2013

MySQL 5.6 Experiences - .mylogin.cnf and mysql_config_editor

Having  basic ideas of how I am going to describe new features explained, I can proceed with some real (and I hope useful) content. As I read this page about new features from top to bottom, let's start with security improvements...

.mylogin.cnf and mysql_config_editor



  • you can store authentication credentials encrypted in an option file named .mylogin.cnf (in user's home directory or in %APPDATA%\MySQL on Windows)
  • password is no longer stored in plain text (like in .my.cnf) and still is not exposed in the command lines...
  • you have to use mysql_config_editor utility to create the .mylogin.cnf file
  • but if someone can read .mylogin.cnf, they have your MySQL password
  • ~/.mylogin.cnf is not a more secure place to store your password than ~/.my.cnf



(!) http://mysqlblog.fivefarmers.com/2012/08/16/understanding-mysql_config_editors-security-aspects/ - Todd explains that "ease of use" was the goal of this feature, not actually security
(!?) http://serge.frezefond.com/2013/02/mysql-5-6-credentials-securely-stored/ - some explanations on what this new feature is about and, most importantly, code of the mysql_showconfigpwd.cc program from Serge to expose the credentials stored
(!?) http://mysqldump.azundris.com/archives/104-.mylogin.cnf-password-recovery.html - Kristian explains file format and provides PHP code to expose the credentials stored 
(?#) http://www.skysql.com/blogs/kolbe/mysql-56-security-through-complacency - Kolbe explains (among other rambling on MySQL 5.6 security features) how to "hack"  the mask_password_and_print() function from  client/mysql_config_editor.cc  file in the source code to build mysql_config_editor  version that will show stored password in plain text. Bug #68602 mentioned in this post is not related to this file and tool (but related to security, still "Verified" as of today and will be discussed later in this series).



New MySQL features always come with related bugs. This simple enough tool and new file caused several related reports:

Bug #68680 - manual had some wrong details on where the .mylogin.cnf file is located on Windows, now fixed
Bug #68034 - while it is declared a "Duplicate", in the bug report you can read how this feature can be used to workaround Bug #66546 that nobody is going to fix (see Todd’s comment)
Bug #68277 - build problem affecting mysql_config_editor, fixed in 5.6.11
Bug #66546 - some people are not happy that there is no way to hardcode password in the script,
without getting warning. But who cares? Only Todd, who explained how to use one new security improvement to workaround problem created by another security improvement...

If you have any new links or bug reports related to the topic of this post, please, send them in comments.



While some more secure approach to store and provide authentication credentials for scripts was requested many times and by many users, I am not sure this new feature adds anything to security. IMHO it just may simplify work for a lazy DBA who does not remember proper permissions for .my.cnf file...

At the same time, this new feature probably caused additional work for developers and QA of MySQL Connectors and Workbench (as I have reasons to think it was probably decided that this time, for 5.6 GA, connectors and tools should really support all new GA features at the moment of GA release). I had found only one related bug in the public bugs database to prove this point (Bug #68356 - problems in Workbench tests that cover both MySQL 5.5 and 5.6), but who knows how many of them were reported internally and then fixed...

I share concerns that this feature may cause wrong feeling of improved security, that's why I talked about it during my presentation and had written this post. I agree with the following comment to Kolbe's post from Sergei Golubchik though:
"There's not much one can do to improve password handling. The mysql client needs to store some bit of information somewhere that allows to identify a user to the server. So if a bad guy would have access to this bit of information - no matter how obfuscated or encrypted it is - he will be able to impersonate the user just as well by repeating whatever mysql client does."


  1. Valeriy,

    You forget to mention the most obvious solution to The Password Problem : just don't use passwords in scripts (and from there on the command line).
    Passwords in scripts don't provide any additional security IMHO.

  2. Yes, this solution is obvious, but with old scripts that worked for years (even if they were neither secure nor perfect) it takes a lot of time to fix them. Let me just quote user comment in the bug report:

    "Does the MySQL development team have ANY idea how many man-hours of work they have inflicted on DBAs and developers relying on command line input responses that DO NOT return the word "Warning" or "Error" in the case of successful execution??? I alone will be spending at least 100 hours to upgrade and test all our in-house maintenance, and installation scripts to work around this idiotic warning."

  3. There doesn't seem to be an option to specify the location of the .mylogin.cnf file so it makes scripting very difficult.