Web Database Applications with PHP and MySQL, 2nd Edition

Managing Users and Privileges

MySQL has complex, flexible account and database access management. It supports multiple accounts (known as users), and each has an optional password and a set of privileges that define what the user can do. For example, you can allow a database administrator to startup, shutdown, and manage MySQL. You can allow an application administrator to create, drop, and alter tables and databases. In a web database application, you might limit a user to only altering data in tables, or give them read-only access. You can also control which databases, tables, and attributes a user can access, and from where they can access the server.

This section explains how user and privilege management is supported in MySQL, and recommends how to manage it for a web database application.

Creating Users and Privileges

When you installed MySQL by following our instructions in Appendix A through Appendix C, you set up two users (the root user and a web database application user) and created passwords for each. The root user has more privileges than should be used with an application: it can create other users and privileges, view and manipulate all databases, and control and manage MySQL. We recommend that you use the additional user you created for your application and that you create an additional user for each application that you build. We also recommend you keep it to one simple user per application: extra users or complex privileges slow down MySQL since there's more ...