Access Granted - Beeping Turkeys (
Page 3 of 7 )
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 | |
+-----------------+--------+-----------+
