MySQL8.0里引入了不少關(guān)于權(quán)限的改動(dòng)，從這些改動(dòng)可以看出來，權(quán)限管理更加的規(guī)范和遍歷了，這和我們之前為rds mysql增加了大量權(quán)限管理很類似，想來Oracle也是通過這些改動(dòng)為其云業(yè)務(wù)服務(wù)的吧。 達(dá)到當(dāng)天最大量API KEY 超過次數(shù)限制

本文主要簡(jiǎn)述下部分相關(guān)的權(quán)限改動(dòng)，不會(huì)涉及代碼實(shí)現(xiàn)部分。當(dāng)前版本為8.0.16

Atomic ACL Statement

由于實(shí)現(xiàn)了新的數(shù)據(jù)詞典表，所有的權(quán)限相關(guān)的信息都存儲(chǔ)在innodb mysql tablespace里。而innodb是事務(wù)性引擎，具有ACID特性，所以對(duì)應(yīng)的ACL操作也具有原子特性。

例如之前如果一個(gè)語(yǔ)句對(duì)多個(gè)user操作的時(shí)候，有些成功，有些會(huì)失敗。而現(xiàn)在則是要么全部成功，要么全部失敗。binlog也會(huì)在事務(wù)提交時(shí)記錄到redo log里。

這里有個(gè)問題是當(dāng)我們通過搭建備庫(kù)的方式從5.7升級(jí)到8.0時(shí)，那些在5.7部分成功的acl操作，到了以8.0作為備庫(kù)的實(shí)例上會(huì)全部失敗.

關(guān)于atomic ddl 見官方文檔

Role

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

關(guān)于role，之前寫了一篇文章介紹了，這里不再贅述，感興趣的點(diǎn)鏈接

參考：

官方文檔

connection control plugin

引入了一個(gè)新的插件，代碼在plugin/connection_control/下，該插件使用的是audit plugin接口，其功能是在數(shù)次登陸失敗后，會(huì)延遲下次登陸的時(shí)間，這也有點(diǎn)類似于多次密碼輸入錯(cuò)誤，會(huì)被凍結(jié)一會(huì)的意思。

在lib/plugin目錄下，我們已經(jīng)編譯好了插件connec，安裝也比較簡(jiǎn)單：

mysql> INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connec'; Query OK, 0 rows affected sec) mysql> INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connec'; Query OK, 0 rows affected 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 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 sec)

如何使用：

connection_control_failed_connections_threshold: 允許失敗的次數(shù)，在這么多次失敗后，會(huì)去增加delay的時(shí)間(設(shè)置為0則表示關(guān)閉該特性，不會(huì)去增加延遲)

當(dāng)超出失敗上限后，就根據(jù)之后失敗的測(cè)試乘以connection_control_min_connection_delay作為delay時(shí)間，但最大不超過connection_control_max_connection_delay, 以默認(rèn)配置為例子，當(dāng)?shù)谒拇问r(shí)是1000毫秒，當(dāng)?shù)谖宕问r(shí)就加倍到2000毫秒

官方文檔

支持雙重密碼

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

root@test 10:07:00>CREATE USER arthurdent@localhost IDENTIFIED WITH 'mysql_native_password' BY 'abcd'; Query OK, 0 rows affected sec) # 再創(chuàng)建一個(gè)密碼，同時(shí)保持當(dāng)前密碼 root@test 10:07:02>ALTER USER arthurdent@localhost IDENTIFIED BY 'efgh' RETAIN CURRENT PASSWORD; Query OK, 0 rows affected sec) #再創(chuàng)建一個(gè)密碼，同時(shí)保持當(dāng)前密碼，但是第一個(gè)創(chuàng)建的密碼abcd就失效了 root@test 10:07:18>ALTER USER arthurdent@localhost IDENTIFIED BY 'efghh' RETAIN CURRENT PASSWORD; Query OK, 0 rows affected sec) 如果要拋棄舊密碼，可以執(zhí)行如下語(yǔ)句 root@test 10:11:36>ALTER USER arthurdent@localhost DISCARD OLD PASSWORD; Query OK, 0 rows affected sec) 此時(shí)你再通過舊密碼efgh就無(wú)法成功登錄了。

