MySQL - How the Privilege System Works

Interview Questions

MySQL and SQL

MySQL - How the Privilege System Works

The MySQL privilege system ensures that all users may do exactly the things that they are supposed to be allowed to do. When you connect to a MySQL server, your identity is determined by the host from which you connect and the user name you specify. The system grants privileges according to your identity and what you want to do.

MySQL considers both your hostname and user name in identifying you because there is little reason to assume that a given user name belongs to the same person everywhere on the Internet. For example, the user bill who connects from whitehouse.gov need not be the same person as the user bill who connects from microsoft.com. MySQL handles this by allowing you to distinguish users on different hosts that happen to have the same name: you can grant bill one set of privileges for connections from whitehouse.gov, and a different set of privileges for connections from microsoft.com.

MySQL access control involves two stages:

Stage 1: The server checks whether or not you are even allowed to connect.
Stage 2: Assuming you can connect, the server checks each request you issue to see whether or not you have sufficient privileges to perform it. For example, if you try to select rows from a table in a database or drop a table from the database, the server makes sure you have the select privilege for the table or the drop privilege for the database. The server uses the user, db, and host tables in the mysql database at both stages of access control. The fields in these grant tables are shown below:

Table name user db host
Scope fields Host Host Host
User Db Db
Password User
Privilege fields Select_priv Select_priv Select_priv
Insert_priv Insert_priv Insert_priv
Update_priv Update_priv Update_priv
Delete_priv Delete_priv Delete_priv
Index_priv Index_priv Index_priv
Alter_priv Alter_priv Alter_priv
Create_priv Create_priv Create_priv
Drop_priv Drop_priv Drop_priv
Grant_priv Grant_priv Grant_priv
References_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv

For the second stage of access control (request verification), the server may, if the request involves tables, additionally consult the tables_priv and columns_priv tables. The fields in these tables are shown below:

Table name tables_priv columns_priv
Scope fields Host Host
Db Db
User User
Table_name Table_name
Column_name
Privilege fields Table_priv Column_priv
Column_priv
Other fields Timestamp Timestamp
Grantor

Each grant table contains scope fields and privilege fields.

Scope fields determine the scope of each entry in the tables, that is, the context in which the entry applies. For example, a user table entry with Host and User values of 'thomas.loc.gov' and 'bob' would be used for authenticating connections made to the server by bob from the host thomas.loc.gov. Similarly, a db table entry with Host, User, and Db fields of 'thomas.loc.gov', 'bob' and 'reports' would be used when bob connects from the host thomas.loc.gov to access the reports database. The tables_priv and columns_priv tables contain scope fields indicating tables or table/column combinations to which each entry applies.

For access-checking purposes, comparisons of Host values are case insensitive. User, Password, Db, and Table_name values are case sensitive. Column_name values are case insensitive in MySQL Version 3.22.12 or later.

Privilege fields indicate the privileges granted by a table entry, that is, what operations can be performed. The server combines the information in the various grant tables to form a complete description of a user's privileges.

Scope fields are strings, declared as shown below; the default value for each is the empty string:

Field name Type
Host CHAR(60)
User CHAR(16)
Password CHAR(16)
Db CHAR(64) (CHAR(60) for the tables_priv and columns_priv tables)
Table_name CHAR(60)
Column_name CHAR(60)

In the user, db and host tables, all privilege fields are declared as ENUM('N','Y') -- each can have a value of 'N' or 'Y', and the default value is 'N'.

In the tables_priv and columns_priv tables, the privilege fields are declared as SET fields:

Table name Field name Possible set elements
tables_priv Table_priv 'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'
tables_priv Column_priv 'Select', 'Insert', 'Update', 'References'
columns_priv Column_priv 'Select', 'Insert', 'Update', 'References'

Briefly, the server uses the grant tables like this:

The user table scope fields determine whether to allow or reject incoming connections. For allowed connections, any privileges granted in the user table indicate the user's global (superuser) privileges. These privileges apply to all databases on the server.
The db and host tables are used together:
The db table scope fields determine which users can access which databases from which hosts. The privilege fields determine which operations are allowed.
The host table is used as an extension of the db table when you want a given db table entry to apply to several hosts. For example, if you want a user to be able to use a database from several hosts in your network, leave the Host value empty in the user's db table entry, then populate the host table with an entry for each of those hosts.

The tables_priv and columns_priv tables are similar to the db table, but are more fine-grained: they apply at the table and column levels rather than at the database level.
Note that administrative privileges (reload, shutdown, etc.) are specified only in the user table. This is because administrative operations are operations on the server itself and are not database-specific, so there is no reason to list such privileges in the other grant tables. In fact, only the user table need be consulted to determine whether or not you can perform an administrative operation.

The file privilege is specified only in the user table, too. It is not an administrative privilege as such, but your ability to read or write files on the server host is independent of the database you are accessing.

When you modify the contents of the grant tables, it is a good idea to make sure that your changes set up privileges the way you want.

A useful diagnostic tool is the mysqlaccess script, which Yves Carlier has provided for the MySQL distribution. Invoke mysqlaccess with the --help option to find out how it works. Note that mysqlaccess checks access using only the user, db and host tables. It does not check table- or column-level privileges.

(Continued on next question...)