USER_PRIVILEGES

• 该表提供查询全局权限的信息，该表中的信息来自mysql.user表

• 该表为Memory引擎临时表

• 表定义语句

CREATE TEMPORARY TABLE `USER_PRIVILEGES` (
  `GRANTEE` varchar(81) NOT NULL DEFAULT '',
  `TABLE_CATALOG` varchar(512) 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：权限拥有者，即账户名称
• PRIVILEGE_TYPE：权限类型
• IS_GRANTABLE：拥有该权限的账户是否可以把该权限授予其他人，注意：只有帐号拥有grant option权限时才为YES，否则该字段为NO

• 表记录内容示例

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

# 查看USER_PRIVILEGES 表
root@localhost : information_schema 12:44:59>  select * from USER_PRIVILEGES where GRANTEE="'test_user'@'%'";
+-----------------+---------------+-------------------------+--------------+
| GRANTEE        | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
+-----------------+---------------+-------------------------+--------------+
| 'test_user'@'%' | def          | SELECT                  | NO          |
| 'test_user'@'%' | def          | INSERT                  | NO          |
| 'test_user'@'%' | def          | UPDATE                  | NO          |
| 'test_user'@'%' | def          | DELETE                  | NO          |
| 'test_user'@'%' | def          | CREATE                  | NO          |
| 'test_user'@'%' | def          | DROP                    | NO          |
| 'test_user'@'%' | def          | RELOAD                  | NO          |
| 'test_user'@'%' | def          | SHUTDOWN                | NO          |
| 'test_user'@'%' | def          | PROCESS                | NO          |
| 'test_user'@'%' | def          | FILE                    | NO          |
| 'test_user'@'%' | def          | REFERENCES              | NO          |
| 'test_user'@'%' | def          | INDEX                  | NO          |
| 'test_user'@'%' | def          | ALTER                  | NO          |
| 'test_user'@'%' | def          | SHOW DATABASES          | NO          |
| 'test_user'@'%' | def          | SUPER                  | NO          |
| 'test_user'@'%' | def          | CREATE TEMPORARY TABLES | NO          |
| 'test_user'@'%' | def          | LOCK TABLES            | NO          |
| 'test_user'@'%' | def          | EXECUTE                | NO          |
| 'test_user'@'%' | def          | REPLICATION SLAVE      | NO          |
| 'test_user'@'%' | def          | REPLICATION CLIENT      | NO          |
| 'test_user'@'%' | def          | CREATE VIEW            | NO          |
| 'test_user'@'%' | def          | SHOW VIEW              | NO          |
| 'test_user'@'%' | def          | CREATE ROUTINE          | NO          |
| 'test_user'@'%' | def          | ALTER ROUTINE          | NO          |
| 'test_user'@'%' | def          | CREATE USER            | NO          |
| 'test_user'@'%' | def          | EVENT                  | NO          |
| 'test_user'@'%' | def          | TRIGGER                | NO          |
| 'test_user'@'%' | def          | CREATE TABLESPACE      | NO          |
+-----------------+---------------+-------------------------+--------------+
28 rows in set (0.00 sec)

• 该表中的信息还可以使用show语句或者mysql.user表查看

# show语句
root@localhost : information_schema 12:45:35> show grants for test_user@'%';
+------------------------------------------------+
| Grants for test_user@%                        |
+------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test_user'@'%' |
+------------------------------------------------+
1 row in set (0.00 sec)

# 查询mysql.user表
root@localhost : information_schema 12:46:18> select * from mysql.user where user='test_user'\G;
*************************** 1. row ***************************
                  Host: %
                  User: test_user
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
          Reload_priv: Y
        Shutdown_priv: Y
          Process_priv: Y
            File_priv: Y
            Grant_priv: N
      References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
      Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
  Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
          x509_issuer: 
          x509_subject: 
        max_questions: 0
          max_updates: 0
      max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
authentication_string: *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29
      password_expired: N
password_last_changed: 2018-01-29 00:44:59
    password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

---

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