This site is the archived OWASP Foundation Wiki and is no longer accepting Account Requests.
To view the new OWASP Foundation website, please visit https://owasp.org

OWASP Backend Security Project MySQL Hardening

From OWASP
Revision as of 09:52, 7 June 2008 by Esonn (talk | contribs) (Linux: dmcrypt)

Jump to: navigation, search

Overview

  • Firstly, we will deal about hardening the underlying operating system environment. This is an ultimately essential step towards application layer security, since also the best security mechnism and configuration won't be useful if the whole system is attackable one layer beyond the actual target application. Operating system hardening includes setting right filesystem permissions, the design and implementation of a virtual chroot-jail application executing environment, the use of access control lists as well as a quick introduction about modern virtualization approaches.
  • The next topic will be about cryptography, which we will use to aid and secure our database instance at filesystem level and, possibly even more important, the DMBS' communication channels. This will be achieved using either OpenSSL, OpenSSH or OpenVPN. For encrypting the raw database pages themselves, we'll also take a look about filesystem encryption.
  • Due to some quite aweful security bugs in the past, we'll discuss how the application's memory area can be protected against stack- and heap-smashing attacks for executing arbitrary code on the machine which actually executes the MySQL database server.
  • Then we'll discuss certain security-related MySQL configuration attributes. MySQL is quite straight to configure, but nevertheless there are a few options which inside the configuration files which make life easier - and more secure.
  • Finally the access control and privilege management mechanism of the MySQL DBMS itself will be explored and shown in some detail.

Description

Introduction

The enormous global increase of information which is to be stored, forces certain approaches of archiving and restoring data, while keeping track of numerous valuable and essential preconditions, e. g. data integrity.

Relational databases are still the common way of accomplishing the storage of masses of information, although its conceptional basics reach back to 1970, where E. F. Codd firstly introduced this method of data handling [Cod70].

As global networking dramatically increased the past decades, the TCP/IP protocol stack has become very popular and nowadays builds the fundamental backbone of the Internet. As conclusion to this tendency, also the way of controlling and operating relational database systems mostly relies on the mentioned protocol suites, with all advantages and disadvantages, inherently given by using them.

Accessability and reliability of information services is often constrained by providing them over the Internet, which should be seen as naturally untrusted and insecure network, since not only permitted persons are able to try to establish connections. With the aspect of Unix-like system environments in mind, I’ll figure out how to secure and harden database systems primarily on Linux, taking MySQL 5 as example, since this software is commonly used and widespread, especially over the Internet, for it is Open Source Software. Except for the description of filesystem encryption, all examples should work also on other POSIX compliant operating systems than Linux.

The language of given sourcecodes should be clear from the context they are mentioned. However, shell scripts are written using the Bourne Again Shell (/bin/bash), and most sources are plain C. When shell command examples are given, every line is prefixed with either # or $. While the hash indicates that the following statement has to be called as root user, the dollarsign commands doesn’t need administrative permissions.


Hardening the operating system environment

Common Unix-like systems offer a wide range of security related tools and methods for obtaining access restrictions. The configuration of certain software packages like databases is assuredly to be done carefully and with respect to secureness.

Nevertheless, a system-wide security model for protecting information and information services should begin (at least) at operating system level.

A perfectly configured Oracle Database Server, including DMBS account and role management etc., won’t be useful if everybody may be able to simply copy the raw data from the filesystem for obtaining the desired information quickly and easily. For more in-depth information about Unix and the Unix system environment, I’d refer to [SWF05], [Amb07] and [Bau02].


Filesystem access restrictions and ACLs

Most suitable filesytems available on POSIX environments provide mechanisms of restricing methods of access in an abrasive way, using (at least) three types of access mode codes, and three ways of describing for whom those modes apply.

The basic filesystem permissions are

  • read (→ 'r'),
  • write (→ 'w'), and
  • execute (→ 'x')

which can be individually referred to

  • the user which is the owner of the filesystem object, e.g. a file or a directory (→ 'u'),
  • the group of persons which belong to the (main) group of the owner (→ 'g' ), and
  • all others (→ 'o').

