Skip to content

Securing MySQL

Presented by Chris Verges on April 23, 2002

Table of Contents


1. What is MySQL?

The MySQL database server is the world’s most popular open source
database. Its architecture makes it extremely fast and easy to
customize. Extensive reuse of code within the software and a
minimalistic approach to producing functionally-rich features has
resulted in a database management system unmatched in speed,
compactness, stability and ease of deployment. The unique
separation of the core server from the storage engine makes it
possible to run with strict transaction control or with ultra-fast
transactionless disk access, whichever is most appropriate for the
situation.

The MySQL database server is available for free under the GNU
General Public License (GPL). Commercial licenses are available
for users who prefer not to be restricted by the terms of the GPL.
(Taken from http://www.mysql.com/products/mysql/)

There are four different versions of MySQL available. The one
most commonly referred to is "MySQL Standard," which includes the
MySQL storage engines and InnoDB storage engines. For 99% of the
applications out there, this is good enough ™. However, if
the licensee is a for-profit corporation (e.g., not non-profit),
MySQL comes in the Pro form, which basically is the same thing
with a different LICENSE.TXT file. :)


2. How do I install MySQL?

MySQL runs on most *nix platforms and quite a few Microsoft-based
ones as well. The source/binaries can be downloaded from
http://www.mysql.com/downloads/mysql.html.

For Gentoo (portage-based systems):

emerge mysql

For Redhat (RPM systems):

(download Server, Client programs, Libraries and Header files,
and Client shared libraries)

rpm -i MySQL-*.rpm

From source code (for MySQL Version 4):

groupadd mysql
useradd -g mysql mysql
tar xvfz mysql-VERSION.tar.gz
cd mysql-VERSION

CFLAGS="-O3 -mpentiumpro" CXX=gcc CXXFLAGS="-O3 -mpentiumpro \
-felide-constructors -fno-exceptions -fno-rtti" ./configure \
--prefix=/usr/local/mysql --enable-assembler \
--with-mysqld-ldflags=-all-static

make
make install
scripts/mysql_install_db
chown -R root /usr/local/mysql
chown -R mysql /usr/local/mysql/var
chgrp -R mysql /usr/local/mysql
cp support-files/my-medium.cnf /etc/my.cnf
/usr/local/mysql/bin/safe_mysqld --user=mysql &


3. Configuration Files

There are three files MySQL reads by default for configuration:

/etc/mysql/my.cnf Global settings
DATADIR/my.cnf Server-specific options
~/.my.cnf User-specific options

The DATADIR directory will be the MySQL data directory, typically
/usr/local/mysql/data or /usr/local/var.

Option files can contain any of the following lines:

#comment
[group]
option
option=value
set-variable = variable=value

The standard *nix escape sequences still apply here. All leading
and trailing whitspace is automatically deleted.

A standard configuration file would be:

[client]
#password= my_password
port= 3306
socket= /var/run/mysqld/mysqld.sock

[safe_mysqld]
err-log= /var/log/mysql/mysql.err

[mysqld]
#skip-networking
skip-innodb
user= mysql
pid-file= /var/run/mysqld/mysqld.pid
socket= /var/run/mysqld/mysqld.sock
port= 3306
log= /var/log/mysql/mysql.log
basedir= /usr
datadir= /var/lib/mysql
tmpdir= /tmp
language= /usr/share/mysql/english
skip-locking
set-variable= key_buffer=16M
set-variable= max_allowed_packet=1M
set-variable= thread_stack=128K

[mysqldump]
quick
set-variable= max_allowed_packet=1M

[mysql]
#no-auto-rehash# faster start of mysql but no tab completition

[isamchk]
set-variable= key_buffer=16M


4. Securing the Configuration Files

There are many ways to secure MySQL by using the /etc/mysql/my.cnf
configuration file.

  • Changing the default port for the server and the clients.
    set ‘port = 3306′ to some other value, like ‘port = 27098′.
  • Use the ‘bind-address’ variable to bind MySQL only to the
    localhost (127.0.0.1).

    bind-address = 127.0.0.1
  • By using the ‘skip-name-resolve’ directive, clients that
    attempt to authenticate to the server must do so using the
    IP only; that is, DNS resolution will not work for
    authentication. While this isn’t a wonderful security
    procedure, it is something that could slow a potential hack.
  • The ‘safe-show-database’ directive will only display
    databases for which the authenticated user has some read/
    write privileges. Otherwise, ‘SHOW DATABASE’ will return
    every database the system includes.

So a ‘more secure’ configuration file could be:

[client]
port= 40044
socket= /var/run/mysqld/mysqld.sock

[safe_mysqld]
err-log= /var/log/mysql/mysql.err

