Home / security

MySQL database security

Anyone using MySQL on a computer connected to the Internet should read this to avoid the most common security mistakes.

In discussing security, we emphasize the necessity of fully protecting the entire server host (not simply 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 a user may attempt to perform. There is also some 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 the mysql root user) 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 which 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 commands are used for controlling access to MySQL. Do not grant any 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, you have problems. 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 item about setting a root password.
    • Use the command SHOW GRANTS and check to see who has access to what. Remove those privileges that are not necessary using the REVOKE command.


  • Do not keep any plain-text passwords in your database. When your computer becomes compromised, the intruder can take the full list of passwords and use them. Instead use MD5(), SHA1() or another one-way hashing function.

  • Do not choose passwords from dictionaries. There are special programs to break them. 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 ``Mhall'' which is taken from the first characters of each word in the sentence ``Mary had a little lamb.'' This is easy to remember and type, but difficult to guess for someone who does not know it.

  • 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 be inaccessible 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 of your MySQL server:
      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 just hangs or the connection is refused, everything is OK; the port is blocked.


  • Do not trust any data entered by your users. 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 may occur as a result of hackers using similar techniques, if you do not prepare for them. Also remember to check numeric data. A common mistake is to protect only strings. Sometimes people think that if a database contains only publicly available data that it need not be protected. This is incorrect. At least denial-of-service type attacks can be performed on such databases. The simplest way to protect from this type of attack is to use apostrophes around the numeric constants: SELECT * FROM table WHERE ID='234' rather than SELECT * FROM table WHERE ID=234. MySQL automatically converts this string to a number and strips all non-numeric symbols from it. Checklist:
    • All web applications:
      • Try to enter `'' and `"' in all your web forms. If you get any kind of MySQL error, investigate the problem right away.
      • Try to modify any dynamic URLs by adding %22 (`"'), %23 (`#'), and %27 (`'') in the URL.
      • Try to modify datatypes in dynamic URLs from numeric ones to character ones containing characters from previous examples. Your application should be safe against this and similar attacks.
      • Try to enter characters, spaces, and special symbols instead of numbers in numeric fields. Your application should remove them before passing them to MySQL or your application should generate an error. Passing unchecked values to MySQL is very dangerous!
      • Check data sizes before passing them to MySQL.
      • Consider having your application connect to the database using a different user name than the one you use for administrative purposes. Do not give your applications any more access privileges than they need.
    • Users of PHP:
      • Check out the addslashes() function. As of PHP 4.0.3, a mysql_escape_string() function is available that is based on the function of the same name in the MySQL C API.
    • Users of MySQL C API:
      • Check out the mysql_real_escape_string() API call.
    • Users of MySQL++:
      • Check out the escape and quote modifiers for query streams.
    • Users of Perl DBI:
      • Check out the quote() method or use placeholders.
    • Users of Java JDBC:
      • Use a PreparedStatement object and placeholders.


  • Do not transmit plain (unencrypted) data over the Internet. These data are 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.0. SSH port-forwarding can be used to create an encrypted (and compressed) tunnel for the communication.

  • Learn to use the tcpdump and strings utilities. For 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 data this doesn't always actually mean that it is encrypted. If you need high security, you should consult with a security expert.

 

To make a MySQL system even more 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 behavior 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.

  • 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!

  • 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.

 

Privileges.

You should never give a user more privileges then needed. Most applications only need SELECT, INSERT, UPDATE, and DELETE privileges.
You can change the privileges of a user with GRANT and REVOKE. 

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    TO user_name [IDENTIFIED BY [PASSWORD] 'password']
        [, user_name [IDENTIFIED BY [PASSWORD] 'password'] ...]
    [REQUIRE
        NONE |
        [{SSL| X509}]
        [CIPHER cipher [AND]]
        [ISSUER issuer [AND]]
        [SUBJECT subject]]
    [WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |
                          MAX_UPDATES_PER_HOUR # |
                          MAX_CONNECTIONS_PER_HOUR #]]

REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
    ON {tbl_name | * | *.* | db_name.*}
    FROM user_name [, user_name ...]
Complex ? Actually its very simple once you break it apart. The following command revokes all right of the user 'test' from the database MYSQL, which is ok cause only the root-user should have access to it.
REVOKE all ON mysql.* FROM test;
GRANT is the same but with a lot more options.

A list of privileges:

PrivilegeColumnContext
ALTERAlter_privtables
DELETEDelete_privtables
INDEXIndex_privtables
INSERTInsert_privtables
SELECTSelect_privtables
UPDATEUpdate_privtables
CREATECreate_privdatabases, tables, or indexes
DROPDrop_privdatabases or tables
GRANTGrant_privdatabases or tables
REFERENCESReferences_privdatabases or tables
CREATE TEMPORARY TABLESCreate_tmp_table_priv server administration
EXECUTEExecute_privserver administration
FILEFile_privfile access on server
LOCK TABLESLock_tables_privserver administration
PROCESSProcess_privserver administration
RELOADReload_privserver administration
REPLICATION CLIENTRepl_client_privserver administration
REPLICATION SLAVERepl_slave_privserver administration
SHOW DATABASESShow_db_privserver administration
SHUTDOWNShutdown_privserver administration
SUPERSuper_privserver administration

The SELECT, INSERT, UPDATE, and DELETE privileges allow you to perform operations on rows in existing tables in a database.

SELECT statements require the SELECT privilege only if they actually retrieve rows from a table. You can execute certain SELECT statements even without permission to access any of the databases on the server. For example, you could use the mysql client as a simple calculator: 

mysql> SELECT 1+1;
mysql> SELECT PI()*2;

The INDEX privilege allows you to create or drop (remove) indexes.

The ALTER privilege allows you to use ALTER TABLE.

The CREATE and DROP privileges allow you to create new databases and tables, or to drop (remove) existing databases and tables.

Note that if you grant the DROP privilege for the mysql database to a user, that user can drop the database in which the MySQL access privileges are stored!

The GRANT privilege allows you to give to other users those privileges you yourself possess.

The FILE privilege gives you permission to read and write files on the server using the LOAD DATA INFILE and SELECT ... INTO OUTFILE statements. Any user to whom this privilege is granted can read any world readable file accessable by the MySQL server and create a new world readable file in any directory where the MySQL server can write. The user can also read any file in the current database directory. The user can however not change any existing file.

The remaining privileges are used for administrative operations, which are performed using the mysqladmin program.

It is a good idea in general to grant privileges only to those users who need them, but you should exercise particular caution in granting certain privileges:

  • The GRANT privilege allows users to give away their privileges to other users. Two users with different privileges and with the GRANT privilege are able to combine privileges.
  • The ALTER privilege may be used to subvert the privilege system by renaming tables.
  • The FILE privilege can be abused to read any world-readable file on the server or any file in the current database directory on the server into a database table, the contents of which can then be accessed using SELECT.
  • The SHUTDOWN privilege can be abused to deny service to other users entirely, by terminating the server.
  • The PROCESS privilege can be used to view the plain text of currently executing queries, including queries that set or change passwords.
  • Privileges on the mysql database can be used to change passwords and other access privilege information. (Passwords are stored encrypted, so a malicious user cannot simply read them to know the plain text password.) If they can access the mysql.user password column, they can use it to log into the MySQL server for the given user. (With sufficient privileges, the same user can replace a password with a different one.)

 

Backup

Because MySQL tables are stored as files, it is easy to do a backup. To get a consistent backup, do a LOCK TABLES on the relevant tables followed by FLUSH TABLES for the tables. The FLUSH TABLE is needed to ensure that the all active index pages is written to disk before you start the backup.

If you want to make an SQL level backup of a table, you can use SELECT INTO OUTFILE or BACKUP TABLE. Starting from 3.23.56 and 4.0.12 BACKUP TABLE will not allow you to overwrite existing files as this would be a security risk.

BACKUP TABLE tbl_name[,tbl_name...] TO '/path/to/backup/directory'
Copies to the backup directory the minimum number of table files needed to restore the table, after flushing any buffered changes to disk. Currently works only for MyISAM tables. For MyISAM tables, copies `.frm' (definition) and `.MYD' (data) files. The index file can be rebuilt from those two.

During the backup, a read lock will be held for each table, one at time, as they are being backed up. If you want to back up several tables as a snapshot, you must first issue LOCK TABLES obtaining a read lock for each table in the group.

To restore a table :

RESTORE TABLE tbl_name[,tbl_name...] FROM '/path/to/backup/directory'
Restores the table(s) from the backup that was made with BACKUP TABLE. Existing tables will not be overwritten; if you try to restore over an existing table, you will get an error. Restoring will take longer than backing up due to the need to rebuild the index. The more keys you have, the longer it will take. Just as BACKUP TABLE, RESTORE TABLE currently works only for MyISAM tables.

If you have shell-access, you might prefer to use the mysqldump program or the mysqlhotcopy script. 

shell> mysqldump --tab=/path/to/some/dir --opt db_name
or 
shell> mysqlhotcopy db_name /path/to/some/dir
You can also simply copy all table files (`*.frm', `*.MYD', and `*.MYI' files) as long as the server isn't updating anything. The script mysqlhotcopy does use this method. (But note that these methods will not work if your database contains InnoDB tables. InnoDB does not store table contents in database directories, and mysqlhotcopy works only for MyISAM and ISAM tables.)

 

In cooperation with SecurityHome.eu

 

TOP

Security: