Databases Reference
In-Depth Information
...| N | N | N |...
...+------------------+----------------+---------------------+...
...+--------------------+--------------+
...| Alter_routine_priv | Execute_priv |
...+--------------------+--------------+
...| N | N |
...+--------------------+--------------+
1 row in set (0.08 sec)
Again, we've modified the output so it fits in the topic.
The tables_priv Table
The
tables_priv
table stores privileges for the table level. This is similar to the
db
table
but holds privilege values for
Host
,
Db
,
User
, and
Table_name
combinations. Consider
what happens when you grant
'bob'@'localhost'
the
INDEX
privilege for the
artist
table in the
music
database:
mysql>
GRANT INDEX on music.artist TO 'bob'@'localhost';
Query OK, 0 rows affected (0.00 sec)
A
SELECT
statement shows the effect:
mysql>
SELECT * FROM tables_priv WHERE User = 'bob';
+-----------+-------+------+------------+----------------+...
| Host | Db | User | Table_name | Grantor |...
+-----------+-------+------+------------+----------------+...
| localhost | music | bob | artist | root@localhost |...
+-----------+-------+------+------------+----------------+...
... +---------------------+------------+-------------+
... | Timestamp | Table_priv | Column_priv |
... +---------------------+------------+-------------+
... | 2006-08-21 10:03:18 | Index | |
... +---------------------+------------+-------------+
1 row in set (0.16 sec)
The structure is a little different from the other tables: the
tables_priv
table includes
who granted the privilege and when it was granted, and it explicitly lists the table
privileges in the
Table_priv
column.
The
Column_priv
column in the
tables_priv
table lists privileges that are available only
at column level for the user. Consider what happens if we grant
'bob'@'localhost'
the
UPDATE
privilege for the
album_name
column on the
album
table:
mysql>
GRANT UPDATE (album_name) ON music.album TO 'bob'@'localhost';
Query OK, 0 rows affected (0.12 sec)
Here's the result:
mysql>
SELECT * FROM tables_priv WHERE User = 'bob';
+-----------+-------+------+------------+----------------+...
| Host | Db | User | Table_name | Grantor |...
+-----------+-------+------+------------+----------------+...
| localhost | music | bob | artist | root@localhost |...
| localhost | music | bob | album | root@localhost |...
