Securing MySQL step-by-step 2003

1. Introduction

MySQL is one of the most popular databases on the Internet and it is often used in conjunction with PHP. Besides its undoubted advantages such as easy of use and relatively high performance, MySQL offers simple but very effective security mechanisms. Unfortunately, the default installation of MySQL, and in particular the empty root password and the potential vulnerability to buffer overflow attacks, makes the database an easy target for attacks.

This article describes the basic steps which should be performed in order to secure a MySQL database against both local and remote attacks. This is the third and last of the series of articles devoted to securing Apache, PHP and MySQL.

1.1 Functionality

The article assumes that the Apache web server with the PHP module is installed in accordance with the previous articles, and is placed in the /chroot/httpd directory.

Apart from the above we assume the following:

• The MySQL database will be used only by PHP applications, installed on the same host;
• The default administrative tools, such as mysqladmin, mysql, mysqldump etc. will be used to manage the database;
• Remote data backup will be performed by utilizing the SSH protocol.

1.2 Security requirements

In order to achieve the highest possible level of security, the installation and configuration of MySQL should be performed in accordance with the following security requirements:

• MySQL database must be executed in a chrooted environment;
• MySQL processes must run under a unique UID/GID that is not used by any other system process;
• Only local access to MySQL will be allowed;
• MySQL root's account must be protected by a hard to guess password;
• The administrator's account will be renamed;
• Anonymous access to the database (by using the nobody account) must be disabled;
• All sample databases and tables must be removed.

2. Installing MySQL

Before we start securing MySQL, we must install the software on the server. As in the previous articles, we will start installation by creating a unique, regular group and user account on the operating system, which will be dedicated to the MySQL database:

pw groupadd mysql
pw useradd mysql -c "MySQL Server" -d /dev/null -g mysql -s /sbin/nologin

2.1 Compiling MySQL

We will compile and install MySQL software in the /usr/local/mysql directory:

./configure --prefix=/usr/local/mysql --with-mysqld-user=mysql --with-unix-socket-path=/tmp/mysql.sock --with-mysqld-ldflags=-all-static
make
su
make install
strip /usr/local/mysql/libexec/mysqld
scripts/mysql_install_db
chown -R root /usr/local/mysql
chown -R mysql /usr/local/mysql/var
chgrp -R mysql /usr/local/mysql

In general, the process of installing the server is almost identical to the one described in the MySQL manual. The only change is the use of a few additional parameters, specified in the ./configure line. The most important difference is the use of --with-mysqld-ldflags=-all-static parameter, which causes the MySQL server to be linked statically. This will significantly simplify the process of chrooting the server, as described in Section 3. With regard to the other parameters, they instruct the make program to install the software in the /usr/local/mysql directory, run the MySQL daemon with the privileges of the mysql account, and create the mysql.sock socket in the /tmp directory.

2.2 Copy configuration file

After executing the above commands, we must copy the default configuration file in accordance with the expected size of the database (small, medium, large, huge). For example:

cp support-files/my-medium.cnf /etc/my.cnf
chown root:sys /etc/my.cnf
chmod 644 /etc/my.cnf

2.3 Start the server

At this point MySQL is fully installed and ready to run. We can start the MySQL server by executing the following command:

/usr/local/mysql/bin/mysqld_safe &

2.4 Test the connection

Try to establish a connection with the database as follows:

/usr/local/mysql/bin/mysql -u root mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2 to server version: 4.0.13-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;

+----------+

| Database |

+----------+

| mysql |

| test |

+----------+

2 rows in set (0.00 sec)

mysql> quit;

Once the connection is successfully established, we can shutdown the database:

/usr/local/mysql/bin/mysqladmin -u root shutdown

and start securing the software. Otherwise, we should analyze the information stored in the /usr/local/mysql/var/`hostname`.err log file, and eliminate the cause of any problems.

3. Chrooting the server

The first step of securing MySQL is to prepare the chrooted environment, in which the MySQL server will run. The chrooting technique was described in detail in the first article of this series ("Securing Apache: Step-by-Step"), so if you are not familiar with the technique or why chrooting is recommended, please refer to that article.

3.1 Operating system

Like in the previous articles, the target operating system is FreeBSD 4.7. However, the methods presented should also apply on most modern UNIX and UNIX-like systems.

3.2 Prepare chroot environment

In order to prepare the chrooted environment, we must create the following directory structure:

mkdir -p /chroot/mysql/dev
mkdir -p /chroot/mysql/etc
mkdir -p /chroot/mysql/tmp
mkdir -p /chroot/mysql/var/tmp
mkdir -p /chroot/mysql/usr/local/mysql/libexec
mkdir -p /chroot/mysql/usr/local/mysql/share/mysql/english

3.3 Set access rights

The access rights to the above directories should be set as follows:

chown -R root:sys /chroot/mysql
chmod -R 755 /chroot/mysql
chmod 1777 /chroot/mysql/tmp

3.4 Create directory structure

Next, the following files have to be copied into the new directory structure:

cp /usr/local/mysql/libexec/mysqld /chroot/mysql/usr/local/mysql/libexec/
cp /usr/local/mysql/share/mysql/english/errmsg.sys /chroot/mysql/usr/local/mysql/share/mysql/english/
cp /etc/hosts /chroot/mysql/etc/
cp /etc/host.conf /chroot/mysql/etc/
cp /etc/resolv.conf /chroot/mysql/etc/
cp /etc/group /chroot/mysql/etc/
cp /etc/master.passwd /chroot/mysql/etc/passwords
cp /etc/my.cnf /chroot/mysql/etc/

3.5 Tighten passwords and groups

From the files: /chroot/mysql/etc/passwords and /chroot/mysql/etc/group we must remove all the lines except the mysql account and group. Next, we have to build the password database as follows (this applies only to FreeBSD):

cd /chroot/mysql/etc
pwd_mkdb -d /chroot/mysql/etc passwords
rm -rf /chroot/mysql/etc/master.passwd

3.6 Special considerations

As in case of the Apache web server, we have to create a special device file /dev/null:

ls -al /dev/null

crw-rw-rw- 1 root sys 2, 2 Jun 21 18:31 /dev/null

mknod /chroot/mysql/dev/null c 2 2

chown root:sys /chroot/mysql/dev/null

chmod 666 /chroot/mysql/dev/null

We must also copy the mysql database, which contains grant tables created during MySQL installation:

cp -R /usr/local/mysql/var/ /chroot/mysql/usr/local/mysql/var
chown -R mysql:mysql /chroot/mysql/usr/local/mysql/var

3.7 Localization

If any language other than English will be used, we should copy the proper charsets from the /usr/local/mysql/share/mysql/charsets directory as well.

3.8 Test the configuration

At this point MySQL is ready to run in the chrooted environment. We can test if it runs correctly by executing the following command:

chrootuid /chroot/mysql mysql /usr/local/mysql/libexec/mysqld &

If any error occurs, we should use the truss command or an alternative, such as ktrace/kdump, strace, etc. This will help us to determine and eliminate the cause of the problems.

Notice, that in order to run the mysqld process, the chrootuid program was used instead of chroot, as in case of Apache or PHP. The main difference is that chrootuid changes the owner of the executing process. In our example, mysqld is being executed in a chrooted environment, but the owner of the process is not root, but mysql user. The chrootuid is not installed by default in many operating systems and it may be necessary to download and install this program manually. The chrootuid software can be downloaded here.

4. Configuring the server

The next step is to configure the database server in compliance with our security requirements.

In case of default installation of MySQL, the main configuration file is /etc/my.cnf. In our case, however, because of running the server in a chrooted environment, we will use two configuration files: /chroot/mysql/etc/my.cnf and /etc/my.cnf. The first one will be used by MySQL server, and the latter will be used by MySQL tools (e.g. mysqladmin, mysql, mysqldump etc.). In both cases, some configuration changes will be required.

4.1 Disable remote access

