For one reason or another, you might want to consider an alternative to MySQL. You can dip your toes in the commercial database waters (Oracle, DB2/UDB, Sybase) or stay in the open source pool. At the top of the open source list is PostgreSQL (http://www.postgresql.org/), which has more of the features of the big commercial relational databasesviews, triggers, referential integrity, subselects, stored procedures, and so on (although many of these features are coming to MySQL). Firebird (http://firebird.sourceforge.net/) is a spin-off of Borland's InterBase. Computer Associates has said it will release Ingres as open source (http://opensource.ca.com/projects/ingres/). SQLite (http://www.sqlite.org/) is an embeddable database that may become more well-known from its inclusion in recent releases of PHP.

Types of Security Problems

The problems a database server may encounter should sound familiar:

• Server compromise. Any software, especially code written in languages such as C or C++, has the potential for buffer overflows, format-string attacks, and other exploits that are by now all too familiar. And software written in any language has logic errors and plain old blunders.

• Data theft. Data can be extracted from the database even if everything seems to be configured well. It just takes one logical error or an overly permissive access control.

• Data corruption or loss. The person in the mirror may do as much damage inadvertently as the hooded and cloaked database vandal does by design.

• Denial of Service. MySQL is fast but does not always degrade gracefully under load. We'll see how far it bends before it breaks, and how to prevent the latter.
  

  Server Location

  Where should you place a database server? The main factors are:

• Who will access the database?

• How important is the data?

Exposing a database directly to the public might earn you a call from the Society for the Prevention of Cruelty to Databases. A public database server is normally an internal server, accessed only by other servers and clients behind the firewall. In this article, we'll look at examples of the most common database users: web servers and database administrators. We'll also show how to insert multiple layers of protection between the sensitive database server and the harsh weather of the public Internet.

The MySQL server listens for connections on a socketa Unix socket for connections on the same machine or a TCP socket for other machines. Its IANA-registered TCP port number is 3306, and I'll use this value in examples, but other port numbers can be used if needed.

How far from the Internet should the database be placed? Truly precious data (such as financial records) should be far back, on a dedicated database server within a second DMZ (internal to the DMZ that contains public-facing things such as web servers). The intervening firewall should pass traffic only between the database client (e.g., the web server) and database server on a specific TCP port. iptables should be configured on each machine so that the database client talks to that database port (3306) on the database server and the database server accepts a connection to port 3306 only from the host containing the web server.

For less precious data, the MySQL server may be on a dedicated machine in the outer DMZ, side by side with its clients. This is a common configuration for security, performance, and economic reasons. Configure iptables on the database server to accept connections on port 3306 only from the web server, and configure iptables on the web server to allow access to the database server on port 3306.

For local client access, MySQL can use a local Unix domain socket, avoiding TCP exploits. If a client accesses the host as localhost, MySQL automatically uses a Unix domain socket. By default, this socket is the special file /tmp/mysql.sock.

Secure Remote Administration

Although we worry most about the security of the connection between the database server and its major clients, we also need to pay attention to the back door: administrative use.

Database administration includes creating and modifying databases and tables, changing permissions, loading and dumping data, creating reports, and monitoring performance. The main methods for administrative access are:

• VPN to the server

• ssh to the server

• Tunneling a local port to the server

• Using the Web

VPN to the server

If you have a VPN (virtual private network) connecting your local machine and the database server, you can access the server as though you were in the DMZ. Open source VPNs include FreeS/WAN (http://www.freeswan.org), Openswan (http://www.openswan.org/), OpenVPN (http://openvpn.sourceforge.net/), and strongSwan (http://www.strongswan.org/). All are under active development except FreeS/WAN.

Cisco and many other vendors sell commercial VPN products.

ssh to the server

If you don't have a VPN, you can do what I do: ssh to the database server and run command-line clients such as mysql, mysqladmin, and mytop. The command line may give you more control (if you're used to text-filled terminal windows), but it can also be more tedious and error-prone. Still, it's a quick way to get in, fix a problem, and get out.

Tunneling a local port to the server

If you'd like to use GUI tools like MySQL Control Center, Administrator, or Query Browser on your local machine, you can tunnel your MySQL port through the intervening firewalls with ssh  or stunnel . If your server is db.hackenbush.com and your Unix account name is wally, enter:

ssh -fNg -L 3306:127.0.0.1:3306 test@db.test.com

Test it with a client on your own machine. Try this:

mysql -h 127.0.0.1 -u test -p

grant all on *.* to test@localhost identified by 'password'

ssh -fNg -L 3307:127.0.0.1:3306 test@db.test.com
 
mysql -P 3307 -h 127.0.0.1 -u test -p

Using the Web

There are many web-based MySQL administrative interfaces, but my favorite is phpMyAdmin (http://www.phpmyadmin.net). You should use HTTP over SSL (URLs start with https:) to protect your connection. Even so, the Web is a tough environment to secure. I never feel quite safe using web-based admin tools and tend to fall back on ssh or tunneling. You might compromise by using web tools during the design phase with a test database and move to other administrative tools for deployment.

Server Installation

Now that you've located your database server to protect against TCP exploits, you need to select a safe version of MySQL to guard against any code-based vulnerabilities.

Choosing a Version

Bug fixes, security fixes, performance enhancements, new features, and new bugs are part of each new server release. You always want the most recent stable version. At the time of writing, MySQL Server 4.1.13 is production, and 5.0 is the development tree. Old 3.x releases still abound, the most recent being 3.23.58. If you're running an older version of mySQL, make sure it's newer than 3.23.55 to avoid a remote MySQL root account (not Linux root) exploit. Make the move to 4.1 if you can, because there are many improvements. Here are some useful links to keep up with new problems as they're discovered:

Vulnerabilities

http://cve.mitre.org/cgi-bin/cvekey.cgi?keyword=mysql

Bugs

http://bugs.mysql.com/search.php

Change logs

http://dev.mysql.com/doc/mysql/en/News.html

 Installing and Configuring the Server and Clients

MySQL comes standard with Red Hat and Fedora, as RPM packages mysql-server and mysql (clients and libraries). If you install from RPM, it creates the startup script /etc/init.d/mysqld and the links to it from the runlevel directories (/etc/rc[0-6].d). If you want to install from source, see the latest details at http://dev.mysql.com/doc/mysql/en/Installing_source.html.

When the MySQL startup script is run by root, it should call another script called safe_mysqld (server Version 4.0 and newer) or mysqld_safe (pre-4.0), which is typically in /usr/bin. This script then starts the MySQL server as user mysql. The database server should not run as the Unix root user. In fact, mysqld won't run as root unless you force it to with --user=root.

Setting the MySQL root User Password

MySQL account names look like Unix account names, but they are not related. In particular, MySQL root is the all-powerful MySQL account but has nothing to do with Linux root. If you try to access MySQL without providing a name, it tries your Linux account name as the MySQL account name. So, if the Linux root user types:

# mysql

it's the same as anyone else typing:

% mysql -u root

The initial configuration of MySQL is wide open. If you can get in with:

% mysql -u root

then you need to create a MySQL root password. To set it to newpassword:

mysqladmin -u root password newpassword

You really shouldn't use the Linux root password as the MySQL root password.
You can even change the name of the MySQL root account, to trip up attackers who might try to crack its password:

mysql -u root ...

mysql> update user set user = 'admin'  where user = 'root';

Although Linux has many tools to improve the security of its user accountsincluding a minimum password length, account expirations, login rejection after repeated failures, and password look-ups in dictionariesMySQL does none of these for its database accounts. Also, MySQL's fast login process enables a cracker to automate fast password attacks. Passwords are stored as an MD5 hash rather than the original text, so dictionary attacks using precomputed MD5 hashes of common passwords are a threat.

If you want to ensure that your passwords are good enough, some MySQL password crackers are:

http://packetstorm.linuxexposed.com/Crackers/mysqlpassword.c

Deleting Anonymous Users and Test Databases

Out of the box, MySQL has a test database and some phantom users that leave open potential risks. Let's whack them. Now that you have a MySQL root user password, you'll be prompted for it:

% mysql -u root Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 to server version: 3.23.58 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>use mysql;
Database changed

mysql>delete from user where user = "";
Query OK, 2 rows affected (0.00 sec) mysql>drop database test; Query OK, 0 rows affected (0.01 sec)

mysql>quit
Bye

Checking Your Server

If setting up your database server feels like as much work as raising cattle, but without the glamor, you may mix business with pleasure and perform some virtual cow tipping: sneak up on your database server and try to push it over. From outside your firewall, see if nmap can prod port 3306. Have nessus poke MySQL holes, including a missing root password or insecure server version. A search for MySQL at http://cgi.nessus.org/plugins/search.html shows nine separate plug-ins.

Some tools that I have not yet tested, yet look promising, include http://www.zone-h.org/files/49/finger_mysql.c and a commercial vulnerability assessor called AppDetective (http://www.appsecinc.com/products/appdetective/mysql/).

The MySQL Configuration File

The file /etc/my.cnf contains overall directives for the MySQL server. Here are the contents of a simple one:

[mysqld]
 
[mysql.server]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock                                                                          
user=mysql
basedir=/var/lib                                                                              
 
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

datadir is the directory containing the database directories and files. socket is the file name of the Unix-domain socket for MySQL to use for local connections. user is the Unix user who runs the database, and should not be root.

Some variables may be added under the [mysqld] section to defend against Denial of Service attacks, or just to tune the server. The format is:

set-variable=variable=value

You can see the current values of all the server variables with the SQL command SHOW VARIABLES. The variables and their meanings are described at http://dev.mysql.com/doc/mysql/en/Server_system_variables.html. The MySQL server can avoid some

Database Operation

Now that you've installed a reasonably secure version of the server in a reasonably secure location, let's look at how to run the thing securely.

MySQL Table Types

Many new developers of MySQL-backed web sites have been horrified to watch their database fall over and sink into the swamp just as their site becomes popular. Although MySQL has a reputation for speed, this is primarily in cases where database reads greatly outnumber writes. Once the number of simultaneous writes crosses some threshold, performance degrades most ungracefully.

This is a self-inflicted Denial of Service by the implementation of the default MySQL table type: MyISAM. It locks the whole table with each write (INSERT, UPDATE, or DELETE), pushing back all other requests. It's like closing all check-in lines but one at a busy airport terminal. Waits lengthen until the administrator must kill database threads or restart the database server.

MySQL actually has multiple table types, each implementing a different storage mechanism and behavior. You'll usually deal with two: MyISAM and InnoDB. MyISAM is great for reads and counts (such as COUNT * FROM TABLE), bad for heavy writes, and lacking true transactionsthe ability to perform multiple SQL statements as a unit and roll back to the original state if there are problems.

InnoDB is more recent, with full transaction support (ACID compliance, for the database folks), foreign-key constraints, and finer-grained locking. It's preferred when there are many writes or a need for transactions. People who are used to MyISAM should be aware that COUNT(*) is much slower in InnoDB tables. InnoDB is more complex and has many specialized options.

If you're just starting with MySQL, try MyISAM first and move up to InnoDB later if you need the write performance or transaction support. Luckily, you can do this with a single SQL command:

alter table table_name type=innodb

Many public MySQL-based sites such as slashdot.org have migrated from MyISAM to InnoDB.

Loading Datafiles

If you have FILE privileges, you can bulk load data from a flat file to a MySQL table. This has obvious security implications.

The SQL LOAD DATA command reads a flat file on the database machine into a MySQL table. This could be used to load /etc/passwd into a table, then read it with a SQL SELECT statement. Since end users should not be stuffing files into tables, it's best to restrict this to administrative accounts. For example, if you need to load a flat file into a particular table every day, create a MySQL account for that purpose and grant it load privileges:

GRANT FILE ON database.table TO user @host identified by "password"

The SQL LOAD DATA LOCAL command allows the database server to read files from the client. This permits an evil server to grab any file from the database client, or an evil client to upload a file of its choice.

Recent versions of MySQL (3.23.49+ and 4.0.2+) are compiled to include an explicit --enable-local-infile option for backward compatibility. To disable this ability completely, they can be compiled without this option. Local loads can also be disabled at runtime by starting mysqld with the --local-infile=0 option.

Writing Data to Files

The SQL command SELECT ... INTO OUTFILE dumps the results of the select operation into an external file. This is another good reason not to run the server as Unix root. The FILE grant permission is needed to write files. There doesn't seem to be a way to grant read-only or write-only permissions.

Viewing Database Threads

Any user with PROCESS privilege can view the cleartext of any currently executing database server threads (with SQL SHOW PROCESSLIST or clients such as mysqladmin processlist or mytop). This includes threads containing password changes, so the privilege should be confined to those who would normally be permitted to view such things.

Killing Database Threads

A user can always kill his own threads, but with SUPER privilege, he can kill any thread. Confine this privilege to administrators.

Stopping the Server

Anyone with SHUTDOWN privilege may stop the MySQL server by running mysqladmin shutdown. The mysql user may also stop the server at the operating system level with commands such as service mysqld stop.

Backups

A database administrator should periodically dump tables to files in case data becomes lost or corrupted and needs to be recovered. The mysqldump client writes all the SQL commands needed to re-create the tables and insert all the data rows. The backup file permissions should only allow reading and writing by the mysql user and group.

Logging

MySQL writes logs to record errors, queries, slow queries, and updates. These are normally written to the same data directory that contains the MySQL database. Besides protecting these files from snooping, they should be rotated before they fill up the disk. Red Hat includes a mysql-log-rotate script as part of its logrotate package.

Replication

To enhance speed and reliability, MySQL can be configured to replicate data in many ways.

Queries

Database servers have some of the same problems as web servers. Each has an embedded language that can be abused or exploited.

If the database is suddenly running very slowly, the cause may be benign (a slow query) or some attack. A good tool to view and kill runaway queries is the Perl application mytop (http://jeremy.zawodny.com/mysql/mytop/).

If the cause is a valid but slow query, database books describe the art and science of query optimization, including building proper indexes, using EXPLAIN to see how a query would be handled, denormalizing, and so on. Some optimizations might include using the appropriate MySQL table type. For example, Innodb tables handle high write/read ratios better than MyISAM tables.

Resources

 

http://www.mysql.com

Home of MySQL.

http://dev.mysql.com/doc/mysql/en/Security.html

MySQL general security issues.

http://jeremy.zawodny.com/mysql/mytop/

mytop is top for MySQL, an indispensable display of database traffic. Helps you to see and kill runaway queries.

Article Written by: R. Koester

Be first to comment this article

Write Comment
Please keep the topic of messages relevant to the subject of the article. Personal verbal attacks will be deleted. Please don't use comments to plug your web site. Such material will be removed. Just ensure to *Refresh* your browser for a new security code to be displayed prior to clicking on the 'Send' button. Keep in mind that the above process only applies if you simply entered the wrong security code.
Name:
Comment:
Code:*