COLUMN_PRIVILEGES - xiaoboluo768/mysql-system-schema GitHub Wiki

该表提供查询关于列(字段)的权限信息，表中的内容来自mysql.column_priv列权限表（需要针对一个表的列单独授权之后才会有内容）

该表是Memory引擎临时表

表定义语句

CREATE TEMPORARY TABLE `COLUMN_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 '',
  `COLUMN_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：PRIVILEGE_TYPE 列值的权限对应的授予者(账户名)

TABLE_SCHEMA：PRIVILEGE_TYPE 列值的权限关联的表对应的库名

TABLE_NAME：PRIVILEGE_TYPE 列值的权限关联的表名

COLUMN_NAME：PRIVILEGE_TYPE 列值的权限关联的字段名

PRIVILEGE_TYPE：具体的列权限名称，注意：该字段值只显示一个权限名称，即，如果一个字段拥有多个可授予的列权限值，则在该表中会记录多行记录，每行PRIVILEGE_TYPE列值仅对应一个权限名称

IS_GRANTABLE：如果GRANTEE列值表示的授予者还同时拥有grant option权限，则该列值为YES，否则为NO

表记录完整字段内容示例

# 针对某个用户授予某表某列的SELECT,INSERT,UPDATE权限
root@localhost : information_schema 09:37:43> grant select(id),insert(id),update(id) on sbtest.sbtest1 to xx@'%' identified by 'xx';
Query OK, 0 rows affected, 1 warning (0.00 sec)

# select语句方式查询该表中的权限信息
root@localhost : information_schema 09:38:32> select * from COLUMN_PRIVILEGES;
+----------+---------------+--------------+------------+-------------+----------------+--------------+
| GRANTEE  | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+----------+---------------+--------------+------------+-------------+----------------+--------------+
| 'xx'@'%' | def          | sbtest      | sbtest1    | id          | SELECT        | NO          |
| 'xx'@'%' | def          | sbtest      | sbtest1    | id          | INSERT        | NO          |
| 'xx'@'%' | def          | sbtest      | sbtest1    | id          | UPDATE        | NO          |
+----------+---------------+--------------+------------+-------------+----------------+--------------+
3 rows in set (0.00 sec)

PS：该表中的信息还可以通过show语句方式查询(select和show方式虽然都能查询该表中的列权限信息，但是查询的结果展示方式有所不同)

# 语法
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

# 示例
root@localhost : information_schema 09:39:10> show grants for 'xx'@'%';
+-------------------------------------------------------------------------------+
| Grants for xx@%                                                              |
+-------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xx'@'%'                                                |
| GRANT SELECT (id), INSERT (id), UPDATE (id) ON `sbtest`.`sbtest1` TO 'xx'@'%' |
+-------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

上一篇：COLUMNS表 |下一篇：ENGINES表

COLUMN_PRIVILEGES - xiaoboluo768/mysql-system-schema GitHub Wiki

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

⚠️ GitHub.com Fallback ⚠️