user_privileges mysql_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)