MySQL newbies often have problems with the MySQL access control system. But once you get used to it, I think you will find it flexible and easy to use, so this month we’re going to examine how to get started with MySQL permissioning.
The USER table
MySQL access is controlled by the mysql database. Let’s take a look at the tables found in this database. The following list comes from MySQL 5 – earlier versions of MySQL will be different, but in this tutorial I only focus on those tables available to the current stable version 4.0.x
mysql> SHOW TABLES; +-----------------+ | Tables_in_mysql | +-----------------+ | columns_priv | | db | | func | | help_category | | help_keyword | | help_relation | | help_topic | | host | | proc | | tables_priv | | user | +-----------------+
When a user tries to connect to the database, MySQL checks that that particular username/host/password combination has permission to connect. Once the connection has been made, before any operations are carried out, MySQL again checks to see whether the user/host combination has the right level of access to carry out that operation.
The user table is the first table MySQL checks. All user/host/password combinations must be listed in this table before any access can be granted. Let’s look at the table in more detail:
mysql> DESC user;
+-----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| Host | char(60) | | PRI | | |
| User | char(16) | | PRI | | |
| Password | char(16) | | | | |
| 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 | |
+-----------------+---------------+------+-----+---------+-------+
It is important to understand that the host and user together determine an individual permission for connecting. User Nosipho may have access from host A, and not from host B. In fact, user Nosipho on host B may be an entirely different user.
A host may be either the hostname of the machine, or the IP, and may be, or include, a wildcard (the % sign), meaning any host. It should be rare to allow access from any host. Web applications, for example, typically only allow access to the database server from the web server (or localhost for small setups, where they’re on the same machine). The password is stored in an encrypted format using the PASSWORD() function. Let’s look at a sample subset from the user table:
mysql> SELECT host,user FROM user; +---------------+------+ | host | user | +---------------+------+ | localhost | mysql| | localhost | mark | | 192.168.5.42 | tiki | | 192.168.5.% | mpho | | 192.168.5.42 | | | % | wiki | +---------------+------+
In this example, the mysql and mark users can connect from localhost only, while user tiki, and any other user, can connect from the IP 192.168.5.42. User mpho can connect from any IP starting with 192.168.5 (as denoted by the wildcard where the last digit would be). Finally, user wiki has access from any machine. This does not necessarily mean they can do anything, just that they can connect.
To decide whether a user has access to perform a particular operation, MySQL again checks the user table first. The remaining fields, all fairly clearly named, come into play. Select_priv determines whether users can run SELECT queries, Insert_priv INSERT queries, and so on.
| Permission | Description |
|---|---|
| Select_priv | Permission to run SELECT queries |
| Insert_priv | Permission to run INSERT statements |
| Update_priv | Permission to run UPDATE statements |
| Delete_priv | Permission to run DELETE statements |
| Create_priv | Permission to CREATE tables and databases |
| Drop_priv | Permission to DROP tables and databases |
| Reload_priv | Permission to RELOAD the database (a FLUSH statement for example) |
| Shutdown_priv | Permission to SHUTDOWN the database server |
| Process_priv | Permission to view or kill PROCESSes. |
| File_priv | Permission to read and write FILEs (for example LOAD DATA INFILE) |
| Grant_priv | Permission to GRANT available permissions to other users |
| References_priv | Permissions to create, modify or drop INDEXes |
| Index_priv | Not used by MySQL 4.0.x |
| Alter_priv | Permission to ALTER table structures. |
All are enumerated types, a Y value allowing the operation, and a N value possibly disallowing it. Only possibly, because the user table is the bluntest kind of permission. A Y value in one of these fields always allows that operation to be performed on all databases in the table. It is often good practice to set values to N in the user table, and then allow them for the appropriate database only, as we’ll see now. Another sample:
mysql> SELECT host,user,select_priv,insert_priv FROM user; +-----------+------+-------------+-------------+ | host | user | select_priv | insert_priv | +-----------+------+-------------+-------------+ | % | mark | Y | N | | localhost | mpho | N | N | +-----------+------+-------------+-------------+
Here user mark can always perform SELECT queries, while for the other operations, MySQL will need to check the other tables first to see, starting with the db table.
The DB table
If the user table allows access, but disallows permission for a particular operation, the next table to worry about is the db table. This sets permissions for specific databases.
mysql> DESC db;
+-----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| Host | char(60) | | PRI | | |
| Db | char(32) | | 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 | |
+-----------------+---------------+------+-----+---------+-------+
Host and User appear in the same way in this table, but attached to a database, not a password. The same host/user combination appears, with a password, in the user table, which allows the user to connect, but if they do not have permission to perform an operation, MySQL will check this
table to see if they can perform it on a particular database. A sample:
mysql> SELECT host,db,user,select_priv,insert_priv FROM db; +-----------+----------+-------+-------------+-------------+ | host | db | user | select_priv | insert_priv | +-----------+----------+-------+-------------+-------------+ | localhost | news | mark | Y | Y | | localhost | archives | mpho | N | N | | localhost | news | mpho | Y | Y | +-----------+----------+-------+-------------+-------------+
Compare this with the previous sample we looked at from the user table. User mark already had Select permission on all databases, but no Insert permissions. Here, he is granted insert permission on the news database only, while user mpho is given select and insert permission on the news database. Most MySQL installations in any kind of multi-user scenario would be best served by denying global permissions, and granting them on the database-level only.
Another possibility exists. The database and user combination were found, but the host was left blank. In this case, MySQL checks the host table. Let’s look at what’s in there:
mysql> DESC host;
+-----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| Host | char(60) | | PRI | | |
| Db | char(32) | | 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 | |
+-----------------+---------------+------+-----+---------+-------+
Exactly the same kind of checks occur here. An example:
mysql> SELECT host,db,select_priv,insert_priv FROM host; +---------------+----------+-------------+-------------+ | host | db | select_priv | insert_priv | +---------------+----------+-------------+-------------+ | localhost | news | Y | Y | | localhost | archives | Y | N | | 192.168.5.42 | news | Y | N | +---------------+----------+-------------+-------------+
If the host had been left blank, permissions are determined here. A user from localhost would have both select and insert permission to the news database, while a user from the host 192.168.5.42 would only have select permission to this database. From localhost, a user would have select privileges only on the archives database.
But there is still more fine-tuning possible. You can assign users permission on a table, or even a column level, with the tables_priv and columns_priv tables, described below:
mysql> DESC tables_priv;
+-------------+---------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------------------------------------+------+-----+---------+-------+
| Host | char(60) | | PRI | | |
| Db | char(60) | | PRI | | |
| User | char(16) | | PRI | | |
| Table_name | char(60) | | PRI | | |
| Grantor | char(77) | | MUL | | |
| Timestamp | timestamp(14) | YES | | NULL | |
| Table_priv | set('Select','Insert','Update','Delete','Create', | | | | |
| |
'Drop','Grant','References','Index','Alter') | | | | |
| Column_priv | set('Select','Insert','Update','References') | | | | |
+-------------+---------------------------------------------------+------+-----+---------+-------+
mysql> DESC columns_priv;
+-------------+----------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------------------+------+-----+---------+-------+
| Host | char(60) | | PRI | | |
| Db | char(60) | | PRI | | |
| User | char(16) | | PRI | | |
| Table_name | char(60) | | PRI | | |
| Column_name | char(60) | | PRI | | |
| Timestamp | timestamp(14) | YES | | NULL | |
| Column_priv | set('Select','Insert','Update','References') | | | | |
+-------------+----------------------------------------------+------+-----+---------+-------+
'Drop','Grant','References','Index','Alter') | | | | |
| Column_priv | set('Select','Insert','Update','References') | | | | |
+-------------+---------------------------------------------------+------+-----+---------+-------+
mysql> DESC columns_priv;
+-------------+----------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------------------+------+-----+---------+-------+
| Host | char(60) | | PRI | | |
| Db | char(60) | | PRI | | |
| User | char(16) | | PRI | | |
| Table_name | char(60) | | PRI | | |
| Column_name | char(60) | | PRI | | |
| Timestamp | timestamp(14) | YES | | NULL | |
| Column_priv | set('Select','Insert','Update','References') | | | | |
+-------------+----------------------------------------------+------+-----+---------+-------+
