Sams Teach Yourself PHP, MySQL and Apache All in One (2012)

---

If you see that MySQL is running as root on your system, immediately contact your Internet service provider and complain. If you are the server administrator, you should start the MySQL process as a non-root user or specify the preferred username in the startup command line:

# mysqld --user=non_root_user_name

For example, if you want to run MySQL as user mysql, use this command:

# mysqld --user=mysql

However, the recommended method for starting MySQL is through the mysqld_safe startup script in the bin directory of your MySQL installation:

# bin/mysqld_safe --user=mysql &

Securing Your MySQL Connection

You can connect to the MySQL monitor (command-line interface) or other MySQL applications in several different ways, each of which has its own security risks. If your MySQL installation is on your own workstation, you have less to worry about than users who have to use a network connection to reach their server.

If MySQL is installed on your workstation, your biggest security concern is leaving your workstation unattended with your MySQL monitor or MySQL GUI administration tool up and running. In this type of situation, anyone can walk over and delete data, insert bogus data, or shut down the server. Use a screensaver or lock-screen mechanism with a password if you must leave your workstation unattended in a public area.

If MySQL is installed on a server outside your network, the security of the connection should be of some concern. As with any transmission of data over the Internet, data can be intercepted. If the transmission is unencrypted, the person who intercepted the data can piece it together and use the information. Suppose that the unencrypted transmission is your MySQL login information; a rogue individual now has access to your database, masquerading as you.

