In this chapter we'll discuss general security issues you should consider when running MySQL. Although we cannot be comprehensive in the space of a single chapter, we will give you a list of the most important don'ts. We will cover the following:
• How the privilege system works in practice
• Securing accounts
• Securing your installation files
• Filtering user data
• Other tips
In Chapter 11, “Managing User Privileges,” we discussed user account creation, granting and revoking privileges, and the grant tables. We will begin this chapter by discussing how your MySQL server applies the privileges you have granted.
There are two stages to the privilege system. In the first stage, MySQL checks whether a user is allowed to connect to the server at all. The user table in the mysql database is used for this purpose. MySQL looks up your username and password as entered and the host from which you are trying to connect to see whether there is a matching row. If no row matches, you will not be able to connect to the server.
Because the user table supports wildcards in the host column, a user/hostname combination may match more than one row. MySQL determines which row is relevant by matching the most specific hostname first. For example, if there are rows in the table for test from host localhost and user test from host % (meaning any host), then the localhost row will be selected. Note that these two rows can have different passwords. This can cause a great deal of confusion. (We will look at an example of this in the section “Deleting Anonymous Accounts” later in this chapter.)
The second stage applies when you try to execute specific queries or commands. MySQL checks each query against the grant tables before it is executed.
If the query you are trying to execute requires a global privilege—such as doing a LOAD DATA INFILE or trying to use SHOW PROCESSLIST—the user table will be checked. For database-specific queries, the user table will be checked first. If the user has the privilege on all databases, this will be sufficient. If not, then the db and host tables are checked. If the user does not have the privilege at this level, then if any table- or column-level privileges are set, these will be checked last.
There are a few general security principles that apply to the management of user accounts in MySQL. We will look at these next.
When you install MySQL, the root password is not set by default. You absolutely must set this password before using MySQL in anything other than a purely experimental environment. Without the root password set, anyone can log in and do anything he wants to your data. In virtually all cases, this is a very bad thing. If you have not done so already, set this password immediately.
When you install MySQL on Windows, it automatically creates some accounts for you. On Linux, this happens when you run the mysql_install_db script. Two of these accounts are anonymous; they represent the account you get when you don't specify a username. One has a host value of localhost and the other % (any other host, so effectively any remote connection). These accounts have no passwords set by default.
You can probably already see where we're going with this, but we strongly recommend that you delete these accounts. You can do this as shown here:
delete from user where User='';
delete from db where User='';
You will need to follow this with a FLUSH PRIVILEGES statement to flush the grant tables.
The second reason to do this is that these accounts can cause confusion when regular users try to log in. If you create an account for, let's say username laura at any host (%), then when laura tries to connect from localhost, the MySQL server looks for matching entries in the user table. It has laura@% and (anonymous)@localhost. Because MySQL matches the most specific hostname first, the matching row is (anonymous)@localhost. Note that although laura has supplied a username, this doesn't matter! The anonymous accounts don't require a username. This anonymous account is likely to have a different password from laura's account (by default, the password is blank, meaning the user should not supply one). This means that when laura tries to log in with her username and password from localhost, she will get an Access Denied error for no obvious reason.
Again, the best way to avoid this problem is to delete these accounts and forget about them.
MySQL has a very fine-grained privilege system, as we discussed in Chapter 11. You must be very careful about to whom you grant some of these privileges. The specific ones to be most careful of are FILE, PROCESS, and WITH GRANT OPTION.
The FILE privilege allows users to LOAD DATA INFILE. This can be manipulated to load in files from the server (such as the password file /etc/passwd) or even database data files, effectively circumventing the privilege system.
The PROCESS privilege allows users to SHOW PROCESSLIST. This reveals the queries being executed at any given time, which may reveal confidential information about one user to another.
The WITH GRANT OPTION privilege allows a user to share his privileges with others. As long as you know this and understand the consequences, you can grant this privilege cautiously.
MySQL user passwords are encrypted. Before version 4.1, you could use the encrypted password as stored to log in. This has now been fixed and the password and login mechanism have been made more secure.
If you are writing an application that stores (non-MySQL) usernames and passwords, we recommend that you use something other than the PASSWORD() function to encrypt them. We recommend use of MD5() or ENCRYPT() instead. See Chapter 8, “Using MySQL Built-In Functions with SELECT,” for a further discussion of these functions.
In addition to setting up MySQL accounts securely, you must control access to the MySQL binaries, scripts, and data files. We will discuss some recommendations for this on your system.
This is a recommendation for Linux and other Unix-like operating systems. Do not be tempted to run the MySQL server (mysqld) from the root user account. Just as you would if you were running a Web server, create a special user account for running the MySQL server. This way you can restrict the access privileges that the MySQL server has to the file system.
There is no point in spending time setting up user accounts in MySQL correctly if you cannot control file access in your operating system. You need to control user access to the MySQL binaries, scripts, and, in particular, the data directory. A common source of security holes involves users who have legitimate access to the machine where your MySQL server resides but not to, say, other users' databases. If these users can access the data directory, they can copy the data files and load them into another MySQL server.
Generally speaking, you want to ensure that the following safeguards are in place:
• Only appropriate users can run mysqld. You can restrict this to the user you have created for the purpose of running mysqld.
• Only appropriate users have access to MySQL's associated programs and scripts such as, for example, mysqladmin, mysqldump, and mysqlhotcopy. You may want to determine this on a program-by-program basis.
• Only appropriate users can access the MySQL data directory. If the server is running as user mysql, this user will need access to the directory. Any other users are optional and are therefore generally best denied.
Before passing on any user-entered data to MySQL, you should do some application-level error checking. Exactly how you go about this depends on the development platform you are using, but let's look at an example of why you should perform this error checking.
The problem may start with something as simple as a user entering his name—Patrick O'Leary—into your application. If you pass this data straight into MySQL, the apostrophe in O'Leary will cause a problem. In a more sinister context, users may try to enter MySQL commands into your application interface or Web forms. The steps you will need to take to check your data depend on your programming language, but some general guidelines can be found in the MySQL manual for a large number of languages.
We have covered the privilege system, looked at user accounts, considered the underlying filesystem files, and briefly mentioned filtering data. If you are concerned about the security of your network connection (which you probably should be with any external connection), MySQL allows you to encrypt transmissions with SSL. You should also give some consideration to physical security.
If you want to avoid having crackers sniffing packets going between the MySQL server and clients, you can configure MySQL for secure connections. This means that all the data sent between the client and the server is encrypted using SSL (Secure Sockets Layer).
Setting up SSL requires that you install the OpenSSL library (available from www.openssl.org), start the server with the --with-vio and --with-ssl options, and do some setup at the command line. A good sample script of the work you need to do can be found in the MySQL manual; we have not reproduced it here.
After this is set up, you can restrict GRANT statements by requiring users to connect using SSL or to have an appropriate certificate. As a simple example, you can use the following GRANT statement:
grant all on employee.*
to testuser identified by 'password'
require ssl;
This creates (or modifies) an account for testuser, giving the user the password password. This user will be able to connect only via SSL. You can demand that all your users connect this way or perhaps all users logging in from anywhere other than localhost.
As a general guideline, if you are going to go to the trouble of carefully setting up user accounts in MySQL and the operating system and perhaps even requiring users to connect via SSL, then it is in your best interest to also secure your installation physically. If someone can take your server down by tripping over the power cable or steal your data by picking up the server and walking away with it, you have an obvious problem. Physical security is frequently forgotten, especially in small to medium-sized companies.
It may be less surprising for Windows, but even otherwise secure Unix/Linux systems are vulnerable when physical security is neglected. For example, with Linux it is trivial to alter a machine's root password when the user has physical access to the machine. Of course, with root access, all the data in your MySQL databases can be compromised.
• Stage 1: Check whether user@host is allowed to connect with this password.
• Stage 2: Check each query to see whether this user@host has sufficient privilege. Check the user and host tables first, then db, and then tables_priv and columns_priv.
• User table rows with more specific hosts are used in preference to those with less specific hosts.
• Make sure you set a root password for MySQL.
• Delete anonymous accounts; they allow access to strangers and may keep out legitimate users.
• Be very careful about granting the privileges FILE, PROCESS, and WITH GRANT OPTION.
• Encrypt application-level passwords with MD5() or CRYPT() rather than PASSWORD().
• Don't run mysqld as the Unix root user. Create a low-privilege user specifically to run mysqld.
• Limit access to mysqld to the MySQL low-privilege user.
• Limit access to programs and scripts as necessary to the user. Use the principle of least privilege: Give users access only if they really need it!
• Limit access to the data directory to the MySQL user.
• Never trust data directly from the user. Always filter it in your application-level logic.
• Turn on SSL connections if encrypted connections are required.
• Remember to watch the physical security of your MySQL server!
In Chapter 16, “Replicating Your Database,” we'll see how you can set up two database servers to serve the same data. This spreads load, increases robustness, and makes backup creation easier.