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.