mySQL comes with a pretty powerful security system, the grant tables, which allows database administrators to precisely control access to databases, tables and even specific rows and columns. In this article, find out how the five grant tables combine to offer power users a tremedous amounts of flexibility and control over database access and operations.
As you will have seen from the example above, the entire mySQL security system consists of five tables.
+-----------------+
| columns_priv |
| db |
| host |
| tables_priv |
| user |
+-----------------+
Each of these has a different role to play in deciding
whether a user has access to a specific database, table or table column. Access rules may be set up on the basis of username, connecting host, or database requested.
When a user requests a connection to the database server from a specific host, mySQL will first check whether there is an entry for the user in the "user" table, if the user's password is correct, and if the user is allowed to connect from that specific host. If the check is successful, a connection will be allowed to the server.
Once a connection is allowed, every subsequent request to the server - SELECT, DELETE, UPDATE and other queries - will first be vetted to ensure that the user has the security privileges necessary to perform the corresponding action. A number of different levels of access are possible - some users may only have the ability to SELECT from the tables, while others may have INSERT and UPDATE capabilities, but not DELETE capabilities.
Of the three tables above, the most important one is the "user" table - let's take a closer look at the fields it contains:
+-----------------+---------------+------+-----+---------+-------+
| 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 | |
+-----------------+---------------+------+-----+---------+-------+
The first three fields (referred to as "scope fields")
are used to define which users are allowed to connect to the database server, their passwords, and the hosts from which they may connect - mySQL uses a combination of both user and host identification as the basis for its security system. Consider the following extract from this table:
+--------------------+--------+----------+
| Host | User | Password |
+--------------------+--------+----------+
| turkey.domain.com | john | |
+--------------------+--------+----------+
This implies that the user "john" (password null) is
allowed to connect from the host "turkey.domain.com"
It's also possible to specify wildcards - the following example would allow access to a user named "john", regardless of the host from which the connection is requested.
+-----------------+--------+
| Host | User | Password |
+-----------------+--------+
| % | john | |
+-----------------+--------+
The % character is used as a wildcard - the following
example would match any user named "john" connecting from the "loudbeep.com" domain.
+-----------------+--------+-----------+
| Host | User | Password |
+-----------------+--------+-----------+
| %.loudbeep.com | john | |
+-----------------+--------+-----------+
