High Performance MySQL [Electronic resources] نسخه متنی

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:

[mysqld]
skip-networking

No matter which option you use, the result is the same. MySQL
won't accept any TCP connections.^[6]

^[6] You can end up with an interesting configuration if you have a
MySQL slave server configured with
skip-networking. Because it initiates its
connection to the master, the slave still gets all its data updates,
but because no remote connections are permitted, you can have a more
secured "backup replica" that
can't be remotely tainted. It should be noted,
though, that obviously you can't use such a replica
in a failover configuration: no other client could connect to
it.

10.5.2 Firewalling

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.^[7]

^[7] For our purposes, a firewall is
simply a device that network traffic passes through for the purposes
of filtering and possibly routing. Whether it's a
"real" firewall, a router, or an
old 486 PC doesn't matter.

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.^[8]

^[8] That's not entirely true.
Many modern network switches allow you to configure multiple Virtual
LANs (VLANs) on a single physical network. Machines that
aren't on the same VLAN may not be able to talk to
each other.

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.^[9] To check your server, simply inspect
the value of the have_openssl variable:

^[9] SSL can be compiled into the Windows version
of MySQL after you download OpenSSL for Windows. If you
aren't in a situation in which you can recompile
MySQL using the OpenSSL libraries, another solution might be to use
STunnel, located at http://www.stunnel.org. It
won't be nearly as fully featured as actually using
the OpenSSL hooks directly, but at least you can encrypt your client
connections.

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/emailAddress=raymond@example.com";

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/emailAddress=admin@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/emailAddress=raymond@example.com"
-> AND ISSUER "/C=US/ST=New+20York/L=Albany/O=Widgets Inc./CN=cacert.example.com/
emailAddress=admin@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.^[10] What a lot of people don't know is that
SSH can be used to establish an encrypted tunnel between two hosts.

^[10] A variant of OpenSSH is also available for
Windows clients. There is a full tutorial on how to set up SSH
tunnels to connect to MySQL machines at http://www.vbmysql.com/articles/security/sshtunnell.

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:^[11]

^[11] Assuming SSH Version 2 is
installed. SSH Version 1 has no -N option. See
your SSH documentation for details.

$ 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

It's currently not possible to set
max_connection_errors to zero and disable the
check entirely. The only way to do that is to remove the check from
the source code.