-> 'Y','Y','Y','Y','N','N','N','N','N','N','N','Y')

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 anywhere 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 fora client to all databases on the server. Suppose you want to forbid access from your web server machine. 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 privilege 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 carryout 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 performed 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.

Download 4.24 Mb.

Share with your friends: