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)

---

上一篇：SCHEMATA表 |下一篇：STATISTICS表