Managing Users and Privileges - Learning MySQL - page 298

you?! Similarly, a manager creating a report of daily sales wouldn't be able to acciden-
tally—or deliberately—change any data.
It's also a good idea to use less privileged accounts yourself wherever possible; if you
log in as the MySQL root user for routine tasks, there's a greater likelihood that an
unauthorized user will somehow be able to gather enough information to access that
account. You might even make a mistake and inadvertently damage your database.
In this chapter, we show you how to:
• Understand MySQL privileges
• Add, remove, and change MySQL users and passwords
• GRANT and REVOKE privileges
• Understand MySQL's default security configuration
• Devise a security policy for your MySQL server
• Manage users and privileges using SQL queries
• Limit server usage by user
Understanding Users and Privileges
MySQL, like most other database servers, has users who have privileges that determine
whether they can create, modify, delete, and query databases, and also whether they
can modify the privileges and control the server. In practice, this control can be coarse-
grained —a user may be allowed or prevented from accessing the server—or fine-
grained , where a user can access only some tables in a database or only some columns
in a table. Some database servers support only coarse-grained control, while others such
as MySQL allow both coarse-grained and fine-grained control over access.
MySQL allows you to control which users can access the server; the databases, tables,
and columns on the server that they can access; and the types of actions that users can
carry out on these structures. For example, MySQL allows you to explicitly control
whether users can run the SELECT , UPDATE , INSERT , and DELETE statements, as well as
whether they can LOCK TABLES , ALTER structures, or create and remove indexes. Most of
the time, you'll create users who can access and modify the data in a database but
otherwise have no privileges to adjust the server configuration, change the database's
structure, or access other databases. We show you how to create different users and
list all of the privileges later in this section.
MySQL users are distinct from the operating system users on the server computer.
When you set up your machine, you automatically create superuser accounts that allow
configuration of the server—the root user on a Linux or Mac OS X server, and the
Administrator on Windows—and also one or more user accounts that you use to work
with the server. For example, you could have a superuser account that's used only when
installing or configuring software such as MySQL or a new word processor, and an