Access Granted - The Perfect Host (
Page 5 of 7 )
A blank entry under the
"Host" column in the "db" table implies that the list of allowed hosts should be
obtained from the third table, the "hosts" table - which looks like this:
+-----------------+---------------+------+-----+---------+-------+
| 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 | |
+-----------------+---------------+------+-----+---------+-------+
This separation is more useful than you might think. If
you would like to connect and use a database from several different hosts, you
would usually have to create a separate entry naming each host in the "db"
table. However, with the introduction of the "host" table, you can place the
host names in the "host" table while retaining only a single entry (with a blank
"Host" field) in the "user" table.
The "host" table also has privilege
fields - these allow you to control the level of access for each database, with
the connecting host name as the criteria for operation.
Here's an example
of how the relationship between the "host" and "db" table can be exploited for
maximum benefit:
mysql> SELECT Host, User, Password FROM user;
+-----------+------+----------+
| Host | User | Password |
+-----------+------+----------+
| | jim | h35472k |
+-----------+------+----------+
mysql> SELECT Host, User, Db FROM db
+------+------+-------+
| Host | User | Db |
+------+------+-------+
| | jim | title |
+------+------+-------+
mysql> SELECT Host, Db, Select_priv, Insert_priv FROM host
+-------------------+-------+----------------+----------------+
| Host | Db | Select_priv | Insert_priv |
+-------------------+-------+----------------+----------------+
| turkey.ix6.com | title | Y | Y |
+-------------------+-------+----------------+----------------+
| blackbox.glue.net | title | Y | N |
+-------------------+-------+----------------+----------------+
| fireball.home.net | title | Y | Y |
+-------------------+-------+----------------+----------------+
In this case, "jim" will be able to connect to the mySQL
server from any of the hosts listed in the "hosts" table, and the privileges
assigned can differ on the basis of the host.
An important point to be
noted is that, in the hierarchy of mySQL grant tables, the "user" table comes
first, with the "db" and "host" tables below it, and the "tables_priv" and
"columns_priv" tables at the bottom. A table at a lower level is referred to
only if a higher-level table fails to provide the necessary scope or
privileges.
When deciding whether or not to allow a particular database
operation, mySQL takes the privilege fields in all three tables into account. It
starts with the "user" table and checks to see if the user has appropriate
privileges for the operation being attempted; if not, the "db" and "host" tables
are checked to see if privileges are available. It is only after logically
parsing the privileges in the different tables that mySQL allows or disallows a
specific database request.
