Internal Security: Securing Data Directory Access
The MySQL server provides a flexible privilege system implemented through the grant tables in the mysql database. You can set up the contents of these tables to allow or deny database access to clients any way you want. This provides you with security against unauthorized network access to your data. However, it won't do you any good to set up good security for network access to your databases if other users on the server host have direct access to the contents of the data directory. Unless you know you are the only person who ever logs in on the machine where the MySQL server is running, you need to be concerned about the possibility of other people on that machine gaining access to the data directory.
Here's what you want to protect:
Database files. Clearly you want to maintain the privacy of the databases maintained by the server. Database owners usually, and rightly, consider database contents private. Even if they don't, it's up to them to make the contents of a database public, not to have those contents be exposed by poor security of the database directory.
Log files. The general and update logs must be kept secure because they contain the text of queries. This is a general concern in that anyone with log file access can monitor the transactions occurring on databases.
A more specific security concern relating to log files is that queries such as GRANT and SET PASSWORD are logged. The general and update log files contain the text of sensitive queries, including passwords. (MySQL uses password encryption, but this applies to connection establishment after passwords already have been set up. The process of setting up a password involves a query such as GRANT, INSERT, or SET PASSWORD, and such queries are logged in plain text form.) If an attacker has read access to the logs, it's only necessary to run grep on the log for words such as GRANT or PASSWORD to discover sensitive information.
Obviously you don't want other users on the server host to have write access to data directory files, because then they can stomp all over your status files or database tables. But read access is just as dangerous. If a table's files can be read, it is trivial to steal the files and to get MySQL itself to show you the contents of the table in plain text. How? Like this:
Install your own rogue MySQL server on the server host, but with a port, socket, and data directory that are different than those used by the official server.
Run mysql_install_db to initialize your data directory. This gives you access to your server as the MySQL root user, so you have full control over the server's access mechanism. It also sets up a test database.
Copy the files corresponding to the table or tables that you want to steal into the test directory under your server's data directory.
Start your rogue server. Presto! You can access the tables at will. SHOW TABLES FROM test shows that you have a copy of the stolen tables, and SELECT * shows the entire contents of any of them.
Think about that for a moment, then reverse the perspective. Do you want someone to do that to you? Of course not.
You can determine whether or not your data directory contains insecure files or directories by executing ls -l in the data directory. Look for files or directories that have the "group" or "other" permissions turned on. Here's a partial listing of a data directory that is insecure, as are some of the database directories within it:
% ls -l
total 10148
drwxrwxr-x 11 mysqladm wheel 1024 May 8 12:20 .
drwxr-xr-x 22 root wheel 512 May 8 13:31 ..
drwx------ 2 mysqladm mysqlgrp 512 Apr 16 15:57 menagerie
drwxrwxr-x 2 mysqladm wheel 512 Jan 25 20:43 mysql
drwxrwxr-x 7 mysqladm wheel 512 Aug 31 1998 sql-bench
drwxrwxr-x 2 mysqladm wheel 1536 May 6 06:11 test
drwx------ 2 mysqladm mysqlgrp 1024 May 8 18:43 tmp
…
As you can see, some database directories have proper permissions, and others do not. The situation in this example resulted over time. The less-restrictive permissions were created by older servers that were less stringent than more recent servers about setting permissions. (Notice that the more restrictive directories, menagerie and tmp, all have more recent dates.) Current versions of MySQL make sure these files are readable only to the user the server runs as.
Let's fix up these permissions so that only the server user can access them. Your principal means of protection comes from the tools provided by the UNIX file system itself to set the ownership and mode of files and directories. Here's what to do:
Move into the data directory:
% cd DATADIR
Set the ownership of all the files under the data directory to be owned by the account used to run the server. (You must perform this step as root.) In this book, I use mysqladm and mysqlgrp for the user and group names of this account. You can change ownerships using either of the following commands:
# chown -R mysqladm.mysqlgrp. # find . -follow -type d -print | xargs chown mysqladm.mysqlgrp
Change the mode of your data directory and database directories so that they are readable only to mysqladm. This prevents other users from accessing the contents of your data directory. You can do this with either of the following commands, which can be run either as root or as mysqladm (the latter is preferable, to minimize the number of commands that are run as root):
% chmod -R go-rwx. % find . -follow -type d -print | xargs chmod go-rwx
The ownership and mode of data directory contents are set for the mysqladm user. Now you should make sure that you always run the server as mysqladm because that is the only user who now has access to the data directory. The procedure for running the server as a non-root is described in Chapter 11.
After following the preceding instructions, you should end up with data directory permissions like this:
% ls -l
total 10148
drwxrwx--- 11 mysqladm mysqlgrp 1024 May 8 12:20 .
drwxr-xr-x 22 root wheel 512 May 8 13:31 ..
drwx------ 2 mysqladm mysqlgrp 512 Apr 16 15:57 menagerie
drwx------ 2 mysqladm mysqlgrp 512 Jan 25 20:43 mysql
drwx------ 7 mysqladm mysqlgrp 512 Aug 31 1998 sql-bench
drwx------ 2 mysqladm mysqlgrp 1536 May 6 06:11 test
drwx------ 2 mysqladm mysqlgrp 1024 May 8 18:43 tmp
…