MySQL 5.7 學習心得之安全相關特性

1，賬號安全相關的特性

1.1：建立使用者

5.7版本的使用者表mysql.user要求plugin欄位非空，且預設值是mysql_native_password認證外掛，並且不再支援mysql_old_password認證外掛。5.7使用者長度最大為32位元組，之前最大長度為16位元組，並且CREATE USER 和 DROP USER 命令裡實現了 IF [NOT] EXISTS 條件判斷。5.7之後使用者透過grant建立使用者報warning。如：

?

grant all on *.* to dxy@localhost identified by 'dxy';Query OK, 0 rows affected, 1 warnings (0.00 sec)show warnings; +---------+------+---------------------------------------------------------------+| Level | Code | Message |+---------+------+---------------------------------------------------------------+| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |+---------+------+---------------------------------------------------------------+2 rows in set (0.01 sec)

提示grant建立賬戶的語法將會被刪除，用cerate user代替，建立使用者分2步：建立和授權。

先透過create user 建立使用者：

?

#明文密碼建立CREATE USER 'dxy'@'localhost' IDENTIFIED BY '123456';等同CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '123456';#加密密碼建立CREATE USER 'dxy'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'; --will be removed in a future release等同CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

再透過grant來授權：

?

1	grant select,insert,update,delete on dba_test.* to dxy@localhost;

注意：授權管理使用者的時候，不止只有all的許可權，還要包括with grant option和proxy的許可權。proxy許可權需要在代理使用者的時候用到。

?

檢視預設管理使用者許可權：show grants for root@localhost; ----2條記錄 +---------------------------------------------------------------------+ |+---------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION || GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |+---------------------------------------------------------------------+新建管理賬號：create user dba@127.0.0.1 identified by '123456';授權：GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;授proxy權：建立代理使用者的時候需要GRANT PROXY ON ''@'' TO 'dba'@'127.0.0.1' WITH GRANT OPTION;檢視：show grants for 'dba'@'127.0.0.1';+--------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'dba'@'127.0.0.1' WITH GRANT OPTION || GRANT PROXY ON ''@'' TO 'dba'@'127.0.0.1' WITH GRANT OPTION |+--------------------------------------------------------------------+

檢視使用者許可權：

?

show grants for dxy@localhost;+---------------------------------------------------------------------------+| Grants for dxy@localhost |+---------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'dxy'@'localhost' || GRANT SELECT, INSERT, UPDATE, DELETE ON `dba_test`.* TO 'dxy'@'localhost' |+---------------------------------------------------------------------------+

檢視使用者密碼：

?

1234

show create user dxy@localhost;+----------------------------------------------------------------------------------+| CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |+----------------------------------------------------------------------------------+

1.2：密碼過期策略

為使用者設定密碼過期時間，一定時間以後，強制使用者修改密碼。可以直接在create user的時候設定，也可以alter user設定：

PASSWORD EXPIRE DEFAULT	預設，過期時間受全域性變數default_password_lifetime控制
PASSWORD EXPIRE NEVER	永不過期
PASSWORD EXPIRE INTERVAL N DAY	N天后過期
PASSWORD EXPIRE	過期

直接建立使用者的時候設定：

?

1	create user dxy@localhost identified by '123456' password expire interval 10 day; ---- 10天后過期

對已有使用者設定

?

1	alter user zjy@localhost password expire never; ----永不過期

注意：設定一個使用者過期後，登陸會有提示修改密碼，不能進行任何操作：適用讓程式不能訪問資料庫。

設定使用者密碼過期：

?

1	alter user dxy@localhost password expire;

執行任何命令報錯：

?

1	ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

解決辦法：重置密碼 alter user dxy@localhost identified by '123456';

1.3：鎖定禁用使用者 alter user

當某些場景需要"鎖"住使用者，暫時禁用某個使用者：適用讓程式不能訪問資料庫。

設定鎖定使用者：

?

1	alter user dxy@localhost account lock;

登陸報錯：

?

1	ERROR 3118 (HY000): Access denied for user 'dxy'@'localhost'. Account is locked.

解決辦法：解鎖使用者

?

1	alter user dxy@localhost account unlock;

1.4 代理使用者

基於mysql_native_password的認證外掛自帶了代理使用者的功能。代理使用者相當於“代理”其他使用者的許可權，這樣很方便的把一個賬號的許可權授予其他賬號，而不需要每個賬號都需要執行授權操作。開啟代理使用者的功能需要開啟引數：check_proxy_users 和 mysql_native_password_proxy_users

建立原始賬號：

?

1	create user dxy@127.0.0.1 identified by '123456';

授權：

?

1	grant all on test.* to dxy@127.0.0.1;

建立代理賬號：

?

1	create user dxy_proxy@127.0.0.1 identified by '123456';

授權代理許可權：

?

1	grant proxy on dxy@127.0.0.1 to dxy_proxy@127.0.0.1;

檢視：

?

12345

show grants for dxy_proxy@127.0.0.1;+-------------------------------------------------------------+| GRANT USAGE ON *.* TO 'dxy_proxy'@'127.0.0.1' || GRANT PROXY ON 'dxy'@'127.0.0.1' TO 'dxy_proxy'@'127.0.0.1' |+-------------------------------------------------------------+

用代理賬號登陸測試：

檢視登陸賬號：代理賬號current_user()，原始賬號user()

?

select user(),current_user();+---------------------+----------------+| user() | current_user() |+---------------------+----------------+| dxy_proxy@127.0.0.1 | dxy@127.0.0.1 |+---------------------+----------------+

檢視許可權：發現代理賬號的許可權顯示的是原始賬號的許可權

?

show grants;+-------------------------------------------------------+ +-------------------------------------------------------+| GRANT USAGE ON *.* TO 'dxy'@'127.0.0.1' || GRANT ALL PRIVILEGES ON `test`.* TO 'dxy'@'127.0.0.1' |+-------------------------------------------------------+

驗證代理賬號是否有test庫的許可權：

?

mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || test |+--------------------+mysql> use testmysql> show tables;+----------------+| Tables_in_test |+----------------+| tttt |+----------------+mysql> select * from tttt; +------+| id |+------+| 1 || 100 |+------+mysql> insert into tttt values(2),(200);mysql> select * from tttt;+------+| id |+------+| 1 || 100 || 2 || 200 |+------+

驗證得出代理賬號(dxy_proxy)代理了原始賬號(dxy)的許可權。

1.5：其他選項：SSL、MAX_QUERIES_PER_HOUR、MAX_UPDATES_PER_HOUR、MAX_CONNECTIONS_PER_HOUR、MAX_USER_CONNECTIONS。當需要限制賬號透過ssl登陸，需要新增require，當需要限制資源，需要新增with：

?

1	create user dxy@localhost identified by '123456' require SSL with MAX_QUERIES_PER_HOUR 100 MAX_USER_CONNECTIONS 100 password expire never account unlock;

2，外部相關的安全

2.1：MySQL5.7已經刪除了test資料庫，預設安裝完後是沒有test資料庫，原先任何使用者都可以訪問test資料庫，增加安全隱患。

2.2：MySQL5.7提供了更為簡單SSL安全訪問配置，並且預設連線就採用SSL的加密方式。在5.7之前，生成SSL相關檔案需要自己手動建立，可以檢視這篇文章，5.7之後MySQL透過

mysql_ssl_rsa_setup可以直接生成了：

?

root@t20:~# mysql_ssl_rsa_setup Generating a 2048 bit RSA private key.................................+++....................+++writing new private key to 'ca-key.pem'-----Generating a 2048 bit RSA private key......+++..............................+++writing new private key to 'server-key.pem'-----Generating a 2048 bit RSA private key.........................................................................................+++..+++writing new private key to 'client-key.pem'-----

可以在資料目錄下面看到一些以pem結尾的檔案，而這些檔案就是開啟SSL連線所需要的檔案(注意檔案許可權)，之後用賬號

預設登陸：

?

