Learning MySQL (2007)

Part II. Using MySQL

Chapter 9. Managing Users and Privileges

Learning MySQL, developing applications, and deploying finished software are tasks with very different security requirements. While you’re learning the basics—especially if you’re working on your own machine—it’s not usually critical if you accidentally remove databases or tables, change data, or don’t carefully limit access to the MySQL server and its databases. However, when you develop and maintain real applications, it’s crucial that you secure your server and databases against accidental or deliberate acts that can delete, change, or expose your data. Fortunately, using MySQL’s sophisticated user and privilege management tools, you can properly set up and secure access to your database server. This chapter shows you how.

In addition to setting up the MySQL server access privileges, you should separately ensure the physical security of your host computer and backup media, and proper configuration of permissions at the operating system level. We’ve explained some important aspects of this in Chapters 2 and10, and we’ll also look at this topic briefly in this chapter.

The MySQL server comes with the user root, who can do everything on the MySQL server, including creating and deleting users, databases, tables, indexes, and data. Up to this point, we’ve connected to the server under this superuser account, which is very convenient, but not very secure—remember the saying about how absolute power corrupts absolutely?

Most applications don’t need superuser privileges for day-to-day activities. You can define less powerful users who have only the privileges they need to get their jobs done. You may want to prevent users creating or changing indexes, tables, or databases. You may even want to prevent users doing more than simply running SELECT statements on a given database or even particular tables in a database.

For example, you could have a user allmusic who can perform any database operation on the music database, and the user partmusic who can read data from the music database but can’t change anything.

In this way, if the allmusic account is compromised, an attacker can at most delete the music database, but nothing else—and, of course, you would have backups, wouldn’t you?! Similarly, a manager creating a report of daily sales wouldn’t be able to accidentally—or deliberately—change any data.

It’s also a good idea to use less privileged accounts yourself wherever possible; if you log in as the MySQL root user for routine tasks, there’s a greater likelihood that an unauthorized user will somehow be able to gather enough information to access that account. You might even make a mistake and inadvertently damage your database.

In this chapter, we show you how to:

§ Understand MySQL privileges

§ Add, remove, and change MySQL users and passwords

§ GRANT and REVOKE privileges

§ Understand MySQL’s default security configuration

§ Devise a security policy for your MySQL server

§ Manage users and privileges using SQL queries

§ Limit server usage by user

Understanding Users and Privileges

MySQL, like most other database servers, has users who have privileges that determine whether they can create, modify, delete, and query databases, and also whether they can modify the privileges and control the server. In practice, this control can be coarse-grained—a user may be allowed or prevented from accessing the server—or fine-grained, where a user can access only some tables in a database or only some columns in a table. Some database servers support only coarse-grained control, while others such as MySQL allow both coarse-grained and fine-grained control over access.

MySQL allows you to control which users can access the server; the databases, tables, and columns on the server that they can access; and the types of actions that users can carry out on these structures. For example, MySQL allows you to explicitly control whether users can run the SELECT,UPDATE, INSERT, and DELETE statements, as well as whether they can LOCK TABLES, ALTER structures, or create and remove indexes. Most of the time, you’ll create users who can access and modify the data in a database but otherwise have no privileges to adjust the server configuration, change the database’s structure, or access other databases. We show you how to create different users and list all of the privileges later in this section.

MySQL users are distinct from the operating system users on the server computer. When you set up your machine, you automatically create superuser accounts that allow configuration of the server—the root user on a Linux or Mac OS X server, and the Administrator on Windows—and also one or more user accounts that you use to work with the server. For example, you could have a superuser account that’s used only when installing or configuring software such as MySQL or a new word processor, and an ordinary account that you log in to while writing, reading email, web browsing, and doing the other things you normally do.

The ordinary account can’t access or modify sensitive system-wide files, such as the system’s hardware settings, or the MySQL server logfiles or datafiles. On a single-user system, having a less privileged account for day-to-day use helps reduce the chances of doing silly things such as deleting important system files or installing malware by mistake. On a corporate or university server, this security is essential: it not only helps prevent accidental damage or malicious attack, but also helps protect confidential files and data.

If a system account on your server can access the MySQL configuration, it can bypass the monitor (and every other MySQL client) and carry out actions directly on the server or databases. For example, the system root user can manipulate any MySQL instance on the system, while an ordinary user can manipulate any MySQL instance that runs under her account. With this access, you can bypass the MySQL server’s authentication and user-management scheme by starting the server with the skip-grant-tables option; we discuss this and other ways to get around a forgotten root password in Resetting Forgotten MySQL Passwords,” later in this chapter. You can also browse data, indexes, and database structures using a text editor, or just copy the databases elsewhere and access them using another installation of MySQL. Therefore, you should take the usual precautions of maintaining physical security of your server, keeping operating system patches up-to-date, adding a network firewall, using appropriate permission settings on files and directories, and requiring hard-to-guess passwords. Remember, if your server is insecure or compromised, your MySQL server is insecure; it doesn’t matter how the MySQL users and privileges are configured. You should be similarly vigilant about access to your database backups.

Creating and Using New Users

To create a new user, you need to have permission to do so; the root user has this permission, so connect to the monitor as the root user:

$ mysql --user=root --password=the_mysql_root_password

Now create a new user called allmusic who’ll connect from the same system as the one the MySQL server is running on (localhost). We’ll grant this user all privileges on all tables in the music database (music.*) and assign the password the_password:

mysql> GRANT ALL ON music.* TO 'allmusic'@'localhost' IDENTIFIED BY 'the_password';

Query OK, 0 rows affected (0.02 sec)

This instruction creates the new user and assigns some privileges. Now, let’s discuss what we’ve done in more detail.

The GRANT statement gives privileges to users. Immediately following the keyword GRANT is the list of privileges that are given, which, in the previous case, is ALL (all simple privileges); we discuss the actual privileges later. Following the privilege list is the required keyword ON, and the databases or tables that the privileges are for. In the example, we grant the privileges for music.*, which means the music database and all its tables. If the specified MySQL user account does not exist, it will be created automatically by the GRANT statement.

In the example, we’re assigning privileges to 'allmusic'@'localhost', which means the user has the name allmusic and can connect to the server only from the localhost, the machine on which the database server is installed. There’s a 16-character limit on usernames. The at symbol (@) implies that the user is trying to connect to the server from the specified host; the MySQL user account doesn’t need to correspond to any system user account on that host, and so there is no relation to any email address. The quotes surrounding the username and the client hostname are optional; you need them only if either the username or the hostname has special characters, such as hyphens (-) or wildcard characters. For example, you could write:

mysql> GRANT ALL ON music.* TO ali@localhost IDENTIFIED BY 'the_password';

Query OK, 0 rows affected (0.02 sec)

However, we recommend that you use the quotes all the time to avoid any surprises.

The optional IDENTIFIED BY 'the_password' component sets the user’s password to the_password. There’s no limit on password length, but we recommend using eight or fewer characters because this avoids problems with system libraries on some platforms.

You’ll find many examples using GRANT throughout this chapter.

Let’s experiment with our new user. Quit the monitor using the QUIT command. Then run it again and connect as the user allmusic:

$ mysql --user=allmusic --password=the_password

Note that this time, we’ve specified the MySQL user allmusic for the user parameter and passed this user’s password to the password parameter.

You should see the mysql> prompt again. You will now be able to use the music database by typing USE music; and pressing Enter. Try running a simple query:

mysql> SELECT * FROM album;

You should see the albums in the database.

So far, we haven’t found the limits of our privileges. Let’s try using the university database:

mysql> USE university;

ERROR 1044 (42000): Access denied for user: 'allmusic'@'localhost' to database

'university'

MySQL complains that our new user doesn’t have permission to access the database university. Indeed, if we ask MySQL what databases are available, you’ll see that MySQL is secretive:

mysql> SHOW DATABASES;

+----------+

| Database |

+----------+

| music |

| test |

+----------+

2 rows in set (0.00 sec)

A user who doesn’t have any privileges for a database can’t see or use that database (the exception to this is a user who has the global SHOW DATABASE privilege we discuss later).

Let’s try to create a new database:

mysql> CREATE DATABASE some_new_database;

ERROR 1044 (42000): Access denied for user 'allmusic'@'localhost' to database

'some_new_database'

We can’t; when we were logged in as the MySQL root user, we never granted the allmusic user the privilege to create new databases.

Let’s create a second new user who can access only the artist table in the music database (music.artist). Quit the monitor (or start the monitor from another terminal or command prompt window) and connect again as the root user. Then, create this new user:

mysql> GRANT ALL ON music.artist TO 'partmusic'@'localhost'

-> IDENTIFIED BY 'the_password';

Query OK, 0 rows affected (0.01 sec)

We’ve specified the artist table in the music database by using music.artist. If you want to provide access to more than one table (but not all tables) in a database (or tables in different databases), you have to type several GRANT statements. For example, to add access to the albumtable to our newly created user, type:

mysql> GRANT ALL ON music.album TO 'partmusic'@'localhost';

Query OK, 0 rows affected (0.01 sec)

Since we’re reusing the username and location 'partmusic'@'localhost', there’s no need to provide a password in this second statement; the password was set when the user was first created, and it isn’t changed by the second statement.

You can also allow a user to access only specific columns in a table. For example, you can allow the partmusic user to have only read (SELECT) access to the title and time columns of the track table:

mysql> GRANT SELECT (track_id, time) ON music.track TO 'partmusic'@'localhost';

Query OK, 0 rows affected (0.01 sec)

The syntax is different from the previous examples: instead of specifying ALL privileges, we’ve specified only SELECT, and we’ve listed the columns to which the privilege applies—track_id and time—in parentheses after it. The remainder of the statement follows the same syntax as the previous examples, including the music.track component that specifies where the columns track-id and time are located. Note that you can’t grant all privileges at the column level; you must specifically list them.

Before you experiment with your new user, let’s summarize what you’ve done. You’ve created a new user, partmusic, set this user’s password to the_password, and allowed access to the database server from only the machine on which the server is installed, the localhost. Thepartmusic user has access to the music database and has all privileges for the album and artist tables. In addition, this user can run SELECT statements that retrieve values from the track_id and time columns in the track table.

Let’s test what our new user can do. Start the monitor as the new user by supplying the appropriate user and password parameters:

$ mysql --user=partmusic --password=the_password

and connect to the music database:

mysql> USE music;

Now, check what tables this user can access:

mysql> SHOW TABLES;

+-----------------+

| Tables_in_music |

+-----------------+

| album |

| artist |

| track |

+-----------------+

3 rows in set (0.00 sec)

You can see three of the four tables in the database, but since you (the partmusic user) don’t have privileges for the played table, you can’t see it. You do have privileges to do anything you want to the album and artist tables, so try this out:

mysql> INSERT INTO artist VALUES (7, "The Jimi Hendrix Experience");

Query OK, 1 row affected (0.00 sec)

mysql> SELECT album_name FROM album WHERE album_id=4;

+------------------------------------------+

| album_name |

+------------------------------------------+

| Retro - New Order / Bobby Gillespie LIVE |

+------------------------------------------+

1 row in set (0.00 sec)

Feel free to test your access to these tables further: you’ll be able to insert, delete, update, and retrieve all data.

Now, let’s test our limited access to the track table. First, we’ll try to retrieve the values in all columns:

mysql> SELECT * FROM track;

ERROR 1143 (42000): select command denied to user:

'partmusic'@'localhost' for column 'track_name' in table 'track'

As expected, MySQL complains that you don’t have privileges to retrieve values from the columns other than track_id and time; note that MySQL stops on its first error and doesn’t report all the columns you can’t access. If you now try to retrieve values for columns you can access, it works as expected:

mysql> SELECT time FROM TRACK LIMIT 3;

+------+

| time |

+------+

| 8.10 |

| 5.27 |

| 8.66 |

+------+

3 rows in set (0.00 sec)

Notice that, unlike databases and tables, you can see the details of all columns in a table even if you don’t have access to them:

mysql> DESCRIBE track;

+------------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------+--------------+------+-----+---------+-------+

| track_id | int(3) | | PRI | 0 | |

| track_name | char(128) | YES | | NULL | |

| artist_id | int(5) | | PRI | 0 | |

| album_id | int(4) | | PRI | 0 | |

| time | decimal(5,2) | YES | | NULL | |

