Update_priv Gives the user the ability to edit table data by using the UPDATE
statement.
Delete_priv Grants the user the ability to remove data from the database by using
the DELETE statement.
Create_priv Grants the user the ability to add tables and databases to the MySQL
server.
Drop_priv Gives the user the ability to delete tables and databases from the
MySQL server. This ability can be very dangerous in the hands of a user, so take
care when granting this privilege.
Reload_priv Allows the user to refresh the Grant tables by using the FLUSH
statement.
Shutdown_priv Allows the user to shut down the server.
Process_priv Grants the user the ability to look at the MySQL processes by using
the mysqladmin processlist command or with the SHOW PROCESSLIST
statement. Also gives the user the ability to kill these processes.
File_priv Allows the user to read and write files that reside on the MySQL server.
You should take special care when granting this permission. If it is not used
properly, a malicious person could overwrite system files on the server machine.
This is one of the reasons why MySQL should never run under the system’s root
user.
Grant_priv Allows the user to grant privileges to other users. This privilege should
be restricted to database administrators for obvious reasons.
References_priv This is not used for anything right now.
Index_priv Grants the user the ability to create indexes on tables. It also allows the
user to drop indexes. Create_priv and Drop_priv do not affect this privilege in
any way. If a user has DROP and CREATE privileges already, he or she must have
the Index_priv to create and drop indexes.
Alter_priv Grants the user the ability to change a table's structure. 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.