Sams Teach Yourself PHP, MySQL and Apache All in One (2012)
Part I. Getting Up and Running
Chapter 2. Installing and Configuring MySQL
In this chapter, you learn the following:
• How to install MySQL
• Basic security guidelines for running MySQL
• How to work with the MySQL user privilege system
This is the first of three installation-related chapters in which you learn how to set up your development environment. We tackle the MySQL installation first because on some systems building the PHP module requires bits of the MySQL installation to be complete if you’re going to use MySQL with PHP.
Current and Future Versions of MySQL
The installation instructions in this chapter refer to MySQL Community Server 5.5.21, the current production version of the software. This version number can be read as “revision number 21 of minor release 5, of the major version 5 of the MySQL server software.” Revisions and minor releases do not follow a set release schedule. When enhancements or fixes are added to the code and thoroughly tested, a new version is released with a new revision or minor version number.
By the time you purchase this book, the version number might have changed to 5.5.22 or later. If so, read the list at http://dev.mysql.com/doc/refman/5.5/en/news-5-5-x.html for any installation/configuration process changes. These processes make up the bulk of this chapter.
Although it is unlikely that any installation instructions will change between minor version updates, always check the changelog of software that you install and maintain. If a minor version change does occur while you are reading this book but the changelog notes no installation changes, just make a mental note and substitute the new version number wherever it appears in the installation instructions and accompanying figures.
How to Get MySQL
MySQL AB was the name of the company that developed, maintained, and distributed the MySQL database server; through a series of acquisitions (Sun Microsystems purchased MySQL AB, and Oracle Corporation purchased Sun Microsystems), database giant Oracle now owns MySQL. However, the MySQL Community Edition of the software remains open source, is supported by open source developers, and is freely available on the MySQL website at http://www.mysql.com/. Binary distributions for all platforms, installer packages for Mac OS X, and RPMs and source code files for Linux/UNIX platforms are all available.
Note
Linux and Mac OS X distributions usually contain some version or another of the open source MySQL software, although these are usually several revision or minor versions behind the current release.
The installation instructions in this chapter are based on the official MySQL 5.5.x Community Server distribution. You can download all files from http://dev.mysql.com/downloads/mysql/5.5.html, and you can find all the current versions as of the time of this writing on the CD included with this book.
Installing MySQL on Linux/UNIX
The process of installing MySQL on Linux/UNIX is straightforward, whether you use RPMs or install the binaries. If you are installing from RPMs, there are platform-specific RPMs such as those for SuSE and generic Linux running on different processor types, such as x86 32- and 64-bit.
For a minimal installation from RPMs, you need two files from the downloads page at http://dev.mysql.com/downloads/mysql/5.5.html (or obtained from the CD-ROM that accompanies this book):
• MySQL-server-type-VERSIONNUMBER.PLATFORM.rpm—The MySQL server
• MySQL-client-type-VERSIONNUMBER.PLATFORM.rpm—The standard MySQL client libraries
To perform a minimal installation from RPMs, type the following at your prompt:
# rpm -i MySQL-server-VERSION.i386.rpm MySQL-client-VERSION.i386.rpm
Note
Replace VERSIONNUMBER in the filename with the actual version you downloaded, and PLATFORM with the short name of the platform you are using. For example, the current MySQL 5.5 server RPM for generic Linux distributions is MySQL-server-5.5.21-1.linux2.6.i386.rpm, and the client libraries RPM is MySQL-client-5.5.21-1.linux2.6.i386.rpm.
For an installation from Debian packages, you need the *.deb file from the downloads page at http://dev.mysql.com/downloads/mysql/5.5.html (or obtained from the CD-ROM that accompanies this book). Then type the following at your prompt:
# dpkg -i mysql-VERSION-debian6.0-i686.deb
Another painless (and very common) installation method is to install MySQL from a binary distribution. This method requires the gunzip and tar utilities to uncompress and unpack the distribution, and also requires the ability to create groups and users on the system. The first series of commands in the binary distribution installation process has you adding a group and a user and unpacking the distribution, as follows:
# groupadd mysql
# useradd –r -g mysql mysql
# cd /usr/local
# tar zxvf /path/to/mysql-VERSION-PLATFORM.tar.gz
Note
Replace VERSION-PLATFORM in the filename with the actual version you downloaded. For example, the current MySQL 5.5 generic Linux binary is mysql-5.5.21-1-linux2.6.i386.tar.
Next, the instructions tell you to create a symbolic link with a shorter name:
# ln -s mysql-VERSION-PLATFORM mysql
# cd mysql
After you unpack them, the README and INSTALL files walk you through the remainder of the installation process for the version of MySQL you’ve chosen. In general, the following series of commands are used:
# scripts/mysql_install_db --user=mysql
# chown -R root .
# chown -R mysql mysql_data
# chgrp -R mysql .
# bin/mysqld_safe --user=mysql &
You’re now ready to start the MySQL server, so skip ahead in this chapter to the “Basic Security Guidelines” section to learn how to add passwords and users. If you experienced any issues with your installation, check the “Troubleshooting Your Installation” section.
Installing MySQL on Mac OS X
The MySQL installation process for Mac OS X is fairly straightforward—there is an installation package for Mac OS X. Go to the MySQL downloads page at http://dev.mysql.com/downloads/mysql/5.5.html and select Mac OS X from the drop-down list (or use the files from the CD-ROM that accompanies this book).
If you are going to the website, be sure to download the appropriate DMG archive for your system: either 10.5 or 10.6, and either 32- or 64-bit.
When you have the file, double-click the DMG archive. After you open the DMG archive, you will see a folder with a few files in it, as shown in Figure 2.1.
Figure 2.1 Showing the contents of the MySQL DMG archive.
Double-click the *.pkg file in that folder and follow these installation steps to complete the process:
1. The MySQL installer launches automatically, as shown in Figure 2.2. Click Continue to move to the next step.
Figure 2.2 The MySQL Installer for the Mac has started.
2. The next few screens contain general information regarding installation and the MySQL license. Read these screens and click Continue to move through them.
3. After stepping through the information and licensing screens, you must select an installation destination. Select the appropriate drive, as shown in Figure 2.3, and then click the Continue button.
Figure 2.3 Select an installation location.
4. The next screen verifies your installation location selection and requires you to click the Install button to continue. At this point, you might be prompted to enter the administrator username and password before the installation process continues. When it continues, let the process run until you see that the installation is complete, as shown in Figure 2.4.
Figure 2.4 MySQL has been installed.
Also included in the installation package is the MySQL Startup Item installer. If you want MySQL to start automatically at system startup, install this additional package. Installation of the MySQL Startup Item follows the standard installation method just described: Double-click the *.pkgfile, select a destination disk, and allow the installation process to run to completion. After installing the MySQL Startup Item, use the following command in a terminal window to start MySQL:
# sudo /Library/StartupItems/MySQLCOM/MySQLCOM start
When attempting to start MySQL, you might be asked to enter your administrator password. After you do so, you can press Ctrl+D to exit the shell once MySQL has started.
After completing the installation steps in this section, you can skip to the “Basic Security Guidelines” section later in this chapter. If you experienced any issues with your installation, check the “Troubleshooting Your Installation” section.
Installing MySQL on Windows
The MySQL installation process on Windows uses a standard Microsoft Windows Installer (MSI) file to walk you through the installation and configuration of MySQL on your Windows XP, Windows Server 2003, Windows Vista, or Windows 7 machine.
Go to the MySQL downloads page at http://dev.mysql.com/downloads/mysql/5.5.html and select the Windows option from the drop-down menu. Download the Windows MSI Installer file for your system, either 32- or 64-bit. When this file has been downloaded (or copied from the CD-ROM for this book), double-click it to begin the installation process.
The following steps detail the installation of MySQL 5.5.21 on Windows 7; the installation sequence follows the same steps regardless of your Windows environment.
Note
A ZIP Archive version is also available for Windows users. If you want to install the ZIP Archive version, be sure to read the descriptions and instructions in the MySQL Manual at http://dev.mysql.com/doc/refman/5.5/en/windows-choosing-package.html.
Jumping right into the installation sequence, just follow these steps:
1. Double-click the *.msi file to begin the installation sequence. You will see the first screen of the MySQL Setup Wizard, as shown in Figure 2.5. Click Next to continue.
Figure 2.5 The first step of the MySQL Setup Wizard for Windows.
2. After agreeing to the terms and conditions, you are asked to choose a setup type—Typical, Custom, or Complete (see Figure 2.6). The Custom option allows you to pick and choose elements of MySQL to install, whereas the Complete option installs all the components of MySQL, which range from documentation to benchmarking suites. The Typical installation method is suitable for most users because it includes the client, server, and numerous tools for general management of your MySQL installation. Select Typical as the installation method and click Next to continue.
Figure 2.6 Select an installation type.
3. Confirm your choice in the next screen and click the Install button to continue. The installation process takes over and installs files in their proper locations.
4. When the installation is complete, you have the option of continuing to the MySQL Instance Configuration Wizard. This wizard is highly recommended because it creates a custom my.ini file tailored to your particular needs. To continue to the MySQL Instance Configuration Wizard, check the Launch the MySQL Instance Configuration Wizard check box and click the Finish button, as shown in Figure 2.7.
Figure 2.7 MySQL has been installed. Now continue to the MySQL Instance Configuration Wizard.
5. When you see the MySQL Instance Configuration Wizard Welcome screen, click the Next button to go to the next step in the wizard. You will see two options for server configuration: Detailed and Standard. We use the Detailed Configuration option so that you can see all the options available to you. If you decide to select the Standard Configuration option, you must manually modify the resulting my.ini file to achieve the configuration you want. Select the Detailed Configuration radio button, and then click Next to continue.
6. The next selection you must make is shown in Figure 2.8. In this step, you select the type of machine you are running: Developer Machine, Server Machine, or Dedicated MySQL Server Machine. Your selection on this screen determines the allotments for memory, disk, and processor usage. If you are using MySQL on your personal machine for testing purposes, select the Developer Machine option. If MySQL is running on a machine with other server software and can take up more system resources than if you were running it on your personal machine, select the Server Machine option. Select the Dedicated MySQL Server Machine option if MySQL is the primary service running on the machine and can take up the bulk of the system resources. After making your selection, click Next to continue.
Figure 2.8 Select your server type as part of the MySQL configuration.
7. The next configuration option pertains to database usage. The options are Multifunctional Database, for both InnoDB and MyISAM storage engines, with resources split evenly between the two; Transactional Database, which enables both InnoDB and MyISAM, but dedicates the most server resources to InnoDB; and Non-Transactional Database Only, which disables InnoDB and applies all resources to MyISAM. Unless you know exactly what your database will be used for, select the Multifunctional Database radio button and click Next to continue.
8. If you have selected a database usage option that includes the InnoDB storage engine, the next step in the configuration process enables you to configure the disk location and storage thresholds. The defaults are shown in Figure 2.9, which you can simply confirm by clicking Next to continue, or you can change these settings and then click Next to continue with your custom settings in place.
Figure 2.9 Tune the disk usage options for the InnoDB storage engine.
9. The next configuration option determines the number of concurrent connections to your MySQL server. Your setting will depend on the amount of traffic and database usage by your website or application. The default setting is Decision Support (DSS)/OLAP, which has a maximum number of 100 concurrent connections with an average of 20 assumed. The Online Transaction Processing (OLTP) option has a maximum of 500 concurrent connections, and the Manual setting allows you to select a number from a drop-down list or enter your own. Make your selection and click Next to continue.
10. The Networking Options screen is next in the configuration sequence. Here you enable or disable TCP/IP networking and configure the port number used to connect to MySQL—the default is 3306, but you can use any unused port you choose. The other option on this screen is to enable or disable strict mode; enabling strict mode is recommended unless you know what you’re changing. See http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html for more information. Make your selections and click Next to continue.
Note
Remember to modify your firewall rules to allow traffic to flow on port 3306 or whichever port you decide to use for MySQL.
11. After the Networking Options screen come the character set options. The default option is Standard Character Set, which results in Latin1 being used throughout your database. You can also select the Best Support for Multilingualism option, which results in UTF8 as the character set; UTF8 allows you to store multiple languages in a single character set. If you want to use a specific character set, select the Manual Selected Default Character Set radio button, and then select the appropriate character set from the drop-down list. After making your selection, click Next to continue.
12. It is recommended that MySQL be installed as a service. Check the Install as Windows Service check box and select a name for the service. Optionally, check the Launch the MySQL Server Automatically check box. You also have the option of adding the MySQL bin directory to your Windows PATH for easier invocation of MySQL from the cmd prompt; check the box if this is appropriate for you. When you have completed your selections, click Next to continue.
13. The Security Options configuration screen is the most important screen of all. As shown in Figure 2.10, use this configuration screen to set a root password. Enter the password twice to confirm it. Do not check the Enable Root Access from Remote Machines check box unless you really know what you’re doing; typically, root connections are allowed only from localhost. In addition, you can create an anonymous account, but doing so is not recommended for security reasons. After completing the configuration options in this screen, click Next to continue.
Figure 2.10 Creating a password for root during MySQL configuration.
14. One step remains in the configuration sequence, and that is to click the Execute button to start the process. After the wizard has made it through the various configuration steps, you will see a confirmation screen, as shown in Figure 2.11, indicating the configuration file has been created and the MySQL service has been started. Click Finish to close the wizard.
Figure 2.11 The MySQL Configuration Wizard completes its tasks.
The completion of the installation and configuration wizards results in a running MySQL service and a custom my.ini file in the C:\Program Files\MySQL\MySQL Server 5.5\ directory.
Tip
You can manually edit the my.ini file with any text editor. After making changes, you must restart your MySQL server.
Now that MySQL has been started, skip to the “Basic Security Guidelines” section later in this chapter. If you experienced any issues with your installation, check the “Troubleshooting Your Installation” section.
Troubleshooting Your Installation
If you have any problems during the installation of MySQL, the first place you should look is Appendix A, “Problems and Common Errors,” in the MySQL Manual, which is located at http://dev.mysql.com/doc/refman/5.5/en/problems.html.
The following are just a few of the common installation problems:
• On Linux/UNIX and Mac OS X, incorrect permissions do not allow you to start the MySQL daemon. If this is the case, be sure that you have changed owners and groups to match those indicated in the installation instructions.
• If you see the message Access denied when connecting to MySQL, be sure that you are using the correct username and password.
• If you see the message Can't connect to server, make sure that the MySQL daemon is running.
If you still have trouble after reading Appendix A in the MySQL Manual, sending an email to the MySQL mailing list (see http://lists.mysql.com/ for more information) will likely produce results. You can also purchase support contracts from MySQL AB.
Basic Security Guidelines
Regardless of whether you are running MySQL on Windows, Linux/UNIX, or Mac OS X, and no matter whether you administer your own server or use a system provided to you by your Internet service provider, you must understand basic security guidelines. If you are accessing MySQL through your Internet service provider, there are several aspects of server security that you, as a non-root user, should not be able to modify or circumvent. Unfortunately, many Internet service providers pay no mind to security guidelines, leaving their clients exposed—and for the most part, unaware of the risk.
Starting MySQL
Securing MySQL begins with the server startup procedure. If you are not the administrator of the server, you cannot change this, but you can certainly check it out and report vulnerabilities to your Internet service provider.
If your MySQL installation is on Linux/UNIX or Mac OS X, your primary concern should be the owner of the MySQL daemon—it should not be root. Running the daemon as a non-root user such as mysql or database limits the ability of malicious individuals to gain access to the server and overwrite files.
Tip
You can verify the owner of the process using the ps (process status) command on your Linux/UNIX or Mac OS X system.
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.