If you can do either of these on your MySQL production server, you need to correct immediately.
1. Login directly to your MySQL server as the ‘root’ Linux Operating System user. For example:
$ ssh root@server-name Password: ************
2. Connect to MySQL database as the ‘root’ MySQL user without a password.
$ mysql -uroot
Here are the 60 second fixes to address these major security flaws.
To disable direct root access to your server, first ensure you can login as a normal user, then su – or sudo su – appropriately. Then, disable ssh root access with the following configuration change.
$ vi /etc/ssh/sshd_config
# ensure this is commented out and set to no
PermitRootLogin no
$ /etc/init.d/sshd restart
This will stop any brute force attack on your server by automated bots and password generators.
Second, the default installation of MySQL *DOES NOT SET A PASSWORD*. Apart from being crazy, I’ve seen production systems without a MySQL ‘root’ user password. To set a password run:
$ mysqladmin -uroot password SOMEPASSWORD $ mysqladmin -uroot -pSOMEPASSWORD -hlocalhost password SOMEPASSWORD
It could be an options file (like ~/.my.cnf) that stores the password. Generally if I’ve had to ssh to the server, I probably want to be mysql-root on it, so I rely on filesystem security to store the password (f they can read my ~/.my.cnf, they can probably read the datadir, so I’ve never considered this an issue).
But I agree – logging in should always be ssh keys, and ideally as another user who uses sudo to upgrade.
The first thing I do after setting up and brand, spanking new server, is change the MySQL ‘root’ user to another name and set a password. In contrast to Linux/Unix, the user name of ‘root’ is not required to be on the MySQL server.
Ronnie,
I agree completely. There are a lot of little things that can really tighten security around a database server. Too often I see services running on the OS that should never be running on a platform with a database server (i.e. telnet). Services not being used to run the OS should always be turned off.
Good blog!
Hi,
May I recommend not to set a new password in the following form:
mysqladmin -uroot password SOMEPASSWORD
since it is then stored in bash (or other shell) history. I think not may will know how to clean history, or that it can be done as well.
Some additional thoughts:
1. Give the account Least Access Required. Do not give any account global privileges, unless absolutely required for that account to function. For example, if ‘appuser’ needs to only perform basic DML on the ‘foobar’ database, then just give them SELECT, UPDATE, DELETE and INSERT privileges on ‘foobar’ (You should also limit what server(s) ‘appuser’ can connect from. Yes, I was one of those few users that actually used the ‘host’ table in the mysql database.) Other functionality to consider: Replication, shutdown, grant, etc. Another thing to note, don’t give any account write privileges to the mysql database. If you do, you’ve just handed that user the keys to the kingdom. For example, if ‘appuser’ has global SELECT, UPDATE, DELETE and INSERT privileges, then they also have permissions to make changes to the tables in the mysql database through INSERT INTO, REPLACE INTO and DELETE FROM, regardless of the user’s GRANT privilege.
2. No accounts without passwords, no passwords buried in .my.cnf files. Trust me, if you type the password long enough, you’ll remember it. “But what about batch jobs?” You might ask. If you are not using MySQL 5.1 (and thus don’t have access to the Event Handler), then yes, you will have to store the password in a file somewhere. But keep in mind the Least Access Required concept. Make sure this user can ONLY do what it HAS to do!
3. Audit. I routinely audit the mysql tables and compare them to the last version checked into our revision control system. I also record changes made in our change management system (or at least blog it internally if you don’t have a change management system.)