|
|
< Day Day Up > |
|
10.2 The Grant TablesMySQL'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.
10.2.1 Privilege ChecksFor 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.
Let's look at the schema for each table as well as the privileges each affects. 10.2.2 The user TableMySQL'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 | |
+-----------------------+-------------------------+------+-----+---------+-------+
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 matchingThe 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:
MySQL sorts them in this order:
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:
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 TableThe 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 TableThe 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 TableGoing 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 TableThe 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. |
|
|
< Day Day Up > |
|
