|< Day Day Up >|
10.5 Network Security
We'd love to say simply, "Don't ever put a MySQL server on the Internet." Period. End of story. But the fact is that you may need to have a MySQL server that is accessible on the Internet. To help keep your server secure, we'll look at several techniques you can use to limit its exposure.
Even if your server is used only on an internal network at your organization, there are steps you should take to keep data away from prying eyes. After all, some of the most serious security threats in a company come from the inside, not Joe Random Hacker.
Keep in mind that this information is only a starting point in the process of ensuring your MySQL servers are well protected. There are numerous good network security books available, including Building Internet Firewalls by Elizabeth D. Zwicky, Simon Cooper, D. Brent Chapman, and TCP/IP Network Administration by Craig Hunt, both from O'Reilly. If you're serious about network security, do yourself a favor and pick up a book on the topic (after you finish this one!).
As with operating-system security, having a third-party audit of your network can be quite helpful in spotting weaknesses before they are exploited.
10.5.1 Localhost-Only Connections
If your MySQL server is used in an application that resides on the same host (common with small and mid-sized web sites), there's a good chance you won't need to allow any access to MySQL over the network. By eliminating the need to accept external connections, you dramatically reduce the number of ways in which a hacker can get data from your MySQL server.
Disabling network access limits your ability to make administrative changes remotely (add users, rotate logs, etc.). So you'll need to either log in to the MySQL server using SSH or install a web-based application that allows you to make those changes. The remote login requirement can be difficult on some Windows systems, but there are other remote-access alternatives on the market. One solution to the problem might be to install phpMyAdmin (discussed in Appendix C).
The skip-networking option tells MySQL not to listen on any TCP socket. It will, however, listen for connections on a Unix socket. Starting MySQL without networking support can be accomplished using the following very simple command:
$ mysqld_safe --skip-networking
You can instead put the skip-networking option in the [mysqld] section of your my.cnf file:
No matter which option you use, the result is the same. MySQL won't accept any TCP connections.
As with any other network-based service, it is important that you allow connections only from authorized hosts. As we showed earlier, you can use MySQL's GRANT command to restrict the hosts from which users can connect, but it's a good idea to have a dual protection. By filtering connections at the network level using a firewall, you gain additional security.
Having multiple ways to filter connections means that a single mistake, such as a typo in a GRANT command, won't allow connections from unauthorized hosts. In many organizations, network security is administered by a group of people that is separate from those developing applications. This further helps reduce the possibility that a single person's change can expose a server.
The most secure approach to use when firewalling a machine is to deny all connections by default. Then you can add rules that allow access to the few services that other hosts may need access to. For a system limited to providing a MySQL server, you should allow connections only to TCP port 3306 (MySQL's default) and possibly a remote login service such as SSH (typically on TCP port 22).
10.5.2.1 No default route
Consider not having a default route configured on your firewalled MySQL servers. That way, even if the firewall configuration is compromised, and someone tries to contact your MySQL server from the outside, the packets will never get back to them. They'll never leave your local network.
Let's say your MySQL server is 192.168.0.10, and the local network has a 255.255.255.0 netmask. In this configuration, any packet from 192.168.0.0/24 is considered "local" because it can be reached directly via the attached network interface (probably eth0 or the host operating system's equivalent). Traffic from any other address would have to be directed to a gateway to reach its final destination, and since there is no default route, there is no way for those packets to find their gateway and get to their destination.
If you must allow a select few outside hosts to access your otherwise firewalled server, add static routes for those hosts. Doing so ensures that the server responds to as few outside hosts as possible.
10.5.3 MySQL in a DMZ
Simply firewalling MySQL servers often isn't secure enough for some installations. If one of your web or application servers is compromised, an attacker could use the server to attack a MySQL server directly. Once the attacker has access to a single computer on the firewalled network, she has relatively unrestricted access to all the other servers on that network.
By moving the MySQL servers to their own separate network segment that isn't accessible from the outside, you can greatly improve security. For instance, imagine a LAN containing the web or other application servers and a firewall. Behind the firewall, on a different physical network segment and a different logical subnet, is one or more MySQL servers. The application servers have restricted access to the MySQL servers: all of their traffic must first pass through the firewall, which can be configured in a very restrictive way.
Taking things a step further, you can argue that the application servers should be either in the DMZ or in their own separate DMZ. Is that going too far? Maybe. As is always the case in security matters, you may need to trade security for convenience and should be aware of the risks you're taking in doing so.
10.5.4 Connection Encryption and Tunneling
Any time you need to communicate with a MySQL server across a network that is public (such as the Internet) or otherwise open to traffic sniffing (many wireless networks), consider using some form of encryption. By doing so, you can make it far more difficult for anyone who might try to intercept the connection and either sniff or spoof the data.
As an added benefit, many encryption algorithms result in a compressed data stream. So not only is your data more secure, but you're also better using the available network bandwidth.
While this discussion is focused on a client accessing a MySQL server, the client could be another MySQL server. This is common when using MySQL's built-in replication. Each slave server connects to the master using the exact same protocol that normal MySQL clients use.
10.5.4.1 Virtual private networks
A company with two or more offices in distant locations may set up a virtual private network (VPN) between them using a variety of technologies. A common solution is for the external routers at each office to encrypt all traffic destined for another office. In such a situation, there's little to worry about. All the traffic is already being encrypted as it is sent out over whichever public or private network happens to connect the offices.
Does the existence of the VPN mean that there is no benefit to applying a MySQL-specific solution? Not necessarily. In the event that the VPN must be disabled for some reason, it would be nice if MySQL's network traffic remained secret. Also, there may be a benefit to restricting access to the data to prevent it from being viewed by the prying eyes of the network administrator, who can easily watch it flow across the network, if he so desired.
10.5.4.2 SSL in MySQL
As of Version 4.1, MySQL has native support for Secure Sockets Layer (SSL)—the same technology that keeps your credit card number safe when you're buying books on Amazon or airline tickets on your favorite travel site. Specifically, MySQL uses the freely available OpenSSL library.
Unfortunately, the binary versions of MySQL that ship with most Linux distributions (and those available for download from the MySQL.com web site) don't have SSL enabled by default. To check your server, simply inspect the value of the have_openssl variable:
mysql> SHOW VARIABLES LIKE 'have_openssl'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_openssl | NO | +---------------+-------+ 1 row in set (0.00 sec)
If it says NO, you'll need to compile your own MySQL server.
If it says YES, whole new levels of security in database access are opened to the administrator, depending on the security needs of your particular application.
At its most basic, you may wish to allow only encrypted sessions, relying on the SSL protocol to protect the user's password. You can require a user to connect via SSL using optional arguments to the GRANT command:
mysql> GRANT ALL PRIVILEGES ON ssl_only_db.* to 'raymond'@'%' -> IDENTIFIED BY "FooBar!" REQUIRE SSL;
That GRANT, however, doesn't place any restrictions on the SSL certificate being used by the connecting client. As long as the client and the MySQL server can negotiate an SSL session, the validity of the client certificate won't be checked.
Minimal checking of the client certificate can be performed by using the REQUIRE x509 option:
mysql> GRANT ALL PRIVILEGES ON ssl_only_db.* to raymond@% -> IDENTIFIED BY "FooBar!" REQUIRE x509;
This requires that the client certificate be at least verifiable against the CA certificates the MySQL server has been set up to recognize.
One step up might be to permit only a specific client certificate to access the database. You can do that using the REQUIRE SUBJECT syntax:
mysql> GRANT ALL PRIVILEGES ON ssl_only_db.* to 'raymond'@'%' -> IDENTIFIED BY "FooBar!" -> REQUIRE SUBJECT "/C=US/ST=New York/L=Albany/O=Widgets Inc./CN=client-ray. example.com/emailAddressfirstname.lastname@example.org";
Maybe you don't care specifically what client license is used, but only that it be one issued using your organization's CA certificate. In this case, you might use the REQUIRE ISSUER syntax to do something like the following:
mysql> GRANT ALL PRIVILEGES ON ssl_only_db.* to 'raymond'@'%' -> IDENTIFIED BY "FooBar!" -> REQUIRE ISSUER "/C=US/ST=New+20York/L=Albany/O=Widgets Inc./CN=cacert.example. com/emailAddressemail@example.com";
For the ultimate in authentication, you can require both the issuer and subject to be predefined values, requiring Raymond to use the specific certificate issued using your organization's CA certificate, for example, by combining the two syntaxes:
mysql> GRANT ALL PRIVILEGES ON ssl_only_db.* to 'raymond'@'%' -> IDENTIFIED BY "FooBar!" -> REQUIRE SUBJECT "/C=US/ST=New York/L=Albany/O=Widgets Inc./CN=client-ray. example.com/emailAddressfirstname.lastname@example.org" -> AND ISSUER "/C=US/ST=New+20York/L=Albany/O=Widgets Inc./CN=cacert.example.com/ emailAddressemail@example.com";
One other minor SSL-related option is the CIPHER requirement option, which allows the administrator to permit only "trusted" (strong) encryption ciphers to be used. SSL is cipher-independent, and the potentially strong SSL encryption can be invalidated if a really weak cipher is used to protect the data being transferred. You can restrict the choice of protocols to a set you consider to be secure by issuing a command like the following:
mysql> GRANT ALL PRIVILEGES ON ssl_only_db.* to 'raymond'@'%' -> IDENTIFIED BY "FooBar!" -> REQUIRE CIPHER "EDH-RSA-DES-CBC3-SHA";
It should be noted that managing individual client certificates may seem like excellent security, but it can be an administrative nightmare. When you create a client certificate, you have to assign it an expiration date, preferably something not too long in duration. You want it to be long enough in life so that you're not constantly having to regenerate a new certificate, but short enough in life that if the certificate holder leaves the company, or the certificate falls into the hands of a hostile entity, it doesn't give them access to your data forever.
In a small environment of a couple of employees, it may be very easy to keep track of individual certificate ownership. When your organization scales upward to hundreds or thousands of employees with certificates, keeping track of which certificates expire when and making sure that client certificates don't expire before they've been replaced can become quite cumbersome.
For some organizations this problem is solved using a combination of REQUIRE ISSUER and a series of monthly client certificates that are distributed via a trusted distribution path, such as a company intranet. Clients can download and connect to the MySQL server using certificates that are good for a month or two. This way, if an employee loses access to the company intranet, or a partner is no longer given access to the monthly key, then even if the administrator isn't told to remove their access, their ability to connect naturally expires in a predetermined schedule.
10.5.4.3 SSH tunneling
If you're using an older version of MySQL or simply don't want to hassle with setting up SSL support, consider using SSH instead. If you use Linux or Unix, there's a good chance you're already using SSH to log in to remote machines. What a lot of people don't know is that SSH can be used to establish an encrypted tunnel between two hosts.
SSH tunneling is best illustrated with an example. Let's suppose that we want an encrypted connection from a Linux workstation to the MySQL server running on db.example.com. On the workstation, you execute the following command:
$ ssh -N -f -L 4406:db.example.com:3306
This establishes a tunnel between TCP port 4406 on the workstation and port 3306 on db.example.com. You could connect to MySQL through the tunnel from the workstation by doing this:
$ mysql -h 127.0.0.1 -P 4406
SSH is a very powerful tool that can do far more than this simple example illustrates. We suggest reading O'Reilly's SSH, The Secure Shell: The Definitive Guide by Daniel J. Barrett and Richard E. Silverman if you'd like to learn more about SSH.
10.5.5 TCP Wrappers
MySQL can be compiled with support for TCP wrappers on Unix systems. If a full-blown firewall isn't an option, TCP wrappers provide a basic level of defense. You'll gain additional control over which hosts MySQL will or will not talk to without having to change your grant tables.
To use TCP wrappers, you need to build MySQL from source and pass the --with-libwrap option to configure so that it will know where to find the proper header files on your operating system:
$ ./configure --with-libwrap=/usr/local/tcp_wrappers
Assuming you have an entry in your /etc/hosts.deny file that denies all connections by default:
# deny all connections ALL: ALL
you can explicitly add MySQL to your /etc/hosts.allow file:
# allow mysql connections from hosts on the local network mysqld: 192.168.1.0/255.255.0.0 : allow
The only other catch is that you need an appropriate entry in /etc/services for MySQL. If you don't already have one, add a line such as the following:
mysql 3306/tcp # MySQL Server
Of course, if you are running MySQL on a nonstandard port, use that number instead of 3306.
10.5.6 Automatic Host Blocking
MySQL provides some help in preventing network-based attacks. If MySQL notices too many bad connections (those that don't result in a valid MySQL session) from a particular host, it starts blocking connections from that host. The server variable max_connection_errors determines how many bad connections MySQL will allow before it begins blocking.
When a host is blocked, MySQL records in the error log a message that looks like this:
Host 'host.badguy.com' blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts'
As that message indicates, you can use the mysqladmin flush-hosts command to unblock the host, presumably after you have figured out why that host was having problems connecting and have addressed whatever issue is relevant. The mysqladmin flush_hosts command simply executes a FLUSH HOSTS SQL command, which empties MySQL's host cache tables. The result is that all blocked hosts are unblocked; there's no way to unblock a single host.
If you find that this becomes a common problem for some reason, you can set the max_connection_errors variable to a relatively high number to avoid the problem.
$ mysqld_safe -O max_connection_errors=999999999
|< Day Day Up >|