摘要：本文主要簡述下部分相關的權限改動，不會涉及代碼實現部分。當前版本為由于實現了新的數據詞典表，所有的權限相關的信息都存儲在里。

MySQL8.0里引入了不少關于權限的改動，從這些改動可以看出來，權限管理更加的規(guī)范和遍歷了，這和我們之前為rds mysql增加了大量權限管理很類似，想來Oracle也是通過這些改動為其云業(yè)務服務的吧。

本文主要簡述下部分相關的權限改動，不會涉及代碼實現部分。當前版本為8.0.16

由于實現了新的數據詞典表，所有的權限相關的信息都存儲在innodb mysql tablespace里。而innodb是事務性引擎，具有ACID特性，所以對應的ACL操作也具有原子特性。

例如之前如果一個語句對多個user操作的時候，有些成功，有些會失敗。而現在則是要么全部成功，要么全部失敗。binlog也會在事務提交時記錄到redo log里。

這里有個問題是當我們通過搭建備庫的方式從5.7升級到8.0時，那些在5.7部分成功的acl操作，到了以8.0作為備庫的實例上會全部失敗.

關于atomic ddl 見官方文檔

Role是一個期待已久的功能，可以認為是一組權限的集合， 你可以為多個賬戶賦予相同的role權限，這也使得權限的管理更加規(guī)范，大大方便了運維和管理。你可以通過 create role "role_name" 創(chuàng)建一個role名，然后再通過grant語句為role賦予權限。之后就可以grant "role_name" to 一個指定的賬戶了。

關于role，之前寫了一篇文章介紹了，這里不再贅述，感興趣的點鏈接

參考：官方文檔

引入了一個新的插件，代碼在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毫秒

官方文檔

這也是個有趣的特性，意思是支持一個賬戶兩個密碼，這通常發(fā)生在你修改了密碼，但又不想導致正在運行的業(yè)務中斷時。如worklog所述，當你有大規(guī)模的復制集群時，又想修改復制密碼，當然不希望正在進行的復制中斷拉。那怎么辦，可以在保持兩個密碼在一段時間內都是有效的。用法也比較簡單，我們舉個簡單的例子：

root@test 10:07:00>CREATE USER arthurdent@localhost IDENTIFIED WITH "mysql_native_password" BY "abcd";
Query OK, 0 rows affected (0.00 sec)

# 再創(chuàng)建一個密碼，同時保持當前密碼
root@test 10:07:02>ALTER USER arthurdent@localhost IDENTIFIED BY "efgh" RETAIN CURRENT PASSWORD;
Query OK, 0 rows affected (0.01 sec)

#再創(chuàng)建一個密碼，同時保持當前密碼，但是第一個創(chuàng)建的密碼abcd就失效了
root@test 10:07:18>ALTER USER arthurdent@localhost IDENTIFIED BY "efghh" RETAIN CURRENT PASSWORD;
Query OK, 0 rows affected (0.01 sec)

如果要拋棄舊密碼，可以執(zhí)行如下語句
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

在之前如果你有create user權限，相應的也有了drop/create/modify任何賬戶的權限，包括root賬戶。 如果用戶有delete/update權限的話，甚至還可以修改grant系統(tǒng)表, 因為有的時候我們需要把部分權限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庫，這實際上對于云業(yè)務而言是非常重要的功能：用戶通常希望擁有超級權限，但云平臺本身也有保留的賬號做維護用，這些我們是不希望被修改的，舉個簡單的例子：

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的賬戶再去創(chuàng)建別的賬戶時，他受限的列表也會傳遞出去

在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

可以設置密碼過期時間，提供了三種操作：

通過參數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

現在很多系統(tǒng)在忘記密碼重設時，都會要求最近幾次使用付的密碼不允許再次使用，這也是為了安全考慮，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

MySQL提供了在線持久化參數修改的功能，通過接口SET PERSIST 和SET PERSIST ONLY來實現，但有些涉及敏感信息的變量則不應該被persist, 因此不應該通過遠程終端來管理，而是要管理員登錄機器，手動的修改my.cnf

新增參數persist_only_admin_x509_subject , 當打開這個參數時，只有通過SSL認證的用戶才能Persist一些受限的系統(tǒng)參數。官方文檔列舉了些可持久化的參數和不可持久化的參數

參考：
參數:persist_only_admin_x509_subject
Nonpersistible and Persist-Restricted System Variables

用過的人的都知道，當以skip-grant-tables啟動時候，系統(tǒng)將不檢查任何權限，這是是很危險的，但有時候如果application和數據庫實例部署在同一臺機器時，我們又可以通過該選項來獲得更好的性能，但帶來的風險是其他人只要知道host和端口號，也可以遠程連接過來，這就有數據安全問題

因此MySQL加入了新選項skip_networking，不再監(jiān)聽tcp/ip連接請求。

另外最近也修復了一個有趣的bug#94394，當mysql.user表損壞時，實例啟動時僅僅打印了一條錯誤信息，并以skip-grant-tables的方式啟動了。這實際上市不安全的，人們可能在install初始化階段不小心忽略這個錯誤，而后數據庫的正常運行，也會造成實例正確安裝的錯覺。

因此在8.0.16版本中，官方修復了這個問題，除非用戶指定skip-grant-tables，實例將打印信息之后直接啟動失敗。

這個修復很簡單，就是說對父表沒權限的用戶，如果在子表上因為foreign key約束，導致錯誤的話，不應該將父表的信息暴露出來，這可能導致安全問題，而是返回統(tǒng)一的錯誤：

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級別的變量，但某些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

閱讀原文

本文為云棲社區(qū)原創(chuàng)內容，未經允許不得轉載。