Server 层混杂信息字典表 | 全方位认识 information_schema(下)-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

Server 层混杂信息字典表 | 全方位认识 information_schema(下)

简介: 上期《Server 层混杂信息字典表 | 全方位认识 information_schema(中)》继续为大家介绍了部分关于Server层混杂信息字典表的知识,本期“Server层混杂信息字典表(下)”将继续为大家介绍。

上期《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:37select * 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:01select * 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:18select * 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



| 作者简介

罗小波·沃趣科技数据库技术专家

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章