You are here: Home / Technologies / Databases / More Basic MySQL Security

More Basic MySQL Security

by

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 [email protected];
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.

Comments

  1. Jaka Jančar says

    If someone has access to the unix root account, then they can change the root mysql password anyways.

    What’s more insecure is passing password as an argument to MySQL, like you’ve written (-p[password]), since that can really be seen by anyone.

  2. says

    So you say that you shouldn’t keep the root password in a file that only root can read, but you don’t mention that the *same user* has the permissions to just restart the server with a –init-file to change the password, skip grants, or create a new user with just as powerful permissions?

    In your example the password also ends up in the bash history file :p

    I’ve never liked having to enter passwords to a service I should already have permissions to. It encourages people to use things that they can remember easily, which generally means that the passwords are weak, or repeated elsewhere.

  3. says

    While I agree with Morgan in principle, setting a password will stop your average “curious teenage hacker” from causing trouble because he was given the opportunity, but would of course not stop someone even reasonably novice, yet determined to sabotage or steal data.

    But yes, ~/.bash_history and ~/.mysql_history is indeed another thing to bear in mind for the security conscious person!

  4. Andrew says

    if you’re root, you can already own a MySQL database with fairly basic knowledge with or without a mysql ‘root’ account. :) Using .cnf files is much preferred to using -p and (as Morgan pointed out) having this exposed and logged in various ways.

  5. says

    Hi,

    Shameless promotion: please check out oak-secutiry-audit, a utility which audits your MySQL installation against many security holes and weaknesses, covering empty passwords, duplicate passwords, permissive grants, weak configuration etc.

    While Linux security is often considered good, an astonishing weakness is “ps aux”, where *every* user can see *every* process running. Therefore, even user “games” can see that user root is running “mysql -pmypassword”.
    I find this a much higher risk than putting the MySQL’s root password in file, where a user need to gain access to machine’s “root”.

    Still, linux security is by far higher than MySQL security (I have no proof for this, just the common understanding). If you chmod 600 /root/.my.cnf, may be move it to /root/surprise/unexpected.filename (who says it’s to be called my.cnf?), you add some more confusion (though still easily hacked by the experienced).

    Obviously, for specific cronjob tasks, just create the required users with limited privileges, never with GRANT OPTION. However, I find that for many tasks, the abused SUPER privilege is required, which allows for nasty stuff like KILL etc.).

  6. says

    Shlomi: “ps aux” does not actually reveal the password. The mysql command line client obfuscates it – if you check the output of “ps aux | grep mysql” you will see, that the password string has been replaced with “xxxxx”.