MySQL Users

Once you've secured the operating system, you need to lock down MySQL itself. The first step to doing this is to address the user accounts and privilege model.

1. Set a strong password for the root@localhost account. The reasoning behind this should be obvious; there is no mechanism in MySQL for locking out a user if the password is guessed incorrectly a number of times. A brute-force attack on MySQL usernames and passwords is fairly effective, as MySQL worms have proven in the past. Setting strong passwords will help defend against the possibility of an attacker guessing yours.

2. Remove all non-root MySQL users. During the initial setup phase it is important to know where you stand in terms of the users that have access to the database. The best approach is to strip the users down to the barest essentialsthe root accountand then build up users as you need them.

3. Rename the root MySQL user to something obscure. The root account in MySQL is a well-known account name; several publicly available tools, scripts, and exploits rely on the fact that there is an account named root. MySQL attaches no specific meaning to the account name root, so there's absolutely no reason why you can't rename it to something a little more obscure, like this:

    update mysql.user set user='mysql_admin' where user='root'; 

4. If remote connections are enabled, specify REQUIRE SSL in the GRANT statement used to set up the user. This is a slightly trickier configuration step that will enforce SSL encryption on connections from the specified user. There are several benefits to this: first of all it ensures that some custom-written exploit scripts will not work (because they don't have SSL support); second, it ensures confidentiality of the password challenge/response sequence, which as you have seen, is weak in current versions of MySQL. Depending on how far you are willing to go, you can also enforce restrictions based on a client-side certificate used to authenticate with SSL, which is a highly secure option because simple knowledge of a password is not enoughyou have to have the specified certificate as well.

5. Create a MySQL user for each web applicationor possibly for each role within each web application. For instance, you might have one MySQL user that you use to update tables, and another, lower-privileged user that you use to "select" from tables. Dividing the various roles within your application into separate user accounts may seem tedious but it makes good security sense. It means that if attackers are able to somehow compromise a particular component of your application, they will be limited (in terms of MySQL) to just that component's privileges. For example, they might be able to read some of the data, but not update any of it.

6. Ensure that MySQL users are restricted by IP address as well as passwords. See section 5.4 of the MySQL manual, "The MySQL Access Privilege System," for more information. Once your MySQL configuration is bedded in, consider restricting the client IP addresses from which users can authenticate. This is an extremely useful feature of MySQL that most other databases lack. Effectively, you're placing another hurdle in front of attackers as they try to compromise your MySQL serverrather than just attacking the server directly, you're forcing them to have to compromise some other, specific host in order to connect to your database server. The more hurdles you can place in front of attackers, the better. Using IP addresses rather than hostnames is slightly harder to manage but much more secure. When you specify that a user can log in only from some hostname or domain, MySQL has to look up the IP address to verify that the IP address in question is a member of that domain. Normally this lookup is performed via DNS, the Domain Name System. An attacker can compromise your DNS server, or imitate its response, resulting in your MySQL server believing that it's talk-ing to a machine in the permitted domain when it isn't. If you use IP addresses, however, the attacker will have to forge the client part of a three-way TCP handshake in order to fake a connection, as well as transmission of the data itself. This is much harder (though it is still possible). It's well worth restricting by IP address if you have the option to do so.

7. Don't give accounts privileges that they don't absolutely need, especially File_priv, Grant_priv, and Super_priv. If you have to interact with the filesystem from within MySQL, consider creating a separate MySQL account that your application can use for this purpose. Bear in mind that this account will be able to read all MySQL data, including the password hashes. It is very easy for an attacker to login as root once he knows the password hash. In versions of MySQL prior to 4.1, giving users File_priv is effectively the same as giving them the root password. They will be able to read the password hashes from the mysql.user table by doing this:

    select load_file('./mysql/user.MYD'); 

   Once they have the password hashes, they can easily log in as the user of their choice, with a small modification to their MySQL client. Other privileges can be just as dangerous.

8. Never give anyone (other than root or whatever you call your root account) access to the mysql.user table. In versions prior to 4.1, the password hash stored in the table can be used directly to log in to MySQLthere is no brute forcing necessary. The mysql tables (user, db, host, tables_priv, and columns_priv) control the privilege model in MySQL. The grant and revoke statements modify these tables. If users have permission to write to these tables, they can give anyone any privileges they like. In MySQL versions prior to 4.1, if they can read the password hashes from the user table, they can log in as any other user.