my表被擴(kuò)展了來存儲(chǔ)兩個(gè)密碼，主密碼存儲(chǔ)在my.authentication_string中，次要密碼存儲(chǔ)在my.user_attributes中

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

除了ALTER USER外，SET PASSWORD也支持類似的語(yǔ)法：

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權(quán)限，相應(yīng)的也有了drop/create/modify任何賬戶的權(quán)限，包括root賬戶。 如果用戶有delete/update權(quán)限的話，甚至還可以修改grant系統(tǒng)表, 因?yàn)橛械臅r(shí)候我們需要把部分權(quán)限r(nóng)evoke掉

worklog舉了個(gè)例子，這里直接列出來啦：

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 my WHERE user = 'root'; mysql@foo>UPDATE my SET authentication_string = 'gibberish' WHERE user='root';

如上例，當(dāng)foo用戶有了由root賬戶賦予的grant權(quán)限，他甚至可以去操作root賬戶。這個(gè)worklog的目的，就確保foo用戶無(wú)法對(duì)root賬戶進(jìn)行操作。

這個(gè)worklog把權(quán)限定義為三類：

- 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存儲(chǔ)在my表中，主要是引入Partial revoke, 可以revoke部分庫(kù)上的權(quán)限，例如mysql庫(kù)，這實(shí)際上對(duì)于云業(yè)務(wù)而言是非常重要的功能：用戶通常希望擁有超級(jí)權(quán)限，但云平臺(tái)本身也有保留的賬號(hào)做維護(hù)用，這些我們是不希望被修改的，舉個(gè)簡(jiǎn)單的例子：

root@(none) 09:26:43>CREATE USER foo; Query OK, 0 rows affected sec) root@(none) 09:26:49>GRANT ALL ON *.* TO foo; Query OK, 0 rows affected sec) root@(none) 09:27:00>SET GLOBAL partial_revokes = 0; Query OK, 0 rows affected 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 sec) root@(none) 09:27:14>REVOKE INSERT ON mysql.* FROM foo; Query OK, 0 rows affected sec) root@(none) 09:27:24>REVOKE DELETE ON mysql.* FROM foo; Query OK, 0 rows affected sec)

這里引入了一個(gè)全局參數(shù)partial_revokes, 只有打開了，你才能對(duì)賬戶做partial revoke操作，這里會(huì)產(chǎn)生一個(gè)對(duì)該賬戶的限制列表，存儲(chǔ)在mysql庫(kù)中:

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

可以看到針對(duì)該賬戶產(chǎn)生了一個(gè)限制列表Restrictions, 以json的形式存儲(chǔ)。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.

當(dāng)一個(gè)有restrictions list的賬戶再去創(chuàng)建別的賬戶時(shí)，他受限的列表也會(huì)傳遞出去

在wl#12098中還引入了system user這樣的權(quán)限類型，只有相同權(quán)限的賬戶才能修改這種類型的賬戶，普通賬戶無(wú)權(quán)對(duì)其進(jìn)行修改。在之后又在wl#12364中，避免擁有CONNECTION_ADMIN權(quán)限的普通用戶能夠去kill超級(jí)用戶的session或者query:

root@(none) 08:20:40>GRANT SYSTEM_USER ON *.* TO foo; Query OK, 0 rows affected sec) root@(none) 08:20:54>GRANT SYSTEM_USER ON *.* TO bar; Query OK, 0 rows affected sec) baz@(none) 08:27:38>GRANT CONNECTION_ADMIN ON *.* to baz; Query OK, 0 rows affected 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 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 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 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 sec) #baz賬戶只能看到自己的線程，如果強(qiáng)制去kill foo呢 ？ baz@(none) 08:30:30>kill 348; ERROR 1095 (HY000): You are not owner of thread 348

可以看到有connection_admin權(quán)限的賬戶被限制了，不僅無(wú)法看到system_user的鏈接，也無(wú)法去kill session.

簡(jiǎn)單來說，有system_user權(quán)限的賬戶可以修改system user和regular user的賬戶；而regular user則無(wú)法修改system user的賬戶

關(guān)于這塊官方文檔有非常詳細(xì)的內(nèi)容，筆者對(duì)這塊也不太熟悉，就不多說了，感興趣的直接翻閱如下文檔吧：

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

