6.14 How to make MySQL secure against crackers

When you connect to a MySQL server, you should normally use a password. The password is not transmitted in clear text over the connection.

All other information is transferred as text that can be read by anyone that is able to watch the connection. If you are concerned about this, you can use the compressed protocol (in MySQL 3.22 and above) to make things much harder. To make things even more secure you should install ssh (see http://www.cs.hut.fi/ssh). With this, you can get an encrypted TCP/IP connection between a MySQL server and a MySQL client.

To make a MySQL system secure, you should strongly consider the following suggestions:

• Use passwords for all MySQL users. Remember that anyone can log in as any other person as simply as mysql -u other_user db_name if other_user has no password. It is common behavior with client/server applications that the client may specify any user name. You can change the password of all users by editing the mysql_install_db script before you run it, or only the password for the MySQL root user like this:

  shell> mysql -u root mysql
  mysql> UPDATE user SET Password=PASSWORD('new_password')
             WHERE user='root';
  mysql> FLUSH PRIVILEGES;
  

• Don't run the MySQL daemon as the Unix root user. mysqld can be run as any user. You can also create a new Unix user mysql to make everything even more secure. If you run mysqld as another Unix user, you don't need to change the root user name in the user table, because MySQL user names have nothing to do with Unix user names. You can edit the mysql.server script to start mysqld as another Unix user. Normally this is done with the su command. For more details, see Changing MySQL user.
• If you put a password for the Unix root user in the mysql.server script, make sure this script is readable only by root.
• Check that the Unix user that mysqld runs as is the only user with read/write privileges in the database directories.
• Don't give the process privilege to all users. The output of mysqladmin processlist shows the text of the currently executing queries, so any user who is allowed to execute that command might be able to see if another user issues an UPDATE user SET password=PASSWORD('not_secure') query. mysqld saves an extra connection for users who have the process privilege, so that a MySQL root user can log in and check things even if all normal connections are in use.
• Don't give the file privilege to all users. Any user that has this privilege can write a file anywhere in the file system with the privileges of the mysqld daemon! To make this a bit safer, all files generated with SELECT ... INTO OUTFILE are readable to everyone, and you can't overwrite existing files. The file privilege may also be used to read any file accessible to the Unix user that the server runs as. This could be abused, for example, by using LOAD DATA to load `/etc/passwd' into a table, which can then be read with SELECT.
• If you don't trust your DNS, you should use IP numbers instead of hostnames in the grant tables. In principle, the --secure option to mysqld should make hostnames safe. In any case, you should be very careful about using hostname values that contain wildcards!

The following mysqld options affect security:

--secure

IP numbers returned by the gethostbyname() system call are checked to make sure they resolve back to the original hostname. This makes it harder for someone on the outside to get access by simulating another host. This option also adds some sanity checks of hostnames. The option is turned off by default in MySQL 3.21 since it sometimes takes a long time to perform backward resolutions. MySQL 3.22 caches hostnames and has this option enabled by default.

--skip-grant-tables

This option causes the server not to use the privilege system at all. This gives everyone full access to all databases! (You can tell a running server to start using the grant tables again by executing mysqladmin reload.)

--skip-name-resolve

Hostnames are not resolved. All Host column values in the grant tables must be IP numbers or localhost.

--skip-networking

Don't allow TCP/IP connections over the network. All connections to mysqld must be made via Unix sockets. This option is unsuitable for systems that use MIT-pthreads, because the MIT-pthreads package doesn't support Unix sockets.