Cream Of The Crop - MySQL

In addition to the three tables already discussed, newer versions of mySQL also come with two additional tables, the "tables_priv" and "columns_priv" tables. These allow a database administrator to restrict access to specific tables in a database, and specific columns of a specific table, respectively.

Here's what these two tables look like:

tables_priv:
+-------------+---------------------------+
| Field       | Type                      |
+-------------+---------------------------+
| Host        | char(60)                  |
| Db          | char(60)                  |
| User        | char(16)                  |
| Table_name  | char(60)                  |
| Grantor     | char(77)                  |
| Timestamp   | timestamp(14)             |
| Table_priv  | set('Select','Insert'...) |
| Column_priv | set('Select','Insert'...) |
+-------------+---------------------------+
columns_priv:
+-------------+----------------------------------------------+
| Field       | Type                                         |
+-------------+----------------------------------------------+
| Host        | char(60)                                     |
| Db          | char(60)                                     |
| User        | char(16)                                     |
| Table_name  | char(60)                                     |
| Column_name | char(60)                                     |
| Timestamp   | timestamp(14)                                |
| Column_priv | set('Select','Insert','Update','References') |
+-------------+----------------------------------------------+

The following example would restrict user to performing SELECT operations on table "cream" *only* - any attempt to run a SELECT query on another table within the same database would result in an error.

+-----------------+-------+------+------------+---------------+-------------+
| Host            | Db    | User | Table_name | Table_priv    | Column_priv |
+-----------------+-------+------+------------+---------------+-------------+
|lost.soul.com    | db563 | john | cream      | Select        |             |
+-----------------+-------+------+------------+---------------+-------------+

>>> More MySQL Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus