Managing Users and Privileges - Learning MySQL - page 313

Suppose now that you want to allow toorak to access the MySQL server that's running
on ruttle . There are several different ways to do this, some more flexible than others.
The simplest approach is to connect to the MySQL server on ruttle as the root user
and grant privileges to a new user 'hugh'@'toorak.invyhome.com' using the following
statement:
mysql> GRANT ALL ON *.* TO 'hugh'@'toorak.invyhome.com' IDENTIFIED BY ' the_password ';
Query OK, 0 rows affected (0.00 sec)
You'll now find that you can run a MySQL monitor on toorak and connect to ruttle
using the following command:
$ mysql --user=hugh --host=ruttle.invyhome.com --password= the_password
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 52 to server version: 5.0.22-standard-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
Using the IP addresses 192.168.1.2 for ruttle.invyhome.com and 192.168.1.4 for
toorak.invyhome.com should work too, and it's more secure, as IP addresses are harder
to spoof than domain names.
Our approach so far has been to create new users for each IP address. We now have
three users with the name hugh , one each for the localhost , 192.168.1.2, and
192.168.1.4. This isn't always a good approach: we now have to remember to maintain
all three users and keep their privileges synchronized if we want the same access level
from all three locations. However, it's also flexible: it allows you to differentiate be-
tween different remote users with the same username, or offer a flexible, customized
security policy when a user connects from different locations.
Let's consider other ways to allow the same user to connect from several locations. You
can allow a user to connect from all computers on a network subnet by using one or
more wildcards in the GRANT statement. Suppose you want to allow jill to connect
from any of the machines in the domain invyhome.com . You can do this with:
mysql> GRANT ALL ON *.* TO 'jill'@'%.invyhome.com' IDENTIFIED BY ' the_password ';
Query OK, 0 rows affected (0.01 sec)
As in the SQL LIKE clause, the wildcard character % matches any string, and so this entry
now matches any domain name with the suffix invyhome.com . The outcome is that
connections as jill from ruttle.invyhome.com and toorak.invyhome.com are allowed;
jill can also connect from any other machine that joins the network.
You can also use wildcards in IP addresses. For example, you can allow connections
from all machines on the invyhome.com subnet by allowing access to machines matching
the IP address range 192.168.1.% .
To do this, run the following: