This section describes some general security issues to be aware of and what you can do to make your MySQL installation more secure against attack or misuse. For information specifically about the access control system that MySQL uses for setting up user accounts and checking database access, see Section 5.8, “The MySQL Access Privilege System”.
Anyone using MySQL on a computer connected to the Internet should read this section to avoid the most common security mistakes.
In discussing security, we emphasize the necessity of fully protecting the entire server host (not just the MySQL server) against all types of applicable attacks: eavesdropping, altering, playback, and denial of service. We do not cover all aspects of availability and fault tolerance here.
MySQL uses security based on Access Control Lists (ACLs) for all connections, queries, and other operations that users can attempt to perform. There is also support for SSL-encrypted connections between MySQL clients and servers. Many of the concepts discussed here are not specific to MySQL at all; the same general ideas apply to almost all applications.
When running MySQL, follow these guidelines whenever possible:
Do not ever give anyone (except MySQL
root accounts) access to the
user table in the
mysql database! This is
critical.
Learn the MySQL access privilege system. The
GRANT and REVOKE
statements are used for controlling access to MySQL. Do not
grant more privileges than necessary. Never grant privileges
to all hosts.
Checklist:
Try mysql -u root. If you are able to
connect successfully to the server without being asked
for a password, anyone can connect to your MySQL server
as the MySQL root user with full
privileges! Review the MySQL installation instructions,
paying particular attention to the information about
setting a root password. See
Section 2.10.3, “Securing the Initial MySQL Accounts”.
Use the SHOW GRANTS statement to
check which accounts have access to what. Then use the
REVOKE statement to remove those
privileges that are not necessary.
Do not store any plain-text passwords in your database. If
your computer becomes compromised, the intruder can take the
full list of passwords and use them. Instead, use
MD5(), SHA1(), or some
other one-way hashing function and store the hash value.
Do not choose passwords from dictionaries. Special programs exist to break passwords. Even passwords like “xfish98” are very bad. Much better is “duag98” which contains the same word “fish” but typed one key to the left on a standard QWERTY keyboard. Another method is to use a password that is taken from the first characters of each word in a sentence (for example, “Mary had a little lamb” results in a password of “Mhall”). The password is easy to remember and type, but difficult to guess for someone who does not know the sentence.
Invest in a firewall. This protects you from at least 50% of all types of exploits in any software. Put MySQL behind the firewall or in a demilitarized zone (DMZ).
Checklist:
Try to scan your ports from the Internet using a tool
such as nmap. MySQL uses port 3306 by
default. This port should not be accessible from
untrusted hosts. Another simple way to check whether or
not your MySQL port is open is to try the following
command from some remote machine, where
server_host is the hostname
or IP number of the host on which your MySQL server
runs:
shell> telnet server_host 3306
If you get a connection and some garbage characters, the port is open, and should be closed on your firewall or router, unless you really have a good reason to keep it open. If telnet hangs or the connection is refused, the port is blocked, which is how you want it to be.
Do not trust any data entered by users of your applications.
They can try to trick your code by entering special or
escaped character sequences in Web forms, URLs, or whatever
application you have built. Be sure that your application
remains secure if a user enters something like
“; DROP DATABASE mysql;”.
This is an extreme example, but large security leaks and
data loss might occur as a result of hackers using similar
techniques, if you do not prepare for them.
A common mistake is to protect only string data values.
Remember to check numeric data as well. If an application
generates a query such as SELECT * FROM table WHERE
ID=234 when a user enters the value
234, the user can enter the value
234 OR 1=1 to cause the application to
generate the query SELECT * FROM table WHERE ID=234
OR 1=1. As a result, the server retrieves every
row in the table. This exposes every row and causes
excessive server load. The simplest way to protect from this
type of attack is to use single quotes around the numeric
constants: SELECT * FROM table WHERE
ID='234'. If the user enters extra information, it
all becomes part of the string. In a numeric context, MySQL
automatically converts this string to a number and strips
any trailing non-numeric characters from it.
Sometimes people think that if a database contains only publicly available data, it need not be protected. This is incorrect. Even if it is allowable to display any row in the database, you should still protect against denial of service attacks (for example, those that are based on the technique in the preceding paragraph that causes the server to waste resources). Otherwise, your server becomes unresponsive to legitimate users.
Checklist:
Try to enter single and double quote marks
(‘'’ and
‘"’) in all of your Web
forms. If you get any kind of MySQL error, investigate
the problem right away.
Try to modify dynamic URLs by adding
%22
(‘"’),
%23
(‘#’), and
%27
(‘'’) to them.
Try to modify data types in dynamic URLs from numeric to character types using the characters shown in the previous examples. Your application should be safe against these and similar attacks.
Try to enter characters, spaces, and special symbols rather than numbers in numeric fields. Your application should remove them before passing them to MySQL or else generate an error. Passing unchecked values to MySQL is very dangerous!
Check the size of data before passing it to MySQL.
Have your application connect to the database using a username different from the one you use for administrative purposes. Do not give your applications any access privileges they do not need.
Many application programming interfaces provide a means of escaping special characters in data values. Properly used, this prevents application users from entering values that cause the application to generate statements that have a different effect than you intend:
MySQL C API: Use the
mysql_real_escape_string() API call.
MySQL++: Use the escape and
quote modifiers for query streams.
PHP: Use the
mysql_real_escape_string() function
(available as of PHP 4.3.0, prior to that PHP version
use mysql_escape_string(), and
prior to PHP 4.0.3, use
addslashes() ). Note that only
mysql_real_escape_string() is
character set-aware; the other functions can be
“bypassed” when using (invalid) multi-byte
character sets. In PHP 5, you can use the
mysqli extension, which supports the
improved MySQL authentication protocol and passwords, as
well as prepared statements with placeholders.
Perl DBI: Use placeholders or the
quote() method.
Ruby DBI: Use placeholders or the
quote() method.
Java JDBC: Use a PreparedStatement
object and placeholders.
Other programming interfaces might have similar capabilities.
Do not transmit plain (unencrypted) data over the Internet. This information is accessible to everyone who has the time and ability to intercept it and use it for their own purposes. Instead, use an encrypted protocol such as SSL or SSH. MySQL supports internal SSL connections as of version 4.0. Another technique is to use SSH port-forwarding to create an encrypted (and compressed) tunnel for the communication.
Learn to use the tcpdump and strings utilities. In most cases, you can check whether MySQL data streams are unencrypted by issuing a command like the following:
shell> tcpdump -l -i eth0 -w - src or dst port 3306 | strings
(This works under Linux and should work with small modifications under other systems.) Warning: If you do not see plaintext data, this doesn't always mean that the information actually is encrypted. If you need high security, you should consult with a security expert.
When you connect to a MySQL server, you should use a password. The password is not transmitted in clear text over the connection. Password handling during the client connection sequence was upgraded in MySQL 4.1.1 to be very secure. If you are still using pre-4.1.1-style passwords, the encryption algorithm is not as strong as the newer algorithm. With some effort, a clever attacker who can sniff the traffic between the client and the server can crack the password. (See Section 5.8.9, “Password Hashing as of MySQL 4.1”, for a discussion of the different password handling methods.)
All other information is transferred as text, and can be read by anyone who is able to watch the connection. If the connection between the client and the server goes through an untrusted network, and you are concerned about this, you can use the compressed protocol to make traffic much more difficult to decipher. You can also use MySQL's internal SSL support to make the connection even more secure. See Section 5.9.7, “Using Secure Connections”. Alternatively, use SSH to get an encrypted TCP/IP connection between a MySQL server and a MySQL client. You can find an Open Source SSH client at http://www.openssh.org/, and a commercial SSH client at http://www.ssh.com/.
To make a MySQL system secure, you should strongly consider the following suggestions:
Require all MySQL accounts to have a password. A client
program does not necessarily know the identity of the person
running it. It is common for client/server applications that
the user can specify any username to the client program. For
example, anyone can use the mysql program
to connect as any other person simply by invoking it as
mysql -u if
other_user
db_nameother_user has no password. If
all account have a password, connecting using another user's
account becomes much more difficult.
For a discussion of methods for setting passwords, see Section 5.9.5, “Assigning Account Passwords”.
Never run the MySQL server as the Unix
root user. This is extremely dangerous,
because any user with the FILE privilege
is able to cause the server to create files as
root (for example,
~root/.bashrc). To prevent this,
mysqld refuses to run as
root unless that is specified explicitly
using the --user=root option.
mysqld can (and should) be run as an
ordinary, unprivileged user instead. You can create a
separate Unix account named mysql to make
everything even more secure. Use this account only for
administering MySQL. To start mysqld as a
different Unix user, add a user option
that specifies the username in the
[mysqld] group of the
my.cnf option file where you specify
server options. For example:
[mysqld] user=mysql
This causes 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 5.7.5, “How to Run MySQL as a Normal User”.
Running mysqld as a Unix user other than
root does not mean that you need to
change the root username in the
user table. Usernames for MySQL
accounts have nothing to do with usernames for Unix
accounts.
Do not allow the use of symlinks to tables. (This capability
can be disabled with the
--skip-symbolic-links option.) This is
especially important if you run mysqld as
root, because anyone that has write
access to the server's data directory then could delete any
file in the system! See
Section 7.6.1.2, “Using Symbolic Links for Tables on Unix”.
Make sure that the only Unix user with read or write privileges in the database directories is the user that mysqld runs as.
Do not grant the PROCESS or
SUPER privilege to non-administrative
users. The output of mysqladmin
processlist and SHOW
PROCESSLIST shows the text of any statements
currently being executed, so any user who is allowed to see
the server process list might be able to see statements
issued by other users such as UPDATE user SET
password=PASSWORD('not_secure').
mysqld reserves an extra connection for
users who have the SUPER privilege, so
that a MySQL root user can log in and
check server activity even if all normal connections are in
use.
The SUPER privilege can be used to
terminate client connections, change server operation by
changing the value of system variables, and control
replication servers.
Do not grant the FILE privilege to
non-administrative 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, files generated with SELECT ...
INTO OUTFILE do not overwrite existing files and
are writable by everyone.
The FILE privilege may also be used to
read any file that is world-readable or accessible to the
Unix user that the server runs as. With this privilege, you
can read any file into a database table. This could be
abused, for example, by using LOAD DATA
to load /etc/passwd into a table, which
then can be displayed with SELECT.
If you do not trust your DNS, you should use IP numbers rather than 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 allowed to
a single account, you can do so by setting the
max_user_connections variable in
mysqld. The GRANT
statement also supports resource control options for
limiting the extent of server use allowed to an account. See
Section 13.5.1.3, “GRANT Syntax”.
Options that begin with --ssl specify
whether to allow clients to connect via SSL and indicate
where to find SSL keys and certificates. See
Section 5.9.7.3, “SSL Command Options”.
The following mysqld options affect security:
This option controls whether user-defined functions that
have only an xxx symbol for the main
function can be loaded. By default, the option is off and
only UDFs that have at least one auxiliary symbol can be
loaded; this prevents attempts at loading functions from
shared object files other than those containing legitimate
UDFs. For MySQL 5.0, this option was added in MySQL 5.0.3.
See Section 24.2.4.6, “User-Defined Function Security Precautions”.
If you start the server with
--local-infile=0, clients cannot use
LOCAL in LOAD DATA
statements. See Section 5.7.4, “Security Issues with LOAD DATA LOCAL”.
Force the server to generate short (pre-4.1) password hashes for new passwords. This is useful for compatibility when the server must support older client programs. See Section 5.8.9, “Password Hashing as of MySQL 4.1”.
--safe-show-database
(OBSOLETE)
In previous versions of MySQL, this option caused the
SHOW DATABASES statement to display the
names of only those databases for which the user had some
kind of privilege. In MySQL 5.0, this option is
no longer available as this is now the default behavior, and
there is a SHOW DATABASES privilege that
can be used to control access to database names on a
per-account basis. See Section 13.5.1.3, “GRANT Syntax”.
If this option is enabled, a user cannot create new MySQL
users by using the GRANT statement unless
the user has the INSERT privilege for the
mysql.user table. If you want a user to
have the ability to create new users that have those
privileges that the user has right to grant, you should
grant the user the following privilege:
GRANT INSERT(user) ON mysql.user TO 'user_name'@'host_name';
This ensures that the user cannot change any privilege
columns directly, but has to use the
GRANT statement to give privileges to
other users.
Disallow authentication for accounts that have old (pre-4.1) passwords.
The mysql client also has a
--secure-auth option, which prevents
connections to a server if the server requires a password in
old format for the client account.
This option causes the server not to use the privilege
system at all. This gives anyone with access to the server
unrestricted access to all
databases. You can cause a running server to
start using the grant tables again by executing
mysqladmin flush-privileges or
mysqladmin reload command from a system
shell, or by issuing a MySQL FLUSH
PRIVILEGES statement. This option also suppresses
loading of user-defined functions (UDFs).
Hostnames are not resolved. All Host
column values in the grant tables must be IP numbers or
localhost.
Do not allow TCP/IP connections over the network. All connections to mysqld must be made via Unix socket files.
With this option, the SHOW DATABASES
statement is allowed only to users who have the
SHOW DATABASES privilege, and the
statement displays all database names. Without this option,
SHOW DATABASES is allowed to all users,
but displays each database name only if the user has the
SHOW DATABASES privilege or some
privilege for the database. Note that any global privilege
is a privilege for the database.
The LOAD DATA statement can load a file that
is located on the server host, or it can load a file that is
located on the client host when the LOCAL
keyword is specified.
There are two potential security issues with supporting the
LOCAL version of LOAD DATA
statements:
The transfer of the file from the client host to the server
host is initiated by the MySQL server. In theory, a patched
server could be built that would tell the client program to
transfer a file of the server's choosing rather than the
file named by the client in the LOAD DATA
statement. Such a server could access any file on the client
host to which the client user has read access.
In a Web environment where the clients are connecting from a
Web server, a user could use LOAD DATA
LOCAL to read any files that the Web server
process has read access to (assuming that a user could run
any command against the SQL server). In this environment,
the client with respect to the MySQL server actually is the
Web server, not the remote program being run by the user who
connects to the Web server.
To deal with these problems, we changed how LOAD DATA
LOCAL is handled as of MySQL 3.23.49 and MySQL 4.0.2
(4.0.13 on Windows):
By default, all MySQL clients and libraries in binary
distributions are compiled with the
--enable-local-infile option, to be
compatible with MySQL 3.23.48 and before.
If you build MySQL from source but do not invoke
configure with the
--enable-local-infile option, LOAD
DATA LOCAL cannot be used by any client unless it
is written explicitly to invoke mysql_options(...
MYSQL_OPT_LOCAL_INFILE, 0). See
Section 22.2.3.48, “mysql_options()”.
You can disable all LOAD DATA LOCAL
commands from the server side by starting
mysqld with the
--local-infile=0 option.
For the mysql command-line client,
LOAD DATA LOCAL can be enabled by
specifying the --local-infile[=1] option,
or disabled with the --local-infile=0
option. Similarly, for mysqlimport, the
--local or -L option
enables local data file loading. In any case, successful use
of a local loading operation requires that the server is
enabled to allow it.
If you use LOAD DATA LOCAL in Perl
scripts or other programs that read the
[client] group from option files, you can
add the local-infile=1 option to that
group. However, to keep this from causing problems for
programs that do not understand
local-infile, specify it using the
loose- prefix:
[client] loose-local-infile=1
If LOAD DATA LOCAL INFILE is disabled,
either in the server or the client, a client that attempts
to issue such a statement receives the following error
message:
ERROR 1148: The used command is not allowed with this MySQL version
On Windows, you can run the server as a Windows service using a normal user account.
On Unix, the MySQL server mysqld can be
started and run by any user. However, you should avoid running
the server as the Unix root user for security
reasons. To change mysqld to run as a normal
unprivileged Unix user user_name, you
must do the following:
Stop the server if it's running (use mysqladmin shutdown).
Change the database directories and files so that
user_name has privileges to read
and write files in them (you might need to do this as the
Unix root user):
shell> chown -R user_name /path/to/mysql/datadir
If you do not do this, the server will not be able to access
databases or tables when it runs as
user_name.
If directories or files within the MySQL data directory are
symbolic links, you'll also need to follow those links and
change the directories and files they point to.
chown -R might not follow symbolic links
for you.
Start the server as user
user_name. If you are using MySQL
3.22 or later, another alternative is to start
mysqld as the Unix
root user and use the
--user=
option. mysqld starts up, then switches
to run as the Unix user user_nameuser_name
before accepting any connections.
To start the server as the given user automatically at
system startup time, specify the username by adding a
user option 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=user_name
If your Unix machine itself isn't secured, you should assign
passwords to the MySQL root accounts in the
grant tables. Otherwise, any user with a login account on that
machine can run the mysql client with a
--user=root option and perform any operation.
(It is a good idea to assign passwords to MySQL accounts in any
case, but especially so when other login accounts exist on the
server host.) See Section 2.10, “Post-Installation Setup and Testing”.