Databases Reference
In-Depth Information
+-----------+-------+------+------------+----------------+...
... +---------------------+------------+-------------+
... | Timestamp | Table_priv | Column_priv |
... +---------------------+------------+-------------+
... | 2006-08-21 10:03:18 | Index | |
... | 2006-08-21 10:12:36 | | Update |
... +---------------------+------------+-------------+
2 rows in set (0.25 sec)
You can see that that the
Update
privilege is now available on at least one of the columns
of the
album
table, but the
tables_priv
table doesn't show which column or columns.
The columns_priv Table
The
columns_priv
table lists which privileges are available for which columns. It's only
accessed if the
tables_priv
table says that a privilege is available for one or more col-
umns in a table and that privilege isn't already available at the table level. We've granted
UPDATE
to
'bob'@'localhost'
for the
album_name
column in the
album
table. Here's what's
stored:
mysql>
SELECT * FROM columns_priv WHERE User = 'bob';
+-----------+-------+------+------------+-------------+...
| Host | Db | User | Table_name | Column_name |...
+-----------+-------+------+------------+-------------+...
| localhost | music | bob | album | album_name |...
+-----------+-------+------+------------+-------------+...
... +---------------------+-------------+
... | Timestamp | Column_priv |
... +---------------------+-------------+
... | 2006-08-21 10:12:36 | Update |
... +---------------------+-------------+
1 row in set (0.07 sec)
The table structure is much the same as
tables_priv
, except that it includes the
Col
umn_name
but no table privileges.
The host Table
The remaining privilege table is
host
. This table isn't modified or accessed by the
GRANT
and
REVOKE
statements. Therefore, it can be maintained only by SQL queries, and
so remains unused in most MySQL installations. Indeed, you can skip this advanced
section if you want.
The server verifies that users have authorization to perform an operation by checking
the global privileges listed for them in the
user
table. If they don't have the required
privilege for all databases, then the server checks the
db
table to see whether they have
that privilege for the active database. If the
Host
field in the
db
table is blank, the user's
privileges for the database vary depending on the host they're connecting from. These
privileges are stored in the
host
table and are verified against the global settings in the
db
table to determine the privileges for a database when it's accessed from a client or
