MySQL 5.7 學(xué)習(xí)心得之安全相關(guān)特性

更新時(shí)間：2016年07月01日 14:36:53 作者：jyzhou

這篇文章主要介紹了MySQL 5.7 學(xué)習(xí)心得之安全相關(guān)特性的相關(guān)資料,需要的朋友可以參考下

1，賬號(hào)安全相關(guān)的特性

1.1：創(chuàng)建用戶(hù)

5.7版本的用戶(hù)表mysql.user要求plugin字段非空，且默認(rèn)值是mysql_native_password認(rèn)證插件，并且不再支持mysql_old_password認(rèn)證插件。5.7用戶(hù)長(zhǎng)度最大為32字節(jié)，之前最大長(zhǎng)度為16字節(jié)，并且CREATE USER 和 DROP USER 命令里實(shí)現(xiàn)了 IF [NOT] EXISTS 條件判斷。5.7之后用戶(hù)通過(guò)grant創(chuàng)建用戶(hù)報(bào)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創(chuàng)建賬戶(hù)的語(yǔ)法將會(huì)被刪除，用cerate user代替，創(chuàng)建用戶(hù)分2步：創(chuàng)建和授權(quán)。

先通過(guò)create user 創(chuàng)建用戶(hù)：

#明文密碼創(chuàng)建
CREATE USER 'dxy'@'localhost' IDENTIFIED BY '123456';等同
CREATE USER 'dxy'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '123456';
#加密密碼創(chuàng)建
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';

再通過(guò)grant來(lái)授權(quán)：

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

注意：授權(quán)管理用戶(hù)的時(shí)候，不止只有all的權(quán)限，還要包括with grant option和proxy的權(quán)限。proxy權(quán)限需要在代理用戶(hù)的時(shí)候用到。

查看默認(rèn)管理用戶(hù)權(quán)限：
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 |
+---------------------------------------------------------------------+
新建管理賬號(hào)：
create user dba@127.0.0.1 identified by '123456';
授權(quán)：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;
授proxy權(quán)：創(chuàng)建代理用戶(hù)的時(shí)候需要
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 |
+--------------------------------------------------------------------+

查看用戶(hù)權(quán)限：

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' |
+---------------------------------------------------------------------------+

查看用戶(hù)密碼：

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：密碼過(guò)期策略

為用戶(hù)設(shè)置密碼過(guò)期時(shí)間，一定時(shí)間以后，強(qiáng)制用戶(hù)修改密碼?？梢灾苯釉赾reate user的時(shí)候設(shè)置，也可以alter user設(shè)置：

PASSWORD EXPIRE DEFAULT	默認(rèn)，過(guò)期時(shí)間受全局變量default_password_lifetime控制
PASSWORD EXPIRE NEVER	永不過(guò)期
PASSWORD EXPIRE INTERVAL N DAY	N天后過(guò)期
PASSWORD EXPIRE	過(guò)期

直接創(chuàng)建用戶(hù)的時(shí)候設(shè)置：

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

對(duì)已有用戶(hù)設(shè)置

alter user zjy@localhost password expire never; ----永不過(guò)期

注意：設(shè)置一個(gè)用戶(hù)過(guò)期后，登陸會(huì)有提示修改密碼，不能進(jìn)行任何操作：適用讓程序不能訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)。

設(shè)置用戶(hù)密碼過(guò)期：

alter user dxy@localhost password expire;

執(zhí)行任何命令報(bào)錯(cuò)：

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：鎖定禁用用戶(hù) alter user

當(dāng)某些場(chǎng)景需要"鎖"住用戶(hù)，暫時(shí)禁用某個(gè)用戶(hù)：適用讓程序不能訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)。

設(shè)置鎖定用戶(hù)：

alter user dxy@localhost account lock;

登陸報(bào)錯(cuò)：

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

解決辦法：解鎖用戶(hù)

alter user dxy@localhost account unlock;

1.4 代理用戶(hù)

基于mysql_native_password的認(rèn)證插件自帶了代理用戶(hù)的功能。代理用戶(hù)相當(dāng)于“代理”其他用戶(hù)的權(quán)限，這樣很方便的把一個(gè)賬號(hào)的權(quán)限授予其他賬號(hào)，而不需要每個(gè)賬號(hào)都需要執(zhí)行授權(quán)操作。開(kāi)啟代理用戶(hù)的功能需要開(kāi)啟參數(shù)：check_proxy_users 和 mysql_native_password_proxy_users

創(chuàng)建原始賬號(hào)：

create user dxy@127.0.0.1 identified by '123456';

授權(quán)：

grant all on test.* to dxy@127.0.0.1;

創(chuàng)建代理賬號(hào)：

create user dxy_proxy@127.0.0.1 identified by '123456';

授權(quán)代理權(quán)限：

grant proxy on dxy@127.0.0.1 to dxy_proxy@127.0.0.1;

查看：

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' |
+-------------------------------------------------------------+

用代理賬號(hào)登陸測(cè)試：

查看登陸賬號(hào)：代理賬號(hào)current_user()，原始賬號(hào)user()

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

查看權(quán)限：發(fā)現(xiàn)代理賬號(hào)的權(quán)限顯示的是原始賬號(hào)的權(quán)限

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

驗(yàn)證代理賬號(hào)是否有test庫(kù)的權(quán)限：

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| test |
+--------------------+
mysql> use test
mysql> 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 |
+------+

