Access control for MySQL databases is managed in two tiers: In the first tier it is merely checked whether the user has the right to establish a connection to MySQL.

user name, host name, and password.

Only if a connection can be established does the second level of access control come into play, which involves every single database command. For example, if a SELECT is executed, MySQL checks whether the user has access rights to the database, the table, and the column. If an INSERT is executed, then MySQL tests whether the user is permitted to alter the database, the table, and finally the column.

Privileges

How, then, does MySQL manage the information as to which commands can be executed? MySQL uses tables in which are stored privileges. If a user, let us call her athena, has a SELECT privilege for the database owls, then she is permitted to read all the data in owls (but not to change it). If athena has a global SELECT privilege, then it holds for all databases saved under MySQL.

The privileges recognized by MySQL are displayed below in table. Note that the names in the corresponding columns of mysql tables always end in _priv. The Select privilege is thus stored in the column Select_priv. In part, the column names are abbreviated (e.g., Create_tmp_table for the Create-Temporary-Table privilege).

Note also that there are quite a few new privileges in MySQL 4.0 that were not present in version 3.23 (e.g., Create Temporary Table, Execute, Lock Tables, Show Databases, Replication Client, Replication Slave, Super), and that many old favorites have been changed (Process).

In the MySQL documentation, you will encounter (for example, in the description of the GRANT command) the privileges All and Usage. All means that all privileges should be granted with the exception of Grant. Usage means that all privileges should be denied.

All and Usage are thus themselves not independent privileges, but an aid in avoiding a listing of privileges in executing the GRANT command.

The meaning of most of the privileges listed in should be clear without further explanation. For the not-so-clear privileges there will be some explanation in the coming paragraphs.

Privileges are given in this book with an intial capital letter followed by lowercase letters in order to distinguish them from their like-named SQL commands (Select privilege and SELECT command). MySQL couldn't care less how you distribute uppercase and lowercase letters.

The Grant Privilege

The Grant privilege indicates that a MySQL user can dispense access privileges. (This is most easily accomplished with the SQL command GRANT, whence the name of the privilege.) However, the ability to dispense privileges is limited to the privileges possessed by the grantor. That is, no user can give privileges to another that he or she does not already possess.

The Grant Option privilege is an often overlooked security risk. For example, a test database is created to which all members of a team have unrestricted access. To this end, all the privileges in the relevant entry in the db table are set to Y (the db table will be described a bit later).

A perhaps unforeseen consequence is that everyone who has unrestricted access to this test table can give unrestricted access privileges (either to him- or herself or to other MySQL users) for other databases as well!

The File Privilege

MySQL users with the File privilege may use SQL commands for direct access to the file system of the computer on which the MySQL server is running, for example, with the command SELECT ... INTO OUTFILE name or the command LOAD DATA or with the function LOAD_FILE.

In the case of file access, it is necessary, of course, to pay heed to the access privileges of the file system. (Under Unix/Linux the MySQL server normally runs under the mysql account. Therefore, only those files that are readable by the Unix/Linux user mysql can be read.) Nevertheless, the File privilege is often a considerable security risk.

The Privileges PROCESS and SUPER

The Process privilege gives the user the right to determine, using the command SHOW PROCESSLIST, a list of all processes (connections), including those of other users. (One may obtain a list of one's own processes without this privilege.)

The privilege Super permits the user to end both his own and others' processes with KILL. (If the Super privilege has not been granted, then only the current process can be ended.)

The Super privilege also permits the execution of some administrative commands: CHANGE MASTER for executing the client configuration of a replication system, PURGE MASTER to delete binary logging files, and SET GLOBAL for changing global MySQL variables.

Global Privileges Versus Object Privileges

In MySQL privileges can be chosen to be either global or related to a particular object. Global indicates that the privilege is valid for all MySQL objects (that is, for all databases, tables, and columns of a table).

The management of object-related privileges is somewhat more difficult, but it is also more secure. Only thus can you achieve, for example, that a particular MySQL user can alter a particular table, and not all tables managed under MySQL. An assumption in the use of object-related privileges is that the corresponding global privileges are not set. (What is globally allowed cannot be withheld at the object level.)

This hierarchical idea is maintained within the object privileges as well. First it is checked whether access to an entire database is allowed. Only if that is not allowed is it then checked whether access to the entire table named in the SQL command is allowed. Only if that is forbidden is it then checked whether perhaps access to individual columns of the table is allowed.

The mysql Database

It is not surprising that the management by MySQL of access privileges is carried out by means of a database. This database has the name mysql, and it consists of several tables, responsible for various aspects of access privileges.

The Tables of the Database mysql

The database mysql contains six tables (five in earlier versions) of which five are for managing access privileges. These five tables are often referred to as grant tables. The following list provides an overview of the tasks of these six tables: