MySQL 5.6 security vs ease of use

MySQL 5.6MySQL 5.6 surely changes the game when it comes to security vs ease of use. Before MySQL 5.6 we would get default MySQL installation being pretty insecure – the user “root” will be created with no password as well as anonymous user with limited access from local host (though still enough to cause DOS attack or crash MySQL Server.

There were some exception to this rule – such as Debian/Ubuntu install scripts would interactively suggest you to set password for root user if it was not set. Still most users would get MySQL install with root account and no password.

This is not the case with MySQL 5.6 when you’re doing fresh MySQL install! Installing official RPM on CentOS6 I’m getting this:

So we’re getting random password for the root account by default instead of empty one. Furthermore it is not stored in the root directory my.cnf but separate .mysql_secret file so you need to enter it explicitly to connect to the server for a first time – and it is for a good reason as this is temporary password only. You can’t really use MySQL Server until you change it:

As Such MySQL will refuse any statements even ones which do not cause any database contents access until you change password with SET PASSWORD command.

If you’re looking to keep password you can run:

You also have an option to go back to the old behavior and remove the password for account (this is what I do on MySQL running on VirtualBox on my Laptop as I keep it for testing only)

So at least with RPM Install MySQL 5.6 is getting more secure, but adding a little more effort after installation is worthwhile. I hope this change will make things more secure and will not discourage a lot of users by complicating the install process.

Share this post

Comments (9)

  • Giuseppe Maxia

    As I mentioned in an article a few months ago, this security features is not the same for all platforms:
    Users of Oracle Unbreakable Linux don’t get the random password.

    While the new installation feature makes the database more secure, it only works well if
    1) someone is supposed to use the database server manually, rather than running a script;
    2) you have installed only one instance of MySQL in the host.

    August 19, 2013 at 11:08 am
  • Peter Zaitsev

    Thanks Giuseppe,

    I agree inconsistency is bad. The documentation is not very clear about behavior among various platforms (and of course distributions can change it outside of Oracle control)

    The basic RPM install assumes single instance of MySQL host anyway. Multi instance management have typically be outside of what stock distribution provide

    August 19, 2013 at 12:36 pm
  • Marcelo Altmann

    Seems like MySQL added the option to EXPIRE an user password in MySQL 5.6
    we can force the user to change the password on next login by issue an ALTER USER ‘user’@’host’ PASSWORD EXPIRE; or changing the value of the field password_expired to Y on mysql.user table;
    It’s a big step forward on security.

    August 20, 2013 at 4:47 am
  • Ronald Bradford

    If you elect to ignore the new 5.6 rpm installation and simply run mysql_install_db for a new datadir, there is no improved security either.

    I concur the inconsistency reflects poorly on an attempted better security practice.

    August 20, 2013 at 7:32 pm
  • Ivan Zahariev

    Does the SET PASSWORD statement get logged in the ~/.mysql_history file? If it does, this isn’t pretty secure as it still leaves your “root” password stored in plain-text.

    August 22, 2013 at 3:17 am
  • Giuseppe Maxia

    SET PASSWORD is not logged in .mysql_history.
    It was, until MySQL 5.5, but in MySQL 5.6 it is not.
    If you enable the general log, the statement is reported, but the string used for the password is masked.

    130822 9:56:19 8 Query SET PASSWORD FOR msandbox=<secret>

    August 22, 2013 at 4:05 am
  • Songgao Li

    How can I aviod this warining?
    I want to connect mysql in the bash shell script, and the connect string like this
    CONN_MYSQL=”-u$USER -p$PASS -S /app/mysql/mysql/mysql.sock $DB –show-warnings=false”
    But the warning is also exist,
    Warning: Using a password on the command line interface can be insecure.
    How can I disappear it. Thanks very much!

    January 23, 2014 at 10:41 pm
  • M

    Songgao Li,

    Store your credentials in /root/.my.cnf

    Here is an example:

    # cat /root/.my.cnf


    April 18, 2014 at 9:29 pm
  • Carlo Verdecchia

    In this post you said that we have “an option to go back to the old behavior “, that is install with RPM without random password generation?

    How can I achive this? It’s a parameter that I’ve to pass?
    I’ve read official mysql site but I didn’t find anything.

    November 14, 2014 at 4:23 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.