数据库内核月报－ 2019/06 - MySQL · 引擎特性 · 安全及权限改进相关 - 《数据库内核月报》

Atomic ACL Statement
Role
connection control plugin
支持双重密码
Partial Revoker
Password Expiration
密码复用
修改账户要求验证
限制SET PERSIST
skip-grant-tables
fk error不显示父表信息
SESSION_VARIABLES_ADMIN

MySQL8.0里引入了不少关于权限的改动，从这些改动可以看出来，权限管理更加的规范和遍历了，这和我们之前为rds mysql增加了大量权限管理很类似，想来Oracle也是通过这些改动为其云业务服务的吧。

本文主要简述下部分相关的权限改动，不会涉及代码实现部分。当前版本为8.0.16

Atomic ACL Statement

由于实现了新的数据词典表，所有的权限相关的信息都存储在innodb mysql tablespace里。而innodb是事务性引擎，具有ACID特性，所以对应的ACL操作也具有原子特性。

例如之前如果一个语句对多个user操作的时候，有些成功，有些会失败。而现在则是要么全部成功，要么全部失败。binlog也会在事务提交时记录到redo log里。

这里有个问题是当我们通过搭建备库的方式从5.7升级到8.0时，那些在5.7部分成功的acl操作，到了以8.0作为备库的实例上会全部失败.

关于atomic ddl 见官方文档

Role

Role是一个期待已久的功能，可以认为是一组权限的集合，你可以为多个账户赋予相同的role权限，这也使得权限的管理更加规范，大大方便了运维和管理。你可以通过 create role ‘role_name’ 创建一个role名，然后再通过grant语句为role赋予权限。之后就可以grant ‘role_name’ to 一个指定的账户了。

关于role，之前写了一篇文章介绍了，这里不再赘述，感兴趣的点链接

参考：官方文档

connection control plugin

引入了一个新的插件，代码在plugin/connection_control/下，该插件使用的是audit plugin接口，其功能是在数次登陆失败后，会延迟下次登陆的时间，这也有点类似于多次密码输入错误，会被冻结一会的意思。

在lib/plugin目录下，我们已经编译好了插件connection_control.so，安装也比较简单：

mysql> INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
Query OK, 0 rows affected (0.01 sec)
mysql> INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
Query OK, 0 rows affected (0.03 sec)
  mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'connection%'\G
  *************************** 1. row ***************************
  PLUGIN_NAME: CONNECTION_CONTROL
  PLUGIN_STATUS: ACTIVE
  *************************** 2. row ***************************
  PLUGIN_NAME: CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS
  PLUGIN_STATUS: ACTIVE
2 rows in set (0.00 sec)
  mysql> SHOW VARIABLES LIKE '%connection%control%';
  +-------------------------------------------------+------------+
  | Variable_name                                   | Value      |
  +-------------------------------------------------+------------+
  | connection_control_failed_connections_threshold | 3          |
  | connection_control_max_connection_delay         | 2147483647 |
  | connection_control_min_connection_delay         | 1000       |
  +-------------------------------------------------+------------+
  3 rows in set (0.00 sec)

如何使用： connection_control_failed_connections_threshold: 允许失败的次数，在这么多次失败后，会去增加delay的时间(设置为0则表示关闭该特性，不会去增加延迟) 当超出失败上限后，就根据之后失败的测试乘以connection_control_min_connection_delay作为delay时间，但最大不超过connection_control_max_connection_delay, 以默认配置为例子，当第四次失败时是1000毫秒，当第五次失败时就加倍到2000毫秒

官方文档

支持双重密码

这也是个有趣的特性，意思是支持一个账户两个密码，这通常发生在你修改了密码，但又不想导致正在运行的业务中断时。如worklog所述，当你有大规模的复制集群时，又想修改复制密码，当然不希望正在进行的复制中断拉。那怎么办，可以在保持两个密码在一段时间内都是有效的。用法也比较简单，我们举个简单的例子：

root@test 10:07:00>CREATE USER arthurdent@localhost IDENTIFIED WITH 'mysql_native_password' BY 'abcd';
Query OK, 0 rows affected (0.00 sec)
# 再创建一个密码，同时保持当前密码
root@test 10:07:02>ALTER USER arthurdent@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD;
Query OK, 0 rows affected (0.01 sec)
#再创建一个密码，同时保持当前密码，但是第一个创建的密码abcd就失效了
root@test 10:07:18>ALTER USER arthurdent@localhost IDENTIFIED BY 'efghh' RETAIN CURRENT PASSWORD;
Query OK, 0 rows affected (0.01 sec)
如果要抛弃旧密码，可以执行如下语句
root@test 10:11:36>ALTER USER arthurdent@localhost DISCARD OLD PASSWORD;
Query OK, 0 rows affected (0.00 sec)
此时你再通过旧密码efgh就无法成功登录了。

mysql.user表被扩展了来存储两个密码，主密码存储在mysql.user.authentication_string中，次要密码存储在mysql.user.user_attributes中

root@test 10:31:36>select user, authentication_string, user_attributes from mysql.user where user = 'arthurdent'\G
*************************** 1. row ***************************
user: arthurdent
authentication_string: *7538919BBFC125D3F772537519E66F8242CD2E6B
user_attributes: {"additional_password": "*1ACFAF7821CBE8E2D6B7C3FA1A539F53CB41BB9D"}
1 row in set (0.00 sec)

除了ALTER USER外，SET PASSWORD也支持类似的语法：

SET PASSWORD [FOR user] = 'auth_string'
[REPLACE 'current_auth_string']
[RETAIN CURRENT PASSWORD]

参考文档： WL#11540: Support 2 active passwords per user account

Partial Revoker

在之前如果你有create user权限，相应的也有了drop/create/modify任何账户的权限，包括root账户。如果用户有delete/update权限的话，甚至还可以修改grant系统表, 因为有的时候我们需要把部分权限revoke掉

worklog举了个例子，这里直接列出来啦：

mysql@root> CREATE USER foo;
mysql@root> GRANT CREATE USER,UPDATE,DELETE ON *.* TO foo WITH GRANT OPTION;
mysql@root> GRANT SELECT ON mysql.* TO foo with grant option;
Now, foo has the ability to do the following:
mysql@foo>CREATE USER bar;
mysql@foo>ALTER USER root@localhost IDENTIFIED BY 'gibberish';
mysql@foo>DROP USER root@localhost;
mysql@foo>DELETE FROM mysql.user WHERE user = 'root';
mysql@foo>UPDATE mysql.user SET authentication_string = 'gibberish'
WHERE user='root';

如上例，当foo用户有了由root账户赋予的grant权限，他甚至可以去操作root账户。这个worklog的目的，就确保foo用户无法对root账户进行操作。

这个worklog把权限定义为三类：

- Global Privileges: DDL/DML privileges that allow object manipulation on all
databases. This includes administrative privileges,
  dynamic privileges.
  - Database Privileges: Restricted to a one (or more) databases.
  They provide ability to manipulate objects and
  data within database.
  - Restrictions_list: List of tuples - (user, database, privileges).
  Each entry in the list represents operations prohibited on
  a given database for given user. Restrictions list implies
  that even if user is granted GLOBAL privileges, if
  revocation list prevents the operation, user can not perform
  it for given database.

其中restrictions_list存储在mysql.user表中，主要是引入Partial revoke, 可以revoke部分库上的权限，例如mysql库，这实际上对于云业务而言是非常重要的功能：用户通常希望拥有超级权限，但云平台本身也有保留的账号做维护用，这些我们是不希望被修改的，举个简单的例子：

root@(none) 09:26:43>CREATE USER foo;
Query OK, 0 rows affected (0.00 sec)
root@(none) 09:26:49>GRANT ALL ON *.* TO foo;
Query OK, 0 rows affected (0.00 sec)
root@(none) 09:27:00>SET GLOBAL partial_revokes = 0;
Query OK, 0 rows affected (0.00 sec)
root@(none) 09:27:05>REVOKE INSERT ON mysql.* FROM foo;
ERROR 1141 (42000): There is no such grant defined for user 'foo' on host '%'
root@(none) 09:27:12>SET GLOBAL partial_revokes = 1;
Query OK, 0 rows affected (0.00 sec)
root@(none) 09:27:14>REVOKE INSERT ON mysql.* FROM foo;
Query OK, 0 rows affected (0.00 sec)
root@(none) 09:27:24>REVOKE DELETE ON mysql.* FROM foo;
Query OK, 0 rows affected (0.00 sec)

这里引入了一个全局参数partial_revokes, 只有打开了，你才能对账户做partial revoke操作，这里会产生一个对该账户的限制列表，存储在mysql库中:

root@(none) 09:29:08>select user, authentication_string, user_attributes from mysql.user where user = 'foo'\G
*************************** 1. row ***************************
user: foo
authentication_string:
user_attributes: {"Restrictions": [{"Database": "mysql", "Privileges": ["INSERT", "DELETE"]}]}
1 row in set (0.00 sec)

可以看到针对该账户产生了一个限制列表Restrictions, 以json的形式存储。Partial Revoke的限制（摘自文档）:

Partial revokes must name the schema literally. Schema names that contain the % or _ SQL wildcard characters (for example, myschema%) are not permitted.
It is possible to use partial revokes to place restrictions on nonexistent schemas, but only if the revoked privilege is granted globally. If a privilege is not granted globally, revoking it for a nonexistent schema produces an error.
Partial revokes apply at the schema level only. You cannot use partial revokes for privileges that apply only globally (such as FILE or BINLOG_ADMIN), or for table, column, or routine privileges.

当一个有restrictions list的账户再去创建别的账户时，他受限的列表也会传递出去

在wl#12098中还引入了system user这样的权限类型，只有相同权限的账户才能修改这种类型的账户，普通账户无权对其进行修改。在之后又在wl#12364中，避免拥有CONNECTION_ADMIN权限的普通用户能够去kill超级用户的session或者query:

root@(none) 08:20:40>GRANT SYSTEM_USER ON *.* TO foo;
Query OK, 0 rows affected (0.00 sec)
root@(none) 08:20:54>GRANT SYSTEM_USER ON *.* TO bar;
Query OK, 0 rows affected (0.01 sec)
baz@(none) 08:27:38>GRANT CONNECTION_ADMIN ON *.* to baz;
Query OK, 0 rows affected (0.00 sec)
#login foo
  foo@(none) 08:27:10>show grants;
  +---------------------------------------+
  | Grants for foo@%                      |
  +---------------------------------------+
  | GRANT USAGE ON *.* TO `foo`@`%`       |
  | GRANT SYSTEM_USER ON *.* TO `foo`@`%` |
  +---------------------------------------+
2 rows in set (0.00 sec)
  foo@(none) 08:28:04>show processlist;
  +-----+------+-----------+------+---------+------+----------+------------------+
  | Id  | User | Host      | db   | Command | Time | State    | Info             |
  +-----+------+-----------+------+---------+------+----------+------------------+
  | 348 | foo  | localhost | NULL | Query   |    0 | starting | show processlist |
  +-----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)
#login baz
  baz@(none) 08:29:03>show grants;
  +--------------------------------------------+
  | Grants for baz@%                           |
  +--------------------------------------------+
  | GRANT USAGE ON *.* TO `baz`@`%`            |
  | GRANT CONNECTION_ADMIN ON *.* TO `baz`@`%` |
  +--------------------------------------------+
2 rows in set (0.00 sec)
  baz@(none) 08:29:05>show processlist;
  +-----+------+-----------+------+---------+------+----------+------------------+
  | Id  | User | Host      | db   | Command | Time | State    | Info             |
  +-----+------+-----------+------+---------+------+----------+------------------+
  | 349 | baz  | localhost | NULL | Query   |    0 | starting | show processlist |
  +-----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)
#baz账户只能看到自己的线程，如果强制去kill foo呢 ？
  baz@(none) 08:30:30>kill 348;
  ERROR 1095 (HY000): You are not owner of thread 348

可以看到有connection_admin权限的账户被限制了，不仅无法看到system_user的链接，也无法去kill session.

简单来说，有system_user权限的账户可以修改system user和regular user的账户；而regular user则无法修改system user的账户

关于这块官方文档有非常详细的内容，笔者对这块也不太熟悉，就不多说了，感兴趣的直接翻阅如下文档吧： WL#12098: MySQL system users WL#12364: Kill administration for system users WL#12820: Extend GRANT syntax to cover partial revokes information Privilege Restriction Using Partial Revokes Account Categories 官方博客:The SYSTEM_USER Dynamic Privilege 官方博客：Partial Revokes from Database Objects 官方博客：How to create multiple accounts for an app?

Password Expiration

可以设置密码过期时间，提供了三种操作：

通过参数default_password_lifetime来控制 , 单位为天

root@(none) 09:21:31>SET PERSIST default_password_lifetime = 180;
Query OK, 0 rows affected (0.00 sec)

该选项的值会被alter user覆盖

通过ALTER USER来控制

指定过期时间

CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
过期时间存储在mysql.user表中
root@(none) 09:35:46>select user,password_lifetime from mysql.user where user = 'jeffrey'\G
*************************** 1. row ***************************
user: jeffrey
password_lifetime: 90
1 row in set (0.00 sec)

禁止密码过期

CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;

默认过期时间为default_password_lifetime:

CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;

直接手动过期

ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE;

参考：官方文档

WL#6587 : Protocol support for password expiration

密码复用

现在很多系统在忘记密码重设时，都会要求最近几次使用付的密码不允许再次使用，这也是为了安全考虑，MySQL也增加了这样的功能，和密码过期类似，也可以通过全局变量，ALTER USER来控制:

例如如下配置：

  password_history=6
  password_reuse_interval=365

表示不要服用最近6次用到的密码或者365天内用过的密码。

也可以通过create/alter user来设置:

  CREATE USER 'jeffrey'@'localhost' PASSWORD HISTORY 5;
  ALTER USER 'jeffrey'@'localhost' PASSWORD HISTORY 5;

  CREATE USER 'jeffrey'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
  ALTER USER 'jeffrey'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;

同样的也可以把上例中的history 5 和 interval 365 day指定为default

参考：官方文档

WL#6595: Password rotation policy

修改账户要求验证

同样是安全相关的，当修改一个账户时，需要去验证密码，可以使用参数password_require_current来控制。默认关闭，当打开该选项时，如果要修改账户密码，必须要提供当前的密码才允许修改，如下摘录的官方示例：

要求在修改时输入当前密码：

CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT;
ALTER USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT;

可选的输入当前密码（感觉有点多余…）

CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT OPTIONAL;
ALTER USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT OPTIONAL;

根据参数配置来决定：

CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT;
ALTER USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT;

那么修改密码时就需要显示当前密码：

CREATE USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT;
ALTER USER 'jeffrey'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT;

SET PASSWORD也一样.

SET PASSWORD [FOR user] = password_option
password_option : {
  'auth_string' [REPLACE 'auth_string']
}

参考：

官方文档

WL#11544 Current password required for SET PASSWORD

限制SET PERSIST

MySQL提供了在线持久化参数修改的功能，通过接口SET PERSIST 和SET PERSIST ONLY来实现，但有些涉及敏感信息的变量则不应该被persist, 因此不应该通过远程终端来管理，而是要管理员登录机器，手动的修改my.cnf

新增参数persist_only_admin_x509_subject , 当打开这个参数时，只有通过SSL认证的用户才能Persist一些受限的系统参数。官方文档列举了些可持久化的参数和不可持久化的参数

参考：参数:persist_only_admin_x509_subject

Nonpersistible and Persist-Restricted System Variables

skip-grant-tables

用过的人的都知道，当以skip-grant-tables启动时候，系统将不检查任何权限，这是是很危险的，但有时候如果application和数据库实例部署在同一台机器时，我们又可以通过该选项来获得更好的性能，但带来的风险是其他人只要知道host和端口号，也可以远程连接过来，这就有数据安全问题

因此MySQL加入了新选项skip_networking，不再监听tcp/ip连接请求。

另外最近也修复了一个有趣的bug#94394，当mysql.user表损坏时，实例启动时仅仅打印了一条错误信息，并以skip-grant-tables的方式启动了。这实际上市不安全的，人们可能在install初始化阶段不小心忽略这个错误，而后数据库的正常运行，也会造成实例正确安装的错觉。

因此在8.0.16版本中，官方修复了这个问题，除非用户指定skip-grant-tables，实例将打印信息之后直接启动失败。

fk error不显示父表信息

这个修复很简单，就是说对父表没权限的用户，如果在子表上因为foreign key约束，导致错误的话，不应该将父表的信息暴露出来，这可能导致安全问题，而是返回统一的错误：

ERROR 23000: Cannot add or update a child row: a foreign key constraint fails

参考： WL#8910: Ensure foreign key error does not reveal information about parent table for which user has no access privileges.

SESSION_VARIABLES_ADMIN

通常任何账户都允许设置session级别的变量，但某些session级别的变量只能特定权限的用户设置，例如binlog_format, sql_log_bin，火鹤sql_log_off等，需要需要SYSTEM_VARIABLES_ADMIN或者SUPER权限来设置。

从MySQL8.0.14开始了增加了一个新的权限位session_variables_admin, wl#12217列出了一些需要该权限位的变量:

The following vairables need to enforce SESSION_VARIABLES_ADMIN:

auto_increment_increment
auto_increment_offset
binlog_direct_non_transactional_updates
bulk_insert_buffer_size
character_set_database
character-set-filesystem
collation_database
pseudo_slave_mode
pseudo_thread_id
transaction_write_set_extraction
rbr_exec_mode

The following variables will not be protected:

default_storage_engine
default_tmp_storage_engine
max_allowed_packet
rand_seed1
rand_seed2

These variables should transition from checking SYSTEM_VARIABLES_ADMIN to SESSION_VARIABLES_ADMIN:

histogram_generation_max_mem_size
sql_log_off
debug_sync
original_commit_timestamp
The not documented gtid_next
The disabled and not documented gtid_next_list
default_collation_for_utf8mb4
explicit_defaults_for_timestamp
sql_log_bin
explicit_defaults_for_timestamp The variable is mis-documented as not requiring SYSTEM_VARIABLES_ADMIN for SET SESSION. But in reality it does require it. Since the variable is deprecated we'll keep the current behavior.
binlog_format
binlog_row_image
binlog_row_value_options
binlog_rows_query_log_events

官方文档：SESSION_VARIABLES_ADMIN

WL#12217: SESSION_VARIABLE_ADMIN