The first change applies to the 3306/tcp port, on which MySQL listens by default. Because, according to the initial assumptions, the database will be used only by locally installed PHP applications, we can freely disable listening on that port. This will limit possibilities of attacking the MySQL database by direct TCP/IP connections from other hosts. Local communication will be still possible throw the mysql.sock socket. In order to disable listening on the mentioned port, the following parameter should be added to the [mysqld] section of /chroot/mysql/etc/my.cnf:

skip-networking

If, for some reason, remote access to the database is still required (e.g. to perform remote data backup), the SSH protocol can be used as follows:

backuphost$ ssh mysqlserver /usr/local/mysql/bin/mysqldump -A > backup

4.2 Improve local security

The next change is to disable the use of LOAD DATA LOCAL INFILE command, which will help to prevent against unauthorized reading from local files. This matters especially when new SQL Injection vulnerabilities in PHP applications are found.

For that purpose, the following parameter should be added in the [mysqld] section in /chroot/mysql/etc/my.cnf:

set-variable=local-infile=0

In addition, to make the use of the database administrative tools convenient, the following parameter should be changed in the [client] section of /etc/my.cnf:

socket = /chroot/mysql/tmp/mysql.sock

Thanks to that, there will be no need to supply the mysql, mysqladmin, mysqldump etc. commands with the --socket=/chroot/mysql/tmp/mysql.sock parameter every time we run these tools.

4.3 Change admin password

One of the most important steps in securing MySQL is changing the database administrator's password, which is empty by default. In order to perform that, we should run MySQL (if it is not already running):

chrootuid /chroot/mysql mysql /usr/local/mysql/libexec/mysqld &

and change the administrator's password as follows:

/usr/local/mysql/bin/mysql -u root
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');

It is good practice not to change passwords from the command line, for example, by using the "mysqladmin password" command. This is especially important when other users work on the server. In that case the password could be easily revealed, e.g. by using the "ps aux" command or reviewing history files (~/.history, ~/.bash_history etc), when improper access rights are set to them.

4.4 Remove default users/db

Next, we must remove the sample database (test) and all accounts except the local root account:

mysql> drop database test;

mysql> use mysql;

mysql> delete from db;

mysql> delete from user where not (host="localhost" and user="root");

mysql> flush privileges;

This will prevent the database from establishing anonymous connections and -- irrespective of the skip-networking parameter in /chroot/mysql/etc/my.cnf -- remote connections as well.

4.5 Change admin name

It is also recommended to change the default name of administrator's account (root), to a different, harder to guess one. Such a change will make it difficult to perform brute-force and dictionary attacks on the administrator's password. In this case the intruder will have to guess not only the password, but first and foremost, the name of the administrator's account.

mysql> update user set user="mydbadmin" where user="root";
mysql> flush privileges;

4.6 Remove history

Finally, we should also remove the content of the MySQL history file (~/.mysql_history), in which all executed SQL commands are being stored (especially passwords, which are stored as plain text):

cat /dev/null > ~/.mysql_history

5. Communication between PHP and MySQL

In the previous article ("Securing PHP: Step-by-Step"), I mentioned about the communication problem between PHP and MySQL when one of these programs is being executed in a chrooted environment. Because locally PHP communicates with MySQL by using the /tmp/mysql.sock socket, placing PHP in the chrooted environment means that they cannot communicate with each other. To solve that problem, each time we run MySQL we must create hard link to the PHP chrooted environment:

ln /chroot/mysql/tmp/mysql.sock /chroot/httpd/tmp/

Note that the /chroot/mysql/tmp/mysql.sock socket and the /chroot/httpd/tmp directory must be physically placed on the same filesystem. Otherwise the programs will not be able to communicate with each other -- hard links do not work between filesystems.

6. Final steps

At this point we can create all databases and accounts which will be used by specific PHP applications. It should be emphasized that these accounts should have access rights only to the databases which are used by the PHP applications. In particular, they should not have any access rights to the mysql database, nor any system or administrative privileges (FILE, GRANT, ALTER, SHOW DATABASE, RELOAD, SHUTDOWN, PROCESS, SUPER etc.).