One way to prevent this from happening is to connect to MySQL through a secure connection such as Secure Shell (SSH), through which all transmissions to and from the remote machine are encrypted. Similarly, if you use a web-based administration interface, such as the highly recommended phpMyAdmin (see http://www.phpmyadmin.net/ for more information, and note that phpMyAdmin is installed as part of the XAMPP-based quick-start installation in Chapter 1, “Installation QuickStart Guide with XAMPP”) or another tool used by your Internet service provider, access that tool over a secure HTTP connection.

In the next section, you learn about the MySQL privilege system, which helps secure your database even further.

Introducing the MySQL Privilege System

The MySQL privilege system is always on. The first time you try to connect, and for each subsequent action, MySQL checks the following three things:

• Where you are accessing from (your host)

• Who you say you are (your username and password)

• What you’re allowed to do (your command privileges)

All this information is stored in the database called mysql, which is automatically created when MySQL is installed. There are several privilege-related tables in the mysql database, such as the following:

• columns_priv—Defines user privileges for specific fields within a table

• db—Defines the permissions for all databases on the server

• host—Defines the acceptable hosts that can connect to a specific database

• procs_priv—Defines user privileges for stored routines

• tables_priv—Defines user privileges for specific tables within a database

• user—Defines the command privileges for a specific user

These tables will become more important to you later in this chapter as you add a few users to MySQL. For now, just remember that these tables exist and must have relevant data in them for users to complete actions.

Understanding the Two-Step Authentication Process

As you’ve learned, MySQL checks three things during the authentication process. The actions associated with these three things are performed in two steps:

1. MySQL looks at the host you are connecting from and the username and password pair you are using. If your host is allowed to connect, your password is correct for your username, and the username matches one assigned to the host, MySQL moves to the second step.

2. For whichever SQL command you are attempting to use, MySQL verifies that your user has permissions to perform that action for that database, table, and field.

If step 1 fails, you see an error about it and you cannot continue on to step 2. For example, suppose that you are connecting to MySQL with a username of joe and a password of abc123 and you want to access a database called myDB. You will receive an error message if any of those connection variables is incorrect for any of the following reasons:

• Your password is incorrect.

• Username joe doesn’t exist.

• User joe can’t connect from localhost.

• User joe can connect from localhost but cannot use the myDB database.

You may see an error like the following:

# mysql -h localhost -u joe -pabc123 test
Error 1045: Access denied for user: 'joe@localhost' (Using password: YES)

If user joe with a password of abc123 is allowed to connect from localhost to the myDB database, MySQL checks the actions that joe can perform in step 2 of the process. For our purposes, suppose that joe is allowed to select data but is not allowed to insert data. The sequence of events and errors would look like the following:

# mysql -h localhost -u joe -pabc123 test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 5.5.21-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT * FROM test_table;
+----+------------+
| id | test_field |
+----+------------+
| 1 | blah |
| 2 | blah blah |
+----+------------+
2 rows in set (0.0 sec)

mysql> INSERT INTO test_table VALUES ('', 'my text');
Error 1044: Access denied for user: 'joe@localhost' (Using password: YES)

Action-based permissions are common in applications with several levels of administration. For example, if you have created an application containing personal financial data, you might grant only SELECT privileges to entry-level staff members, but INSERT and DELETE privileges to executive-level staff with security clearances.

In most cases when you are accessing MySQL through an Internet service provider, you have only one user and one database available to you. By default, that user has access to all tables in that database and is allowed to perform all commands. In this case, the responsibility is yours as the developer to create a secure application through your programming.

However, if you are the administrator of your own server, or if your Internet service provider allows you to add as many databases and users as you want and to modify the access privileges of your users, you can do so as described in the following subsections.

Adding Users to MySQL

Administering your server through a third-party application might afford you a simple method for adding users by using a wizard-like process or a graphical interface. However, adding users through the MySQL monitor is not difficult, especially if you understand the security checkpoints used by MySQL, which you just learned.

The simplest method for adding new users is the GRANT command. By connecting to MySQL as the root user, you can issue one command to set up a new user. The other method is to issue INSERT statements into all the relevant tables in the mysql database, which requires you to know all the fields in the tables used to store permissions. This method works just as well but is more complicated than the simple GRANT command. The simple syntax of the GRANT command is shown here:

GRANT privileges
ON databasename.tablename
TO username@host
IDENTIFIED BY "password";

Following are some of the common privileges you can grant. (For a complete list, see the GRANT entry in the MySQL Manual at http://dev.mysql.com/doc/refman/5.5/en/grant.html.)

• ALL—Gives the user all common privileges.

• ALTER—User can alter (modify) tables, columns, and indexes.

• CREATE—User can create databases and tables.

• DELETE—User can delete records from tables.

• DROP—User can drop (delete) tables and databases.

• FILE—User can read and write files; this privilege is used to import or dump data.

• INDEX—User can add or delete indexes.

• INSERT—User can add records to tables.

• PROCESS—User can view and stop system processes; only trusted users should be able to do this.

• RELOAD—User can issue FLUSH statements; only trusted users should be able to do this.

• SELECT—User can select records from tables.

• SHUTDOWN—User can shut down the MySQL server; only trusted users should be able to do this.

• UPDATE—User can update (modify) records in tables.

If, for instance, you want to create a user called john with a password of 99hjc!5, with SELECT and INSERT privileges on all tables in the database called myDB, and you want this user to be able to connect from any host, use this command:

GRANT SELECT, INSERT
ON myDB.*
TO john@"%"
IDENTIFIED BY "99hjc!5";

Note the use of two wildcards: * and %. These wildcards replace values. In this example, * replaces the entire list of tables, and % replaces a list of all hosts in the known world—a very long list indeed.

Here’s another example of adding a user with the GRANT command, this time to add a user called jane with a password of 45sdg11, with ALL privileges on a table called employees in the database called myCompany. This new user can connect only from a specific host:

GRANT ALL
ON myCompany.employees
TO jane@janescomputer.company.com
IDENTIFIED BY "45sdg11";

If you know that janescomputer.company.com has an IP address of 63.124.45.2, you can substitute that address in the hostname portion of the command, as follows:

GRANT ALL
ON myCompany.employees
TO jane@janescomputer.company.com
IDENTIFIED BY "45sdg11";

One note about adding users: Always use a password and make sure that the password is a good one!

If you use the GRANT command to add users, the changes take immediate effect. To make absolutely sure of this, you can issue the FLUSH PRIVILEGES command in the MySQL monitor to reload the privilege tables.

Removing User Privileges

Removing privileges is as simple as adding them; instead of the GRANT command, you use REVOKE. The REVOKE command syntax is as follows:

REVOKE privileges
ON databasename.tablename
FROM username@hostname;

In the same way that you can grant permissions using INSERT commands, you can also revoke permissions by issuing DELETE commands to remove records from tables in the mysql database. However, this requires that you be familiar with the fields and tables, and it is much easier and safer to use REVOKE.

To revoke the ability for user john to INSERT items in the myCompany database, you issue this REVOKE statement:

REVOKE privileges
ON databasename.tablename
FROM username@hostname;

Changes made to the data in the privilege tables happen immediately, but for the server to be aware of your changes, issue the FLUSH PRIVILEGES command in the MySQL monitor.

Summary

Thanks to a wizard-based installation method, installing MySQL on Windows and Mac OS X is a simple process. Linux/UNIX users do not have a wizard-based installation process, but it is not difficult to follow a simple set of commands to unpack the MySQL client and server binaries. Linux/UNIX users can also use RPMs for installation.

Security is always a priority, and you can take several steps to ensure a safe and secure installation of MySQL. Even if you are not the administrator of the server, you should be able to recognize security breaches and raise a ruckus with the server administrator.

The MySQL server should never run as the root user. In addition, named users within MySQL should always have a password, and their access privileges should be well defined.

MySQL uses the privilege tables in a two-step process for each request that is made. MySQL needs to know who you are and where you are connecting from, and each piece of this information must match an entry in its privilege tables. Also, the user whose identity you are using must have specific permission to perform the type of request you are making.

You can add user privileges using the GRANT command, which uses a simple syntax to add entries to the user table in the mysql database. The REVOKE command, which is equally simple, is used to remove those privileges.

Q&A

Q. How do I completely remove a user? The REVOKE command just eliminates the privileges.

A. To completely remove a user from the privilege table, you have to issue a specific DELETE query from the user table in the mysql database.

Q. What if I tell my Internet service provider to stop running MySQL as root and it won’t?

A. Switch providers. If your Internet service provider doesn’t recognize the risks of running something as important as your database as the root user and doesn’t listen to your request, find another provider. There are providers with plans as low as $2.95/month (or even free) that do not run important processes as the root user.

Workshop

The workshop is designed to help you review what you’ve learned and begin putting your knowledge into practice.

Quiz

1. True or false: SSH is a perfectly acceptable method to securely connect to MySQL from a remote host.

2. Which three pieces of information does MySQL check each time a request is made?

3. What command would you use to grant SELECT, INSERT, and UPDATE privileges to a user named bill on localhost to all tables on the BillDB database? Also, what piece of information is missing from this statement that is recommended for security purposes?

Answers

1. True. SSH encrypts data between hosts and therefore enables you to securely connect to your server.

2. Who you are, where you are accessing from, and what actions you’re allowed to perform.

3. The command is as follows:

GRANT SELECT, INSERT, UPDATE
ON BillDB.*
TO bill@localhost;

The important missing piece is a password for the user.

Activities

1. Think of situations in which you might want to restrict command access at the table level. For example, you wouldn’t want the intern-level administrator to have shutdown privileges for the corporate database.

2. If you have administrative privileges in MySQL, issue several GRANT commands to create dummy users. It doesn’t matter whether the tables and databases you name are actually present.

3. Use REVOKE to remove some of the privileges of the users you created in activity 2.