Learning Mysql

+-----------+-------+------+------------+----------------+...
... +---------------------+------------+-------------+
... | Timestamp | Table_priv | Column_priv |
... +---------------------+------------+-------------+
... | 2006-08-21 10:03:18 | Index | |
... | 2006-08-21 10:12:36 | | Update |
... +---------------------+------------+-------------+
2 rows inset 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 columns 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 inset sec)
The table structure is much the same as tables_priv
, except that it includes the Column name but notable 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 inmost 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 fora database when it’s accessed from a client or

Download 4.24 Mb.

Share with your friends: