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.
Next: Cream Of The Crop >>
More MySQL Articles
More By icarus, (c) Melonfire
