Managing Users and Privileges - Learning MySQL - page 297

CHAPTER 9
Managing Users and Privileges
Learning MySQL, developing applications, and deploying finished software are tasks
with very different security requirements. While you're learning the basics—especially
if you're working on your own machine—it's not usually critical if you accidentally
remove databases or tables, change data, or don't carefully limit access to the MySQL
server and its databases. However, when you develop and maintain real applications,
it's crucial that you secure your server and databases against accidental or deliberate
acts that can delete, change, or expose your data. Fortunately, using MySQL's sophis-
ticated user and privilege management tools, you can properly set up and secure access
to your database server. This chapter shows you how.
In addition to setting up the MySQL server access privileges, you should separately
ensure the physical security of your host computer and backup media, and proper
configuration of permissions at the operating system level. We've explained some im-
portant aspects of this in Chapters 2 and 10, and we'll also look at this topic briefly in
this chapter.
The MySQL server comes with the user root , who can do everything on the MySQL
server, including creating and deleting users, databases, tables, indexes, and data. Up
to this point, we've connected to the server under this superuser account, which is very
convenient, but not very secure—remember the saying about how absolute power cor-
rupts absolutely?
Most applications don't need superuser privileges for day-to-day activities. You can
define less powerful users who have only the privileges they need to get their jobs done.
You may want to prevent users creating or changing indexes, tables, or databases. You
may even want to prevent users doing more than simply running SELECT statements on
a given database or even particular tables in a database.
For example, you could have a user allmusic who can perform any database operation
on the music database, and the user partmusic who can read data from the music data-
base but can't change anything.
In this way, if the allmusic account is compromised, an attacker can at most delete the
music database, but nothing else—and, of course, you would have backups, wouldn't