MySQL權(quán)限管理實(shí)戰(zhàn)！

前言： 

不清楚各位同學(xué)對(duì)數(shù)據(jù)庫(kù)用戶權(quán)限管理是否了解，作為一名 DBA ，用戶權(quán)限管理是繞不開的一項(xiàng)工作內(nèi)容。特別是生產(chǎn)庫(kù)，數(shù)據(jù)庫(kù)用戶權(quán)限更應(yīng)該規(guī)范管理。本篇文章將會(huì)介紹下 MySQL 用戶權(quán)限管理相關(guān)內(nèi)容。

  1.用戶權(quán)限簡(jiǎn)介

當(dāng)我們創(chuàng)建過數(shù)據(jù)庫(kù)用戶后，還不能執(zhí)行任何操作，需要為該用戶分配適當(dāng)?shù)脑L問權(quán)限。

關(guān)于 MySQL 用戶權(quán)限簡(jiǎn)單的理解就是數(shù)據(jù)庫(kù)只允許用戶做你權(quán)利以內(nèi)的事情，不可以越界。比如只允許你執(zhí)行 select 操作，那么你就不能執(zhí)行 update 操作。只允許你從某個(gè) IP 上連接 MySQL ，那么你就不能從除那個(gè) IP 以外的其他機(jī)器連接 MySQL 。

在 MySQL 中，用戶權(quán)限也是分級(jí)別的，可以授予的權(quán)限有如下幾組：

• 列級(jí)別，和表中的一個(gè)具體列相關(guān)。例如，可以使用 UPDATE 語句更新表 students 中 student_name 列的值的權(quán)限。

• 表級(jí)別，和一個(gè)具體表中的所有數(shù)據(jù)相關(guān)。例如，可以使用 SELECT 語句查詢表 students 的所有數(shù)據(jù)的權(quán)限。

• 數(shù)據(jù)庫(kù)級(jí)別，和一個(gè)具體的數(shù)據(jù)庫(kù)中的所有表相關(guān)。例如，可以在已有的數(shù)據(jù)庫(kù) mytest 中創(chuàng)建新表的權(quán)限。

• 全局，和 MySQL 中所有的數(shù)據(jù)庫(kù)相關(guān)。例如，可以刪除已有的數(shù)據(jù)庫(kù)或者創(chuàng)建一個(gè)新的數(shù)據(jù)庫(kù)的權(quán)限。

權(quán)限信息存儲(chǔ)在 mysql 系統(tǒng)庫(kù)的 user、db、tables_priv、columns_priv、procs_priv 這幾個(gè)系統(tǒng)表中。

• user 表：存放用戶賬戶信息以及全局級(jí)別（所有數(shù)據(jù)庫(kù)）權(quán)限。

• db 表：存放數(shù)據(jù)庫(kù)級(jí)別的權(quán)限，決定了來自哪些主機(jī)的哪些用戶可以訪問此數(shù)據(jù)庫(kù)。

• tables_priv 表：存放表級(jí)別的權(quán)限，決定了來自哪些主機(jī)的哪些用戶可以訪問數(shù)據(jù)庫(kù)的這個(gè)表。

• columns_priv 表：存放列級(jí)別的權(quán)限，決定了來自哪些主機(jī)的哪些用戶可以訪問數(shù)據(jù)庫(kù)表的這個(gè)字段。

• procs_priv 表：存放存儲(chǔ)過程和函數(shù)級(jí)別的權(quán)限。

參考官方文檔，可授予的權(quán)限如下表所示：

看起來各種可授予的權(quán)限有很多，其實(shí)可以大致分為數(shù)據(jù)、結(jié)構(gòu)、管理三類，大概可分類如下：

  2.權(quán)限管理實(shí)戰(zhàn)

我們一般用 grant 語句為數(shù)據(jù)庫(kù)用戶賦權(quán)，建議大家先用 create user 語句創(chuàng)建好用戶之后再單獨(dú)進(jìn)行授權(quán)。下面通過示例來具體看下：

# 創(chuàng)建用戶
create user 'test_user'@'%' identified by 'xxxxxxxx';

# 全局權(quán)限
GRANT super,select on *.* to 'test_user'@'%';

# 庫(kù)權(quán)限
GRANT select,insert,update,delete,create,alter,execute on `testdb`.* to 'test_user'@'%';

# 表權(quán)限
GRANT select,insert on `testdb`.tb to 'test_user'@'%';

# 列權(quán)限
GRANT select (col1), insert (col1, col2) ON `testdb`.mytbl to 'test_user'@'%';

# GRANT命令說明：
super,select 表示具體要授予的權(quán)限。
ON 用來指定權(quán)限針對(duì)哪些庫(kù)和表。
*.* 中前面的*號(hào)用來指定數(shù)據(jù)庫(kù)名，后面的*號(hào)用來指定表名。
TO 表示將權(quán)限賦予某個(gè)用戶。
'test_user'@'%' 表示test_user用戶，@后面接限制的主機(jī)，可以是IP、IP段、域名以及%，%表示任何地方。

# 刷新權(quán)限
flush privileges;

# 查看某個(gè)用戶的權(quán)限
show grants for 'test_user'@'%';

# 回收權(quán)限
revoke delete on `testdb`.* from 'test_user'@'%';

權(quán)限管理是一件不容忽視的事，我們不能為了方便而給數(shù)據(jù)庫(kù)用戶很大的權(quán)限。特別是對(duì)于生產(chǎn)庫(kù)，更應(yīng)該進(jìn)行權(quán)限管控，建議程序用戶只賦予增刪改查等基礎(chǔ)權(quán)限，個(gè)人用戶只賦予查詢權(quán)限。

出于安全考慮，建議遵循以下幾個(gè)經(jīng)驗(yàn)原則：

• 只授予能滿足需要的最小權(quán)限，防止用戶干壞事。比如用戶只是需要查詢，那就只給 select 權(quán)限就可以了。

• 創(chuàng)建用戶的時(shí)候限制用戶的登錄主機(jī)，一般是限制成指定 IP 或者內(nèi)網(wǎng) IP 段。

• 給各個(gè)服務(wù)單獨(dú)創(chuàng)建數(shù)據(jù)庫(kù)用戶，單個(gè)用戶最好只能操作單個(gè)庫(kù)。

• 及時(shí)記錄各數(shù)據(jù)庫(kù)用戶權(quán)限等信息，以免忘記。

• 若有外部系統(tǒng)調(diào)用，應(yīng)配置只讀用戶，并且權(quán)限要精確到表或視圖。

• 定期清理不需要的用戶，回收權(quán)限或者刪除用戶。

參考：

• https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html

• https://www.cnblogs.com/richardzhu/p/3318595.html