本文共 12354 字，大约阅读时间需要 41 分钟。
上期继续为大家介绍了部分关于Server层混杂信息字典表的知识，本期“Server层混杂信息字典表（下）”将继续为大家介绍。
该表提供查询关于库级别的权限信息，每种类型的库级别权限记录一行信息
该表中的信息来自mysql.db表
该表为Memory引擎临时表
下面是该表中存储的信息内容
# 创建一个库级别权限的帐号 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)
字段含义如下（该表中所有字段为 "MySQL extension" 列）
GRANTEE：表示权限对应的账户名称
TABLE_CATALOG：该字段总是为def
TABLE_SCHEMA：表示权限对应的数据库名称
PRIVILEGE_TYPE：表示权限列别，如：SELECT、TRIGGER等
IS_GRANTABLE：表示拥有该权限的账户是否可给其他人授予该权限，注意：只有帐号拥有grant option权限时才为YES，否则该字段为NO
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)
该表提供查询关于表级别权限信息，该表中的内容来自mysql.tables_priv
该表为Memory引擎临时表
下面是该表中存储的信息内容
# 创建测试帐号 root@localhost : information_schema 12:17:33> grant all on sbtest.sbtest1 to 'test_table'@'%' identified by 'test'; Query OK, 0 rows affected, 1 warning (0.00 sec) # 查询该帐号的表级别权限 root@localhost : information_schema 12:18:20> select * from TABLE_PRIVILEGES where GRANTEE="'test_table'@'%'"; +------------------+---------------+--------------+------------+----------------+--------------+ | GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE | +------------------+---------------+--------------+------------+----------------+--------------+ | 'test_table'@'%' | def | sbtest | sbtest1 | SELECT | NO | | 'test_table'@'%' | def | sbtest | sbtest1 | INSERT | NO | | 'test_table'@'%' | def | sbtest | sbtest1 | UPDATE | NO | | 'test_table'@'%' | def | sbtest | sbtest1 | DELETE | NO | | 'test_table'@'%' | def | sbtest | sbtest1 | CREATE | NO | | 'test_table'@'%' | def | sbtest | sbtest1 | DROP | NO | | 'test_table'@'%' | def | sbtest | sbtest1 | REFERENCES | NO | | 'test_table'@'%' | def | sbtest | sbtest1 | INDEX | NO | | 'test_table'@'%' | def | sbtest | sbtest1 | ALTER | NO | | 'test_table'@'%' | def | sbtest | sbtest1 | CREATE VIEW | NO | | 'test_table'@'%' | def | sbtest | sbtest1 | SHOW VIEW | NO | | 'test_table'@'%' | def | sbtest | sbtest1 | TRIGGER | NO | +------------------+---------------+--------------+------------+----------------+--------------+ 12 rows in set (0.00 sec)
表字段含义：
GRANTEE：权限拥有者，即账户名称
TABLE_CATALOG：该字段总是为def
TABLE_SCHEMA：表级别权限涉及的表所属的数据库名称
TABLE_NAME：表级别权限涉及的表名称
PRIVILEGE_TYPE：权限类型，有效值为：SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、REFERENCES、ALTER、INDEX、CREATE VIEW、SHOW VIEW、TRIGGER
IS_GRANTABLE：拥有该权限的用户是否可以将此权限授予给其他人，注意：只有帐号拥有grant option权限时才为YES，否则该字段为NO
PS：该表中的内容还可以使用show语句和mysql.tables_priv表查看
# show语句 root@localhost : information_schema 12:19:14> show grants for test_table@'%'; +----------------------------------------------------------------+ | Grants for test_table@% | +----------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test_table'@'%' | | GRANT ALL PRIVILEGES ON `sbtest`.`sbtest1` TO 'test_table'@'%' | +----------------------------------------------------------------+ 2 rows in set (0.00 sec) # mysql.tables_priv表 root@localhost : information_schema 12:21:01> select * from mysql.tables_priv where user='test_table'\G; *************************** 1. row *************************** Host: % Db: sbtest User: test_table Table_name: sbtest1 Grantor: root@localhost Timestamp: 0000-00-00 00:00:00 Table_priv: Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger Column_priv: 1 row in set (0.00 sec)
该表提供查询全局权限的信息，该表中的信息来自mysql.user表
该表为Memory引擎临时表
下面是该表中存储的信息内容
# 创建测试帐号 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)
字段含义如下（该表中所有的字段都为 "MySQL extension" 列）
GRANTEE：权限拥有者，即账户名称
TABLE_CATALOG：该字段总是为def
PRIVILEGE_TYPE：权限类型
IS_GRANTABLE：拥有该权限的账户是否可以把该权限授予其他人，注意：只有帐号拥有grant option权限时才为YES，否则该字段为NO
该表中的信息还可以使用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)
本期内容就介绍到这里，本期内容参考链接如下：
https://dev.mysql.com/doc/refman/5.7/en/status-table.html
https://dev.mysql.com/doc/refman/5.7/en/variables-table.html
https://dev.mysql.com/doc/refman/5.7/en/optimizer-trace-table.html
https://dev.mysql.com/doc/refman/5.7/en/plugins-table.html
https://dev.mysql.com/doc/refman/5.7/en/processlist-table.html
https://dev.mysql.com/doc/refman/5.7/en/profiling-table.html
https://dev.mysql.com/doc/refman/5.7/en/character-sets-table.html
https://dev.mysql.com/doc/refman/5.7/en/collations-table.html
https://dev.mysql.com/doc/refman/5.7/en/column-privileges-table.html
https://dev.mysql.com/doc/refman/5.7/en/schema-privileges-table.html
https://dev.mysql.com/doc/refman/5.7/en/table-privileges-table.html
https://dev.mysql.com/doc/refman/5.7/en/user-privileges-table.html
| 作者简介
罗小波·沃趣科技数据库技术专家
转载地址：http://tntyx.baihongyu.com/