Learning Mysql

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 anew user 'hugh'@'toorak.invyhome.com'
using the following statement:
mysql> GRANT ALL ONTO '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. 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 between 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 ONTO '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 To do this, run the following:

Download 4.24 Mb.

Share with your friends: