As you can see, there are two control points. One point is the connection verification, and the second
control point is the request verification. These control points offer a more secure environment for your
database. A person who can connect to the database may not be able to do anything once he or she is
in the database. This provides a very secure wall against would-be crackers disrupting your business
and protects the database from users who may do harm unintentionally.Connection Verification
Connection verification occurs the moment you try to connect to MySQL. Any connection to MySQL requires
a username, password, and hostname. The username is the name of the person who is trying to connect.
The password is an additional verification tool to ensure the person who is connecting is really who he or she
claims to be. The hostname is the name of the computer from which the user is connecting. MySQL can not
only limit a person from connecting, it can restrict a machine from connecting as well. You can allow or
disallow connections from entire domains if you want. This provides a very secure place for your data.
The connection verification process is pretty simple. MySQL checks the incoming request against the
information in the user grant table for a username, password, and hostname match. If it finds a match,
the user is allowed to make a connection. If MySQL fails to find a match within the user table, it denies
access (see Figure 17.1).
Figure 17.1 The connection verification stage.
Request Verification
Request verification occurs every time a user issues a query to the database. Every command that is issued
after a connection is made goes through the same process. This ensures that users work under the
restrictions that have been placed for them. This provides an extra blanket of security by preventing
malicious people from doing any harm to your data even if they get into the database.
The process again is pretty simple. Whenever a request is issued, MySQL first checks the permissions
given to the user at the user level. If the person has permissions here, he or she is allowed to do
whatever he or she wants to any database that is contained in the MySQL RDBMS, so permissions at
this level should be given out sparingly. If MySQL doesn’t find the necessary privileges to carry out the
command, it looks at the db table.
The db table is the next line of security. Permissions granted here apply only to the specified database.
A SELECT privilege given at this level allows the person to view all the data in all the tables in the
specified database. This is generally okay, but if you want to restrict access even further, you can hold
off permissions here and give them in the tables_priv grant table.
The tables_priv table is the next table MySQL looks at to see if a user has privileges to carry out the
command he or she is requesting. If the necessary privileges are found here, MySQL executes the
query. If not, MySQL looks in one more place—the columns_priv table.
The columns_priv table is the last place MySQL looks to see if a person has the right to carry out the
command. If the user does not have the permission here, MySQL returns an error, saying that the
request is denied. All of this happens very quickly—so quickly that performance is hardly affected, so it
behooves you to maintain a high level of security within your database. See Figure 17.2 for a diagram of
the request verification process.