新的更安全更快的的認證方式

密碼管理

5.7中無密碼管理

新增三個密碼管理相關配置項

password_histroy=3 (不能和最近三次使用過的密碼相同)

password_reuse_interval=90 (不能和最近90天使用過的密碼相同)

password_require_current=ON (開啟後修改密碼需要驗證舊密碼，root使用者不需要)

角色管理

MySQL角色是指定的權限集合.

像使用者帳戶一樣，角色可以擁有授予和撤消的權限:

可以授予使用者帳戶角色，授予該帳戶與每個角色相關的權限
使用者被授予角色權限，則該使用者擁有該角色的權限。

以下清單總結了MySQL提供的角色管理功能：

CREATE ROLE并 DROP ROLE角色建立和删除；
GRANT并 REVOKE為使用者和角色配置設定和撤銷權限；
SHOW GRANTS 顯示使用者和角色的權限和角色配置設定；
SET DEFAULT ROLE 指定哪些帳戶角色預設處于活動狀态；
SET ROLE 更改目前會話中的活動角色。
CURRENT_ROLE()功能顯示目前會話中的活動角色。

建立角色并授予使用者角色權限

考慮如下幾種場景：

應用程式使用名為app_db的資料庫。

與應用程式相關聯，可以為建立和維護應用程式的開發人員以及管理者賬戶。

開發人員需要完全通路資料庫。有的使用者隻需要讀取權限，有的使用者需要讀取/寫入權限。

為清楚區分角色的權限，将角色建立為所需權限集的名稱。通過授權适當的角色，可以輕松地為使用者帳戶授予所需的權限。

建立角色，CREATE ROLE

CREATE ROLE 'app_developer', 'app_read', 'app_write';

角色名稱與使用者帳戶名稱非常相似，由格式中的使用者部分和主機部分組成.

主機部分，如果省略，則預設為%。使用者和主機部分可以不加引号，除非它們包含特殊字元

與帳戶名稱不同，角色名稱的

使用者部分不能為空

為角色配置設定權限，使用與為使用者配置設定權限相同的文法執行：

GRANT ALL ON app_db.* TO 'app_developer';  
GRANT SELECT ON app_db.* TO 'app_read';  
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';CREATE ROLE 'app_developer', 'app_read', 'app_write';

現在假設最初需要一個開發人員帳戶，兩個需要隻讀通路權的使用者以及一個需要讀取/寫入權限的使用者.

使用CREATEUSER建立使用者：

CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass';  
CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass';  
CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'read_user2pass';  
CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';

要為每個使用者配置設定其所需的權限，可以使用GRANT與剛才顯示的形式相同的語句，但這需要列舉每個使用者的個人權限。相反，使用GRANT允許授權角色而非權限的替代文法：

GRANT 'app_developer' TO 'dev1'@'localhost';  
GRANT 'app_read' TO 'read_user1'@'localhost', 'read_user2'@'localhost';  
GRANT 'app_read', 'app_write' TO 'rw_user1'@'localhost';

結合角色所需的讀取和寫入權限，在GRANT中授權 rw_user1使用者讀取和寫入的角色。

在GRANT授權角色的文法和授權使用者的文法不同：有一個ON來區分角色和使用者的授權，有ON的為使用者授權，而沒有ON用來配置設定角色。

由于文法不同，是以不能在同一語句中混合配置設定使用者權限和角色。（允許為使用者配置設定權限和角色，但必須使用單獨的GRANT語句，每種語句的文法都要與授權的内容相比對。）

檢查角色權限

要驗證配置設定給使用者的權限，使用 SHOW GRANTS

mysql> SHOW GRANTS FOR 'dev1'@'localhost';  
+-------------------------------------------------+  
| Grants for dev1@localhost                       |  
+-------------------------------------------------+  
| GRANT USAGE ON *.* TO `dev1`@`localhost`        |  
| GRANT `app_developer`@`%` TO `dev1`@`localhost` |  
+-------------------------------------------------+

但是，它會顯示每個授予的角色，而不會将其顯示為角色所代表的權限。如果要顯示角色權限，添加一個 USING來顯示：

mysql> SHOW GRANTS FOR 'dev1'@'localhost' USING 'app_developer';  
+----------------------------------------------------------+  
| Grants for dev1@localhost                                |  
+----------------------------------------------------------+  
| GRANT USAGE ON *.* TO `dev1`@`localhost`                 |  
| GRANT ALL PRIVILEGES ON `app_db`.* TO `dev1`@`localhost` |  
| GRANT `app_developer`@`%` TO `dev1`@`localhost`          |  
+----------------------------------------------------------+

同樣驗證其他類型的使用者：

mysql> SHOW GRANTS FOR ‘read_user1’@‘localhost’ USING ‘app_read’;

±-------------------------------------------------------+

| Grants for read_user1@localhost |

| GRANT USAGE ON . TO read_user1@localhost |

| GRANT SELECT ON app_db.* TO read_user1@localhost |

| GRANT app_read@% TO read_user1@localhost |

mysql> SHOW GRANTS FOR ‘rw_user1’@‘localhost’ USING ‘app_read’, ‘app_write’;

±-----------------------------------------------------------------------------+

| Grants for rw_user1@localhost |

| GRANT USAGE ON . TO rw_user1@localhost |

| GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO rw_user1@localhost |

| GRANT app_read@%,app_write@% TO rw_user1@localhost |

2.3 撤消角色或角色權限

正如可以授權某個使用者的角色一樣，可以從帳戶中撤銷這些角色：

REVOKE role FROM user;

REVOKE可以用于角色修改角色權限。這不僅影響角色本身權限，還影響任何授予該角色的使用者權限。假設想臨時讓所有使用者隻讀，使用REVOKE從該app_write角色中撤消修改權限：

REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM ‘app_write’;

碰巧，某個角色完全沒有任何權限，正如可以看到的那樣SHOW GRANTS （這個語句可以和角色一起使用，而不僅僅是查詢使用者權限可用）：

mysql> SHOW GRANTS FOR ‘app_write’;

±--------------------------------------+

| Grants for app_write@% |

| GRANT USAGE ON . TO app_write@% |

從角色中撤銷權限會影響到該角色中任何使用者的權限，是以 rw_user1現在已經沒有表修改權限（INSERT， UPDATE，和 DELETE權限已經沒有了）：

mysql> SHOW GRANTS FOR ‘rw_user1’@‘localhost’

USING ‘app_read’, ‘app_write’;

±---------------------------------------------------------------+

| GRANT SELECT ON app_db.* TO rw_user1@localhost |

實際上，rw_user1讀/寫使用者已成為隻讀使用者。對于被授予app_write角色的任何其他使用者也會發生這種情況，說明修改使用角色而不必修改個人帳戶的權限。

要恢複角色的修改權限，隻需重新授予它們即可：

GRANT INSERT, UPDATE, DELETE ON app_db.* TO ‘app_write’;

現在rw_user1再次具有修改權限，就像授權該app_write角色的其他任何帳戶一樣。

2.4 删除角色

要删除角色，請使用DROP ROLE：

DROP ROLE ‘app_read’, ‘app_write’;

删除角色會從授權它的每個帳戶中撤消該角色。

2.5 角色和使用者在實際中的應用

假設遺留應用開發項目在MySQL中的角色出現之前開始，是以與該項目相關聯的所有使用者都是直接授予權限（而不是授予角色權限）。其中一個帳戶是最初被授予權限的開發者使用者，如下所示：

CREATE USER ‘old_app_dev’@‘localhost’ IDENTIFIED BY ‘old_app_devpass’;

GRANT ALL ON old_app.* TO ‘old_app_dev’@‘localhost’;

如果此開發人員離開項目，則有必要将權限配置設定給其他使用者，或者項目參與人增多，則可能需要多個使用者。以下是解決該問題的一些方法：

不使用角色：更改帳戶密碼，以便原始開發人員不能使用它，并讓新的開發人員使用該帳戶：