At last, we should also create a shell script that will be used to run MySQL during operating system start up. Sample script is shown below and also available for download:

#!/bin/sh

CHROOT_MYSQL=/chroot/mysql

CHROOT_PHP=/chroot/httpd

SOCKET=/tmp/mysql.sock

MYSQLD=/usr/local/mysql/libexec/mysqld

PIDFILE=/usr/local/mysql/var/`hostname`.pid

CHROOTUID=/usr/local/sbin/chrootuid

echo -n " mysql"

case "$1" in

start)

rm -rf ${CHROOT_PHP}/${SOCKET}

nohup ${CHROOTUID} ${CHROOT_MYSQL} mysql ${MYSQLD} >/dev/null 2>&1 &

sleep 5 && ln ${CHROOT_MYSQL}/${SOCKET} ${CHROOT_PHP}/${SOCKET}

;;

stop)

kill `cat ${CHROOT_MYSQL}/${PIDFILE}`

rm -rf ${CHROOT_MYSQL}/${SOCKET}

;;

*)

echo ""

echo "Usage: `basename $0` {start|stop}" >&2

exit 64

;;

esac

exit 0

In case of our FreeBSD system, the above script should be placed in the /usr/local/etc/rc.d directory, under the name of mysql.sh.

6.1 Summary

Applying the methods described in the article allows us to significantly increase the security of MySQL. By running the database in a chrooted environment, disabling listening on 3306/tcp port and applying strong passwords to users' accounts we can make the database immune to a many of the attacks that would be possible with the default installation.

Although no method will let us achieve 100% security, applying the outlined methods will at least limit attack possibilities from users who visit our web servers with unfair intentions.

Secure MySQL Database Design

