HomeMySQL Page 5 - Security and More in MySQL Databases
4.3.2 Making MySQL Secure Against Attackers - MySQL
If you need to administer MySQL, this article gets you off to a good start. In this section, we discuss system and other variables, then begin to look at general security issues. The second of a multi-part series, it is excerpted from chapter four of the book MySQL Administrator's Guide, written by Paul Dubois (Sams; ISBN: 0672326345).
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 using an older version of MySQL, or are still using pre-4.1.1-style passwords, the encryption algorithm is less strong and with some effort a clever attacker who can sniff the traffic between the client and the server can crack the password. (See Section 4.4.9, "Password Hashing in MySQL 4.1," for a discussion of the different password handling methods.) 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 3.22 and above) to make traffic much more difficult to decipher. To make the connection even more secure, you should 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/.
If you are using MySQL 4.0 or newer, you can also use internal OpenSSL support. See Section 4.5.7, "Using Secure Connections."
To make a MySQL system secure, you should strongly consider the following suggestions:
Use passwords for all MySQL users. 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 other_userdb_name if other_user has no password. If all users have a password, connecting using another user's account becomes much more difficult.
To change the password for a user, use the SET PASSWORD statement. It is also possible to update the user table in the mysql database directly. For example, to change the password of all MySQL accounts that have a username of root, do this:
shell> mysql -u root
mysql> UPDATE mysql.user SET
Password=PASSWORD('newpwd') -> WHERE User='root';
mysql> FLUSH PRIVILEGES;
Don't run the MySQL server 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 refuses to run as root unless that is specified explicitly using a --user=root option.
mysqld can be run as an ordinary unprivileged user instead. You can also create a separate Unix account named mysql to make everything even more secure. Use the account only for administering MySQL. To start mysqld as another Unix user, add a user option that specifies the username 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:
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 A.3.2, "How to Run MySQL as a Normal User."
Running mysql 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.
Don't allow the use of symlinks to tables. (This 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 184.108.40.206, "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.
Don't grant the PROCESS or SUPER privilege to non-administrative 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 SUPER privilege (PROCESS before MySQL 4.0.2), 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.
Don't 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 will 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 don't 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.
4.3.3 Startup Options for mysqld Concerning Security
The following mysqld options affect security:
If you start the server with --local-infile=0, clients cannot use LOCAL in LOAD DATA statements. See Section 4.3.4, "Security Issues with LOAD DATA LOCAL."
With this option, the SHOW DATABASES statement displays the names of only those databases for which the user has some kind of privilege. As of MySQL 4.0.2, this option is deprecated and doesn't do anything (it is enabled by default), because there is now a SHOW DATABASES privilege that can be used to control access to database names on a per-account basis.
If this is enabled, a user cannot create new users with 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 with those privileges that the user has right to grant, you should grant the user the following privilege:
mysql> GRANT INSERT(user) ON mysql.user TO
This will ensure that the user can't 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. This option is available as of MySQL 4.1.1.
This option causes the server not to use the privilege system at all. This gives everyone fullaccess to all databases! (You can tell a running server to start using the grant tables again by executing a mysqladmin flush-privileges or mysqladmin reload command, or by issuing a FLUSH PRIVILEGES statement.)
Hostnames are not resolved. All Host column values in the grant tables must be IP numbers or localhost.
Don't allow TCP/IP connections over the network. All connections to mysqld must be made via Unix socket files. This option is unsuitable when using a MySQL version prior to 3.23.27 with the MIT-pthreads package, because Unix socket files were not supported by MIT-pthreads at that time.
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.