Password Expiration

可以設(shè)置密碼過期時(shí)間，提供了三種操作：

1. 通過參數(shù)default_password_lifetime來控制 , 單位為天

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

該選項(xiàng)的值會(huì)被alter user覆蓋

1. 通過ALTER USER來控制

指定過期時(shí)間

CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY; ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY; 過期時(shí)間存儲(chǔ)在my表中 root@(none) 09:35:46>select user,password_lifetime from my where user = 'jeffrey'G *************************** 1. row *************************** user: jeffrey password_lifetime: 90 1 row in set sec)

禁止密碼過期

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

默認(rèn)過期時(shí)間為default_password_lifetime:

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

1. 直接手動(dòng)過期

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

參考：

官方文檔

WL#6587 : Protocol support for password expiration

密碼復(fù)用

現(xiàn)在很多系統(tǒng)在忘記密碼重設(shè)時(shí)，都會(huì)要求最近幾次使用付的密碼不允許再次使用，這也是為了安全考慮，MySQL也增加了這樣的功能，和密碼過期類似，也可以通過全局變量，ALTER USER來控制:

例如如下配置：

password_history=6 password_reuse_interval=365

表示不要服用最近6次用到的密碼或者365天內(nèi)用過的密碼。

也可以通過create/alter user來設(shè)置:

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

修改賬戶要求驗(yàn)證

同樣是安全相關(guān)的，當(dāng)修改一個(gè)賬戶時(shí)，需要去驗(yàn)證密碼，可以使用參數(shù)password_require_current來控制。默認(rèn)關(guān)閉，當(dāng)打開該選項(xiàng)時(shí)，如果要修改賬戶密碼，必須要提供當(dāng)前的密碼才允許修改，如下摘錄的官方示例：

要求在修改時(shí)輸入當(dāng)前密碼：

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

可選的輸入當(dāng)前密碼（感覺有點(diǎn)多余...）

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

根據(jù)參數(shù)配置來決定：

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

那么修改密碼時(shí)就需要顯示當(dāng)前密碼：

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提供了在線持久化參數(shù)修改的功能，通過接口SET PERSIST 和SET PERSIST ONLY來實(shí)現(xiàn)，但有些涉及敏感信息的變量則不應(yīng)該被persist, 因此不應(yīng)該通過遠(yuǎn)程終端來管理，而是要管理員登錄機(jī)器，手動(dòng)的修改my.cnf

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

參考：

參數(shù):persist_only_admin_x509_subject

Nonpersistible and Persist-Restricted System Variables

skip-grant-tables

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

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

另外最近也修復(fù)了一個(gè)有趣的bug#94394，當(dāng)my表?yè)p壞時(shí)，實(shí)例啟動(dòng)時(shí)僅僅打印了一條錯(cuò)誤信息，并以skip-grant-tables的方式啟動(dòng)了。這實(shí)際上市不安全的，人們可能在install初始化階段不小心忽略這個(gè)錯(cuò)誤，而后數(shù)據(jù)庫(kù)的正常運(yùn)行，也會(huì)造成實(shí)例正確安裝的錯(cuò)覺。

因此在8.0.16版本中，官方修復(fù)了這個(gè)問題，除非用戶指定skip-grant-tables，實(shí)例將打印信息之后直接啟動(dòng)失敗。

fk error不顯示父表信息

這個(gè)修復(fù)很簡(jiǎn)單，就是說對(duì)父表沒權(quán)限的用戶，如果在子表上因?yàn)閒oreign key約束，導(dǎo)致錯(cuò)誤的話，不應(yīng)該將父表的信息暴露出來，這可能導(dǎo)致安全問題，而是返回統(tǒng)一的錯(cuò)誤：

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

通常任何賬戶都允許設(shè)置session級(jí)別的變量，但某些session級(jí)別的變量只能特定權(quán)限的用戶設(shè)置，例如binlog_format, sql_log_bin，火鶴sql_log_off等，需要需要SYSTEM_VARIABLES_ADMIN或者SUPER權(quán)限來設(shè)置。

從MySQL8.0.14開始了增加了一個(gè)新的權(quán)限位session_variables_admin, wl#12217列出了一些需要該權(quán)限位的變量:

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

作者：zhaiwx_yinfeng