ALTER USER ‘old_app_dev’@‘localhost’ IDENTIFIED BY ‘new_password’;

使用角色：鎖定帳戶以防止任何人使用它來連接配接伺服器：

ALTER USER ‘old_app_dev’@‘localhost’ ACCOUNT LOCK;

然後将該帳戶視為角色。對于每個新開發項目的開發者，建立一個新帳戶并授予其原始開發者帳戶：

CREATE USER ‘new_app_dev1’@‘localhost’ IDENTIFIED BY ‘new_password’;

GRANT ‘old_app_dev’@‘localhost’ TO ‘new_app_dev1’@‘localhost’;

其效果是将原始開發者帳戶權限配置設定給新帳戶。

MySQL8.0的使用者和角色管理也越來越像Oracle了，8.0中有不少新的特性，變化還是很大的，需要DBA不斷的學習和測試，更新對MySQL新版的認知，更好地運維MySQL資料庫。未來MySQL資料庫自治和智能資料庫是必然發展趨勢，對DBA來說是解放，也是挑戰。

同時也非常感謝好友知名MySQL資料庫專家吳炳錫老師在百忙中抽空對本文進行校對。

優化器索引

隐藏索引

MySQL 8.0有一個稱為“隐藏索引”的新功能，它允許快速啟用/禁用MySQL Optimizer使用的索引。

有什麼用？

一是如果你想删除一個索引，但又想事先知道效果。你就可以使它對優化程式不可見。這是一個快速的中繼資料更改，使索引不可見。一旦确定沒有性能下降，就可以真正去删除索引。

關鍵的一點是，隐藏索引不能供優化器使用，但它仍然存在，并通過寫入操作保持最新。即便我們嘗試“FORCE INDEX”，優化器也不會使用它，雖然我認為我們應該能夠在某種程度上強制它。可能會有這樣的情況：

我們可以建立一個新的隐形索引，但如果想要測試它，必須使它可見。這意味着所有對應用程式有即時影響的查詢都将能夠使用它。如果目的隻是想測試它，我不認為這是最好的方法，不是所有人的伺服器上都有相同的資料大小和真實資料。強制隐藏索引這時候可能會很有用。

你有許多索引，但不确定哪一個未使用。你可以将一個索引更改為不可見，以檢視是否存在任何性能下降。如果是，你可以立即更改。

你可能有一個特殊情況，隻有一個查詢可以使用該索引。在這種情況下，隐藏索引可能是一個很好的解決方案。

建立隐藏索引

有兩個選項

我們可以建立一個具有隐藏索引的表

或者我們可以使用alter table并将索引更改為隐藏

![](

https://img-blog.csdnimg.cn/20190310004149713.png

)

使用隐藏索引

如果我們現在要删除索引，我們可以将其更改為隐藏。但是使用“FORCE / USE INDEX”的查詢怎麼樣？他們是否會抛出一個錯誤？如果強制不存在的索引，你會收到錯誤。你不會看到隐藏索引的錯誤。優化器不會使用它，但知道它存在。

show create table t1 G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`i` int(11) DEFAULT NULL,
`j` int(11) DEFAULT NULL,
`k` int(11) DEFAULT NULL,
KEY `i_idx` (`i`),
KEY `idx_1` (`i`,`j`,`k`)
) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)
mysql> explain select * from t1 force index(idx_1) where i=1 and j=4;
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_1         | idx_1 | 10      | const,const |    2 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)
mysql> alter table t1 alter index idx_1 invisible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> explain select * from t1 force index(idx_1) where i=1 and j=4;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   16 |     6.25 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.01 sec)
mysql> explain select * from t1 where i=1 and j=4;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | i_idx         | i_idx | 5       | const |    2 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

正如你所看到的，如果我們使用帶有隐藏索引的“FORCE INDEX”，MySQL會執行全表掃描。 MySQL不會抛出任何錯誤，因為索引存在，但它不可見。即使有另一個可用的索引，它也将執行全表掃描。在大型表上，這可能會導緻嚴重的性能問題。即使MySQL在查詢執行期間不抛出任何錯誤，它也應該會在錯誤日志中記錄一個警告。

降序索引

通用表達式

MySQL遞歸CTE簡介

遞歸公用表表達式(CTE)是一個具有引用CTE名稱本身的子查詢的CTE。以下說明遞歸CTE的文法

WITH RECURSIVE cte_name AS (
    initial_query  -- anchor member
    UNION ALL
    recursive_query -- recursive member that references to the CTE name
)
SELECT * FROM cte_name;

SQL遞歸CTE由三個主要部分組成：

形成CTE結構的基本結果集的初始查詢(initial_query)

初始查詢部分被稱為錨成員。遞歸查詢部分是引用CTE名稱的查詢，是以稱為遞歸成員。遞歸成員由一個UNION ALL或UNION DISTINCT運算符與錨成員相連

終止條件是當遞歸成員沒有傳回任何行時，確定遞歸停止。

遞歸CTE的執行順序如下：

首先，将成員分為兩個：錨點和遞歸成員。

接下來，執行錨成員形成基本結果集(R0)，并使用該基本結果集進行下一次疊代

然後，将Ri結果集作為輸入執行遞歸成員，并将Ri+1作為輸出

之後，重複第三步，直到遞歸成員傳回一個空結果集，換句話說，滿足終止條件

最後，使用UNION ALL運算符将結果集從R0到Rn組合。

遞歸成員限制遞歸成員不能包含以下結構

聚合函數，如MAX，MIN，SUM，AVG，COUNT等
GROUP BY子句
ORDER BY子句
LIMIT子句
DISTINCT

請注意，上述限制不适用于錨定成員。另外，隻有在使用UNION運算符時，要禁止DISTINCT才适用。如果使用UNION DISTINCT運算符，則允許使用DISTINCT。

另外，遞歸成員隻能在其子句中引用CTE名稱，而不是引用任何子查詢。

簡單的MySQL遞歸CTE示例請參閱以下簡單的遞歸CTE 示例：

WITH RECURSIVE cte_count (n) 
AS (
      SELECT 1
      UNION ALL
      SELECT n + 1 
      FROM cte_count 
      WHERE n < 3
    )
SELECT n 
FROM cte_count;

SQL在此示例中，以下查詢：SELECT 1

SQL是作為基本結果集傳回1的錨成員。以下查詢

SELECT n + 1
FROM cte_count 
WHERE n < 3

是遞歸成員，因為它引用了cte_count的CTE名稱。遞歸成員中的表達式<3是終止條件。當n等于3，遞歸成員将傳回一個空集合，将停止遞歸。下圖顯示了上述CTE的元素：

遞歸CTE傳回以下輸出：

遞歸CTE的執行步驟如下：

首先，分離錨和遞歸成員。

接下來，錨定成員形成初始行(SELECT 1)，是以第一次疊代在n = 1時産生1 + 1 = 2。

然後，第二次疊代對第一次疊代的輸出(2)進行操作，并且在n = 2時産生2 + 1 = 3。

之後，在第三次操作(n = 3)之前，滿足終止條件(n < 3)，是以查詢停止。

最後，使用UNION ALL運算符組合所有結果集1,2和3。

參考

MySQL 8.0使用者和角色管理關于 MySQL 8.0 新特性“隐藏索引”的一點思考 MySQL遞歸CTE(公共表表達式)

MySQL 8.0從入門到精通密碼管理角色管理優化器索引通用表達式參考

密碼管理

角色管理

建立角色并授予使用者角色權限

建立角色，CREATE ROLE

為角色配置設定權限，使用與為使用者配置設定權限相同的文法執行：

檢查角色權限

優化器索引

隐藏索引

有什麼用？

建立隐藏索引

使用隐藏索引

降序索引

通用表達式

MySQL遞歸CTE簡介

SQL遞歸CTE由三個主要部分組成：

遞歸CTE的執行順序如下：

遞歸成員限制遞歸成員不能包含以下結構

簡單的MySQL遞歸CTE示例請參閱以下簡單的遞歸CTE 示例：

參考

繼續閱讀

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List