系列文章参考自《MySQL 性能优化金字塔法则》，删除了书里重复说明和过于复杂的一些解释，完整版请参考原书。

前一篇有提到，information_schema 下的表都是非持久的，重启数据就会丢失，那么这些数据来自哪里呢？一部分来自ibdata共享表空间中的数据字典表的映射，而另一部分就来自 mysql 系统库下的持久表。

 

mysql 系统库中包含以下类型的表：

  • 权限系统表与访问权限控制系统
  • 数据库对象信息记录表
  • 统计信息记录表
  • 优化器成本记录表
  • 时区信息记录表
  • 复制信息记录表
  • 日志信息记录表

下面先来看第一部分，权限系统表与访问权限控制系统

 

一、 权限系统表

MySQL 访问权限系统表包含如下几张表：

  • user：包含用户、全局权限和其他非权限列表（安全配置选项和资源控制选项列）

  • db：数据库级别的权限表

  • tables_priv：表级别的权限表

  • columns_priv：列级权限表

  • procs_priv：存储过程和函数权限表

  • proxies_priv：代理用户权限表

 

1. user表

提供查询全局权限信息，可查询用户是否拥有该实例下所有数据库的相应全局权限。表中信息决定是否允许用户连接。

在user表中有任意一个权限列为Y的用户，就被认为拥有全局权限，所以用户在使用show databases或者使用information_schema的schemata表查询时，可以查询到所有数据库名称列表。

select * from mysql.user limit 1\G;

*************************** 1. row ***************************
              Host: %
              User: qfsys
       Select_priv: Y
       Insert_priv: N
       Update_priv: N
       Delete_priv: N
       Create_priv: N
         Drop_priv: N
       Reload_priv: Y
     Shutdown_priv: Y
      Process_priv: Y
         File_priv: Y
        Grant_priv: N
   References_priv: N
        Index_priv: N
        Alter_priv: N
      Show_db_priv: N
        Super_priv: Y
 Create_tmp_table_priv: N
  Lock_tables_priv: Y
      Execute_priv: N
   Repl_slave_priv: Y
  Repl_client_priv: Y
  Create_view_priv: N
    Show_view_priv: N
   Create_routine_priv: N
Alter_routine_priv: N
  Create_user_priv: N
        Event_priv: N
      Trigger_priv: N
Create_tablespace_priv: N
          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: *3B3D7D2FD587C29C730F36CD52B4BA8CCF4C744F
  password_expired: N
 password_last_changed: 2017-07-01 14:37:32
 password_lifetime: NULL
    account_locked: N
1 row in set (0.00 sec)

表字段含义：

  • 范围列：host和user。

  • 权限列：Select_priv到Create_tablespace_priv，每列对应一个具体的权限，为Y代表有权限，为N代表没权限

  • 以下列官方称为安全列，与客户端与服务端之间的安全、加密通讯有关 
    * ssl_type：如果用户配置了使用加密ssl连接，则该字段记录用户使用的加密证书类型 
    * ssl_cipher：表示用于SSL连接握手中可能使用到的密码列表 
    * x509_issuer：x509证书相关字段 
    * x509_subject：x509证书相关字段 
    * plugin：该用户使用的密码认证插件名称 
    * authentication_string：表示用户密码的md5加密字符串 
    * password_expired：用户密码是否会过期，为Y表示用户密码会过期，为N表示用户密码永不过期 
    * password_last_changed：表示用户密码的最近一次修改时间，如果使用MySQL内建的认证插件(mysql_native_password or sha256_password)则该字段为非空，使用外部认证插件则该字段为空。
    * password_lifetime：如果password_expired字段为Y，则字段记录该用户密码距离过期剩余的天数；如果用户未单独指定该值，则使用default_password_lifetime的值代替；当password_lifetime为NULL且default_password_lifetime为0，或者password_lifetime为0时，表示该用户密码永不过期 
    * account_locked：代表用户当前状态是锁定状态还是处于激活可用状态

  • 以下列官方称为资源控制列，用于限制用户的一些访问资源 
    * max_questions：代表所有用户每小时的最大并发查询数 
    * max_updates：代表所有用户每小时最大并发更新次数 
    * max_connections：代表所有用户每小时的最大并发连接数 
    * max_user_connections：代表该用户每小时的最大并发连接数

 

2. db表

提供库级别对象权限信息， 可查询用户是否有相应权限操作指定数据库下的所有对象。

select * from db limit 1\G;

*************************** 1. row ***************************
             Host: localhost
               Db: performance_schema
             User: mysql.session
      Select_priv: Y
      Insert_priv: N
      Update_priv: N
      Delete_priv: N
      Create_priv: N
        Drop_priv: N
       Grant_priv: N
  References_priv: N
       Index_priv: N
       Alter_priv: N
Create_tmp_table_priv: N
 Lock_tables_priv: N
 Create_view_priv: N
   Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
     Execute_priv: N
       Event_priv: N
     Trigger_priv: N
1 row in set (0.00 sec)

表字段含义：

  • 范围列：host、db、user三列

  • xxx_priv：与user表的xxx_priv字段含义相同。与user表相比少了部分字段，说明少的字段对应的权限是全局范围的，不区分库表级别

 

3. tables_priv

表级别权限信息，代表用户对表或列有哪些权限。

select * from tables_priv;
+-----------+--------+---------------+------------+----------------+---------------------+----------------------------------------------------------------------------------------------+----------------------+
| Host      | Db     | User          | Table_name | Grantor        | Timestamp           | Table_priv                                                                                   | Column_priv          |
+-----------+--------+---------------+------------+----------------+---------------------+----------------------------------------------------------------------------------------------+----------------------+
| localhost | sys    | mysql.sys     | sys_config | root@localhost | 2017-07-01 14:31:32 | Select                                                                                       |                      |
| localhost | mysql  | mysql.session | user       | root@localhost | 2017-12-11 23:41:19 | Select                                                                                       |                      |
| %         | sbtest | xx            | sbtest1    | root@localhost | 0000-00-00 00:00:00 |                                                                                              | Select,Insert,Update |
| %         | sbtest | test_table    | sbtest1    | root@localhost | 0000-00-00 00:00:00 | Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger |                      |
+-----------+--------+---------------+------------+----------------+---------------------+----------------------------------------------------------------------------------------------+----------------------+
4 rows in set (0.00 sec)

表字段含义：

  • 范围列：host、db、user、Table_name

  • 权限列：Table_priv和Column_priv。Table_priv对应表级的Select,Insert,Update,Delete,Create,Drop,Grant,References,Index,Alter,Create View,Show view,Trigger权限；Column_priv对应列级的Select,Insert,Update,References权限

  • Grantor：该用户权限被谁授予的

  • Timestamp：授权时间

 

4. columns_priv

列级别权限信息，用户可以使用哪些权限操作某个表的指定列。该表的Column_priv列含义与tables_priv表中的Column_priv列含义相同。

root@localhost : mysql 12:55:13> select * from columns_priv;
+------+--------+------+------------+-------------+---------------------+----------------------+
| Host | Db     | User | Table_name | Column_name | Timestamp           | Column_priv          |
+------+--------+------+------------+-------------+---------------------+----------------------+
| %    | sbtest | xx   | sbtest1    | id          | 0000-00-00 00:00:00 | Select,Insert,Update |
+------+--------+------+------------+-------------+---------------------+----------------------+
1 row in set (0.00 sec)

 

5. procs_priv

存储程序权限信息，用户可以使用哪些权限操作指定的存储程序（存储过程和函数）

select * from procs_priv; -- 该表权限数据为空，目前并未找到使表中填充数据的方法

表字段含义：

  • 范围列：host、db、user、Routine_name、Routine_type

  • 权限列：Proc_priv。是一个集合类型，代表存储程序的Execute,Alter Routine,Grant权限

  • Timestamp与Grantor含义同tables_priv表

 

6. proxies_priv

代理用户权限信息。用户可以充当哪些用户的代理，以及是否可以将PROXY权限授予其他用户。

