Databases Reference
In-Depth Information
range of clients. For example, you could specify that users connecting from outside the
company network do not have the privilege to delete data or drop tables.
We'll explain how the
host
table works through an example. Suppose you've decided
to create a MySQL user for Sam (with the username
sam
), but you want to allow him
to access the
music
database from different locations, but with different privileges. As-
sume there are three scenarios you want to implement. First, when
sam
accesses the
server from the
localhost
, you want him to have all privileges for the database except
GRANT OPTION
. Second, when he accesses the server from anywhere else on your network
subnet—which is all machines matching
192.168.1.%
—you want him to have all simple
non-administrator privileges. Last, when he connects from anywhere else, you want
him to have the
SELECT
privilege only. You know how to do this by creating three users
that that have access to
music.*
:
'sam'@'localhost'
,
'sam'@'192.168.1.%
, and
'sam'@'%'
. However, using the
host
table, you can instead create just one user.
Here's how you create one user with two or more host specifications. First, you add
the user account with a superset of the privileges you want on
music.*
:
mysql>
GRANT ALL ON music.* TO 'sam'@'' IDENTIFIED BY 'p^R5wrD';
Notice that we've given the privileges to
'sam'@''
, which sets the
Host
column value to
the empty string; don't use just
'sam'
because this is the same as
'sam'@'%'
. We've also
set this user's password to
'p^R5wrD'
.
We've created an account that allows Sam to log in to the MySQL server from any host,
and he now has all privileges for the
music
database. Let's now create the entries in the
host
database that allow and restrict his access depending on the client from which he
connects. To begin, let's create an entry for the
localhost
that doesn't restrict his priv-
ileges at all. To do this, you need to understand the structure of the
host
table:
mysql>
DESCRIBE host;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | | PRI | | |
| Db | char(64) | | 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 | |
| Create_tmp_table_priv | enum('N','Y') | | | N | |
| Lock_tables_priv | enum('N','Y') | | | N | |
+-----------------------+---------------+------+-----+---------+-------+
14 rows in set (0.21 sec)
