MySQL 5.6 has introduced a set of new features related to security (default randomised root password at install, password Validation Plugin ...). MySQL now also provides a method for storing authentication credentials securely in an option file named .mylogin.cnf.
This new feature has been introduced to avoid exposing password when running MySQL scripts. Its behaviour and limitations have been well described here :
http://mysqlblog.fivefarmers.com/2012/08/16/understanding-mysql_config_editors-security-aspects/
In all previous version and also in 5.6 if you run a MySQL script without interactively asking for password the password is exposed on the commands line and it can be accessed though a "ps" command or through history files.
With MySQL 5.6 it is now possible to transform a command like this :
mysql -hlocalhost -uroot -pmypass
into :
mysql --login-path=safelogin
To create this login-path containing the credential
mysql_config_editor set --login-path=safelogin --host=localhost --user=root --password
The login file must be readable and writeable to the current user, and inaccessible to other users. Otherwise, mysql_config_editor ignores it, and the file is not used by client programs, either.
-rw-------. 1 sfrezefo sfrezefo 152 15 févr. 21:25 .mylogin.cnf
To list the content of this login file you can run mysql_config_editor and through this command you will not see the password :
[sfrezefo@serge ~]$ mysql_config_editor print --all [local] user = localuser password = ***** host = localhost [safelogin] user = root password = ***** host = localhost
So one of the principal benefits of mysql_config_editor is ease-of-use. But users must realize that even though it uses AES for encrypting the credentials security is not the main point. In fact the key is left on the door you just have to turn it to enter.
I have developed a very basic program "mysql_showconfigpwd" that expose the credentials. This could be useful if you have forgotten your encrypted credentials.
[sfrezefo@serge ~]$ mysql_showconfigpwd File exists. file_buff [local] user = localuser password = manager1 host = localhost [safelogin] user = root password = manager1 host = localhost
The way mysql_config_editor works is quite simple : All the information associated with a login path is stored encrypted through AES. A random key is generated which is used to encrypt all the login path information (password included).
All these information are stored in a file. But the key used to encrypt the login path is store in clear.
The OS protection is thus the only thing that protect the content of the file. This is the same issue you get with ssh keys except in that case you have the idea of key pair.
This is even more critical because beside the operating system the database itself could get access to the login file.
A database user with the FILE privilege can do a LOAD DATA INFILE and then get access to all the encrypted password. This problem is not an easy one and we always fall back to the egg and hen problem 🙂 .All mechanism based on private key encryption meet these issue of where to sore the key. How does it work with other databases. Oracle 11G has the same concept "Secure External Password Store". The only difference is that being closed source it create a false feeling of security 🙂
Here is the code of "mysql_showconfigpwd.cc" :
#include "my_config.h" #include "my_aes.h" #include "client_priv.h" #include "my_default.h" #include "my_default_priv.h" #define MY_LINE_MAX 4096 #define MY_LOGIN_HEADER_LEN (4 + LOGIN_KEY_LEN) static int g_fd; static size_t file_size; static char my_login_file[FN_REFLEN]; static char my_key[LOGIN_KEY_LEN]; int main(int argc, char *argv[]) { DYNAMIC_STRING file_buf, path_buf; char cipher[MY_LINE_MAX], plain[MY_LINE_MAX]; uchar len_buf[MAX_CIPHER_STORE_LEN]; int cipher_len = 0, dec_len = 0, total_len = 0; MY_STAT stat_info; const int access_flag = (O_RDWR | O_BINARY); init_dynamic_string(&path_buf, "", MY_LINE_MAX, MY_LINE_MAX); init_dynamic_string(&file_buf, "", file_size, 3 * MY_LINE_MAX); if (!my_default_get_login_file(my_login_file, sizeof(my_login_file))) { fprintf(stderr, "Error! Failed to set login file name.\n"); goto error; } if (my_stat(my_login_file, &stat_info, MYF(0))) { fprintf(stderr, "File exists.\n"); file_size = stat_info.st_size; if ((g_fd = my_open(my_login_file, access_flag, MYF(MY_WME))) == -1) { fprintf(stderr, "Error! Couldn't open the file.\n"); goto error; } } if (my_seek(g_fd, 4, SEEK_SET, MYF(MY_WME)) != 4) { fprintf(stderr, "Error! Couldn't seek 4.\n"); goto error; } if (my_read(g_fd, (uchar *) my_key, LOGIN_KEY_LEN, MYF(MY_WME)) != LOGIN_KEY_LEN) { fprintf(stderr, "Failed to read login key.\n"); goto error; } if (file_size) { while (my_read(g_fd, len_buf, MAX_CIPHER_STORE_LEN, MYF(MY_WME)) == MAX_CIPHER_STORE_LEN) { cipher_len = sint4korr(len_buf); if (cipher_len > MY_LINE_MAX) { fprintf(stderr, "Error!cipher_len > MY_LINE_MAX.\n"); goto error; } if ((int) my_read(g_fd, (uchar *) cipher, cipher_len, MYF(MY_WME)) == cipher_len) { if ((dec_len = my_aes_decrypt(cipher, cipher_len,(char *) plain, my_key, LOGIN_KEY_LEN)) < 0) { fprintf(stderr, "Error! failed to decrypt the file.\n"); goto error; } total_len += dec_len; plain[dec_len] = 0; dynstr_append(&file_buf, plain); } } fprintf(stderr, "file_buff %s \n", file_buf.str); } error: dynstr_trunc(&file_buf, 0); dynstr_trunc(&path_buf, 0); my_close(g_fd, MYF(MY_WME)); dynstr_free(&file_buf); dynstr_free(&path_buf); exit(0); }
Tu use it install this file in the MySQL source tree (mysql-5.6.8-rc/client in my case).
Add these 2 lines to the CMakeLists.txt in this folder.
MYSQL_ADD_EXECUTABLE(mysql_showconfigpwd mysql_showconfigpwd.cc) TARGET_LINK_LIBRARIES(mysql_showconfigpwd mysqlclient)
Go to the root of your MySQL source tree and do :
rm CMakeCache.txt cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql56 make -j 8 sudo make install