MySQL and Password Security


The first thing any MySQL user learns is mysql -u user database -p to use the mysql client program to connect with an instance. In this case the user will be prompted for the password and the given password will be encrypted before being passed to the server as part of the authentication process. Some folks (and the majority of scripts) will use -p password on the com
I yanked out the binary atamand line which is less secure as the password is in plain text for anyone privileged or sneaky to see. You could put the password in the ~.my.cnf file but once again the password is there in plain text.

MySQL 5.6 introduced the mysql_config_editor that stores authentication credentials in an encptryed file named .mylogin.cnf in your home directory. MySQL client programs can read this file. No exposure of the password on the command line or in an environmental variable. This file is readable only by the user and not by others.

As found in other MySQL option files, the .mylogin.cnf file is made up of option groups and each group is a login-path. This file is read FIRST which means you need to make sure that your other option files do not reset what you have set.

To look at the file, use mysql_config_editor print -all
mysql_config_editor print --all
[local]
user = dstokes
host = localhost
[root]
user = root
host = localhost

A simple concatenation will show gibberish.

Each section, in the previous example local and root, has the needed credentials for connection to a server. Note the password is there and encrypted but not displayed.

Invoking mysql –login-path=root world passes on the information from the appropriate login-path. This information can contain all the usual players such as hostname, socket, and port.

So let us set up a root connection to a test server.

mysql_config_editor set --login-path=testserver --host=testbed.stokes.net --user=dave --password
Enter password:
$mysql_config_editor print --all
[local]
user = dstokes
host = localhost
[root]
user = root
host = localhost
[testserver]
user = dave
password = *****
host = testbed.stokes.net
$

There are corresponding arguments to remove entries, or to reset the file.

So you get better security or an easier way to set credentials for your users.

One Response to MySQL and Password Security

  1. […] was intrigued by a post from David Stokes entitled MySQL and Password Security.  It discusses some new options for command line password security in MySQL 5.6.  First, I want […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s