Learning Mysql

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 sophisticated user and privilege management tools, you can properly setup 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 important 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 corrupts 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 database 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
297

you Similarly, a manager creating a report of daily sales wouldn’t be able to accidentally or deliberately—change any data.
It’s also a good idea to useless privileged accounts yourself wherever possible if you login 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

Download 4.24 Mb.

Share with your friends: