mysql is_grantable_SCHEMA_PRIVILEGES

该表提供查询关于库级别的权限信息，每种类型的库级别权限记录一行信息

该表中的信息来自mysql.db表

该表为Memory引擎临时表

表定义语句

CREATE TEMPORARY TABLE `SCHEMA_PRIVILEGES` (

`GRANTEE` varchar(81) NOT NULL DEFAULT '',

`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',

`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',

`PRIVILEGE_TYPE` varchar(64) NOT NULL DEFAULT '',

`IS_GRANTABLE` varchar(3) NOT NULL DEFAULT ''

) ENGINE=MEMORY DEFAULT CHARSET=utf8

表字段含义(该表中所有字段为 "MySQL extension" 列)

GRANTEE：表示权限对应的账户名称

TABLE_SCHEMA：表示权限对应的数据库名称

PRIVILEGE_TYPE：表示权限列别，如：SELECT、TRIGGER等

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

表记录内容示例

# 创建一个库级别权限的帐号

root@localhost : information_schema 06:50:42> grant all on sbtest.* to test@'%' identified by 'test';

Query OK, 0 rows affected, 1 warning (0.01 sec)

# 查询这个帐号的库级别权限

root@localhost : information_schema 06:52:31> select * from SCHEMA_PRIVILEGES where GRANTEE="'test'@'%'";

+------------+---------------+--------------+-------------------------+--------------+

| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE | IS_GRANTABLE |

+------------+---------------+--------------+-------------------------+--------------+

| 'test'@'%' | def | sbtest | SELECT | NO |

| 'test'@'%' | def | sbtest | INSERT | NO |

| 'test'@'%' | def | sbtest | UPDATE | NO |

| 'test'@'%' | def | sbtest | DELETE | NO |

| 'test'@'%' | def | sbtest | CREATE | NO |

| 'test'@'%' | def | sbtest | DROP | NO |

| 'test'@'%' | def | sbtest | REFERENCES | NO |

| 'test'@'%' | def | sbtest | INDEX | NO |

| 'test'@'%' | def | sbtest | ALTER | NO |

| 'test'@'%' | def | sbtest | CREATE TEMPORARY TABLES | NO |

| 'test'@'%' | def | sbtest | LOCK TABLES | NO |

| 'test'@'%' | def | sbtest | EXECUTE | NO |

| 'test'@'%' | def | sbtest | CREATE VIEW | NO |

| 'test'@'%' | def | sbtest | SHOW VIEW | NO |

| 'test'@'%' | def | sbtest | CREATE ROUTINE | NO |

| 'test'@'%' | def | sbtest | ALTER ROUTINE | NO |

| 'test'@'%' | def | sbtest | EVENT | NO |

| 'test'@'%' | def | sbtest | TRIGGER | NO |

+------------+---------------+--------------+-------------------------+--------------+

18 rows in set (0.00 sec)

PS：该表中记录的权限也可以通过show语句查询或者查询mysql.db表

# show语句查询

root@localhost : information_schema 06:53:19> show grants for test@'%';

+--------------------------------------------------+

| Grants for test@% |

+--------------------------------------------------+

| GRANT USAGE ON *.* TO 'test'@'%' |

| GRANT ALL PRIVILEGES ON `sbtest`.* TO 'test'@'%' |

+--------------------------------------------------+

2 rows in set (0.00 sec)

# 查询mysql.db表

root@localhost : information_schema 06:52:37> select * from mysql.db where user='test'\G;

*************************** 1. row ***************************

Host: %

Db: sbtest

User: test

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Execute_priv: Y

Event_priv: Y

Trigger_priv: Y

1 row in set (0.00 sec)