How the Privilege System Works in Practice

In Chapter 11, "Managing User Privileges," we discussed user account creation, granting and revoking privileges, and the grant tables. We will begin this chapter by discussing how your MySQL server applies the privileges you have granted.

There are two stages to the privilege system. In the first stage, MySQL checks whether a user is allowed to connect to the server at all. The user table in the mysql database is used for this purpose. MySQL looks up your username and password as entered and the host from which you are trying to connect to see whether there is a matching row. If no row matches, you will not be able to connect to the server.

Because the user table supports wildcards in the host column, a user/hostname combination may match more than one row. MySQL determines which row is relevant by matching the most specific hostname first. For example, if there are rows in the table for test from host localhost and user test from host % (meaning any host), then the localhost row will be selected. Note that these two rows can have different passwords. This can cause a great deal of confusion. (We will look at an example of this in the section "Deleting Anonymous Accounts," later in this chapter.)

The second stage applies when you try to execute specific queries or commands. MySQL checks each query against the grant tables before it is executed.

If the query you are trying to execute requires a global privilege ”such as doing a LOAD DATA INFILE or trying to use SHOW PROCESSLIST ”the user table will be checked. For database-specific queries, the user table will be checked first. If the user has the privilege on all databases, this will be sufficient. If not, then the db and host tables are checked. If the user does not have the privilege at this level, then if any table- or column-level privileges are set, these will be checked last.