Previous Section  < Day Day Up >  Next Section

10.2 The Grant Tables

MySQL's grant tables are the heart of its security system. The information in these tables determines the privileges of every user and host that connects to MySQL. By correctly manipulating the records, you can give users exactly the permissions they need (and no more). Incorrectly manipulating them can open up your server to the possibility of abuse and damage.

Let's take a brief look at the five grant tables before really digging in. We've included them here in the order that MySQL consults them. You'll see why that becomes important in a minute.


user

The user table contains the global privileges and encrypted passwords. It is responsible for determining which hosts and users may connect to the server.


host

The host table assigns privileges on a per-host basis, regardless of the user. When deciding to accept or reject a connection, MySQL consults the user table as noted earlier. Though we list it as a grant table, the host is never modified through use of the GRANT or REVOKE commands. You can add and remove entries manually, however.


db

The db table sets database-level privileges.


tables_priv

The tables_priv table controls table-specific privileges.


columns_priv

Records in the columns_priv table specify a user's privileges for a single column of a single table in a particular database.

10.2.1 Privilege Checks

For each query issued, MySQL checks to make sure the user has the required privileges to perform the query. In doing so, it consults each of the tables in a specific order. Privileges set in one table may be overridden by a table checked later.

In other words, the privilege system works through inheritance. Privileges granted in the user table are passed down through all the other checks. If there are no matching records in any of the other tables, the original privileges set forth in the user table apply.

MySQL uses different criteria when checking each grant table. Records in the host table, for example, are matched based on the host from which the user has connected and the name of the database that the query will read from or write to. Records in the db table, on the other hand, match based on the host, database, and username. Table 10-2 summarizes the fields used for matching records in each of the grant tables.

Table 10-2. Fields used for matching grant table records

Table

Password

User

Host

Db

Table

Column

user

figs/check.gif

figs/check.gif

figs/check.gif

   
host
  

figs/check.gif

figs/check.gif

  
db
 

figs/check.gif

figs/check.gif

figs/check.gif

  
tables_priv
 

figs/check.gif

figs/check.gif

figs/check.gif

figs/check.gif

 
columns_priv
 

figs/check.gif

figs/check.gif

figs/check.gif

figs/check.gif

figs/check.gif

Let's look at the schema for each table as well as the privileges each affects.

10.2.2 The user Table

MySQL's user table contains authentication information about users as well as their global privileges. It contains fields for the username, hostname, and password. The remainder of the fields represent each of the privileges, which are all off by default. As you'll see, many of the other tables also contain the Host and User fields as well as a subset of the privilege fields that are present in the user table, but only the user table contains passwords. In a way, it is the /etc/passwd of MySQL.

Even if a user has no global privileges at all, there must be a record in the user table for her, if she is to issue a command successfully. See the Section 10.3.1, later in this chapter, for an example.

In the meantime, let's have a look at the fields in the user table:

mysql> DESCRIBE user;

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

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

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

| Host                  | varchar(60)             |      | PRI |         |       |

| User                  | varchar(16)             |      | PRI |         |       |

| Password              | varchar(45)             |      |     |         |       |

| 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       |       |

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

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

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

| File_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       |       |

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

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

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

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

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

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

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

| ssl_type                  | enum('','ANY','X509','SPECIFIED')    |         |       |

| ssl_cipher            | blob                    |      |     |         |       |

| x509_issuer           | blob                    |      |     |         |       |

| x509_subject          | blob                    |      |     |         |       |

| max_questions         | int(11) unsigned        |      |     | 0       |       |

| max_updates           | int(11) unsigned        |      |     | 0       |       |

| max_connections       | int(11) unsigned        |      |     | 0       |       |

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

Password Security

Just because MySQL passwords aren't stored in plain text, you shouldn't be lax about password selection. Anyone with the ability connect to your MySQL server can run a brute-force attack against your server in an attempt to discover passwords. A password such as fred or database is worthless; either can be easily guessed by automated software. It is best to invent a password that isn't a real word.

Because choosing strong passwords is an important part of giving users access to MySQL, here are a few guidelines for selecting and keeping good passwords:


