Managing Users and Privileges - Learning MySQL - page 346

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.29 sec)
This limits Sam's access to the SELECT privilege when he accesses the server from any-
where but localhost and our local subnet.
The host table allows you to configure client access controls. For example, using the
wildcard % in the Db column, you can control access for a client to all databases on the
server. Suppose you want to forbid access from your web server machine,
192.168.1.200. To do this, you add this entry to the host table:
mysql> INSERT INTO host VALUES ('192.168.1.200', '%',
-> 'N','N','N','N','N','N','N','N','N','N','N','N');
Query OK, 1 row affected (0.20 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.29 sec)
All database privileges are denied to connections from the web server, as long as no
db table rows specify it as a valid host, and global privileges are not granted.
Activating Privileges
We've used SQL statements to manipulate the host table in the previous section, and
prior to that in “The Default Users” as a shortcut for removing users. You'll find this
is useful: it's sometimes easier to apply an SQL statement to all rows in a table, or join
tables, rather than apply successive GRANT and REVOKE statements.
You've also seen that whenever we manipulate the mysql database with SQL statements,
we run the FLUSH PRIVILEGES statement afterward. This clears MySQL's internal priv-
ilege cache, causing it to reread the current privileges and cache any recent updates. If
you don't do this, your privilege changes won't appear until you restart the server or
carry out a GRANT or REVOKE statement that affects the same privilege table. You must
remember to run FLUSH PRIVILEGES after any privilege or user modifications are per-
formed with SQL statements; you don't need to use FLUSH PRIVILEGES with GRANT or
REVOKE , as the server does this for you automatically.
You may also have wondered when exactly privilege changes with GRANT and REVOKE
take effect on a current connection. Any change you make at the column or table level
takes effect when you run the next statement. Changes at the database level take effect
when you next choose a database. Changes at the global level—including password
changes—take effect when the specified user next connects. Finally, all changes take
effect immediately if you stop and restart the server.
Privileges and Performance
MySQL's user and privilege management gives you fine-grain control over who has
access to which parts of the server and its databases, as well as what that access allows.