Chapter 12. Security Issues

Information stored in MySQL databases must be kept secure to avoid exposing data that MySQL users expect to be private. Risks to a MySQL installation come in several forms, but they can be grouped into two general categories:

• Filesystem security risks. Database information is stored in directories and files, and the server also maintains log files that contain information about queries that clients execute. Because these directories and files are part of the filesystem, they need to be protected so that other users who have login accounts on the server host cannot access them directly. A MySQL installation also includes the programs and scripts used to manage and access databases. Users need to be able to run some of these (such as the client programs), but should not be able to modify or replace them. This means that MySQL programs need to be protected appropriately as well.

• Network security risks. The MySQL server provides access to databases by allowing clients to connect and make requests. Information about client accounts is stored in the mysql database. Each account should be set up with privileges that provide access only to the data the accounts needs to see or modify. Accounts also should be assigned passwords to make it difficult for people to connect to the server using someone else's account. For example, a MySQL root account has full privileges to perform any database operation, so it's important to assign the account a password that is not easily guessed.

This chapter describes several steps that an administrator can take to maintain the integrity of a MySQL installation. It covers the following topics:

• Basic security procedures, both for the filesystem and for the initial MySQL accounts. These procedures prevent users with login accounts on the server host from attacking MySQL directories or files. They also prevent clients from connecting to the MySQL server without a password.

• How to set up MySQL accounts. This includes a discussion of the types of privileges available, and how the GRANT and REVOKE statements work.

• How the server uses the information in the grant tables to manage access control when clients connect.

Questions on the material in this chapter make up approximately 15% of the exam.

Note that 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.