Mysql 用戶權(quán)限管理實(shí)現(xiàn)

1. MySQL 權(quán)限介紹

mysql中存在4個(gè)控制權(quán)限的表，分別為user表，db表，tables_priv表，columns_priv表，我當(dāng)前的版本mysql 5.7.22 。

mysql權(quán)限表的驗(yàn)證過程為：

1. 先從user表中的Host,User,Password這3個(gè)字段中判斷連接的ip、用戶名、密碼是否存在，存在則通過驗(yàn)證。
2. 通過身份認(rèn)證后，進(jìn)行權(quán)限分配，按照user，db，tables_priv，columns_priv的順序進(jìn)行驗(yàn)證。即先檢查全局權(quán)限表user，如果user中對應(yīng)的權(quán)限為Y，則此用戶對所有數(shù)據(jù)庫的權(quán)限都為Y，將不再檢查db, tables_priv,columns_priv；如果為N，則到db表中檢查此用戶對應(yīng)的具體數(shù)據(jù)庫，并得到db中為Y的權(quán)限；如果db中為N，則檢查tables_priv中此數(shù)據(jù)庫對應(yīng)的具體表，取得表中的權(quán)限Y，以此類推。

1.1 MySQL 權(quán)限級別

分為：
全局性的管理權(quán)限： 作用于整個(gè)MySQL實(shí)例級別
數(shù)據(jù)庫級別的權(quán)限： 作用于某個(gè)指定的數(shù)據(jù)庫上或者所有的數(shù)據(jù)庫上
數(shù)據(jù)庫對象級別的權(quán)限：作用于指定的數(shù)據(jù)庫對象上（表、視圖等）或者所有的數(shù)據(jù)庫對象上

權(quán)限存儲在mysql庫的user, db, tables_priv, columns_priv, and procs_priv這幾個(gè)系統(tǒng)表中，待MySQL實(shí)例啟動(dòng)后就加載到內(nèi)存中

查看mysql 有哪些用戶：

mysql> select user,host from mysql.user;

來看root 用戶在權(quán)限系統(tǒng)表中的數(shù)據(jù)：

mysql> use mysql;
mysql> select * from user where user='root' and host='localhost'\G;  #所有權(quán)限都是Y ，就是什么權(quán)限都有
mysql> select * from db where user='root' and host='localhost'\G;  # 沒有此條記錄
mysql> select * from tables_priv where user='root' and host='localhost';  # 沒有此條記錄
 
mysql> select * from columns_priv where user='root' and host='localhost'; # 沒有此條記錄
 
mysql> select * from procs_priv where user='root' and host='localhost'; # 沒有此條記錄

上面說過：權(quán)限的驗(yàn)證過程

查看root@'localhost'用戶的權(quá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)

2. MySQL 權(quán)限詳解

All/All Privileges權(quán)限代表全局或者全數(shù)據(jù)庫對象級別的所有權(quán)限

Alter權(quán)限代表允許修改表結(jié)構(gòu)的權(quán)限，但必須要求有create和insert權(quán)限配合。如果是rename表名，則要求有alter和drop原表， create和insert新表的權(quán)限

Alter routine權(quán)限代表允許修改或者刪除存儲過程、函數(shù)的權(quán)限

Create權(quán)限代表允許創(chuàng)建新的數(shù)據(jù)庫和表的權(quán)限

Create routine權(quán)限代表允許創(chuàng)建存儲過程、函數(shù)的權(quán)限

Create tablespace權(quán)限代表允許創(chuàng)建、修改、刪除表空間和日志組的權(quán)限

Create temporary tables權(quán)限代表允許創(chuàng)建臨時(shí)表的權(quán)限

Create user權(quán)限代表允許創(chuàng)建、修改、刪除、重命名user的權(quán)限

Create view權(quán)限代表允許創(chuàng)建視圖的權(quán)限

Delete權(quán)限代表允許刪除行數(shù)據(jù)的權(quán)限

Drop權(quán)限代表允許刪除數(shù)據(jù)庫、表、視圖的權(quán)限，包括truncate table命令

Event權(quán)限代表允許查詢，創(chuàng)建，修改，刪除MySQL事件

Execute權(quán)限代表允許執(zhí)行存儲過程和函數(shù)的權(quán)限

File權(quán)限代表允許在MySQL可以訪問的目錄進(jìn)行讀寫磁盤文件操作，可使用的命令包括load data infile,select … into outfile,load file()函數(shù)

Grant option權(quán)限代表是否允許此用戶授權(quán)或者收回給其他用戶你給予的權(quán)限,重新付給管理員的時(shí)候需要加上這個(gè)權(quán)限

Index權(quán)限代表是否允許創(chuàng)建和刪除索引

Insert權(quán)限代表是否允許在表里插入數(shù)據(jù)，同時(shí)在執(zhí)行analyze table,optimize table,repair table語句的時(shí)候也需要insert權(quán)限

Lock權(quán)限代表允許對擁有select權(quán)限的表進(jìn)行鎖定，以防止其他鏈接對此表的讀或?qū)?/p>

Process權(quán)限代表允許查看MySQL中的進(jìn)程信息，比如執(zhí)行show processlist, mysqladmin processlist, show engine等命令

Reference權(quán)限是在5.7.6版本之后引入，代表是否允許創(chuàng)建外鍵

Reload權(quán)限代表允許執(zhí)行flush命令，指明重新加載權(quán)限表到系統(tǒng)內(nèi)存中，refresh命令代表關(guān)閉和重新開啟日志文件并刷新所有的表

Replication client權(quán)限代表允許執(zhí)行show master status,show slave status,show binary logs命令

Replication slave權(quán)限代表允許slave主機(jī)通過此用戶連接master以便建立主從復(fù)制關(guān)系

Select權(quán)限代表允許從表中查看數(shù)據(jù)，某些不查詢表數(shù)據(jù)的select執(zhí)行則不需要此權(quán)限，如Select 1+1， Select PI()+2；而且select權(quán)限在執(zhí)行update/delete語句中含有where條件的情況下也是需要的

Show databases權(quán)限代表通過執(zhí)行show databases命令查看所有的數(shù)據(jù)庫名

Show view權(quán)限代表通過執(zhí)行show create view命令查看視圖創(chuàng)建的語句

Shutdown權(quán)限代表允許關(guān)閉數(shù)據(jù)庫實(shí)例，執(zhí)行語句包括mysqladmin shutdown

Super權(quán)限代表允許執(zhí)行一系列數(shù)據(jù)庫管理命令，包括kill強(qiáng)制關(guān)閉某個(gè)連接命令， change master to創(chuàng)建復(fù)制關(guān)系命令，以及create/alter/drop server等命令

Trigger權(quán)限代表允許創(chuàng)建，刪除，執(zhí)行，顯示觸發(fā)器的權(quán)限

Update權(quán)限代表允許修改表中的數(shù)據(jù)的權(quán)限

Usage權(quán)限是創(chuàng)建一個(gè)用戶之后的默認(rèn)權(quán)限，其本身代表連接登錄權(quán)限

2.1 系統(tǒng)權(quán)限表

User表：存放用戶賬戶信息以及全局級別（所有數(shù)據(jù)庫）權(quán)限，決定了來自哪些主機(jī)的哪些用戶可以訪問數(shù)據(jù)庫實(shí)例，如果有全局權(quán)限則意味著對所有數(shù)據(jù)庫都有此權(quán)限
Db表：存放數(shù)據(jù)庫級別的權(quán)限，決定了來自哪些主機(jī)的哪些用戶可以訪問此數(shù)據(jù)庫
Tables_priv表：存放表級別的權(quán)限，決定了來自哪些主機(jī)的哪些用戶可以訪問數(shù)據(jù)庫的這個(gè)表
Columns_priv表：存放列級別的權(quán)限，決定了來自哪些主機(jī)的哪些用戶可以訪問數(shù)據(jù)庫表的這個(gè)字段
Procs_priv表：存放存儲過程和函數(shù)級別的權(quán)限

最重要的還是user表

2.1.1 User和 db 權(quán)限表的結(jié)構(gòu)