Managing Users and Privileges - Learning MySQL - page 345

You can see it has an entry for a Host and a Db , as well as the usual table-level privileges.
Now, let's add an entry for localhost that allows all privileges:
mysql> INSERT INTO host VALUES ('localhost', 'music',
-> 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
Query OK, 1 row affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.29 sec)
From now on, Sam can access the server as 'sam'@'localhost' and have all privileges
for the music database. We explain how this works next.
When Sam connects as 'sam'@'localhost' , the following steps occur:
1. The row in the user table for Sam is matched against the user and password cre-
dentials, giving him access to the server.
2. Global privileges are added to Sam's permissions.
3. The db table is searched for entries that match the user sam ; there's one matching
entry for the music database that has a blank Host column.
4. The host table is searched for any specific entries for the database music ; in this
case, there's one row with a Host value of localhost . We now have two sets of
privileges for the music database: privileges for Sam, and privileges for localhost .
5. Last, the intersection—the logical AND—of the privileges is computed, and these
are added to the global permissions for Sam's connection; if either or both rows
have an N for a privilege, then that privilege is denied unless it was granted globally.
In this case, since Sam has all privileges in both rows except GRANT OPTIONS (he
doesn't have it for the db table row), then Sam has those privileges for accessing
the music database.
We still need to configure access so that Sam has the appropriate access privileges from
other machines on our network subnet and from the Internet. To configure for the
network subnet, we add the following entry to the host table:
mysql> INSERT INTO host VALUES ('192.168.1.%','music',
-> 'Y','Y','Y','Y','N','N','N','N','N','N','N','Y');
Query OK, 1 row affected (0.21 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.29 sec)
When Sam connects from the local network, he has only the SELECT , INSERT , UPDATE ,
DELETE , and LOCK TABLES privileges, since the intersection of this row and his row in the
db table yields a Y for only those privileges.
To configure his access for elsewhere on the Internet, we add:
mysql> INSERT INTO host VALUES ('%', 'music',
-> 'Y','N','N','N','N','N','N','N','N','N','N','N');
Query OK, 1 row affected (0.20 sec)