tables_priv

• 该表提供查询表表级别权限信息，与db表类似，但粒度更细，tables_priv表中记录的权限信息代表着用户是否可以使用这些权限访问某个表中的所有列
• 表结构定义

CREATE TABLE `tables_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Grantor` char(93) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Table_priv` set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`),
  KEY `Grantor` (`Grantor`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Table privileges';

• 表字段含义

• host、db、user、Table_name四个列官方称为范围列，可以理解为这两个字段构成了允许访问的客户端范围以及客户端可以访问的表对象资源范围
• Table_priv和Column_priv官方称为权限列，对应这表级别权限和列级别权限，需要注意的是，这两列权限列与user和db表不同，这两列是set类型，记录着表级别和列级别的权限集合，而不是对应具体的某个权限。Table_priv对应着表级别的'Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger'权限，Column_priv对应着列级别的'Select','Insert','Update','References'权限
• 其他字段：

* Grantor：代表该表中记录的用户权限被谁授予的，即该表中的用户权限在被授予时的current_user函数返回的用户值(account形式)

* Timestamp：代表授予Grantor表示的帐号权限时的时间戳

• 表记录内容示例

root@localhost : mysql 12:54:14> select * from tables_priv;
+-----------+--------+---------------+------------+----------------+---------------------+----------------------------------------------------------------------------------------------+----------------------+
| Host      | Db     | User          | Table_name | Grantor        | Timestamp           | Table_priv                                                                                   | Column_priv          |
+-----------+--------+---------------+------------+----------------+---------------------+----------------------------------------------------------------------------------------------+----------------------+
| localhost | sys    | mysql.sys     | sys_config | root@localhost | 2017-07-01 14:31:32 | Select                                                                                       |                      |
| localhost | mysql  | mysql.session | user       | root@localhost | 2017-12-11 23:41:19 | Select                                                                                       |                      |
| %         | sbtest | xx            | sbtest1    | root@localhost | 0000-00-00 00:00:00 |                                                                                              | Select,Insert,Update |
| %         | sbtest | test_table    | sbtest1    | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger |                      |
+-----------+--------+---------------+------------+----------------+---------------------+----------------------------------------------------------------------------------------------+----------------------+
4 rows in set (0.00 sec)

---

上一篇：db表 |下一篇：columns_priv表