When it comes to installing software, secure design is often the last consideration. The first goal is usually just to get it to work. This is particularly true of databases. Databases are commonly referred to the keys to the kingdom: meaning that once they are compromised, all the valuable data that is stored there could fall into the hands of the attacker. With this in mind, this article will discuss various methods to secure databases, specifically one of the most popular freeware databases in use today, MySQL.
Introduction to MySQL
MySQL can be found at http://www.mysql.com or at http://sourceforge.net/projects/mysql/. MySQL is used in over 4 million installations all over the world. It is licensed under both GNU GPL (General Public License) and commercial licenses, depending upon what level of support that you require. It has a large user community, which makes it somewhat easier to use under the GPL license. There are actually four versions of MySQL:
MySQL Standard includes the standard storage engine, as well as the InnoDB storage engine, which is touted as a “transaction-safe, ACID-compliant database” with some additional features over the standard version.
MySQL Pro is the commercial version.
MySQL Max includes the more technologically advanced features that are available during early access programs.
MySQL Classic is the standard storage engine without the InnoDB engine. This is another commercial version.
Most of the recommendations discussed in this article apply to all versions of MySQL unless otherwise noted.
Introducing Security Into MySQL Design
As with securing a network, securing a database by looking at the various layers that are involved is an effective approach. Security of databases can be defined as preventing unauthorized or accidental disclosure, alteration, or destruction of data [2]. In addition, the confidentiality of data that exists in the database must be considered, as should the availability of that data. The following section will discuss a secure database design; while not all-inclusive, it should provide a good, basic starting point.
Three-Tier Design
Also referred to as n-tier design, this design incorporates the three layers of a Web application running on different servers, usually set apart by firewalls that have specific rules to only let traffic through to the specific port on a specific server at whichever layer that the user is trying to access:
Internet -> Firewall -> Web -> Firewall -> Application -> Firewall -> Database
Something else that it should demonstrate is that it is very costly to implement such a design because firewalls and servers are not cheap. Oftentimes, a sys admin will choose a compromise, combining the application and database servers. This isn’t ideal from a security perspective; nevertheless, it is a vast improvement over leaving a sensitive database facing the Internet directly. The point is that if one of the layers closest to the Internet is compromised, then several more layers still need to be compromised before access to the vital information can be gained.
Access Control
Access to information contained in the tables must be properly regulated. This can be done with control over direct access to the tables, and also through views. Views and privileges assigned to the views can be created to limit users to only see specified portions of data contained within a table [2]. Through the use of the selects, projections and joins, existing relations between tables in a relational database, as well as a single table, can be created. Control over the read, insert, update and delete commands must also be assigned appropriately within those views.
Roles
Role-based authentication should be considered when adding access to any database. Typical roles for access include administrator, user, programmer and operator. For the first three roles, it is fairly obvious what access should be granted; it is the operator role that can be a sticking point. Operators are expected to play an essential part in the production operation of a system, yet they are often restricted in what type of access they are granted. Segregation of duties should be considered in the operator role, instead of just granting one operator control over an entire process. Operators’ roles do need to be carefully defined and kept within the realm of production support as much as possible. Furthermore, all roles should have logging enabled to keep track of what occurs [3].
Integrity
Another key ingredient in database design is data integrity, or ensuring that the data that is stored in the database is in fact valid and accurate. It is best to determine very early in the design process that it will be responsible for ensuring the integrity of the database. No matter the sensitivity of the data (credit card information vs. your record collection), if the data isn’t right, then what good is the database? When the owner is determined, they should maintain this role and appropriate access only, not attempting to dole this out to others less it become diluted and possibly become corrupt.
A good process for ensuring the integrity of the data includes understanding what is processed and then identifying what can be considered personal, critical, or proprietary. As with any security issue, risk must be assigned according to the likelihood that something could occur to that data and the potential effect of such an occurence. Most of all, accountability must be assigned and designed into the environment where the database resides. Otherwise, the goals of privacy and security cannot be met [3].
Encryption
The sensitivity of the data will logically determine the need for the use of encryption. There are a few things to consider when thinking about implementing encryption:
1. Will the data stored in the database need to be encrypted or just the user passwords?
2. Will you need to encrypt the data only in the local instance of the database, or do you need to also encrypt the data in transit?
Change Control
It is important to remember that changes made to the database, whether structural or to the data itself, must be tracked and regulated by interested parties. Whether formal or informal, the process must be defined and followed by all roles defined in the database structure.
Specific MySQL Security Considerations
Now that we have covered some of the general principals of database security, we can examine some specific considerations for the MySQL database. Please note that many variables that are mentioned in the following discussion are set in the “my.cnf” file. The location of this depends on how the MySQL database is installed. Essentially, you can create the file on your own, or use one of the handy sample files that come with the distribution (see the “support-files” directory). Then, if you would like the parameters to apply all MySQL users, you can place the “my.cnf” file in /etc. If you want the parameters to apply to specific users, then you can set the file in their respective home directory as “.my.cnf”. Make sure that the appropriate permissions are applied to the file wherever it resides, ensuring that the unauthorizedusers cannot write to it.
A discussion of the basic post-installation configuration of MySQL is beyond the scope of this discussion. For that information, please refer to the MySQL documentation, Post-Installation Set-up and Testing, and Setting Up the Initial MySQL Privileges, as well as Ryan W. Maple's article MySQL Security.
The MySQL Permission Model
In order to fully implement a secure MySQL database, it is necessary to learn the MySQL access control system (your friends the GRANT and REVOKE commands). There are four privilege levels that apply:
1. Global: these privileges apply to all databases on a server.
2. Database: these privileges apply to all tables in a database.
3. Table: these apply to all columns within a table.
4. Column: these apply to individual columns in a table.
The usage of these commands is varied:
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name * *.* db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[REQUIRE
NONE
[{SSL X509}]
[CIPHER cipher [AND]]
[ISSUER issuer [AND]]
[SUBJECT subject]]
[WITH [GRANT OPTION MAX_QUERIES_PER_HOUR #
MAX_UPDATES_PER_HOUR #
MAX_CONNECTIONS_PER_HOUR #]]

REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name * *.* db_name.*}
FROM user_name [, user_name ...]
The privileges can get very granular, so it is important that they are used in a well planned fashion. The types of privileges include:
Alter
Delete
Create
Drop
Execute
Select
Update
Once a database is completely set up, these privileges should be reviewed prior to going to any usage of the database to ensure that the privileges were set up appropriately.
For instance, if you wanted to limit the alter privilege only to the user kristyw for table called CreditCards, you would use the command as follows:
Mysql> GRANT alter on CreditCards to kristyw
Mysql> IDENTIFIED by "password";
This could take some time if multiple privileges for the same user are to be added. In this case, wildcards can be used, but use caution in doing so! You never want to add more privilege than is necessary or intended. Further, if a user account is compromised, then the use of blanket permissions to numerous hosts can open up unexpected trust relationships between systems. Additionally, if the hostname is left blank for connections, which also effectively works as a wildcard [7].
For example, say the user kristyw should now have all the privileges to everything in the database, as well as be required to connect to the database via an SSL connection:
Mysql> GRANT all on *.* to kristyw
Mysql> IDENTIFIED by ‘password’ REQUIRE SSL;
The wildcards that apply with the GRANT and REVOKE permissions include the “*”, which when used with grant privileges to *.* indicates global permissions, the “_”’, which if not used with a “\” in front of it (as in “\_”) could unintentionally indicate access to other databases, and lastly, the “%” can be used in hostnames.
Another privilege that can be assigned controls via GRANT and REVOKE is the PROCESS privilege, which should be restricted to only appropriate users. When used in the format: “mysqladmin processlist”, disclosure of password information is possible. This is especially true if the user excecuted the query with the syntax of “UPDATE user SET password=PASSWORD(‘not_secure')” in their query. Furthermore, restrict the use of the FILE privilege. This privilege allows the assigned user to write a file wherever the mysqld daemon has privileges too. In addtion, the FILE privilege can be used to view any file within the UNIX filesystem that the user has privileges to [7].
More Advanced Tips…
If a database only needs to be accessed locally, TCP networking can be disabled. You can achieve this by editing the safe_mysqld script (located in /mysq_linstall_dir/bin). Search for “skip-locking”, and add the “skip-networking” flag to the beginning of the line that includes “–skip-locking”:
--skip-networking --skip-locking > > $err_log 2> &1

--skip-networking --skip-locking "$@" > > $err_log 2> &1
Now no one will be able to remotely connect to the database [4].
Start up MySQL with the parameter to disable the use of symlinks (via the –skip-symlink option). This will prevent the possibility of escalated privileges given to the owner of whoever started the mysqld process. Ultimately this could result in accidental or deliberate overwriting of files on your system, so it is best to just remove their usage.
To prevent a type of denial of service by one compromised or careless user account, you should restrict connections for a single user, by setting the max_user_connections variable in mysqld. These options can be viewed via the SHOW VARIABLES command, and can be updated via the SET command [7].
There are a few methods to encrypt stored data in a MySQL database: you can use the ENCRYPT or ENCODE commands. The difference between the two is that ENCRYPT uses the Unix crypt call, where as ENCODE uses a password provided in the command to encrypt the string. Both MD5 and SHA1 hash algorithms are available, as well as AES and DES. (Note: SHA1, DES and AES algorithms only available in version 4.0.2 and later).
By default, passwords are inserted into tables using encryption. Also by default, passwords in MySQL are unrelated to operating system passwords. There is no length limit on passwords in MySQL; they can be as short or as long as you want (however, the OS may restrict that length). User names can be up to sixteen characters, but can also be shorter. Therefore, any strict password parameters will have to be encouraged by setting policies and enforced by auditing. Overall, just make sure that all accounts do have passwords, just like you should on their operating system accounts.
All the Other Goodies
Another thing to keep in mind when securing your database is all of the other possible tools that are installed on your server. If you are on a tight budget, and have placed your Web server with the database (which is still not recommended), then tools like Apache, PHP or Java may be loaded on the same server. If these tools are not kept up-to-date (just as with the OS), then possible exploits may apply, and the server may be vulnerable to intrusion.
The same principle applies to the other utility packages that may be loaded on your server, such as: SSH, zlib, or wu-ftp. Always remember to apply security checklists to your servers, know what is running on them, and keep up with the latest vulnerabilities.
We have considered many things here to design into the database itself, but one item that needs to be considered is how the traffic is transmitted between the client and the server. If the data is sensitive and/or going over the Internet, then SSL should be employed. Version 4.0 of MySQL will satisfy this requirement. To have your version of MySQL use SSL, configure it with the following flags: --with-vio --with-openssl.
There are also ways to lock down the usage of SSL once you have it properly installed. If the REQUIRE SSL option is used, no non-SSL connections can be made to this server. Be cautious when employng the REQUIRE X509 option, though, because its usage alone does not mean that the certificate will be validated, only that the user must have one. Other REQUIRE options must be set to have proper validation (e.g. ISSUER, SUBJECT). Lastly, REQUIRE CIPHER is the parameter that requires that certain ciphers and keylengths be used [6].
For older versions of MySQL, an encrytped SSH tunnel is a viable option.
And All That Other Stuff…
Don’t forget the other layers of your servers!!! When I say this, I mean that the security of the operating system, the server authentication, and the server access control must all be taken care of as well, because if these are weak, then why even bother securing your database?
Database Backups
Another area that often gets lost in the layers of security is the critical area of database backup and recovery. As a part of whatever backup type is used, testing recovery of data is mandatory. Further, since version 3.23.47, checkpointing (where copies of the database are saved at defined times while processing) has been improved to be done more frequently, also easing the recovery process. With frequent checkpointing, as well as transaction logging (now available with InnoDB) and making regular backup copies, backup and recovery of databases is made more straightforward.
Specifically, the InnoDB transactional model allows for commit, rollback and crash recovery. By adding locking capabilities for users, having many users access the database at the same time becomes faster and more stable. To ensure that InnoDB is available with your installation, configure your package with the ‘—with-innodb’ flags. You will also want to specify InnoDB options in your ‘my.cnf’ file. Details on these set-up options can be found at the MySQL Documentation on InnoDB Start-up Options.
Add-Ons
As is the case with a lot of popular freeware tools, MySQL has spawned many other tools that can help improve the management of MySQL databases; thus, making the improving the security of the databases as well. If you are looking for a tool to help scan your network for blank MySQL passwords, try this MySQL Network Scanner script. It was originally compiled for Linux and to scan a class C network, but could be modified if needed.
There are several GUI consoles available to make the management of MySQL database easier. For instance, MySQL Explorer allows several management processes to be done via an interface that runs on several windows platforms. The MySQL team also has a version in beta called MySQL Control Center, and the source code is available here: here. Just keep in mind when using these graphical tools and editors to help you manage a MySQL database that security needs to apply to them as well. This can be done through use of the ACLs to make sure that only certain servers can connect to your database on certain ports.
Conclusion
Many of the standard secure database design principles apply to MySQL. Of course, it has many of its own intricacies that need to be understood and audited carefully before any database is fully implemented. Lastly, it is important to keep in mind that other layers of security apply when hosting a database, such as network and operating system security. The good news is that the makers of MySQL have an excellent documentation area on their Web site that, although sometimes cumbersome to navigate, is well stocked with information for the MySQL developer and administrator.
References
[1] Database Management and Design, Gary W. Hansen and James V. Hansen, Prentice Hall, 1992
[2] A Primer on SQL, Roy Ageloff, Times Mirror/Mosby College Publishing, 1988
[3] Database: Structure Techniques for Design, Performance and Management, 2nd Edition, Shaku Atre, John Wiley and Sons, 1988
[4] mysql security, Ryan W. Maple, http://www.linuxsecurity.com/tips/tip-24.html
[5] General Security Guidelines, http://www.mysql.com/doc/en/General_security.html
[6] SSL Usage Requirements http://www.mysql.com/doc/en/Secure_requirements.html
[7] How to Make MySQL Secure Against Crackers http://www.mysql.com/doc/en/Security.html