Have a minimum length

The longer a password is, the more difficult it will be to guess.


Require special characters

A password that includes nonalphanumeric characters such as !@#$%^&* is more difficult to guess than one composed of numbers and letters only. Substitute the at sign (@) for the letter a. Add punctuation. Be creative.


Change passwords

Once a password is set, many people have a tendency never to change it. Often, a password may be assigned to an account that doesn't even correspond to a real person. It might belong to an application such as a web server, or middleware application. Because of this, MySQL has no built-in password aging mechanism, so you'll need to put a note on your calendar or somehow automate the process of aging passwords.

It's important to note, though, that MySQL doesn't provide any way for an administrator to enforce good password standards. You can't link MySQL against libcrack and demand that passwords meet that criteria, no matter how cool that idea may be. Luckily, most users can't change their own MySQL passwords, so you don't have to worry about them switching to a weak password at a later date, and as long as you (as the administrator) choose a strong password for them, they should be all right.


When a user first connects to MySQL, it checks the user table to decide if the user is allowed to connect and is who she says she is (the password check). But how exactly does MySQL make those decisions?

Matching a username is a simple test of equality. If the username exists in the table, it's a match. The same is true of the password. Because all MySQL passwords are hashed using the built-in PASSWORD( ) function, expect MySQL to do something like this:

SELECT *

  FROM user

 WHERE User = 'username'

   AND Password = PASSWORD('password')

However, this query could return multiple records. The user table's primary key is composed of the fields User and Host, not just User, which means a single user can have multiple entries in the table—especially if she is allowed to connect from several specifically named hosts. MySQL must check all those records to see which one matches.

Things get more interesting when you realize that the Host field may contain any of the standard SQL wildcard characters: _ (matches a single character) and % (matches any number of characters). What does MySQL do if the user jane attempts to connect from the host jane.example.com, and the user table contains records for jane@jane.example.com as well as jane@%.example.com?

10.2.2.1 Host matching

The first rule you need to know about MySQL's privilege system is this: the most specific match always wins. MySQL will always prefer an exact match over one that uses a wildcard of any sort.

MySQL accomplishes this by internally sorting the records in the user table based on the Host and User fields—in that order. Hostnames and IP addresses without wildcards come before those that contain them.

Given a list of host entries such as this:

  • %

  • localhost

  • jane.example.com

  • %.example.com

  • 192.168.1.50

  • joe.example.com

  • 192.168.2.0/255.255.255.0

MySQL sorts them in this order:

  • localhost

  • 192.168.1.50

  • jane.example.com

  • joe.example.com

  • 192.168.2.0/255.255.255.0

  • %.example.com

  • %

To clarify what "most specific" means to MySQL, let's consider how MySQL will match several username and hostname combinations. Assuming that the user jane and the "any user" (represented here as the absence of a username) can connect from some of the various hosts listed earlier, MySQL sorts the entries like this:

  • jane@jane.example.com

  • jane@joe.example.com

  • @localhost

  • @192.168.1.50

  • @jane.example.com

  • @joe.example.com

  • @%.example.com

  • jane@%.example.com

  • jane@%

When jane connects from jane.example.com, she may have a different set of privileges from when she connects from joe.example.com. Other users connecting from web.example.com will match the %@%.example.com record and receive whatever privileges have been granted in that row. When jane connects from web.example.com, she'll receive the privileges granted to jane@%.example.com.

10.2.3 The host Table

The host table assigns database-level privileges for users connecting from specific hosts (or groups of hosts). Let's look at the 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       |       |

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

With the exception of the Db field, this table is a subset of the user table. It is missing all the global privileges (such as the shutdown privilege), but all the privileges that can be applied to a database objects are there. As expected, they all default to No.

Records might appear in this table to enforce a rule that all connections from hosts in the public.example.com domain are forbidden from changing any data. You can also allow anyone connecting from secure.example.com to have full privileges on tables in the security database.

10.2.4 The db Table

The db table specifies database-level privileges for a particular user and database:

mysql> DESCRIBE db;

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

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

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

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

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

| User                  | char(16)      |      | 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       |       |

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

This table is virtually identical to the host table. The only difference is the addition of the User field, which is needed in order to create per-user privileges.

By making the appropriate entries in this table, you could ensure that joe has full privileges on the sales database when connecting from either accounting.example.com or cfo.example.com.

10.2.5 The tables_priv Table

Going a level deeper, the tables_priv table controls table-level privileges (those applied to all columns in a table) for a particular user:

mysql> DESCRIBE tables_priv;

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

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

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

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

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

| User        | char(16) binary     |      | PRI |         |       |

| Table_name  | char(60) binary     |      | PRI |         |       |

| Grantor     | char(77)            |      | MUL |         |       |

| Timestamp   | timestamp(14)       | YES  |     | NULL    |       |

| Table_priv  | set(...)            |      |     |         |       |

| Column_priv | set(...)            |      |     |         |       |

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

This table probably looks a bit odd. The creators of MySQL decided to use a SET( ) function to represent privileges in both the tables_priv and columns_priv tables. In doing so, they made it difficult for authors to present a nice clean listing of all the grant tables in their books (we're sure that wasn't their intent).

The ... in the Table_priv field should actually read:

'Select','Insert','Update','Delete','Create','Drop','Grant'

and the ... in the Column_priv field really contains:

'Select','Insert','Update','References'

Both are new fields not seen in previous tables. As their names imply, they control table and column privileges. There's another new field in the table: Grantor. This 77-character field records the identity of the user who granted these privileges. It is 77 characters in size because it is intended to hold a username (up to 16 characters), an @ symbol, and a hostname (up to 60 characters).

The Timestamp field also makes its first appearance in this table. As you'd expect, it simply records the time when the record was created or modified.

Using table-level privileges isn't very common in MySQL, so don't be surprised if your server has no records in its tables_priv table. If you've installed the popular phpMyAdmin utility (discussed in Appendix C), however, you might see something like this:

mysql> SELECT * FROM tables_priv \G

*************************** 1. row ***************************

       Host: localhost

         Db: mysql

       User: phpmyadmin

 Table_name: user

    Grantor: root@localhost

  Timestamp: 20020308185823

 Table_priv:

Column_priv: Select

This entry grants the phpmyadmin user access to the database, with the Select privileges he needs to obtain information from MySQL. This table doesn't grant privileges on any particular data; that has to be done in another table, as you'll see in the next section.

10.2.6 The columns_priv Table

The final table, columns_priv, is similar to the tables_priv table. It specifies individual column privileges in a particular table:

mysql> DESCRIBE columns_priv;

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

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

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

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

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

| User        | char(16) binary     |      | PRI |         |       |

| Table_name  | char(64) binary     |      | PRI |         |       |

| Column_name | char(64) binary     |      | PRI |         |       |

| Timestamp   | timestamp(14)       | YES  |     | NULL    |       |

| Column_priv | set(...)            |      |     |         |       |

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

Just as in the previous table, the ... in the Column_priv field really contains:

'Select','Insert','Update','References'

Column-level privileges also aren't very common in MySQL. But there are cases when you're likely to encounter them. Again, phpMyAdmin is a great example:

mysql> SELECT * FROM columns_priv LIMIT 1 \G

*************************** 1. row ***************************

       Host: localhost

         Db: mysql

       User: phpmyadmin

 Table_name: tables_priv

Column_name: Column_priv

  Timestamp: 20020308185830

Column_priv: Select

This record allows the phpmyadmin user to select data from the Column_priv column of the tables_priv table in the mysql database.

Confused yet? Can't blame you. The grant tables can be quite confusing at first. Until you spend some time working with them, you won't really appreciate the flexibility this design provides.

We wouldn't recommend spending that time unless absolutely necessary. Instead, read the next section. It reviews the GRANT and REVOKE commands and then looks at how they interact with the grant tables so that you don't have to. It's only worth delving deeply into the grant tables if you find a situation that can't be set up (or is too complex) using the GRANT command.

    Previous Section  < Day Day Up >  Next Section