Managing Users and Privileges - Learning MySQL - page 333

mysql> DROP USER 'root'@' host.domain '
Query OK, 0 rows affected (0.00 sec)
Replace host.domain with the server's fully qualified domain name. For example, if your
host was called ruttle.invyhome.com , you would write:
mysql> DROP USER 'root'@'ruttle.invyhome.com'
Again, you can instead manually modify the grant tables; here, you can delete all ac-
counts that have a host other then localhost :
mysql> DELETE FROM mysql.user WHERE Host <> 'localhost';
Query OK, 1 row affected (0.26 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.20 sec)
Removing users makes us nervous, especially when a wrong keystroke in a DELETE
statement can remove all your users; you can even remove the root account! If you
make a mistake, you should restore the files for the mysql database (in the mysql direc-
tory of the data directory) from your backups. On a Linux or Mac OS X system, you
can also restore the default users with the mysql_install_db script; simply run this
command the same way you ran it in Chapter 2. We look at backups and recovery in
Chapter 10.
Devising a User Security Policy
You now understand the principles of creating, maintaining, and removing users and
their privileges. In this section, we show you how to take those basics and put them
together to develop a security policy and a maintainable, flexible, secure MySQL in-
stallation. Importantly, we also show you how to balance server performance against
security, and develop the thinking that'll allow you to effectively manage your MySQL
server.
Flexibility and security are enemies. The most secure MySQL installation has no users
with no privileges. The most flexible installation lets everyone in as root , in case they
need to administer or change the server or its databases. Balancing security and flexi-
bility is important: you should have sufficient users and privileges to permit the user
requirements of the applications you develop, but you should constrain those users
and privileges to the minimal set that's needed. The next section walks you through a
checklist of decisions you should make in setting up your users and their privileges.
Choosing Users and Privileges
To begin, you should decide whether you'll have a “default allow” or “default deny”
philosophy. In the “default allow” philosophy, you decide on all of the users you might
need and grant them all privileges. You then explicitly revoke any privileges they don't
need. In the “default deny” philosophy, you decide on the users you must have and