mySQL/Security

mySQL is easily one of the most popular databases in use today, but awareness of proper deployment procedures hasn't kept up with its popularity.

So here are a few basic mySQL security tips:

Limit TCP connections to localhost

Clients can talk to mySQL via a local UNIX domain socket (usually /var/lib/mysql/mysql.sock) or TCP/IP via port 3306. However, out of the box, most mySQL installations allow access from any host, and this must be fixed in /etc/my.cnf (in RedHat 8.0 and 9.0 firewall rules block incoming TCP traffic to most ports, but I'd rather make sure things are properly secure at all levels).

To limit access to the local machine only, bind the mysqld to 127.0.0.1. You can also change the TCP port (I like to declare it explicitly, even if I'm using the default):

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
bind-address=127.0.0.1
port=3306

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

This should be mandatory upon install (and actually is enforced by install scripts in some distros), but here goes:

# mysqladmin -u root password <the new password>

Drop the test database and remove the %@localhost grants - leave only root@localhost in:

# mysql -u root -p
Password:
mysql> drop database test;
Query OK, 0 rows affected (0.03 sec)

mysql> use mysql;
mysql> delete from user where Host not like 'localhost';
...
mysql> delete from user where User not like 'root';
...
mysql> flush privileges;

Create a user per each application (or role) you need, and don't grant DROP, DELETE (or even INSERT) privileges unless you definetly have to. This will save you a lot of grief when deploying applications on the Net (or even when developing - it's not hard to wipe out an entire table by mistake when you're debugging code).