Granting this privilege does not allow the user to add indexes to tables. The user must have those permissions as well to change the table.
By giving a user permissions at this level, you are giving him or her global access to the database. This means that a user who has DELETE privileges granted in the user table can delete records in any database that is in the MySQL server. There are times when you may not want to do this. For example, suppose that you are the administrator for a MySQL server that has two databases: one for Accounting and one for Human Resources. The Accounting database contains all the tables and data that are tracked by the business, such as AR (accounts receivable), AP (accounts payable), and Payroll. The Human Resources database contains all employee information. In a situation like this, you would want to give the users in Accounting the ability to delete their own records, but you wouldn’t want them to have the ability to delete records from the Human Resources database. However, you would want the users in Accounting to have the ability to view records from the HR database. If you were to give the Accounting users the DELETE privilege in the user table, they would have the ability to delete records from the HR database. So how do you prevent this? Read on.
The db Table
The db table contains the permissions for all the databases that are contained in your MySQL server.
Permissions granted here are given only for the named database. So, in the previous example, you could give DELETE permissions to the users at the database level instead of the user level.
The db table has most of the same columns as the user table with a few exceptions. Because this table governs permissions at the database level, there are no administrator-level privileges, such as Reload_priv, Shutdown_priv, Process_priv, and File_priv. These permissions do not relate to databases operations that can be performed on databases, so they are only found in the user table.
The only new column in the db table is Db. This is the database for which to apply these privileges.
The host Table
The host table, along with the db table, controls access by limiting the hosts that can connect to the database. This table has the same columns as the db table.
The columns_priv and tables_priv Tables
The columns_priv and tables_priv tables govern the permissions for a database’s tables and columns.
With MySQL, you can limit what a user can do down to the column in a table. These tables share the following columns:
Host The host from which the user is connecting.
- 186 -
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.