35.5. Network Security


MySQL Server operates in the client/server environment and thus provides an inherently network-oriented service. It's important to make sure that only authorized clients can connect to the server to access its databases. You should make sure that MySQL accounts are protected with passwords and do not have unnecessary privileges. You may also want to consider limiting the network interfaces used by the server.

35.5.1. Securing the Initial MySQL Accounts

MySQL Server controls client access using the mysql database, which contains the grant tables. Privileges listed in the grant tables are tied to accounts, each of which is defined by a username and a hostname. That is, a MySQL account depends not only on your username, but the client host from which you connect to the server.

Note: Usernames and passwords for MySQL accounts are unrelated to those for system login accounts. For example, on Unix, your login name need not be the same as the name that you use to identify yourself when connecting to the MySQL server.

The MySQL installation procedure sets up one or more initial accounts in the grant tables. By default, these accounts have no passwords at first. You should assign passwords to these accounts, particularly any that have administrative privileges, so that unauthorized clients cannot connect to the server and gain control over it. This is true no matter what platform you install MySQL Server on, whether Windows or Unix. Any unneeded accounts should be removed.

At a minimum, there will be an account for root@localhost. On Unix, there is also an account for root@host_name, where host_name is the name of the server host. On Windows, depending on your installation method, there may also be a root@% account. root accounts have full access to the server's capabilities.

The grant tables may also contain anonymous-user accounts that have a blank username and that can be used by anyone.

On Windows, if you install MySQL using a distribution that includes the Configuration Wizard, the wizard presents a dialog containing check boxes that enable you to specify that root can connect only from the local host, and that anonymous accounts are to be created. You should check the first box and leave the second unchecked. (The Configuration Wizard also gives you the option of specifying the root password. If you use the wizard to set this password, you don't need to do so using the following instructions.)

The initial MySQL accounts have no password by default. You should assign a password immediately to any root accounts to prevent other people from connecting to the server as root and gaining complete control over it. With regard to anonymous accounts, you could assign passwords to them as well, but the best security is achieved if you remove them. This study guide follows the latter course.

There are various ways to set up MySQL passwords:

  • Use the GRANT statement

  • Use the SET PASSWORD statement

  • Use the mysqladmin password command

  • Modify the grant tables directly with the UPDATE statement

Generally, it's preferable to use one of the first three methods, and to avoid modifying the grant tables directly. For example, after installing MySQL, a simple procedure to protect the root accounts by assigning them passwords is to use these two mysqladmin password commands, where rootpass represents the password and host_name is the hostname of your machine:

 shell> mysqladmin -u root password 'rootpass' shell> mysqladmin -u root -h host_name password 'rootpass' 

However, these commands will not take care of the anonymous accounts. The following procedure secures all the initial accounts by assigning a password to any root accounts and removing any anonymous accounts. The procedure also serves to demonstrate how modifying the grant tables directly can be useful.

1.

On the server host, connect to the server as the MySQL root user to access the grant tables in the mysql database. Initially, assuming that the root accounts have no password, you can connect as follows without specifying a password option:

 shell> mysql -u root mysql 

2.

Account names and passwords are stored in the user table of the mysql database. Modify any user table records for root to assign a password. The following statement represents this password as rootpass:

 mysql> UPDATE user SET Password = PASSWORD('rootpass')     -> WHERE User = 'root'; 

3.

On Windows, the user table may contain a record for a 'root'@'%' account that allows root connections from any host. To allow only local access by root, remove that account:

 mysql> DELETE FROM user mysql> WHERE User = 'root' AND Host = '%'; 

4.

To remove any anonymous accounts, use the following statements:

 mysql> DELETE FROM user WHERE User = ''; mysql> DELETE FROM db WHERE User = ''; 

5.

If you want to see what effect the preceding operations have on the user table, issue this statement:

 mysql> SELECT Host, User, Password FROM user; 

6.

Finally, flush the grant tables:

 mysql> FLUSH PRIVILEGES; 

The reason for flushing the grant tables is that the server makes access-control decisions based on in-memory copies of the grant tables. The FLUSH statement tells the server to create new in-memory copies from the on-disk tables that were changed by the previous steps. This is necessary because the procedure changes the grant tables directly using UPDATE and DELETE rather than with account-management statements such as SET PASSWORD. Only the latter statements cause the server to reload the grant statements automatically.

After setting the root password to rootpass, you'll need to supply that password whenever you connect to the server with a username of root.

On Unix, MySQL comes with a mysql_secure_installation script that can perform several helpful security-related operations on your installation. This script has the following capabilities:

  • Set a password for the root accounts.

  • Remove any remotely accessible root accounts. This improves security because it prevents the possibility of anyone connecting to the MySQL server as root from a remote host. The result is that anyone who wants to connect as root must first be able to log in on the server host, which provides an additional barrier against attack.

  • Remove the anonymous-user accounts.

  • Remove the test database. (If you remove the anonymous accounts, you might also want to remove the test database to which they have access.)

35.5.2. General Privilege Precautions

This section describes general precautions to observe when granting privileges to MySQL accounts.

  • Don't grant privileges for the mysql database. Giving a user the ability to modify the grant tables directly is equivalent to granting the user full rights to modify accounts in any manner whatsoever.

  • Be selective about granting administrative privileges. The following items describe some of the dangers of giving these privileges to non-administrative users:

    • The FILE privilege allows users to cause the MySQL server to read and write files in the server host filesystem.

    • The PROCESS privilege allows use of SHOW PROCESSLIST to see all client threads. Output from this statement shows the statements that clients are executing, which exposes data.

    • The SUPER privilege allows a client to kill other client connections or to change the runtime configuration of the server.

  • To prevent GRANT from creating new accounts unless an IDENTIFIED BY clause is given, enable the NO_AUTO_CREATE_USER SQL mode.

  • Make sure that all MySQL accounts have passwords. To find accounts that have no password, connect as the root user to the mysql database and issue this statement:

     mysql> SELECT Host, User FROM user WHERE Password = ''; 

35.5.2.1 Restricting the Server's Network Interfaces

If your clients all connect to the local server, it's not necessary to allow remotely initiated connections. To prevent remote clients from connecting at all, disable TCP/IP connections by starting the server with the --skip-networking option. With the TCP/IP interface disabled, clients can connect only from the local host, which means that the server must be started with at least one local interface enabled. For Unix servers, this is not an issue, because the Unix socket file is always available. On Windows, the local interfaces are shared memory and named pipes. However, these interfaces are not enabled by default, so you must turn on at least one of them. To enable shared-memory connections, start the server with the --shared-memory option. To enable named-pipe connections, you must use either the mysqld-nt or mysqld-max-nt server and start it with the --enable-named-pipe option.

If clients cannot connect via one of the non-TCP/IP interfaces, you can leave TCP/IP connections enabled, but restrict connections to use only the loopback interface, which has TCP/IP address 127.0.0.1. This is another way to prevent remote clients from connecting. It can be done by using the --bind-address option. For example, place the following lines in an option file:

 [mysqld] bind-address=127.0.0.1 

35.5.3. MySQL Cluster Network Security

If you run MySQL Cluster, all nodes in the cluster should be located on the same local network and protected behind a firewall. Communication between nodes must be fast, so placing them on the same local network allows them to be connected by high-speed media such as 100 Mbps or gigabit ethernet. However, communication is not encrypted because that requires extra processing power and lowers performance. Thus, the cluster nodes should be protected behind a firewall so that internode communication cannot be monitored.



MySQL 5 Certification Study Guide
MySQL 5.0 Certification Study Guide
ISBN: 0672328127
EAN: 2147483647
Year: 2006
Pages: 312

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net