[mysqld]
skip-innodb
user= mysql
pid-file= /var/run/mysqld/mysqld.pid
socket= /var/run/mysqld/mysqld.sock
port= 40044
log= /var/log/mysql/mysql.log
basedir= /usr
datadir= /var/lib/mysql
tmpdir= /tmp
language= /usr/share/mysql/english
skip-locking
set-variable= key_buffer=16M
set-variable= max_allowed_packet=1M
set-variable= thread_stack=128K
bind-address= 127.0.0.1
skip-name-resolve
safe-show-database

[mysqldump]
quick
set-variable= max_allowed_packet=1M

[isamchk]
set-variable= key_buffer=16M


5. What about SSL magic?

MySQL 3.x does not support SSL; MySQL 4.x, however, does. Since the
4.x line was just released, this author does not have much experience
with all the changes and wrote this tutorial for 3.x. Documentation
can be found on MySQL’s website at http://www.mysql.com/.

However, MySQL 3.x can be used in conjunction with stunnel. stunnel
creates an encrypted tunnel from the client to the server through
which all database transactions can be securely transmitted over the
‘net.

stunnel can be obtained from http://www.stunnel.org/. It runs on *nix
and Windows boxes so it makes a very useful addition to the MySQL
setup. Compiling and installing is a simple matter of reading the
Install file located in the tar file (or just typing ‘emerge
stunnel’, though this does not give you the latest version).

stunnel is currently in its fourth major version. On Gentoo it can be
installed by typing:

emerge /usr/portage/net-misc/stunnel/stunnel-4.x.ebuild

Otherwise, you can download and install stunnel from
http://www.stunnel.org/. The basic install procedures are simple enough.
You still need to create the stunnel.pem file, however (used for openssl
encryption). Since Gentoo handles all the installation, there are a few
commands beyond the ordinary you need to run:

ebuild /usr/portage/net-misc/stunnel/stunnel-4.x.ebuild \

fetch unpack compile
cd /var/tmp/portage/stunnel-4.x/work/stunnel/tools/

Now, regardless of operating system, you need to create the stunnel.pem
file:

make stunnel.pem
chown root:root stunnel.pem
chmod 400 stunnel.pem
cp stunnel.pem /etc/stunnel/stunnel.pem

This procedure will need to be repeated on both client and server.
There will be options you need to type in for stunnel.pem … simple
enough to BS, so have fun.

For the server, you will have to modify the /etc/stunnel/stunnel.conf
file as such:

cert = /etc/stunnel/stunnel.pem
pid = /var/tmp/stunnel/stunnel.pid

setuid = nobody
setgid = nobody

client = no

[3306]-- your regular mysql port
accept = 3307-- your mysql ssl port
connect = 3306-- your regular mysql port

For the client, you will have to modify the /etc/stunnel/stunnel.conf
file as such:

cert = /etc/stunnel/stunnel.pem
pid = /var/tmp/stunnel/stunnel.pid

setuid = nobody
setgid = nobody

client = yes

[3307]-- your mysql ssl port
accept = 3306-- your regular mysql port
connect = server:3307 -- your server IP and mysql ssl port

After these files have been modified, start the stunnel daemon. On
Gentoo it can be started by running ‘/etc/init.d/stunnel start’. Make
sure both the client and the server are running this daemon and that the
server has the mysql engine running.

At this point, simply typing ‘mysql -h server -u user -p’ should connect
from the client to the server over the SSL connection.

Better reference for stunnel setup can be found at
http://www.freebsddiary.org/stunnel-v3-to-v4.php.


6. Miscellaneous Notes

Since I didn’t have a Redhat box available, I couldn’t see how to do
this via RPMs. However, after installing MySQL from the RPMS, you can
still configure all the options in the my.cnf file and can still use
stunnel to route connections over SSL.

The only RPMS for stunnel that I could find were for Rawhide Linux, so I
don’t hold any responsibility for faulty installations. Smile I would
definitely suggest either writing your own RPM for the 4.x line (and
publishing it) or just compiling it in as source. Very few if any
programs actually have it as a dependency, so you should be good to go
by just downloading the source.

DO NOT UNDER ANY CIRCUMSTANCES USE THE stunnel.pem THAT MIGHT COME WITH
stunnel! It is a standard SSL key that has been distributed all over
the Internet.

Other fault points I have discovered involve using MySQL with PHP.
When PHP code has to access the MySQL database, you need to supply a
username and password. These PHP scripts are usually world readable,
and therefore so is the username and password to access the database.
Special measures outside the scope of this document may be taken when
attempting to secure MySQL with PHP/Apache. Perhaps this can be
discussed in a different presentation, one concerned with securing
web-related applications.

Posted in Articles.

No comments

Posted on

« »