TABLE_PRIVILEGES - xiaoboluo768/mysql-system-schema GitHub Wiki

该表提供查询关于表级别权限信息，该表中的内容来自mysql.tables_priv

该表为Memory引擎临时表

表定义语句

CREATE TEMPORARY TABLE `TABLE_PRIVILEGES` (
  `GRANTEE` varchar(81) NOT NULL DEFAULT '',
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `PRIVILEGE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `IS_GRANTABLE` varchar(3) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8

表字段含义

GRANTEE：权限拥有者，即账户名称

TABLE_SCHEMA：表级别权限涉及的表所属的数据库名称

TABLE_NAME：表级别权限涉及的表名称

PRIVILEGE_TYPE：权限类型，有效值为：SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、REFERENCES、ALTER、INDEX、CREATE VIEW、SHOW VIEW、TRIGGER

IS_GRANTABLE：拥有该权限的用户是否可以将此权限授予给其他人，注意：只有帐号拥有grant option权限时才为YES，否则该字段为NO

表记录内容示例

# 创建测试帐号
root@localhost : information_schema 12:17:33> grant all on sbtest.sbtest1 to 'test_table'@'%' identified by 'test';
Query OK, 0 rows affected, 1 warning (0.00 sec)

# 查询该帐号的表级别权限
root@localhost : information_schema 12:18:20> select * from TABLE_PRIVILEGES where GRANTEE="'test_table'@'%'";
+------------------+---------------+--------------+------------+----------------+--------------+
| GRANTEE          | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+------------------+---------------+--------------+------------+----------------+--------------+
| 'test_table'@'%' | def          | sbtest      | sbtest1    | SELECT        | NO          |
| 'test_table'@'%' | def          | sbtest      | sbtest1    | INSERT        | NO          |
| 'test_table'@'%' | def          | sbtest      | sbtest1    | UPDATE        | NO          |
| 'test_table'@'%' | def          | sbtest      | sbtest1    | DELETE        | NO          |
| 'test_table'@'%' | def          | sbtest      | sbtest1    | CREATE        | NO          |
| 'test_table'@'%' | def          | sbtest      | sbtest1    | DROP          | NO          |
| 'test_table'@'%' | def          | sbtest      | sbtest1    | REFERENCES    | NO          |
| 'test_table'@'%' | def          | sbtest      | sbtest1    | INDEX          | NO          |
| 'test_table'@'%' | def          | sbtest      | sbtest1    | ALTER          | NO          |
| 'test_table'@'%' | def          | sbtest      | sbtest1    | CREATE VIEW    | NO          |
| 'test_table'@'%' | def          | sbtest      | sbtest1    | SHOW VIEW      | NO          |
| 'test_table'@'%' | def          | sbtest      | sbtest1    | TRIGGER        | NO          |
+------------------+---------------+--------------+------------+----------------+--------------+
12 rows in set (0.00 sec)

PS：该表中的内容还可以使用show语句和mysql.tables_priv表查看

# show语句
root@localhost : information_schema 12:19:14> show grants for test_table@'%';
+----------------------------------------------------------------+
| Grants for test_table@%                                        |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_table'@'%'                        |
| GRANT ALL PRIVILEGES ON `sbtest`.`sbtest1` TO 'test_table'@'%' |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)

# mysql.tables_priv表
root@localhost : information_schema 12:21:01> select * from mysql.tables_priv where user='test_table'\G;
*************************** 1. row ***************************
      Host: %
        Db: sbtest
      User: test_table
Table_name: sbtest1
    Grantor: root@localhost
  Timestamp: 0000-00-00 00:00:00
Table_priv: Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger
Column_priv: 
1 row in set (0.00 sec)

上一篇：TABLE_CONSTRAINTS表 |下一篇：TRIGGERS表

TABLE_PRIVILEGES - xiaoboluo768/mysql-system-schema GitHub Wiki

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️