Taking the major configuration file of MySQL, which is normally found at /etc/mysql/my.cnf, the filesystem rights are given as following:

 $ ls -lh /etc/mysql/my.cnf
 -rw-r--r-- 1 root root 3.7K 2007-07-18 00:14 /etc/mysql/my.cnf

The access rights are shown in the string -rw-r--r--. Disregarding the first - character, Unix returns basically a nine-character string, which is to be read in triples, as rw-|r--|r--. The first triple describes the permissions of the owner, the second the permissions of the owner’s group and the third triple refers to all other users. Therefore, only the owner of the file (the root user, the administrator) is allowed to modify the file because of the write permission - users in the same group as well as all other system users may only read the object. The upcoming columns, both entitled as root describe the owner of the object, and group membership belonging of the object. As we see, the my.cnf file is owned by the user root and belongs to the system group root.

The configuration files should always belong to the root user, and only permit root to write on these objects, since nobody else should be able to modify its contents in any way. The right permission settings may be assured by

# chown -R root:root /etc/mysql/
# chmod 0644 /etc/mysql/my.cnf

In dependency on what other configuration files MySQL actually is referring to, the chmod command may also be applied to other items inside the /etc/mysql/ directory.

Storage data

MySQL stores the actual data (tables, etc.) in /var/lib/mysql or $MYSQL/data by default. In contrast to the configuration files, the data storage files should not be owned by the administrator, but by a completely unprivileged user, normally called mysql, which isn’t allowed to to anything else inside the Unix system as what is absolutely necessary. Besides the administrator of course, nobody should be able to read and/or modify these objects, therefore we completely revoke any rights of the others user section and just let mysql read and write.

Moreover, the mysql user should by no means be able to invoke a command shell. This assures that crackers arn't be able to login at the server system, even if this user has been hacked. Revoking command shells is done within /etc/passwd, by changing the last column of the mysql user from /bin/bash to /bin/false. The program given here will be invoked when a user has been successfully authenticated by the system.


Logfiles

MySQL commonly logs every event, relevant to the database. Absolutely no other users than root and mysql should be able to read or write the logs, preventing the leaking of information out of the logfiles. For example, certain queries like GRANT may offer sensitive information like user passwords, which are stored plaintext inside the protocol files. The logs are normally owned by the mysql user, since MySQL needs to write the events here (in contraty to the configuration files, only the administrator should be able to modify, not the MySQL system).

Access control lists

ACLs, or Access control lists offer a very granular method of defining and granting permissions. As opposed to the standard Unix filesystem permissions, POSIX ACLs are not built-in in the filesystem device driver (as done in ext2/3, reiserfs, xfs, etc.).

The usage of ACLs offers mechanisms for setting up per-user-permissions of single filesystem objects and therefore provide fine-grained definitions of access restrictions, if needed. The corresponding POSIX commands are getfacl for viewing ACLs, and setfacl for setting up an ACL. These features may be useful to add certain permissions to other users (e. g. automatic logfile analyzers). The following example quickly shows the usage of setfacl, allowing the user syslog to write on the MySQL log files:

 # setfacl -m user:syslog:-w- /var/log/mysql/*

Designing a chroot-jail

Even when accurately managing user- and group-memberships as well as read and write permissions to the relevant MySQL filesystem objects, we should assure, that, in case of a successful attack, the system environment does not get compromised in any way. Numerous attacks have been reported on this topic. When talking about attacks, we now commonly mean attacks from within the database system, when users or programs try to gain sensitive system parameters like the /etc/shadow file or logfiles via outfoxing the DMBS.

That’s why we need to create a sandbox-like environment where MySQL runs within and is restricted to. In terms of POSIX systems, this is called a change root - environment, or chroot-jail named by the corresponding command chroot. In the early Eighties when nowadays keywords like virtualization havn’t been born, Bill Joy introduced the concept of the chroot command which can be seen as forerunner of an virtual system environment.

chroot basically repositions the global root directory (/) via remapping it into a specific directory of any directory within the filesystem tree. Any commands, applications, users etc. which act within the chroot-environment actually don’t know that they are working in a sandbox and should have no chance for accessing any part of the filesystem outside the jailed area.


Manually designing a sandbox

Since the jailed environment won’t be able to access the rest of the filesystem, all relevant system objects like binaries, libraries, the directory structure, logs, etc. have to be copied into the sandbox.

The easiest way to accomplish this by hand, is to get an official static build of MySQL, which doesn’t mandatorily rely on external dymanic libraries (shared objects, respectively) and defines the right directory structure. The first step is to download and unpack the package, as shown here by example of MySQL 5.0.45:

 $ export MYSQL_CHROOT=/chroot/mysql
 # mkdir -p $MYSQL_CHROOT
 # cd $MYSQL_CHROOT
 $ wget http://$SERVER/mysql-5.0.45-linux-i686.tar.gz
 $ tar xfz mysql-5.0.45-linux-686.tar.gz
 $ MYSQL_CHROOT=$MYSQL_CHROOT/mysql-5.0.45-linux-i686
 $ cd $MYSQL_CHROOT

We have now prepared a basically functional MySQL environment. Nevertheless, we want to have at least a working shell, as well as some system-wide configuration files needed by MySQL. Therefore we need to copy /bin/bash to the sandbox. Since the Linux Bash also depends on certain libraries, it’s necessary to find out which libraries are needed, using the ldd command:

 $ ldd /bin/bash
 linux-gate.so.1 = >    (0xffffe000)
 libncurses.so.5 = > /lib/libncurses.so.5 (0xb7f8f000)
 libdl.so.2 = > /lib/i686/cmov/libdl.so.2 (0xb7f8b000)
 libc.so.6 = > /lib/i686/cmov/libc.so.6(0xb7e42000)
 /lib/ld-linux.so.2 (0xb7fd9000)


Now we’ll just need to copy the given objects in the corresponding directories of the sandbox. This can be done manually file by file, or simply with the following piece of code:

    $ for i in `ldd /bin/bash | awk '{print $3}' | egrep '^/.*'`; do
      mkdir -p " ./`dirname $i` " ;
      cp $i ./`dirname $i`;
    done
    cp /bin/bash ./bin

Since MySQL also uses some shell scripts, it will also need the following files:

    $ for i in /bin/hostname /bin/chown /bin/chmod /bin/touch
          /bin/date /bin/rm /usr/bin/tee /usr/bin/dirname
          /etc/passwd /etc/group /lib/librt.so.1 /lib/libthread.so.0; do
      mkdir -p "./`dirname $i`" ;
      cp $i ./`dirname $i`;
    done


We can now initially start the MySQL Server inside the chroot-environment by calling

 # chroot $MYSQL_CHROOT /bin/mysqld_safe

The chroot command now repositions the global root node / for the command mysqld_safe. If an attacker forces to gain access of the system behind the database server, he’s limited to MySQL’s root directroy, which is represented by the $MYSQL_CHROOT environment variable, and pointing to /chroot/mysql of the real filesystem behind the sandbox.

MySQL's built-in chroot mechanism

Modern virtualization approaches

Since chroot can be seen as an old-school pseudo-virtualisation, just keeping the MySQL server in a sandbox of an existing system, modern approaches have shown that virtualization and para-virtualization are leading the way of running multiple operating system kernels on one machine.

Therefore, there is no need of creating a sandbox, since every server-system may run in a completely isolated full featured Unix system, while all of these (virtual) servers are run on one single physical server.

The most common ways of aquiring an virtual server environment are currently the open-source project Xen as well as the comparable closed-source software VMWare ESX Server. Basically, those projects provide a so called Hypervisor, which can be seen as an additional abstraction layer, between the system’s hardware and the operating sytstem’s kernels. The hypervisor manages to devide the system resources by the running kernels, independent on which operating systems are used above the hypervisor, without producing much overhead in comparison to natively running the virtualized operating systems.

Since the installation of MySQL on a virtual server is done exactly like a normal installation, I won’t provide more information on this topic within this paper, but I’d refer to [SBZD07].

Another way of performing system restrictions are security suites like the NSA SELinux, as well as Novell AppArmor. Those applications aim to spy and re- strict the behaviour of certain programs and what they are trying to perform on the filesystem as well as via system calls.

Cryptographic appliances

Encrypting network traffic

For encrypting network traffic, there are several differnet ways. One may use

  • OpenSSL as MySQL's built-in cryptosystem,
  • OpenSSH as external tunneling application, or
  • OpenVPN tunneling.

All cryptographic implementations are available for every platform MySQL is capable of, and all three use strong encryption. Using OpenSSL deserves some MySQL internal configuration, and is based on certificates. This may be a good choice if there already is a public-key-infrastructure (PKI) available.

OpenVPN provides a link between two trusted private networks, over an untrusted (mostly non-private) network (normally the Internet). This needs an OpenVPN gateway server, which should commonly not be run on the same machine as the MySQL daemon does due to security reasons. Setting up an VPN tunnel is normally done to encrypt the whole network traffic between two parties, and deserves deeper knowledge of configuring a VPN gateway. Therefore, I won’t provide information on this variant, which can be obtained from [BLTR06].

An OpenSSH tunnel is easy to setup and maintain, as well as secure and well-known to most Unix users.


OpenSSL

For using OpenSSL encryption, the MySQL server has to be capable of understanding OpenSSL. Most standard MySQL packages of the common Linux distributions already offer OpenSSL-enabled MySQL services out of the box. If not, you may compile the sources of MySQL manually and run the configure script with the option --with-vio --with-openssl. OpenSSL activation forces the environment variable have_openssl to be set to YES. This can be checked by

     mysql > SHOW VARIABLES LIKE ’%openssl%’;
     +---------------+-------+
     | Variable_name | Value |
     +---------------+-------+
     | have_openssl  | YES   |
     +---------------+-------+

Since the OpenSSL encryption implementation of MySQL sustains upon certificates, we need to create

  • a Certificate Authority (CA) key and certificate,
  • a server encryption key, as well es a server certificate request,
  • a client encryption key, as well as a client certificate request.

The following shellscript will do this for us (OpenSSL binaries have to be installed):

#!/bin/bash
DIR=`pwd`/openssl
PRIV=$DIR/private
mkdir $DIR $PRIV $DIR/newcerts
cp /usr/lib/ssl/openssl.cnf $DIR
replace ./demoCA $DIR -- $DIR/openssl.cnf
openssl req -new -x509 -keyout $PRIV/cakey.pem 
  -out $DIR/cacert.pem -config $DIR/openssl.cnf
openssl req -new -keyout $DIR/server-key.pem 
  -out $DIR/server-req.pem -days 3600 -config $DIR/openssl.cnf
openssl rsa -in $DIR/server-key.pem -out $DIR/server-key.pem
openssl ca  -policy policy_anything
  -out $DIR/server-cert.pem -config $DIR/openssl.cnf -infiles $DIR/server-req.pem
openssl req -new -keyout $DIR/client-key.pem
  -out $DIR/client-req.pem -days 3600 -config $DIR/openssl.cnf
openssl rsa -in $DIR/client-key.pem -out $DIR/client-key.pem
openssl ca  -policy policy_anything
  -out $DIR/client-cert.pem -config $DIR/openssl.cnf -infiles $DIR/client-req.pem

Lines 1 - 6 create a useable directory structure for storing the resulting keys and certificates. Be sure to call this script from a safe location; keys are normally stored in /etc/mysql/keys or something similar.

Line 7 and 8 generate a local Certificate Authority for signing the certificates which are to be created.

Lines 9 and 10 create an encryption key for the MySQL server and a certificate request, which is to be signed afterwards. The certificate will be valid for 3600 days.

Line 11 (and line 16) is optional and would remove the passphrase from the server key. This means that it’s not necessary to give the passphrase every time the MySQL server is restartet. This behaviour may be seen as security risk, depending on where the (unencrypted) key will be stored.

Lines 12 and 13 will sign the previously generated server certificate with our local CA instance.

Lines 14 and 25 create a client key and certificate request.

The last lines sign the client certificate with our local CA instance.

We finally have to tell MySQL where our encryption keys and certificates are stored, which is done in my.cnf. We need entries for both, server and client. Note that the client configuration as well as the client and CA certificates have to be available on all clients who wish to encrypt MySQL related network traffic.

ssl-ca=$DIR/cacert.pem
ssl-cert=$DIR/client-cert.pem
ssl-key=$DIR/client-key.pem
<...>
[mysqld]
ssl-ca=$DIR/cacert.pem
ssl-cert=$DIR/server-cert.pem
ssl-key=$DIR/server-key.pem
<...>

$DIR is to be replaced by the chosen key and certificate directory.


OpenSSH

Encrypting network traffic using OpenSSH is done via tunnelling. The advantages of this method are:

  • An existing MySQL configuration has not to be altered
  • There is no administrative overhead for creating and maintaining certificates and keys
  • The tunnel itself is transparant to MySQL since SSH does everything on its

own

  • Easy setup

However, there are several points which may be seen as disadvantages:

  • The tunnelling mechanism itself has to be done on the client(s), which leads to decentralized administration
  • The calling client(s) require to have a valid system user on the box where the OpenSSH server is running
  • The server machine must run an OpenSSH server (which is the easiest way, but not unconditionally necessary), the clients must have the ssh binary installed

The basic idea is that the ssh binary on the client(s) opens a socket which is bound to a specific port (3307 in the following example). ssh encrypts all the traffic, coming through this port and sends it to the OpenSSH server which will perform the decryption transparently and redirect the unecrypted traffic to the port, the MySQL server is listening on.

The MySQL TCP connection a client tries to establish, is done to localhost instead of the MySQL server, to the port number bound my ssh.

On the client side, the following command will set up our OpenSSH tunnel:

ssh -L 3307:<MySQL server address>:3306 <username>@<OpenSSH server address>

The clients can now connect through localhost the get in touch with the MySQL server:

mysql -u <mysql_username> -p -h 127.0.0.1 -P 3307 

Note: The OpenSSH server doesn't mandatorily have to run on the same machine as the database server does. If OpenSSH runs on server A and MySQL on server B, we need to set up an packet redirection, which can be done using iptablest on machine A: echo 1 > /proc/sys/net/ipv4/ip_forward iptables -t nat -A PREROUTING -p tcp --dport 3306 -j DNAT --to-destination <address of MySQL server> iptables -t nat -A POSTROUTING -p tcp -d <address of MySQL server> --dport 3306 -j MASQUERADE

The statement in line 1 just activates IP packet forwarding in the Linux kernel. The second command activates traffic redirection from the OpenSSH server (where the iptables rulebase is active) to the MySQL database server. Finally, with the third command, we activate masquerading to ensure that responses of the MySQL server are correctly translated and redirected to the calling host (e.g. the MySQL client).



OpenVPN

Encrypting raw databases on filesystem level

As long as the MySQL server is up and running, and keeping track of incoming queries to provide stored data, the database files have to be unencrypted and readable. It’s primarily the job of the DMBS, to only allow authorized users to read and/or write data of certain tables.

Nevertheless, if a harddisk (including backups, tapes, etc.) gets stolen, the stored data is world-readable from every external system. If needed, encryption can solve this problem. Using encryption on filesystem level is quite easy in nowadays 2.6 Linux kernels.

The following section contains two different approaches for encrypting the filesystem, for the first one is quite Linux specific and the second one will run on Windows, Linux and OSX.

Linux: dmcrypt

The following steps need to have losetup and cryptsetup installed on the System, as well as a kernel which has been built with CONFIG_DM_CRYPT and CONFIG_BLK_DEV_DM support (which most of the current kernels have). Most Unices offer the use of encryption, but most of them are not platform independent.

MySQL stores its data in the $MYSQL_CHROOT/data directory, we will now encrypt. We will proceed with the following steps:

  1. We generate a file with completely randomized content, with the maximum size of the MySQL storage tables (in the following example, 100MiB). If the reserved space points out to be too few, we can simply create a bigger one and transfer the encrypted data later.
  2. We create a new loopback-device, which is capable of handling our crypted data-image as harddisk partition.
  3. We connect the loopback-device with a so called crypto-target, which encrypts everything which is written onto the target, and decrypts everything which is read from the target, as long as the crypto-target is enabled.
  4. Format the crypted data container with a filesystem of our choice (ReiserFS in this case).
  5. Mount the crypted container, as it’s ready to use.


These steps are done via the following commands:

# dd if=/dev/urandom of=$MYSQL_CHROOT/data.crypt
# losetup /dev/loop0 $MYSQL_CHROOT/data.crypt
# cryptsetup -y create mysql_data /dev/loop0
 Enter passphrase: Passphrase
 Verify passphrase: Passphrase
