The MySQL Grant Tables (
Page 1 of 4 )
One of the most powerful aspects of the MySQL server is the amazing amount of control the administrator has over each user's intended behavior. This control can restrict user privileges over a general part of the server, such as limited access to an entire database, but can also be as specific as limiting privileges for a specific table or even column.One of the most powerful aspects of the MySQL server (
http://www.mysql.com) is the amazing amount of
control the administrator has over each user's intended behavior. This control
can restrict user privileges over a general part of the server, such as limited
access to an entire database, but can also be as specific as limiting privileges
for a specific table or even column. This article will serve to explain the
process in which the MySQL server grants/revokes these user privileges,
highlighting in particular the newest additions to the MySQL privilege system,
the
tables_priv and
columns_priv tables. Please keep in mind that
the GRANT/REVOKE commands detailed later in this article are only relevant to
MySQL version 3.22.11 and up. These commands are irrelevant to any previous
version.
The MySQL privilege system is controlled within the MySQL
database. There are currently 5 tables that provide this control; the
user,
db,
host,
tables_priv and
columns_priv
tables. These tables all vary slightly in purpose, yet all serve the same
function which is to verify that the user is doing what the user is allowed to
do. Each table can be broken down into two categories of fields: scope fields
and privilege fields. The scope fields identify a host, user or database. The
privilege fields determine which actions can be performed in reference to that
host, user or database. Let's take a brief look at each table's
function:
- user - Determines whether or not the connecting user is allowed to
connect to the server. Assuming the connection is allowable, the privilege
fields contain the user's global privileges.
- db - Determines which users can access which databases from which
hosts. The privilege contained within the db table apply to the database
identified within this table.
- host - The host table is used when you want to extend an entry within
the db table's range. For example, if a certain db is to be accessed by more
than one host, then the superuser would leave the host column empty within the
db table and fill the host table with all of the necessary hostnames.
- tables_priv - In principle works just like the db table, except that
it is used for tables instead of databases. This table also contains one other
field category (Other) in which a timestamp and grantor column is stored.The
tables_priv table will be explained in further detail later in this article.
- columns_priv - Works just like the db and tables_priv tables, except
that it provides access privileges for certain columns of certain tables. This
table also contains one other field category (Other) in which a timestamp column
is stored. The columns_priv table will be explained in further detail later in
this article.
Let's move on to an explantion of MySQL's
user-authorization procedure.
Part 1: The MySQL Access Control
Process.
How do the MySQL Grant tables work anyway?
Part 2: The
tables_priv and
columns_priv grant tables.
An explanation and
several examples relating to MySQL's tables_priv table.
Part 3:
References
An explanation and several examples relating to MySQL's
columns_priv grant table.
/ 16 
