The reason for yesterday’s Basic OS/MySQL Security was a request to review a system and I was given the production server ‘root’ password in an email. Never email a ‘root’ password, especially including the hostname as well. Email is an insecure protocol that can be monitored by hackers. However, today’s basic security tip following a look at the system is:
Never store the MySQL ‘root’ user password in a ~root/.my.cnf file.
There is simply no reason to do so, and you expose your database to destruction or manipulation when a user has access to the ‘root’ OS user, for example via sudo.
I’ve heard excuses why the ‘root’ MySQL password has to be in a file, I’ve yet to be convinced.
Do you need to store a MySQL password in a file? Yes. Connection management for your application is an example, however that use should never be ‘root’ user to connect to your application.
You may also need to run scripts to backup your data with mysqldump. The solution is to create a dedicated user .e.g. ‘backup’, and then grant that user only the permissions necessary to do the specific task at hand. By default, a simple mysql command will never grant access without any authentication, the user will need to find the password.
As David Minor pointed out in comments, you can also with MySQL change the ‘root’ user name, which is not a bad idea for improved security. The follow steps perform this.
$ mysql -uroot -p[password] mysql> create user dba@localhost identified by '[newpassword]'; mysql> grant all on *.* to dba@localhost with grant option; mysql> exit # Check you can really login $ mysql -udba -pnewpassword mysql> select host,user from mysql.user; # Drop 'root' users as listed, generally mysql> drop user root@127.0.0.1; mysql> drop user root@localhost; mysql> drop user root@[hostname]; mysql> select host,user from mysql.user; mysql> exit
More information see the MySQL 5.1 Reference Manual at MySQL Privileges , Create User and Grant Syntax .