root@t20:/var/lib/mysql# mysql -udba -p -h127.0.0.1Enter password: mysql> s--------------mysql Ver 14.14 Distrib 5.7.12, for Linux (x86_64) using EditLine wrapperConnection id: 4Current database: Current user: dba@localhostSSL: Cipher in use is DHE-RSA-AES256-SHA......

強制ssl登陸：

?

root@t20:~# mysql -udba -p -h127.0.0.1 --ssl=1 WARNING: --ssl is deprecated and will be removed in a future version. Use --ssl-mode instead.Enter password: mysql> s--------------mysql Ver 14.14 Distrib 5.7.12, for Linux (x86_64) using EditLine wrapperConnection id: 10Current database: Current user: dba@localhostSSL: Cipher in use is DHE-RSA-AES256-SHA......

從上面看到均已ssl登陸，若在建立使用者時，希望該使用者每次必須透過SSL方式，則需在建立使用者透過REQUIRE SSL來進行設定，上面已經介紹。姜承堯文章中的測試案例顯示開啟SSL效能開銷在25%左右：MySQL的SSL加密連線與效能開銷

2.3：MySQL5.7開始建議使用者使用 mysqld --initialize來初始化資料庫，放棄之前的mysql_install_db的方式，新的方式只建立了一個root@localhost的使用者，隨機密碼儲存在~/.mysql_secret檔案中，第一次使用必須reset password。

初始化資料庫：新建例項。

mysqld --initialize --datadir=/var/lib/mysql3309/

2.4：MySQL5.7 sql_mode的變更，

5.7預設的sql_mode

select @@sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

5.7之前預設的sql_mode

select @@sql_mode;
NO_ENGINE_SUBSTITUTION

看到在5.7中sql_mode更加嚴格。解釋下各個mode的含義：

ONLY_FULL_GROUP_BY	不要讓GROUP BY部分中的查詢指向未選擇的列
STRICT_TRANS_TABLES	為事務儲存引擎啟用嚴格模式，也可能為非事務儲存引擎啟用嚴格模式
NO_ZERO_IN_DATE	在嚴格模式，不接受月或日部分為0的日期
NO_ZERO_DATE	在嚴格模式，不將 '0000-00-00'做為合法日期
ERROR_FOR_DIVISION_BY_ZERO	在嚴格模式，在INSERT或UPDATE過程中，如果被零除(或MOD(X，0))，則產生錯誤
NO_AUTO_CREATE_USER	防止GRANT自動建立新使用者，除非還指定了密碼
NO_ENGINE_SUBSTITUTION	如果需要的儲存引擎被禁用或未編譯，可以防止自動替換儲存引擎

在預設情況下5.7的情況：

?

----對於datetime型別：插入"0000-00-00 00:00:00"值，會報錯：Incorrect datetime value----對於varchar/char型別：插入字串超出長度，會報錯： Data too long for column...----對於not null的列：插入不指定not null的列會報錯：Field 'xxx' doesn't have a default value '----對於grant：授權一個使用者，不指定密碼會報錯：Can't find any matching row in the user table '----對於engine儲存引擎：建立一個不支援的儲存引擎，不會轉換為預設的儲存引擎，直接報錯：Unknown storage engine ... Using storage engine InnoDB for table '...'

注意：在一個主從環境下，為保證資料的一致性，一定要設定主從的sql_mode一樣，在資料遷移的時候也要保證sql_mode的一致，不然複製和遷移遇到上面的限制均會失敗，所以儘可能使用標準SQL語法。

3，總結：

在MySQL 5.7中，有不少安全性相關的改進：建立賬號分2步：用create user來建立賬號（賬號長度加大），用grant 來授權；初始資料庫的時候密碼不為空；賬號可以鎖和可以設定密碼過期；test庫被刪除；預設提供ssl連線；sql_mode增強等。文章從這些方面進行了介紹和測試，進一步加深對MySQL5.7的認識。

以上所述是小編給大家介紹的MySQL 5.7 學習心得之安全相關特性，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回覆大家的。在此也非常感謝大家對指令碼之家網站的支援！