4.2.2 How to Make MySQL Secure Against Crackers

When you connect to a MySQL server, you normally should use a password. The password is not transmitted in clear text over the connection, however the encryption algorithm is not very strong, and with some effort a clever attacker can crack the password if he is able to sniff the traffic between the client and the server. If the connection between the client and the server goes through an untrusted network, you should use an SSH tunnel to encrypt the communication.

All other information is transferred as text that can be read by anyone who is able to watch the connection. If you are concerned about this, you can use the compressed protocol (in MySQL Version 3.22 and above) to make things much harder. To make things even more secure you should use ssh. You can find an Open Source ssh client at http://www.openssh.org/, and a commercial ssh client at http://www.ssh.com/. With this, you can get an encrypted TCP/IP connection between a MySQL server and a MySQL client.

If you are using MySQL 4.0, you can also use internal OpenSSL support. See section 4.3.9 Using Secure Connections.

To make a MySQL system secure, you should strongly consider the following suggestions:

• Use passwords for all MySQL users. Remember that anyone can log in as any other person as simply as mysql -u other_user db_name if other_user has no password. It is common behaviour with client/server applications that the client may specify any user name. You can change the password of all users by editing the mysql_install_db script before you run it, or only the password for the MySQL root user like this:

  shell> mysql -u root mysql
  mysql> UPDATE user SET Password=PASSWORD('new_password')
      ->             WHERE user='root';
  mysql> FLUSH PRIVILEGES;
  

• Don't run the MySQL daemon as the Unix root user. This is very dangerous, because any user with the FILE privilege will be able to create files as root (for example, ~root/.bashrc). To prevent this, mysqld will refuse to run as root unless it is specified directly using a --user=root option. mysqld can be run as an ordinary unprivileged user instead. You can also create a new Unix user mysql to make everything even more secure. If you run mysqld as another Unix user, you don't need to change the root user name in the user table, because MySQL user names have nothing to do with Unix user names. To start mysqld as another Unix user, add a user line that specifies the user name to the [mysqld] group of the `/etc/my.cnf' option file or the `my.cnf' option file in the server's data directory. For example:

  [mysqld]
  user=mysql
  

  This will cause the server to start as the designated user whether you start it manually or by using mysqld_safe or mysql.server. For more details, see section A.3.2 How to Run MySQL As a Normal User.
• Don't support symlinks to tables (this can be disabled with the --skip-symlink option). This is especially important if you run mysqld as root as anyone that has write access to the mysqld data directories could then delete any file in the system! See section 5.6.1.2 Using Symbolic Links for Tables.
• Check that the Unix user that mysqld runs as is the only user with read/write privileges in the database directories.
• Don't give the PROCESS privilege to all users. The output of mysqladmin processlist shows the text of the currently executing queries, so any user who is allowed to execute that command might be able to see if another user issues an UPDATE user SET password=PASSWORD('not_secure') query. mysqld reserves an extra connection for users who have the PROCESS privilege, so that a MySQL root user can log in and check things even if all normal connections are in use.
• Don't give the FILE privilege to all users. Any user that has this privilege can write a file anywhere in the filesystem with the privileges of the mysqld daemon! To make this a bit safer, all files generated with SELECT ... INTO OUTFILE are writeable by everyone, and you cannot overwrite existing files. The FILE privilege may also be used to read any world readable file that is accessible to the Unix user that the server runs as. One can also read any file to the current database (which the user need some privilege for). This could be abused, for example, by using LOAD DATA to load `/etc/passwd' into a table, which can then be read with SELECT.
• If you don't trust your DNS, you should use IP numbers instead of hostnames in the grant tables. In any case, you should be very careful about creating grant table entries using hostname values that contain wildcards!
• If you want to restrict the number of connections for a single user, you can do this by setting the max_user_connections variable in mysqld.

User Comments

It really should be pointed out that running mysqld as nobody is almost
as bad as running it as root. Toss in e.g. apache run as nobody and
anyone who can execute CGI programs can do whatever he wants to
your database. Hooray.

When sorting rows containing hebrew (and perhaps
some other languages who doesn't use english
characters) - hebrew is not sorted at all.
Moreover, when mixed with English rows, the
English goes out fine, while in the middle of it
are some Hebrew rows (unsorted), some more
English rows, again some Hebrew rows (again
unsorted) and so on and so on.

Nothing on the manual here as it appears. If
someone else encountered that and knows of a
workaround, I'll be glad. :-)

The field type is "text" b.t.w.

The command I am using (in php) is:
$query = mysql_query("select * from friends order
by nickname") or die(mysql_error());
while(@($r = mysql_fetch_array($query))) {

I stand to be corrected. :>

I feel that the final comment on this page is
inappropriate and serves only to confuse the new
user, since indexes have not yet been introduced.
Also, if the "menagerie" database is no
longer used from this point on, it would make
sense to demonstrate how to remove it at this
point (DROP DATABASE menagerie"?).

How about being able to configure the Ip address
that mysql listens on. Would be very nice for
multihomed MySQL hosts don't you think?

For example, how about a situation in which each
box in a server
cluster is directly connected to the Internet,
with a second network
set up for intra-cluster communications that isn't
connected to the
Internet ... why expose MySQL directly to the
Internet. then?

That's what the following option to mysqld is
for:
--bind-address=IP Ip address to bind to
which can be entered into my.cnf as
follows:
[mysqld]
bind-address=192.168.1.1
or similar.

Having run through the post-install, I have found that the root user has two entries; one for localhost, and one for localhost.localdomain, which is left with a blank password.

As such, a user at the machine can log into the db as root by passing -h localhost.localdomain. By using the UPDATE user.. WHERE user='root' above, you set the password for both entries.

If you run "UPDATE user SET Password=PASSWORD('new_password') ..." from a UNIX MySQL shell then it will put that exact command -including cleartext password- in your ~/.mysql_history file.

Be sure to remove it when you exit mysql. The command "cat /dev/null > ~/.mysql_history" is overkill, but works great.

Add your own comment.