Learning MySQL

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 installation. 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 flexibility 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 create them with no privileges. You then explicitly grant the privileges ...