Figure 17.2 The request verification process.
Modifying User Privileges
You may be wondering if you have to worry about the way MySQL implements security. The simple answer
is No. MySQL has tools that take care of this for you. But you really won’t be a good DBA if you dodn’t know
how MySQL implements security. It will also help you find security holes if you ever have any.
As with most things, there is more than one way to protect your database. MySQL provides you with
many tools to help you control security in your database. The following sections will start with the
hardest way and work backwards.Altering the grant Table
The hardest way, relatively speaking, is to alter the grant tables yourself. By first understanding how the
grant tables are organized, you will gain a greater appreciation of the other methods that are used later in
this chapter. It also may be necessary at times to alter the grant tables directly. This section will show you
how. Because these are just ordinary MySQL tables, INSERT, DELETE, UPDATE, and SELECT queries work
the way with which you are already familiar. Begin by adding a user.
To add a user, you must first log in to the MySQL database as root and use the mysql database. To
do this, perform the following steps:
- Change to the mysql installation directory.
cd /usr/local/mysql - Ensure that the mysqld is up and running.
bin/mysqladmin –p ping - You will be prompted for your root password. After you have entered it correctly,
you be able to access the mysql database by typing in the following:
use mysql;
The mysql database should now be the active database. The next step is to insert a row into the user
table. Remember that this is the table MySQL checks to see if a person has access privileges. To insert
a person without any permissions, enter the following:
INSERT INTO user (Host, User, password)
VALUES("localhost", "Scott", PASSWORD("dinosaur"));
This statement adds a record to the user grant table. It adds Scott, who can only connect from the
database server itself. Scott will not be allowed to connect remotely. It also gives Scott the password
"dinosaur." Passwords in MySQL are case sensitive. The password function encrypts the password and
stores it in its encrypted form in the table. This stops prying eyes from seeing everybody’s password.
Scott can now connect to the MySQL server. Once connected, Scott will not be able to do anything
because haven’t given him any global or database privileges yet.
Providing Remote Access
If you wanted to allow Scott to connect from a remote machine, you would have to change the hostname in
the table to reflect this. Hostnames can be either IP addresses or domain names. For instance, a hostname
could be 10.1.1.50, or it could be staff.jsmc.org. This would allow Scott to connect remotely if he wwere