Build Your Own DatabaseDriven Website Using PHP amp;amp; MySQL [Electronic resources]

MySQL Access Control

Early in this book, I mentioned that the database called mysql, which appears on every MySQL server, is used to keep track of users, their passwords, and what they're allowed to do. Until now, however, we've always logged into the server as the root user, which gives us access to all databases and tables.

If your MySQL server will only be accessed through PHP, and you're careful about who is given the password to the root MySQL account, then the root account may be sufficient for your needs. However, in cases where a MySQL server is shared among many users, for example, if a Web host wishes to use a single MySQL server to provide a database to each of its users, it's usually a good idea to set up user accounts with more restricted access.

The MySQL access control system is fully documented in Chapter 6 of the MySQL Reference Manual. In essence, user access is governed by the contents of five tables in the mysql database: user, db, host, tables_priv, and columns_priv. If you plan to edit these tables directly using INSERT, UPDATE, and DELETE statements, I'd suggest you read the section of the MySQL manual on the subject first. For us mere mortals, MySQL provides a simpler method to manage user access. Using GRANT and REVOKE—nonstandard commands provided by MySQL—you can create users and set their privileges without worrying about the details of how they'll be represented in the tables mentioned above.

Using GRANT

The GRANT command, used to create new users, assign user passwords, and add user privileges, looks like this:

mysql>GRANT privileges [(columns)] ON what
    ->TO user [IDENTIFIED BY "password"]
    ->[WITH GRANT OPTION];

As you can see, there are a lot of blanks to be filled in with this command. Let's describe each of them in turn, and then look at some examples to give you an idea of how they work together.

privileges is a comma-separated list of the privileges you wish to grant. The privileges you can specify can be sorted into three groups:

Database/Table/Column privileges

Global administrative privileges

Special privileges

Some of these privileges apply to features of MySQL that we have not yet seen, but many should be familiar to you.

what defines the areas of the database sever to which the privileges apply. *.* means the privileges apply to all databases and tables. dbName.* means the privileges apply to all tables in the database called dbName. dbName.tblName means the privileges apply only to the table called tblName in the database called dbName. You can even specify privileges for individual table columns—simply list the columns between the parentheses that follow the privileges to be granted (we'll see an example of this in a moment).

user specifies the user to which these privileges should apply. In MySQL, a user is specified both by the user name given at login, and the host name/IP address of the machine from which the user connects. The two values are separated by the @ sign (i.e. username@hostname). Both values may contain the % wild card character, but you need to put quotes around any value that does (e.g. kevin@"%" will allow the user name kevin to log in from any host and use the privileges you specify).

password specifies the password required by the user to connect to the MySQL server. As indicated by the square brackets above, the IDENTIFIED BY "password" portion of the GRANT command is optional. Any password specified will replace the existing password for that user. If no password is specified for a new user, a password will not be required to connect.

The optional WITH GRANT OPTION portion of the command specifies that the user be allowed to use the GRANT/REVOKE commands to give to another user any privileges granted to him or her. Be careful with this option—the repercussions are not always obvious! For example, two users who have this option enabled can get together and share their privileges with each other.

Let's consider a few examples. To create a user named dbmgr that can connect from server.host.net with password managedb and have full access to the database named db only (including the ability to grant access to that database to other users), use this GRANT command:

mysql>GRANT ALL ON db.*
    ->TO dbmgr@server.host.net
    ->IDENTIFIED BY "managedb"
    ->WITH GRANT OPTION;

To subsequently change that user's password to funkychicken, use:

mysql>GRANT USAGE ON *.*
    ->TO dbmgr@server.host.net
    ->IDENTIFIED BY "funkychicken";

Notice that we haven't granted any additional privileges (the USAGE privilege doesn't let a user do anything besides log in), but the user's existing privileges remain unchanged.

Now let's create a new user named jess, who will connect from various machines in the host.net domain. Say she's responsible for keeping the names and email addresses of users in the database up to date, but may need to refer to other database information at times. As a result, she will have read-only (i.e. SELECT) access to the db database, but will be able to UPDATE the name and email columns of the Users table. Here are the commands:

mysql>GRANT SELECT ON db.*
    ->TO jess@"%.host.net"
    ->IDENTIFIED BY "jessrules";
mysql>GRANT UPDATE (name,email) ON db.Users
    ->TO jess@"%.host.net";

Notice in the first command how we used the % (wild card) character in the host name to indicate the host from which Jess could connect. Notice also that we haven't given her the ability to pass her privileges to other users, as we didn't put WITH GRANT OPTION on the end of the command. The second command demonstrates how to grant privileges for specific table columns—it lists the column(s) separated by commas in parentheses after the privilege(s) being granted.

Using REVOKE

The REVOKE command, as you'd expect, is used to strip previously granted privileges from a user. The syntax for the command is as follows:

mysql>REVOKE privileges [(columns)]
    ->ON what FROM user;

All the fields in this command work just as they do in GRANT above. To revoke the DROP privileges of a co-worker of Jess's (for instance, if he or she has demonstrated a habit of occasionally deleting tables and databases by mistake), you would use this command:

mysql>REVOKE DROP ON *.* FROM idiot@"%.host.net";

Revoking a user's login privileges is about the only thing that can't be done using GRANT and REVOKE. REVOKE ALL ON *.* would definitely prevent a user from doing anything of consequence besides logging in, but to remove a user completely requires that you delete the corresponding entry in the user table:

mysql>DELETE FROM user
    ->WHERE User="idiot" AND Host="%.host.net";

Access Control Tips

As a result of the way the access control system in MySQL works, there are a couple of idiosyncrasies that you should be aware of before you launch into user creation.

