Databases Reference
In-Depth Information
mysql>
GRANT ALL ON *.* TO 'harry'@'192.168.1.%' IDENTIFIED BY '
the_password
';
Query OK, 0 rows affected (0.01 sec)
Again, connections as
harry
from
ruttle
and
toorak
(and any other machines on the
local network) are allowed.
Table 9-3 shows different specifications of network addresses that use wildcards and
gives examples of where the user
lloyd
could connect from. The entry in the final row
allows
lloyd
to connect from anywhere and should be used with caution.
Table 9-3. Host specifications and their meanings in GRANT statements
Host specification
Example
Effect
Hostname
'lloyd'@'lloyd.lloydhouse.com'
1
Domain name
'lloyd'@'%.lloydhouse.com'
2
IP address
'lloyd'@'192.168.1.2'
3
IP address range
'lloyd'@'192.168.1.0/255.255.255.0'
4
Any machine
'lloyd'@'%'
or
'lloyd'
5
The effect of each of these settings is as follows:
1. Connections are allowed only from the machine
lloyd.lloydhouse.com
.
2. Connections are allowed from any machine in the
lloydhouse.com
domain.
3. Connections are allowed only from the machine with the IP address 192.168.1.2.
4. The address range is specified as an IP address and a netmask. The current standard
IPv4 addresses are 32 bits long and are commonly shown in the dotted decimal
notation as 4 decimal numbers, each corresponding to 8 bits of the IP address or
netmask. For example, the IP address:
11000000101010000000000100000001
is shown as:
192.168.1.1
which is much easier to read!
The
netmask
specifies how many of these bits (from left) identify the network; the
remaining bits identify the hosts on that network. The smaller the netmask, the
more bits remain for the hosts, and so the greater number of hosts that fall into the
specified range. The sample specification
192.168.1.0/255.255.255.0
says that the
first 24 bits of the host IP address must match the first 24 bits of the address
192.168.1.0
, so any address that starts with
192.168.1.
is accepted. The netmask
specified to MySQL can only be 8, 16, 24, or 32 bits in length.
5. Connections are allowed from any machine. If you omit the host specification,
%
is assumed.
