Professional LAMP: Linux®, Apache, MySQL®, and PHP5 Web Development

4.5. Controlling Access

One of the most important jobs you'll have as a MySQL database administrator is controlling who can access the system and what resources they can access. In most situations, you'll need only a handful of user accounts for each database, and those that you do need to create should only be given the minimal access required to perform their task. Luckily, MySQL provides you with a handful of ways to control who has access to your systems and to what resources exactly.

4.5.1. User Administration

In order to help control the user accounts in your database, MySQL provides a couple of ways to maintain user accounts. Whether it's an account for a co-developer, a tech-savvy client, or a web-based front end to the system, each user needs to have access specifically granted for the resources they require.

One of the first things you need to do when configuring a database for the real world is set up any accounts that might be needed by any users, or the system itself. In order to set up specialized access for a user in your database, you need to know about the different levels for which MySQL can control access. MySQL 4.1 can differentiate four different access levels in regards to permissions: the global level, database level, table level, and column level.

Global-level privileges are the most powerful and apply to all the databases running on an instance of MySQL. Normally such privileges are reserved for administrator accounts, as it is a rare occasion when an ...