+------------+--------------+------+-----+---------+-------+

5 rows in set (0.00 sec

Privileges

So far, we’ve shown you how to add new users and grant privileges for databases, tables, and columns. In this section, we discuss the privileges in more detail and explain which ones are used at the global, database, table, and column level. Then we discuss how the different privilege levels interact.

You can see a list of all available privileges by running the SHOW PRIVILEGES command in the MySQL monitor; Table 9-1 lists some of the more important of these. Each row shows a privilege, followed by a description, and then a list of the four levels at which the privilege can be granted. For example, the second row shows the ALTER privilege that controls whether the ALTER TABLE statement can be used, and shows that it can be controlled at the global, database, and table levels:

Global level

You can use ON *.* in a GRANT statement to grant a user a particular privilege across all databases on the server.

Database level

You can use, for example, music.* to grant a privilege for one or more databases.

Table level

You can use, for example, music.album to grant a privilege for one or more tables in a database.

Column level

Grants access for one or more columns in a table in a database (but isn’t available for ALTER). You grant column-level access using a comma-separated list in parentheses after the privilege, as in, for example:

GRANT SELECT (album_name, album_id) ON music.album

In this chapter, we explain how to manage privileges using the GRANT statement. Many of the statements affected by the privileges are discussed elsewhere as follows:

§ The statements DELETE, INSERT, SELECT, SHOW DATABASES, and UPDATE are introduced in Chapter 5 and discussed further in Chapters 7 and 8.

§ The statements ALTER, CREATE, DROP, LOAD DATA INFILE, and SELECT ... INTO are described in Chapter 6.

§ The statements LOCK TABLES and UNLOCK TABLES are discussed in Chapter 7.

The EXECUTE, PROCESS, REPLICATION CLIENT, REPLICATION SLAVE, CHANGE MASTER, KILL, and PURGE MASTER LOGS statements are outside the scope of this book; see the MySQL manual for more on these. We discuss GRANT OPTION in the next section.

Table 9-1 shows the levels at which the privileges can be configured: Global (G), Database (D), Table (T), and Column (C). For example, the first row shows that the ALL option is available at all levels except for columns.

Table 9-1. Privileges and their levels in MySQL

Table 9-2 shows what the ALL option means at the global, database, and table levels. For example, the second column shows what happens when you GRANT ALL ON *.* to a user. All privileges listed with a checkmark (✓) are given to the user, and those with a cross (✗) are omitted. TheGRANT OPTION—which allows a user to pass on his privileges to another user—isn’t available for ALL at any level, and therefore must be granted explicitly. We discuss it next.

Table 9-2. Simple privileges that comprise the ALL privilege at different levels

The GRANT OPTION Privilege

The GRANT OPTION privilege allows a user to pass on any privileges she has to other users. Consider an example, which we’ve run when connected to the monitor as the root user:

mysql> GRANT ALL ON music.* TO 'hugh'@'localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT GRANT OPTION ON music.* TO 'hugh'@'localhost';

Query OK, 0 rows affected (0.00 sec)

This creates a MySQL user hugh (with no password!) and allows him to pass on his privileges for the music database to other users. Since the GRANT OPTION is given at the database level (to music.*), hugh can pass on his privileges on that database, or on any of the tables or columns in that database. GRANT OPTION always allows a user to pass on his privileges at the level which they’re given, or any lower level, and it also allows him to pass on any future privileges he’s given. We explain this hierarchy more in the next section.

Let’s test our new privilege using the user hugh. Quit the monitor, and then reconnect as the MySQL user hugh:

$ mysql --user=hugh

Now, let’s give our privileges to another user:

mysql> GRANT ALL ON music.* TO 'selina'@'localhost';

Query OK, 0 rows affected (0.00 sec)

This passes on all privileges to a new user, selina (with no password). It doesn’t pass on the GRANT OPTION privilege, but you can do this if you want to:

mysql> GRANT GRANT OPTION ON music.* TO 'selina'@'localhost';

Query OK, 0 rows affected (0.00 sec)

Now selina can do the same things hugh can on the music database.

You can also pass on the GRANT OPTION privilege in a single SQL statement that also grants other privileges. Here’s an example using an alternative syntax:

mysql> GRANT ALL ON music.* to 'lucy'@'localhost' WITH GRANT OPTION;

Query OK, 0 rows affected (0.00 sec)

This has exactly the same effect as the previous two-step example that created the user selina.

As discussed previously, users can pass on privileges at the same or lower levels. Consider an example that’s executed when we’re connected as hugh:

mysql> GRANT ALL ON music.artist TO 'rose'@'localhost';

Query OK, 0 rows affected (0.00 sec)

Since hugh has all privileges for all tables in the music database, he can pass all privileges for only the artist table to a new user, rose.

Be careful with GRANT OPTION; users with this privilege can share other privileges in ways you may not anticipate. We discuss this further later in this chapter in More Security Tips.”

How Privileges Interact

In the previous section, we explained how the GRANT OPTION privilege is used to pass privileges to other users and how it allows privileges at lower levels in the privilege hierarchy to be granted. In this section, we explore the privilege hierarchy further and explain how MySQL allows or denies access to resources.

Figure 9-1. The privilege hierarchy

Figure 9-1 shows an example of the MySQL privilege hierarchy. There are four levels; reading from highest to lowest, these are global, database, table, and column. In Figure 9-1, the global level contains the MySQL server system and three databases: music, university, and flight. Each database contains tables; the figure shows the tables in the music database. Each table in turn contains columns, and the figure shows the columns in the artist table.

When you grant privileges at a level, those privileges are available at that and all lower levels. In Figure 9-1, if you grant privileges at the global level, those privileges are available for MySQL server functions and throughout the databases, tables, and columns. For example, if you have theUPDATE privilege at the global level, you can execute the UPDATE statement on any table or column in any database. If you grant privileges for only the music database, the privileges are available for just it and its tables and columns. Privileges never propagate up the hierarchy; for example, if you grant privileges for only a column, those privileges don’t apply for the table, database, or server.

When you run a statement, your privileges to run that statement are determined using a logical OR operation. The operation checks whether you have any of the following for the statement:

§ Global privileges

§ Database privileges

§ Table privileges

§ Column privileges

If any of these permit the statement, it proceeds. This has an important consequence: if you allow a privilege for a statement at a level, it doesn’t matter if it’s allowed or disallowed at another level. This can lead to unexpected behavior. For example, if you revoke a previously granted permission to SELECT from the artist table, access will not be revoked if the user still has the SELECT privilege to the music database or at the global level. Users and Hosts” discusses how users are allowed or disallowed server connections, and Revoking Privileges” explains how to revoke privileges.

Users and Hosts

So far, we’ve discussed the steps to grant privileges, as well as how these privileges interact in a hierarchy. However, we’ve skipped over the basic principles of connecting to the server and explaining how MySQL validates a connection. This section covers these topics and helps you understand how you connect to a MySQL server.

Local and Remote Users

MySQL supports both local and remote users. A local user connects to the server and accesses the databases from the same computer that the MySQL server is running on (localhost). All our examples so far have been for a local user. In contrast, a remote user connects to the server and accesses the databases from another computer.

For each application, you must decide how the database will be used and apply the most restrictive set of access privileges needed to get the job done. There are performance as well as security issues to be considered when doing this. MySQL actually treats local connections differently; if the client is local, the connection is made internally through a Unix socket (for Linux and Mac OS X) or through a named pipe (for Windows). This is generally much faster than the TCP/IP network connection used for remote access.

You should be careful not to give remote access to the database when you can avoid it. Consider the case where three different managers need to see how many items of each title there are in stock. You could give each manager an account on the MySQL server and allow remote access so that they can connect to the database from their own computers and run queries to view the data. This is shown in Figure 9-2.

Figure 9-2. Database server, with managers’ computers configured for remote access to the database server

Since there are a limited number of queries needed to generate standard reports for the managers, you could instead create a password-protected dynamic web page that displays the output of the necessary reporting queries; managers would still access the reports from their own computers, but through a web browser rather than a MySQL client. This approach has several security benefits: you don’t have to give database server accounts to other users, you don’t have to allow remote access, only your own client programs can run queries on the database server, and only the limited set of queries supported by your client program will be executed. Figure 9-3 shows how this could be configured.

Figure 9-3. Database server and web application; the web application has local access to the database server, and the managers’ computers interact with the database through the web application

If the web server and the database server are on different computers, you have to allow the web application on the web server to connect remotely to the database server, as shown in Figure 9-4.

Figure 9-4. Web application, web server, and database server configured for remote access

Creating a New Remote User

If you want to allow a user to connect to the server from another computer, you must specify the host from which they can do so (the remote client). Suppose that you have a home network, that your machine has the private IP address 192.168.1.2, that your home domain is invyhome.com, and that your machine is named ruttle (that is, its complete name is ruttle.invyhome.com). Let’s also assume that you’re running a MySQL server on ruttle, and that there are two other machines on the network: toorak.invyhome.com with the IP address 192.168.1.4 andyazd.invyhome.com with IP address 192.168.1.6.

Figure 9-5 illustrates this setup.

Figure 9-5. A simple home network with three computers, and one MySQL server

Again, we differentiate between user accounts on the MySQL server (MySQL users or MySQL accounts) and user accounts on the host machines (system users or system accounts).

Now, let’s consider the MySQL setup on ruttle. Let’s assume you’ve previously logged in to the MySQL server on ruttle as the root user and created the user hugh with the statement:

mysql> GRANT ALL on *.* TO 'hugh'@'localhost' IDENTIFIED BY 'the_password';

Query OK, 0 rows affected (0.00 sec)

If you’re logged in to your system account on ruttle, you can connect to the MySQL server on ruttle using the following:

$ mysql --host=localhost --user=hugh --password=the_password

Including the --host=localhost actually has no effect, since localhost is the default anyway. Now, let’s try specifying the IP address for localhost; this is always 127.0.0.1:

$ mysql --user=hugh --host=127.0.0.1 --password=the_password

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 47 to server version: 5.0.22-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

The result is another successful connection, since localhost and 127.0.0.1 are the same system, and MySQL matches the request for host 127.0.0.1 against the privileges for localhost.

Now, let’s try connecting to the MySQL server on ruttle from ruttle by using its IP address:

$ mysql --user=hugh --host=192.168.1.2 --password=the_password

ERROR 1130 (): #HY000Host '192.168.1.2' is not allowed to connect to this

MySQL server

This time, the connection isn’t successful. If you replace 192.168.1.2 with ruttle.invyhome.com, you’ll see the same problem. Let’s explore why we can’t connect.

At the beginning of this section, we allowed access to the user 'hugh'@'localhost'. That’s exactly what the MySQL server is enforcing: we can only connect from the localhost, and not from anywhere else, including from the actual IP address or domain of the localhost machine. If you want to allow access from 192.168.1.2 (and its equivalent domain name ruttle.invyhome.com), you need to grant those privileges by creating a new user with the username hugh and the host 192.168.1.2. Note that each username and host pair is treated as a separate user and has its own password.

Log in to the monitor as the root user, and type:

mysql> GRANT ALL ON *.* TO 'hugh'@'192.168.1.2' IDENTIFIED BY 'the_password';

Query OK, 0 rows affected (0.00 sec)

Now, quit the monitor and try connecting as the user hugh:

$ mysql --user=hugh --host=192.168.1.2 --password=the_password

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 50 to server version: 5.0.22-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

You’ll also find you can now connect using ruttle.invyhome.com in place of 192.168.1.2, as long as you’ve got a correctly configured domain nameserver (DNS) setup. If you have trouble connecting to the MySQL server, refer to the checklist in Client Programs Can’t Connect to the Server” in Chapter 2.

Suppose now that you want to allow toorak to access the MySQL server that’s running on ruttle. There are several different ways to do this, some more flexible than others. The simplest approach is to connect to the MySQL server on ruttle as the root user and grant privileges to a new user 'hugh'@'toorak.invyhome.com' using the following statement:

mysql> GRANT ALL ON *.* TO 'hugh'@'toorak.invyhome.com' IDENTIFIED BY 'the_password';

Query OK, 0 rows affected (0.00 sec)

You’ll now find that you can run a MySQL monitor on toorak and connect to ruttle using the following command:

$ mysql --user=hugh --host=ruttle.invyhome.com --password=the_password

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 52 to server version: 5.0.22-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Using the IP addresses 192.168.1.2 for ruttle.invyhome.com and 192.168.1.4 for toorak.invyhome.com should work too, and it’s more secure, as IP addresses are harder to spoof than domain names.

Our approach so far has been to create new users for each IP address. We now have three users with the name hugh, one each for the localhost, 192.168.1.2, and 192.168.1.4. This isn’t always a good approach: we now have to remember to maintain all three users and keep their privileges synchronized if we want the same access level from all three locations. However, it’s also flexible: it allows you to differentiate between different remote users with the same username, or offer a flexible, customized security policy when a user connects from different locations.

Let’s consider other ways to allow the same user to connect from several locations. You can allow a user to connect from all computers on a network subnet by using one or more wildcards in the GRANT statement. Suppose you want to allow jill to connect from any of the machines in the domain invyhome.com. You can do this with:

mysql> GRANT ALL ON *.* TO 'jill'@'%.invyhome.com' IDENTIFIED BY 'the_password';

Query OK, 0 rows affected (0.01 sec)

As in the SQL LIKE clause, the wildcard character % matches any string, and so this entry now matches any domain name with the suffix invyhome.com. The outcome is that connections as jill from ruttle.invyhome.com and toorak.invyhome.com are allowed; jill can also connect from any other machine that joins the network.

You can also use wildcards in IP addresses. For example, you can allow connections from all machines on the invyhome.com subnet by allowing access to machines matching the IP address range 192.168.1.%.

To do this, run the following:

mysql> GRANT ALL ON *.* TO 'harry'@'192.168.1.%' IDENTIFIED BY 'the_password';

Query OK, 0 rows affected (0.01 sec)

Again, connections as harry from ruttle and toorak (and any other machines on the local network) are allowed.

Table 9-3 shows different specifications of network addresses that use wildcards and gives examples of where the user lloyd could connect from. The entry in the final row allows lloyd to connect from anywhere and should be used with caution.

Table 9-3. Host specifications and their meanings in GRANT statements

The effect of each of these settings is as follows:

1. Connections are allowed only from the machine lloyd.lloydhouse.com.

2. Connections are allowed from any machine in the lloydhouse.com domain.

3. Connections are allowed only from the machine with the IP address 192.168.1.2.

4. The address range is specified as an IP address and a netmask. The current standard IPv4 addresses are 32 bits long and are commonly shown in the dotted decimal notation as 4 decimal numbers, each corresponding to 8 bits of the IP address or netmask. For example, the IP address:

11000000101010000000000100000001

is shown as:

192.168.1.1

which is much easier to read!

The netmask specifies how many of these bits (from left) identify the network; the remaining bits identify the hosts on that network. The smaller the netmask, the more bits remain for the hosts, and so the greater number of hosts that fall into the specified range. The sample specification 192.168.1.0/255.255.255.0 says that the first 24 bits of the host IP address must match the first 24 bits of the address 192.168.1.0, so any address that starts with 192.168.1. is accepted. The netmask specified to MySQL can only be 8, 16, 24, or 32 bits in length.

5. Connections are allowed from any machine. If you omit the host specification, % is assumed.

The wildcard approach typically removes the need to create multiple users, as a given user can connect from more than one machine. However, consider the case where the user steph wants to connect from the client hosts localhost, steph.lloydhouse.com, andsteph.hughwilliams.com. Using the techniques we’ve discussed so far, you have two choices: have three users, one for each host; or allow access from any host using 'steph'@'%'. Neither solution is ideal. Fortunately, MySQL supports yet another way to create one user for different hosts, but this requires knowledge of the structure of the database tables used to manage the privilege system. We present this later in the chapter in Managing Privileges with SQL.”

Anonymous Users

We’ve previously seen how we can use wildcard specifications for hosts, but wildcard characters aren’t allowed in usernames; you can’t, for example, specify 'fred%'@'localhost'. However, you can have a user with an empty username that allows anonymous connections and matches all usernames.

You can create an anonymous local user who can read data from the music database as follows:

mysql> GRANT SELECT ON music.* TO ''@'localhost';

Query OK, 0 rows affected (0.00 sec)

Note that the username is specified as two single quotes, with nothing between them. This user allows you to connect without a username or password from the localhost:

$ mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 55 to server version: 5.0.22-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

You can use the CURRENT_USER() function to check which user you’re logged in as:

mysql> SELECT CURRENT_USER();

+----------------+

| CURRENT_USER() |

+----------------+

| @localhost |

+----------------+

1 row in set (0.00 sec)

Here, there is nothing before the @ symbol, indicating that you’re logged in as the anonymous user ''@localhost. The MySQL server decides which user to log you in as based on a checklist we describe in the next section.

Which User Is Connected?

So far, we have created several users and deliberately chosen a different name for each. We did this to avoid discussing an important issue: what happens if more than one user and host combination matches when a connection is attempted?

To understand how MySQL allows connections, connect as the MySQL user root and create two users with the same name and different host specifications:

mysql> GRANT SELECT ON music.* TO 'dave'@'%' IDENTIFIED BY 'the_password';

Query OK, 0 rows affected (0.06 sec)

mysql> GRANT ALL ON music.* TO 'dave'@'localhost' IDENTIFIED BY 'the_password';

Query OK, 0 rows affected (0.01 sec)

The first user dave can now connect from any host and run only SELECT statements on the music database. The second user dave is specific to the localhost and is allowed all privileges on music. The host specifications of the two users overlap: '%' means all hosts, and so includeslocalhost as one of the allowed hosts. Now, let’s experiment with dave.

Let’s connect to the server using the monitor installed on localhost:

$ mysql --user=dave --password=the_password

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 57 to server version: 5.0.22-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Now, let’s try to do more than just SELECT data:

mysql> USE music;

Database changed

mysql> INSERT INTO artist VALUES (8, "The Psychedelic Furs");

Query OK, 1 row affected (0.06 sec)

That worked, so we must be logged in as the user 'dave'@'localhost' and not 'dave'@'%'.

Here’s what the CURRENT_USER() function reports for the connection we’ve just used:

mysql> SELECT CURRENT_USER();

+----------------+

| CURRENT_USER() |

+----------------+

| dave@localhost |

+----------------+

1 row in set (0.00 sec)

This confirms we’re logged in as 'dave'@'localhost', and not through the more general host specification 'dave'@'%'.

How does MySQL decide which user to use when you establish a connection? The answer has two parts: first, MySQL sorts the user entries by host from most to least specific and, for duplicate hosts, any anonymous user entry appears last. Consider an example. Suppose you have four user and host combinations:

§ 'dave'@'localhost'

§ 'dave'@'%'

§ ''@'localhost'

§ 'hugh'@'192.168.1.%'

From most specific to least, the hosts are localhost, then 192.168.1.%, and finally %. There are two entries for users on the localhost, and, since one is anonymous, the one with a name (dave) is more specific. Overall, this leads to the following sort order:

§ 'dave'@'localhost'

§ ''@'localhost'

§ 'hugh'@'192.168.1.%'

§ 'dave'@'%'

The second step in establishing a connection is matching your connection request against the sorted list. The first entry that matches your connection requirements is used; if none match, you’re denied access. Suppose you try to connect from the localhost using the username dave. The first entry in the list, 'dave'@'localhost' matches, and so you’re authenticated and given the privileges of that user. Suppose now you try to connect from the localhost using the username hugh. Here’s a surprise: MySQL ignores the username you provide, and you’re connected as''@'localhost' because, as discussed in the previous section, the anonymous username is a wildcard that matches all usernames! You might find this annoying, but some argue it’s a feature that can be used to ensure users from particular hosts get at least a minimal set of privileges.

Consider a final example, where you try to connect from the network machine yazd (192.168.1.6) as dave. The first two entries are for the localhost and so don’t match. The third entry has a host specification that matches, but the username hugh does not. The final entry’s host specification matches, and so does the username, and therefore the connection is established with the privileges of the user 'dave'@'%'.

Checking Privileges

We’ve explained how to grant privileges and how to understand the scope of those privileges. This section explains how to identify the privileges that a user has, and how to revoke those privileges.

If you’ve been following our examples, you have created more than 10 users so far in this chapter, and you probably can’t remember all of them. It’s important to know the users you have defined and the privileges that these users have, and that you understand how connections are verified: without careful management, you can accidentally allow more privileges than you planned, or allow connections by users you didn’t want to grant access to. Fortunately, there are a few tools available to help you explore access privileges.

The simplest method to check the privileges of a user is to use the SHOW GRANTS statement. You can execute this statement to check the privileges of other users only if you have access to the mysql database; however, you can always check your own privileges. If you want to experiment, it’s best to log in now as the root user or another user with sufficient global privileges. We explain the role of the mysql database in privilege management later in Managing Privileges with SQL.”

After logging in to the monitor, you can check the current user’s privileges with:

mysql> SHOW GRANTS;

+---------------------------------------------------------------------+

| Grants for root@localhost |

+---------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |

+---------------------------------------------------------------------+

1 row in set (0.00 sec)

MySQL reports the privileges as one or more GRANT statements. Not surprisingly, 'root'@'localhost' has all privileges, including GRANT OPTION.

Now let’s check the privileges of the user 'selina'@'localhost' we created earlier in this chapter:

mysql> SHOW GRANTS FOR 'selina'@'localhost';

+-----------------------------------------------------------------------------+

| Grants for selina@localhost |

+-----------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'selina'@'localhost' |

| GRANT ALL PRIVILEGES ON `music`.* TO 'selina'@'localhost' WITH GRANT OPTION |

+-----------------------------------------------------------------------------+

2 rows in set (0.00 sec)

This syntax works on MySQL versions later than 4.1.2. The first GRANT statement is a default privilege that creates the user with no privileges (yes, USAGE implies no privileges!). The second statement gives all privileges for the music database.

Let’s also check the user 'partmusic'@'localhost':

mysql> SHOW GRANTS FOR 'partmusic'@'localhost';

+-----------------------------------------------------------------------------+

| Grants for partmusic@localhost |

+-----------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'partmusic'@'localhost' IDENTIFIED BY |

| PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' |

| GRANT ALL PRIVILEGES ON `music`.`album` TO 'partmusic'@'localhost' |

| GRANT ALL PRIVILEGES ON `music`.`artist` TO 'partmusic'@'localhost' |

| GRANT SELECT (track_id, time) ON `music`.`track` TO 'partmusic'@'localhost' |

+-----------------------------------------------------------------------------+

4 rows in set (0.00 sec)

Again, the first statement creates a user with no privileges, and later statements add the privileges. The first statement also serves another purpose: it sets the password for the user. Since the password has been hashed with a one-way encryption function, it can’t be decrypted, and so we repeat the encrypted password string with the statement IDENTIFIED BY PASSWORD. We discuss passwords in detail later in this chapter.

The SHOW GRANTS statement works only for exploring exactly one user that matches the string you provide. For example, if you’ve previously created a user 'fred'@'%', you can list the privileges of that user with:

mysql> SHOW GRANTS FOR 'fred'@'%';

This statement doesn’t check for all users with the name fred, however. Each username and host pair is treated separately; for example, we could have the user 'ali'@'sadri.invyhome.com' with all privileges on the test database, and the user 'ali'@'saleh.invyhome.com' with all privileges on the music database:

mysql> GRANT ALL ON test.* TO 'ali'@'sadri.invyhome.com'

-> IDENTIFIED BY 'a_password';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON music.* TO 'ali'@'saleh.invyhome.com'

IDENTIFIED BY 'another_password';

Query OK, 0 rows affected (0.00 sec)

If you check the privileges with the SHOW GRANTS statement, you’ll see that the access privileges and the stored password are different for each username and host pair:

mysql> SHOW GRANTS FOR 'ali'@'sadri.invyhome.com';

+--------------------------------------------------------------------+

| Grants for ali@sadri.invyhome.com |

+--------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'ali'@'sadri.invyhome.com' |

| IDENTIFIED BY PASSWORD '*5DC1D11F45824A9DD613961F05C1EC1E7A1601AA' |

| GRANT ALL PRIVILEGES ON `test`.* TO 'ali'@'sadri.invyhome.com' |

+--------------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'ali'@'saleh.invyhome.com';

+--------------------------------------------------------------------+

| Grants for ali@saleh.invyhome.com |

+--------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'ali'@'saleh.invyhome.com' |

| IDENTIFIED BY PASSWORD '*A5CF560EBFDD483CD4162DD31FBA6AF8F5586069' |

| GRANT ALL PRIVILEGES ON `music`.* TO 'ali'@'saleh.invyhome.com' |

+--------------------------------------------------------------------+

2 rows in set (0.00 sec)

As you can see, a connection by ali is allowed different privileges and uses a different password according to the host the connection is coming from.

To explore all of the users available on your MySQL server, you can use the command-line mysqlaccess utility that we describe in the next section.

Another method to check current privileges is to use SQL to explore the mysql database that manages the privileges. If you’re experienced with SQL, this is perhaps the easiest approach, and it’s the one we usually use. We describe this approach later in Managing Privileges with SQL.”

mysqlaccess

To quickly see what level of access a particular user has for a particular database, you can use the mysqlaccess script from a terminal or command prompt. Let’s see what level of access the user partmusic has for the database music. We’ll need to specify the MySQL superuser name (root) and password to access the information:

$ mysqlaccess --user=root --password=the_mysql_root_password partmusic music

mysqlaccess Version 2.06, 20 Dec 2000

By RUG-AIV, by Yves Carlier (Yves.Carlier@rug.ac.be)

Changes by Steve Harvey (sgh@vex.net)

This software comes with ABSOLUTELY NO WARRANTY.

Access-rights

for USER 'partmusic', from HOST 'localhost', to DB 'music'

+-----------------------+---+ +----------------------+---+

| Select_priv | N | | Lock_tables_priv | N |

| Insert_priv | N | | Execute_priv | N |

| Update_priv | N | | Repl_slave_priv | N |

| Delete_priv | N | | Repl_client_priv | N |

| Create_priv | N | | Create_view_priv | N |

| Drop_priv | N | | Show_view_priv | N |

| Reload_priv | N | | Create_routine_priv | N |

| Shutdown_priv | N | | Alter_routine_priv | N |

| Process_priv | N | | Create_user_priv | N |

| File_priv | N | | Ssl_type | ? |

| Grant_priv | N | | Ssl_cipher | ? |

| References_priv | N | | X509_issuer | ? |

| Index_priv | N | | X509_subject | ? |

| Alter_priv | N | | Max_questions | 0 |

| Show_db_priv | N | | Max_updates | 0 |

| Super_priv | N | | Max_connections | 0 |

| Create_tmp_table_priv | N | | Max_user_connections | 0 |

+-----------------------+---+ +----------------------+---+

NOTE: A password is required for user `partmusic' :-(

The following rules are used:

db : 'No matching rule'

host : 'Not processed: host-field is not empty in db-table.'

user : 'localhost','partmusic','652f9c175d1914f9',

'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N',

'N','N','N','N','N','N','N','N','N','','','','','0','0','0','0'

BUGs can be reported by email to bugs@mysql.com

If you specify the wildcard character '*' in place of music, the access privileges for all databases will be shown. Similarly, you can specify the wildcard character '*' in place of partmusic to see the access privileges for all users: if you specify both, you can exhaustively explore all users of all databases:

$ mysqlaccess --user=root --password=the_mysql_root_password '*' '*'

A particularly useful feature of mysqlaccess is that it shows what privileges a current user would have for any new database that is created. For example, part of the output of the previous command is:

Access-rights

for USER 'partmusic', from HOST 'localhost', to DB 'ANY_NEW_DB'

+-----------------------+---+ +----------------------+---+

| Select_priv | N | | Lock_tables_priv | N |

| Insert_priv | N | | Execute_priv | N |

| Update_priv | N | | Repl_slave_priv | N |

| Delete_priv | N | | Repl_client_priv | N |

| Create_priv | N | | Create_view_priv | N |

| Drop_priv | N | | Show_view_priv | N |

| Reload_priv | N | | Create_routine_priv | N |

| Shutdown_priv | N | | Alter_routine_priv | N |

| Process_priv | N | | Create_user_priv | N |

| File_priv | N | | Ssl_type | ? |

| Grant_priv | N | | Ssl_cipher | ? |

| References_priv | N | | X509_issuer | ? |

| Index_priv | N | | X509_subject | ? |

| Alter_priv | N | | Max_questions | 0 |

| Show_db_priv | N | | Max_updates | 0 |

| Super_priv | N | | Max_connections | 0 |

| Create_tmp_table_priv | N | | Max_user_connections | 0 |

+-----------------------+---+ +----------------------+---+

NOTE: A password is required for user `partmusic' :-(

The following rules are used:

db : 'No matching rule'

host : 'Not processed: host-field is not empty in db-table.'

user : 'localhost','partmusic','652f9c175d1914f9','N','N','N','N',

'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N',

'N','N','N','N','N','N','N','','','','','0','0','0','0'

It also shows what privileges an anonymous user has for all current databases:

Access-rights

for USER 'ANY_NEW_USER', from HOST 'localhost', to DB 'ANY_NEW_DB'

+-----------------------+---+ +----------------------+---+

| Select_priv | N | | Lock_tables_priv | N |

| Insert_priv | N | | Execute_priv | N |

| Update_priv | N | | Repl_slave_priv | N |

| Delete_priv | N | | Repl_client_priv | N |

| Create_priv | N | | Create_view_priv | N |

| Drop_priv | N | | Show_view_priv | N |

| Reload_priv | N | | Create_routine_priv | N |

| Shutdown_priv | N | | Alter_routine_priv | N |

| Process_priv | N | | Create_user_priv | N |

| File_priv | N | | Ssl_type | ? |

| Grant_priv | N | | Ssl_cipher | ? |

| References_priv | N | | X509_issuer | ? |

| Index_priv | N | | X509_subject | ? |

| Alter_priv | N | | Max_questions | 0 |

| Show_db_priv | N | | Max_updates | 0 |

| Super_priv | N | | Max_connections | 0 |

| Create_tmp_table_priv | N | | Max_user_connections | 0 |

+-----------------------+---+ +----------------------+---+

BEWARE: Everybody can access your DB as user `ANY_NEW_USER' from host `localhost'

: WITHOUT supplying a password.

: Be very careful about it!!

BEWARE: Accessing the db as an anonymous user.

: Your username has no relevance

The following rules are used:

db : 'No matching rule'

host : 'Not processed: host-field is not empty in db-table.'

user : 'localhost','','','N','N','N','N','N','N','N','N','N','N','N',

'N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','',

'','','','0','0','0','0'

BUGs can be reported by email to bugs@mysql.com

You can also check the results for all hosts by executing mysqlaccess '*' '*' '*'. Note that mysqlaccess shows only database-level access and not finer-grained privileges such as table- or column-level access. For this level of information, you must use SHOW GRANTS or direct SQL access to the mysql database.

Configuring mysqlaccess

The mysqlaccess program is a Perl script; if you’re using Windows, you’ll need to follow the instructions in Installing Perl modules under Windows” in Chapter 2 to be able to use Perl scripts.

A common problem occurs when mysqlaccess doesn’t know where to find your MySQL directories. If you’ve installed MySQL in a nonstandard location, you may get an error message saying that the script couldn’t find the MySQL client program. To resolve this problem, you’ll need to provide the correct path to the mysql executable. Find the mysqlaccess file in the MySQL bin directory, open it in a text editor, find the line that sets the $MYSQL variable, and modify it to specify the correct path for your MySQL installation.

For example, you might find the path set to /usr/bin/mysql as below:

$MYSQL = '/usr/bin/mysql'; # path to mysql executable

If you’ve installed MySQL in /usr/local/mysql, you would change this to:

$MYSQL = '/usr/local/mysql/bin/mysql'; # path to mysql executable

Now save the file and exit the editor; hopefully, everything should work now.

Revoking Privileges

You can selectively revoke privileges with the REVOKE statement, which essentially has the same syntax as GRANT. Consider a simple example, in which we remove the SELECT privilege from the user 'partmusic'@'localhost' for the time column in the track table in the musicdatabase. Here’s the statement, which we’ve run when logged in as 'root'@'localhost':

mysql> REVOKE SELECT (time) ON music.track FROM 'partmusic'@'localhost';

Query OK, 0 rows affected (0.06 sec)

The format of REVOKE is straightforward when you understand GRANT. Following the keyword REVOKE is one or more comma-separated privileges, and these are optionally followed by column names, comma-separated in braces; this is the same as GRANT. The ON keyword has the same function as in GRANT and is followed by a database and table name, both of which can be wildcards. The FROM keyword is followed by the user and host from which the privileges are to be revoked, and the host can include wildcards.

Removing privileges using the basic syntax is laborious, since it requires that you remove the privileges in the same way they are granted. For example, to remove all privileges of 'partmusic'@'localhost', you would use the following steps:

mysql> REVOKE SELECT (track_id) ON music.track FROM 'partmusic'@'localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE ALL PRIVILEGES ON music.artist FROM 'partmusic'@'localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE ALL PRIVILEGES ON music.album FROM 'partmusic'@'localhost';

Query OK, 0 rows affected (0.00 sec)

You can remove all database-, table-, and column-level privileges of a user at once using one of the following two methods. If you’re using a version of MySQL earlier than 4.1.2, use:

mysql> REVOKE ALL PRIVILEGES FROM 'partmusic'@'localhost';

Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE GRANT OPTION FROM 'partmusic'@'localhost';

Query OK, 0 rows affected (0.00 sec)

From MySQL version 4.1.2 onward, you can combine these into a single statement:

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'partmusic'@'localhost';

Query OK, 0 rows affected (0.00 sec)

You could get a similar result using:

mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'allmusic'@'localhost';

but this would not revoke any GRANT OPTION privileges that the user might have. To limit the revocation to the music database, you would write music.* rather than *.*.

Removing Users

The previous section explained how to remove privileges, but the user is not actually deleted from the server; you can check this using the SHOW GRANTS statement:

mysql> SHOW GRANTS FOR 'partmusic'@'localhost';

+-------------------------------------------------------------+

| Grants for partmusic@localhost |

+-------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'partmusic'@'localhost' IDENTIFIED BY |

| PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' |

+-------------------------------------------------------------+

1 row in set (0.00 sec)

This means the user can still connect, but has no privileges when she does.

You can remove access to the MySQL server by removing a user. The DROP USER statement (available since MySQL 4.1.1) removes a user who has no privileges. Here’s an example that completes the removal of 'partmusic'@'localhost' that we began in the previous section:

mysql> DROP USER 'partmusic'@'localhost';

Query OK, 0 rows affected (0.00 sec)

Prior to MySQL version 5.02, the DROP USER statement reported an error if any privileges remained for a user:

mysql> DROP USER 'selina'@'localhost';

ERROR 1268 (HY000): Can't drop one or more of the requested users

In such a case, you must first revoke all privileges for a user before trying to DROP them.

Prior to MySQL version 4.1.1, you needed to use the SQL DELETE statement to remove a user. Here’s how you remove the user 'partmusic'@'localhost' in these versions:

mysql> DELETE FROM mysql.user WHERE User='partmusic' and Host='localhost';

Query OK, 1 row affected (0.00 sec)

Whenever you update the grant tables in the mysql database directly, you have to use the FLUSH PRIVILEGES instruction to tell the server to read in the updated data:

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

The structure of the mysql database and the FLUSH PRIVILEGES statement are explained later in Managing Privileges with SQL.”

Understanding and Changing Passwords

This section explains how user passwords work in MySQL and how they can be set, changed, and removed. Passwords can be of any length, though practical limitations in some operating systems necessitate that they be no longer than eight characters. When assigning passwords, we recommend that you follow the same principles that you would with any other password-protected system: choose passwords that have a mix of uppercase, lowercase, numeric, and special characters; avoid using dictionary words; and avoid recording your password anywhere it can be easily found. We use no passwords and simple passwords—such as the_password—in this chapter to demonstrate concepts, but we recommend that in practice you use a more complex password that incorporates a mix of letters, numbers, and punctuation symbols (for example, 1n1T?s313Y0). Of course, choose a password that you can remember without having to write it down somewhere; pieces of paper often turn up in the wrong hands!

The simplest method to set a password is to use the IDENTIFIED BY clause when you create or modify the privileges of a user. You’ve seen several examples of this so far in this chapter. Here’s one reproduced from a previous section:

mysql> GRANT ALL ON music.* TO 'allmusic'@'localhost' IDENTIFIED BY 'the_password';

Query OK, 0 rows affected (0.06 sec)

This process takes the plain-text string the_password, hashes it using the MySQL PASSWORD() function, and stores the hashed string in the user table in the mysql database. Later, when a client wants a connection as this user, the plain-text password supplied by the client is hashed with the PASSWORD() function and compared to the string in the database. If it matches, the client is authenticated; otherwise, not. Prior to MySQL 4.1.0, the hashed string was 16 characters in length, and since 4.1.1 it has been 41 characters; don’t use MySQL 4.1.0, which has an incompatible 45-character password and a different PASSWORD() function.

You can experiment with the PASSWORD() function to examine the strings produced from a plain-text password. With a server older than 4.1.1, or with a new server configured with the old_passwords option, you would see:

mysql> SELECT PASSWORD('the_password');

+--------------------------+

| PASSWORD('the_password') |

+--------------------------+

| 268f5b591007a24f |

+--------------------------+

1 row in set (0.07 sec)

Using exactly the same command on a MySQL server that is newer than version 4.1.1 (and that has not been configured with the old_passwords option), we get:

mysql> SELECT PASSWORD('the_password');

+-------------------------------------------+

| PASSWORD('the_password') |

+-------------------------------------------+

| *201716EF6717C367868F777B9C6E17796F19F379 |

+-------------------------------------------+

1 row in set (0.02 sec)

You can still list the old-format password using the OLD_PASSWORD() function:

mysql> SELECT OLD_PASSWORD('the_password');

+--------------------------+

| PASSWORD('the_password') |

+--------------------------+

| 268f5b591007a24f |

+--------------------------+

1 row in set (0.07 sec)

It’s not possible to reverse the hashing process to derive the plain-text password from the hashed string, so the actual passwords cannot be deduced even if you have access to the hashed passwords in the mysql database. However, this scheme is still susceptible to dictionary and brute-force attacks, and allowing access to any user details can have security implications. Hence, you shouldn’t allow users to access the mysql database unless they have administrator privileges.

There are three ways to set or change a password. One way is to issue a GRANT statement and include the IDENTIFIED BY clause. Suppose you’ve already created the user 'selina'@'localhost' using this statement:

mysql> GRANT ALL ON music.* TO 'selina'@'localhost' IDENTIFIED BY 'the_password';

Query OK, 0 rows affected (0.00 sec)

If the user exists, you can change the password while you’re granting new privileges, or simply by granting no further privileges as follows:

mysql> GRANT USAGE ON *.* TO 'selina'@'localhost' IDENTIFIED BY 'another_password';

Query OK, 0 rows affected (0.00 sec)

This statement changes the password but has no effect on the current privileges.

Another way to change a password is to use the SET PASSWORD statement. Here’s an example:

mysql> SET PASSWORD FOR 'selina'@'localhost' = PASSWORD('another_password');

Query OK, 0 rows affected (0.00 sec)

You can set the password for the user you’re logged in as by using:

mysql> SET PASSWORD=PASSWORD('the_password');

Query OK, 0 rows affected (0.00 sec)

In both cases, remember to include the PASSWORD() function in the statement; if you leave it out, the server will store the plain-text password instead of the hashed string. When authenticating a user, MySQL compares the hash of the user’s input to the stored string; if the stored string isn’t already hashed, these won’t match, and the server will refuse access.

You can also use the mysqladmin password command to change your own password from the command line. For example, you can change the password for the user your_mysql_username from your_old_mysql_password to your new mysql password by typing:

$ mysqladmin \

--user=your_mysql_username \

--password=your_old_mysql_password \

password "your new mysql password"

Notice that since the new password contains spaces, we’ve enclosed it in quotes.

The user and host options are for the user you want to connect as and the server you want to connect to, respectively. You can use mysqladmin to change the password for only your own username on localhost. For example, if your MySQL username is sarah, you can change the password only for 'sarah'@'localhost'. Or if you want to change the password for another username and host pair, such as 'sarah'@'sadri.invyhome.com or 'susan'@'localhost', you’ll need to use the MySQL monitor or another more flexible MySQL client.

If you’re running MySQL for the first time, or if your MySQL user doesn’t have a password already set, you don’t need to specify the current password—that is, you can omit the password option.

You can also remove a user’s password. Here’s an example using the SET PASSWORD statement:

mysql> SET PASSWORD FOR 'selina'@'localhost' = '';

Query OK, 0 rows affected (0.00 sec)

This stores the empty string as the password, allowing connections without a password parameter. Again, it’s important to always use passwords for any production server.

Sometimes, you’ll want to create a new user with the same password as another, or you’ll want to re-create or migrate users from one installation to another. In these cases, you may not know the plain-text password of all users, but if you have access to the SHOW GRANTS statement or themysql database, you can discover the hashed values. If you want to create a user using a hashed password instead of asking MySQL to hash the password for you, use the PASSWORD keyword as follows:

mysql> GRANT USAGE ON *.* TO 'partmusic'@'localhost'

-> IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7';

Query OK, 0 rows affected (0.00 sec)

The PASSWORD keyword stores the hashed string directly, rather than passing it through the PASSWORD() function. You’ll recall from earlier that the plain-text password was actually the_password, and you’ll find you can now connect using it:

$ mysql --user=partmusic --password=the_password

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 60 to server version: 5.0.22-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

You can also manually set a password to its hashed version by using the SET PASSWORD statement without the PASSWORD() function as follows:

mysql> SET PASSWORD FOR 'partmusic'@'localhost' =

'*14E65567ABDB5135D0CFD9A70B3032C179A49EE7'

Query OK, 0 rows affected (0.00 sec)

Again, you can now connect using the plain-text password the_password.

The Default Users

This section explains the user accounts that are created when MySQL is installed and shows you how to secure your installation by making important changes to these default settings. The next section explains how to put together the things you’ve learned to develop a complete user security policy.

When you install your MySQL server, it comes preconfigured with one or two default users. The privileges of these users and the locations vary between operating systems, but you must ensure that their privileges match your requirements, and make decisions about the machines that connections to your database server can come from; you might also decide to remove one or more of the default users. Before we explain how to make these decisions, let’s discuss the users and how they access the server.

On all platforms, MySQL may come installed with two users:

root

This is the superuser, who can do anything to the server, users, databases, and data. The superuser usually creates new users who have authority to access and manipulate specific databases. Once you’ve installed MySQL, you must configure the root user, and we show you how to do this later in this section.

anonymous

This user has no username; you can use it to connect to the server without supplying any credentials. Also, as described earlier in Anonymous Users,” it is used when host credentials match but the requested username doesn’t. The anonymous user has very limited privileges by default; in the next section, we’ll explain what these are.

Both users have no password by default. With the anonymous user, this means you can connect to the database server without a username and password. For the root user, you supply the username root, but there’s no password. Again, these are user accounts on the MySQL server, not on the operating system (Linux, Windows, or Mac OS X).

Default User Configuration

The default installation allows the default users to access the server, but the machines they can connect from depend on whether you’re using Windows or a Unix-like system, such as Linux or Mac OS X. This section shows you the GRANT statements used to create the default users and explains what they mean in practice.

Linux and Mac OS X

For Linux, Mac OS X, and other Unix variants, the root user can access the server from only the computer hosting the server, specified using the mnemonic localhost, using the IP address 127.0.0.1, or by providing its actual IP address or hostname.

You can see the default configuration by listing the user- and hostnames in the user table of the mysql database:

mysql> SELECT User,Host FROM mysql.user;

+------+---------------------+

| User | Host |

+------+---------------------+

| | localhost |

| root | localhost |

| | ruttle.invyhome.com |

| root | ruttle.invyhome.com |

+------+---------------------+

4 rows in set (0.00 sec)

When the MySQL server is installed, the root user is automatically created with the following GRANT statements:

mysql> SHOW GRANTS for 'root'@'localhost';

+---------------------------------------------------------------------+

| Grants for root@localhost |

+---------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |

+---------------------------------------------------------------------+

1 row in set (0.01 sec)

mysql> SHOW GRANTS for 'root'@'ruttle.invyhome.com';

+-------------------------------------------------------------------------------+

| Grants for root@ruttle.invyhome.com |

+-------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'ruttle.invyhome.com' WITH GRANT OPTION |

+-------------------------------------------------------------------------------+

1 row in set (0.00 sec)

Here, ruttle.invyhome.com is the network name of the localhost system.

The anonymous user can access the server from only the localhost, specified using the mnemonic localhost, using the IP address 127.0.0.1, or by providing the actual IP address or hostname of that machine. The anonymous user has default access to only the test database and those databases beginning with the string test_.

When the MySQL server is installed, the anonymous user is automatically created with the following GRANT statements:

mysql> SHOW GRANTS for ''@'localhost';

+--------------------------------------+

| Grants for @localhost |

+--------------------------------------+

| GRANT USAGE ON *.* TO ''@'localhost' |

+--------------------------------------+

1 row in set (0.00 sec)

mysql> SHOW GRANTS for ''@'ruttle.invyhome.com';

+------------------------------------------------+

| Grants for @hugh.local |

+------------------------------------------------+

| GRANT USAGE ON *.* TO ''@'ruttle.invyhome.com' |

+------------------------------------------------+

1 row in set (0.00 sec)

The anonymous user also has permission to access the test database and databases beginning with test_, effectively as though you’d executed these statements:

mysql> GRANT ALL ON test.* TO ''@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON `test\_%`.* TO ''@'%';

Query OK, 0 rows affected (0.06 sec)

Note that we’ve used backtick symbols (`) on the second line to enclose the table name to prevent the backslash, underscore, and percentage symbols from confusing MySQL.

You can verify that these privileges are in effect by running a SELECT * FROM db; query. However, you can’t explore these privileges with the SHOW GRANTS statement because there’s no matching user ''@'%'. It would be more secure for the default installation to grant privileges for thetest databases to only the local anonymous users ''@'localhost' and ''@'ruttle.invyhome.com', rather than to ''@'%'.

Windows

Current versions of MySQL for Windows come with only the root user defined. You can allow anonymous access by asking the MySQL Windows installer program to create anonymous users. Again, we recommend that you don’t do this.

The root user has permission to access the server only from the localhost machine:

mysql> SELECT User,Host from mysql.user;

+------+-----------+

| User | Host |

+------+-----------+

| root | localhost |

+------+-----------+

1 row in set (0.01 sec)

When the MySQL server is installed, the root user is automatically created with the following GRANT statements:

mysql> SHOW GRANTS for 'root'@'localhost';

+---------------------------------------------------------------------+

| Grants for root@localhost |

+---------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |

+---------------------------------------------------------------------+

1 row in set (0.01 sec)

You can explicitly allow access to other specific users; for example, you can create an anonymous user and allow anonymous access from any host by typing:

mysql> GRANT USAGE on *.* to ''@'';

Securing the Default Users

Now that you understand the default users and from which locations they can access the database server, let’s take steps to secure the users. We recommend that you do the following:

Always set a password for the root user

Choosing and setting a strong password for your administrator user is essential, except in the case where you’re the only user of a machine that is unconnected to a network and contains no valuable information.

Remove privileges for the test databases

Allowing any user to work with the test database and any database beginning with the string test_ is insecure.

Remove anonymous access

Unless you want anyone to be able to connect to your MySQL server, it’s better to allow access only by named users. We therefore recommend that you remove the anonymous users. If you understand and want anonymous access, read the next section, Devising a User Security Policy,” to devise an appropriate access policy.

Remove remote access

Unless there’s a requirement for the server to allow client connections from other machines, it’s better to allow access from only the localhost. If you need remote access, read Devising a User Security Policy” to devise an appropriate access policy.

To perform our recommended steps to secure your server, you need to log in to the monitor as the root user:

$ mysql --user=root --password=the_mysql_root_password

Having connected, set a password for the root user connecting from localhost:

mysql> SET PASSWORD FOR 'root'@'localhost' = password('the_mysql_root_password');

Query OK, 0 rows affected (0.22 sec)

If you’ve already set a password for the root user, this will update it. If you plan to keep other root users who can access the server from other hosts, make sure you add passwords for these, too. If you don’t plan to keep them, don’t worry; our later steps will remove them anyway.

To remove access to the test databases, type the following:

mysql> REVOKE ALL ON test.* FROM ''@'%';

Query OK, 0 rows affected (0.28 sec)

mysql> REVOKE ALL ON `test\_%`.* FROM ''@'%';

Query OK, 0 rows affected (0.16 sec)

You might also want to remove the test database; you’ll almost never need to use it, and removing it leaves one less thing to worry about:

mysql> DROP DATABASE test;

Query OK, 0 rows affected (0.18 sec)

That’s the test issue dealt with.

The next step is to remove anonymous access. You can do this by deleting the accounts that have no username:

mysql> DROP USER ''@'localhost';

Query OK, 0 rows affected (0.27 sec)

mysql> DROP USER ''@'host.domain

Query OK, 0 rows affected (0.00 sec)

Replace host.domain with the server’s fully qualified domain name, such as ruttle.invyhome.com.

Alternatively, you can manually update the grant tables:

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

Query OK, 2 rows affected (0.26 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.20 sec)

We discuss managing privileges with SQL, including the FLUSH PRIVILEGES syntax, later in Managing Privileges with SQL.”

Instead of deleting the anonymous accounts, you can disable unauthenticated access to the server by setting passwords for these accounts:

mysql> UPDATE mysql.user SET Password = PASSWORD('the_new_anonymous_user_password')

-> WHERE User = '';

mysql> FLUSH PRIVILEGES;

This allows authenticated, minimally privileged access to the MySQL server from any host, allowing access to test databases but nothing else. It’s rare for such a setup to be needed, so we recommend you simply remove any anonymous accounts.

The final step we recommend is to remove remote access unless you really need it. Allowing only local connections is more secure. As we explained in Configuring Access to the MySQL Server,” you can increase security even further by telling the server to not accept incoming network connections, and to communicate with clients only through TCP sockets (Linux and Mac OS X) or named pipes (Windows).

Since we’ve removed the anonymous user, the only remaining user is root; we can remove remote access for root with:

mysql> DROP USER 'root'@'host.domain'

Query OK, 0 rows affected (0.00 sec)

Replace host.domain with the server’s fully qualified domain name. For example, if your host was called ruttle.invyhome.com, you would write:

mysql> DROP USER 'root'@'ruttle.invyhome.com'

Again, you can instead manually modify the grant tables; here, you can delete all accounts that have a host other then localhost:

mysql> DELETE FROM mysql.user WHERE Host <> 'localhost';

Query OK, 1 row affected (0.26 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.20 sec)

Removing users makes us nervous, especially when a wrong keystroke in a DELETE statement can remove all your users; you can even remove the root account! If you make a mistake, you should restore the files for the mysql database (in the mysql directory of the data directory) from your backups. On a Linux or Mac OS X system, you can also restore the default users with the mysql_install_db script; simply run this command the same way you ran it in Chapter 2. We look at backups and recovery in Chapter 10.

Devising a User Security Policy

You now understand the principles of creating, maintaining, and removing users and their privileges. In this section, we show you how to take those basics and put them together to develop a security policy and a maintainable, flexible, secure MySQL installation. Importantly, we also show you how to balance server performance against security, and develop the thinking that’ll allow you to effectively manage your MySQL server.

Flexibility and security are enemies. The most secure MySQL installation has no users with no privileges. The most flexible installation lets everyone in as root, in case they need to administer or change the server or its databases. Balancing security and flexibility is important: you should have sufficient users and privileges to permit the user requirements of the applications you develop, but you should constrain those users and privileges to the minimal set that’s needed. The next section walks you through a checklist of decisions you should make in setting up your users and their privileges.

Choosing Users and Privileges

To begin, you should decide whether you’ll have a “default allow” or “default deny” philosophy. In the “default allow” philosophy, you decide on all of the users you might need and grant them all privileges. You then explicitly revoke any privileges they don’t need. In the “default deny”philosophy, you decide on the users you must have and create them with no privileges. You then explicitly grant the privileges that these users need. Security experts prefer the “default deny” approach over the “default allow” one, since there’s a smaller chance that you’ll create users or privileges that make your server insecure. Developers tend to prefer the “default allow” approach, since you only need to think about the few things that you don’t want to happen, rather than the larger set of things you do want to allow. We recommend that you use the “default deny”approach during production, but the “default allow” approach is acceptable if you’re just experimenting on noncritical data in a relatively secure environment. We now consider the issues you need to consider when creating users and assigning privileges using the “default deny” philosophy:

Clients

From what computers does the database server need to be accessed? Typically, the answer is at least the localhost, where the machine’s security protects accounts that are used to access the database server; if you are authenticated to access the localhost, you must have access to an account on the host, and so must have passed an important security check. What other clients need access? Be as explicit as possible, listing client machines by their IP addresses or full domain names; avoid using the wildcard % where possible. Avoid listing clients that may want access, and limit your choices to only those that must have access; you can always add clients later when you’re sure they’re needed.

Users

Who needs access to the database server? The answer always includes the root user who administers the server. You’ll also need at least one other user who—as we discuss in the next step—has the smallest set of privileges required to work with your database. If possible, partition your database users into those that need more privileges and those that need fewer. For example, can you divide the users into a database administrator user, and then others who need fewer privileges to use the database?

Can you separate the database users from the application users? For example, in a web application, it’s typical that the server scripts manage the users of the application and always access MySQL as a single user themselves. If you have more than one application, we recommend having a different user for each application.

Privileges

What needs to be done? The section Privileges” at the beginning of this chapter lists all of the privileges that are available. The root user has all of the privileges for all databases and the server. Consider which privileges are needed for your database, and what components of the database each is needed for. Are the privileges needed for columns, tables, or for the database? Don’t add any privileges you don’t need, avoid adding privileges you only think you might want, and avoid privileges for parts of the database that you don’t need to access. Try to avoid using the asterisk * wildcard, and, in particular, avoid assigning global privileges with *.* where possible.

Now that you’ve made it through the checklist, you need to consider how the issues are related. For each user, consider which client hosts the user must be able to access the server from. For example, you might decide that root needs access from only localhost, while the user working with the music database needs access from localhost and ruttle.invyhome.com. For each user and host combination, determine the minimal set of privileges that you need.

Let’s try a simple example. Suppose you’re setting up a database server that will manage the music database and be accessed by PHP scripts that run on a web server on the localhost. Let’s run through the checklist:

Clients

Only the localhost needs access.

Users

We need the root user, and one other user that we’ll name musicuser to use in the PHP script.

Privileges

After examining the list at the beginning of the chapter in Privileges,” we identify that the PHP scripts need the following privileges: DELETE, INSERT, SELECT, UPDATE, and LOCK TABLES. We identify that they are needed for all tables in the database.

Now we’re ready to create musicuser.

The first step in setting up our user is to remove all other non-root users and ensure root is allowed access from only the localhost; the steps for this were described earlier in Securing the Default Users.” Then, create musicuser with the following statement (we’re setting the password as 'MiSeCr8'):

mysql> GRANT DELETE, INSERT, SELECT, UPDATE, LOCK TABLES ON music.*

-> TO 'musicuser'@'localhost' IDENTIFIED BY 'MiSeCr8';

Query OK, 0 rows affected (0.28 sec)

Your PHP scripts now have sufficient privileges to access the database.

More Security Tips

The previous section explained a simple philosophy for creating users and privileges. This section lists some basic tips to consider when creating users and privileges. Think very carefully before granting these privileges:

ALTER

The ALTER privilege allows the user to change the structure of databases, permitting operations such as renaming tables, adding and removing columns, and creating and deleting indexes. This can allow the user to change or destroy data; for example, reducing the size of an INT(5)column to an INT(1) destroys four digits of integer precision. Importantly, if you grant ALTER as a global privilege, the user can subvert the privilege-checking process by renaming the mysql database or its tables.

FILE

The FILE privilege allows the user to use statements that read and write disk files, permitting access to potentially sensitive information on the server and allowing the user to write large files. In practice, the user can only read and write files to which the server has access; this includes all world-readable files and any file in the database directories. Fortunately, existing files can’t be overwritten, but this is still a powerful privilege.

CREATE, DROP, and INDEX

The CREATE and DROP privileges allow the user to create and delete databases, tables, and indexes. At a global level, these privileges pose the same security problems as ALTER. At a database and table level, they allow destruction of data and indexes. The INDEX privilege is a subset ofCREATE, allowing only the key-creation feature; you should limit access to this privilege too, since a user could add unnecessary indexes that slow down the operation of your database server.

GRANT OPTION

This privilege allows one user to pass on privileges to another. In practice, only administrators should grant privileges, and you should avoid allowing other users to do so. A particular problem can occur if one user shares his privileges with another; the user receiving additional privileges will obviously end up with more than he was initially granted—and perhaps more than he’s supposed to have.

PROCESS

This allows the user to view current processes, including the statements that started them. In practice, this means that the user can view databases and tables being created and changed and, importantly, statements that create users and their passwords.

SHUTDOWN

This allows a user to stop the server.

You should avoid granting any privileges on the special mysql database. This is a default part of any MySQL installation that stores user privileges. Nobody other than the MySQL root user should be able to be read, change, or delete information in this database.

Avoid granting access to anonymous users. You should instead require that all users be explicitly identified, along with the hosts they can connect from and the databases that they can access.

Choose good passwords: always specify passwords when creating users, and ensure these passwords meet the basic criteria of being hard to guess while remaining straightforward to remember.

Finally, use secure remote connections: if you allow remote access to the MySQL server, require that these connections be encrypted. We don’t discuss how to do this, but you’ll find more detail under the heading “Using Secure Connections” in the MySQL manual.

Resource-Limit Controls

MySQL 4.0.2 added new resource-limit controls for users. These are maintained along with the global privileges, and affect users rather than client connections. With these controls, you can limit:

§ The number of SQL statements per hour, using the MAX_QUERIES_PER_HOUR clause. All statements executed by a user are counted toward this limit.

§ The number of updates per hour, using the MAX_UPDATES_PER_HOUR clause. Any statement that modifies a database or its tables counts toward this limit.

§ The number of connections per hour, using the MAX_CONNECTIONS_PER_HOUR clause. Any connection, from the monitor, a program, or a web script, counts toward this limit.

These clauses can be added to a GRANT statement, or you can set them manually using SQL as discussed later in Managing Privileges with SQL.”

For example, to set limits for the existing user 'partmusic'@'localhost', giving this user a maximum of 100 queries per hour, 10 updates, and 5 connections, you’d type:

mysql> GRANT USAGE ON *.* to 'partmusic'@'localhost' WITH

-> MAX_QUERIES_PER_HOUR 100

-> MAX_UPDATES_PER_HOUR 10

-> MAX_CONNECTIONS_PER_HOUR 5;

Query OK, 0 rows affected (0.06 sec)

Since we’ve used USAGE, the privileges aren’t affected when the new limits are imposed.

After imposing these limits, you’ll find an error message is returned when you exceed them. For example, after running and quitting the monitor five times in succession, you’ll see this:

$ mysql --user=partmusic

ERROR 1226 (42000): User 'partmusic' has exceeded the 'max_connections'

resource (current value: 5)

Remember, these limits apply per user and not per connection. If you start two separate instances of the MySQL monitor client and log in to the server as the same user, both connections contribute towards reaching the user’s hourly limits.

Another useful parameter to manage the MySQL server load is the MAX_USER_CONNECTIONS option. This limits the number of simultaneous clients that can access the server and is usually set when you start mysqld or in an options file. We discuss options files in Chapter 11.

The mysql_setpermission Program

mysql_setpermission is an interactive program that allows you to choose from a menu of routine database and user administration tasks, such as creating a database, setting a user password, and modifying user privileges. The program menu is shown here:

$ mysql_setpermission --user=root --password=the_mysql_root_password

######################################################################

## Welcome to the permission setter 1.3 for MySQL.

## made by Luuk de Boer

######################################################################

What would you like to do:

1. Set password for an existing user.

2. Create a database + user privilege for that database

and host combination (user can only do SELECT)

3. Create/append user privilege for an existing database

and host combination (user can only do SELECT)

4. Create/append broader user privileges for an existing

database and host combination

(user can do SELECT,INSERT,UPDATE,DELETE)

5. Create/append quite extended user privileges for an

existing database and host combination (user can do

SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,

LOCK TABLES,CREATE TEMPORARY TABLES)

6. Create/append database administrative privileges for an

existing database and host combination (user can do

SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,LOCK TABLES,

CREATE TEMPORARY TABLES,SHOW DATABASES,PROCESS)

7. Create/append full privileges for an existing database

and host combination (user has FULL privilege)

8. Remove all privileges for for an existing database and

host combination.

(user will have all permission fields set to N)

0. exit this program

Make your choice [1,2,3,4,5,6,7,0]:

The program’s very easy to use; for example, let’s choose option number 1 to set the password for the user allmusic connecting from localhost:

Setting a (new) password for a user.

For which user do you want to specify a password: allmusic

Username = allmusic

Would you like to set a password for allmusic [y/n]: y

What password do you want to specify for allmusic: the_password

Type the password again: the_password

We now need to know which host for allmusic we have to change.

Choose from the following hosts:

- localhost

The host please (case sensitive): localhost

The following host will be used: localhost.

######################################################################

That was it ... here is an overview of what you gave to me:

The username : allmusic

The host : localhost

######################################################################

Are you pretty sure you would like to implement this [yes/no]: yes

Okay ... let's go then ...

The password is set for user 'allmusic'.

The mysql_setpermission program is a Perl script; it should run on a Linux or Mac OS X system, but for a Windows system you will need to follow the instructions of Installing Perl modules under Windows” in Chapter 2. Using the monitor approach is more portable, since you can use it even when mysql_setpermission isn’t installed on a system, or where you have limited access to the server, such as on a server run by a hosting company.

Only users who have access to the mysql database can use the mysql_setpermission command. Usually, only the MySQL root user has this access; if you try using the script as a user who doesn’t have access privileges for the mysql database, you’ll get an “Access denied” message:

$ mysql_setpermission --user=unprivileged_username

Password for user unprivileged_username to connect to MySQL:

Can't make a connection to the mysql server.

The error: Access denied for user 'unprivileged_username'@'localhost' to

database 'mysql' at /usr/bin/mysql_setpermission line 70, <STDIN> line 1.

Managing Privileges with SQL

MySQL privileges are managed in five tables in the mysql database. You can manage this database yourself, using queries to manage users and privileges rather than using the GRANT and REVOKE statements. It’s useful to know how to do this, because it can save you time and allow you to access features that aren’t available through GRANT and REVOKE. This section explains how the privileges are managed and shows you how to modify them directly.

The privileges are managed in the mysql database. As we’ve discussed previously, only administrators should have access to this database and, therefore, you’ll usually need to log in as the root user to follow the steps in this section. In MySQL 5.0, the database contains 17 tables, but only 5 are relevant to privileges: user, db, tables_priv, columns_priv, and host.

The user Table

The user table manages users and global privileges. Its structure is straightforward, even though it has around 30 columns. Each row includes a User, Password, and Host column; these are the credentials that are used to match against connection attempts and authenticate users. All three are optional; the User and Password values are optional because MySQL includes support for anonymous access and because it’s possible for a user to not have a password (although this isn’t recommended). We explain why the Host value is optional later in this section. Each row also contains a Y or N for each possible privilege—for example, Select_priv and Alter_priv might be set to Y and N, respectively—and other values associated with the user; we explain the other parameters that can be set for users later.

Let’s consider an example. Suppose you issue the statement:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES ON *.*

-> TO 'fred'@'localhost' IDENTIFIED BY '4fgh6!aa';

Query OK, 0 rows affected (0.19 sec)

This creates a row in the User table of the mysql database. Select this database, and list the table rows for fred; here’s the output, modified so that it fits on this page:

mysql> USE mysql

Database changed

mysql> SELECT * FROM user WHERE User = 'fred';

+-----------+------+-------------------------------------------+-------------+...

| Host | User | Password | Select_priv |...

+-----------+------+-------------------------------------------+-------------+...

| localhost | fred | *8325B39F81993E24AC6802CD33722DB8B1D64C21 | Y |...

+-----------+------+-------------------------------------------+-------------+...

...+-------------+-------------+-------------+-------------+-----------+...

...| Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv |...

...+-------------+-------------+-------------+-------------+-----------+...

...| Y | Y | Y | N | N |...

...+-------------+-------------+-------------+-------------+-----------+...

...+-------------+---------------+--------------+-----------+------------+...

...| Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv |...

...+-------------+---------------+--------------+-----------+------------+...

...| N | N | N | N | N |...

...+-------------+---------------+--------------+-----------+------------+...

...+-----------------+------------+------------+--------------+------------+...

...| References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv |...

...+-----------------+------------+------------+--------------+------------+...

...| N | N | N | N | N |...

...+-----------------+------------+------------+--------------+------------+...

...+-----------------------+------------------+--------------+...

...| Create_tmp_table_priv | Lock_tables_priv | Execute_priv |...

...+-----------------------+------------------+--------------+...

...| N | Y | N |...

...+-----------------------+------------------+--------------+...

...+-----------------+------------------+------------------+----------------+...

...| Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv |...

...+-----------------+------------------+------------------+----------------+...

...| N | N | N | N |...

...+-----------------+------------------+------------------+----------------+...

...+---------------------+--------------------+------------------+----------+...

...| Create_routine_priv | Alter_routine_priv | Create_user_priv | ssl_type |...

...+---------------------+--------------------+------------------+----------+...

...| N | N | N | |...

...+---------------------+--------------------+------------------+----------+...

...+------------+-------------+--------------+---------------+-------------+...

...| ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates |...

...+------------+-------------+--------------+---------------+-------------+...

...| | | | 0 | 0 |...

...+------------+-------------+--------------+---------------+-------------+...

...+-----------------+----------------------+

...| max_connections | max_user_connections |

...+-----------------+----------------------+

...| 0 | 0 |

...+-----------------+----------------------+

1 row in set (0.00 sec)

You can see that the password is encrypted using the PASSWORD() function, and that all privileges are N except for the four simple privileges we’ve granted. If you create a user with no global privileges—because the privileges you grant are for a database, tables, or columns—you’ll find that all privileges in the user table are set to N.

The user table is used to authenticate connections, as well as store global privileges. If a connection’s parameters—its username, password, and host—don’t match an entry in the user table, then the user isn’t authenticated and it doesn’t matter what privileges are available in the other four tables. If the parameters do match, then the user is allowed access to the MySQL server, and her privileges are a combination of those in the five privilege tables. There’s no requirement for an exact match between the parameters and the user table for authentication because a blank username allows anonymous access from a host, and the hostname column can contain wildcards.

The db Table

When you grant privileges for a particular database, they are stored in the db table of the mysql database. The table is similar to the user table but stores privilege values for Host, Db, and User combinations. Consider what happens when you grant 'bob'@'localhost' privileges for themusic database:

mysql> GRANT SELECT, INSERT, DELETE on music.*

-> TO 'bob'@'localhost';

Query OK, 0 rows affected (0.00 sec)

You’ll now see these privileges in the db table:

mysql> SELECT * FROM db WHERE User = 'bob';

+-----------+-------+------+-------------+-------------+-------------+...

| Host | Db | User | Select_priv | Insert_priv | Update_priv |...

+-----------+-------+------+-------------+-------------+-------------+...

| localhost | music | bob | Y | Y | N |...

+-----------+-------+------+-------------+-------------+-------------+...

...+-------------+-------------+-----------+------------+-----------------+...

...| Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv |...

...+-------------+-------------+-----------+------------+-----------------+...

...| Y | N | N | N | N |...

...+-------------+-------------+-----------+------------+-----------------+...

...+------------+------------+-----------------------+------------------+...

...| Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv |...

...+------------+------------+-----------------------+------------------+...

...| N | N | N | N |...

...+------------+------------+-----------------------+------------------+...

...+------------------+----------------+---------------------+...

...| Create_view_priv | Show_view_priv | Create_routine_priv |...

...+------------------+----------------+---------------------+...

...| N | N | N |...

...+------------------+----------------+---------------------+...

...+--------------------+--------------+

...| Alter_routine_priv | Execute_priv |

...+--------------------+--------------+

...| N | N |

...+--------------------+--------------+

1 row in set (0.08 sec)

Again, we’ve modified the output so it fits in the book.

The tables_priv Table

The tables_priv table stores privileges for the table level. This is similar to the db table but holds privilege values for Host, Db, User, and Table_name combinations. Consider what happens when you grant 'bob'@'localhost' the INDEX privilege for the artist table in the musicdatabase:

mysql> GRANT INDEX on music.artist TO 'bob'@'localhost';

Query OK, 0 rows affected (0.00 sec)

A SELECT statement shows the effect:

mysql> SELECT * FROM tables_priv WHERE User = 'bob';

+-----------+-------+------+------------+----------------+...

| Host | Db | User | Table_name | Grantor |...

+-----------+-------+------+------------+----------------+...

| localhost | music | bob | artist | root@localhost |...

+-----------+-------+------+------------+----------------+...

... +---------------------+------------+-------------+

... | Timestamp | Table_priv | Column_priv |

... +---------------------+------------+-------------+

... | 2006-08-21 10:03:18 | Index | |

... +---------------------+------------+-------------+

1 row in set (0.16 sec)

The structure is a little different from the other tables: the tables_priv table includes who granted the privilege and when it was granted, and it explicitly lists the table privileges in the Table_priv column.

The Column_priv column in the tables_priv table lists privileges that are available only at column level for the user. Consider what happens if we grant 'bob'@'localhost' the UPDATE privilege for the album_name column on the album table:

mysql> GRANT UPDATE (album_name) ON music.album TO 'bob'@'localhost';

Query OK, 0 rows affected (0.12 sec)

Here’s the result:

mysql> SELECT * FROM tables_priv WHERE User = 'bob';

+-----------+-------+------+------------+----------------+...

| Host | Db | User | Table_name | Grantor |...

+-----------+-------+------+------------+----------------+...

| localhost | music | bob | artist | root@localhost |...

| localhost | music | bob | album | root@localhost |...

+-----------+-------+------+------------+----------------+...

... +---------------------+------------+-------------+

... | Timestamp | Table_priv | Column_priv |

... +---------------------+------------+-------------+

... | 2006-08-21 10:03:18 | Index | |

... | 2006-08-21 10:12:36 | | Update |

... +---------------------+------------+-------------+

2 rows in set (0.25 sec)

You can see that that the Update privilege is now available on at least one of the columns of the album table, but the tables_priv table doesn’t show which column or columns.

The columns_priv Table

The columns_priv table lists which privileges are available for which columns. It’s only accessed if the tables_priv table says that a privilege is available for one or more columns in a table and that privilege isn’t already available at the table level. We’ve granted UPDATE to'bob'@'localhost' for the album_name column in the album table. Here’s what’s stored:

mysql> SELECT * FROM columns_priv WHERE User = 'bob';

+-----------+-------+------+------------+-------------+...

| Host | Db | User | Table_name | Column_name |...

+-----------+-------+------+------------+-------------+...

| localhost | music | bob | album | album_name |...

+-----------+-------+------+------------+-------------+...

... +---------------------+-------------+

... | Timestamp | Column_priv |

... +---------------------+-------------+

... | 2006-08-21 10:12:36 | Update |

... +---------------------+-------------+

1 row in set (0.07 sec)

The table structure is much the same as tables_priv, except that it includes the Column_name but no table privileges.

The host Table

The remaining privilege table is host. This table isn’t modified or accessed by the GRANT and REVOKE statements. Therefore, it can be maintained only by SQL queries, and so remains unused in most MySQL installations. Indeed, you can skip this advanced section if you want.

The server verifies that users have authorization to perform an operation by checking the global privileges listed for them in the user table. If they don’t have the required privilege for all databases, then the server checks the db table to see whether they have that privilege for the active database. If the Host field in the db table is blank, the user’s privileges for the database vary depending on the host they’re connecting from. These privileges are stored in the host table and are verified against the global settings in the db table to determine the privileges for a database when it’s accessed from a client or range of clients. For example, you could specify that users connecting from outside the company network do not have the privilege to delete data or drop tables.

We’ll explain how the host table works through an example. Suppose you’ve decided to create a MySQL user for Sam (with the username sam), but you want to allow him to access the music database from different locations, but with different privileges. Assume there are three scenarios you want to implement. First, when sam accesses the server from the localhost, you want him to have all privileges for the database except GRANT OPTION. Second, when he accesses the server from anywhere else on your network subnet—which is all machines matching 192.168.1.%—you want him to have all simple non-administrator privileges. Last, when he connects from anywhere else, you want him to have the SELECT privilege only. You know how to do this by creating three users that that have access to music.*: 'sam'@'localhost','sam'@'192.168.1.%, and 'sam'@'%'. However, using the host table, you can instead create just one user.

Here’s how you create one user with two or more host specifications. First, you add the user account with a superset of the privileges you want on music.*:

mysql> GRANT ALL ON music.* TO 'sam'@'' IDENTIFIED BY 'p^R5wrD';

Notice that we’ve given the privileges to 'sam'@'', which sets the Host column value to the empty string; don’t use just 'sam' because this is the same as 'sam'@'%'. We’ve also set this user’s password to 'p^R5wrD'.

We’ve created an account that allows Sam to log in to the MySQL server from any host, and he now has all privileges for the music database. Let’s now create the entries in the host database that allow and restrict his access depending on the client from which he connects. To begin, let’s create an entry for the localhost that doesn’t restrict his privileges at all. To do this, you need to understand the structure of the host table:

mysql> DESCRIBE host;

+-----------------------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------------------+---------------+------+-----+---------+-------+

| Host | char(60) | | PRI | | |

| Db | char(64) | | PRI | | |

| Select_priv | enum('N','Y') | | | N | |

| Insert_priv | enum('N','Y') | | | N | |

| Update_priv | enum('N','Y') | | | N | |

| Delete_priv | enum('N','Y') | | | N | |

| Create_priv | enum('N','Y') | | | N | |

| Drop_priv | enum('N','Y') | | | N | |

| Grant_priv | enum('N','Y') | | | N | |

| References_priv | enum('N','Y') | | | N | |

| Index_priv | enum('N','Y') | | | N | |

| Alter_priv | enum('N','Y') | | | N | |

| Create_tmp_table_priv | enum('N','Y') | | | N | |

| Lock_tables_priv | enum('N','Y') | | | N | |

+-----------------------+---------------+------+-----+---------+-------+

14 rows in set (0.21 sec)

You can see it has an entry for a Host and a Db, as well as the usual table-level privileges. Now, let’s add an entry for localhost that allows all privileges:

mysql> INSERT INTO host VALUES ('localhost', 'music',

-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

Query OK, 1 row affected (0.00 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.29 sec)

From now on, Sam can access the server as 'sam'@'localhost' and have all privileges for the music database. We explain how this works next.

When Sam connects as 'sam'@'localhost', the following steps occur:

1. The row in the user table for Sam is matched against the user and password credentials, giving him access to the server.

2. Global privileges are added to Sam’s permissions.

3. The db table is searched for entries that match the user sam; there’s one matching entry for the music database that has a blank Host column.

4. The host table is searched for any specific entries for the database music; in this case, there’s one row with a Host value of localhost. We now have two sets of privileges for the music database: privileges for Sam, and privileges for localhost.

5. Last, the intersection—the logical AND—of the privileges is computed, and these are added to the global permissions for Sam’s connection; if either or both rows have an N for a privilege, then that privilege is denied unless it was granted globally. In this case, since Sam has all privileges in both rows except GRANT OPTIONS (he doesn’t have it for the db table row), then Sam has those privileges for accessing the music database.

We still need to configure access so that Sam has the appropriate access privileges from other machines on our network subnet and from the Internet. To configure for the network subnet, we add the following entry to the host table:

mysql> INSERT INTO host VALUES ('192.168.1.%','music',

-> 'Y','Y','Y','Y','N','N','N','N','N','N','N','Y');

Query OK, 1 row affected (0.21 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.29 sec)

When Sam connects from the local network, he has only the SELECT, INSERT, UPDATE, DELETE, and LOCK TABLES privileges, since the intersection of this row and his row in the db table yields a Y for only those privileges.

To configure his access for elsewhere on the Internet, we add:

mysql> INSERT INTO host VALUES ('%', 'music',

-> 'Y','N','N','N','N','N','N','N','N','N','N','N');

Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.29 sec)

This limits Sam’s access to the SELECT privilege when he accesses the server from anywhere but localhost and our local subnet.

The host table allows you to configure client access controls. For example, using the wildcard % in the Db column, you can control access for a client to all databases on the server. Suppose you want to forbid access from your web server machine, 192.168.1.200. To do this, you add this entry to the host table:

mysql> INSERT INTO host VALUES ('192.168.1.200', '%',

-> 'N','N','N','N','N','N','N','N','N','N','N','N');

Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.29 sec)

All database privileges are denied to connections from the web server, as long as no db table rows specify it as a valid host, and global privileges are not granted.

Activating Privileges

We’ve used SQL statements to manipulate the host table in the previous section, and prior to that in The Default Users” as a shortcut for removing users. You’ll find this is useful: it’s sometimes easier to apply an SQL statement to all rows in a table, or join tables, rather than apply successive GRANT and REVOKE statements.

You’ve also seen that whenever we manipulate the mysql database with SQL statements, we run the FLUSH PRIVILEGES statement afterward. This clears MySQL’s internal privilege cache, causing it to reread the current privileges and cache any recent updates. If you don’t do this, your privilege changes won’t appear until you restart the server or carry out a GRANT or REVOKE statement that affects the same privilege table. You must remember to run FLUSH PRIVILEGES after any privilege or user modifications are performed with SQL statements; you don’t need to useFLUSH PRIVILEGES with GRANT or REVOKE, as the server does this for you automatically.

You may also have wondered when exactly privilege changes with GRANT and REVOKE take effect on a current connection. Any change you make at the column or table level takes effect when you run the next statement. Changes at the database level take effect when you next choose a database. Changes at the global level—including password changes—take effect when the specified user next connects. Finally, all changes take effect immediately if you stop and restart the server.

Privileges and Performance

MySQL’s user and privilege management gives you fine-grain control over who has access to which parts of the server and its databases, as well as what that access allows. However, this fine-grained control comes at a price: when you implement complex user and privilege settings, checking these for each SQL statement you execute adds a performance penalty.

When you choose your users and their privileges, you should strive to balance control and performance. Here are some basic tips:

§ Keep it simple. If you follow the “default deny” philosophy, you’ll create only the users you need; avoid creating users whom you only think you might want in the future.

§ Grant the privilege as high up the hierarchy as possible. For example, if you want to grant a privilege for all tables in a database, grant it for the database instead. Avoid using column and table privileges unless you really need them.

§ Minimize your use of the host table.

Remember, the more comparisons required to determine permissions, the slower each query will run on your server. However, don’t compromise your security policy for the sake of performance; a server that has been rendered unusable by an attack has zero performance!

Resetting Forgotten MySQL Passwords

If you’ve forgotten a MySQL user password, you can log in to the server as the MySQL root user and update the password manually. If you’ve forgotten the root password, you’ll need to stop the server and restart it in a special way to allow you to change the root password.

The server is normally stopped with the mysqladmin shutdown command, but you can’t use this command if you’ve forgotten the MySQL root user password. Instead, you’ll need to use the /etc/init.d/mysql or /etc/init.d/mysqld script under Linux, theMySQL_Directory/scripts/mysql.server under Linux or Mac OS X, the MySQL preferences pane under Mac OS X, or the Windows Services window to shut down the server. If none of these are available, you can forcibly end or “kill” the server process, though this is not recommended since it can lead to data loss. You will need to have sufficient authorization to kill the server process, so you should be logged in under the same username the server is running under—for example, mysql, or your own account if the server is running under your username—or as a superuser (system root or Windows administrator).

To kill the server under Linux or Mac OS X, you should first determine the process identifier (or PID) of the server process. The PID is normally stored under the server data directory in a file with the extension .pid. You can list the contents of this file using the cat command and enclose the command in backtick symbols (`) to pass the output directly to the kill command:

$ kill `cat MySQL_Directory/data/your_host_name.pid`

An example of a command to kill a server running from the /usr/local/mysql directory would be:

$ kill `cat /usr/local/mysql/data/localhost.pid`

If you specified a custom PID file location with the pid-file option, you’ll need to specify the same location here.

To kill the server under Windows, press the Ctrl-Alt-Del keys together to open the Task Manager, select the mysqld-nt.exe entry under the Processes tab, and click on the End Process button.

Once the server is stopped, you need to restart it and change the database root user password. There are two approaches that you can use. First, you can open a text editor and create a text file containing an SQL command to update the database root user password:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('the_mysql_root_password');

Save this file under the name reset_root_password.sql. Now, you need to start the MySQL server with the init-file option, telling the server to execute the commands in this file:

$ mysqld_safe --init-file=path_to_the_init_file &

For example, on a Linux or Mac OS X system, with the file in the ~/tmp directory, you would write:

$ mysqld_safe --init-file=~/tmp/reset_root_password.sql &

while on a Windows system with the file in the C:\ directory, you would write:

C:\> mysqld-nt --init-file=C:\reset_root_password.sql &

Note that the server should have permission to access and read this file. If the server can’t access the file, it will refuse to start and write a message such as this one in the server log:

051009 22:12:01 [ERROR] /usr/sbin/mysqld: File '/home/adam/tmp/reset_root_passwrd.sql'

not found (Errcode: 13)

Here, the name of the initialization file has been mistyped. Once you’ve started the server successfully, you should shut it down and start it again normally without the init-file option. You should then carefully delete the text file containing the password. If you drag and drop the file to the system Trash can or Recycle Bin, empty this so that the file can’t be easily recovered. Even better, you should use a file-wiping tool such as shred (under Linux and Mac OS X) or Eraser (under Windows) that ensures that sensitive files—and any temporary or backup files created by your text editor—are destroyed when they are deleted.

An alternative approach is to start the server using the skip-grant-tables option. This tells the server not to check user passwords and access levels:

$ mysqld_safe --skip-grant-tables

Now, anyone can have access to the server with maximum privileges and without a password. Be very careful—it’s wise to disconnect your system from the network while you’re doing this! Connect to the server with the MySQL monitor program:

$ mysql

(You don’t need to specify any user or password, since without the grant tables, MySQL can’t enforce any authentication). Then immediately re-enable the grant tables so that the authentication details will be checked if anyone else tries to connect to the server:

mysql> FLUSH PRIVILEGES;

You can then reset the root user password using the SQL query:

mysql> UPDATE mysql.user SET Password = PASSWORD('new_password') WHERE User = 'root';

or alternatively:

mysql> SET PASSWORD for 'root'@'localhost'=PASSWORD('the_new_mysql_root_password');

Now, tell MySQL to put the new privileges into effect:

mysql> FLUSH PRIVILEGES;

and exit the monitor:

mysql> QUIT

You can now restart the server normally.

Exercises

1. What’s the difference between a local and a remote user?

2. When would you grant only read access to a user?

3. Write a GRANT statement to create a user, rowena, who has privileges to execute SELECT, UPDATE, and INSERT statements on the contacts and appointment databases. The user should be allowed to access the server from machines in the domain invyhome.com.

4. Write a GRANT statement that modifies the privileges of the user rowena created in Question 3. Add privileges to SELECT from the customer table in the sales database, and to SELECT the debtor column from the invoice table in the accounts database.

5. Three GRANT statements have been issued on your MySQL server:

6. GRANT ALL ON *.* TO 'hugh'@'hugh.invyhome.com';

7. GRANT SELECT, UPDATE, INSERT, DELETE ON *.* TO 'hugh'@'*.invyhome.com';

GRANT SELECT ON *.* TO ''@'localhost';

For each of the following attempts to connect to the server, state whether the connection is allowed and, if so, which user the client is connected as. Assume all connections are attempted from localhost:

o mysql --user=hugh --host=localhost

o mysql --user=fred

o mysql

8. You’ve been employed to evaluate the security of a MySQL installation. Assuming that you’re already satisfied with the security configuration from the physical and operating system perspective, list four things that you’d check about the MySQL server. For each item, explain why you would check it and what you would expect the outcome to be.

9. You’ve recently installed a wireless access point for visitors to your office and configured it so that machines that connect through it have IP addresses in the range 192.168.1.1 to 192.168.1.254. You’ve decided you want users who connect to your MySQL server from those IP addresses to have only the SELECT privilege on the contacts database. What steps do you take in your MySQL privilege tables to set this up?