# mkreiserfs /dev/mapper/mysql_data
# mount /dev/mapper/mysql_data $MYSQL_CHROOT/data

Now, before starting up the MySQL database server for everyday use, we have to enforce step 2, 3 and 7. Detailed information about the theoretical backgrounds to cryptography may be found in the wonderful reference of Bruce Schneier [Sch05], as well as [Ert03] and [Wae03]. Information on practical filesystem encryption is found in [Pac05].

TrueCrypt

Truecrypt has experienced a large hype in the last years since it's very easy to use, focussed for desktop systems and has a graphical user interface. Nevertheless, my own benchmark tests have proven that TrueCrypt's performance is much slower than dmcrypt, and the data throughput stagnates at about 40% of what dmcrypt is capable in terms of performance (this has been tested on an Intel Core2 Duo, 2 x 3.2 GHz, 2GiB RAM on SATA2 harddisks using Linux 2.6.23).

Non-realtime encryption routines for database backups

Protection against stack-smashing and other memory corruption attacks

Security related configuration attributes

Inside MySQL: DBMS' access control and privilege management

General management table structures

Access management via SQL

Setting up connection limits

Conclusion

References

  • [AB05] MySQL AB. Inside mysql 5.0 - a dba’s perspective, 2005.
  • [Ale06] Michael Alexander, Huehtig, Netzwerke und Netzwerksicherheit. Telekommunikation, 2006. (ISBN 3826650484).
  • [Amb07] Eric Amberg. Linux-Server mit Debian. mitp, 2007. (ISBN 3826615875).
  • [Bau02] Michael Bauer. Building secure servers with Linux. O’Reilly, 2002. (ISBN 0596002173).
  • [BLTR06] Johannes Bauer, Albrecht Liebscher, and Klaus Thielking-Riechert. OpenVPN. Grundlagen, Konfiguration, Praxis. Dpunkt Verlag, 2006. (ISBN 3898643964).
  • [Cod70] E. F. Codd. A relational model of data for large shared data banks. Communications of the ACM 13 (6), 377-387, 1970.
  • [Eri03] Jon Erickson. Hacking - the art of exploitation. No starch press, 2003. (ISBN 1593270070).
  • [Ert03] Wolfgang Ertel. Angewandte Kryptographie. Hanser Fachbuchverlag, 2003. (ISBN 3446223045).
  • [Fos05] James Foster. Buffer overflow attacks. Syngres Media, 2005. (ISBN 1932266674).
  • [Gri05] Lenz Grimmer. Mysql backup and security, 2005.
  • [Kre04] Juergen Kreileder. Chrooting mysql on debian, 2004.
  • [MBBS07] Keith Murphy, Peter Brawley, Dan Buettner, and Baron Schwartz. Mysql magazine, 2007. Issue 1.
  • [One] Aleph One. Smashing the stack for fun and profit. Phrack magazine vol 49, File 14 of 16.
  • [Pac05] Lars Packshies. Praktische Kryptographie unter Linux. Open source press, 2005. (ISBN: 3937514066).
  • [PW07] Johannes Ploetner and Steffen Wendzel. Netzwerksicherheit. Galileo press, 2007. (ISBN 3898428286).
  • [SBZD07] Henning Sprang, Timo Benk, Jaroslaw Zdrzalek, and Ralph Dehner. Xen. Virtualisierung unter Linux. Open source press, 2007. (ISBN 3937514295).
  • [Sch05] Bruce Schneier. Angewandte Kryptographie. Algorithmen, Protokolle und Sourcecode in C. Pearson Studium, 2005. (ISBN 0471117099).
  • [SR07] M. Stipcevic and B. Medved Rogina. Quantum random number generator. Rudjer Boskovic Institute, Bijenicka, Zagreb, Croata, 2007.
  • [SWF05] Ellen Siever, Aaron Weber, and Stephen Figgins. Linux in a nutshell. O’Reilly, 2005. (ISBN 0596009305).
  • [Vas04] Vikram Vaswani. MySQL: The complete reference. Mcgraw-Hill Professional, 2004. (ISBN 0072224770).
  • [Wae03] Dietmar Waetjen. Kryptographie. Grundlagen, Algorithmen, Protokolle. Spektrum Adakemischer Verlag, 2003. (ISBN 3827414318).