上期《Server 层混杂信息字典表 | 全方位认识 information_schema（中）》继续为大家介绍了部分关于Server层混杂信息字典表的知识，本期“Server层混杂信息字典表（下）”将继续为大家介绍。
10、SCHEMA_PRIVILEGES
该表提供查询关于库级别的权限信息，每种类型的库级别权限记录一行信息
-
该表中的信息来自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)
11、TABLE_PRIVILEGES
该表提供查询关于表级别权限信息，该表中的内容来自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)
12、USER_PRIVILEGES
该表提供查询全局权限的信息，该表中的信息来自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
| 作者简介
罗小波·沃趣科技数据库技术专家