驗(yàn)證得出代理賬號(hào)(dxy_proxy)代理了原始賬號(hào)(dxy)的權(quán)限。

1.5：其他選項(xiàng)：SSL、MAX_QUERIES_PER_HOUR、MAX_UPDATES_PER_HOUR、MAX_CONNECTIONS_PER_HOUR、MAX_USER_CONNECTIONS。當(dāng)需要限制賬號(hào)通過(guò)ssl登陸，需要添加require，當(dāng)需要限制資源，需要添加with：

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，外部相關(guān)的安全

2.1：MySQL5.7已經(jīng)刪除了test數(shù)據(jù)庫(kù)，默認(rèn)安裝完后是沒(méi)有test數(shù)據(jù)庫(kù)，原先任何用戶(hù)都可以訪(fǎng)問(wèn)test數(shù)據(jù)庫(kù)，增加安全隱患。

2.2：MySQL5.7提供了更為簡(jiǎn)單SSL安全訪(fǎng)問(wèn)配置，并且默認(rèn)連接就采用SSL的加密方式。在5.7之前，生成SSL相關(guān)文件需要自己手動(dòng)創(chuàng)建，可以查看這篇文章，5.7之后MySQL通過(guò)

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'
-----

可以在數(shù)據(jù)目錄下面看到一些以pem結(jié)尾的文件，而這些文件就是開(kāi)啟SSL連接所需要的文件(注意文件權(quán)限)，之后用賬號(hào)

默認(rèn)登陸：

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

強(qiáng)制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 wrapper
Connection id: 10
Current database: 
Current user: dba@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
...
...

從上面看到均已ssl登陸，若在創(chuàng)建用戶(hù)時(shí)，希望該用戶(hù)每次必須通過(guò)SSL方式，則需在創(chuàng)建用戶(hù)通過(guò)REQUIRE SSL來(lái)進(jìn)行設(shè)置，上面已經(jīng)介紹。姜承堯文章中的測(cè)試案例顯示開(kāi)啟SSL性能開(kāi)銷(xiāo)在25%左右：MySQL的SSL加密連接與性能開(kāi)銷(xiāo)

2.3：MySQL5.7開(kāi)始建議用戶(hù)使用 mysqld --initialize來(lái)初始化數(shù)據(jù)庫(kù)，放棄之前的mysql_install_db的方式，新的方式只創(chuàng)建了一個(gè)root@localhost的用戶(hù)，隨機(jī)密碼保存在~/.mysql_secret文件中，第一次使用必須reset password。

初始化數(shù)據(jù)庫(kù)：新建實(shí)例。

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

2.4：MySQL5.7 sql_mode的變更，

5.7默認(rèn)的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之前默認(rèn)的sql_mode

select @@sql_mode;
NO_ENGINE_SUBSTITUTION

看到在5.7中sql_mode更加嚴(yán)格。解釋下各個(gè)mode的含義：

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

在默認(rèn)情況下5.7的情況：

----對(duì)于datetime類(lèi)型<NO_ZERO_DATE>：
插入"0000-00-00 00:00:00"值，會(huì)報(bào)錯(cuò)：Incorrect datetime value
----對(duì)于varchar/char類(lèi)型<STRICT_TRANS_TABLES>：
插入字符串超出長(zhǎng)度，會(huì)報(bào)錯(cuò)： Data too long for column...
----對(duì)于not null的列<STRICT_TRANS_TABLES>：
插入不指定not null的列會(huì)報(bào)錯(cuò)：Field 'xxx' doesn't have a default value ' 
----對(duì)于grant<NO_AUTO_CREATE_USER>：
授權(quán)一個(gè)用戶(hù)，不指定密碼會(huì)報(bào)錯(cuò)：Can't find any matching row in the user table '
----對(duì)于engine存儲(chǔ)引擎<NO_ENGINE_SUBSTITUTION>：
創(chuàng)建一個(gè)不支持的存儲(chǔ)引擎，不會(huì)轉(zhuǎn)換為默認(rèn)的存儲(chǔ)引擎，直接報(bào)錯(cuò)：Unknown storage engine ... Using storage engine InnoDB for table '...'

注意：在一個(gè)主從環(huán)境下，為保證數(shù)據(jù)的一致性，一定要設(shè)置主從的sql_mode一樣，在數(shù)據(jù)遷移的時(shí)候也要保證sql_mode的一致，不然復(fù)制和遷移遇到上面的限制均會(huì)失敗，所以盡可能使用標(biāo)準(zhǔn)SQL語(yǔ)法。

3，總結(jié)：

在MySQL 5.7中，有不少安全性相關(guān)的改進(jìn)：創(chuàng)建賬號(hào)分2步：用create user來(lái)建立賬號(hào)（賬號(hào)長(zhǎng)度加大），用grant 來(lái)授權(quán)；初始數(shù)據(jù)庫(kù)的時(shí)候密碼不為空；賬號(hào)可以鎖和可以設(shè)置密碼過(guò)期；test庫(kù)被刪除；默認(rèn)提供ssl連接；sql_mode增強(qiáng)等。文章從這些方面進(jìn)行了介紹和測(cè)試，進(jìn)一步加深對(duì)MySQL5.7的認(rèn)識(shí)。

以上所述是小編給大家介紹的MySQL 5.7 學(xué)習(xí)心得之安全相關(guān)特性，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！

您可能感興趣的文章: