Managing Privileges with SQL | 343

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. Assume 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 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 wanton 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 top R5wrDWeve created an account that allows Sam to login 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 privileges 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) | | PRI | | |
| Db | char) | | 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 inset sec)

Download 4.24 Mb.

Share with your friends: