[玩轉MySQL之三]MySQL用戶及許可權
引言
資料庫保存著應用程序日積夜累記錄下來的數據資產，安全級別特別高，所以只能讓授權的用戶可以訪問，其他用戶需一律拒絕。MySQL是一個多用戶資料庫，擁有功能強大的訪問控制系統，可以為不同的用戶指定不同的許可權。 小編一直對MySQL的用戶及許可權管理都是一知半解，存有疑問，具體疑問如下:
1.MySQL如何認證一個用戶？
小編認為"用戶認證"就是為瞭解決一個問題:你是誰？。在國內，中國公民要證明他是誰，只要拿出身份證就可以，因為身份證上的照片，姓名，家庭住址，性別，出生年月，身份證號碼等信息，是中國政府為了說明你就是中國大地上某個地方的某某某而制定的。那麼在MySQL Server中，一個用戶是如何認證的？
2.MySQL的許可權分哪幾種及存儲在什麼地方？
小編認為"MySQL許可權"是為瞭解決一個問題: 你能在MySQL Server內幹哪些事情？就好比圖書館一樣，只有辦了卡的人才允許進入，不同的卡可以進入不同的圖書館區域，可以做不同的事情，即擁有不一樣的許可權，那麼MySQL的許可權有哪些？並且這些許可權存儲在哪裡？
3. MySQL是如何控制用戶訪問的？
繼續使用圖書館的栗子，當你要進圖書館的時候，需要刷卡或者與管理員溝通，如果無效，那麼將會出現謝絕參閱的禮貌回復；假如你有許可權進入圖書館，但是你沒有借書的權利，那麼在你借書的時候，會借書失敗。在MySQL Server中， 一個用戶想要對MySQL Server進行操作，MySQL Server是如何控制用戶行為的？
一、MySQL用戶認證
MySQL的用戶認證形式是: 用戶名+主機。比如[email protected]和[email protected]是不一樣的用戶。就好比現實中的牛家村的張三和馬家村的張三是分別兩個人一樣。MySQL中的許可權分配都是分配到用戶+主機的實體上。MySQL的主機信息可以是本地(localhost)，某個IP，某個IP段，以及任何地方等，即用戶的地址可以限制到某個具體的IP，或者某個IP範圍，或者任意地方。MySQL用戶分為普通用戶和root用戶。root用戶是超級管理員，擁有所有許可權，普通用戶只擁有被授予的各種許可權。
二、MySQL的許可權分類及存儲
1.MySQL用戶許可權層級
- 全局層級：全局許可權適用於一個給定MySQL Server中的所有資料庫，這些許可權存儲在mysql.user表中。
GRANT ALL ON *.* TO user@host; # *.* 表示資料庫庫的所有庫和表，對應許可權存儲在mysql.user表中
- 資料庫層級：資料庫許可權適用於一個給定資料庫中的所有目標，這些許可權存儲在mysql.db表中。
GRANT ALL ON mydb.* TO user@host; #mydb.* 表示mysql資料庫下的所有表，對應許可權存儲在mysql.db表中
- 表層級：表許可權適用於一個給定表中的所有列，這些許可權存儲在mysql.tables_priv表中。
GRANT ALL ON mydb.mytable TO user@host; #mydb.mytable 表示mysql資料庫下的mytable表，對應許可權存儲在mysql.tables_priv表
- 列層級：列許可權使用於一個給定表中的單一列，這些許可權存儲在mysql.columns_priv表中。
GRANT ALL (col1， col2， col3) ON mydb.mytable TO user@host; #mydb.mytable 表示mysql資料庫下的mytable表， col1, col2, col3表示mytable表中的列名
- 子程序層級：CREATE ROUTINE、ALTER ROUTINE、EXECUTE和GRANT許可權適用於已存儲的子程序。這些許可權可以被授予為全局層級和資料庫層級。而且，除了CREATE ROUTINE外，這些許可權可以被授予子程序層級，並存儲在mysql.procs_priv表中。
GRANT EXECUTE ON PROCEDURE mydb.myproc TO user@host; #mydb.mytable 表示mysql資料庫下的mytable表，PROCEDUR表示存儲過程
2. MySQL許可權簡單分類
- 數據許可權分為：庫、表和欄位三種級別
- 管理許可權主要是管理員要使用到的許可權，包括：資料庫創建，臨時表創建、主從部署、進程管理等
- 程序許可權主要是觸發器、存儲過程、函數等許可權。
2. MySQL許可權詳情
第一列表示可以在grant命令中制定的許可權第二列對應著幾張許可權表(user,db,tables_priv, columns_priv, procs_priv)中的列第三列表示許可權的作用範圍，其中Global（Server administration）對應 mysql.user 表，Database 對應 mysql.db 表，Tables 對應 mysql.tables_priv 表，Columns 對應 mysql.columns_priv 表，Stored routines 對應 mysql.procs_priv 表。
MYSQL的許可權如何分佈，就是針對錶可以設置什麼許可權，針對列可以設置什麼許可權等等，這個可以從官方文檔中的一個表來說明：
許可權分佈可能設置的許可權表許可權Select, Insert, Update, Delete, Create, Drop, Grant, References, Index, Alter列許可權Select, Insert, Update, References程序許可權Execute, Alter Routine, Grant
三、 MySQL訪問控制
MySQL訪問控制分為兩個階段:
- 用戶連接檢查階段
- 執行SQL語句時檢查階段
1、用戶連接時的檢查
1）當用戶連接時，MySQL伺服器首先從user表裡匹配host, user, password，匹配不到則拒絕該連接2）接著檢查user表的max_connections和max_user_connections，如果超過上限則拒絕連接3）檢查user表的SSL安全連接，如果有配置SSL，則需確認用戶提供的證書是否合法只有上面3個檢查都通過後，伺服器才建立連接，連接建立後，當用戶執行SQL語句時，需要做SQL語句執行檢查。
2、執行SQL語句時的檢查
1）從user表裡檢查max_questions和max_updates，如果超過上限則拒絕執行SQL下面幾步是進行許可權檢查：2）首先檢查user表，看是否具有相應的全局性許可權，如果有，則執行，沒有則繼續下一步檢查3）接著到db表，看是否具有資料庫級別的許可權，如果有，則執行，沒有則繼續下一步檢查4）最後到tables_priv, columns_priv, procs_priv表裡查看是否具有相應對象的許可權從以上的過程我們可以知道，MySQL檢查許可權是一個比較複雜的過程，所以為了提高性能，MySQL的啟動時就會把這5張許可權表載入到內存。
四、許可權表欄位詳解
1.user表user表的許可權是基於伺服器範圍的所有許可權，比如用戶擁有伺服器中所有資料庫的select許可權那麼在user表中的Select_priv列為Y,如果用戶單單只擁有某個一資料庫的select許可權那麼user表中的Select_priv為N,會在DB表中記錄一條信息在DB表中的select_priv為Y。
desc mysql.user;
2.db表如果授予一個用戶單獨某個資料庫的許可權，就會在db表中記錄一條相關信息。
desc mysql.db;
3.tables_priv表
desc mysql.tables_priv;
上面的Column_priv比較奇怪，因為照理說tables_priv只顯示錶級別的許可權，列級別的許可權應該在columns_priv裏顯示才對。後來查了資料才知道，原來這是為了提高許可權檢查時的性能，試想一下，許可權檢查時，如果發現tables_priv.Column_priv為空，就不需要再檢查columns_priv表了，這種情況在現實中往往佔大多數。
4. columns_priv表
desc mysql.columns_priv;
5. procs_priv表
desc mysql.procs_priv;
五、用戶管理實踐
1.用戶創建
- 通過create user語句創建用戶
在執行CREATE USER或CRANT語句後，MySQL伺服器會修改相應的用戶許可權表，添加或修改用戶及許可權。
create user USERNAME@HOST identified by PASSWORD;
HOST的形式：1. IP地址，如172.16.16.1；2. 主機名，如localhost；3. 網路地址，如172.16.0.04. 通配符，如%：匹配任意字元_：匹配任意單個字元如172.16.16._(允許172.16.16.1-172.16.16.9)
eg:
mysql> CREATE USER jeffrey@localhost IDENTIFIED BY mypass;
Query OK, 0 rows affected (0.00 sec)
- 通過grant語句創建新用戶
GRANT語句是添加新用戶並授權它們訪問MySQL對象的首選方法，其語法格式為：
grant all on DB_NAME.TABLE_NAME to USERNAME@HOST identified by PASSWORD;
HOST的表現形式和create user一樣
eg：
# 用戶 testUser對所有的數據有查詢和更新許可權，並授於對所有數據表的SELECT和UPDATE許可權
mysql> GRANT SELECT,UPDATE ON *.* TO testUser@localhost IDENTIFIED BY testpwd;
Query OK, 0 rows affected (0.00 sec)
1) 創建root用戶
mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY root WITH GRANT OPTION;
mysql> flush privileges;
2). 創建一個基本的增刪改查用戶
mysql> GRANT UPDATE, DELETE, INSERT, SELECT ON *.* TO test@% identified by test WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
mysql> flush privileges;
# MAX_QUERIES_PER_HOUR，MAX_CONNECTIONS_PER_HOUR，MAX_UPDATES_PER_HOUR設置為0表示不限制
3). 創建資料庫基本的增刪改查用戶
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, SHOW VIEW, EXECUTE ON `db_name`.* TO test@% identified by test;
mysql> flush privileges;
4). 授予資料庫名以db開頭的資料庫的許可權
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, SHOW VIEW, EXECUTE ON `db%`.* TO perform@%;
mysql> flush privileges;
5). 創建備份用戶許可權
mysql> GRANT SELECT,EVENT,SHOW DATABASES,LOCK TABLES,SUPER,REPLICATION CLIENT ON *.* TO backup@localhost identified by backup;
mysql> flush privileges;
6). 備份恢復用戶許可權
mysql> GRANT INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER,CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON *.* TO restore@localhost identified by 123456;
mysql> flush privileges;
7). 複製用戶許可權
mysql> GRANT PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@% IDENTIFIED BY 123456;
mysql> flush privileges;
2.用戶刪除
mysql> drop user USERNAME@HOST;
# 刪除MySQL默認的無用賬戶;
mysql> drop user [email protected];
# 刪除MySQL默認的無用賬戶;
mysql> drop user [email protected];
3. 更改用戶名
mysql> rename user OLD_NAME to NEW_NAME;
4. 修改用戶密碼
- 通過mysqladmin工具
# 給root@localhost用戶登錄mysql設置密碼為"redhat";
$ mysqladmin -u root -h localhost password "redhat"
# 修改root@localhost用戶登錄mysql資料庫的密碼;
$ mysqladmin -u root -h localhost password "new passwd" -p "old passwd"
- 通過直接修改mysql.user表的用戶記錄
# MySQL 5.6
mysql> update mysql.user set password=PASSWORD(redhat) where user=root;
mysql> flush privileges;
# MySQL 5.7
mysql> update mysql.user set authentication_string=PASSWORD(redhat) where user=root;
mysql> flush privileges;
- set password語句
mysql> set password for root@localhost=PASSWORD(redhat);
mysql> flush privileges;
- ALTER USER語句(MYSQL5.7版本)
mysql> use mysql
mysql> alter user root@localhost identified by 123456;
mysql> flush privileges;
六、MySQL管理員密碼找回
1.修改配置文件，跳過授權表在配置文件中[mysqld]欄位添加skip-grant-tables指令
$ cat /etc/my.cnf
[mysqld]
skip-grant-tables
2. 重啟MySQL Server
service mysqld restart
3. 給root用戶登錄mysql設置密碼為helloWORD並以加密方式
mysql> use mysql;
# MySQL5.6
mysql> update user set password=PASSWORD(helloWORD) where user=root;
# MySQL5.7
mysql> update mysql.user set authentication_string=PASSWORD(helloWORD) where user=root;
mysql> flush privileges;
4.修改配置文件，注釋剛才添加的配置項
$ cat /etc/my.cnf
[mysqld]
#skip-grant-tables
5.重啟MySQL Server
service mysqld restart
七、 MySQL 許可權管理實踐
賬戶許可權信息被存儲在MySQL資料庫的幾張許可權表中，在MySQL啟動時，伺服器將這些資料庫表中許可權信息的內容讀入內存。其中GRANT和REVOKE語句所涉及的常用許可權大致如下這些：CREATE、DROP、SELECT、INSERT、UPDATE、DELETE、INDEX、ALTER、CREATE、ROUTINE、FILE等，還有一個特殊的proxy許可權，是用來賦予某個用戶具有給他人賦予許可權的許可權。1. grant 所有許可權
mysql> grant all privileges on *.* to USERNAME@HOST;
mysql> flush privileges;
2. grant super許可權在*.*上(super許可權可以對全局變數更改)；
mysql> grant super on *.* to USERNAME@HOST;
mysql> flush privileges;
3. grant某個庫下所有表的所有許可權
mysql> grant all privileges on DB_NAME.* to USERNAME@HOST;
mysql> flush privileges;
4. grant某個庫下所有表的select許可權
mysql>grant select on DB_NAME.* to USERNAME@HOST;
mysql> flush privileges;
5. grant某個庫下某個表的insert許可權
mysql> grant insert on DB_NAME.TABLE_NAME to USERNAME@HOST;
mysql> flush privileges;
6. grant某個庫下某個表的update許可權
mysql>grant update on DB_NAME.TABLE_NAME to USERNAME@HOST;
mysql> flush privileges;
7. grant某個庫下某個表的某個欄位update許可權
mysql> grant update(COLUMN_NAME) on DB_NAME.TABLE_NAME to USERNAME@HOST;
mysql> flush privileges;
8.通過GRANT語句中的USAGE許可權，可以創建賬戶而不授予任何許可權
mysql> grant usage on *.* to USERNAME@HOST;
mysql> flush privileges;
9. grant創建、修改、刪除MySQL數據表結構許可權
mysql> grant create on testdb.* to [email protected].%;
mysql> grant alter on testdb.* to [email protected].%;
mysql> grant drop on testdb.* to [email protected].%;
mysql> flush privileges;
10. grant操作MySQL外鍵許可權
mysql> grant references on testdb.* to [email protected].%;
mysql> flush privileges;
11. grant操作MySQL臨時表許可權
mysql> grant create temporary tables on testdb.* to [email protected].%;
mysql> flush privileges;
12. grant操作MySQL索引許可權
mysql> grant index on testdb.* to [email protected].%;
mysql> flush privileges;
13.grant操作MySQL視圖、查看視圖源代碼許可權
mysql> grant create view on testdb.* to [email protected].%;
mysql> grant show view on testdb.* to [email protected].%;
mysql> flush privileges;
14. grant操作MySQL存儲過程、存儲函數許可權
mysql> grant create routine on testdb.* to [email protected].%;
mysql> grant alter routine on testdb.* to [email protected].%;
mysql> grant execute on testdb.* to [email protected].%;
mysql> flush privileges;
15.PROXY特殊許可權如果想讓某個用戶具有給他人賦予許可權的能力，那麼就需要proxy許可權了。當你給一個用戶賦予all許可權之後，你查看mysql.user表會發現Grant_priv欄位還是為N，表示其沒有給他人賦予許可權的許可權。
我們可以查看一下系統默認的超級管理員許可權：
mysql> show grants for root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO root@localhost WITH GRANT OPTION |
| GRANT PROXY ON @ TO root@localhost WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
可以看到其本身有PROXY許可權，並且這個語句跟一般授權語句還不太一樣。所以如果想讓一個遠程用戶有給他人賦予許可權的能力，就需要給此用戶PROXY許可權，如下：
mysql> grant all on *.* to test@% identified by helloWORD;
mysql> GRANT PROXY ON @ TO test@% WITH GRANT OPTION;
mysql> flush privileges;
16. 查看用戶的許可權
Mysql> show grants for USERNAME@HOST;
17. 移除用戶許可權
# 移除tom用戶對於db.xsb的許可權;
Mysql> revoke all on db.xsb from tom@localhost;
# 刷新授權表;
Mysql> flush privileges;
使用REVOKE收回許可權之後，用戶帳戶的記錄將從db、host、tables_priv、columns_priv表中刪除，但是用戶帳號依然在user表中保存。
八、MySQL 用戶和許可權管理經驗
1. 用戶管理經驗
- 1)、盡量使用create user, grant等語句，而不要直接修改許可權表。
雖然create user, grant等語句底層也是修改許可權表，和直接修改許可權表的效果是一樣的，但是，對於非高手來說，採用封裝好的語句肯定不會出錯，而如果直接修改許可權表，難免會漏掉某些表。而且，修改完許可權表之後，還需要執行flush privileges重新載入到內存，否則不會生效。
- 2). 把匿名用戶刪除掉。
匿名用戶沒有密碼，不但不安全，還會產生一些莫名其妙的問題，強烈建議刪除。
2. 許可權管理經驗
- 1)、只授予能滿足需要的最小許可權，防止用戶幹壞事。比如用戶只是需要查詢，那就只給select許可權就可以了，不要給用戶賦予update、insert或者delete許可權。
- 2)、創建用戶的時候限制用戶的登錄主機，一般是限制成指定IP或者內網IP段。
- 3)、初始化資料庫的時候刪除沒有密碼的用戶。安裝完資料庫的時候會自動創建一些用戶，這些用戶默認沒有密碼。
- 4)、為每個用戶設置滿足密碼複雜度的密碼。
- 5)、定期清理不需要的用戶，回收許可權或者刪除用戶。
九、參考文獻
MySQL之許可權管理MySQL用戶和許可權管理MySQL許可權探索許可權表MySQL 許可權機制和許可權存儲
更多內容請關注公眾號: 雲小祕
推薦閱讀：
