Managing Privileges with SQL | 341

...| N | N | N |...
...+------------------+----------------+---------------------+...
...+--------------------+--------------+
...| Alter_routine_priv | Execute_priv |
...+--------------------+--------------+
...| N | N |
...+--------------------+--------------+
1 row inset sec)
Again, we’ve modified the output so it fits in the book.
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 inset 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 |...

Download 4.24 Mb.

Share with your friends: