Db The database that contains the tables to which you’re applying privileges.
User The username of the person to whom you are granting permissions.
Table_name The table name of the database on which you’re setting permissions.
This column is case sensitive.
Column_priv This column of either table controls the access a user has. It can
contain the following values: SELECT, INSERT, UPDATE, and REFERENCES. If more
than one privilege is granted, the fields must be separated by a comma.
Timestamp This column contains the timestamp indicating when changes were
made.
The Grantor and Table_priv columns are the only ones in the tables_priv table that do not
appear in the columns_priv table. The Grantor column holds the name of the person granting the
permissions. The Table_priv column maintains the permissions for the given table. The values it can
contain are: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT, REFERENCES, INDEX, and
ALTER.
The columns_priv table has only one column that does not appear in both tables; It is the
Column_name column. This column contains the name of the column that is affected by the
permissions granted in the Column_priv column.
You may be wondering how this works—it works very similarly to the user and db tables. If you want to
grant a user SELECT privileges for all the columns in a table, you can grant those privileges in the
tables_priv table. However, if do not want a user to have certain rights, you have to limit his or her
privileges in the column level.Applying Security Controls
Now take a look at the big picture to help give you a better understanding. This section will expand on the
previous Accounting and Human Resources example a little more. The scenario: You are the database
administrator for all the databases in your company. In each division of the company, you have appointed
superusers. These people help you with day-to-day administration tasks. And, of course, each division has
its own set of workers who have varying access needs to their particular databases. To keep everything as
secure as possible, you only grant people the privileges they need to do their jobs.
Bill is the superuser in Accounting, and Julie is the superuser in HR. Keeping with company policy, you
want to give Bill and Julie administrator rights to their databases only, so these two people would have
entries in the user table that would reflect their status. Because you are going to limit their access to
their respective databases, you would not grant them any privileges in this table. They would only have
their name, password, and Hostname. The db table is where you would grant all their privileges. By
doing it this way, you limit their access. You, on the other hand, are the database administrator and
would have global access. You would be the only person with full privileges in the user table. By
limiting who can do what, you are ensuring a more secure environment.
Because you have limited their access, you are the only one who can add new users and give them
permissions. Julie has a new person in HR. She wants that person to be able to update only a few
columns in the Employee_Benefits table, but she still wants that person to be able to view all the
records in the database. To do this, you give this person, Sheila, an entry in the user table. This is so
she can at least connect to the database. The next step is to grant SELECT privileges on all the tables in
the HR database. To limit Sheila’s update capability, you must enter her privileges in the tables_priv
and columns_priv tables.
Stages of Control
You will learn how to add users and their permissions shortly. For now, just understand that there is a
hierarchy of security in the MySQL database system. When a user connects to the MySQL database,
MySQL first looks in the user table to see if it can find a match for the hostname, username, and password.
If it can, the user is given access to the system. When he or she issues a query against the database,
MySQL first looks at the user table to see what privileges the user has. If the user has no privileges in that
table, it looks to the db table. Again, it searches the table for a match with the hostname, username, and
database. If it finds a match, it will look at the privileges the person has. If that person doesn’t have the
privileges needed to issue the query, MySQL will then search the tables_priv table and then the
columns_priv table for the permissions necessary to execute the query. If it cannot find any permissions,
an error will be generated. This all happens every time a query is performed in MySQL.