MySQL Configuration

Once the users and privileges have been resolved, there are a few other configuration changes you may wish to make, in order to tighten things up a little more.

1. Enable logging via the --log option.

   The "general query log" is considered a debugging feature in the MySQL documentation, but you may prefer to use the feature as a routine part of your security posture . It logs successful connections and every query that executes. It doesn't log the results of those queries, or the data that was returned, but it does give you a good idea of who has been doing what on your database, so it may be a worthwhile configuration change. The query log is not enabled by default; you'll have to turn it on using the --log option.

   Bear in mind that as well as being an invaluable resource for an administrator, a full query log is an excellent source of information to an attacker. If the log is large, it may contain passwords or other sensitive information. You should ensure that the log file is visible only to MySQL and to the Administrator (or root) account on the system in question.

   Another interesting point about the query log is that any account that has FILE privilege (file_priv) can of course read the log file by executing a statement like

    select load_file('query.log'); 

   ( assuming the log file is named query.log). This is another fine reason to avoid giving people FILE privilege.

2. Disable the LOAD DATA LOCAL INFILE command by adding set-variable =local-infile=0 to the my.cnf file.

   LOAD DATA LOCAL INFILE is a variation of the LOAD DATA statement that allows clients to directly upload data from a file in their local filesystem into a table in MySQL. This can be abused by an attacker to read files on client hosts under certain circumstances. For example, suppose a web server is running a PHP application that is vulnerable to SQL injection. If the MySQL server that the web app is connecting to allows "load data local infile," the attacker can upload data from the web server into the MySQL server, where he can analyze it at his leisure.

3. Remove any unused UDFs (the default at the time of this writing was for the mysql.func table to be empty).

   UDFs can be exceptionally dangerous. MyPHP, MyPERL, MyLUA, and so on all allow attackers to greatly extend their control over a server with a few simple script commands. If you're not using UDFs, and you see them in the mysql.func table, remove them.

4. If only you're using only local connections, disable TCP/IP connections via the --skip-networking option.

   Sometimes there's no need for remote hosts to connect to MySQL. In these cases, you might as well disable network support, by specifying the --skip-networking option.

5. Depending on your operating system, and how your data directory is configured, you might want to disallow the use of symbolic links via the --skip-symbolic-links option.

   MySQL symbolic links are supported on the Windows platform, in a limited fashion involving the creation of a file named <database>.sym, and containing the path to the directory that will contain all of the table files for that database.

   Allowing symbolic links may cause problems on some Unix systems. If users can modify a symbolic link, they will be able to interact with the filesystem to a limited extent as though they were the mysql user .

6. Remove the default test database.

   The test database is present by default, and should be removed. Knowledge of a valid database name that he can access may be useful to an attacker, so the default test database should be removed.

7. Ensure MySQL traffic is encrypted.

   By default, MySQL traffic is not encrypted. This means that if an attacker can eavesdrop on the connection between the client and the server, he can obtain usernames and password challenge/response sequences. If you are running a version of MySQL prior to 4.1, it is possible to determine the password hash from the challenge/response sequence. The process is slightly harder in MySQL 4.1, but still possible.

   MySQL version 4.0 onward has support for SSL-encrypted connections between the client and server, making a brute-force attack on the password hash almost impossible . To configure this, use the --ssl option, and other associated SSL options. The best way to ensure that an SSL connection is actually being used is to specify the REQUIRE SSL clause in the GRANT statement that creates a user, or to manually set the ssl_type field in the mysql.user table.