(mysql.info) security-guidelines
Info Catalog
(mysql.info) security
(mysql.info) security
(mysql.info) security-against-attack
5.7.1 General Security Guidelines
---------------------------------
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. *The
encrypted password is the real password in MySQL.* Anyone who
knows the password that is listed in the `user' table and has
access to the host listed for the account *can easily log in as
that user*.
* 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 default-privileges.
* 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_escape_string()' function, which is based
on the function of the same name in the MySQL C API. (Prior
to PHP 4.0.3, use `addslashes()' instead.) 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 the `quote()' method or use placeholders.
* Ruby DBI: Use placeholders.
* 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.
Info Catalog
(mysql.info) security
(mysql.info) security
(mysql.info) security-against-attack
automatically generated byinfo2html