如果一个用户需要将它的PROXY权限授予其他帐号，那么它必须在该表中有一行权限信息，且With_grant字段必须为1。

root@localhost : mysql 12:58:16> select * from proxies_priv;
+-----------+------+--------------+--------------+------------+----------------------+---------------------+
| Host      | User | Proxied_host | Proxied_user | With_grant | Grantor              | Timestamp           |
+-----------+------+--------------+--------------+------------+----------------------+---------------------+
| localhost | root |              |              |          1 | boot@connecting host | 0000-00-00 00:00:00 |
+-----------+------+--------------+--------------+------------+----------------------+---------------------+
1 row in set (0.00 sec)

表字段含义：

  • Proxied_host和Proxied_user表示被授予proxy权限的用户host和用户名

  • 其他列的含义和tables_priv含义相同

 

注意权限表中一些字段有长度存储限制，具体如下：

  • Host, Proxied_host：长度限制 60个字符

  • User, Proxied_user：长度限制 32个字符

  • Password：长度限制 41个字符

  • Db：长度限制 64个字符

  • Table_name：长度限制 64个字符

  • Column_name：长度限制 64个字符

  • Routine_name：长度限制 64个字符

 

二、 访问权限控制系统

mysql 系统库提供了前文中的六张表存放不同权限范围的用户权限数据，这些表共同组成了MySQL 的访问权限系统。MySQL在启动时将这些表的内容读入内存，后续针对用户的访问控制决策基于权限表的内存副本实现。

MySQL权限系统的主要功能是对从给定主机连接到MySQL Server的用户进行身份验证，并校验该用户在该Server中的数据库对象访问的权限，另外还包括管理匿名用户访问和授予特定的MySQL权限的功能（如执行 LOAD DATA INFILE 语句和管理操作权限等）。

 

1. 访问控制阶段

当用户使用客户端程序连接到MySQL时，MySQL的访问控制分为如下两个阶段：

  • 连接阶段：Server根据身份标识（host+user）在mysql.user表中查询相关信息，确定接受还是拒绝该用户的连接。如果查询到了用户记录，则校验用户提供的帐号密码是否正确（包括密码是否已过期），如果密码不正确或已过期则拒绝连接。

  • 权限验证阶段：用户连接成功之后，Server会检查其他权限表，判断用户发出的每个sql是否有足够权限执行。

如果某用户在建立连接后，权限发生了变更，该用户执行下一条语句时这些权限变更不一定会立即生效。如果未生效需要执行flush privileges;语句。

 

2、MySQL 提供了哪些权限

MySQL 提供的权限列表如下：

show privileges;

+-------------------------------------------------------++-------------------------+--------------------------------------- 
| Privilege               | Context                               |     Comment                                               |
+-------------------------+---------------------------------------    +-------------------------------------------------------+
| Alter                   | Tables                                |     To alter the table                                    |
| Alter routine           | Functions,Procedures                  |     To alter or drop stored functions/procedures          |
| Create                  | Databases,Tables,Indexes              |     To create new databases and tables                    |
| Create routine          | Databases                             |     To use CREATE FUNCTION/PROCEDURE                      |
| Create temporary tables | Databases                             |     To use CREATE TEMPORARY TABLE                         |
| Create view             | Tables                                |     To create new views                                   |
| Create user             | Server Admin                          |     To create new users                                   |
| Delete                  | Tables                                |     To delete existing rows                               |
| Drop                    | Databases,Tables                      |     To drop databases, tables, and views                  |
| Event                   | Server Admin                          |     To create, alter, drop and execute events             |
| Execute                 | Functions,Procedures                  |     To execute stored routines                            |
| File                    | File access on server                 |     To read and write files on the server                 |
| Grant option            | Databases,Tables,Functions,Procedures |     To give to other users those privileges you possess   |
| Index                   | Tables                                |     To create or drop indexes                             |
| Insert                  | Tables                                |     To insert data into tables                            |
| Lock tables             | Databases                             |     To use LOCK TABLES (together with SELECT privilege)   |
| Process                 | Server Admin                          |     To view the plain text of currently executing queries |
| Proxy                   | Server Admin                          |     To make proxy user possible                           |
| References              | Databases,Tables                      |     To have references on tables                          |
| Reload                  | Server Admin                          |     To reload or refresh tables, logs and privileges      |
| Replication client      | Server Admin                          |     To ask where the slave or master servers are          |
| Replication slave       | Server Admin                          |     To read binary log events from the master             |
| Select                  | Tables                                |     To retrieve rows from table                           |
| Show databases          | Server Admin                          |     To see all databases with SHOW DATABASES              |
| Show view               | Tables                                |     To see views with SHOW CREATE VIEW                    |
| Shutdown                | Server Admin                          |     To shut down the server                               |
| Super                   | Server Admin                          |     To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger                 | Tables                                |     To use triggers                                       |
| Create tablespace       | Server Admin                          |     To create/alter/drop tablespaces                      |
| Update                  | Tables                                |     To update existing rows                               |
| Usage                   | Server Admin                          |     No privileges - allow connect only                    |
+-------------------------+---------------------------------------    +-------------------------------------------------------+
31 rows in set (0.00 sec)

权限含义如下：

  • ALL或ALL PRIVILEGES：包含除grant option之外表中其他所有权限

  • ALTER：使用ALTER TABLE语句更改表的结构（ALTER TABLE语句除了该权限之外还需要CREATE和INSERT权限。ALTER TABLE RENAME语句需要旧表上的ALTER和DROP权限，新表上的CREATE和INSERT权限）。

  • ALTER ROUTINE：修改或删除存储过程或存储函数。

  • CREATE：创建库和表。

  • CREATE ROUTINE：创建存储过程或函数。

  • CREATE TABLESPACE：创建、修改、删除表空间文件和日志组文件。

  • CREATE TEMPORARY TABLES：创建临时表，创建后会话对该临时表有所有权限

  • CREATE USER：可使用ALTER USER、CREATE USER、DROP USER、RENAME USER、REVOKE ALL PRIVILEGES语句。

  • CREATE VIEW：创建视图

  • DELETE：从表中删除数据。

  • DROP：删除现有库、或表、或视图等对象。注意在分区表上使用ALTER TABLE ... DROP PARTITION语句必须要有表的DROP权限，执行TRUNCATE TABLE也需要DROP权限。

  • EVENT：创建、更改、删除或查看Event Scheduler事件。

  • EXECUTE：执行存储过程或函数。

  • FILE：执行LOAD DATA INFILE和SELECT ... INTO OUTFILE语句以及LOAD_FILE()来读取和写入Server主机上的文件。具有FILE权限的用户可读取datadir目录中的所有文件，还能在MySQL Server有写入权限的任何目录下创建新文件。5.7版本中，可以使用secure_file_priv系统变量限制FILE权限的读写目录。

  • GRANT OPTION：授予或回收其他用户或自己拥有的权限。

  • INDEX：创建或删除索引。INDEX权限适用于在已存在的表上使用CREATE INDEX语句，如果用户具有CREATE权限，则可以在CREATE TABLE语句中包含索引定义语句。

  • INSERT：向表中插入数据。ANALYZE TABLE、OPTIMIZE TABLE和REPAIR TABLE表维护语句也需要INSERT权限。

  • LOCK TABLES：对表显式加锁，持有表锁的用户对该表有读写权限，未持有表锁的用户对表的读写访问会被阻塞。

  • PROCESS：显示有关在Server内执行的线程信息。拥有该权限的用户在使用SHOW PROCESSLIST语句或mysqladmin processlist命令时可以查看到属于其他帐号的线程信息。另外，使用SHOW ENGINE语句以及查看information_schema 数据字典库中的相当一部分表也需要该权限。

  • PROXY：该权限使用户能够代理另一个用户。

  • REFERENCES：在创建外键约束时，需要用户具有父表的REFERENCES权限。

  • RELOAD：允许用户使用FLUSH语句及与FLUSH操作等效的mysqladmin子命令：flush-hosts，flush-logs，flush-privileges，flush-status，flush-tables，flush-threads，refresh和reload

  • REPLICATION CLIENT：可使用SHOW MASTER STATUS、SHOW SLAVE STATUS和SHOW BINARY LOGS语句。

  • REPLICATION SLAVE：从库服务器可连接到主库服务器并请求主库binlog 日志。

  • SELECT：该权限用于从数据库表中查询数据行记录。另外，UPDATE或DELETE语句使用where子句指定条件时也需要该列的SELECT权限。否则你会发现可以update更新全表，却不能使用where语句指定更新某些行。对基表或视图使用EXPLAIN语句也需要用户对表或视图具有该权限。

  • SHOW DATABASES：用于执行SHOW DATABASE语句，若没有此权限则只能看到具有对应访问权限的数据库列表

  • SHOW VIEW：用于执行SHOW CREATE VIEW语句。对视图使用EXPLAIN语句也需要此权限。

  • SHUTDOWN：用于执行SHUTDOWN语句、mysqladmin shutdown命令和mysql_shutdown() C API函数。

  • SUPER：用于以下操作和Server行为： 
    * 修改全局系统变量及某些会话级别系统变量
    * 对全局事务特征的更改（start transaction语句）。
    * 从库服务器用于执行启动和停止复制的语句，包括组复制，以及执行CHANGE MASTER TO和CHANGE REPLICATION FILTER语句 。
    * 执行PURGE BINARY LOGS和BINLOG语句 。
    * 如果视图或存储程序定义了DEFINER属性，则拥有SUPER权限的用户就算不是该视图或存储程序的创建者，仍然可以执行该视图或存储程序 。
    * 执行CREATE SERVER、ALTER SERVER和DROP SERVER语句 。
    * 执行mysqladmin debug命令 。
    * 用于InnoDB key自旋 。
    * 用于执行通过DES_ENCRYPT()函数启用读取DES密钥文件 。
    * 用于执行用户自定义函数时启用版本令牌 。
    * 超过最大连接数之后具有SUPER的帐户还可以执行的操作：
      1) 使用KILL语句或mysqladmin kill命令来终止属于其他帐户的线程。
      2) 即使Server总连接数达到max_connections系统变量定义的值，也会接受具有SUPER权限的用户一个额外的连接
      3) 即使Server启用了read_only系统变量，具有SUPER权限的用户仍然可以执行数据更新，还有GRANT和REVOKE语句
      4) SUPER客户端连接Server时，Server不执行init_connect系统变量指定的内容 。
      5) 处于脱机模式（已启用offline_mode系统变量）的Server不会中断具有SUPER权限用户的连接，且仍然接收具有SUPER权限用户的新连接请求 。

  • TRIGGER：创建、删除、执行或查看触发器。

  • UPDATE：更新表数据

  • USAGE：除能连接到数据库外基本无任何权限

 

参考

https://dev.mysql.com/doc/refman/5.7/en/grant-tables.html

https://dev.mysql.com/doc/refman/5.7/en/privilege-system.html

https://dev.mysql.com/doc/refman/5.7/en/user-account-management.html

https://mp.weixin.qq.com/s/BWfiTmAYy7z9Ok6dbjkd6w

https://mp.weixin.qq.com/s/Bw6V0X-jZBPsic1xwJX39w

Logo
开放原子开发者工作坊

开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！

更多推荐

第二届开放原子大赛首批创新成果集结武汉，诚邀广大开发者共鉴开源技术盛宴

第二届开放原子大赛首批创新成果集结武汉，诚邀广大开发者共鉴开源技术盛宴

avatar 开放原子开发者工作坊

诚邀报名 | 开源基础设施能力建设分论坛：打造开源生态的“心脏”

诚邀报名 | 开源基础设施能力建设分论坛：打造开源生态的“心脏”

avatar 开放原子开发者工作坊

诚邀报名 | 编程语言分论坛：AI时代的技术革新与开源实践

诚邀报名 | 编程语言分论坛：AI时代的技术革新与开源实践

avatar 开放原子开发者工作坊