When you create users that can log into the MySQL server only from the computer on which that server is running (i.e. you require them to use Telnet or SSH to log into the server and run the MySQL client from there, or communicate using server-side scripts like PHP), you may ask yourself what the user part of the GRANT command should be. Say the server is running on www.host.net. Should you set up the user as username@www.host.net, or username@localhost?

The answer is that you can't rely on either one to handle all connections. In theory, if, when connecting, the user specifies the host name either with the mysql client, or with PHP's mysql_connect function, that host name will have to match the entry in the access control system. But as you probably don't want to force your users to specify the host name a particular way (in fact, users of the mysql client probably won't want to specify the host name at all), it's best to use a work-around.

For users that need to be able to connect from the same machine on which the MySQL server is running, it's best to create two user entries in the MySQL access system: one with the actual host name of the machine (e.g. username@www.host.net), the other with localhost (e.g. username@localhost). Of course, you will have to grant/revoke all privileges to both of these user entries individually, but this is the only work-around that you can really rely upon.

Another common problem faced by MySQL administrators is that user entries with wild cards in their host names (e.g. jess@%.host.net above) fail to work. When this happens, it's usually due to the way MySQL prioritizes the entries in the access control system. Specifically, it orders entries so that more specific host names appear first (e.g. www.host.net is completely specific, %.host.net is less specific, and % is totally unspecific).

In a fresh installation, the MySQL access control system contains two anonymous user entries (which allow connections from the local host that use any user name—the two entries are to support connections from localhost and the server's actual host name^[2], as described above), and two root user entries. The problem described above occurs when the anonymous user entries take precedence over our new entry because their host name is more specific.

Let's look at the abridged contents of the user table on www.host.net, our fictitious MySQL server, after we add Jess's entry. The rows here are sorted in the order in which the MySQL server considers them when it validates a connection:

+--------------+------+-------------------+
| Host         | User | Password          |
+--------------+------+-------------------+
| localhost    | root | (encrypted value) |
| www.host.net | root | (encrypted value) |
| localhost    |      |                   |
| www.host.net |      |                   |
| %.host.net   | jess | (encrypted value) |
+--------------+------+-------------------+

As you can see, since Jess's entry has the least specific host name, it comes last in the list. When Jess attempts to connect from www.host.net, the MySQL server matches her connection attempt to one of the anonymous user entries (a blank User value matches anyone). Since these anonymous entries don't require a password, and presumably Jess enters her password, MySQL rejects the connection attempt. Even if Jess managed to connect without a password, she would be given the very limited privileges that are assigned to anonymous users, as opposed to the privileges assigned to her entry in the access control system.

The solution to this problem is to either make your first order of business as a MySQL administrator the deletion of those anonymous user entries (DELETE FROM mysql.user WHERE User="), or to give two more entries to all users who need to connect from localhost (i.e. entries for localhost and the actual host name of the server):

+--------------+------+-------------------+
| Host         | User | Password          |
+--------------+------+-------------------+
| localhost    | root | (encrypted value) |
| www.host.net | root | (encrypted value) |
| localhost    | jess | (encrypted value) |
| www.host.net | jess | (encrypted value) |
| localhost    |      |                   |
| www.host.net |      |                   |
| %.host.net   | jess | (encrypted value) |
+--------------+------+-------------------+

Since it's excessive to maintain three user entries (and three sets of privileges) for each user, I recommend that you remove the anonymous users, unless you have a particular need for them:

+--------------+------+-------------------+
| Host         | User | Password          |
+--------------+------+-------------------+
| localhost    | root | (encrypted value) |
| www.host.net | root | (encrypted value) |
| %.host.net   | jess | (encrypted value) |
+--------------+------+-------------------+

Locked Out?

Like locking your keys in the car, forgetting your password after you've spent an hour installing and tweaking a new MySQL server can be an embarrassment to say the least. Fortunately, if you have root access to the computer on which the MySQL server is running, or if you can log in as the user you set up to run the MySQL server (mysqlusr if you followed the instructions in "Installation"), all is not lost. This next procedure will let you regain control of the server.

First, you must shut down the MySQL server. Since you would normally do this using mysqladmin, which requires your forgotten password, you'll instead have to kill the server process to shut it down. Under Windows, use the task manager to find and end the MySQL process, or simply stop the MySQL service if you have installed it as such. Under Linux, use the ps command, or look in the server's PID file, in the MySQL data directory, to determine the process ID of the MySQL server, and then terminate it with this command:

shell%kill pid

pid is the process ID of the MySQL server. This should be enough to stop the server. Do not use kill -9 unless absolutely necessary, as this may damage your table files. If you're forced to do so, however, the next section provides instructions on how to check and repair those files.

Now that the server's down, you can restart it by running safe-mysqld (mysqld-opt.exe, mysqld-nt.exe, or whichever server executable you decided on under Windows) with the --skip-grant-tables command line option. This instructs the MySQL server to allow unrestricted access to anyone. Obviously, you'll want to run the server in this mode as infrequently as possible, to avoid the inherent security risks.

Once you're connected, change your root password to something you'll remember:

mysql>USE mysql;
mysql>UPDATE user SET Password=PASSWORD("newpassword")
    ->WHERE User="root";

Finally, disconnect, and instruct the MySQL server to reload the grant tables to begin requiring passwords:

shell%mysqladmin flush-privileges

That does it—and nobody ever has to know what you did. As for locking your keys in your car, you're on your own there.

^[2]On Windows installations of MySQL, the second entry's hostname is set to %, not the server's hostname. It therefore does not contribute to the problem described here. It does, however, permit connections with any user name from any computer, so it's a good idea to delete it anyway.