An introduction to MySQL permissions

Tuesday Feb 17th 2004 by Ian Gilfillan
Share:

Ian Gilfillan examines the intricacies of MySQL permissioning.

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.
PermissionDescription
Select_privPermission to run SELECT queries
Insert_privPermission to run INSERT statements
Update_privPermission to run UPDATE statements
Delete_privPermission to run DELETE statements
Create_privPermission to CREATE tables and databases
Drop_privPermission to DROP tables and databases
Reload_privPermission to RELOAD the database (a FLUSH statement for example)
Shutdown_privPermission to SHUTDOWN the database server
Process_privPermission to view or kill PROCESSes.
File_privPermission to read and write FILEs (for example LOAD DATA INFILE)
Grant_privPermission to GRANT available permissions to other users
References_privPermissions to create, modify or drop INDEXes
Index_privNot used by MySQL 4.0.x
Alter_privPermission 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') |      |     |         |       |
+-------------+----------------------------------------------+------+-----+---------+-------+

A brief recap of the process

The order of precedence of the tables is as follows:
USER
|
DB/HOST
|
TABLES_PRIV
|
COLUMNS_PRIV

MySQL checks the user table first, if permission is not granted there, it will check the db and host tables, and, if further confirmation is required, the tables_priv and even the columns_priv tables. Be aware that excessive use of all these tables comes at a performance cost - if before every operation MySQL has to check permissions at a column level, it will be that much slower. Use what you need, no more, no less.

How to GRANT permissions

Hopefully the process has been easy to follow, but the burning question must be, how does anyone set these permissions! There are two ways - directly manipulating the tables, with INSERT, UPDATE and DELETE statements (which require MySQL to be reloaded, or the privileges flushed, for example with the FLUSH PRIVILEGES statement. Note also that if you add a record directly to the password field in the user table, you must use the PASSWORD() function. The alternative, more convenient in my opinion, is using a GRANT statement. The syntax of a GRANT statement is:
GRANT privilege ON table_or_database_name TO user@hostname IDENTIFIED BY 'password'.

The privileges are:

PrivilegeDescription
ALL/ALL PRIVILEGESAll the basic permissions
ALTERPermission to run ALTER statements
CREATEPermission to CREATE tables or databases
CREATE TEMPORARY TABLESPermission to run CREATE TEMPORARY TABLE statements
DELETEPermission to run DELETE statements
DROPPermission to DROP tables or databases
EXECUTEPermission to run stored procedures (in MySQL 5)
FILEPermission to read and write files (e.g. LOAD DATA INFILE statements)
GRANTPermission to GRANT available permissions to other users
INDEXPermission to create, change or drop indexes
INSERTPermission to run INSERT statements
LOCK TABLESPermission to LOCK tables which the user has SELECT access to
PROCESSPermission to view or kill MySQL processes
REFERENCESCurrently unused
RELOADPermission to reload the database (e.g. FLUSH statements)
REPLICATION CLIENTPermission to ask about replication
REPLICATION SLAVEPermission to replicate from the server
SHOW DATABASESPermission to see all databases
SELECTPermission to run SELECT statements
SHUTDOWNPermission to SHUTDOWN the MySQL server
SUPERPermission to connect, even if the number of connections is exceeded, and perform maintenance commands
UPDATEPermission to run UPDATE statements
USAGEPermission to connect and and perform basic commands only

Database and Table names in a GRANT statement

NameDescription
*.*All tables in a database
*All tables in the current database
dbname.*All tables in the named database
dbname.tbnameThe named table in the named database

So, some examples of GRANT in action:

mysql> GRANT SELECT ON *.* TO rushdi@localhost IDENTIFIED BY 'supa_password'
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT host,user,password,select_priv,insert_priv FROM user WHERE user = 'rushdi';
+-----------+--------+------------------+-------------+-------------+
| host      | user   | password         | select_priv | insert_priv |
+-----------+--------+------------------+-------------+-------------+
| localhost | rushdi | 0b3bcd316f1c8020 | Y           | N           |
+-----------+--------+------------------+-------------+-------------+

Note that the password has been automatically encrypted. The record also only appears in the user table, not the db table, as permission was granted to all databases. Another example:

mysql> GRANT INSERT ON mysql.* TO suretha@localhost IDENTIFIED BY 'supa_password2';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT host,user,password,select_priv,insert_priv FROM user WHERE user = 'Suretha';
+-----------+---------+------------------+-------------+-------------+
| host      | user    | password         | select_priv | insert_priv |
+-----------+---------+------------------+-------------+-------------+
| localhost | suretha | 30f59c271b923c47 | N           | N           |
+-----------+---------+------------------+-------------+-------------+

mysql> SELECT host,db,user,select_priv,insert_priv FROM db WHERE user='Suretha';
+-----------+-------+---------+-------------+-------------+
| host      | db    | user    | select_priv | insert_priv |
+-----------+-------+---------+-------------+-------------+
| localhost | mysql | suretha | N           | Y           |
+-----------+-------+---------+-------------+-------------+

Here records are added to both the user and db tables. To revoke permission, we use the REVOKE statement.

mysql> REVOKE SELECT ON *.* FROM rushdi@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT host,user,password,select_priv,insert_priv FROM user WHERE user = 'rushdi';
+-----------+--------+------------------+-------------+-------------+
| host      | user   | password         | select_priv | insert_priv |
+-----------+--------+------------------+-------------+-------------+
| localhost | rushdi | 0b3bcd316f1c8020 | N           | N           |
+-----------+--------+------------------+-------------+-------------+

Note that the record still appears in the table, so he can connect, but select_priv has been disabled.

mysql> REVOKE INSERT ON mysql.* FROM suretha@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT host,db,user,select_priv,insert_priv FROM db WHERE user='Suretha';
Empty set (0.00 sec)

mysql> SELECT host,user,password,select_priv,insert_priv FROM user WHERE user = 'Suretha';
+-----------+---------+------------------+-------------+-------------+
| host      | user    | password         | select_priv | insert_priv |
+-----------+---------+------------------+-------------+-------------+
| localhost | suretha | 30f59c271b923c47 | N           | N           |
+-----------+---------+------------------+-------------+-------------+

The record has been deleted from the db table, but still appears, with no permissions, in the user table.

Hopefully you're starting to find MySQL permissions flexible and easy to use. Although there is much more to explore, do not overuse the available options. You will make management more complex, and affect performance. Good luck!

» See All Articles by Columnist Ian Gilfillan

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved