Basic Security Guidelines

Regardless of whether you are running MySQL on Windows or Linux/Unix, and no matter whether you administer your own server or use a system provided by your Internet service provider, every developer needs to understand basic security guidelines. If you are accessing MySQL through your Internet service provider, there are several aspects of server security that you, as a non-root user, should not be able to modify or circumvent. Unfortunately, many Internet service providers pay no mind to security guidelines, leaving their clients exposed?and for the most part, unaware of the risk.

Starting MySQL

Securing MySQL begins with the server startup procedure. If you are not the administrator of the server, you won't be able to change this, but you can certainly check it out and report vulnerabilities to your Internet service provider.

If your MySQL installation is on Linux/Unix, your primary concern should be the owner of the MySQL daemon?it should not be root. Running the daemon as a non-root user such as mysql or database will limit the ability of malicious individuals to gain access to the server and overwrite files.

You can verify the owner of the process using the ps (process status) command on your Linux/Unix system. The following output shows MySQL running as a non-root user (see the first entry on the second line):

#> ps auxw | grep mysqld
mysql 153 0.0 0.6 12068 2624 ? S Nov16 0:00
--basedir=/usr/local/bin/mysql --datadir=/usr/local/bin/mysql/data
--user=mysql --pid-file=/usr/local/bin/mysql/data/ --skip-locking

The following output shows MySQL running as the root user (see the first entry on the second line):

#> ps auxw | grep mysqld
root 21107 0.0 1.1 11176 1444 ? S Nov 27 0:00
--basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --skip-locking

If you see that MySQL is running as root on your system, immediately contact your Internet service provider and complain. If you are the server administrator, you should start the MySQL process as a non-root user or specify the username in the startup command line:

mysqld --user=non_root_user_name

For example, if you want to run MySQL as user mysql, use

mysqld --user=mysql

Securing Your MySQL Connection

You can connect to the MySQL monitor or other MySQL applications in several different ways, each of which has its own security risks. If your MySQL installation is on your own workstation, you have less to worry about than users who have to use a network connection to reach their server.

If MySQL is installed on your workstation, your biggest security concern is leaving your workstation unattended with your MySQL monitor or MySQL GUI administration tool up and running. In this type of situation, anyone can walk over and delete data, insert bogus data, or shut down the server. Utilize a screen saver or lock screen mechanism with a password if you must leave your workstation unattended in a public area.

If MySQL is installed on a server outside your network, the security of the connection should be of some concern. As with any transmission of data over the Internet, it can be intercepted. If the transmission is unencrypted, the person who intercepted it can piece it together and use the information. Suppose the unencrypted transmission is your MySQL login information?a rogue individual now has access to your database, masquerading as you.

One way to prevent this from happening is to connect to MySQL through a secure connection. Instead of using Telnet to reach the remote machine, use SSH. SSH looks and acts like Telnet, but all transmissions to and from the remote machine are encrypted. Similarly, if you use a Web-based administration interface, such as phpMyAdmin (see for more information) or another tool used by your Internet service provider, access that tool over a secure HTTP connection.

In the next section, you'll learn about the MySQL privilege system, which helps secure your database even further.

    Part III: Getting Involved with the Code