When dealing with security, it is always best to start off as strict as possible, and then loosen up the
security as you go. When granting permissions, give new users as few as possible—just enough to let
them accomplish their jobs. With stringent security policies, you can help guard your database against
malicious attacks.Summary
Today you have learned a lot about security. You learned how MySQL implements security in a hierarchy,
and that there are several stages of control and two verification points—the access verification and the
request verification. You learned how this two-pronged attack can help prevent security breeches.
You also learned how to add users to a MySQL database. You learned that MySQL provides many
ways of doing it. You can add users by directly editing the grant tables with SQL statements or by
using the GRANT command from the MySQL monitor. You also read about the mysql_setpermission
utility that offers a simple and quick way of adding new users and granting privileges.
Another topic that was covered today was the process by which users are removed from the database.
You learned that the only way to remove users entirely from the database is to delete them from the
grant tables directly by using an SQL DELETE statement. You also learned that you could remove
some or all of a user’s privileges with the REVOKE statement.
Finally, you learned some additional steps to take to help guard your database against malicious
attacks. Ultimately, the responsibility of the data falls upon your shoulders as the DBA. You are in
charge of the database, as well as all the data it holds. It behooves you and your business to implement
very strict security policies, regardless of what data you are maintaining.Q&A
Q:
Are there any other precautions that I can take to ensure the security
of my database?
A:
There are a ton of things you can do to help you secure your database. This
chapter discussed what you can do internally with MySQL. What wasn't
covered is what you can do to make your server more secure. Adding
firewalls and proxy servers are two ways to help deter crackers. Locking
down services and installing security patches as they are available is
another way to maintain the overall security of your machine. Unfortunately,
this is beyond the scope of this book. You may want to look around and find
some good books dealing with security for the operating system you are
using.
Q: I removed a user from my database entirely, but he still can get in. Why
is this?
A:
There can be several explanations for this. The most likely one is that you
have an anonymous user in your user database. An anonymous user is a
user who has a blank entry in the User column of the user table. This
allows anyone to connect to your database. To see if this is your problem,
run the following query:
SELECT * FROM user WHERE User = "";
This could cause a huge security breech without you ever knowing it.
Q:
I have written an Internet application that interacts with the MySQL
database. I want the user to log in to my system, but I do not want to
create a thousand user accounts. What should I do?
A:
It is common practice to create another user table inside your own
database. You can add users to this database instead of to MySQL proper.
You could verify their ability to use the database by performing a query on
this table, in much the same way MySQL does.Exercises
- Add several users to the Meet_A_Geek database using the techniques that were
discussed in this chapter. Add one using the GRANT statement, one using the
mysql_setpermission utility, and one by manipulating the grant tables manually. - Revoke the privileges you granted the users you created in exercise 1 by using the
REVOKE statement. - Delete the users from the database.